Shuffling

If you have a list of items, for example a list of questions:

Dim Questions As Variant
Questions = Array("How many legs does a cat have", "1 + 1 equals...",
                  "What colour is the sky?", "What does a bird use its wings for?",
                  "What is the letter after 'a'?")

Yes, I know these are silly questions but you get the idea :o)

If you want to show them in a random order, your first idea may be to generate a random number and show that item:

For Index = 0 To UBound(Questions)
  Debug.Print Questions(Int((UBound(Questions) + 1) * Rnd))
Next

You run it once, you get the following results:

What does a bird use its wings for?
What colour is the sky?
What colour is the sky?
1 + 1 equals...
1 + 1 equals...

Hmmm, I'm getting duplicates. I know, I'll store a list of ones I've shown so far:

For Index = 0 To UBound(Questions)
  Item = Int((UBound(Questions) + 1) * Rnd)
  Do While Shown(Item)
    Debug.Print " Already shown item " & CStr(Item + 1)
    Item = Int((UBound(Questions) + 1) * Rnd)
  Loop
  Shown(Item) = True
  
  Debug.Print Questions(Item)
Next

This time the results are better:

What does a bird use its wings for?
What colour is the sky?
 Already shown item 3
1 + 1 equals...
 Already shown item 2
 Already shown item 4
How many legs does a cat have?
 Already shown item 4
What is the letter after 'a'?

Note that while each item is different there are a number of "Already shown item X". With more and more items it will become significantly slower to give a full list. As you get closer to the last questions, you will have a much smaller selection to choose from so you be needlessly looping more.

The correct way to generate a random/shuffled list is to shuffle them:

'Create a sorted array of indexes into the question array
ReDim Shuffled(UBound(Questions))
For Index = 0 To UBound(Shuffled)
  Shuffled(Index) = Index
Next

'Shuffle all the entries
For Count = 0 To UBound(Shuffled) * 10
  'Pick a random position
  Item = Int((UBound(Shuffled)) * Rnd)
  'Swap this entry and the next
  Temp = Shuffled(Item + 1)
  Shuffled(Item + 1) = Shuffled(Item)
  Shuffled(Item) = Temp
Next

You can then show the questions easily in a very random order:

For Index = 0 To UBound(Questions)
  Debug.Print Questions(Shuffled(Index))
Next

The reults from this are as follows...

1 + 1 equals...
What is the letter after 'a'?
How many legs does a cat have?
What does a bird use its wings for?
What colour is the sky?