Evaluate("1")
 

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

Evaluate("1")
 

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
"John Coleman" wrote in message
oups.com...
| 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
|