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.

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

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

I would recommend you to read that article first.

# Random BSN Number Generator in Excel using VBA # 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.

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
`````` 