|
|
|
| Title | Use VBA code to pick random elements from the currently selected cells in an Excel workbook |
|---|
| Description | This example shows how to use VBA code to pick random elements from the currently selected cells in an Excel workbook. |
|---|
| Keywords | VBA, Visual Basic for Applications, random, pick random, Excel, pick random cells, Visual Basic, Visual Basic .NET, VB.NET |
|---|
| Categories | Office |
|---|
|
|
|
The SelectRandom subroutine shown in the following code picks an indicated number of random items chosen from the currently selected cells.
|
|
' Select the indicated number of items from the
' currently selected cells.
Public Sub SelectRandom(ByVal num_to_select As Integer)
Dim num_items As Integer
Dim indexes() As Integer
Dim i As Integer
Dim j As Integer
Dim temp As Integer
' Make sure the selection is a range.
If Not (TypeOf Application.Selection Is Range) Then
MsgBox "The current selection is not a range."
Exit Sub
End If
' Make sure we're selecting at least 1 item.
If num_to_select < 1 Then
MsgBox "Cannot pick fewer than 1 item."
Exit Sub
End If
' See how many items are selected.
num_items = Application.Selection.Count
If num_to_select > num_items Then
MsgBox "You cannot pick more items than there are " & _
"in total."
Exit Sub
End If
' Make an array of this many numbers.
' Add 1 because the cell indexes
' in the selection start at index 1.
ReDim indexes(0 To num_items - 1)
For i = 0 To num_items - 1
indexes(i) = i + 1
Next i
' Randomize the numbers.
For i = num_items - 1 To 1 Step -1
' Randomly pick an index at or below this one.
j = Int((i + 1) * Rnd)
' Swap indexes(j) and indexes(i).
temp = indexes(i)
indexes(i) = indexes(j)
indexes(j) = temp
Next i
' Deselect all items.
Application.Selection.Font.Bold = False
Application.Selection.Font.Color = vbBlack
' Select the first items.
For i = 0 To num_to_select - 1
Application.Selection.Cells(indexes(i)).Font.Bold = _
True
Application.Selection.Cells(indexes(i)).Font.Color _
= vbRed
Next i
End Sub
|
|
|
The code first verifies that the current selection is a range (not a button or something), and that the number of items to pick is between 1 and the total number of items.
Next the code makes an index array containing the numbers 1, 2, ..., num_items, and it randomizes that array.
The code unmarks all of the items in the selection by resetting their colors and font boldness. It then loops through the first several items in the randomized index array picking the necessary number of items. Because the array is randomized, this gives a random selection.
|
| |
|
| |
|
|
