You can use the IsNumeric function in VBA to check if a given cell is a number.
This function will return True if the value in a given cell is recognized as a number.
Otherwise, the function will return False.
Here is one common way to use this function in practice:
Sub CheckNumeric()
Dim i As Integer
For i = 1 To 9
If IsNumeric(Range("A" & i)) = True Then
Range("B" & i) = "Numeric Value"
Else
Range("B" & i) = "Not a Numeric Value"
End If
Next i
End Sub
This particular macro will check if each cell in the range A1:A9 is a number.
If a cell is a number, then “Numeric Value” will be returned in the corresponding cell in the range B1:B9.
If a cell is not a number, then “Not a Numeric Value” will be returned instead.
The following example shows how to use this syntax in practice.
Example: How to Use IsNumeric in VBA
Suppose we have the following column of values in Excel:
Suppose we would like to check if each cell in column A is number.
We can create the following macro to do so:
Sub CheckNumeric()
Dim i As Integer
For i = 1 To 9
If IsNumeric(Range("A" & i)) = True Then
Range("B" & i) = "Numeric Value"
Else
Range("B" & i) = "Not a Numeric Value"
End If
Next i
End Sub
When we run this macro, we receive the following output:
Column B displays output that tells us whether or not each corresponding cell in column A is recognized as a number or not.
Here are some interesting things to note from the output:
- Numbers with decimals are recognized as numbers.
- Percentages are recognized as numbers.
- Dates are not recognized as numbers.
- Text with numbers are not recognized as numbers.
Note: You can find the complete documentation for the VBA IsNumeric function here.
Additional Resources
The following tutorials explain how to perform other common tasks in VBA:
How to Convert String to Integer in VBA
How to Convert String to Double in VBA
How to Check if String Contains Another String in VBA