Excel: parsing cell and creating a function

Question

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). Possible 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.

You could just put the sum into the cell, like =5*A-2*B-4*C. — Saladin Akara
– Saladin Akara, Commented Nov 28, 2010 at 10:34

jtolle · Accepted Answer · 2010-11-29 16:51:49Z

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/

chris neilsen · Accepted Answer · 2011-02-25 13:06:31Z

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)

Collectives™ on Stack Overflow

Excel: parsing cell and creating a function

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related