Numeric Tools:

To test if a Variant Object is Numeric use:

• bIsNumeric (Returns Boolean)

To convert a Variant value TO Numeric use:

• ConvertTestToNumber (Returns Double - ok to use for Currency)
  

To Test a Variant Object and return Data Type: [Blank][Numeric][Alpha][Mixed]

• IdentifyStringType(Returns String)
  

---

bIsNumeric(Variant) as Boolean

Function bIsNumeric(myString As Variant) As Boolean
Dim iLoop As Long

'Set Defaults:
myString = Trim(myString)
bIsNumeric = True

'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV
'Validate: Ensure that there is a valid year entered before proceding
sNumberTest = "0123456789"

    'Set Boolean to False if value to test is blank
    If myString = "" Then GoTo SetBooleanToFalse

    'Set Boolean to False if length of string is 0 (overkill - should already be re-directed if "")
    If Len(myString) = 0 Then GoTo SetBooleanToFalse
    
    'Loop through each character in the text box - test to make sure it is numeric
    For iLoop = 1 To Len(myString)
        If InStr(1, sNumberTest, Mid(myString, iLoop, 1), vbTextCompare) = 0 Then GoTo SetBooleanToFalse
    Next iLoop
'VVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVVV
Exit Function

SetBooleanToFalse:
    'One of the tests eliminated this value as a valid numeric value.  Set it to false
    bIsNumeric = False
    Exit Function
End Function

---

ConvertTextToNumber(Variant) as Double

Function ConvertTextToNumber(myString As Variant) As Double
'Converts ANY string to a number by multiplying by 1.  Will return [0] when ANY error is encountered.
ConvertTextToNumber = 0
On Error GoTo ThisDidntWorkSoDoNotConvert

ConvertTextToNumber = myString * 1

ThisDidntWorkSoDoNotConvert:
End Function

---

IdentifyStringType(Variant) as String

Function IdentifyStringType(vMyInput As Variant) As String
Dim iLoop As Long, iLenPre As Long, iLenPost As Long, sTemp As String
    'Load the string and take a snapshot of the length
    sTemp = vMyInput
    iLenPre = Len(sTemp)
  
    'Skip the review process for 0 length strings
    If iLenPre = 0 Then GoTo gNoString
  
    'Loop through all 10 digits and remove them from the string
    For iLoop = 0 To 9
        sTemp = Replace(sTemp, iLoop, "", , , vbTextCompare)
    Next iLoop

    'Take a snapshot of the length AFTER replacement of numeric values
    iLenPost = Len(sTemp)
  
    'Mark "Numeric" if there are no Alpha Characters
    If iLenPost = 0 Then GoTo gAllNumbers
  
    'Mark "Alpha" if there are no Numeric Characters
    If iLenPre = iLenPost Then GoTo gAllAlpha
  
    'If it gets this far, the string is a mix of ALPHA + NUMERIC
    GoTo gMixed
  
'------------------------------------------------------------------------
gNoString:
IdentifyStringType = "Blank"
Exit Function
'------------------------------------------------------------------------
gAllNumbers:
IdentifyStringType = "Numeric"
Exit Function
'------------------------------------------------------------------------
gAllAlpha:
IdentifyStringType = "Alpha"
Exit Function
'------------------------------------------------------------------------
gMixed:
IdentifyStringType = "Mixed"
Exit Function
'------------------------------------------------------------------------
End Function