CP212 Lab 5 : Arrays

Week of

It is important that you have read through the text chapters on arrays before attempting the lab or at least have an understanding of arrays.

Sub ArraysExample()
    ' Declare an array of 6 pets
    Dim pets(5) As String ' static array
    Dim pet As Variant 'For Each control variables on arrays must be Variant

    Dim zoo() As String ' dynamic array

    pets(0) = "Cat"
    pets(1) = "Dog"
    pets(2) = "Fish"
    pets(3) = "Snake"
    pets(4) = "Lemur"
    pets(5) = "Meerkat"

    ' Loop through the array, though can't extract the index number.
    For Each pet In pets
       MsgBox "The pet is a " & pet
    Next

    ' Resize the zoo array to store 10 items
    ReDim zoo(9)
End Sub

Sorting

We didn't discuss sorting in class but it is quite a large topic when dealing with computer programs and is linked to arrays. Sorting algorithms are a very large area of study. While it is possible to sort ranges of cells in Excel, you might not always be working in Excel (actually, even Word allows you to sort paragraphs) and it would be useful to know about different ways of sorting lists such as the Bubble Sort, Binary Sort, or Quick Sort (and there are many others). These techniques are important when working with arrays.

I'm presenting this file from John Walkenbach - Mr. Spreadsheets himself! - as a demonstration of the timing of various sorts, but have locked the code. If you want, check out his book for the solutions, but a good exercise it to write your own sort, like in Exercise 18b, page 202 of your text (pg 179 in 3rd edition). Sort Demo.xlsm

Searching

Searching for items in an array is also very common. Often the best way to search for an item requires that the list be sorted, so a lot of searching algorithms use data that has already been sorted using one of the methods discussed above. If you are trying to find an item in a range of cells, it may be faster to sort the range first, but in some cases you may not want the data sorted or changed. You either have to deal with poor search performance (ie, starting at the top and checking each item until you find your quarry - called a sequential search) or copy the data to a temporary location - like an array - sort it, then perform the search.

Loop Fill versus Array Fill

One quick note is that using an Excel array fill can be as much as 10x faster (or more!) than looping through an array 1 element at a time and writing the values into a cell. I tested this procedure with a value in 6000 rows and 3 columns and got 10x faster performance using array fill.

For example, if you had a 10x10 array (like a matrix) you can place the values on a worksheet with one command by setting a range of the right size and using the command:

TheMatrixRange.Value = myArray

Check out this example using a 20x10 array: TheMatrix.xlsm. You can see it visually takes longer, but you can add timing to see for yourself.

Remember, that you can easily create an array using the Array function, though there are other techniques: 

Dim weekdayGods As Variant ' Must be variant in this situation
weekdayGods = Array("Sun", "Moon", "Tiw", "Woden", "Thor", "Freya", "Saturn")

Practice

Download the file days.xlsx and use it for the following task. Read the names of the months from column A into an array called months, and the number of days in each month into an array called daysInMonth. On a new worksheet, output the names of the months in the first row of the spreadsheet starting in A1 and the number of days in the months starting at A2. Place a button on the worksheet to run your macro.

Your results should look like this.

Fun

Sometimes I find examples that seem totally pointless, but they often demonstrate some creative thinking in how to use an application. Some examples are found below. Not all require VBA.

Spirograph.xls - A spirograph in Excel! Can you figure out how they did it? Contains no VBA.

Hypocycloid - animated.xls - another one from John Walkenbach, available on his web page: Animated HypoClycloid Charts (may run slowly)

Poker Simulation - from Chapter 34 of the text.

Blackjack.xls - Found on the net; comments and some code written in Portugese

Instructions

Tasks

  1. Download the file Customer_Spending_Rate.xlsx and complete the following task. Open the file and save it as a Macro Enabled Workbook (file extension .xlsm). Name this spreadsheet with your network login followed by _l05.xlsm. For example, if your network login was barn4520, then the file should be named barn4520_l05.xlsm. Remember to save your work frequently in case there are problems.

    Write a subroutine that:

    • Reads the existing two lists and formulate two arrays(listSize 1: size of Customer list name it as list11 , and listSize2: size of AmtSpent list name it as list12).
    • Creates two new arrays list21 and list22 of customer names and and spent amounts for customers who spent at least $500.
    • After these new arrays have been filled, write their contents to columns D and E, respectively in the worksheet.
    • Use a button to access your tasks.
    • Set arrays of amount of money as Currency type.

Note

  • You have to use Button(s) on worksheet to run the tasks in this lab and all next labs and assignments. Marks will be deducted if you do not use Buttons

  • You can use only the covered material in the course till now. You can't use uncovered topics. You can't use topics out of the context of this course.