PDA

View Full Version : arrays



kwaku101
11-15-2008, 10:32 AM
Can someone explain arrays in its simplistic form or concept. I'm new to excel vba and have difficulty with arrays, especially using them with loops....also do arrays always correspond to columns and loops to rows?

Thanks in advance, your help is much appreciated.

lucas
11-15-2008, 10:45 AM
Just to get this conversation started.........

From the help file:



The required arglist argument is a comma-delimited list of values that are assigned to the elements of the array contained within the Variant.


You can create the list like this:

Sub InsertHeaderRow()
Dim myarray As Variant
myarray = Array("Heading 1", "Heading 2", "Heading 3", "Heading 4", "Heading 5")
Range("a1:e1").Value = myarray
MsgBox myarray(2)
End Sub


which will put a header row in the active sheet and return in a messagebox the index number 2 from the array. Notice when you run it that it returns "Heading 3"

That is because array index begins with 0 not 1. This is important and I believe you can change it to begin with 1 by using option base 1 at the top of the module......don't see the reason to do that though as long as you understand that it begins with 0 unless you address that issue.

Oorang
11-15-2008, 10:46 AM
Ok first you have a variable. Think of a variable as a cell where you store a value in memory. Give the cell a name, "MyVariable". Store data in it MyVariable=1, retrieve data from it, MsgBox MyVariable. Simple.
Well an array is like a variable, but instead of being a cell, it's a column. (Hence the name, it's a vast array of variables:).) You can store data in an array just like you do a variable you just have to specify the "row". MyVariable(10) = 1 put the data in row 10, MyVariable(9) = 2 puts 2 in row 9 and so on.
The only other thing to look out for is to make sure the row you try to access exists before you try to use it. You will see this as "Dim" and "Redim" this just a way of specifying how many rows your array has. Dim MyVariable(5) As String says I have a column of strings that has rows 0 through 5 or 6 total cells.
To use an array in a loop, you just set up a basic for loop:
For i = 0 to 5
Msgbox MyVariable(i)
Next

As you might already know, this loop will start at 0 and each time "Next" is hit, i will have 1 added to it, and the code will return to the line that has the "For" statement. It will keep doing this until "i" is greater than 5. So when you message box MyVariable(i) the first time i has a 0 so you are messaging row 0 then it will loop and i will be 1 and thus row 1 gets messaged. And so on.

There is more of course but that's the basics, and you can do quite a lot with them. Post back with questions:)

david000
11-15-2008, 09:05 PM
I personally never found the excel help files very helpful with the overall topic of arrays. One thing that moved me along faster than anything was learning that the dimensions or shape of the array as it corresponds to the actual spreadsheet.

Particularly the Ubound function as in this example:


Sub test()

Dim arr() As Variant, row, col As Long

arr = Range("A1:C10").Value

For row = 1 To UBound(arr, 1) 'Rows down then a comma then, "1" that it is the first dimension.

For col = 1 To UBound(arr, 2)
'Columns across then a comma then a, "2" that it is the second dimension
'(even though it is three or more columns it is still the second dimension).
'That took me the longest time to get my head around!

arr(row, col) = arr(row, col) + 1

Next col

Next row

Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr

End Sub

Bob Phillips
11-16-2008, 03:24 AM
One thing you should never do with arrays is to assume that you know where the lower bound starts at, 0 or 1 , it is totally unnecessary as you can use LBound(array,dimension)

Oorang
11-29-2008, 02:12 PM
Glad to see that we are making progress:) A few quick tips: you should avoid using UBound inside a loop. It will work, but your program will run faster if you load the value into a variable and then use it. Also, if you are going to declare multiple variables on a line, you must give each an "As MyType" or it will be created as a variant. (For example, the variable "row" is actually created as variant not a long in the code you posted.)
Sub test()
Dim arr() As Variant, row as Long, col As Long, uprbnd As Long
arr = Range("A1:C10").Value
uprbnd = UBound(arr, 2)
For row = 1 To UBound(arr, 1) 'Rows down then a comma then, "1" that it is the first dimension.
For col = 1 To uprbnd
arr(row, col) = arr(row, col) + 1
Next col
Next row
Range("A1").Resize(UBound(arr, 1), UBound(arr, 2)) = arr
End Sub

