How to Shuffle an Array Using Excel VBA (4 Examples)

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.


Download Practice Workbook


4 Examples to Shuffle an Array in Excel VBA

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.

excel vba shuffle array

For creating this article, we have used 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 used as a worksheet function.

Using VBA OFFSET, RAND Functions to Shuffle an Array in Excel

Step-01:

  • 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.

Using VBA OFFSET, RAND Functions to Shuffle an Array in Excel

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.

type code

  • Press F5.

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

Run code to Shuffle an Array in Excel VBA


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 name of the employees.

Generating Some Random Numbers to Shuffle an Array Using VBA in Excel

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.

code

  • Press F5.

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

Generating Some Random Numbers to Shuffle an Array Using VBA in Excel


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.

Calling Sub-Procedure within a Sub to shuffle an array in Excel VBA

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.

write down code

  • 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.

Using User-Defined Function to shuffle Array Through Message Box in Excel VBA

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*9Variant.
  • 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.

result


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.

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

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.

result


Practice Section

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

practice section to shuffle array in Excel VBA


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.

Tanjima Hossain

Tanjima Hossain

Hello everyone, This is Tanjima Hossain. I have completed my graduation from BUET. Then I have started working as a technical writer in SOFTEKO. I have grown interest in technical content writing, research topics, numerical analysis related field and so I am here. Besides this I love to interact with different people and I love to spend my spare time by reading, gardening ,cooking etc.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo