Processing Related Data

1. Processing Related Data • If the Toy number is given, a long, complex decision can be used to assign Toy name: If ToyID = “1” Then Toy = “Bean bag toys” ElseIf ToyID = “2” Then Toy = “Kids’ books” : : ElseIf ToyID = “14” Then Toy = “Footballs” Else Toy = “Basketballs” End If

2. Processing Related Data • For a small number of items, using a nested If or Select Case to determine the desired toy given the Toy ID may not seem too bad. • What happens if we have 100 or 1000 or 20000 toys? • A case for arrays: • Data for arrays is typically entered by the user or is already stored in an external file or database. • Once the data is loaded into the Toy() array, the toy name can be retrieved through a single statement (assume ToyID has been set to the correct number): SelectedToy = Toy (ToyId)

3. Array Definitions • A group of related, contiguous memory locations with the same name and type. • Also called tables or subscripted variables • Subscript • Used to reference a specific array element by specifying the amount of offset (from the first element) • Appear between parentheses, e.g. 12 in Qz(12) • Like ListIndex property of list controls • Element • Refers to a single element of the array at the specified subscript location

4. Declarations: Static Arrays • Declaring one-dimensional, static arrays • {Private | Dim} MyArray(Lsub To Usub) As Type • If Lsub is not specified, it is assumed to be zero • Describes a column of data at locations Lsub to Usub

5. Manually Populating the Toy Array Private fToy(1 To 15) As String Private Sub Form_Load() fToy(1) = “Bean bag toys” fToy(2) = “Kids’ books” fToy(3) = “Kids’ software” fToy(4) = “Bicycles” fToy(5) = “Baseballs” fToy(6) = “Mitts” fToy(7) = “Bats” fToy(8) = “Board games” fToy(9) = “Water toys” fToy(10) = “Dress-up gear” fToy(11) = “Crayons” fToy(12) = “Paper supplies” fToy(13) = “Virtual pets” fToy(14) = “Footballs” fToy(15) = “Basketballs” End Sub

6. Dynamic Arrays • Declaring static arrays reserves a fixed number of memory locations, which may be much more or much less than what you typically need. • Dynamic arrays can expand and shrink as needed during execution. • Use UBound and LBound to keep track of the size of the array at any point during execution.

7. Working with Dynamic Arrays • Declaration {Private | Dim} MyArray() As Type • Initialization Private Sub Form_Load ReDim MyArray(0 To 0) ‘ unused array element End Sub • Addition LastSub = UBound(MyArray) + 1 ReDim Preserve MyArray(0 To LastSub) MyArray(LastSub) = NewValue • Deletion If UBound(MyArray) > 0 Then LastSub = UBound(MyArray) - 1 ReDim Preserve MyArray(0 To LastSub) ElseCall MsgBox(“There is nothing to delete -- array is empty.”) End If

8. Avoiding Subscript Errors • An attempt to use a nonexistent subscript results in a subscript out of range error. • Example: Assume that the array Test() has elements at subscripts 1 through 5 • A reference to Test(0), Test(6), Test(x), where x is 10, all result in a subscript out of range error • UBound function for above Test array • UBound(Test) returns 5 • LBound function for above Test array • LBound(Test) returns 1

9. For Next Loop to Process Array Set Index = FirstSub False Index > LastSub? True Steps that access MyArray(Index) Steps to process after loop ends Index = Index + 1 • Have index vary through all subscripts to do any of the following: • Input values into each array element • Display the content of each array element • Assign each array element a fixed value • Assign each array element a value that varies by its index ‘ Use UBound & LBound to prevent errors: FirstSub = LBound(MyArray) LastSub = UBound(MyArray) ‘ Process all array elements: ForIndex = FirstSubToLastSubStep1 steps that access MyArray(Index) NextIndex steps to process after loop ends

10. For Each/Next Statement False Process all elements? True Steps that access MyArray via ElemName Steps to process after loop ends • Like For/Next loop, but you don’t have to write code to go through all the subscripts ‘ Declare element index Dim ElemName As Variant ‘ Process all array elements: For EachElemNameInMyArray steps that access ElemName NextElemName steps to process after loop ends

