VBA IsNumeric Vs IsNumber [Compared with 4 Examples]
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 Application.WorksheetFunction object.
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 Application.WorksheetFunction object.
What do both function 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:
|
1 2 3 4 5 6 7 8 9 10 |
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:
|
1 2 3 4 5 6 7 8 9 |
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?
|
1 2 3 4 5 6 7 |
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:
|
1 2 3 4 5 6 7 8 9 10 |
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.
