Using Isnumeric and Isnumber in VBA

This tutorial will teach you how to use the IsNumeric and IsNumber functions in VBA to check if values are numbers.

IsNumeric is a built-in VBA function, while IsNumber is an Excel function which can be called from VBA code.

Difference between IsNumber and IsNumeric in VBA

IsNumber checks if a value is stored as a number. Whereas, IsNumeric checks if a value can be converted into a number.

For example, if you pass a blank cell as a parameter, IsNumber will return FALSE, while IsNumeric will return TRUE. Also, if you pass a cell containing number stored as a text, IsNumber will return FALSE and IsNumeric TRUE.

You need to pay attention to these limitations of both functions and decide in which cases is better to use IsNumeric and when IsNumber.

Using IsNumeric in VBA

IsNumeric is the VBA function which checks if a value is numeric and returns a Boolean TRUE or FALSE as a result.

The function can take a variable or a cell value.

Here is an example of taking a cell value:

If IsNumeric(Sheet1.Range("A1").Value) = True Then
    MsgBox "The value in A1 is numeric"
Else
    MsgBox "The value in A1 is not numeric"
End If

In this example, we check if the value from the cell A1 is numeric using the IsNumeric. This function returns the appropriate message, depending on the result of the function.

This next example perform the same operation, except with a variable instead of a cell value:

Dim n as Variant

n = Sheet1.Range("A1").Value

If IsNumeric(n) = True Then
    MsgBox "The value in A1 is numeric"
Else
    MsgBox "The value in A1 is not numeric"
End If

 

Using IsNumber in VBA

IsNumber is an Excel Function, which can be used in VBA. It has an almost similar output as IsNumeric. Let’s look at the example of the IsNumber function:

If Application.WorksheetFunction.IsNumber(Sheet1.Range("A1").Value) = True Then

    MsgBox "The value in A1 is numeric"

Else

    MsgBox "The value in A1 is not numeric"

End If

As you can see from the code, the difference is in the syntax when calling the function. Since IsNumber is the Excel function, we need to put Application.WorksheetFunction before the function call.