Why Arrays

[Kayo]

1. I hear so much about arrays. What are they and when should you use them?


V/r,

 

[Kryzsoccer]

Here is how to use an array:
dim myarray(1 to 10) as integer
'You have niw just created 10 integers their names are myarray(1), myarray(2), myarray(3)...

'this can be very helpful if you want to do something to all of them (for example set them all to 5):

For k = 1 to 10
myarray(k) = 5
Next k

'This is just a quick example. Obviously there are many many other uses for arrays. Another cool feature is Dynamic arrays.
If you have any more questions just let me know!!

 

[Bowers74]

This comes right out of Excel VBA's Help File for "Using Arrays":

You can declare an array to work with a set of values of the same data type. An array is a single variable with many compartments to store values, while a typical variable has only one storage compartment in which it can store only one value. Refer to the array as a whole when you want to refer to all the values it holds, or you can refer to its individual elements.

For example, to store daily expenses for each day of the year, you can declare one array variable with 365 elements, rather than declaring 365 variables. Each element in an array contains one value. The following statement declares the array variable curExpense with 365 elements. By default, an array is indexed beginning with zero, so the upper bound of the array is 364 rather than 365.

Dim curExpense(364) As Currency

To set the value of an individual element, you specify the element's index. The following example assigns an initial value of 20 to each element in the array.

Sub FillArray()
Dim curExpense(364) As Currency
Dim intI As Integer
For intI = 0 to 364
curExpense(intI) = 20
Next
End Sub

Changing the Lower Bound
You can use the Option Base statement at the top of a module to change the default index of the first element from 0 to 1. In the following example, the Option Base statement changes the index for the first element, and the Dim statement declares the array variable curExpense with 365 elements.

Option Base 1
Dim curExpense(365) As Currency

You can also explicitly set the lower bound of an array by using a To clause, as shown in the following example.

Dim curExpense(1 To 365) As Currency
Dim strWeekday(7 To 13) As String

Storing Variant Values in Arrays
There are two ways to create arrays of Variant values. One way is to declare an array of Variant data type, as shown in the following example:

Dim varData(3) As Variant
varData(0) = "Claudia Bendel"
varData(1) = "4242 Maple Blvd"
varData(2) = 38
varData(3) = Format("06-09-1952", "General Date&quot

The other way is to assign the array returned by the Array function to a Variant variable, as shown in the following example.

Dim varData As Variant
varData = Array("Ron Bendel", "4242 Maple Blvd", 38, _
Format("06-09-1952", "General Date&quot)

You identify the elements in an array of Variant values by index, no matter which technique you use to create the array. For example, the following statement can be added to either of the preceding examples.

MsgBox "Data for " & varData(0) & " has been recorded."

Using Multidimensional Arrays
In Visual Basic, you can declare arrays with up to 60 dimensions. For example, the following statement declares a 2-dimensional, 5-by-10 array.

Dim sngMulti(1 To 5, 1 To 10) As Single

If you think of the array as a matrix, the first argument represents the rows and the second argument represents the columns.

Use nested For...Next statements to process multidimensional arrays. The following procedure fills a two-dimensional array with Single values.

Sub FillArrayMulti()
Dim intI As Integer, intJ As Integer
Dim sngMulti(1 To 5, 1 To 10) As Single

' Fill array with values.
For intI = 1 To 5
For intJ = 1 To 10
sngMulti(intI, intJ) = intI * intJ
Debug.Print sngMulti(intI, intJ)
Next intJ
Next intI
End Sub



Peace!

Mike

Never say Never!!!
Nothing is impossible!!!

 

[mrmovie]

arrays are good but a dictionary (aka hash tables) are faster and you dont have to worry about redim'n them when you want the size to be dynamic. only down side is that each key has to be unique, like a dictionary i guess. you can check that an instance doesnt already exist and if it does append something like %1, %2, %3 to the end but there you go

 

[Naug]

I couldnt find neither dictionary or hash classes in my vba, where do I get em?

 

[Naug]

note to self#1: Dictionary is in Microsoft Scripting runtime

note to self#2: what ever for, would someone call hash a dictionary? thats allmost as bad as calling insert queries "append