If you want to shuffle an array using Excel VBA, then this article will help you a lot. As we have demonstrated here 4 different ways of shuffling an array using some VBA codes. So, let’s start with our main article.

**Table of Contents**Expand

## How to Shuffle an Array Using Excel VBA: 4 Examples

Here, we have a list of employee names. If we want to shuffle these names randomly for any purpose, then we can follow the stated **4** examples below to do this job.

For creating this article, we have used the ** Microsoft Excel 365 version**. However, you can use any other version at your convenience.

__Example-1__: Using VBA OFFSET, RAND Functions to Shuffle an Array

Here, we will shuffle the employee names and get these shuffled results in the **Shuffled Employee Name **column. In our **VBA** code, we will be using **the RAND function** as a worksheet function.

- Go to the
**Developer**tab >>**Visual Basic**.

After that, the **Visual Basic Editor **window will appear.

- Go to the
**Insert**tab >>**Module**.

Then, we will have a new module like **Module 1**.

** Step-02**:

- Write the following code in your created module.

```
Sub shuff_rand_1()
Dim employee As Variant, sht As Worksheet
Set sht = Worksheets("Offset,Rand")
With sht.Range("C5:C12")
employee = .Value
.Offset(0, 1).Formula = "=Rand()"
.Resize(, 2).Sort Key1:=.Offset(0, 1)
.Offset(0, 1).Value =.Value
.Value = employee
End With
End Sub
```

__Code Breakdown__

- Here, we have declared two variables
**employee**as**Variant**, and**sht**as**Worksheet**. - Then, we assigned the sheet name
**“Offset, Rand”**for the**sht** - Using
**the WITH statement**we have discarded the need of restating the object**Range(“C5:C12”)**for further purposes. **.Offset(0, 1).Formula = “=Rand()” → The RAND function**will be used in the adjacent column of our stated range.**.Resize(, 2).Sort Key1:=.Offset(0, 1) → .Resize(, 2)**will select the two ranges from**C5:C12**to**D5:D12**and then**D5:D12**will be sorted.

- Press
**F5**.

In this way, we randomly shuffled the name of the employees.

__Example-2__: Generating Some Random Numbers in Excel to Shuffle an Array

Here, we will generate some random numbers, and then taking the help of these numbers we will shuffle the names of the employees.

** Steps**:

- Follow
**Step-01**of**Method-1**. - Now write down the following code in your created module.

```
Sub shuff_rand_2()
Dim employee_temp As String, value_int As Integer
For val_1 = 5 To 12
Cells(val_1, 4).Value = WorksheetFunction.RandBetween(0, 200)
Next val_1
For val_1 = 5 To 12
For val_2 = val_1 + 1 To 12
If Cells(val_2, 4).Value < Cells(val_1, 4).Value Then
employee_temp = Cells(val_1, 3).Value
Cells(val_1, 3).Value = Cells(val_2, 3).Value
Cells(val_2, 3).Value = employee_temp
value_int = Cells(val_1, 4).Value
Cells(val_1, 4).Value = Cells(val_2, 4).Value
Cells(val_2, 4).Value = value_int
End If
Next val_2
Next val_1
End Sub
```

__Code Breakdown__

- Here, we have declared two variables
**employee_temp**as**String**, and**value_int**as**Integer**. - Then, we assigned the limit for
**val_1**from**5**to**12**(row numbers of our range). **Cells(val_1, 4).Value = WorksheetFunction.RandBetween(0, 200) →**Using**the RANDBETWEEN function**we generated random numbers from**0**to**200**in**column****D**.- After that, we used two
**FOR loops**for creating two loops within the range**5**to**12**and the other one will be incremented by**1**from this range. - Later we used
**the IF statement**to state the condition that the value of a cell will be less than the following cell.

- Press
**F5**.

Finally, we will be able to rearrange the name of the employees in the **Employee Name **column.

__Example-3__: Calling Sub-Procedure within a Sub to Shuffle Array in Excel

In this section, we will use two sub-procedures together to shuffle the name of the employees.

** Steps**:

- Follow
**Step-01**of**Method-1**. - Now write down the following code in your created module.

```
Sub shuff_rand_3(employee() As Variant)
Dim lim_rng As Long, empl_trans As Variant, new_rng As Long
Randomize
For lim_rng = LBound(employee) To UBound(employee)
new_rng = CLng(((UBound(employee) - lim_rng) * Rnd) + lim_rng)
If lim_rng <> new_rng Then
empl_trans = employee(lim_rng)
employee(lim_rng) = employee(new_rng)
employee(new_rng) = empl_trans
End If
Next lim_rng
End Sub
Public Sub shuff_rand_real()
Dim empl_rng() As Variant
empl_rng = Application.Transpose(Range("C5:C12"))
shuff_rand_3 empl_rng
Range("D5:D12") = Application.Transpose(empl_rng)
End Sub
```

