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.

Macro file can be downloaded from Github here.

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
initialDate = DateAdd("d", -730, 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
            dicInvCRM.Add RandomNumber, 0
            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
            dicInvERP.Add RandomValue, 0
            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

Related Posts