Search notes:

Excel: evaluate expressions on the worksheet

It is possible to evaluate (mathematical) expressions on a worksheet and display the result.

The following example tries to demonstrate how this might be done with Visual Basic for Applications:

option explicit

sub main() ' {

    activeSheet.name = "expressions"

    cells(2,2) = "2+3+4+5"
    cells(3,2) = "22/3"
    cells(4,2) = "sin(3.1)"

    activeSheet.names.add _
       name          := "evalExprToTheRight"             , _
       refersToR1C1  := "=evaluate( expressions!rc[-1])"

    cells(2,3).formulaR1C1 = "=evalExprToTheRight"
    cells(2,3).autoFill                               _
       destination := range(cells(2,3), cells(4,3)) , _
       type        := xlFillCopy

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Name/evaluate-expression.bas

After running the program, the worksheet has the following expresssions and results.

This example roughly corresponds to the following GUI manipulations:

Enter some expressions (such as 2+3+4+5)
Open Formulas -> Defined Names -> Define Name
Assign evalExprToTheRight to Name
Assign =evaluate( expressions!rc[-1]) to Refers To (Be sure to use a relative referience)
Create a formula to the right of the expressions with the simple text = evalExprToTheRight

Excel: evaluate expressions on the worksheet

See also