Contents

ValidISIN function

This article describes the formula syntax and usage of the ValidISIN user defined function in Microsoft Excel.

NOTE: This function requires that the VBA code be implemented in the target workbook, or be contained in an addin. If contained in an addin, all parties to whom the code is distributed must also have the addin installed.

Description

Returns a boolean result (True or False), indicating whether or not a given String passes the International Securities Identification Number (ISIN) code pattern and Luhn algorithm. You can use this function either as a worksheet function (formula) and/or call it from VBA.

NOTE: This function does not validate the country prefix against the list of ISO recognised countries – nor does it provide any further validation of the codes authenticity.

Syntax

ValidISIN(ISIN)

The ValidISIN function has the following arguments:

strISIN: Required. The International Securities Identification Number that you want to validate. This argument can be passed as a String Constant, or reference a cell range containing the ISIN.

Remarks

Returns True if strISIN passes the validation check, otherwise returns False.
If called as a worksheet function, returns #VALUE! if strISIN is an error value (e.g. #N/A, #REF!, #VALUE! etc.)
If called as a VBA function, throws a Runtime Error ’13’: Type Mismatch if strISIN is an error value.

Example

Use in formula

Copy the example data in the following table, and paste it in cell A1 of a new Excel worksheet. For formulas to show results, select them, press F2, and then press Enter. If you need to, you can adjust the column widths to see all the data.

ISIN	Formula	Result
IE0000020408	=ValidISIN(A2)	TRUE
GB00B126KH98	=ValidISIN(A3)	FALSE

Use in VBA

Basic Case – validate a single ISIN

Copy this code to a standard module in your workbook and click ‘Run’ (F5).

Public Sub Demo1()
    Dim blnIsValid  As Boolean
    Const strISIN   As String = "US0378331005"
    
    blnIsValid = ValidISIN(strISIN)
    
    Call MsgBox(Prompt:=WorksheetFunction.Trim$(strISIN & " is " & IIf(blnIsValid, "", "not") & " a valid ISIN!"))
End Sub

Public Sub Demo1()

Dim blnIsValid As Boolean

Const strISIN As String = "US0378331005"

blnIsValid = ValidISIN(strISIN)

Call MsgBox(Prompt:=WorksheetFunction.Trim$(strISIN & " is " & IIf(blnIsValid, "", "not") & " a valid ISIN!"))

End Sub

Practical Case – Validate a table of ISIN’s

Input a series of ISIN codes into Sheet1 range A1:A10. Copy this code to a standard module in your workbook and click ‘Run’ (f5). The result will be a message box for each ISIN that is NOT valid.

Public Sub Demo2()
    Dim rngISINs    As Excel.Range
    Dim rngCell     As Excel.Range
    
    Set rngISINs = Sheet1.Range("A1:A10")

    For Each rngCell In rngISINs
        If Not ValidISIN(rngCell.Text) Then
            Call MsgBox(rngCell.Text & " is not a valid ISIN!")
        End If
    Next rngCell
End Sub

Public Sub Demo2()

Dim rngISINs As Excel.Range

Dim rngCell As Excel.Range

Set rngISINs = Sheet1.Range("A1:A10")

For Each rngCell In rngISINs

If Not ValidISIN(rngCell.Text) Then

Call MsgBox(rngCell.Text & " is not a valid ISIN!")

End If

Next rngCell

End Sub

Installation

Option Explicit
Option Private Module

Public g_objREGEX       As Object

Public Function ISINPattern(ByVal strISIN As String) As Boolean
    If g_objREGEX Is Nothing Then
        Set g_objREGEX = CreateObject("vbscript.regexp")
    End If

    With g_objREGEX
        .IgnoreCase = False
        .Pattern = "^[A-Z]{2}[A-Z0-9]{9}[0-9]{1}$"
    End With

    ISINPattern = g_objREGEX.Test(strISIN)
End Function

