Creating Arrays

[sandylou]

I am just getting into arrays and am creating a sample file which will select distinct names from a table, randomize a number from 1 to 10 and then assign each name that number.
I am so new at arrays that I understand how to create one
dim myArray(9) as Integer

and add to it
for i = 1 to 10
myArray(i) = randomNumber
Next i

But can't figure out how to access the array to see if the number exists and if it does randomize again.

Does this make sense? Any ideas? Let me know if I should post the code I have. Thanks!

 

[tbl]

I hope this is what you are looking for - if not just let me know

Richard

Sub arrays()
Dim myArray(9) As Integer

For I = 1 To 9
myArray(I) = Int((9 * Rnd) + 1)
Next I

A = myArray(1)
B = myArray(2)
C = myArray(3)
D = myArray(4)
E = myArray(5)
F = myArray(6)
G = myArray(7)
H = myArray(8)
I = myArray(9)

End Sub

 

[sfvb]

You dimensioned the array as 9, and I see a problem with that.

If you didn't use the Option Base 1 statement at the module level, then the default lower range of your array will be 0. So, the lower range of your array is 0 while the upper range of your array 9.

If you did use the Option Base 1 statement at the module level, then the lower and upper range of your array will be 1 and 9, respectively.

Either way your FOR/NEXT loop will throw an exception when the index to your loop reaches 10. Your loop will try to assign a random number to an array element that doesn't exist and you will get:

So you have a couple of choices:
If you are comfortable with 0 based arrays you can change your macro to:

   Dim myArray(9)    As Integer
   ...
   For i =

0

 To

9

   ...

or you can use the Option Base 1 statement at the module level:

   Dim myArray(

10

)    As Integer
   ...
   For i = 1 To 10
   ...

or, perhaps the easiest, is to just let the module default to a zero based lower bound array, but still use 10 as the upper bound of your array, as in:

   Dim myArray(

10

)   As Integer
   ...
   For i = 1 To 10
   ...

The only differnce between this and using the Option Base 1 statement, is that myArray(0) does exist as an array element, but you just choose not to use it. Since myArry(0) does exist, it is allocated memory, and if unused is wasteful. But on a single-dimensional array, the non-use is extremely minimal. (2 bytes for an Integer data type) (I find it easier to think that the 1st elemenet I'm going to use has 1 as it's index, as opposed to 0. Oftentimes, I use the 0 index for sorting routines.)

That will work for predefined arrays. But you said "... and am creating a sample file which will select distinct names from a table". What if the number of distinct names is greater than 10? Well, you can create a dynamic array! You do this by NOT dimensioning the array with a preset number of elements. After you have determined the number of elements you'll need, you redimension the array using ReDim statement.
To loop through all the elements of a dynamic array, you'll probably want use the UBound function. The UBound function returns the upper bound of your array.

Here's an an example of a dynamic array. (The number of elements in this array is random).

Sub TestDynamicRandomNumberArray()
   Dim myArray()     As Integer
   Dim idx           As Integer
   Dim minValue      As Integer
   Dim maxValue      As Integer
   Dim maxElements   As Integer   
   
   ActiveSheet.Cells.ClearContents
   maxElements = Int(100 * Rnd) + 20

   ReDim myArray(maxElements)
   minValue = 0
   maxValue = 100
   For idx = 1 To UBound(myArray)
      myArray(idx) = Int((maxValue - minValue + 1) * Rnd) + minValue
      ActiveSheet.Range("A" & idx).Value = myArray(idx)
   Next idx
End Sub

 

[sandylou]

So after playing around with this for sometime, my question becomes more, how can i ensure that each random number is assigned into the array only once. I select one person randomize the size of the recordcount, and assign it one of the numbers once and only once...If the randomization selects a number that has already been chosen, It has to keep randomizing until it picks a number that has not been chosen.
***********************************
Dim myArray()
Call Randomize

'all of the opening of the recordset here
'set the intSize = to the recordcount

randomNumber = Int((intSize - 1 + 1) * Rnd + 1)
ReDim Preserve myArray(intSize)
myArray(intSize) = randomNumber

For i = LBound(myArray) To UBound(myArray)
'do i need to set up another array to help test what I already have in myArray?
Dim testArray()
ReDim Preserve testArray(i)
testArray(i) = myArray(intSize)
If myArray(intSize) = testArray(i) Then
'if number exists in myArray randomize again
Do While myArray(intSize) <> testArray(i)
Randomize (4)
randomNumber = Int((4 - 1 + 1) * Rnd + 1)
Loop
myArray(intSize) = randomNumber
Else
Exit For
End If
Next

 

[RobBroekhuis]

The sub below illustrates an approach (you'll have to tailor it for your situation) in which all numbers are assigned initially to a second array, and &quot;removed&quot; once picked:

Sub randomy()
Dim a(9) As Integer, b(9) As Integer, i As Integer, j As Integer
For i = 1 To 9: b(i) = i: Next i
For i = 1 To 9
j = Int((10 - i) * Rnd) + 1
a(i) = b(j)
b(j) = b(10 - i)
Next i
End Sub

An alternative solution would be to put all the numbers into the one array (in order), and shuffle by repeatedly picking two random numbers and exchanging the elements corresponding to the numbers.


Rob

 

[RobBroekhuis]

Come to think of it, I like that &quot;alternative solution&quot; better (fewer variables to keep track of). Something like:

Sub randomy()
Dim a(9) As Integer, i As Integer, j As Integer, temp As Integer
For i = 1 To 9: a(i) = i: Next i
For i = 1 To 9
j = Int(9 * Rnd) + 1
If i <> j Then temp = a(i): a(i) = a(j): a(j) = temp
Next i
End Sub


Rob