VBA IsNumeric

What is the VBA IsNumeric Function in Excel?

Excel has a built-in function called the VBA IsNumeric that checks if an expression may be evaluated as a number. It is frequently used in Excel programming to guard against mistakes that can happen while manipulating non-numeric values mathematically. When the expression is recognized as a numeric value, the function returns True; otherwise, it returns False. In Excel VBA programs, this function is essential for managing errors and validating data.

Let us look at an example. Here, the subroutine “CheckNumericStatus” is designed to assess the numeric status of values within a specific range (A1:A10) in an Excel worksheet. A Range variable named “TargetCell” is declared to navigate through each cell in the loop. After evaluation, either True or False, is then written to a cell two columns to the right of the original (targetCell.Offset(0, 1).Value). With this method, data in a column can be reviewed quickly and easily, and neighboring cells can be marked to indicate whether or not their corresponding values are numeric. It provides a quick and easy way to see what kinds of data are present throughout the evaluated range.

Syntax

The syntax of the VBA IsNumeric function is straightforward.

IsNumeric(expression)

• expression: This is the variable or value you want to test for numeric evaluation. It can be any data type.

How to Use the VBA IsNumeric Function?

Examples

Example #1

This example shows you how to loop over a range of cells in a column and determine whether or not a numerical value is present in each cell. It enters the outcome (True or False) in the cell next to it.

• Step 1: Begin by defining a new subroutine named IsNumeric_CheckColumn for your code.

• Step 2: Declare a variable named cell that will represent each cell within a Range object during the loop.

• Step 3: Initiate a loop that will iterate over each cell within the range A2:A7.

• Step 4: Within the loop, this line evaluates whether the value in the current cell is numeric and writes the Boolean result (True or False) into the adjacent cell to the right.

• Step 5: This marks the end of the loop, which repeats for each cell in the range.

• Step 6: Run the code

Upon running this code, each cell in the designated range is methodically examined for numeric values. The results are directly sent to the neighboring cells on the right, giving you a quick visual reference to the various data kinds in your column.

Here is the complete code:

Sub IsNumeric_CheckColumn()
Dim cell As Range
For Each cell In Range(“A2:A7”)
cell.Offset(0, 1).Value = IsNumeric(cell.Value)
Next cell
End Sub

Example #2

This example shows you how to apply VBA IsNumeric on a row that contains text, numbers, and strings, among other mixed data types. After analyzing every cell, the result is displayed in the row immediately below.

• Step 1: Start by defining a new subroutine named IsNumeric_MixedDataTypes.

• Step 2: Declare a Range object variable named cell to represent each cell that will be examined in the loop.

• Step 3: Begin a loop that will traverse each cell within the range A2:F2, encompassing a row.

• Step 4: For each cell in the row, this line checks if the value is numeric and writes the result directly below the examined cell.

This line ends the loop, allowing the script to move on to the next cell until the end of the range is reached.

• Step 5: Now click on run.

When you run this macro, it assesses each cell in the specified row for numeric content, documenting the findings (True or False) in the row immediately beneath.

Here is the complete code:

Sub IsNumeric_MixedDataTypes()
Dim cell As Range
For Each cell In Range(“A2:F2”)
cell.Offset(1, 0).Value = IsNumeric(cell.Value)
Next cell
End Sub

Example #3

In this example, we will see how to handle special cases, such as empty cells and cells containing special characters or symbols, showcasing the VBA IsNumeric function’s ability to identify numeric values accurately.

• Step 1: Define a new subroutine called IsNumeric_SpecialCases, preparing to write the script that follows.

• Step 2: Declare a Range object variable named cell for looping purposes and another Range object named TestRange, specifically setting it to the range A1:A10.

• Step 3: This line initiates a loop through each cell within TestRange, allowing individual analysis.

• Step 4: Within the loop, this segment first checks if a cell is empty, marking it as “Empty Cell” in the adjacent cell if true. Otherwise, it evaluates the cell’s content for numeric values, writing the result to the right.

• Step 5: This command signifies the completion of the looping process. The script will iterate through each cell in the defined range, performing the specified checks and actions for each one.

• Step 6: Now, save the macro and click on run. When the code is run, it processes each cell within the specified range (A1:A10). It checks if each cell is empty, marking adjacent cells with “Empty Cell” when true.

For non-empty cells, it evaluates whether the content is numeric and records the result (True or False) in the cell directly to the right.

Here is the complete code:

Sub IsNumeric_SpecialCases()
Dim cell As Range
Dim TestRange As Range
Set TestRange = Range(“A1:A10”)
For Each cell In TestRange
If cell.Value = “” Then
cell.Offset(0, 1).Value = “Empty Cell”
Else
cell.Offset(0, 1).Value = IsNumeric(cell.Value)
End If
Next cell
End Sub

Important Things to Note

• When using the VBA IsNumeric empty cell, the function will return False, as it interprets the lack of content as non-numeric.
• Since VBA IsNumeric is intended to precisely determine the numeric potential of a given input, it is imperative that you confirm that the data or expression being assessed is appropriate for numeric validation if you run into a situation where VBA IsNumeric not working as planned.
• A VBA IsNumeric type mismatch error typically occurs when the function is passed an argument that it cannot evaluate, such as an object or array.
• When working with a VBA IsNumeric range, it is important to iterate through the range cell by cell, applying the VBA IsNumeric function to each individually, to determine their numeric status, effectively facilitating targeted data validation within specified cell ranges.
• The VBA IsNumeric returns False for empty cells since they are not considered numeric.

Frequently Asked Questions (FAQs)

Download Template

This article must be helpful to understand VBA IsNumeric, with its formula and examples. You can download the template here to use it instantly.

Recommended Articles

This has been a guide to VBA IsNumeric. Here we learn IsNumeric function and how to use it in Excel VBA with examples and points to remember. You can learn more from the following articles –

• VBA IsEmpty
• VBA ISNULL
• VBA IsDate Function