Public Function CharToDigit(ByVal strSecurity As String) As Variant
    Dim objMatch            As Object
    Dim objMatches          As Object
    Dim strStagingString    As String

    If g_objREGEX Is Nothing Then
        Set g_objREGEX = CreateObject("vbscript.regexp")
    End If

    With g_objREGEX
        .Global = True
        .IgnoreCase = False
        .Pattern = "([A-Z])"
    End With

    strStagingString = Left$(StrConv(strSecurity, vbUnicode), Len(strSecurity) * 2 - 1)

    Set objMatches = g_objREGEX.Execute(strStagingString)

    For Each objMatch In objMatches
        strStagingString = Replace$(strStagingString, objMatch.Value, Asc(objMatch.Value) - 55)
    Next objMatch

    CharToDigit = Split(strStagingString, Chr$(0))
End Function

Option Explicit

Option Private Module

Public g_objREGEX As Object

Public Function ISINPattern(ByVal strISIN As String) As Boolean

If g_objREGEX Is Nothing Then

Set g_objREGEX = CreateObject("vbscript.regexp")

End If

With g_objREGEX

.IgnoreCase = False

.Pattern = "^[A-Z]{2}[A-Z0-9]{9}[0-9]{1}$"

End With

ISINPattern = g_objREGEX.Test(strISIN)

End Function

Public Function CharToDigit(ByVal strSecurity As String) As Variant

Dim objMatch As Object

Dim objMatches As Object

Dim strStagingString As String

If g_objREGEX Is Nothing Then

Set g_objREGEX = CreateObject("vbscript.regexp")

End If

With g_objREGEX

.Global = True

.IgnoreCase = False

.Pattern = "([A-Z])"

End With

strStagingString = Left$(StrConv(strSecurity, vbUnicode), Len(strSecurity) * 2 - 1)

Set objMatches = g_objREGEX.Execute(strStagingString)

For Each objMatch In objMatches

strStagingString = Replace$(strStagingString, objMatch.Value, Asc(objMatch.Value) - 55)

Next objMatch

CharToDigit = Split(strStagingString, Chr$(0))

End Function

Option Explicit
Option Base 0

Public Function ValidISIN(ByVal strISIN As String) As Boolean
    Dim lngCheckNum         As Long
    Dim varNums             As Variant
    Dim lngChar             As Long
    Dim lngResult           As Long

    strISIN = Trim$(UCase$(strISIN))

    If Not ISINPattern(strISIN) Then Exit Function

    lngCheckNum = CLng(Right$(strISIN, 1))

    varNums = Join$(CharToDigit(Left$(strISIN, 11)), "")
    varNums = Split(Left(StrConv(varNums, vbUnicode), Len(varNums) * 2 - 1), Chr$(0))

    For lngChar = LBound(varNums) + UBound(varNums) Mod 2 To UBound(varNums) Step 2
        varNums(lngChar) = varNums(lngChar) * 2
    Next lngChar
    
    varNums = Join$(Split(StrConv(Join$(varNums, ""), vbUnicode), Chr$(0)), ",")

    lngResult = (10 - (Evaluate("sum({" & Left$(varNums, Len(varNums) - 1) & "})") Mod 10)) Mod 10

    ValidISIN = CBool(lngCheckNum = lngResult)
End Function

Option Explicit

Option Base 0

Public Function ValidISIN(ByVal strISIN As String) As Boolean

Dim lngCheckNum As Long

Dim varNums As Variant

Dim lngChar As Long

Dim lngResult As Long

strISIN = Trim$(UCase$(strISIN))

If Not ISINPattern(strISIN) Then Exit Function

lngCheckNum = CLng(Right$(strISIN, 1))

varNums = Join$(CharToDigit(Left$(strISIN, 11)), "")

varNums = Split(Left(StrConv(varNums, vbUnicode), Len(varNums) * 2 - 1), Chr$(0))

For lngChar = LBound(varNums) + UBound(varNums) Mod 2 To UBound(varNums) Step 2

varNums(lngChar) = varNums(lngChar) * 2

Next lngChar

varNums = Join$(Split(StrConv(Join$(varNums, ""), vbUnicode), Chr$(0)), ",")

lngResult = (10 - (Evaluate("sum({" & Left$(varNums, Len(varNums) - 1) & "})") Mod 10)) Mod 10

ValidISIN = CBool(lngCheckNum = lngResult)

End Function

ISIN Validation (VBA Function)

ValidISIN function

Description

Syntax

Remarks

Example

Use in formula

Use in VBA

Installation

References

One Comment