Testing for numeric value in VBA

[rmcompute]

I am trying to test a variable to determine if it is numeric

I typed the following in the immediate window:
mvar = 4 ?
isnumber(mvar)

Result: Sub or Function not defined.

In the program, I typed:

Dim mvar as String
mvar = "5"

Application.WorksheetFunction.IsNumber(mvar)
Result: False

mvar = "A"
Application.WorksheetFunction.IsNumber(mvar)
Result: False

Shouldn' t the test return true for 5. How can I test to determine if the
value is a number without checking if it is 0,1,2,3 etc. ?

 

[Steve Yandl]

Try Isnumeric( )

Steve

 

[Ron Rosenfeld]

I am trying to test a variable to determine if it is numeric

I typed the following in the immediate window:
mvar = 4 ?
isnumber(mvar)

Result: Sub or Function not defined.

In the program, I typed:

Dim mvar as String
mvar = "5"

Application.WorksheetFunction.IsNumber(mvar)
Result: False

mvar = "A"
Application.WorksheetFunction.IsNumber(mvar)
Result: False

Shouldn' t the test return true for 5. How can I test to determine if the
value is a number without checking if it is 0,1,2,3 etc. ?

The IsNumber worksheet function tests whether a value IS a number. In your
case, although the value can be evaluated as a number, since you have enclosed
it within double quotes, it IS a string.

The VBA IsNumeric function tests whether an expression can be EVALUATED as a
number. Perhaps that is what you want.

This Sub may help you to see the differences:

=====================
Option Explicit

Sub foo()
Const myvar1 = "1"
Const myvar2 = 1

Debug.Print IsNumeric(myvar1) 'True
Debug.Print IsNumeric(myvar2) 'True

Debug.Print Application.WorksheetFunction.IsNumber(myvar1) 'False
Debug.Print Application.WorksheetFunction.IsNumber(myvar2) 'False


End Sub
======================


--ron

 

[Gary''s Student]

A string containing digits is not a number. IsNumber wil return FALSE for :

1. text strings
2. blanks
3. errors
4. Booleans (logicals)
even though TRUE is 1, IsNumber(TRUE) is false!!
5. references


Use the function and believe what it tells you.