Using Isnumeric and Isnumber in VBA

Written by

Editorial Team

Reviewed by

Editorial Team

Last updated on March 16, 2024

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.