VBA IsNumber

What is VBA IsNumber Function?

The VBA IsNumber function is employed to ascertain if a particular statement evaluates to a whole number. It returns True if the expression is numeric and False otherwise. This function is handy when working with conditional logic or data validation in VBA macros.

Let us look at an example. Here, a VBA subroutine named “CheckCellValue” is defined to verify the numeric nature of the value in cell A1 of Sheet1.

The code retrieves the value from cell A1 and stores it in the variable myValue.

Using the VBA IsNumber function from the “Application.WorksheetFunction,” we check whether the value stored in myValue is numeric.

If the value is indeed numeric, the message box displays “The value in A1 is numeric”; otherwise, it indicates “The value in A1 is not numeric”.

Syntax

The syntax for the IsNumber function in VBA is:

IsNumber(Expression)

• Expression is the value or expression you want to evaluate.

How to Use VBA IsNumber Function? 

To use the VBA IsNumber function, follow these steps:

Step 1: Open Excel and press ALT + F11 to open the VBA editor.

Step 2: Insert a new module by clicking on “Insert” in the menu and selecting “Module.”

Step 3: Before using the VBA IsNumber function, declare any variables that will hold the values you want to evaluate.

Variables can be of any data type in VBA, such as Variant, Integer, Double, etc., depending on your specific requirements.

For example: Dim myValue As Variant

Step 4: Assign the values you want to evaluate to the variables declared in Step 1. These values can be obtained from cells, user input, or calculated within the code.

For example: myValue = Sheet1.Range(“A1”).Value

Step 5: Invoke the IsNumber function to check if the assigned values are numeric.

For example” 

If Application.WorksheetFunction.IsNumber(myValue) Then

Note: To use Excel’s built-in functions, such as the VBA IsNumber, we must use “Application.WorksheetFunction” since it permits direct execution of these methods within VBA code.

Step 6: Based on the result returned by the IsNumber function, you can implement conditional logic to execute different actions.

MsgBox “The value in A1 is numeric.”

Step 7: Finally, save the macro and click on run to execute the code.

Examples

Let us look at some examples to understand the working of VBA IsNumber. 

Example #1

In this example, we will see how to iterate through a range of cells and determine if each cell contains a numeric value.

Step 1: In the new module, we start by initiating a subroutine named “CheckRangeForNumbers.”

Step 2: Next, a variable named “rng” of type Range is declared. This variable will represent the range of cells A1:A10.

Step 3: In this step, the variable “rng” is assigned the range of cells A1:A10 from Sheet1.

Step 4: Next, another variable named cell of type Range is declared. This variable will represent each cell within the range A1:A10.

Step 5: In this line of code, we initiate a loop that iterates through each cell in the range specified by rng.

Step 6: Within the loop, the VBA IsNumber function is used to check if the value of the current cell (cell.Value) is numeric.

Step 7: If the value in the cell is numeric, a message box is displayed indicating that the value in the cell is numeric. The “cell.Address” returns the address of the current cell.

Step 8: If the value in the cell is not numeric, a message box is displayed indicating that the value in the cell is not numeric.

Step 9: The loop moves to the next cell in the range, repeating the process until all cells in the range have been checked.

Step 10: Now, save the macro and click on run.

When we run the code, it iterates through each cell in the range A1:A10 on Sheet1.

For each cell, it checks if the value is numeric using the IsNumber function. Depending on the result, it displays a message box indicating whether the value in the cell is numeric or not. This process continues until all cells in the range have been checked.

Here is the full code:

Sub CheckRangeForNumbers()
Dim rng As Range
Set rng = Sheet1.Range(“A1:A10”)
Dim cell As Range
For Each cell In rng
If Application.WorksheetFunction.IsNumber(cell.Value) Then
MsgBox “The value in cell ” & cell.Address & ” is numeric”
Else
MsgBox “The value in cell ” & cell.Address & ” is not numeric”
End If
Next cell
End Sub

Example #2

In this example, we will learn how to check if the value of a specific cell is numeric using the VBA IsNumber function.