__Code Breakdown__

- Here, in the first sub-procedure, we declared it as
**shuff_rand_3(employee() As Variant)** - Then, we declared
**lim_rng**as**Long**,**empl_trans**as**Variant**,**new_rng**as**Long** - Later we used
**the RANDOMIZE function**to provide the input value for**the RND function**. **The FOR loop**used the limit from the**lower**limit of the**employee array**to the**upper**limit of this array.- Then,
**new_rng**will store some randomly generated numbers using**the RND function**. - After that, according to these numbers we rearranged our array
- Finally, we declared the second sub-procedure
**shuff_rand_real**and declared**empl_rng()**as**Variant**. - Using
**the TRANSPOSE function**we transposed the range**C5:C12**and then stored it into**empl_rng**. - Then we called the previous sub-procedure for this array and after transposing again got the result in the range
**D5:D12**.

- Press
**F5**.

Eventually, we randomly shuffled the name of the employees.

__Example-4__: Employing User-Defined Function to Get Shuffled Array Through Message Box

Here, we will shuffle the name of the employees and show the results through a message box.

** Steps**:

- Follow
**Step-01**of**Method-1**. - Now write down the following code in your created module.

```
Function stuff_rand_4(employee() As Variant) As Variant()
Dim lim_rng As Long, empl_trans As Variant, new_rng As Long
Randomize
For lim_rng = LBound(employee) To UBound(employee)
new_rng = CLng(((UBound(employee) - lim_rng) * Rnd) + lim_rng)
If lim_rng <> new_rng Then
empl_trans = employee(lim_rng)
employee(lim_rng) = employee(new_rng)
employee(new_rng) = empl_trans
End If
Next lim_rng
stuff_rand_4 = employee()
End Function
Sub shuff_rand_real_1()
Dim empl_rng() As Variant
empl_rng = Array("Watson", "Jennifer", "Kelvin", "Lara", _
"Jackson", "Peter", "Mary", "Ursula")
empl_rng = stuff_rand_4(empl_rng)
MsgBox Join(empl_rng, ",")
End Sub
```

__Code Breakdown__

- Firstly, in the function
**stuff_rand_4**, we declared**employee() as Variant**. - Then, we declared
**lim_rng**as**Long**,**empl_trans**as**Variant**,**new_rng**as**Long** - Later we used
**the RANDOMIZE function**to provide the input value for**the RND function**. **The FOR loop**used the limit from the**lower**limit of the**employee array**to the**upper**limit of this array.- Then,
**new_rng**will store some randomly generated numbers using**the RND function**. - After that, according to these numbers we rearranged our array
- Finally, we declared the sub-procedure
**shuff_rand_real_1**and declared**empl_rng()**as a 6*9**Variant**. - Then we declared our array under this variable.
- Later we called the previous function for this array.
- Eventually, using
**the JOIN function**all of the entities of this array will be joined together to show in the**message box**(**MsgBox**).

- Press
**F5**to show the shuffled result through a message box.

## How to Generate Random Numbers Between 1 and 100 Using VBA in Excel

In this section, we will generate **100** random numbers between **1 **and **100** in the following two columns. In our **VBA **code, we will use the RANDARRAY function.

** Steps**:

- Follow
**Step-01**of**Method-1**. - Now write down the following code in your created module.

```
Sub rand_numbers()
Range("B5:C54").Value = WorksheetFunction.RandArray(50, 2, 0, 100, True)
End Sub
```

Here, **RandArray(50, 2, 0, 100, True) **will generate these random numbers. In this function, **50 **is the **row **number, **2 **is the column number, **0 **is the **start **number, and **100 **is the **end **number. Finally, the result will be shown throughout the range **“B5:C54”**.

- Press
**F5**to generate the numbers.

In the following figures, we have shown all of these randomly generated numbers.

## Practice Section

To practice by yourself, we have created a Practice section on the right side of each sheet.

**Download Practice Workbook**

## Conclusion

In this article, we have discussed different ways to shuffle an array using Excel VBA. Hope these methods will help you a lot. If you have any further queries, then leave a comment below.

**<< Go Back to** **Randomize in Excel** **| Learn Excel**