malik641
11-29-2008, 03:38 PM
While we are mentioning speed, I would recommend avoiding Redim as much as possible. It is usually used inside loops when people don't know what the size of the array. The problem is that it causes a lot of overhead if used often. I'm willing to bet that most times you can have 2 loops where one would be for counting how many elements you will need, then declare the array with that number, then do the same loop assigning the values to the array... I have a feeling people will disagree with that, but try Redimming an array 5000 times and try declaring an array with 5000 elements and assigning the values. It is way faster to use the latter even with using the same loop twice in most situations in my opinion.

Bob Phillips
11-29-2008, 06:36 PM
I'll disagree!

If you want to avoid redimming continuosly, set it to an arbitrary high value. If it goes above, set it tice as big and so on. At the end, reset it to its true value.

malik641
11-30-2008, 08:55 AM
Yeah, that's definitely another way. I wonder how well it would handle scalability, though. But for the most part I can see that being a better solution. Thanks for the idea! :)

Oorang
12-01-2008, 08:51 AM
Well I think you both are right :) It is way faster to redim in large increments, but if you multiply the value, as Malik mentioned, you could get into trouble. I usually redim in fixed increments. Such 1,000 or 10,000 as the case may merit. In this way you prevent exponential growth.

CreganTur
12-01-2008, 02:27 PM
But don't the empty indexes of an array still use memory? I would imagine that the memory an empty index uses is nothing compared to the memory used during large ReDim processes (or the memory used by a filled index), but still if you can avoid wasted memory...

@kwaku

also do arrays always correspond to columns and loops to rows?
Arrays have dimensionality.

An array created using the Array method (shown in post 2) is a single dimension array. Think of it as a single column with multiple rows.

There are 2 dimensional arrays- think of these as multiple columns and multiple rows. The following code is a good example of a basic 2 dimensional array:
Sub Exchange()
Dim t As String
Dim r As String
Dim Ex(3, 3) As Variant
t = vbTab
r = vbCrLf
Ex(1, 1) = "Japan"
Ex(1, 2) = "Yen"
Ex(1, 3) = 102.76
Ex(2, 1) = "Europe"
Ex(2, 2) = "Euro"
Ex(2, 3) = 0.744734
Ex(3, 1) = "Canada"
Ex(3, 2) = "Canadian Dollar"
Ex(3, 3) = 1.20892
MsgBox "Country" & t & t & "Currency" & t & t & "Value per US$" _
& r & r _
& Ex(1, 1) & t & t & Ex(1, 2) & t & t & Ex(1, 3) & r _
& Ex(2, 1) & t & t & Ex(2, 2) & t & t & Ex(2, 3) & r _
& Ex(3, 1) & t & t & Ex(3, 2) & t & Ex(3, 3), , "Exchange"
'added from LBound and UBound section
MsgBox "The lower bound (first dimension): " & LBound(Ex, 1) & vbCrLf _
& "The upper bound (first dimension): " & UBound(Ex, 1) & vbCrLf _
& "The lower bound (second dimension): " & LBound(Ex, 2) & vbCrLf _
& "The upper bound (second dimension): " & UBound(Ex, 2)
End Sub

Arrays can have many dimensions- these are called multi-dimensional arrays. The most common type is an array with 3 dimensions. Think of them as multiple tables that contain multiple columns, which in turn contain multiple rows.

The loops you're referring to are just ways of sorting through the records in the arrays so you can perform some action on/with them.

HTH:thumb

Oorang
12-01-2008, 03:18 PM
For the OP's benefit, you can think of 1 dimensional array as a list, 2 dimensional as table, 3 as a cube, 4 as a table of tables, 5 as a cube containing tables, 6 as a cube of cubes and so on (thanks to Professor Larry Lazofson for that explanation.. see I was too paying attention).

As for memory consumed, redim preserve will consume an amount of memory equal to the current size of the array + the target size of the array (making the "double it" approach only slightly better). Wheras the "large increment" should (if I am not mistaken) only increase the size of the array (assuming a string array) by 6 Bytes times the number of new elements. To give a comparison, a Long is 4 bytes and a double is 8. I don't think you will run any danger of chewing up too much memory as the superflous amount is always less than or equal to the 6*increment bytes.

