Search notes:
Excel: evaluate expressions on the worksheet
It is possible to evaluate (mathematical) expressions on a worksheet and display the result.
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 ' }
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