Table of Contents
IsNumeric Function (VB Work-A-Like)
This function is inspired by the VB function of the same name. It is smaller in scope in that only simple numeric notation is supported. It will test validity for both integer (opt=0 or absent) and decimal number strings (opt<>0).
It is intended for use where an arbitrary input must be forced into numeric compliance. It does not sanitize the string (out of scope for a small building block), rather providing an indication if the argument is/not compliant.
Usage:
=IsNumeric(MyString,Option)
Examples:
x=IsNumeric(“345”) If Not IsNumeric(a$,1) Then Print “Not a Number”
Caveats:
The string cannot be blank “” No support for scientific notation e.g. 1.78E+34 The Function tests the input string for numeric compliance, it does not guarantee conversion to any numeric data-type, i.e. Val(). White space must be pre-trimmed as required.
Dependencies:
None
The Code:
'Test a string as numeric. opt=1 allows decimals Function IsNumeric(a$,opt As Integer) As Integer If a$="" Then Exit Function Local Integer n,Dots Local q$,c$ If opt Then q$=" 0123456789." Else q$=" 0123456789" For n=1 To Len(a$) c$=Mid$(a$,n,1) If Instr(q$,c$)<2 Then Exit Function Dots=Dots+(c$=".") Next IsNumeric=(Dots<=1) End Function
The following is an alternative function that uses regular expressions to perform an identical function. The overhead of the regexp parser is considerable and this version is probably best avoided unless you are using regular expressions elsewhere in your code.
Dependencies:
Regular expressions.
Caveats:
Much smaller function but slower execution due to overhead of regular expressions. A good companion if you are already using regexps (IsDate/IsTime) in your code
The Code:
'Test a string as numeric using regular expressions. opt=1 allows decimals Function IsNumeric(a$,opt As Integer) As Integer If a$="" Then Exit Function Local q$ If opt Then q$="^[0123456789]*\.?[0123456789]*$" Else q$="^[0123456789]*$" IsNumeric=RegExp(q$,a$) End Function