Ch9 - CP212

1. Ch9 - CP212 Slides updated Feb 6, 2013

2. A Product Mix Application • brief look at a complex application • Chapter 21 - Product Mix.xlsm • Chapter 20 deals with a Blending Application - VBA is simpler (most done ahead of time, lots of charts) • uses linear programming • Illustrates custom made wood furniture

3. Resources • Labour Hours for Senior Woodworkers • Labour Hours for Junior Wood Workers • Machine Hours • Wood (measured in board feet) • Oak • Cherry Different products can be made from each type of wood: • End tables, Rocking chairs, Coffee tables, Desks, Bookshelves

4. Objective Maximize profit: revenues - costs Constraints • Each product has a constraint on MAX/MIN production levels Open Product Mix.xlsm

5. Arrays • basic data structure used to organize lists • known as Lists in Python but with limited operations • each element is accessed by its index • index can be base 0 or base 1, base 0 is default. • Base 1 may seem more natural, but it is uncommon in programming • Visual Basic .Net does not have the option for Base 1, so if you are moving up, don't get used to it.

6. Arrays - Like Mailboxes, but different • used to store values of the same data type (here, String) • referenced by index number Sub ArraysChapter9() ' Declare an array of 6 pets Dim pets(5) As String Dim pet As Variant 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 End Sub

7. Sales Regions Example See Transactions Finished.xlsm

8. Dim myArray(n) AsdataType Declares an array with n+1 elements. Using Base 0 arrays: Dim scores(99) gives us 100 elements Dim scores(100) gives us 101 elements Sub Sample() Dim scores(100) As Byte     scores(100) = 5 ' Legal MsgBox "scores(100) = " & scores(100)     scores(101) = 5 ' Illegal End Sub

9. More arrays • can specify arrays with the values • Dim scores() As Variant • scores = Array("4", "5", "6", "7", "2") • or • Dim daysOfTheWeek() As Variant • daysOfTheWeek = Array("Mon", "Tue", "Wed", "Thu", "Fri", "Sat", "Sun") • need to use Variant in this case

10. Rules with Arrays • different ways of declaring arrays • Dim pets(0 to 5) As String • dynamic arrays • Dim pets() As String' No size yet • Can resize with ReDim • Redim pets(newSize) ' or Redim pets(0 to newSize) • redim will delete the existing values in the array

11. Rules of Arrays (cont'd) • Dynamic arrays can be resized at runtime • need to preserve the values during resize? • ReDim Preserve pets(newSize) or • ReDim Preserve pets(0 to newSize) • Cannot resize static arrays •  Dim pets(5) As String • ReDim Preserve pets(6) will give an error message • can't redim arrays that have fixed size at compile time

12. Board Code Sub staticArraysExample()  Dim cars(3) As String     cars(0) = "Lynx"     cars(1) = "Tiger"     cars(2) = "Wombat"     cars(3) = "Ptarmigan"     ' cars(4) = "Ermine" ' FAIL!     ' ReDim Preserve cars(5) ' FAIL! End Sub

13. Sub dynamicArraysExample()     Dim animals() As String     Dim msg As String     Dim i As Byte  ' Resize the array at runtime     ReDim animals(3)     animals(0) = "Lynx"     animals(1) = "Tiger"     animals(2) = "Wombat"     animals(3) = "Ptarmigan"     'animals(4) = "Stoat" ' FAIL! ' Resize to add more.     ReDim Preserve animals(5)     animals(4) = "Stoat"     animals(5) = "Dragon"     For i = 0 To 5         msg = msg & animals(i) & vbCrLf     Next i     MsgBox "The animals are now: " & vbCrLf & msg, vbInformation, "Dynamic Array Resized" End Sub

14. LBound, UBound(UBound on page 194) • Join And Split (not in text) • See Chapter9Example files regarding LBound, UBound and Join & Split • Multidimensional arrays (page 158)

15. Looking Up a Price • could easily be done with vlookup() - recommend you learn it • See Unit Prices.xlsm • Find the number of items • redim the arrays to the proper size • Loop through column A (in an array), and as a new product is found, store it in an array called productCodes and increase the number of unique productCodes • Do the same for unitPrices • Find the product in the product array and display its unit price

16. Code Highlights

17. Travelling Sales Person • a common algorithm used in computer science • a basic solution is called the "nearest-neighbour heuristic" • play with the spreadsheet, run it a few times • examine the code • nothing new, but the algorithm may be complex • simple to understand, difficult to code

18. Merging Lists • two lists: last year's customers and this year's customers • want to make one list of customers from both years • no duplicates • merge, not append • think of a manual solution first • its the only way you can code one • write it out (pseudocode, comments)

19. Merging (cont'd) • Start at the top of each list and compare names • if they are the same, put the name in column D • then move to the next item in both columns • if name in col A comes before name in col B, put it in col D and move down to the next item in col A (but not B) • similar if name in B comes before name in A • Continue until all names from one column have been transferred • then just copy over the rest of the names in the remaining column Now, can you code it?

20. Merging - Useful Code

21. Fill a Range from an Array (Array Fill) • much quicker than looping Dim adblTempArray() As Double Dim rngTheRange As Range ' Array gets filled with lots of values ' Range is defined to be same size as # of values in array ' (or less) ' Place all values in the array in each cell in the range rngTheRange.Value = adblTempArray (see loop fill vs. array fill.xlsm in MyLS)

22. Fill a Range from an Array - Transpose The ‘shape’ (horizontal or vertical) of an array and the range it is copied to must match. Dim values (1 To 10) As Integer … fill array Range(“A1:J1”) = values Works as expected. To put this horizontal array into a vertical column requires transposing the values: Range(“A1:A10”) = Application.Transpose(values)

23. Fill Array from a Range Dim values(10) As Integer values = Range(“A1:J1”) defines a row spanning 10 columns. Dim values(10,0) As Integer values = Range(“A1:A10”) defines a column spanning 10 rows Because it is 2D, items are accessed with: For i = 1 To 10 values(i,0)