Validate BurgerServiceNummer (BSN) using Excel VBA

.

Important Note: This post will make more sense to those who stay in the Netherlands.
Also do not forget to download the BSN validator tool created at the end of the post. It is FREE to download and play around with. Thanks.

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


BSN is the short form of Dutch Word – BurgerServiceNummer . In English, this is called  Citizen Service Number. This is a unique Personal Identification Number assigned by Netherlands Govt. This was introduced in 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

In standard Check Digit or 11-Check, individual digits are multiplied by a weighted number and summed up together. If the total sum is divisible by 11. or in other words, if the total sum is a multiple of 11 then, the number passes the 11-Check or Elfproef.
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 a 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[/fusion_text][fusion_text][fusion_text]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 <> 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.

Your FREE Workbook to Play around

At last, your free copy of the Excel workbook, which you can download and play around with code.

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

BSN Validator

Excel VBA code to Validate BSN

Buy a coffee for the author

Adsense

Download FREE Tools and Templates

There are many cool and useful excel tools and templates available to download for free. For most of the tools, you get the entire VBA code base too which you can look into it, play around it, and customize according to your need.

Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide
Dynamic Arrays and Spill Functions in Excel: A Beginner’s Guide

In today's tutorial, we'll be diving into the exciting world of dynamic arrays and spill functions in Office 365 Excel. These features have revolutionized the way we work with data, providing a more flexible and efficient way to handle arrays. I am going to explain...

How to Declare a Public Variable in VBA
How to Declare a Public Variable in VBA

While programming in VBA sometimes you need to declare a Public Variable that can store the value throughout the program. Use of Public Variable: Let's say you have 4 different Functions in your VBA Code or Module and you have a variable that may or may not be...

How to Copy content from Word using VBA

As many of us want to deal with Microsoft Word Document from Excel Macro/VBA. I am going to write few articles about Word from Excel Macro. This is the first article which opens a Word Document and read the whole content of that Word Document and put it in the Active...

What is Excel Formula?

Excel Formula is one of the best feature in Microsoft Excel, which makes Excel a very very rich application. There are so many useful built-in formulas available in Excel, which makes our work easier in Excel. For all the automated work, Excel Macro is not required. There are so many automated things can be done by using simple formulas in Excel. Formulas are simple text (With a Syntax) which is entered in to the Excel Worksheet Cells. So how computer will recognize whether it is a formula or simple text? Answer is simple.. every formula in Excel starts with Equal Sign (=).

You May Also Like…

0 Comments

Trackbacks/Pingbacks

  1. Random BSN Generator - SoFiNummer Generator - Welcome to LearnExcelMacro.com - […] What is Elfproef or 11-Check – Specific to BSN […]

Submit a Comment

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Join and get a FREE! e-Book

Don't miss any articles, tools, tips and tricks, I publish here

You have Successfully Subscribed!

Pin It on Pinterest