Home > Excel, Financial, UDF > ISIN Code UDF

ISIN Code UDF

May 17th, 2011 admin

April 2nd, 2008

In my daily job validating security codes is a must.
So I wrote a UDF for validating ISINCodes.
There are no comments in the code, sorry for that…. maybe later…. enjoy..


Public Function ISINCODE(ByVal sISINCode As String) As Boolean

   Dim i As Integer: Dim iTotalScore As Integer
   Dim s As String: Dim sDigits As String

   sISINCode = UCase(Trim(sISINCode))

   If Len(sISINCode) <> 12 Then Exit Function

   If MID(sISINCode,1,1) < "A" Or MID(sISINCode,1,1) > "Z" Then Exit Function
   If MID(sISINCode,2,1) < "A" Or MID(sISINCode,2,1) > "Z" Then Exit Function

   sDigits = ""

   For i = 1 To 11
      s = Mid(sISINCode, i, 1)
      If s &gt;= "0" And s &lt;= "9" Then
         sDigits = sDigits &amp; s
      ElseIf s &gt;= "A" And s &lt;= "Z" Then
         sDigits = sDigits &amp; CStr(Asc(s) - 55)
      Else
         Exit Function
      End If
   Next i

   sDigits = StrReverse(sDigits)

   iTotalScore = 0

   For i = 1 To Len(sDigits)
      iTotalScore = iTotalScore + CInt(Mid(sDigits, i, 1))
      If i Mod 2 = 1 Then
         iTotalScore = iTotalScore + CInt(Mid(sDigits, i, 1))
         If CInt(Mid(sDigits, i, 1)) &gt; 4 Then
            iTotalScore = iTotalScore - 9
         End If
      End If
   Next i

   If (10 - (iTotalScore Mod 10)) Mod 10 = CInt(Mid(sISINCode, 12, 1)) Then
      ISINCODE = True
   End if

End Function
Categories: Excel, Financial, UDF Tags: , ,
Comments are closed.