Picking from a Random List Without Repetition in vba

Posted on 2011-10-11
Medium Priority
Last Modified: 2012-05-12
On VBA I have two arrays, a() and b()
a() is filled with a set of values from an excel spreadsheet
b() is filled by selecting random numbers from a().
I want b() to be filled by selecting random numbers from a() without repetition.
Right now I am using a function like the following
b(z, J) = a(Int(((totalvalues - 6) - 1 + 1) * Rnd + 1), J)
How would i do the same thing and ensure there are no repetitons?
Question by:awesomejohn19
  • 2
LVL 51

Expert Comment

by:Martin Liss
ID: 36950175
Using code like the following you can check to see if an item is in b() and not add it again if it is.

Private Declare Function SendMessage Lib "user32" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, ByVal wParam As Long, lParam As Any) As Long

Private Sub Command1_Click()
If (SendMessage(List1.hwnd, LB_FINDSTRINGEXACT, -1&, _
                        ByVal CStr(strSomeValue))) > -1& Then
    'Don't add it
    'Add it
End If

Open in new window

LVL 51

Expert Comment

by:Martin Liss
ID: 36950184
I'm sorry, ignore that.
LVL 81

Expert Comment

ID: 36950191
Create a third array c() filled with random numbers, same dimension as a(). Populate b array by picking the n smallest values in c, drawing values from the a array with corresponding index numbers.

Sub RandomPicker()
Dim a(20) As Variant, b(5) As Variant
Dim c() As Double, d As Double
Dim i As Long, j As Long, k As Long, n As Long, nPicks As Long
n = UBound(a)
ReDim c(n)
nPicks = UBound(b)

For i = LBound(c) To n
    a(i) = Chr(64 + i)  'Populate a array
    c(i) = Rnd()        'Populate c array
For i = LBound(b) To nPicks
    k = i - LBound(b) + 1   'Index reference starting with 1
    d = Application.Small(c, k)
    j = Application.Match(d, c, 0)
    b(i) = a(j)
    Debug.Print b(i)       'Print value chosen in Immediate pane
End Sub

Open in new window

LVL 19

Accepted Solution

Arno Koster earned 2000 total points
ID: 36950210
you could copy the valies of array A into a temporarily array C
then select a randon number from 0 to the length of array C
move the selected item from C to B (copy it from C to B, delete from array C)
continue until C is empty.

When you do not want to write the 'delete from array' function yourself, you could opt to implement the array C as an collection :

dim c as collection

c.add a(item)
c.remove(random selected item number)

for more information on using collections, visit
blogspot forum

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
I tried to use the SharePoint app to Import a Spreadsheet and import an Excel sheet into a Team site made in SharePoint 2016. But that just resulted in getting an error message 'Unknown Error'...
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question