## IsNumeric in VBA: 8 Ways to Use the Function

*in*DATE

In VBA, to test whether an expression is a number, the ** IsNumeric** function can be used.

Contents

## Description

The **IsNumeric** function evaluates whether the input expression is a number and returns a **Boolean** value (TRUE or FALSE). It returns **True** if the entire *expression* is a number; otherwise, it returns **False**.

## Syntax

**IsNumeric**(*expression*)

The input *expression* is either a numeric expression or a string expression.

## VBA examples of IsNumeric

### Example 1 – Using IsNumeric with IF-THEN-ELSE

The following VBA function CheckNumeic uses **IsNumeric** to test whether input1 is numeric or not with an IF-THEN-ELSE statement.

Function CheckNumeric(ByVal input1) As String If IsNumeric(input1) = True Then MyFunction1 = "Input is numeric" Else MyFunction1 = "Input is not numeric" End If End Function

I would want to draw your attention to line 2. While this line is correct, it is not necessary. Instead, it can be written as:

`If IsNumeric(input1) Then`

In VBA, whenever you perform a logical test, for the condition of TRUE, you don’t have to type the `=TRUE`

in the statement.

### Example2 – Negate the result of IsNumeric

The VBA function below returns the opposite of `IsNumeric`

. The function uses the same IF-THEN-ELSE structure.

Function IsNumericReverse(ByVal input1) As Boolean If IsNumeric(input1) = True Then IsNumericReverse = False Else IsNumericReverse = True End If End Function

However, this entire structure is not necessary. Instead, the function can be simplified as follows. The **Not** logical operator can be used to reverse (negate) the answer of IsNumeric, which serves the objective of the function in this case.

Function IsNumericReverse(ByVal input1) As Boolean IsNumericReverse = Not (IsNumeric(input1)) End Function

### Example 3 – VBA action if not numeric

The VBA function counts the number of non-numeric cells in the input range. In line 5, note the use of `Not`

with `IsNumeric`

. In the sentence structure here, putting a space after `No`

t is enough, which is equivalent to using a bracket after `Not`

to wrap the condition being tested.

Function countNonNumeric(range1 As Range) As Long Dim cell As Range Dim counter As Long For Each cell In range1.Cells If Not IsNumeric(cell.Value) Then 'use Not to test for non-numeric counter = counter + 1 End If Next countNonNumeric = counter End Function

## Special Case with Blank

When using IsNumeric, you need to be aware that “blank” (string of zero length) does not mean zero (0) and it is considered to be non-numeric:

`IsNumeric("") `

returns False

Therefore, when you write your macro, if you want to treat blank as numeric, you may have to use conditional statements to handle the case of blank string inputs.

## Special Case with Dates

Another special case with `IsNumeric`

is the treatment of date inputs. Most people conceptually think dates are numeric (after all, they can be converted into date-serials in the computer). However the `IsNumeric`

function in VBA considers dates as non-Numeric:

`IsNumeric("10/2/2020") `

returns False

Even if you try to input a date truly in Date format, IsNumeric still returns False:

`IsNumeric(DateSerial(2020, 10, 2)) `

returns False

Therefore, you may also need to use conditional statements to handle the case of dates expressions.

## Special Case with Time

Handling of time expressions by the ISNUMERIC is difficult to manage, or I would describe it as unpredictable. Therefore, when you input expressions contain time expressions, you must test your macro thoroughly.

There are three possibilities with time expressions. Let’s experiment with the macro below. In cell A1, we place a time of “3:00:00 AM” first. Then we run the macro which test 3 cases:

- time in form of string (variable x1)
- time in form of time value (variable x2)
- time placed in a cell in an Excel sheet (variable x3)

Sub TimeCases() Dim y x1 = IsNumeric("3:00:00 AM") x2 = IsNumeric(TimeSerial(3, 0, 0)) y = Range("A1").Value x3 = IsNumeric(y) MsgBox x1 &amp;amp;amp;amp; Chr(10) &amp;amp;amp;amp; x2 &amp;amp;amp;amp; Chr(10) &amp;amp;amp;amp; x3 End Sub

Run the macro and the answers will be displayed in the Msgbox:

## Calling Excel Worksheet Function ISNUMBER() in VBA

As an alternative to ** IsNumeric** in VBA, you may call the Excel worksheet function

**in your macro. There are two ways to do this. See line 2 and line 3 in the VBA function below, which do the same job. (I personally prefer Method 2.)**

`ISNUMBER()`

Function CallIsNumber(input1) As Boolean x = WorksheetFunction.IsNumber(input1) 'Method 1' x = Application.IsNumber(input1) 'Method 2' CallIsNumber = x End Function

## VBA ISNUMERIC vs ISNUMBER Worksheet Function

Although you can either use `IsNumeric`

or Excel worksheet function `ISNUMBER`

to check whether an input expression is numeric, you have to be aware of the differences between the two methods in order to program your macros correctly to produce expected result.

Expression | Date type of expression | ISNUMERIC returns (VBA) | ISNUMBER returns (worksheet function) |

123 | Number | TRUE | TRUE |

“123” | Number in form of string | TRUE | FALSE |

12/2/2020 | Date | FALSE | TRUE |

“12/2/2020” | Date in form of String | FALSE | FALSE |

DateSerial(2020,10,2) | Date in form of Date Value | FALSE | FALSE |

3:00:00 AM | Time placed in a cell | TRUE | TRUE |

“3:00:00 AM” | Time in form of String | FALSE | FALSE |

TimeSerial(3, 0,0) | Time in form of Time Value | FALSE | FALSE |

“” (blank) | String | FALSE | TRUE |

See also: