How to ensure an array only contains unique numbers in vba

Question

as the title says, i have an array of 20 integers. Each time i generate a new random number, i want to check if it is already contained in the array. If the number exists in the array, i want to re-generate a random number and re-check the array from start again. In the end, my array of 20 integers should contain all unique values between 7 to 30. I have created a perfect code mess doing this. Appreciate some help from the experts here. Thanks so much.

 Dim Low, High, tmpNo As Double
 Dim myarray(20) As Integer
 Dim i, x As Integer

 Low = 7
 High = 30
 i = 0
 x = 0

 For i = 0 To 19
  tmpno = Int((High - Low + 1) * Rnd() + Low)
  If i = 0 Then
    myarray(i) = tmpno
    tmpno = Int((High - Low + 1) * Rnd() + Low)
  End If

  For x = 0 To Len(i)
    If tmpno = myarray(x) Then
        tmpno = Int((High - Low + 1) * Rnd() + Low)
        x = 0
    Else
        If myarray(x + 1) = 0 Then
            myarray(x + 1) = tmpno
            tmpno = Int((High - Low + 1) * Rnd() + Low)
            x = 0
        End If
    End If
  Next
 Next

Rather than generate, detect dup and retry, I suggest you do a shuffle instead. Fill an array from Low to High, shuffle, then take first 20 elements. — chris neilsen, Sep 11 '14 at 06:36
Thanks for the tip. I guess this should do the trick neat...And now to figure out how to shuffle array. — schenker, Sep 11 '14 at 13:05
See [this answer](http://stackoverflow.com/a/18543399/445425). Note the comment and link by a Webb re bias — chris neilsen, Sep 11 '14 at 14:33

score 0 · Answer 1 · answered Sep 11 '14 at 09:46

You use dictionaries not arrays for this. You only set the key. You can't add duplicate keys so test for errors with On Error Resume Next and If err.number <> 0 then ...

A Dictionary object is the equivalent of a PERL associative array. Items can be any form of data, and are stored in the array. Each item is associated with a unique key. The key is used to retrieve an individual item and is usually an integer or a string, but can be anything except an array.

The following code illustrates how to create a Dictionary object:

Visual Basic Script  Copy Code 
Dim d   ' Create a variable.

Set d = CreateObject("Scripting.Dictionary")

d.Add "a", "Athens"   ' Add some keys and items.

d.Add "b", "Belgrade"

d.Add "c", "Cairo"

...

How to ensure an array only contains unique numbers in vba

1 Answers1