Array variables in VBA

Posted by Dan on December 03, 2001 6:49 AM

In VBA, I have an array of 10 variables. Is there an easy way to make sure that the 10 variables are unique? I am assigning them a random integer between 1 and 100 and don't want duplicates. Do I just need a bunch of If statements to check the values? Seems like there should be an easier way. What is the most efficient code?

Posted by Bruno on December 03, 2001 7:11 AM

Hi Dan, is this what you arelooking for ?

Regards,
Bruno
====================
Sub RandomNumbers()

Dim MyValue(10)

For counter = 1 To 10
duplicates = 0
Do While duplicates = 0
MyValue(counter) = Int((100 * Rnd) + 1)
For Cont = 1 To counter
If MyValue(Cont) = MyValue(counter) Then duplicates = 1
Next
Loop
Next

'results
For dummy = 1 To 10
MsgBox dummy & " : " & MyValue(dummy)
Next
====================

End Sub

Posted by Bruno on December 03, 2001 7:14 AM

Sorry Dan, it doesn't work correctly ...

Posted by Dan on December 03, 2001 7:22 AM

Thanks, Bruno. I see the logic though.

I think the problem may lie in

For Cont = 1 To counter
If MyValue(Cont) = MyValue(counter) Then duplicates = 1
Next

Because at the last loop Cont = counter, so you would be comparing two identical arrays. I bet it will work with:

For Cont = 1 To (counter - 1)
If MyValue(Cont) = MyValue(counter) Then duplicates = 1
Next

I'll try it and let you know. Thanks for your help.

Posted by Dan on December 03, 2001 9:21 AM

Here's what the final code is:

Actually there was a problem with the loop also. Here is the final code I came up with. Thanks for getting me started.

For Counter = 1 To 10
Array1(Counter) = Int((100 * Rnd) + 1)
If Counter > 1 Then
Duplicates = 0
Do While Duplicates = 0
For Count = 1 To (Counter - 1)
If Array1(Count) = Array1(Counter) Then Duplicates = 1
Next
If Duplicates = 1 Then
Array1(Counter) = Int((100 * Rnd) + 1)
Duplicates = 0
Else
Duplicates = 1
End If
Loop
Else
End If
Next

Posted by Bruno on December 03, 2001 11:08 PM

Re: Here's an alternative ;-)

Hi Dan,
Yesterday I made a little mistake as you know.
This is my 'final' code. With the command Debug.Print you can also see the build-up of the filling-in of the array.
I take random numbers from 1 to 20, so there must be some duplicates...

Best regards,
Bruno
==================
Sub RandomNumbers()

Dim MyValue(10)

For counter = 1 To 10
duplicates = 1
Do Until duplicates = 0
MyValue(counter) = Int((20 * Rnd) + 1)
Debug.Print MyValue(1) & " - "; MyValue(2) & " - " & MyValue(3) _
& " - " & MyValue(4) & " - " & MyValue(5) & " - " & MyValue(6) _
& " - " & MyValue(7) & " - " & MyValue(8) & " - " & MyValue(9) _
& " - " & MyValue(10)

duplicates = 0
For Cont = 1 To counter - 1
If MyValue(Cont) = MyValue(counter) Then duplicates = 1
Next
Loop
Next
'result
For dummy = 1 To 10
msgTxt = msgTxt & dummy & " : " & MyValue(dummy) & vbLf
Next
MsgBox msgTxt

End Sub