Generate Unique Random Numbers With Excel VBA

Objective

We aim to create a VBA script that generates a specified number of unique random numbers in Excel. The key challenge here is ensuring that each generated number is unique, and we want to avoid any duplicates.

Steps and Explanation

1. Initial Setup:
   • We will create a subroutine that takes in a few parameters:
     • Total numbers to generate (e.g., 10 unique random numbers)
     • Range for the random numbers (e.g., between 1 and 100)
   • The program will generate numbers within the specified range and ensure they are unique (i.e., no duplicates).
2. Generating Random Numbers:
   • The standard method for generating random numbers in VBA is the Rnd function, but it generates floating-point numbers between 0 and 1. To get integers, we use the formula:
   • Int((upper bound – lower bound + 1) * Rnd + lower bound)

where upper bound is the maximum value, and lower bound is the minimum value.

1. Ensuring Uniqueness:
   • To make sure the numbers are unique, we’ll store each number we generate in an array or a collection.
   • We will repeatedly generate random numbers and check if the generated number is already in the collection. If it is, we will generate another one.
   • The process will continue until the required number of unique random numbers is generated.
2. Storing the Results:
   • The unique random numbers will be written into an Excel worksheet.

VBA Code: Generate Unique Random Numbers

Sub GenerateUniqueRandomNumbers()
    Dim totalNumbers As Integer
    Dim lowerBound As Integer
    Dim upperBound As Integer
    Dim uniqueNumbers As Collection
    Dim randomNumber As Integer
    Dim i As Integer
    Dim rowNum As Integer
    Dim found As Boolean
    ' Set the parameters for the random numbers
    totalNumbers = 10  ' Number of unique random numbers to generate
    lowerBound = 1     ' Minimum value of the random numbers
    upperBound = 100   ' Maximum value of the random numbers
    ' Initialize the collection to hold unique numbers
    Set uniqueNumbers = New Collection
    ' Start inserting numbers in the worksheet (starting from row 2)
    rowNum = 2
    ' Loop until the desired number of unique random numbers is generated
    Do While uniqueNumbers.Count < totalNumbers
        ' Generate a random number within the specified range
        randomNumber = Int((upperBound - lowerBound + 1) * Rnd + lowerBound)     
        ' Check if the number is already in the collection (unique check)
        found = False
        On Error Resume Next  ' Ignore error if number is not found
        uniqueNumbers.Item randomNumber
        If Err.Number = 0 Then
            found = True ' The number already exists in the collection
        End If
        On Error GoTo 0 ' Reset error handling       
        ' If the number is not found, add it to the collection
        If Not found Then
            uniqueNumbers.Add randomNumber
            ' Write the unique number to the worksheet (starting from row 2, column 1)
            Cells(rowNum, 1).Value = randomNumber
            rowNum = rowNum + 1
        End If
    Loop
    MsgBox "Unique random numbers generated!"
End Sub

Detailed Explanation of the Code

1. Setting up the Parameters:

totalNumbers = 10  ' Number of unique random numbers to generate
lowerBound = 1     ' Minimum value of the random numbers
upperBound = 100   ' Maximum value of the random numbers

• totalNumbers defines how many unique random numbers we want to generate.
• lowerBound and upperBound specify the range within which the random numbers will be generated.

2. Initializing the Collection:

Set uniqueNumbers = New Collection

• A Collection object is used to store the unique random numbers because collections do not allow duplicate values. If a duplicate is added, it will raise an error, which we can handle to check uniqueness.

3. Generating Random Numbers and Ensuring Uniqueness:

randomNumber = Int((upperBound - lowerBound + 1) * Rnd + lowerBound)

• Rnd generates a random number between 0 and 1. By scaling it with upperBound – lowerBound + 1 and adding the lowerBound, we get a number within the specified range (lowerBound to upperBound).

4. Checking for Duplicates:

On Error Resume Next  ' Ignore error if number is not found
uniqueNumbers.Item randomNumber
If Err.Number = 0 Then
    found = True
End If
On Error GoTo 0  ' Reset error handling

• We use error handling to check if the random number is already in the collection.
• If the number already exists in the collection, Err.Number will be 0, and we mark it as found. If it does not exist, we add it to the collection.

5. Adding the Number to the Worksheet:

• Cells(rowNum, 1).Value = randomNumber
  rowNum = rowNum + 1

  If the number is unique, we add it to the worksheet starting from row 2 (you can adjust this to your preferred row). We also increment the rowNum to place the next number in the next row.

6. Loop Until Desired Number of Unique Random Numbers is Generated:

Do While uniqueNumbers.Count < totalNumbers
    ' Generate and check for uniqueness here...
Loop

• The loop continues until we have generated the required number of unique random numbers.

7. Completion Message:

MsgBox "Unique random numbers generated!"

• Once the loop finishes and the required unique random numbers are generated, a message box is displayed to inform the user that the task is complete.

How to Use

1. Open Excel and press Alt + F11 to open the VBA editor.
2. Insert a new module by going to Insert > Module.
3. Copy and paste the above code into the module.
4. Close the editor and run the macro using Alt + F8, then select GenerateUniqueRandomNumbers and click Run.
5. The unique random numbers will be inserted starting from cell A2.

Conclusion

This code allows you to generate a specified number of unique random numbers within a given range in Excel using VBA. By utilizing a Collection, we ensure that duplicates are avoided, and by using error handling, we can check whether a number already exists in the collection. The numbers are written into an Excel worksheet starting from the second row.