UDF to sum the cells when they are too large for excel to handle

Sometime you get a large string to be entered as formula(returned by a script or code) and that formula can go beyond excel limitation.  In this case excel can not compute this large string of formula.

This is where this UDF will help.  It splits the formula string on basis of consistent delimiter and then uses application.evaluate method to calculate it and sum them up.


Public Function sumCells(expressionStr As String) As Double
   
    Dim expressionArr() As String
    Dim currentExp As String
    Dim currExpValue As Double
    Application.Volatile
        'split and store in array
        expressionArr = Split(expressionStr, "+")
       
        'evaluate every element and sum them up if its numeric
        For i = 0 To UBound(expressionArr)
            currExpValue = Application.Evaluate(expressionArr(i))
            If IsNumeric(currExpValue) Then
                    sumCells = sumCells + currExpValue
            End If
        Next

    If Not IsNumeric(sumCells) Then
        sumCells = "-"
    End If
    Erase expressionArr
End Function