Posts Tagged ‘Math’

Evaluate Math functions

October 14th, 2011 admin 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….


 '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
Categories: Excel, Formulas, Math, VBA Tags: , , ,