#VALUE Error in Evaluate Function of Excel VBA

Question

I am trying to find out what is wrong with the process below. In my sheet, there is a formula string which needs evaluation for different values of variable, x.

(0.5*(23-9.81)*3*(x^2))*(x/3)+(0.5*9.81*(x^2))*(x/3)-1*((0.5*18.5*0.33*(2.5^2))*(x+2.5/3)+(18.5*0.33*2.5*x)*(x/2)+(0.5*(23-9.81)*0.33*(x^2))*(x/3)+(0.5*9.81*(x^2))*(x/3)+(100*0.33*(2.5+x))*((2.5+x)/2))

I have a vba code which looks like this:

Public Function ev(r As Range, x As Double) As Variant
    ev = Application.Evaluate(Replace(r.Value, "x", x))
End Function

What's weird is that this function only works if the cell input is like

=ev(Q25,10.01)

but not

=ev(Q25,10.000000001)

or even

=ev(Q25,10.00001)

Why is this happening? I need the program to calculate using small increments of up to 0.0000000001. Thank you for your insights.

Note: the string above might be wrong but i am not sure why SO prints it like that. I am certain that there are no errors or missing parenthesis in the formula — Jem Eripol
– Jem Eripol, Commented Oct 25, 2020 at 0:31
Text that is enclosed in * characters gets interpreted as italics. That's why your formula looked strange. If you escape the * character like this: \* then it won't create italics — barrowc
– barrowc, Commented Oct 25, 2020 at 0:50
Why don't you use A1 instead of x in your formula and simply change the value in A1 ? — teylyn
– teylyn, Commented Oct 25, 2020 at 1:03
I edited the question and add code tags, so the formula displays as intended. — teylyn
– teylyn, Commented Oct 25, 2020 at 1:04
I think Evaluate can only handle a string up to 255 characters long. — BigBen
– BigBen, Commented Oct 25, 2020 at 2:39

BigBen · Accepted Answer · 2020-10-25 13:50:13Z

2

There is a 255 character limit to a String passed to Application.Evaluate. You're running into that hard limit after Replaceing x with the corresponding numeric value.

Your options include:

Breaking up the formula into pieces and Evaluateing it in several steps.
Rewriting the formula to something shorter as mentioned in the comments (though you may still surpass the 255 limit based on length of the proposed arguments).
Using an approach like this and writing the revised formula to a cell, then reading back the calculated value.

answered Oct 25, 2020 at 13:50

BigBen

50.4k7 gold badges29 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

#VALUE Error in Evaluate Function of Excel VBA

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related