Application.Evaluate - unexpected results

[Jehannum_2000]

I have a simple function which is meant to evaluate a numerical expression input by the user:

Function eval(n As String) As Double
'Evaluates numeric expression contained in string n

Application.Volatile

On Error GoTo Handle_Error: 'need handler for /0 exceptions etc. from user input

eval = Application.Evaluate(n)
Exit Function

Handle_Error:
MsgBox ("Incorrect expression.")
eval = 0

End Function

When the user enters a simple number, not an expression, the function sometimes works and sometimes doesn't work.

In particular, the numbers 1, 3, 5, 7, 60, 124 and 125 give an error (Type Mismatch, I think). All other positive integers up to 126 work perfectly.

However, if I include the following line in the function (before Application.Evaluate) it always works without an error:

n = n & "+0"

I guessed the above fix, but don't really understand why it works.

Are the 'forbidden' numbers numbers that were previously entered at some point that Excel doesn't want to evaluate again for efficiency reasons? If so, why does appending "+ 0" cause them to work?

I would like to understand a little more about what's going on. Any help appreciated!

 

[Jehannum_2000]

If it were that simple, I'd be able to replicate it, and you would always have the same errors on the same inputs. It sounds like some other factor is at work to me, but I can't think what. That's why I wanted a sample workbook (doesn't need any data in it) just so I can rule out (or in) something specific to the workbook rather than the code.

Well, I can send the cut-down version with just the eval function and input box.

Can I email it to you?

It's on the work computer, so I can send it tomorrow.

 

[RoryA]

Can you not put it on a sharing site like OneDrive or Dropbox?

 

[Alex Blakenburg]

This is not really answering your question but would't doing an explicit conversion using CDbl( n ) instead of application.evaluate( n ) be a better option than your workaround of adding +0 ?

 

[Jehannum_2000]

Can you not put it on a sharing site like OneDrive or Dropbox?

Dropbox

www.dropbox.com

While the cell with the 1 entered in it is selected, the button will try to evaluate the expression.

It doesn't work when it's 1, but does with 2, 3, 4 etc.

 

[RoryA]

That might be the weirdest thing I've seen in a while. With your workbook active, using Application.Evaluate("1") raises a 438 error (Object doesn't support this property or method). Using any other value seems to be fine and also making any other workbook active first makes it work fine with 1. I'm going to have to do some investigating!

 

[RoryA]

It's the buttons! If you delete the button, then the code works. If you then add another button (which defaults to Button 2) then Application.Evaluate(2) fails with the same error. Changing the button name or caption doesn't seem to have any effect - it seems to be the internal index that causes it.
FWIW, using = at the start of the string also seems to fix it.

 

[Jehannum_2000]

Very interesting. It's also more than a little concerning that such a side effect can occur.

I replicated what you did - deleting the button and making a new one - and got the same result.

I repeated the process and yes, it failed on 3 and worked on 1 and 2.

(Edit): with two extant buttons it fails on both their indices whichever button is clicked.

I still wonder why 125 (and 1, 3, 5 etc.) didn't work in my other workbook. I didn't have 125 buttons! I was thinking it was something to do with the fact I'd used these values before (125 kW is my go-to example of the heat input of a commercial gas appliance).