Arrays 101

[Aussiebear]

I just thought its about time we started an Arrays 101 type discussion. Those of you who would like to contribute please feel free to do so...

Arrays are, in a broad sense, a collection of data within an range. Most of us would recognise a similar pattern say Months of the year, Days of the week, set results for a sports function. But arrays are more than this. They are in fact a series of data, either in a singular range, or within a column or row (2 dimensional) or even within a multicolumn, Multi row and multi sheet (3 dimensional).

What I'm looking for here is contributions which encourage users to experiment with arrays. If you wish to put forward a concept please include an example for others to follow.

[snb]

Good initiative !

Are you familiar with http://www.snb-vba.eu/VBA_Arrays_en.html ?

[Aussiebear]

No I wasn't however this is a good source to fire up the conversations. Besides we ae going to have to get you over this "sn" habit to make it more readable won't we!

[SamT]

You mean like he did in format all files in a folder (http://www.vbaexpress.com/forum/showthread.php?52649-format-all-files-in-a-folder/page2) Posts #52 & #53.
Buwahahahahaha. Good luck.

Just think of him as a visiting instructor from VBA 501, and we are mere tutors for VBA 101

I having a hard time wrapping my head around transferring data between Ranges and Arrays.

Given an array and two Ranges:
Array(5, 3)
Range("A1:C5")
Range("A1:E3")

[Aflatoon]

Given a fixed size array, you have to loop:

Sub foo() Dim myArray(1 To 5, 1 To 3)
Dim x As Long
Dim y As Long
Dim myRange As Range


Set myRange = Range("A1:C5")


For x = 1 To myRange.Rows.Count
For y = 1 To myRange.Columns.Count
myArray(x, y) = myRange.Cells(x, y).Value
Next y
Next x
End Sub
Sub foobar()
Dim myArray(1 To 5, 1 To 3)
Dim x As Long
Dim y As Long
Dim myRange As Range


Set myRange = Range("A1:E3")


For x = 1 To myRange.Rows.Count
For y = 1 To myRange.Columns.Count
myArray(y, x) = myRange.Cells(x, y).Value
Next y
Next x
End Sub


However it is usually better to simply use:

Sub bar()
Dim myArray

myArray = Range("A1:C5").Value
End Sub

[snb]

Another 'simple' method:


Sub M_snb()
Set myRange = Range("A1:C5")

ReDim sn(1 To myRange.Rows.Count, 1 To myRange.Columns.Count)
For j = 1 To UBound(sn) * UBound(sn, 2)
sn((j - 1) \ UBound(sn, 2) + 1, (j - 1) Mod UBound(sn, 2) + 1) = myRange.Cells(j).Value
Next

' Cells(20, 1).Resize(UBound(sn), UBound(sn, 2)) = sn ' only to check...
End Sub
or

Sub M_snb()
Set myRange = Range("A1:C5")

ReDim sn(myRange.Rows.Count - 1, myRange.Columns.Count - 1)
For j = 0 To (UBound(sn) + 1) * (UBound(sn, 2) + 1) - 1
sn(j \ (UBound(sn, 2) + 1), j Mod (UBound(sn, 2) + 1)) = myRange.Cells(j + 1).Value
Next

' Cells(20, 1).Resize(UBound(sn) + 1, UBound(sn, 2) + 1) = sn
End Sub

[Kenneth Hobs]

Sorting arrays would be a good topic. SNB has some good short examples using vb.net framework methods.

Just a bit more can be added such as unique and reverse or ascending sorts can be added. This was discussed some in: http://www.vbaexpress.com/forum/showthread.php?48491

Class 201 might involve the more advanced muti-dimensional methods.


Of course Chip Pearson has some excellent array routines at: http://www.cpearson.com/excel/vbaarrays.htm

This transpose routine is interesting: ' AFlatoon, http://www.vbaexpress.com/forum/showthread.php?47629-Fastest-way-to-temporarily-store-results-for-further-analysis

Some decent routines by Alan Beban, copyright 2000 so I don't use them or repost his code: http://home.pacbell.net/beban/
and his file: http://filedb.experts-exchange.com/incoming/2011/09_w40/505534/ArrayFunctions.xls