Dear Friends,

In one of my previous article, I explained you about Netherlands BSN Number validation. This used to be called as SoFi nummer (Short form of dutch word – Sociaalfiscaal nummer) as well in the Past. Here in this article, I am sharing VBA code and a FREE downloadable excel to generate random BSN numbers.
At the end of this article, you will find a download link to download the tool – Random BSN Generator Tool – Excel.

In the previous article, I had shared in detail about the followings:

[fullwidth background_color=”#fff4f4″ background_image=”” background_parallax=”none” parallax_speed=”0.1″ enable_mobile=”no” background_repeat=”no-repeat” background_position=”left top” video_url=”” video_aspect_ratio=”16:9″ video_webm=”” video_mp4=”” video_ogv=”” video_preview_image=”” overlay_color=”” overlay_opacity=”0.5″ video_mute=”yes” video_loop=”yes” fade=”no” border_size=”1px” border_color=”#ff8e8e” border_style=”solid” padding_top=”20″ padding_bottom=”20″ padding_left=”20″ padding_right=”20″ hundred_percent=”no” equal_height_columns=”no” menu_anchor=”” class=”” id=””]

What is Elfproef or 11-Check – Specific to BSN

What is BSN Number

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

A downloadable excel tool – BSN Validator – Function and UDF both

[/fullwidth]

I would recommend you to read that article first.

Random BSN Number Generator in Excel using VBA

Now since you already know enough about BSN number, here I am going to share with you a VBA code which can help you in generating a random valid BSN Number. I have also create a downloadable excel tool to generate the Random BSN number which can be downloaded from the link mentioned at the end of this article. Here is a screen shot of the tool:

What is the Use of this Code / Tool?

For testing any application which uses a BSN number as an input, every time you need a valida BSN number handy to use it. This simple VBA code can be used to achieve this.
You can ofcourse get it generated from Internet. There are websites which generates a random BSN number every time you press a button.
This excel tool, also does the same thing.

Advantages

1. For this to work you do not need to be connected to Internet.
2. If you are capturing your Test data or test cases in Excel, you don’t have to generate it from some website and put it in your excel sheet.

Logic used behind generating this Random BSN number

It consists of two functions.
1. Function to generate a Random 9 digit Number between 100000000(lowest 9 digit number) and 999999999 (highest 9 digit number).

2. Function to validate whether a 9 digit number is a valid BSN number or not?

After generating every random number, first function call the second function to validate if that random number is passing the elfCheck or it is a valid BSN number.
It keeps on generating random number, until it finds a random number which passes the BSN check.
As soon as a random number passes a BSN check, it comes out of the loop and displays that Random number as a next random valid BSN number.
This random number you can capture in a message box, text box, in a cell or how ever you want to use.

Here is the VBA code for Generating Valid BSN Number

VBA Function to generate 9 digit Random Number

Sub generateRandomBSNNumber()
' Function to generate a random 9 digit number
    Dim rndNumber As Double
    Dim lowLimit As Double
    Dim highLimit As Double
    lowLimit = 100000000 ' lowest 9 digit number
    highLimit = 999999999 ' highest 9 digit number
    Do
' generate a 9 digit Random number
        rndNumber = Int((highLimit - lowLimit + 1) * Rnd() + lowLimit)
' loop until random generated number is a valid BSN
    Loop Until isValidBSN(CStr(rndNumber)) = True
' store the random number which
' passes the elfCheck
    Range("bsn").Value = rndNumber
End Sub

VBA Function to check if given number is a Valid BSN Number

This is the same function which I have explained in detail in this article which I mentioned above.

Function isValidBSN(bsn As String) As Boolean
        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
End Function

Download – Random BSN Number Generator – Excel Tool

Here is your Excel tool to download. Do not forget to provide your feedback 🙂

Random BSN Number Generator