Evaluate("1")

[John Coleman]

Greetings,

This is strange - I wrote a simple macro (stored in my personal macro
workbook) to generate a column of equally spaced numbers. It contained
the line

stepSize = CDec(Evaluate(InputBox("Enter step size")))

I did it this way since I wanted to be able to enter mathematical
expressions like 1/45 and not just numbers. It seemed to work fine for
a week or so. But then - when I tried to use it to modify a previously
written spreadsheet the above line threw a "Run-time error 438 -
Object doesn't support this property or method" error on the stepsize
1. Somewhat curiously, I found that when I typed ?Evaluate("1") in the
immediate window I got the error but ?Evaluate("3") returned 3 as
expected. I experminented with several old workbooks and found that
for all of the ones I wrote in Excel 2000 (or earlier) and some that I
wrote in Excel 2003 I get the error when I type ?Evaluate("n") for
some but not all small n. Curiously, sometimes "1" is ok but it balks
at "2" or "3". Any idea what is going on?

The workaround was easy:

stepSize = CDec(Evaluate("=" & InputBox("Enter step size")))

seems ok.

Is Evaluate supposed to work this way or is it a bug?

-John Coleman

 

[John Coleman]

Greetings,

This is strange - I wrote a simple macro (stored in my personal macro
workbook) to generate a column of equally spaced numbers. It contained
the line

stepSize = CDec(Evaluate(InputBox("Enter step size")))

I did it this way since I wanted to be able to enter mathematical
expressions like 1/45 and not just numbers. It seemed to work fine for
a week or so. But then - when I tried to use it to modify a previously
written spreadsheet the above line threw a "Run-time error 438 -
Object doesn't support this property or method" error on the stepsize
1. Somewhat curiously, I found that when I typed ?Evaluate("1") in the
immediate window I got the error but ?Evaluate("3") returned 3 as
expected. I experminented with several old workbooks and found that
for all of the ones I wrote in Excel 2000 (or earlier) and some that I
wrote in Excel 2003 I get the error when I type ?Evaluate("n") for
some but not all small n. Curiously, sometimes "1" is ok but it balks
at "2" or "3". Any idea what is going on?

The workaround was easy:

stepSize = CDec(Evaluate("=" & InputBox("Enter step size")))

seems ok.

Is Evaluate supposed to work this way or is it a bug?

-John Coleman

This topic was covered in a thread started in this group by Walkenbach
back in 1999 (search for "Bug in Evaluate Method?"). It was a bug in
Excel 1997 - and hasn't been fixed since then. I is caused by
Evaluate("1") returning the first embedded OLE object (if any) in the
active sheet and 1 otherwise. Pretty much all of my older spreadsheets
written in Excel 2000 have such things but many of mynewer sheets lack
them (stylistically, I have been moving away from embedded controls to
user forms more often). Thus when I noticed the strong correlation
between the bug and when the workbook was created I thought it was a
compatibility issue. I'll have to remember this example for my stats
class - correlations don't always mean what you first suppose.

-John Coleman

 

[Jim Rech]

I never would have thought to add Evaluate to the mix. What does it add?

Sub a()
Dim stepSize As Variant
On Error Resume Next
stepSize = CDec(InputBox("Enter step size"))
If IsEmpty(stepSize) Then ''No entry, Cancel, Non-number
MsgBox "No step"
Else
MsgBox "Step is " & stepSize
End If
End Sub


--
Jim
| Greetings,
|
| This is strange - I wrote a simple macro (stored in my personal macro
| workbook) to generate a column of equally spaced numbers. It contained
| the line
|
| stepSize = CDec(Evaluate(InputBox("Enter step size")))
|
| I did it this way since I wanted to be able to enter mathematical
| expressions like 1/45 and not just numbers. It seemed to work fine for
| a week or so. But then - when I tried to use it to modify a previously
| written spreadsheet the above line threw a "Run-time error 438 -
| Object doesn't support this property or method" error on the stepsize
| 1. Somewhat curiously, I found that when I typed ?Evaluate("1") in the
| immediate window I got the error but ?Evaluate("3") returned 3 as
| expected. I experminented with several old workbooks and found that
| for all of the ones I wrote in Excel 2000 (or earlier) and some that I
| wrote in Excel 2003 I get the error when I type ?Evaluate("n") for
| some but not all small n. Curiously, sometimes "1" is ok but it balks
| at "2" or "3". Any idea what is going on?
|
| The workaround was easy:
|
| stepSize = CDec(Evaluate("=" & InputBox("Enter step size")))
|
| seems ok.
|
| Is Evaluate supposed to work this way or is it a bug?
|
| -John Coleman
|