How to check LEI code

Question

I am writing a macro to verify LEI codes.

LEI Code:

• 20 char string
• The first 18 characters are alphanumeric
• Last two characters

Example: F50EOCWSQFAUVO9Q8Z97

Check

• Replace all alphabetic characters in a 2-digit number string to create an integer
• If mod of integer divided by 97 is 1, then the code is valid, otherwise invalid

I wrote the following:

Sub Test() Dim LEI_String As String LEI_String = Range("B1") LEI_String = Replace(LEI_String, "A", "10") LEI_String = Replace(LEI_String, "B", "11") LEI_String = Replace(LEI_String, "C", "12") LEI_String = Replace(LEI_String, "D", "13") LEI_String = Replace(LEI_String, "E", "14") LEI_String = Replace(LEI_String, "F", "15") LEI_String = Replace(LEI_String, "G", "16") LEI_String = Replace(LEI_String, "H", "17") LEI_String = Replace(LEI_String, "I", "18") LEI_String = Replace(LEI_String, "J", "19") LEI_String = Replace(LEI_String, "K", "20") LEI_String = Replace(LEI_String, "L", "21") LEI_String = Replace(LEI_String, "M", "22") LEI_String = Replace(LEI_String, "N", "23") LEI_String = Replace(LEI_String, "O", "24") LEI_String = Replace(LEI_String, "P", "25") LEI_String = Replace(LEI_String, "Q", "26") LEI_String = Replace(LEI_String, "R", "27") LEI_String = Replace(LEI_String, "S", "28") LEI_String = Replace(LEI_String, "T", "29") LEI_String = Replace(LEI_String, "U", "30") LEI_String = Replace(LEI_String, "V", "31") LEI_String = Replace(LEI_String, "W", "32") LEI_String = Replace(LEI_String, "X", "33") LEI_String = Replace(LEI_String, "Y", "34") LEI_String = Replace(LEI_String, "Z", "35") MsgBox Len(LEI_String) Range("B2").Value = CCur(LEI_String) Mod 97 MsgBox CCur(LEI_String) Mod 97 End Sub 

And of course, I get 6 overflow errors at runtime, since the integer I'm working with is 35 digits.

Is there any way around this?

Answer 1

The LEI number is too large to match the decimal type. To get the module, you will have to calculate modulo each digit after converting to base 10:

 Private Sub Test() Debug.Print IsValidLEI("F50EOCWSQFAUVO9Q8Z97") ' >> True ' Debug.Print IsValidLEI("T50EOCWSQFAUVO9Q8Z97") ' >> False ' End Sub Public Function IsValidLEI(lei As String) As Boolean Dim i As Long, c As Long, m As Long For i = 1 To Len(lei) ' each character ' c = AscW(Mid(lei, i, 1)) ' get the character code (see ASCII table) ' Select Case c Case 48 To 57 ' 0-9 -> 0-9 ' m = (m * 10 + c - 48) Mod 97 ' x10 to shift 1 digit, -48 to convert to base10 ' Case 65 To 90 ' AZ -> 10-35 ' m = (m * 100 + c - 55) Mod 97 ' x100 to shift 2 digits, -55 to convert to base10 ' Case Else Err.Raise 5, , "Unexpected character at " & i End Select Next IsValidLEI = m = 1 End Function 

Answer 2

 Sub Test() Dim LEI_String As String LEI_String = Range("B1") LEI_String = Replace(LEI_String, "A", "10") LEI_String = Replace(LEI_String, "B", "11") LEI_String = Replace(LEI_String, "C", "12") LEI_String = Replace(LEI_String, "D", "13") LEI_String = Replace(LEI_String, "E", "14") LEI_String = Replace(LEI_String, "F", "15") LEI_String = Replace(LEI_String, "G", "16") LEI_String = Replace(LEI_String, "H", "17") LEI_String = Replace(LEI_String, "I", "18") LEI_String = Replace(LEI_String, "J", "19") LEI_String = Replace(LEI_String, "K", "20") LEI_String = Replace(LEI_String, "L", "21") LEI_String = Replace(LEI_String, "M", "22") LEI_String = Replace(LEI_String, "N", "23") LEI_String = Replace(LEI_String, "O", "24") LEI_String = Replace(LEI_String, "P", "25") LEI_String = Replace(LEI_String, "Q", "26") LEI_String = Replace(LEI_String, "R", "27") LEI_String = Replace(LEI_String, "S", "28") LEI_String = Replace(LEI_String, "T", "29") LEI_String = Replace(LEI_String, "U", "30") LEI_String = Replace(LEI_String, "V", "31") LEI_String = Replace(LEI_String, "W", "32") LEI_String = Replace(LEI_String, "X", "33") LEI_String = Replace(LEI_String, "Y", "34") LEI_String = Replace(LEI_String, "Z", "35") MsgBox Len(LEI_String) Range("B2").Value = Round(LEI_String / 97, 0) MsgBox Round(LEI_String / 97, 0) End Sub