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!

 

[RoryA]

When the user enters a simple number

How/where does the user input the number?

 

[Jehannum_2000]

How/where does the user input the number?

A user form, either by typing directly into a text input box in the form, or - if an appropriate cell on the spreadsheet is selected - the form pulls the number from the cell into the textbox.

It gives the same result whichever source is used: certain numbers (1, 3, 5, 7, 60, 125) cause an error while others don't, and valid expressions such as 6*19 never seem to give an error (hence I had the workaround idea of appending "+0" to numbers to turn them into expressions).

 

[RoryA]

What is the actual calling code?

 

[Jehannum_2000]

Private Sub ConvertButton_Click()

Dim gasType As String
Dim heatInput As Double
Dim gasRate As Double
Dim sh As Worksheet
Set sh = ActiveSheet

gasType = Range("Gas_type")

heatInput = eval(HeatInputTextBox)
'used eval because they may have entered an expression, not a simple value

If heatInput < 0 Then
    MsgBox ("Heat input cannot be negative.")
    Unload Me
    Exit Sub
End If

 

[Jehannum_2000]

Private Sub UserForm_Initialize()

If (Val(ActiveCell.Value) > 0) And (ActiveCell.Column = 12) Then
    HeatInputTextBox = Round(Val(ActiveCell), 2)
Else
    HeatInputTextBox = ""
End If
m3perHourTextBox = ""
kWnetButton = True

End Sub

 

[RoryA]

I can't replicate your error.
Can you post a workbook somewhere so that I can double-check the code in situ? Also, which version(s) of Excel have you tested in?

 

[Jehannum_2000]

Unfortunately, I can't post the workbook.

I've tried it on my own laptop (Microsoft Office Professional Plus 2021) and the work computer (don't know the version, but different from mine) with the same error for the same inputs.

I just think it must be some weird side effect of Application.Evaluate ...

The workaround works, so it's not critical to fix it - it's just my curiosity about Excel weirdness.

Thanks for trying!

 

[Jehannum_2000]

Oh, I forgot to mention - I set up another completely fresh spreadsheet and just copied the eval function and a simple input textbox.

This time, every input worked except the number 1 !?

 

[RoryA]

I just think it must be some weird side effect of Application.Evaluate

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.