String variables in vba 2010 truncated to 251 characters

[jprealini]

Hi...

I have read that in old Excel versions (97, 2000) when working with string variables in vba there is a character limit of 255, but also read that Excel 2010 should not have that limitation... I am trying to pass a function in a string variable to an Evaluate method, and I am getting error 2015... when watching the string variable that contains the function, I found that it is truncating the string...

Basically what I am doing is this

Dim func As String


    func = "=PERCENTILE(IF('" & officeRangeSheet & "'!" & officeRangeAddress & "=" & officeValue.Address & ",IF('" & statusRangeSheet & "'!" & statusRangeAddress & "=" & statusValue.Address & ",IF(" & yearValue.Address & "=""(All)"",'" & valuesRangeSheet & "'!" & valuesRangeAddress & ",IF(" & yearFunctionString & ",'" & valuesRangeSheet & "'!" & valuesRangeAddress & "))))," & Percentile.Address & ")"
    
    result = Application.ActiveSheet.Evaluate(func)

The func variable contains the function which is being put together using parameters that are being sent from the function call (ranges and values)

This is the original function, just in case

=PERCENTILE(IF('RAW C TIME'!$D$2:$D$51816=$J7,IF('RAW C TIME'!$J$2:$J$51816=$K$4,IF($K$3="(All)",'RAW C TIME'!$I$2:$I$51816,IF('RAW C TIME'!$G$2:$G$51816=$K$3,'RAW C TIME'!$I$2:$I$51816)))),N$6)

Even though it does not have more than 255 characters, in some cases we are sending an array of values for $K$3, so then the function will be longer and get truncated

I am not sure how to solve this, if the case is that in fact the string variables can hold only up to 255 characters.

Possible solutions I see are:

1. Find a workaround for the string variables limit

2. Find a way to first run the array formulas (IF's) in a nested way, in order to retrieve the range of values that will be used for the PERCENTILE function... in that case, I would ask someone to at least tell me where I can find some documentation in order to achieve this. I don't have a lot of experience with vba...

Thanks!

 

[RoryA]

String variables can hold a lot more than 255 characters (2^31 in fact). However, Evaluate will fail if you pass more than 255 characters.

 

[jprealini]

Thanks for your reply

Well... What I am seeing is that when the function with all the parameters is assigned to the func variable, and prior to running the evaluate, func is truncated to 251 characters...

Besides that, then... if evaluate will fail anyway... Is my second option the best choice? Any idea where I can find help to do this?

Thanks!