Is there a limit to "Evaluate" in VBA

[jcg31]

I have an application that uses Evaluate.

Calcit = "Y794 + Y873 + Y907 + Y946 + Y980 + Y1042 + Y526 + Y567 . . ."
FinalNum= Worksheet(Name).Evaluate (Calcit)

I am finding that Evaluate will not sum more than 32 cells arranged as above. Is this the case and if so is there a way around it?

Thanks,
Jim

 

[cgcamal]

Hi jcg31,

An idea would be to include all that cells in a unique range. Define the range containing those cells,
as they are non contiguous cells, I think "Union" command could help. After that you can use SUM(Range)
within Evaluate, something like:

[COLOR=Navy]x=Evaluate("[/COLOR][COLOR=Navy]SUM(Range)[/COLOR][COLOR=Navy]")[/COLOR]

You can try, It's only an idea.

Hope this helps.

Regards.

 

[jcg31]

Thanks,
the issue is that the user is selecting non contiguous cells and operands to create the formula being evaluated. So rather than a series of sums it could be ((k112/k32*23)+27)/k23. it just happens that I noticed that a series of sums above 32 fails, and while it isn't likely to occur frequently it was a problem I wanted to correct.

Jim

 

[jcg31]

So I am finding out that there is a 255 character limit to the string being evaluated, but so far no work around.

Jim