11. Multi-dimensional Static Arrays • A group of related, contiguous memory locations with the same name and type with multiple subscripts required to reference a single cell. • Two-dimensional array has a dimensionality of 2 • Two subscripts are required to reference a specific element • Example {Private | Dim} MyArray(L1 To U1, L2 To U2) As Type • If L1 or L2 are not specified, they are assumed to be zero • Dim StQuizzes(1 To 95, 1 To 10) As Single • The 95 rows represents 95 students • The 10 columns represent 10 different quizzes • StQuizzes(25,7) represents the 25th student’s 7th quiz grade • Three-dimensional array: rows, columns & planes • Dimensionality of 3 requires 3 subscripts for single element

12. Two-dimensional Array Dim SeatChart(2,3) As String allocates a 3 x 4 grid of string elements named SeatChart

13. User-defined Data Types • Every element in a multi-dimensional array must be the same type. • The case for user-defined types • Allow storage of multiple sets of related information of different types as a single unit • Must be in the General Declarations section of code window • Can be procedure/function argument as long as passed ByRef • Cannot use For Each with arrays of a user-defined type • Type statement & related declaration {Public|Private} Type MyType FieldName1 As Type FieldName2 As Type : End Type Dim VarName As MyType‘ simple variable of MyType Dim MyArray() As MyType‘ dynamic array of MyType

14. User-defined Data Type Example • User-defined type for storing student data Private Type SinfoType Name As String Class As Integer Quiz(1 To 10) As Single Grade As String End Type Dim Sinfo(1 To 100) As SinfoType ‘ 1-column table • Assigning values to elements of user-defined type array Sinfo(5).Name = “John Doe” Sinfo(5).Class = 5 Sinfo(5).Quiz(3) = 7.5 Sinfo(5).Grade = “C” Sinfo(100) = Sinfo(5) ‘ copies John Doe’s info to 100th cell

15. Array LookUp (Sequential Search) Set Index = StartSub MyArray(Index) = SearchVal Or Index >= EndSub False Index = Index + 1 True False True MyArray(Index) = SearchVal Not Found Actions Found Actions • Use a loop to vary through all array elements in sequence until a match is found or the last element is reached.

16. Array Example - Toy Store

17. Toy Store Example:Form-scope Declarations Option Explicit Private Type ToyType ID As Byte Name As String Cost As Currency Qty As Integer End Type Private fToy(1 To 15) As ToyType Private fSearchToyID As Byte Private fTotalToys As Integer Private fTotalPurchase As Currency

18. Toy Store Example: Form Load Private Sub Form_Load() Dim Index As Integer fToy(1).Name = "Bean bag toys" fToy(1).Cost = 5.95 ‘ replace with manual assignments to elements 2-14 fToy(15).Name = "Basketballs" fToy(15).Cost = 21.17 For Index = 1 To 15 Step 1 fToy(Index).ID = Index fToy(Index).Qty = 25 Next Index fTotalToys = 0 fTotalPurchase = 0 End Sub

19. Toy Store Example: Lookup ID Private Sub cmdLookUp_Click() Dim Index As Byte If ToyIDValid Then fSearchToyID = CByte(txtToyID.Text) Index = 1 Do Until fToy(Index).ID = fSearchToyID Index = Index + 1 Loop If fToy(Index).ID = fSearchToyID Then ‘ do Found processing lblToyName.Caption = fToy(Index).Name If fToy(Index).Qty = 0 Then Call MsgBox("No " & fToy(Index).Name & " in stock.", _ vbOKOnly + vbInformation) cmdAdd.Enabled = False Else cmdAdd.Enabled = True End If Else ‘ do Not Found processing Call MsgBox("No such Toy ID.", vbOKOnly + vbInformation) cmdAdd.Enabled = False End If End If End Sub

20. Toy Store Example: Add to Cart Private Sub cmdAdd_Click() Dim Qty As Integer If QtyValid Then Qty = CInt(txtQty.Text) fTotalToys = fTotalToys + Qty fToy(fSearchToyID).Qty = fToy(fSearchToyID).Qty - Qty fTotalPurchase = fTotalPurchase + fToy(fSearchToyID).Cost * Qty cmdShow.Enabled = True End If End Sub