Generate Random Numbers With Excel VBA

Objective:

This code will generate random numbers within a specified range and place them in a selected range of cells. You can customize the range and number of random values you want.

VBA Code to Generate Random Numbers:

Sub GenerateRandomNumbers()
    ' Define variables
    Dim ws As Worksheet
    Dim rng As Range
    Dim cell As Range
    Dim minValue As Double
    Dim maxValue As Double
    Dim numberOfValues As Long
    Dim isInteger As Boolean  
    ' Initialize variables
    Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change to your target sheet name
    Set rng = ws.Range("A1:A10")  ' Change to your target range
    minValue = 1  ' Minimum value for random number generation
    maxValue = 100  ' Maximum value for random number generation
    numberOfValues = rng.Cells.Count  ' Number of cells to fill with random numbers
    isInteger = True  ' Set to True for integers, False for decimals
    ' Loop through each cell in the range
    For Each cell In rng
        If isInteger Then
            ' Generate random integer between minValue and maxValue
            cell.Value = Int((maxValue - minValue + 1) * Rnd + minValue)
        Else
            ' Generate random decimal number between minValue and maxValue
            cell.Value = (maxValue - minValue) * Rnd + minValue
        End If
    Next cell
End Sub

Explanation of the Code:

1. Defining Variables:

The first step in the code is to define the necessary variables. These will help us manage the target worksheet, the range of cells where we want to place the random numbers, and other settings like the minimum and maximum values for the numbers.

Dim ws As Worksheet
Dim rng As Range
Dim cell As Range
Dim minValue As Double
Dim maxValue As Double
Dim numberOfValues As Long
Dim isInteger As Boolean

• ws refers to the worksheet where the random numbers will be generated.
• rng refers to the range of cells where the random numbers will be placed.
• minValue and maxValue define the boundaries of the random number generation.
• numberOfValues holds the count of cells in the specified range.
• isInteger is a Boolean flag to decide whether to generate whole numbers (integers) or decimal numbers.

2. Initial Setup:

After defining the variables, you initialize them with the appropriate values. You can change these values based on your needs.

Set ws = ThisWorkbook.Sheets("Sheet1")  ' Change to your target sheet name
Set rng = ws.Range("A1:A10")  ' Change to your target range
minValue = 1  ' Minimum value for random number generation
maxValue = 100  ' Maximum value for random number generation
numberOfValues = rng.Cells.Count  ' Number of cells to fill with random numbers
isInteger = True  ' Set to True for integers, False for decimals

• ws is set to the worksheet « Sheet1 ». You can replace « Sheet1 » with the name of the sheet you are working with.
• rng is set to the range A1:A10. You can change this to any range you want, for example, « B1:B50 ».
• minValue and maxValue are set to 1 and 100, respectively. These are the boundaries for the random numbers you want to generate.
• numberOfValues is automatically set to the number of cells in the target range (A1:A10 in this case), so the code can know how many random numbers to generate.
• isInteger is set to True, which means the code will generate whole numbers (integers) by default.

3. Generating Random Numbers:

For Each cell In rng
    If isInteger Then
        ' Generate random integer between minValue and maxValue
        cell.Value = Int((maxValue - minValue + 1) * Rnd + minValue)
    Else
        ' Generate random decimal number between minValue and maxValue
        cell.Value = (maxValue - minValue) * Rnd + minValue
    End If
Next cell

This loop iterates over each cell in the specified range (rng), and for each cell, it generates a random number.

• Rnd: This function returns a random value between 0 and 1 (excluding 1). It is used to generate random numbers within the desired range.
• Integer Random Numbers: If isInteger is True, the code generates a random integer by using the Int function. The expression (maxValue – minValue + 1) * Rnd + minValue ensures that the random number falls between the minValue and maxValue inclusively.
• Decimal Random Numbers: If isInteger is False, the code generates a random decimal number using the formula (maxValue – minValue) * Rnd + minValue, ensuring the number falls within the specified range.

4. Explanation of Random Number Generation Formula:

The formula used to generate random numbers is:
randomNumber = (maxValue - minValue + 1) * Rnd + minValue

• maxValue – minValue + 1: This part calculates the total range of possible values.
• Rnd: This generates a random decimal between 0 and 1.
• (maxValue – minValue + 1) * Rnd: This scales the random decimal to a number in the desired range (from 0 to the range width).
• + minValue: This shifts the generated number up so that the minimum value starts at minValue and goes up to maxValue.

The above formula ensures that the random numbers fall within the specified range and are appropriately caled.

5. Placing Random Numbers in Cells:

The random number generated for each cell is then assigned to the value of that specific cell using:

cell.Value = randomNumber

Customization:

• Range: You can change the range where the random numbers are placed. Just modify the Set rng = ws.Range(« A1:A10 ») line to any desired range (e.g., B1:B20, C1:C50).
• Value Type: The isInteger variable can be set to False if you prefer to generate random decimal numbers instead of integers.
• Min and Max Values: Modify the minValue and maxValue variables to change the range of generated numbers.

This approach gives you flexibility to generate random numbers tailored to your specific needs and can be customized for a variety of scenarios.