How to test for Large Number

[tvanstiphout]

The Large Number data type is not very well supported in VBA. FWIW, I am using 32-bit Access 365, with support for large numbers checked.
Assuming I have a string value and I want to test if it is a valid Large Number, how would I do it?
Large numbers pass IsNumeric - good, but the below technique where I test for Long Integer cannot be used. What would be a good alternative?

Public Function IsLongIntegerValue(ByVal varValue As Variant) As Boolean
On Error Resume Next
Dim blnReturn As Boolean
blnReturn = (CLng(varValue) = varValue)
blnReturn = (Err.Number = 0)
IsLongIntegerValue = blnReturn
End Function

 

[MajP]

From copilot

' ========= Module: BigIntValidate.bas =========
Option Explicit

' Public entry point:
' Returns True if s represents a valid signed 64-bit integer (Access Large Number / BigInt)
Public Function IsValidLargeNumber(ByVal s As String) As Boolean
    Dim sign As Integer, digits As String

    ' Normalize whitespace
    s = Trim$(s)
    If Len(s) = 0 Then
        IsValidLargeNumber = False
        Exit Function
    End If

    ' Extract sign and digits
    sign = GetSign(s)
    digits = StripSign(s)

    ' Must be all digits and not empty
    If Len(digits) = 0 Or Not IsAllDigits(digits) Then
        IsValidLargeNumber = False
        Exit Function
    End If

    ' Remove leading zeros for magnitude comparison
    digits = StripLeadingZeros(digits)

    ' Zero is always valid
    If digits = "0" Then
        IsValidLargeNumber = True
        Exit Function
    End If

    ' Compare magnitude against max/min 64-bit bounds
    If sign >= 0 Then
        IsValidLargeNumber = MagnitudeLE(digits, "9223372036854775807")
    Else
        ' For negative, compare to absolute of min bound (note: min has larger magnitude)
        IsValidLargeNumber = MagnitudeLE(digits, "9223372036854775808")
    End If
End Function

' -------- Helpers --------

Private Function GetSign(ByVal s As String) As Integer
    ' Returns +1 for positive/+, -1 for negative/-, 0 for invalid sign char
    If Left$(s, 1) = "-" Then
        GetSign = -1
    ElseIf Left$(s, 1) = "+" Then
        GetSign = 1
    Else
        GetSign = 1 ' default positive
    End If
End Function

Private Function StripSign(ByVal s As String) As String
    If Left$(s, 1) = "-" Or Left$(s, 1) = "+" Then
        StripSign = Mid$(s, 2)
    Else
        StripSign = s
    End If
End Function

Private Function StripLeadingZeros(ByVal s As String) As String
    Dim i As Long
    i = 1
    Do While i < Len(s) And Mid$(s, i, 1) = "0"
        i = i + 1
    Loop
    If i > Len(s) Then
        StripLeadingZeros = "0"
    Else
        StripLeadingZeros = Mid$(s, i)
    End If
End Function

Private Function IsAllDigits(ByVal s As String) As Boolean
    Dim i As Long, ch As String
    If Len(s) = 0 Then
        IsAllDigits = False
        Exit Function
    End If
    For i = 1 To Len(s)
        ch = Mid$(s, i, 1)
        If ch < "0" Or ch > "9" Then
            IsAllDigits = False
            Exit Function
        End If
    Next i
    IsAllDigits = True
End Function

' Returns True if numeric string a <= numeric string b (no sign, no leading zeros required)
Private Function MagnitudeLE(ByVal a As String, ByVal b As String) As Boolean
    ' First by length, then lexicographic
    If Len(a) < Len(b) Then
        MagnitudeLE = True
    ElseIf Len(a) > Len(b) Then
        MagnitudeLE = False
    Else
        MagnitudeLE = (a <= b)
    End If
End Function
``

 

[DaveMD]

I've dealt with this type of thing before with very large numbers. There is an easy but not well documented solution. Change your CLng() cast to Fix(). Fix can handle working with Doubles (8 byte numbers) while CLng is limited to 4 byte numbers which is why it errors out on large numbers.

Public Function IsLongIntegerValue(ByVal varValue As Variant) As Boolean
    Dim blnReturn As Boolean
    blnReturn = (Fix(varValue) = varValue)
    IsLongIntegerValue = blnReturn
End Function

 

[tvanstiphout]

Ha! I thought of doing something like your MagnitudeLE function, because "123" is less than "456", as a string comparison.
Thank you very much.

 

[tvanstiphout]

I've dealt with this type of thing before with very large numbers. There is an easy but not well documented solution. Change your CLng() cast to Fix(). Fix can handle working with Doubles (8 byte numbers) while CLng is limited to 4 byte numbers which is why it errors out on large numbers.

Public Function IsLongIntegerValue(ByVal varValue As Variant) As Boolean
    Dim blnReturn As Boolean
    blnReturn = (Fix(varValue) = varValue)
    IsLongIntegerValue = blnReturn
End Function

I thought about the similar CDec, which does indeed work at least for some large numbers, but I like the exact solution by MajP even better.