Digita
12-02-2008, 05:16 PM
Hi guys,

On the topic of array, I have a question. Let me go back 1 step by using the example given by Lucas.


myarray = Array("Heading 1", "Heading 2", "Heading 3", "Heading 4", Heading 5")
Range("a1:e1").Value = myarray


The above is a single dimensional array. Now, when I load myarray with the values from range A1:E1.


myarray = Range("a1:e1").Value


The array would become 2 dimensional.

Similarly, if we load the values from a range say Range("a1:a5").Value = Application.Transpose(myarray), without using for each .... in myarray we reference the array elements by using For i = 1 To UBound(myarray, 1)
Debug.Print myArray(i, 1)
Next

Again, we have only 1 column of data. The array containing the data is a 2 dimensional one. Why?

Thanks & regards

david000
12-03-2008, 03:01 AM
I agree with your question Digita.

That was basically my frustration as well, but like Oorang said "...think of 1 dimensional array as a list, 2 dimensional as table", Excel is a 2D table. I regret having to learn that the hard way. I sometimes wonder if Excel is the best place to start learning programming.

Every book I own on this topic tries to drive this point home in one way or another. For example, via Excel VBA in easy steps;

"2-D arrays have one index which denotes the row, and one index which denotes the column, and therefore can be likened to the Excel spreadsheet structure itself."

It's like your playing chess against yourself!

Bob Phillips
12-03-2008, 03:21 AM
... we reference the array elements by using For i = 1 To UBound(myarray, 1)
Debug.Print myArray(i, 1)
Next

You should NEVER assume the bounds of an array, especially as it is so easy not to



For i = LBound(myArray, 1) To UBound(myArray, 1)
Debug.Print myArray(i, 1)
Next


Even if you set the Option Base to 1, some arrays will start and 0, so never presume.

Digita
12-03-2008, 02:57 PM
David000 & Bob,

Thanks for your advice. Have a great day.

Regards


kp

asingh
12-03-2008, 09:42 PM
You should NEVER assume the bounds of an array, especially as it is so easy not to



For i = LBound(myArray, 1) To UBound(myArray, 1)
Debug.Print myArray(i, 1)
Next


Even if you set the Option Base to 1, some arrays will start and 0, so never presume.

I usually set my programs to Option Base 1...is it still possible that the array will/can start at element "0"..?

Bob Phillips
12-04-2008, 01:33 AM
Yep.

Set Option Base to 0 and try this



Dim ary1 As Variant
Dim ary2 As Variant

ary1 = Array(1, 2, 3)
ary2 = Split("4,5,6", ",")
Debug.Print "LBound for ary1 " & LBound(ary1)
Debug.Print "LBound for ary2 " & LBound(ary2)


then set it to 1 and try again

Oorang
12-04-2008, 11:01 AM
The default behavior is set to 0. And while many things catch the option base 1 and adjust, many things don't. IMHO it's generally better to explicit set 0 and stick with it throughout. You will encounter significantly fewer exceptions to the 0 rule than you will the 1 rule. And then you don't have to be quite so paranoid about lower bounds. (Although if you have the time and the inclination, it is generally wise to account for varying lowerbounds.)
Another thing to consider is that VBA is based on VB6 and there may very well come a day when you will need to port your VBA app to .Net. .Net does not support non zero based arrays with invoking a compatability library and sending you through a few hoops. Also many languages don't support anything other than a base 0 array let alone arrays with negative numbers as indexes. This cause difficulty for programmers coming from other languages and if they fail to account for the possibility that someone may be using a non-standard array can cause them to make critical mistakes. So while you can use the feature, it a little self-sabotaging IMO.


A few more gotchas are ParamArrays which will be 0 based regardless of the option set. And even the "Array" keyword will get it wrong if it was prefixed (Ex: VBA.Array(1,2,3) will return a 0 based array regardless of option, wheras just plain "Array" will read the option.)

Bob Phillips
12-04-2008, 11:24 AM
It is not paranoia, it is just good coding disciplines. Why make any assumptions about the base when it is just so simple not to have to?

Oorang
12-05-2008, 04:37 AM
lol Well, I didn't mean it disparagingly. I always support rigor. Perhaps a better way to phrase it would have been: "Forcing option base 0 lowers the price tag for failing be rigorous." :)