256 char limitiations on functions

[NelCanDo]

this is a copy of a previous post on summing a text string.

Example: 21+12+13.....

The string is of variable length with the possibility of 256 or more chars.

Try Jindon's
Function SumInCell(ByVal txt As String) As Double
SumInCell = Evaluate(Replace(Application.Trim(txt), ",", "+"))
End Function

I tried Jindon's solution as suggested by Armondo Montes which works great...except for one thing. Apparently, there is a limitation of a 256 char text string being passed to the evaluate function. Does anyone know if this problem has been resolved after 2010 or if there is a workaround?

I also tried the following solution with the same results. The function crashes when the array is passed to ubound. I would prefer to use evaluate if there is a way to send it a larger string.

Function SumInCell(ByVal txt)
Dim i As Long
Dim NumArray

If (Application.Trim(txt) = NODATA) Then
SumInCell = NODATA
Else
NumArray = Split(DelimitString(Application.Trim(txt)), "+")
For i = LBound(NumArray) To UBound(NumArray)
SumInCell = SumInCell + NumArray(i)
Next i
'SumInCell = Evaluate(DelimitString(Application.Trim(txt)))
End If
End Function
Thanks in advance

 

[drsarao]

VBA as such does not have any char limit. So using native functions, try this:

Function SumInCell(ByVal txt)
Dim i As Long
Dim NumArray
SumInCell = 0
NumArray = Split(Trim(txt), ",")
For i = LBound(NumArray) To UBound(NumArray)
    SumInCell = SumInCell + NumArray(i)
Next i
End Function