`IsNumeric` Function

Returns a Boolean value indicating whether an expression can be evaluated as a number.

Syntax

IsNumeric(expression)

Parameters

expression (Required): Variant expression to test

Return Value

Returns a Boolean: - True if the expression can be evaluated as a number - False if the expression cannot be evaluated as a number - Returns True for numeric strings ("123", "45.67", "-89") - Returns True for date/time values (they're stored as numbers) - Returns True for Boolean values (True = -1, False = 0) - Returns False for Null - Returns False for Empty - Returns False for non-numeric strings - Recognizes hexadecimal (&H) and octal (&O) notation - Recognizes currency symbols in some locales

Remarks

The IsNumeric function is used to determine whether an expression can be converted to a number:

Validates input before numeric conversion
Prevents Type Mismatch errors from CInt, CLng, CDbl, etc.
Recognizes various numeric formats (Integer, Decimal, and scientific notation)
Locale-dependent for currency and decimal separators
Date/Time values return True (internally stored as Double)
Boolean values return True (True = -1, False = 0)
Returns False for Null and Empty
Hexadecimal literals (&H10) return True
Octal literals (&O77) return True
Leading/trailing spaces are ignored
Common in data validation and input processing
Use before converting strings to numbers
Cannot distinguish between integer and floating-point capable strings
VarType and TypeName provide more detailed type information

Typical Uses

Input Validation: Verify user input is numeric before conversion
Data Type Checking: Determine if Variant contains numeric data
Form Validation: Validate textbox entries contain valid numbers
File Processing: Validate data from CSV or text files
Error Prevention: Avoid Type Mismatch errors in calculations
Dynamic Typing: Handle Variant data with unknown types
Database Import: Validate data before insertion
Report Generation: Filter numeric values from mixed data

Basic Usage Examples

' Example 1: Basic numeric validation
Dim value As Variant

value = 123
Debug.Print IsNumeric(value)       ' True

value = "456"
Debug.Print IsNumeric(value)       ' True - numeric string

value = "abc"
Debug.Print IsNumeric(value)       ' False - non-numeric string

' Example 2: Various data types
Debug.Print IsNumeric(123)         ' True - Integer
Debug.Print IsNumeric(45.67)       ' True - Double
Debug.Print IsNumeric("123")       ' True - numeric string
Debug.Print IsNumeric("12.34")     ' True - decimal string
Debug.Print IsNumeric("-89")       ' True - negative number
Debug.Print IsNumeric("1E+5")      ' True - scientific notation
Debug.Print IsNumeric(True)        ' True - Boolean (-1)
Debug.Print IsNumeric(False)       ' True - Boolean (0)
Debug.Print IsNumeric(#1/1/2025#)  ' True - Date (stored as number)
Debug.Print IsNumeric("Hello")     ' False - text
Debug.Print IsNumeric(Null)        ' False - Null
Debug.Print IsNumeric(Empty)       ' False - Empty
Debug.Print IsNumeric("")          ' False - empty string
Debug.Print IsNumeric("&H10")      ' True - hexadecimal
Debug.Print IsNumeric("&O77")      ' True - octal

' Example 3: Input validation
Sub ProcessInput()
Dim userInput As String
Dim numValue As Double

userInput = InputBox("Enter a number:")

If IsNumeric(userInput) Then
numValue = CDbl(userInput)
MsgBox "You entered: " & numValue
Else
MsgBox "Invalid number. Please enter a numeric value.", vbExclamation
End If
End Sub

' Example 4: Filter numeric values from array
Function GetNumericValues(data As Variant) As Variant
Dim result() As Variant
Dim count As Long
Dim i As Long

If Not IsArray(data) Then
GetNumericValues = Array()
Exit Function
End If

ReDim result(LBound(data) To UBound(data))
count = LBound(data) - 1

For i = LBound(data) To UBound(data)
If IsNumeric(data(i)) Then
count = count + 1
result(count) = data(i)
End If
Next i

If count >= LBound(result) Then
ReDim Preserve result(LBound(result) To count)
GetNumericValues = result
Else
GetNumericValues = Array()
End If
End Function

Common Patterns

' Pattern 1: Safe numeric conversion
Function SafeCDbl(value As Variant) As Variant
If IsNumeric(value) Then
SafeCDbl = CDbl(value)
Else
SafeCDbl = Null
End If
End Function

' Pattern 2: Numeric with default value
Function ToNumber(value As Variant, Optional defaultValue As Double = 0) As Double
If IsNumeric(value) Then
ToNumber = CDbl(value)
Else
ToNumber = defaultValue
End If
End Function

' Pattern 3: Validate all values are numeric
Function AllNumeric(ParamArray values() As Variant) As Boolean
Dim i As Long

For i = LBound(values) To UBound(values)
If Not IsNumeric(values(i)) Then
AllNumeric = False
Exit Function
End If
Next i

AllNumeric = True
End Function

' Pattern 4: Count numeric values
Function CountNumeric(arr As Variant) As Long
Dim count As Long
Dim i As Long

If Not IsArray(arr) Then
CountNumeric = 0
Exit Function
End If

count = 0
For i = LBound(arr) To UBound(arr)
If IsNumeric(arr(i)) Then
count = count + 1
End If
Next i

CountNumeric = count
End Function

' Pattern 5: Validate textbox input
Function ValidateNumericTextBox(txt As TextBox) As Boolean
If Trim$(txt.Text) = "" Then
MsgBox "Please enter a value", vbExclamation
ValidateNumericTextBox = False
ElseIf Not IsNumeric(txt.Text) Then
MsgBox "Please enter a valid number", vbExclamation
txt.SetFocus
ValidateNumericTextBox = False
Else
ValidateNumericTextBox = True
End If
End Function

' Pattern 6: Sum only numeric values
Function SumNumeric(values As Variant) As Double
Dim total As Double
Dim i As Long

If Not IsArray(values) Then
SumNumeric = 0
Exit Function
End If

total = 0
For i = LBound(values) To UBound(values)
If IsNumeric(values(i)) Then
total = total + CDbl(values(i))
End If
Next i

SumNumeric = total
End Function

' Pattern 7: Parse numeric value with error info
Function TryParseNumber(text As String, ByRef result As Double) As Boolean
If IsNumeric(text) Then
result = CDbl(text)
TryParseNumber = True
Else
result = 0
TryParseNumber = False
End If
End Function

' Pattern 8: Validate range of values
Function ValidateNumericRange(value As Variant, minVal As Double, maxVal As Double) As Boolean
If Not IsNumeric(value) Then
ValidateNumericRange = False
Exit Function
End If

Dim numVal As Double
numVal = CDbl(value)

ValidateNumericRange = (numVal >= minVal And numVal <= maxVal)
End Function

' Pattern 9: Extract numbers from mixed array
Function ExtractNumbers(data As Variant) As Variant
Dim numbers() As Double
Dim count As Long
Dim i As Long

If Not IsArray(data) Then
ExtractNumbers = Array()
Exit Function
End If

ReDim numbers(LBound(data) To UBound(data))
count = -1

For i = LBound(data) To UBound(data)
If IsNumeric(data(i)) Then
count = count + 1
numbers(count) = CDbl(data(i))
End If
Next i

If count >= 0 Then
ReDim Preserve numbers(0 To count)
ExtractNumbers = numbers
Else
ExtractNumbers = Array()
End If
End Function

' Pattern 10: Database field validation
Function ValidateNumericField(rs As Recordset, fieldName As String) As Boolean
If IsNull(rs.Fields(fieldName).Value) Then
ValidateNumericField = False
ElseIf IsNumeric(rs.Fields(fieldName).Value) Then
ValidateNumericField = True
Else
ValidateNumericField = False
End If
End Function

Advanced Usage Examples

' Example 1: Comprehensive form validator
Public Class FormValidator
Private m_errors As Collection

Private Sub Class_Initialize()
Set m_errors = New Collection
End Sub

Public Function ValidateForm(frm As Form) As Boolean
Dim ctrl As Control

m_errors.Clear

For Each ctrl In frm.Controls
If TypeOf ctrl Is TextBox Then
If ctrl.Tag <> "" Then
ValidateControl ctrl
End If
End If
Next ctrl

ValidateForm = (m_errors.Count = 0)
End Function

Private Sub ValidateControl(ctrl As Control)
Dim tags As Variant
Dim i As Long

tags = Split(ctrl.Tag, ",")

For i = LBound(tags) To UBound(tags)
Select Case Trim$(tags(i))
Case "required"
If Trim$(ctrl.Text) = "" Then
m_errors.Add ctrl.Name & ": Field is required"
End If

Case "numeric"
If Trim$(ctrl.Text) <> "" Then
If Not IsNumeric(ctrl.Text) Then
m_errors.Add ctrl.Name & ": Must be a number"
End If
End If

Case "positive"
If IsNumeric(ctrl.Text) Then
If CDbl(ctrl.Text) <= 0 Then
m_errors.Add ctrl.Name & ": Must be positive"
End If
End If

Case "integer"
If IsNumeric(ctrl.Text) Then
If CDbl(ctrl.Text) <> CLng(ctrl.Text) Then
m_errors.Add ctrl.Name & ": Must be an integer"
End If
End If
End Select
Next i
End Sub

Public Function GetErrors() As Collection
Set GetErrors = m_errors
End Function
End Class

' Example 2: CSV parser with type detection
Public Class CSVParser
Public Function ParseLine(line As String) As Variant
Dim fields() As Variant
Dim parts As Variant
Dim i As Long

parts = Split(line, ",")
ReDim fields(LBound(parts) To UBound(parts))

For i = LBound(parts) To UBound(parts)
fields(i) = ConvertField(Trim$(parts(i)))
Next i

ParseLine = fields
End Function

Private Function ConvertField(value As String) As Variant
' Auto-convert to appropriate type
If value = "" Then
ConvertField = Empty
ElseIf UCase$(value) = "NULL" Then
ConvertField = Null
ElseIf IsNumeric(value) Then
' Determine if integer or floating point
If InStr(value, ".") > 0 Or InStr(value, "E") > 0 Then
ConvertField = CDbl(value)
Else
ConvertField = CLng(value)
End If
ElseIf IsDate(value) Then
ConvertField = CDate(value)
Else
ConvertField = value  ' Keep as string
End If
End Function
End Class

' Example 3: Data statistics calculator
Public Class DataStatistics
Public Function Calculate(data As Variant) As Dictionary
Dim stats As Dictionary
Dim numbers() As Double
Dim count As Long
Dim i As Long
Dim total As Double
Dim mean As Double
Dim variance As Double

Set stats = CreateObject("Scripting.Dictionary")

If Not IsArray(data) Then
stats("error") = "Not an array"
Set Calculate = stats
Exit Function
End If

' Extract numeric values
ReDim numbers(LBound(data) To UBound(data))
count = -1

For i = LBound(data) To UBound(data)
If IsNumeric(data(i)) Then
count = count + 1
numbers(count) = CDbl(data(i))
End If
Next i

If count < 0 Then
stats("error") = "No numeric values found"
Set Calculate = stats
Exit Function
End If

ReDim Preserve numbers(0 To count)

' Calculate statistics
total = 0
For i = 0 To count
total = total + numbers(i)
Next i

mean = total / (count + 1)

variance = 0
For i = 0 To count
variance = variance + (numbers(i) - mean) ^ 2
Next i
variance = variance / (count + 1)

stats("count") = count + 1
stats("sum") = total
stats("mean") = mean
stats("variance") = variance
stats("stddev") = Sqr(variance)
stats("min") = MinValue(numbers)
stats("max") = MaxValue(numbers)

Set Calculate = stats
End Function

Private Function MinValue(arr() As Double) As Double
Dim min As Double
Dim i As Long

min = arr(0)
For i = 1 To UBound(arr)
If arr(i) < min Then min = arr(i)
Next i

MinValue = min
End Function

Private Function MaxValue(arr() As Double) As Double
Dim max As Double
Dim i As Long

max = arr(0)
For i = 1 To UBound(arr)
If arr(i) > max Then max = arr(i)
Next i

MaxValue = max
End Function
End Class

' Example 4: Smart input parser
Public Class SmartParser
Public Function Parse(input As String) As Variant
Dim trimmed As String

trimmed = Trim$(input)

If trimmed = "" Then
Parse = Empty
Exit Function
End If

' Try to parse as number
If IsNumeric(trimmed) Then
Parse = ParseAsNumber(trimmed)
Exit Function
End If

' Try to parse as date
If IsDate(trimmed) Then
Parse = CDate(trimmed)
Exit Function
End If

' Try to parse as boolean
Select Case UCase$(trimmed)
Case "TRUE", "YES", "Y", "1"
Parse = True
Exit Function
Case "FALSE", "NO", "N", "0"
Parse = False
Exit Function
End Select

' Return as string
Parse = trimmed
End Function

Private Function ParseAsNumber(value As String) As Variant
' Determine best numeric type
If InStr(value, ".") > 0 Or InStr(value, "E") > 0 Or InStr(value, "e") > 0 Then
ParseAsNumber = CDbl(value)
Else
Dim numVal As Double
numVal = CDbl(value)

If numVal >= -32768 And numVal <= 32767 And numVal = CLng(numVal) Then
ParseAsNumber = CInt(numVal)
ElseIf numVal >= -2147483648# And numVal <= 2147483647# And numVal = CLng(numVal) Then
ParseAsNumber = CLng(numVal)
Else
ParseAsNumber = numVal
End If
End If
End Function
End Class

Error Handling

The IsNumeric function itself does not raise errors:

' IsNumeric is safe to call on any value
Debug.Print IsNumeric(123)           ' True
Debug.Print IsNumeric("456")         ' True
Debug.Print IsNumeric("abc")         ' False
Debug.Print IsNumeric(Null)          ' False
Debug.Print IsNumeric(Empty)         ' False

' Common pattern: validate before conversion
Dim text As String
text = "123abc"

If IsNumeric(text) Then
value = CDbl(text)  ' Safe conversion
Else
MsgBox "'" & text & "' is not a valid number", vbExclamation
End If

Performance Considerations

Fast Operation: IsNumeric is a relatively fast check
Locale-Dependent: May be slower in some locales due to format parsing
Cache Results: If checking the same value multiple times, cache the result
Prefer Typed Variables: When possible, use typed variables to avoid checks

Best Practices

Always Validate Input: Check IsNumeric before converting user input
Handle All Cases: Account for Null, Empty, and empty string
Provide Feedback: Give clear error messages when validation fails
Consider Range: IsNumeric doesn't check if value fits in target type
Locale Awareness: Be aware of decimal separator differences across locales
Combine Checks: Often combine with IsNull, IsEmpty for complete validation
Type-Specific Validation: Check if integer is needed vs. any numeric
Error Messages: Provide helpful guidance when validation fails

Function	Purpose	Returns	Use Case
`IsNumeric`	Check if numeric	`Boolean`	Validate numeric data
`IsDate`	Check if date	`Boolean`	Validate date data
`IsNull`	Check if `Null`	`Boolean`	Detect `Null` values
`IsEmpty`	Check if uninitialized	`Boolean`	Detect `Empty` Variants
`VarType`	Get variant type	`Integer`	Detailed type information
`TypeName`	Get type name	`String`	Type name as string
`Val`	Extract number	`Double`	Convert string to number (partial)

`IsNumeric` vs `Val` Function

Dim text As String

text = "123"
Debug.Print IsNumeric(text)    ' True
Debug.Print Val(text)          ' 123

text = "123abc"
Debug.Print IsNumeric(text)    ' False - not fully numeric
Debug.Print Val(text)          ' 123 - extracts leading numbers

text = "abc123"
Debug.Print IsNumeric(text)    ' False
Debug.Print Val(text)          ' 0 - no leading numbers

' IsNumeric is stricter - entire expression must be numeric
' Val extracts numeric portion from beginning of string

Platform and Version Notes

Available in all VB6 versions
Part of VBA core functions
Returns Boolean type
Locale-dependent for decimal separators and currency
Recognizes hexadecimal (&H) and octal (&O) notation
Date/Time values return True
Boolean values return True

Limitations

Does not distinguish between integer and floating-point capable values
Does not check if value fits in target type (Integer, Long, etc.)
Locale-dependent behavior can cause issues across regions
Cannot validate specific numeric formats (phone numbers, SSN, etc.)
Scientific notation may not be recognized in all locales
Does not validate reasonable ranges for specific use cases

IsDate: Check if expression can be converted to a date
VarType: Get detailed Variant type information
TypeName: Get type name as string
Val: Extract numeric value from string (different behavior)
CDbl, CLng, CInt: Convert to specific numeric types

IsNumeric Function