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


"rmcompute" <[email protected]> wrote in message
news:[email protected]...
>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. ?

[Ron Rosenfeld]

On Sat, 4 Feb 2006 12:24:02 -0800, rmcompute
<[email protected]> wrote:

>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.


--
Gary''s Student


"rmcompute" wrote:

> 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. ?