Custom VBA Function to Convert to Base62

Question

I'm trying to create a function in VBA that will encode a number into base62. If the encoded base62 string is less than 6 total characters, the functions pads the front of the string with zeros.

Function Base62Encode(ByVal num As Variant) As String
    Dim base62_chars As String
    base62_chars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
    Dim base62 As String
    base62 = ""
    
    ' Encode using base62 conversion
    Do While num > 0
        base62 = Mid(base62_chars, (num Mod 62) + 1, 1) & base62
        num = Int(num / 62)
    Loop
    
    ' Pad with leading zeros to ensure 6-character length
    Base62Encode = String(6 - Len(base62), "0") & base62
End Function

The function works perfectly fine for all integers I pass to the function until I go past 2,147,483,647—then I get an error. I'm definitely a novice at vba, but it seems like the error has something to do with the Long integer data type range limit but I'm not sure how I can get around that problem.

I tried converting values throughout the function to the decimal data type as well, but I am still getting the error (which appear as a #VALUE! error in Excel)

Thanks in advance!

Answer 1

• As per the MS documentation, both arguments should be 'any numeric expression.' It does not explicitly state whether large number (eg. LongLong) is supported.

Microsoft documentation:

Mod operator

• It seems this is a limitation of the Mod operator. Please try using a express with Int function as a replacement. (Thanks for @Gserg's comment)

Function Base62Encode(ByVal num As Variant) As String
    Dim base62_chars As String
    base62_chars = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz"
    Dim base62 As String
    base62 = ""
    ' Encode using base62 conversion
    Do While num > 0
'        base62 = Mid(base62_chars, (num Mod 62) + 1, 1) & base62
        base62 = Mid(base62_chars, (num - Int(num / 62) * 62) + 1, 1) & base62
        num = Int(num / 62)
    Loop
    ' Pad with leading zeros to ensure 6-character length
    Base62Encode = String(6 - Len(base62), "0") & base62
End Function

Sub Test()
    Dim i As Double
    For i = 2147483647 To 2147483650#
        Debug.Print i, Base62Encode(i)
    Next
End Sub

Output:

 2147483647   2LKcb1
 2147483648   2LKcb2
 2147483649   2LKcb3
 2147483650   2LKcb4