开发者

Excel: parsing cell and creating a function

I have some cells with, for an example, this value:

5*A-2*B-4*C

In other cells I would put the values for A, B and C. I would like to make additional cell that would count the value. So, for an example, if in some cells it is written that the value of A is 2, value of B is 3 and value of C is 1, I would like an additional cell that would calculate and put the value 0 (that's the result of 5*2-2*3-4*1). Pos开发者_如何学运维sible variables are A, B and C, but they don't have to be contained in every cell (e.g., some cell may be just 5*A-3*C).

Is that possible? Does anyone know how to write that function?

P.S. I can't split manualy values in different cells because there are hundreds of them.

Thanks.


The comment from Saladin Akara points the way to your simplest solution. Define some named formulas (see the Excel help topic "Working with names") for A, B, and C. Then any other cells can contain formulas that use those named values.

If that's not enough, for example if you really want to see and edit the formula in a cell, and then calculate the value of the formula in a different cell, you can use Excel's built-in evaluator without having to parse the formula yourself. The easiest way to do so is via the Evaluate method of the Application object. (Again, see the help.) Charles Williams has example code on his website that evaluates an Excel expression here: http://www.decisionmodels.com/calcsecretsh.htm

Going beyond that, you can use Application.Evaluate to evaluate expressions with (scalar) parameters without defining any names, and still without actually parsing the formula, by doing some rudimentary string replacement. There are several examples on the web, but a very good one from Doug Jenkins is here: http://newtonexcelbach.wordpress.com/2008/04/22/evaluate-function/


This can be done with the VBA function EVALUTE

simple example:

Function ev(f As Variant, A As Range, B As Range, C As Range) As Variant
    Dim s As String
    s = f
    s = Replace(s, "A", "~A~")
    s = Replace(s, "B", "~B~")
    s = Replace(s, "C", "~C~")

    s = Replace(s, "~A~", A.Address)
    s = Replace(s, "~B~", B.Address)
    s = Replace(s, "~C~", B.Address)

    ev = Evaluate(s)
End Function

eg if your expression is in A2, and the values of A, B, C are in C2:E2

=ev(A2,C2,D2,E2)

returns the calculated value

You would want to use a ParamArray instead of A, B, C the variable values to allow for an arbitrary number of variables.

A slightly more complete version:

Function ev(expr As Variant, VarNames As Range, varValues As Range) As Variant
    Dim s As String
    Dim i As Long

    s = expr
    For i = 1 To VarNames.Columns.Count
        s = Replace(s, VarNames.Cells(1, i), "~" & VarNames.Cells(1, i) & "~")
    Next
    For i = 1 To VarNames.Columns.Count
        s = Replace(s, "~" & VarNames.Cells(1, i) & "~", varValues.Cells(1, i).Address)
    Next

    ev = Evaluate(s)
End Function

Usage:

Same data as above plus variable names in C1:E1

=ev(A2,C1:E1,C2:E2)
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