Step 1: Firstly, start by declaring a new subroutine named “CheckVariableForNumber.”

Step 2: Next, a variable named “myValue” of type Variant is declared. The value of cell A1 from Sheet1 is assigned to this variable.

Step 3: In this step, the VBA IsNumber function is used to check if the value stored in myValue is numeric.

Step 4: If the value in myValue is numeric, a message box is displayed indicating that the value in cell A1 is numeric.

Step 5: If the value in myValue is not numeric, a message box is displayed indicating that the value in cell A1 is not numeric.

Step 6: When the code is executed, it retrieves the value from cell A1 on Sheet1 and stores it in the variable myValue.

Then, it checks if the value stored in myValue is numeric using the VBA IsNumber function.

Here is the full code:

Sub CheckVariableForNumber()
Dim myValue As Variant
myValue = Sheet1.Range(“A1”).Value
If Application.WorksheetFunction.IsNumber(myValue) Then
MsgBox “The value in A1 is numeric”
Else
MsgBox “The value in A1 is not numeric”
End If
End Sub

Example #3

In this example, we will see how to prompt the user for input, convert it to a numeric value, and determine if it’s numeric using the VBA IsNumber function.

Step 1: Start the code by creating a new subroutine named “CheckUserInputForNumber.”

Step 2: In this step. two variables are declared: “userInput” to store the user’s input as a string, and “numericValue” to store the converted numeric value.

Step 3: Here, we use an input box in VBA that prompts the user to enter a value. The entered value is stored in the userInput variable.

Step 4: The VBA IsNumeric function is used to check if the value stored in userInput can be converted to a numeric value.

Step 5: If the input is numeric, the CDbl function in VBA is used to convert the string value in userInput to a double-precision floating-point number and store it in the “numericValue” variable.

Step 6: The IsNumber function is used to check if the value stored in numericValue is numeric.

Step 7: If the converted value is numeric, a message box is displayed indicating that the input is numeric.

Step 8: If the converted value is not numeric, a message box is displayed indicating that the input is not numeric.

Step 9: If the user input is not numeric, a message box is displayed indicating that the input is invalid.

Step 10: Save the code and click on run. When you run the code, it prompts the user to enter a value.

If the entered value is numeric, it converts the value to a numeric type and checks if it’s numeric using the VBA IsNumber function.

Since the entered value is not numeric, it displays a message box indicating invalid input.

Here is the complete code:

Sub CheckUserInputForNumber()
Dim userInput As String
Dim numericValue As Double
userInput = InputBox(“Enter a value:”)
If IsNumeric(userInput) Then
numericValue = CDbl(userInput)
If Application.WorksheetFunction.IsNumber(numericValue) Then
MsgBox “The input is numeric”
Else
MsgBox “The input is not numeric”
End If
Else
MsgBox “Invalid input”
End If
End Sub

Important Things To Note

1. When using the VBA IsNumber function, if it is not recognized or declared, the error “VBA IsNumber Not Defined” occurs. It could happen if the function is mistyped or if the VBA project does not contain the required library or reference that contains the function definition.
2. For search activities, the VBA IsNumber Search is not usually utilized. Its main purpose is to determine if an expression is a numeric value or not. Instead of using IsNumber to search within strings or ranges, VBA uses methods like Find or functions like “InStr.”
3. The VBA IsNumber Range can be applied to individual cell values within a range to determine their numeric status.
4. The VBA Match function, which is frequently used to find values within arrays or ranges, is not directly interfaced with the VBA IsNumber Match. Match is used for searching and locating certain values within arrays or ranges based on specific criteria, whereas VBA IsNumber verifies the numerical state of a variable.

Frequently Asked Questions (FAQs)

Download Template

This article must help us understand the VBA ISNUMBER formula and examples. You can download the template here to use it instantly.

Recommended Articles

Guide to VBA IsNumber. Here we explain how to use ISNUMBER function in excel VBA along with examples & downloadable excel template. You can learn more from the following articles –

• VBA Format Date
• VBA ISNULL
• VBA IsEmpty