Testing for numeric value in VBA

G

Guest

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

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
 
G

Guest

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top