VBA arrays

[Matt]

I'm trying to get my head around lists (arrays?) in VBA. Lets say I
want to create an array of random numbers whose length is defined by
the user. Is this the right idea?

1 - It would ask the user how long the array should be.
2 - Then starting with an empty array, it would run through a for loop
as many times as defined by the user, each time appending a new random
number to the end.

I can't seem to figure out the right way to use arrays in VBA. Is
array(a, b, c) the best way to create them? How then do you add
another number to it? Or access the nth value in the array?

 

[Sheeloo]

A static array is an array that is sized in the Dim statement that declares
the array. E.g.,
Dim StaticArray(1 To 10) As Long
You cannot change the size or data type of a static array. When you Erase a
static array, no memory is freed. Erase simple set all the elements to their
default value (0, vbNullString, Empty, or Nothing, depending on the data type
of the array).

A dynamic array is an array that is not sized in the Dim statement. Instead,
it is sized with the ReDim statement. E.g.,
Dim DynamicArray() As Long
ReDim DynamicArray(1 To 10)
-----------------------------------------------------------------------
This is an extract from Chip Person's article on VBA Arrays. Pl. visit
http://www.cpearson.com/excel/VBAArrays.htm for the complete excellent
article..
:

While there you may also like to look at Arrays Formulas....

 

[Dave Peterson]

Option Explicit
Sub testme()

Dim myArr() As Double
Dim HowMany As Long
Dim iCtr As Long

HowMany = CLng(Application.InputBox(Prompt:="How many?", Type:=1))

'some minor validity
If HowMany < 1 _
Or HowMany > 100 Then
MsgBox "that won't work!"
Exit Sub
End If

ReDim myArr(1 To HowMany)

Randomize 'shuffle
For iCtr = LBound(myArr) To UBound(myArr)
myArr(iCtr) = Rnd
Next iCtr

End Sub

You could add a number to any element in the array:

myArr(77) = myArr(77) + 5.32421

(assuming that you have 77 elements)

You may want to visit J.E. McGimpsey's site and grab a copy of his =RandInt()
function.
http://www.mcgimpsey.com/excel/udfs/randint.html

 

[Bob Phillips]

A quick way to load an array is from a range on a worksheet.

Dim ary

Range("A1:A10").Formula = "=RAND()"
ary = Application.Transpose(Range("A1:A10"))
Range("A1:A10").ClearContents