Arrays

1. Arrays

2. What is an Array? • An array is a way to structure multiple pieces of data of the same type and have them readily available for multiple operations • In this way they resemble Excel ranges • As with ranges, you can perform operations on an array in an efficient way using loops

3. Why use arrays when you have ranges? • You might not want to do the calculations on a sheet in the workbook; the array is self-contained and you don’t risk wiping out other data or revealing intermediate computations to the user • It can be very much faster to manipulate data in an array rather than in a range, especially if you are dealing with large amounts of data • You might want three or more dimensions

4. VBA Arrays • VBA arrays are declared much like variables: Dim name As String ‘creates a normal variable DimnameArray(20) As String ‘creates an array • The second Dim creates an array of 20 elements, numbered from 1 to 20, similar to part of a row or column • You can optionally set your module to start numbering at 0 instead of 1; this is better for some kinds of code, but we’ll stick with the default

5. Alternate Array Declarations • Instead of using the simplest form of declaration, as on the previous slide, you can use DimtestArray(1 To 20) As Double • Or use a different lower bound: DimanotherTestArray(1955 To 2020) As String

6. Arrays vs Variables • A variable is a place to store a value; a place in the computer’s memory • You reference the memory location by using the name of the variable • An array stores a sequence of values • You reference an element by using the array name and an index

7. Arrays vs Variables Variable num of type Double occupies some location in memory 3.2 num 1.3 Array numArray of type Double has 6 elements and occupies 6 times the memory of one variable of type Double. Here, numArray(3) has value 3.5. 1 2.4 2 3.5 3 4.6 4 5.7 5 6.8 6 numArray

8. Things to watch out for with arrays • You must be careful not to try to put more items in the array than it will hold (overflow). Doing so should cause a runtime error but may instead just result in mysterious bugs • You should not try to index beyond the end of an array. This should also cause a runtime error, but might just result in getting a strange answer back.

9. Array Size to Use • Try to make your array big enough to handle any reasonable expectation of how your program will be used • In many languages you are out of luck if you guess wrong and run out of space in your array. In VBA though we have the ReDim feature

10. ReDim • This is a very unusual feature of VB • It lets you change the size of an array while the program is running! • Example: ReDim Preserve exampleArray(1 TonewSize) • Without the Preserve, all the data in the array is lost when you ReDim.

11. Dynamic Arrays • You can also declare an array without giving it an initial size. This is called a dynamic array. (The empty parens tell VBA it’s an array) Dim testDynamic() As String • Later in the program, when you know how big you need it to be, you can use ReDim to set the size: ReDimtestDynamic (1 to size) • [this slide used info from www.exceltip.com]

12. More Dimensions • We’ve been looking at one-dimensional arrays, but an array can have two (or more!) dimensions • This is the array version of nesting. In some languages you literally have an array of arrays, but VBA uses more dimensions to achieve the same effect • Example: DimarrayName (1 To 3, 1 To 4) As Integer arrayName(1, 2) = 20

13. Showing the whole array DimarrayName (1 To 3, 1 To 4) As Integer arrayName(1, 2) = 20 ‘row 1, column 2 1 2 3 4 1 2 3

14. The ArrayRangeDemo • You will find most of the code in this presentation in the workbook called ArrayRangeDemo.xlsm • Indexing is important when working with both arrays and ranges • The demo illustrates indexing in two dimensional arrays and ranges; since it is easiest to see what is happening with ranges, most of the code modifies them

15. Copying A Range to an Array • This is something you might want to do if you are going to do extensive computations on the values in the range • It is possible to do this directly with one statement, if everything is declared just right (for info see msdn.microsoft.com/en-us/library) • We’ll do it cell by cell using a nested loop

16. Declarations • Here is our array declaration: Const DIM1 As Integer = 8 'length Const DIM2 AsInteger = 10 'width Dim demo2DArray(1 To DIM1, 1 To DIM2) As Double • And the range: DimtwoDAreaAs Range 'global SubWorkbook_Open() SettwoDArea = Range(Cells(1, 1), Cells(DIM1, DIM2)) End Sub

17. Copy Range to Array SubRangetoTwoDArray() Dim rowNdxAs Integer, colNdxAs Integer ForrowNdx = 1 To DIM1 ForcolNdx = 1 To DIM2 demo2DArray(rowNdx, colNdx) = Cells(rowNdx, colNdx).Value NextcolNdx NextrowNdx End Sub

18. Row vs Column • For some reason, in a two-dimensional array or range, it is customary to have the outer loop go row by row and the inner loop go across the columns • The next few slides show you this method and also how to go column by column instead

19. Filling the Range by Rows SubFillRangeByRow() DimrowNdxAs Integer, colNdxAs Integer, count As Integer count = 1 ForrowNdx = 1 To DIM1 ForcolNdx = 1 To DIM2 Cells(rowNdx, colNdx) = count count = count + 1 NextcolNdx NextrowNdx End Sub

20. The Result Note how the numbers increase across the rows first.

21. Filling the Range by Columns SubFillRangeByColumn() Dim rowNdxAs Integer, colNdxAs Integer, count As Integer count = 1 ForcolNdx = 1 To DIM2 ForrowNdx = 1 To DIM1 Cells(rowNdx, colNdx) = count count = count + 1 Next rowNdx NextcolNdx End Sub

22. The Result Note how the numbers increase down the columns first

23. Using Random Data • The demo has examples of filling the range (or array) with random data, either the basic random numbers which range between 0 and 1, or a modified version that generates random numbers between 1 and 6 • It also includes code for doing some data manipulation while the data is in the array; give it a try!