## Evaluate Math functions

October 14th, 2011
No comments

I got a question about math functions in excel from a guy

He needed to parse a function like (3^[x1])+([x2]^2)+[x1]+[x3]+3*[x4] into a function

And needed to parse the values for the variables via a range.

I thought I would spread the wealth and share my code with the world….

voila..

'Set reference to: Microsoft Scripting Runtime Public Function EVAL(sFormula As String, rVarValues As Range) Dim tmpDict As Dictionary Dim Cell As Range Dim i As Long Set tmpDict = VariableDictionary(sFormula, "[", "]") i = 0 For Each Cell In rVarValues sFormula = Replace(sFormula, tmpDict.Items(i), Cell.Value): i = i + 1 Next tmpCell Set tmpDict = Nothing EVAL = EVALUATE(sFormula) End Function Private Function VariableDictionary(sString, sStart, sEnd) As Dictionary Dim tmpDict As Dictionary Dim strTemp As String Dim i As Long strTemp = "" Set tmpDict = New Dictionary On Error Resume Next For i = 1 To Len(sString) If Mid(sString, i, 1) = sStart Then strTemp = "" strTemp = strTemp & Mid(sString, i, 1) If Mid(sString, i, 1) = sEnd Then tmpDict.Add strTemp, strTemp Next i On Error GoTo 0 Set VariableDictionary = tmpDict Set tmpDict = Nothing End Function