How to validate a LEI code

Question

I am writing a Macro to validate LEI codes.

LEI Code is:

1. 20 char string
2. First 18 chars alphanumeric
3. Last two chars numeric

Example: F50EOCWSQFAUVO9Q8Z97

Validation

1. Replace all alphabetical chars in string with 2 digit number to create integer
2. If mod of integer divided by 97 is 1 then code valid, else invalid

I have written the below:

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 am of course getting run-time error 6 overflow errors as the integer I am working with is 35 digits long.

Is there a way of working around this?

Answer 1

The LEI number is too big to fit in a Decimal type. To get the modulo, you'll have to compute the modulo on each digit once converted 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 ' A-Z -> 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