PowerPoint vba - Names from a hat simulation

Published on
9,152 Points
Last Modified:
This article shows how to simulate drawing numbers or names from a hat or bag using vba in PowerPoint and prevents duplicate items being selected.

It’s not difficult to choose a (semi) random number in vba. The RND function returns a decimal number between zero and 1 (but never 1) so we can easily use it to produce any range. This produces a number between 1 and 10 for example.

Dim myNumber as Long
myNumber=INT(RND  * 10)+1 

Open in new window

A more general formula to produce a range of numbers not starting with one would be:

myNumber = INT(RND * the number of items) + lowest number

Open in new window

so to produce a a number in the range 3 to 10 (8 numbers starting with 3)
myNumber=INT(RND * 8)+3

Open in new window

Note that RND is not truly random and the use of randomize resets the seed to increase its variability.

Real Life Scenario:
Consider now drawing a name or number from a hat. Once drawn that number is discarded and cannot be drawn again. You can continue drawing numbers or names confident that you won’t see any duplicates.  This doesn’t happen with RND so there is a chance a name or number can be drawn more than once. This is obviously not desirable!
Perhaps the obvious answer is to check each choice to make sure it has not been used and then to redraw until a unique name or number is selected.  This will work but if most of the numbers or names have already been selected there may be a large number of ‘used’ items selected.

Using a Collection in vba can closely simulate the real life hat though by removing selections as they are drawn. Here’s how it works.

First the “myHat” Collection is created by declaring it as a new Collection. Then we fill the collection with the names or numbers. Using the random number method one of the items in the collection is selected. A message box informs us of the choice and then this item is deleted so that it cannot be used again.

Sub From_Hat()
'Declare the collection
Dim myHat As New Collection
Dim names() As String
Dim x As Long
Dim iChoice As Integer

'load names (or numbers) into an array using SPLIT
'you can use a longer list!
names = Split("Bob\Mary\Joe\John", "\")
'Bob will be names(0) and John names(3)

'load into the new collection
For x = 0 To UBound(names)
myHat.Add (names(x))
Next x

'choose a random name
iChoice = Int(Rnd * myHat.Count) + 1
MsgBox myHat(iChoice)
'remove that choice
myHat.Remove (iChoice)
Loop Until myHat.Count = 0
MsgBox "All chosen"
End Sub

You should be able to modify the code easily to create other similar simulations.
If you need a range of numbers instead of names then you can omit the array and slit and just use a simple loop to load the collection.


'load into the new collection
For x = 1 To 99
myHat.Add (x)
Next x

Open in new window

You might also want to put a message box to enable a jump out of the loop when enough names have been chosen.

'choose a random name
iChoice = Int(Rnd * myHat.Count) + 1
If MsgBox("The choice is " & myHat(iChoice) _
& vbCrLf & "Choose again?", vbYesNo) <> vbYes Then Exit Do
'remove that choice
myHat.Remove (iChoice)
Loop Until myHat.Count = 0

Open in new window


This is an efficient way of selecting items when you cannot have any duplicates. As written it reports to a message box but it could be easily modified to change text in a shape on a slide.

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

This video teaches viewers how to fit pictures into slides, crop and remove backgrounds, and alter photos to look more professional.
The viewer will learn how to  create a slide that will launch other presentations in Microsoft PowerPoint. In the finished slide, each item launches a new PowerPoint presentation and when each is finished it automatically comes back to this slide: …

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month