ISIN Validation (VBA Function)

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.

ISINFormulaResult
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).

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.

Installation

References

One Comment

  1. Pingback: Understanding/using RegExp for string processing in VBA

Comments are closed