VBA IsNumeric Vs IsNumber [Compared with 4 Examples]

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?

The IsNumeric function checks if an expression can be evaluated as a number.

On the other hand, IsNumber checks if the given expression is a number or not.

Both return a Boolean value i.e. True/False

We will show their potential usage with examples.

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:

str = “15”

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, x is declared as a Variant. If we declared x as an integer then it results in True for 450 number (for both functions).

Conclusion

• The IsNumber function is used to check if a given expression (number, variable, cell, etc.) is a number or not.
• It returns True if it’s a number. For a string (with a number), empty cell, or text - it returns False.
• The IsNumeric function is used to test if the given expression can be evaluated as a number.
• If a string contains numbers, it can be converted to an integer – so you may want to check before performing conversion or using a suspected string in the calculation.