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

Here are more IsNumeric examples:

MsgBox IsNumeric("Number")

Result: False

MsgBox IsNumeric("12.34")

Result: True

MsgBox IsNumeric("12:00")

Result: False

To check the cells on Excel Sheet, you can use the following example.

Sub IsNumeric_Example()
    Dim cell As Range
    
    For Each cell In Range("A2:A7")
        cell.Offset(0, 1) = IsNumeric(cell.Text)
    Next cell
End Sub

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.

VBA Coding Made Easy

Stop searching for VBA code online. Learn more about AutoMacro - A VBA Code Builder that allows beginners to code procedures from scratch with minimal coding knowledge and with many time-saving features for all users!


Learn More!