# Create a Randomized Dataset [Excel VBA]

Note: This webpage is designed to accompany the Youtube video posted above. The video offers detailed explanation on all topics; while this webpage will serve as a repository for the code presented in the video.

In this project, we are going to look at how to create a rich dataset at the push of a button using the Random function in VBA.

While the use-case of this project might be rather specific, there are a lot of creative data manipulation tricks showcased here, that might be worth your while.

We will be mainly, using dictionary, custom function, Chr function and the Random function.

## Premise

We want to create a dataset of 5000 open or unpaid invoices from the past 2 years. There are some rules that we want to follow. But, for the most part, we are going to use the Random function in some form to help generate each field.

## The Random Function

As per the Microsoft docs’ page, The Rnd function returns a value less than 1 but greater than or equal to zero.

More accurately though, we usually get a decimal number between 0 and 1. And occasionally, we will get 0.

Code to use the vanilla Rnd function.

``````Sub checkRandomFunction()
Debug.Print Rnd()
End Sub
``````

We can use this to generate valid random numbers. But, for a more realistic scenario, lets bound this number within certain limits. Let’s restrict the lower bound to 10000 and upper bound to 19999. The formula to do this is:

``Int(LowerBound + Rnd * (UpperBound – LowerBound + 1))``

Lets, replace the Lower Bound with 10000 and the Upper Bound with 19999

``````Sub checkRandomFunction()
Debug.Print Int(10000 + Rnd * (19999 - 10000 + 1))
End Sub
``````

Awesome. We are actually, going to use this exact code, when we generate our invoice numbers.

Let’s now, generate random Upper Case alphabets that we will use in our alpha numeric strings.

For this, we need the (character) Chr Function. This function returns a character associated with a certain code. For the full list of codes, lets go to the Microsoft docs page.

Uppercase alphabets start from code 65 through to 90.

So, our lower bound will be 65 and upper bound will be 90.

Let’s feed this into our function.

``````

Sub checkRandomFunction()
Debug.Print Chr(Int(65 + Rnd * (90 - 65 + 1)))
End Sub``````

Works well. And that’s all the basics that we need to know.

Below is the full code for the module presented in the above Youtube video.

## Main Code

``````Sub GenerateRandomData()

wsUpInv.Range("A2:I" & wsUpInv.Rows.Count).Clear
wsUpInv.Columns("A").NumberFormat = "dd-mm-yyyy"
wsUpInv.Columns("B:F").NumberFormat = "General"
wsUpInv.Columns("G").NumberFormat = "@"
wsUpInv.Columns("H:I").NumberFormat = "General"

Dim RandomNumber As Long, RandomValue As String
Dim initialDate As Date

Dim dicInvCRM As Object
Set dicInvCRM = CreateObject("Scripting.Dictionary")
Dim uniqueCRM As Boolean

Dim lrow As Long
lrow = wsRndList.Cells(wsRndList.Cells.Rows.Count, 1).End(xlUp).Row
Dim arrStore() As Variant
arrStore = wsRndList.Range("A2:A" & lrow).Value

Dim dicInvERP As Object
Set dicInvERP = CreateObject("Scripting.Dictionary")
Dim UniqueERP As Boolean

Dim arrSource() As Variant
lrow = wsRndList.Cells(wsRndList.Cells.Rows.Count, 2).End(xlUp).Row
arrSource = wsRndList.Range("B2:B" & lrow).Value

Dim arrCRMRef() As Variant
lrow = wsRndList.Cells(wsRndList.Cells.Rows.Count, 3).End(xlUp).Row
arrCRMRef = wsRndList.Range("C2:C" & lrow).Value

Dim i As Long

For i = 2 To 5001
'   Col1 = Date
wsUpInv.Range("A" & i).Value = DateAdd("d", GetRandomNumber(0, 730), initialDate)
'   Col2 = CRM Invoice Number
Do Until uniqueCRM = True
RandomNumber = GetRandomNumber(10000, 19999)
If Not dicInvCRM.exists(RandomNumber) Then
wsUpInv.Range("B" & i).Value = RandomNumber
uniqueCRM = True
End If
Loop
uniqueCRM = False
' Col3 = Store Name
RandomNumber = GetRandomNumber(LBound(arrStore), UBound(arrStore))
wsUpInv.Range("C" & i).Value = arrStore(RandomNumber, 1)
' Col4 =Customer Code
' CX-00001234
wsUpInv.Range("D" & i).Value = "CX-0000" & GetRandomNumber(1000, 9999)
' Col5 =Invoice Amount
wsUpInv.Range("E" & i).Value = GetRandomNumber(5000, 20000)
' Col6 = Open Amount
wsUpInv.Range("F" & i).Value = GetRandomNumber(1, wsUpInv.Range("E" & i).Value)
' Col7 =ERP Invoice Number
Do Until UniqueERP = True
RandomValue = CreateAlphaNumericString(8)
If Not dicInvERP.exists(RandomValue) Then
wsUpInv.Range("G" & i).Value = RandomValue
UniqueERP = True
End If
Loop
UniqueERP = False
' Col 8 = Source
RandomNumber = GetRandomNumber(LBound(arrSource), UBound(arrSource))
wsUpInv.Range("H" & i).Value = arrSource(RandomNumber, 1)
' Col 9 = CRM Reference Code
RandomNumber = GetRandomNumber(LBound(arrCRMRef), UBound(arrCRMRef))
wsUpInv.Range("I" & i).Value = arrCRMRef(RandomNumber, 1)

Next i

wsUpInv.Columns("A:I").AutoFit
End Sub``````

## Supporting Functions

Below are the two supporting functions that are referenced by the above code.

### Random Number Generator

``````Function GetRandomNumber(fLowerBound As Long, fUpperBound As Long)

GetRandomNumber = Int(fLowerBound + Rnd * (fUpperBound - fLowerBound + 1))

End Function``````

### Create a Alpha Numeric String

``````Function CreateAlphaNumericString(stringLen As Long)

Dim i As Long
Dim alphaOrNumeric As Long, singleString As String, fullString As String

For i = 1 To stringLen
alphaOrNumeric = GetRandomNumber(1, 2)
Select Case alphaOrNumeric
Case 1
'generates 1-digit string from 0-9
singleString = CStr(GetRandomNumber(0, 9))
Case 2
'generates A-Z
singleString = Chr(GetRandomNumber(65, 90))
End Select
fullString = fullString & singleString
Next i

CreateAlphaNumericString = fullString
End Function``````