Picking from a Random List Without Repetition in vba

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?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Martin LissOlder than dirtCommented:
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

Martin LissOlder than dirtCommented:
I'm sorry, ignore that.
byundtMechanical EngineerCommented:
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

Arno KosterCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.