Workaround for Error 2015 when function too long using Application.Evaluate?

Hi...

I have been trying to find a solution for this error with no luck. I am executing a PERCENTILE function using a custom function in vba. I am passing the parameters (different ranges to filter data and return the array that will be used to calculate the percentile).

The problem is that sometimes, depending on the parameters, the function gets too long. I already learned that Error 2015 is caused because the function string it longer than 255 characters, and Application.Evaluate has that limit.

Now... is there a simple workaround for this?

Thanks

Isn't Error 2015 caused by incorrectly formatted dates, not an over-long string?

So in that case you should check the format of your input.

Well, if it's just that your string reference is too long, couldn't you slice it up into two+ nested references?

Thank you Ben_Hensel...

I am possitive this has to do with string length, since I am creating a string variable to assign it the function, and then pass it to the ActiveSheet.Evaluate method, and when I check the variable, the function is truncated always at 251 chars...

I am wondering if I can slice the references as you said... the problem is that I can't seem to find a way to do it as I suppose it should be done...

This is the function I am trying to run

=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)

this is the short version... the error happens when I add some option in between (options that have been tested already directly in the worksheet, not from vba, and the function works). So... If I am right, the problem is that all the IF's inside the percentile function are dependent, each one creates a filter for the next one... So basically, I should not try to "separate" those, or slice them... right? Or do you see any way I could accomplish this??

Thanks!

Just thinking outloud...

What I am doing is creating a custom function in vba called "CustomPercentile". I pass it all the parameters used in the function I posted above (the original function used in the cells), run it and return the resulting value to the cell

Since I don't have a lot of experience with VBA, the simplest way I found to do this was use the paremeters to recreate the function as a string variable, and then run

Application.ActiveSheet.Evaluate(function)

Now... maybe there is another way (not necessarily easier) to do this without having to create a huge string variable (the function) and run it with the Evaluate method... I tried that approach first, but spent too much time looking for documentation or references on how to do it, until I got tired and decided to use the Evaluate method.

I am not asking for anyone to do the work for me, but if anyone could provide a link with documentation on how I could solve this function in VBA a different way, I would appreciate it.

Thanks!