Important Note: This post will make more sense to those who stays in Netherlands.

**But wait.. even if you do not stay in Netherlands, you will learn some interesting Algorithm!!**

BSN is short form of Dutch Word – B*urgerServiceNummer . *In English, this is called Citizen Service Number. This is a unique Personal Identification Number assigned by Netherlands Govt. This was introduced since 2007. To know more about this you can read this wiki page or you can refer the NL government website about it. *This is a 9 digit number which should corresponds to 11-check or in dutch Elfproef*

## What is Elfproef or 11-Check - Standard

*Weighted Number is the position number of the digit in number from right to Left direction.*

**Example:**

Check if **614961122** is a valid 11-check number**Total sum** = 6 * **9** + 1***8** + 4***7** + 9 ***6** + 6***5** + 1***4** + 1***3** + 2***2** + 2***1 = 187**

Check if Total sum is divisible by 11 => 187 / 11 = 17 ( Remainder = 0)

This means **614961122** passes the 11-check standard.

# What is Elfproef or 11-Check – Specific to BSN

A variant of standard Elfproef, is used for BSN. There is small change in the standard. Weighted value for the last digit from Left to Right is changed from 1 to **-1** . That’s all the deviation from the standard 11-Check.**Example:**

Check if **614961122** is a valid 11-check number**Total sum** = 6 * **9** + 1***8** + 4***7** + 9 ***6** + 6***5** + 1***4** + 1***3** + 2***2** + 2***(-1) = 183**

Check if Total sum is divisible by 11 => 183 / 11 = 17 ( Remainder = 7)

This means **614961122** is not a VALID BSN Number of Netherlands

To do this validation easily in Excel, I created a Excel VBA function using above Algorithm, to validate if a given number is a valid BSN number of Netherlands or not.**Note:** With validity, I do not mean, if this is a real existing BSN number or not, but whether that number qualifies the **Elfproef** or 11-check.

# VBA Code to do 11-Check (Elfproef) for BSN

Function IsValidBSN(bsn As String) As Boolean 'first check if it has 9 digit If VBA.Len(bsn) = 9 Then Dim totalSum totalSum = 0 ' Follow standard 11-check algorithm till the ' second last digit of the 9 digit number For i = 9 To 2 Step -1 totalSum = totalSum + CInt(VBA.Mid(bsn, 10 - i, 1)) * i Next ' Weighted multiplication factor for ' the last digit is (-1) totalSum = totalSum + (CInt(VBA.Mid(bsn, 10 - i, 1))) * (-1) '11-Check true if divisible by 11 If (totalSum Mod 11) = 0 Then IsValidBSN = True Else IsValidBSN = False Else IsValidBSN = False ' Not a valid BSN, if length is &amp;amp;lt;&amp;amp;gt; 9 Digits End If End Function

# What is the use of this function ?

This is the most important question about any thing you do… What would you do with this? What is the use of it? So this function can be used for checking if entered BSN number is a valid BSN number or not. There are companies or organizations which uses excel as a Data form for the users to enter their details like Name, Date of Birth, Address, BSN etc. In such form, you can implement such validation to prevent from entering an Invalid BSN number.

# How to use this function ?

This code can be used in many different ways, depending on your requirement. You can call this function on clicking on a button to validate the entered BSN number and display the result accordingly.

Another way of using this function can be — using it as a UDF (User Defined Function) or custom function. Where you can use it like a formula to check the validity of the BSN number entered by a user in a cell.

You can read more about User Defined Functions here on this page.

# Your FREE Workbook to Play around

Excel VBA code to Validate BSNAt last, your free copy of Excel workbook, which you can download and play around with code.

This workbook has example of both the usage I explained above.

## Leave A Comment