VBA IsNumeric Vs IsNumber

IsNumeric Vs IsNumber in VBA

The first difference between these two functions is:

IsNumeric is the VBA function that you may use directly.

IsNumber is the Excel function that you may use in VBA. However, you have to use this with the Application.WorksheetFunction object.

What do both functions check?

An example that will make it clear

Just look at this simple example to learn how both functions work.

We have a String variable in a VBA program and assign it the following value:

Now, let us pass this string in both functions and see the output:

Sub IsNum_ex()

Dim str As String
str = "15"

MsgBox "IsNumber Result: " & Application.WorksheetFunction.IsNumber(str)

MsgBox "IsNumeric Result: " & IsNumeric(str)

End Sub

Results:

IsNumber output:

IsNumeric output:

Now let us look at a few more examples to understand the difference.

What if we pass the Integer variable to both functions

We declared an integer variable now and passed this to IsNumber and IsNumeric functions.

See the output for both:

Sub IsNum_ex()
Dim num As Integer
num = 10

MsgBox "IsNumber Result: " & Application.WorksheetFunction.IsNumber(num)

MsgBox "IsNumeric Result: " & IsNumeric(num)

End Sub

Result:

What about using an empty Excel cell in IsNumber and IsNumeric?

Sub IsNum_ex()

MsgBox "Empty Cell IsNumber Result: " & Application.WorksheetFunction.IsNumber(Range("A3"))

MsgBox "Empty Cell IsNumeric Result: " & IsNumeric(Range("A3"))

End Sub

Result:

Using an InputBox example

In the example below, we take the user input by using the InputBox function.

Entered a number in the input box and let us see what both functions return:

Sub IsNum_ex()

Dim x
x = InputBox("Enter a number")

MsgBox "User Input IsNumber Result: " & Application.WorksheetFunction.IsNumber(x)

MsgBox "User Input IsNumeric Result: " & IsNumeric(x)

End Sub

Result:

You saw we entered 450 in the input box and isNumber returned False whereas IsNumeric returned True as this value can be converted to a number.

If we enter an alphabet, both functions will return False.

Also, note that x is declared as a Variant. If we declare x as an integer then it results in True for the “450” number (for both functions).

Conclusion