• At this point, we know how to use the data control and associated data bound tools to access a database. The power of Visual Basic lies in its ability to manipulate records in code. Such tasks as determining the values of particular fields, adding records, deleting records, and moving from record to record are easily done. This allows us to build a complete database management system (DBMS).
• We don’t want to change the example database, BIBLIO.MDB. Let’s create our own database to change. Fortunately, Visual Basic helps us out here. The Visual Data Manager is a Visual Basic Add-In that allows the creation and management of databases. It is simple to use and can create a database compatible with the Microsoft Jet (or Access) database engine.
• To examine an existing database using the Data Manager, follow these steps:
1. Select Visual Data Manager from Visual Basic’s Add-In menu (you may be asked if you want to add SYSTEM.MDA to the .INI file - answer No.)
2. Select Open Database from the Data Manager File menu.
3. Select the database type and name you want to examine.
Once the database is opened, you can do many things. You can simply look through the various tables. You can search for particular records. You can apply SQL queries. You can add/delete records. The Data Manager is a DBMS in itself. You might try using the Data Manager to look through the BIBLIO.MDB example database.
• To create a new database, follow these steps:
1. Select Visual Data Manager from Visual Basic’s Add-In menu (you may be asked if you want to add SYSTEM.MDA to the .INI file - answer No.)
2. Select New from the Data Manager File menu. Choose database type (Microsoft Access, Version 7.0), then select a directory and enter a name for your database file. Click OK.
3. The Database window will open. Right click the window and select New Table. In the Name box, enter the name of your table. Then define the table’s fields, one at a time, by clicking Add Field, then entering a field name, selecting a data type, and specifying the size of the field, if required. Once the field is defined, click the OK button to add it to the field box. Once all fields are defined, click the Build the Table button to save your table.
Tuesday, February 2, 2010
Data Links
• After placing a data control on a form, you set the ConnectionString property. The ADO data control can connect to a variety of database types. There are three ways to connect to a database: using a data link, using an ODBC data source, or using a connection string. In this lesson, we will look only at connection to a Microsoft Access database using a data link. A data link is a file with a UDL extension that contains information on database type.
• If your database does not have a data link, you need to create one. This process is best illustrated by example. We will be using the BIBLIO.MDB database in our first example, so these steps show you how to create its data link:
1. Open Windows Explorer.
2. Open the folder where you will store your data link file.
3. Right-click the right side of Explorer and choose New. From the list of files, select Microsoft Data Link.
4. Rename the newly created file BIBLIO.UDL
5. Right-click this new UDL file and click Properties.
6. Choose the Provider tab and select Microsoft Jet 3.51 OLE DB Provider (an Access database).
7. Click the Next button to go to the Connection tab.
8. Click the ellipsis and use the Select Access Database dialog box to choose the BIBLIO.MDB file which is in the Visual Basic main folder. Click Open.
9. Click Test Connection. Then, click OK (assuming it passed). The UDL file is now created and can be assigned to ConnectionString, using the steps below.
• If a data link has been created and exists for your database, click the ellipsis that appears next to the ConnectionString property. Choose Use Data Link File. Then, click Browse and find the file. Click Open. The data link is now assigned to the property. Click OK.
• If your database does not have a data link, you need to create one. This process is best illustrated by example. We will be using the BIBLIO.MDB database in our first example, so these steps show you how to create its data link:
1. Open Windows Explorer.
2. Open the folder where you will store your data link file.
3. Right-click the right side of Explorer and choose New. From the list of files, select Microsoft Data Link.
4. Rename the newly created file BIBLIO.UDL
5. Right-click this new UDL file and click Properties.
6. Choose the Provider tab and select Microsoft Jet 3.51 OLE DB Provider (an Access database).
7. Click the Next button to go to the Connection tab.
8. Click the ellipsis and use the Select Access Database dialog box to choose the BIBLIO.MDB file which is in the Visual Basic main folder. Click Open.
9. Click Test Connection. Then, click OK (assuming it passed). The UDL file is now created and can be assigned to ConnectionString, using the steps below.
• If a data link has been created and exists for your database, click the ellipsis that appears next to the ConnectionString property. Choose Use Data Link File. Then, click Browse and find the file. Click Open. The data link is now assigned to the property. Click OK.
Creating a Virtual Table
• Many times, a database table has more information than we want to display. Or, perhaps a table does not have all the information we want to display. For instance, in Example 8-1, seeing the Title and ISBN of a book is not real informative - we would also like to see the Author, but that information is not provided by the Titles table. In these cases, we can build our own virtual table, displaying only the information we want the user to see.
• We need to form a different SQL statement in the RecordSource property. Again, we won’t be learning SQL here. We will just give you the proper statement.
Quick Example: Forming a Virtual Table
1. We’ll use the results of Example 8-1 to add the Author name to the form. Replace the RecordSource property of the dtaTitles control with the following SQL statement:
SELECT Author,Titles.ISBN,Title FROM Authors,[Title Author],Titles WHERE Authors.Au_ID=[Title Author].Au_ID AND Titles.ISBN=[Title Author].ISBN ORDER BY Author
This must be typed as a single line in the Command Text (SQL) area that appears when you click the ellipsis by the RecordSource property. Make sure it is typed in exactly as shown. Make sure there are spaces after ‘SELECT’, after ‘Author,Titles.ISBN,Title’, after ‘FROM’, after ‘Authors,[Title Author],Titles’, after ‘WHERE’, after ‘Authors.Au_ID=[Title Author].Au_ID’, after ‘AND’, after ‘Titles.ISBN=[Title Author].ISBN’, and separating the final three words ‘ORDER BY Author’. The program will tell you if you have a syntax error in the SQL statement, but will give you little or no help in telling you what’s wrong.
Here’s what this statement does: It selects the Author, Titles.ISBN, and Title fields from the Authors, Title Author, and Titles tables, where the respective Au_ID and ISBN fields match. It then orders the resulting virtual table, using authors as an index.
2. Add a label box and text box to the form, for displaying the author name. Set the control properties.
Label3:
Caption - Author
Text1:
DataSource - dtaTitles (select, don’t type)
DataField - Author (select, don’t type)
Locked - True
Name - txtAuthor
Text - [Blank]
When done, the form should resemble this:
3. Save, then rerun the application. The author’s names will now appear with the book titles and ISBN values. Did you notice you still haven’t written any code?
I know you had to type out that long SQL statement, but that’s not code, technically speaking. Notice how the books are now ordered based on an alphabetical listing of authors’ last names
• We need to form a different SQL statement in the RecordSource property. Again, we won’t be learning SQL here. We will just give you the proper statement.
Quick Example: Forming a Virtual Table
1. We’ll use the results of Example 8-1 to add the Author name to the form. Replace the RecordSource property of the dtaTitles control with the following SQL statement:
SELECT Author,Titles.ISBN,Title FROM Authors,[Title Author],Titles WHERE Authors.Au_ID=[Title Author].Au_ID AND Titles.ISBN=[Title Author].ISBN ORDER BY Author
This must be typed as a single line in the Command Text (SQL) area that appears when you click the ellipsis by the RecordSource property. Make sure it is typed in exactly as shown. Make sure there are spaces after ‘SELECT’, after ‘Author,Titles.ISBN,Title’, after ‘FROM’, after ‘Authors,[Title Author],Titles’, after ‘WHERE’, after ‘Authors.Au_ID=[Title Author].Au_ID’, after ‘AND’, after ‘Titles.ISBN=[Title Author].ISBN’, and separating the final three words ‘ORDER BY Author’. The program will tell you if you have a syntax error in the SQL statement, but will give you little or no help in telling you what’s wrong.
Here’s what this statement does: It selects the Author, Titles.ISBN, and Title fields from the Authors, Title Author, and Titles tables, where the respective Au_ID and ISBN fields match. It then orders the resulting virtual table, using authors as an index.
2. Add a label box and text box to the form, for displaying the author name. Set the control properties.
Label3:
Caption - Author
Text1:
DataSource - dtaTitles (select, don’t type)
DataField - Author (select, don’t type)
Locked - True
Name - txtAuthor
Text - [Blank]
When done, the form should resemble this:
3. Save, then rerun the application. The author’s names will now appear with the book titles and ISBN values. Did you notice you still haven’t written any code?
I know you had to type out that long SQL statement, but that’s not code, technically speaking. Notice how the books are now ordered based on an alphabetical listing of authors’ last names
Control Structures in Visual Basic 6.0
Control Statements are used to control the flow of program's execution. Visual Basic supports control structures such as if... Then, if...Then ...Else, Select...Case, and Loop structures such as Do While...Loop, While...Wend, For...Next etc method.
If...Then selection structure
The If...Then selection structure performs an indicated action only when the condition is True; otherwise the action is skipped.
Syntax of the If...Then selection
If Then
statement
End If
e.g.: If average>75 Then
txtGrade.Text = "A"
End If
If...Then...Else selection structure
The If...Then...Else selection structure allows the programmer to specify that a different action is to be performed when the condition is True than when the condition is False.
Syntax of the If...Then...Else selection
If Then
statements
Else
statements
End If
e.g.: If average>50 Then
txtGrade.Text = "Pass"
Else
txtGrade.Text = "Fail"
End If
Nested If...Then...Else selection structure
Nested If...Then...Else selection structures test for multiple cases by placing If...Then...Else selection structures inside If...Then...Else structures.
Syntax of the Nested If...Then...Else selection structure
You can use Nested If either of the methods as shown above
Method 1
If < condition 1 > Then
statements
ElseIf < condition 2 > Then
statements
ElseIf < condition 3 > Then
statements
Else
Statements
End If
Method 2
If < condition 1 > Then
statements
Else
If < condition 2 > Then
statements
Else
If < condition 3 > Then
statements
Else
Statements
End If
End If
EndIf
e.g.: Assume you have to find the grade using nested if and display in a text box
If average > 75 Then
txtGrade.Text = "A"
ElseIf average > 65 Then
txtGrade.Text = "B"
ElseIf average > 55 Then
txtGrade.text = "C"
ElseIf average > 45 Then
txtGrade.Text = "S"
Else
txtGrade.Text = "F"
End If
Select...Case selection structure
Select...Case structure is an alternative to If...Then...ElseIf for selectively executing a single block of statements from among multiple block of statements. Select...case is more convenient to use than the If...Else...End If. The following program block illustrate the working of Select...Case.
Syntax of the Select...Case selection structure
Select Case Index
Case 0
Statements
Case 1
Statements
End Select
e.g.: Assume you have to find the grade using select...case and display in the text box
Dim average as Integer
average = txtAverage.Text
Select Case average
Case 100 To 75
txtGrade.Text ="A"
Case 74 To 65
txtGrade.Text ="B"
Case 64 To 55
txtGrade.Text ="C"
Case 54 To 45
txtGrade.Text ="S"
Case 44 To 0
txtGrade.Text ="F"
Case Else
MsgBox "Invalid average marks"
End Select
Note: In this example I have used a message box function. In later lessons you will learn how to use message box functions.
If...Then selection structure
The If...Then selection structure performs an indicated action only when the condition is True; otherwise the action is skipped.
Syntax of the If...Then selection
If
statement
End If
e.g.: If average>75 Then
txtGrade.Text = "A"
End If
If...Then...Else selection structure
The If...Then...Else selection structure allows the programmer to specify that a different action is to be performed when the condition is True than when the condition is False.
Syntax of the If...Then...Else selection
If
statements
Else
statements
End If
e.g.: If average>50 Then
txtGrade.Text = "Pass"
Else
txtGrade.Text = "Fail"
End If
Nested If...Then...Else selection structure
Nested If...Then...Else selection structures test for multiple cases by placing If...Then...Else selection structures inside If...Then...Else structures.
Syntax of the Nested If...Then...Else selection structure
You can use Nested If either of the methods as shown above
Method 1
If < condition 1 > Then
statements
ElseIf < condition 2 > Then
statements
ElseIf < condition 3 > Then
statements
Else
Statements
End If
Method 2
If < condition 1 > Then
statements
Else
If < condition 2 > Then
statements
Else
If < condition 3 > Then
statements
Else
Statements
End If
End If
EndIf
e.g.: Assume you have to find the grade using nested if and display in a text box
If average > 75 Then
txtGrade.Text = "A"
ElseIf average > 65 Then
txtGrade.Text = "B"
ElseIf average > 55 Then
txtGrade.text = "C"
ElseIf average > 45 Then
txtGrade.Text = "S"
Else
txtGrade.Text = "F"
End If
Select...Case selection structure
Select...Case structure is an alternative to If...Then...ElseIf for selectively executing a single block of statements from among multiple block of statements. Select...case is more convenient to use than the If...Else...End If. The following program block illustrate the working of Select...Case.
Syntax of the Select...Case selection structure
Select Case Index
Case 0
Statements
Case 1
Statements
End Select
e.g.: Assume you have to find the grade using select...case and display in the text box
Dim average as Integer
average = txtAverage.Text
Select Case average
Case 100 To 75
txtGrade.Text ="A"
Case 74 To 65
txtGrade.Text ="B"
Case 64 To 55
txtGrade.Text ="C"
Case 54 To 45
txtGrade.Text ="S"
Case 44 To 0
txtGrade.Text ="F"
Case Else
MsgBox "Invalid average marks"
End Select
Note: In this example I have used a message box function. In later lessons you will learn how to use message box functions.
Bound Data Tools
• Most of the Visual Basic tools we’ve studied can be used as bound, or data-aware, tools (or controls). That means, certain tool properties can be tied to a particular database field. To use a bound control, one or more data controls must be on the form.
• Some bound data tools are:
Label - Can be used to provide display-only access to a specified text data field.
Text Box - Can be used to provide read/write access to a specified text data field. Probably, the most widely used data bound tool.
Check Box - Used to provide read/write access to a Boolean field.
Combo Box - Can be used to provide read/write access to a text data field.
List Box - Can be used to provide read/write access to a text data field.
Picture Box - Used to display a graphical image from a bitmap, icon, or metafile on your form. Provides read/write access to a image/binary data field.
Image Box - Used to display a graphical image from a bitmap, icon, or metafile on your form (uses fewer resources than a picture box). Provides read/write access to a image/binary data field.
• There are also three ‘custom’ data aware tools, the DataCombo (better than using the bound combo box), DataList (better than the bound list box), and DataGrid tools, we will look at later.
• Bound Tool Properties:
DataChanged - Indicates whether a value displayed in a bound control has changed.
DataField - Specifies the name of a field in the table pointed to by the respective data control.
DataSource - Specifies which data control the control is bound to.
If the data in a data-aware control is changed and then the user changes focus to another control or tool, the database will automatically be updated with the new data (assuming LockType is set to allow an update).
• To make using bound controls easy, follow these steps (in order listed) in placing the controls on a form:
1. Draw the bound control on the same form as the data control to which it will be bound.
2. Set the DataSource property. Click on the drop-down arrow to list the data controls on your form. Choose one.
3. Set the DataField property. Click on the drop-down arrow to list the fields associated with the selected data control records. Make your choice.
4. Set all other properties, as required.
By following these steps in order, we avoid potential data access errors.
• The relationships between the bound data control and the data control are:
• Some bound data tools are:
Label - Can be used to provide display-only access to a specified text data field.
Text Box - Can be used to provide read/write access to a specified text data field. Probably, the most widely used data bound tool.
Check Box - Used to provide read/write access to a Boolean field.
Combo Box - Can be used to provide read/write access to a text data field.
List Box - Can be used to provide read/write access to a text data field.
Picture Box - Used to display a graphical image from a bitmap, icon, or metafile on your form. Provides read/write access to a image/binary data field.
Image Box - Used to display a graphical image from a bitmap, icon, or metafile on your form (uses fewer resources than a picture box). Provides read/write access to a image/binary data field.
• There are also three ‘custom’ data aware tools, the DataCombo (better than using the bound combo box), DataList (better than the bound list box), and DataGrid tools, we will look at later.
• Bound Tool Properties:
DataChanged - Indicates whether a value displayed in a bound control has changed.
DataField - Specifies the name of a field in the table pointed to by the respective data control.
DataSource - Specifies which data control the control is bound to.
If the data in a data-aware control is changed and then the user changes focus to another control or tool, the database will automatically be updated with the new data (assuming LockType is set to allow an update).
• To make using bound controls easy, follow these steps (in order listed) in placing the controls on a form:
1. Draw the bound control on the same form as the data control to which it will be bound.
2. Set the DataSource property. Click on the drop-down arrow to list the data controls on your form. Choose one.
3. Set the DataField property. Click on the drop-down arrow to list the fields associated with the selected data control records. Make your choice.
4. Set all other properties, as required.
By following these steps in order, we avoid potential data access errors.
• The relationships between the bound data control and the data control are:
Assigning Tables
• Once the ADO data control is connected to a database, we need to assign a table to that control. Recall each data control is attached to a single table, whether it is a table inherent to the database or the virtual table we discussed. Assigning a table is done via the RecordSource property.
• Tables are assigned by making queries of the database. The language used to make a query is SQL (pronounced ‘sequel,’ meaning structured query language). SQL is an English-like language that has evolved into the most widely used database query language. You use SQL to formulate a question to ask of the database. The data base ‘answers’ that question with a new table of records and fields that match your criteria.
• A table is assigned by placing a valid SQL statement in the RecordSource property of a data control. We won’t be learning any SQL here. There are many texts on the subject - in fact, many of them are in the BIBLIO.MDB database we’ve been using. Here we simply show you how to use SQL to have the data control ‘point’ to an inherent database table.
• Click on the ellipsis next to RecordSource in the property box. A Property Pages dialog box will appear. In the box marked Command Text (SQL), type this line:
SELECT * FROM TableName
This will select all fields (the * is a wildcard) from a table named TableName in the database. Click OK.
• Setting the RecordSource property also establishes the Recordset property, which we will see later is a very important property.
• In summary, the relationship between the data control and its two primary properties (ConnectionString and RecordSource) is:
• Tables are assigned by making queries of the database. The language used to make a query is SQL (pronounced ‘sequel,’ meaning structured query language). SQL is an English-like language that has evolved into the most widely used database query language. You use SQL to formulate a question to ask of the database. The data base ‘answers’ that question with a new table of records and fields that match your criteria.
• A table is assigned by placing a valid SQL statement in the RecordSource property of a data control. We won’t be learning any SQL here. There are many texts on the subject - in fact, many of them are in the BIBLIO.MDB database we’ve been using. Here we simply show you how to use SQL to have the data control ‘point’ to an inherent database table.
• Click on the ellipsis next to RecordSource in the property box. A Property Pages dialog box will appear. In the box marked Command Text (SQL), type this line:
SELECT * FROM TableName
This will select all fields (the * is a wildcard) from a table named TableName in the database. Click OK.
• Setting the RecordSource property also establishes the Recordset property, which we will see later is a very important property.
• In summary, the relationship between the data control and its two primary properties (ConnectionString and RecordSource) is:
Assigning and returning arrays in Visual Basic 6
Visual Basic 6 adds two important features to arrays. First, you can perform assignments between arrays. Second, you can write procedures that return arrays. You can assign arrays only of the same type and only if the target is a dynamic array. (The latter condition is necessary because Visual Basic might need to resize the target array.)
ReDim a(10, 10) As Integer
Dim b() As Integer
' Fill the a array with data (omitted).
b() = a() ' This works!
It's no surprise that native assignment commands are always faster than the corresponding For…Next loops that copy one item at a time. The actual increment in speed heavily depends on the data type of the arrays and can vary from 20 percent to 10 times faster. A native assignment between arrays also works if the source array is held in a Variant. Under Visual Basic 4 and 5, you could store an array in a Variant, but you couldn't do the opposite—that is, retrieve an array stored in a Variant variable and store it back in an array of a specific type. This flaw has been fixed in Visual Basic 6:
Dim v As Variant, s(100) As String, t() As String
' Fill the s() array (omitted).
v = s() ' Assign to a Variant.
t() = v ' Assign from a Variant to a dynamic string array.
You often use the capacity to assign arrays to build functions that return arrays. Notice that pair of brackets at the end of the first line in the following procedure:
Function InitArray(first As Long, Last As Long) As Long()
ReDim result(first To Last) As Long
Dim i As Long
For i = first To Last
result(i) = i
Next
InitArray = result
End Function
The new capability of returning arrays lets you write highly versatile array routines. Visual Basic 6 itself includes a few new string functions—namely Join, Split, and Filter—that rely on it. (You'll find more about these new string functions in Chapter 5). Here are two examples of what you can do with this intriguing feature:
' Returns a portion of a Long array
' Note: fails if FIRST or LAST are not valid
Function SubArray(arr() As Long, first As Long, last As Long, _
newFirstIndex As Long) As Long()
Dim i As Long
ReDim result(newFirstIndex To last _ first + newFirstIndex) As Long
For i = first To last
result(newFirstIndex + i - first) = arr(i)
Next
SubArray = result
End Function
' Returns an array with all the selected items in a ListBox
Function SelectedListItems(lst As ListBox) As String()
Dim i As Long, j As Long
ReDim result(0 To lst.SelCount) As String
For i = 0 To lst.ListCount - 1
If lst.Selected(i) Then
j = j + 1
result(j) = lst.List(i)
End If
Next
SelectedListItems = result
End Function
ReDim a(10, 10) As Integer
Dim b() As Integer
' Fill the a array with data (omitted).
b() = a() ' This works!
It's no surprise that native assignment commands are always faster than the corresponding For…Next loops that copy one item at a time. The actual increment in speed heavily depends on the data type of the arrays and can vary from 20 percent to 10 times faster. A native assignment between arrays also works if the source array is held in a Variant. Under Visual Basic 4 and 5, you could store an array in a Variant, but you couldn't do the opposite—that is, retrieve an array stored in a Variant variable and store it back in an array of a specific type. This flaw has been fixed in Visual Basic 6:
Dim v As Variant, s(100) As String, t() As String
' Fill the s() array (omitted).
v = s() ' Assign to a Variant.
t() = v ' Assign from a Variant to a dynamic string array.
You often use the capacity to assign arrays to build functions that return arrays. Notice that pair of brackets at the end of the first line in the following procedure:
Function InitArray(first As Long, Last As Long) As Long()
ReDim result(first To Last) As Long
Dim i As Long
For i = first To Last
result(i) = i
Next
InitArray = result
End Function
The new capability of returning arrays lets you write highly versatile array routines. Visual Basic 6 itself includes a few new string functions—namely Join, Split, and Filter—that rely on it. (You'll find more about these new string functions in Chapter 5). Here are two examples of what you can do with this intriguing feature:
' Returns a portion of a Long array
' Note: fails if FIRST or LAST are not valid
Function SubArray(arr() As Long, first As Long, last As Long, _
newFirstIndex As Long) As Long()
Dim i As Long
ReDim result(newFirstIndex To last _ first + newFirstIndex) As Long
For i = first To last
result(newFirstIndex + i - first) = arr(i)
Next
SubArray = result
End Function
' Returns an array with all the selected items in a ListBox
Function SelectedListItems(lst As ListBox) As String()
Dim i As Long, j As Long
ReDim result(0 To lst.SelCount) As String
For i = 0 To lst.ListCount - 1
If lst.Selected(i) Then
j = j + 1
result(j) = lst.List(i)
End If
Next
SelectedListItems = result
End Function
arrays in VB6 (Visual Basic 6) While
While you can create two-dimensional arrays in Visual Basic, their structure isn't really flexible for at least two reasons: All rows in the array must have the same number of elements, and you can use ReDim Preserve to change the number of columns but you can't add new rows. The first point is especially important because it often leads you to declare an array that's far too large for your needs, thus allocating a lot of memory that in most cases remains largely unused. You can solve both problems using a structure known as an array of arrays.
The technique is conceptually simple: Since you can store an array in a Variant variable, you can build an array of Variants, where each item holds an array. Each subarray—a row of this pseudo-array—can hold a different number of elements, and you don't need to use more memory than is strictly necessary.
Here's an example, based on an imaginary PIM (Personal Information Manager) program. In this program, you need to keep track of a list of appointments for each day of the year. The simplest solution would be to use an array in which each row corresponds to a day in the year and each column to a possible appointment. (For the sake of simplicity, let's assume that each appointment's data can be held in a string.)
ReDim apps(1 To 366, 1 To MAX_APPOINTMENTS) As String
Of course, you now have the problem of setting a reasonable value for the MAX_APPOINTMENTS symbolic constant. It should be high enough to account for all possible appointments in a day but not too high because you might be wasting a lot of memory without any real reason. Let's see how the array of arrays technique can help us save memory without posing any artificial limit to your application:
' A module-level variable
Dim apps(1 To 366) As Variant
' Add an appointment for a given day.
Sub AddNewAppointment(day As Integer, description As String)
Dim arr As Variant
If IsEmpty(apps(day)) Then
' This is the first appointment for this day.
apps(day) = Array(description)
Else
' Add the appointment to those already scheduled.
arr = apps(day)
ReDim Preserve arr(0 To UBound(arr) + 1) As Variant
arr(UBound(arr)) = description
apps(day) = arr
End If
End Sub
' Extract all the appointments for a given day.
Sub ListAppointments(day As Integer, lst As ListBox)
Dim i As Long
For i = 0 To UBound(apps(1))
lst.AddItem apps(1)(i)
Next
End Sub
In this example, I kept the code as simple as possible and used an array of Variant arrays. You could save even more memory if each row of this array were built using an array of a more specific data type (String, in this case). Note the special syntax used to address an item in an array of arrays:
' Change the description for the Nth appointment.
apps(day)(n) = newDescription
Nothing keeps you from extending this concept further, introducing an array of arrays of arrays, and so on. If you're dealing with arrays in which each row can vary considerably in length, this approach is going to save you a lot of memory and, in most cases, improve your overall performance too. A key feature of an array of arrays is that you can process entire rows of your pseudo-array as if they were single entities. For example, you can swap them, replace them, add and delete them, and so on.
' Move the January 1st appointments to January 2nd.
apps(2) = apps(1)
apps(1) = Empty
Finally, an important advantage of this technique is that you can add new rows without losing the current contents of the array. (Remember that you can use ReDim Preserve on regular arrays only to modify the number of columns, not the number of rows.)
' Extend the appointment book for another nonleap year.
ReDim Preserve apps(1 to UBound(apps) + 365) As Variant
The technique is conceptually simple: Since you can store an array in a Variant variable, you can build an array of Variants, where each item holds an array. Each subarray—a row of this pseudo-array—can hold a different number of elements, and you don't need to use more memory than is strictly necessary.
Here's an example, based on an imaginary PIM (Personal Information Manager) program. In this program, you need to keep track of a list of appointments for each day of the year. The simplest solution would be to use an array in which each row corresponds to a day in the year and each column to a possible appointment. (For the sake of simplicity, let's assume that each appointment's data can be held in a string.)
ReDim apps(1 To 366, 1 To MAX_APPOINTMENTS) As String
Of course, you now have the problem of setting a reasonable value for the MAX_APPOINTMENTS symbolic constant. It should be high enough to account for all possible appointments in a day but not too high because you might be wasting a lot of memory without any real reason. Let's see how the array of arrays technique can help us save memory without posing any artificial limit to your application:
' A module-level variable
Dim apps(1 To 366) As Variant
' Add an appointment for a given day.
Sub AddNewAppointment(day As Integer, description As String)
Dim arr As Variant
If IsEmpty(apps(day)) Then
' This is the first appointment for this day.
apps(day) = Array(description)
Else
' Add the appointment to those already scheduled.
arr = apps(day)
ReDim Preserve arr(0 To UBound(arr) + 1) As Variant
arr(UBound(arr)) = description
apps(day) = arr
End If
End Sub
' Extract all the appointments for a given day.
Sub ListAppointments(day As Integer, lst As ListBox)
Dim i As Long
For i = 0 To UBound(apps(1))
lst.AddItem apps(1)(i)
Next
End Sub
In this example, I kept the code as simple as possible and used an array of Variant arrays. You could save even more memory if each row of this array were built using an array of a more specific data type (String, in this case). Note the special syntax used to address an item in an array of arrays:
' Change the description for the Nth appointment.
apps(day)(n) = newDescription
Nothing keeps you from extending this concept further, introducing an array of arrays of arrays, and so on. If you're dealing with arrays in which each row can vary considerably in length, this approach is going to save you a lot of memory and, in most cases, improve your overall performance too. A key feature of an array of arrays is that you can process entire rows of your pseudo-array as if they were single entities. For example, you can swap them, replace them, add and delete them, and so on.
' Move the January 1st appointments to January 2nd.
apps(2) = apps(1)
apps(1) = Empty
Finally, an important advantage of this technique is that you can add new rows without losing the current contents of the array. (Remember that you can use ReDim Preserve on regular arrays only to modify the number of columns, not the number of rows.)
' Extend the appointment book for another nonleap year.
ReDim Preserve apps(1 to UBound(apps) + 365) As Variant
VB6 Arrays and variants (Visual Basic 6)
Visual Basic lets you store arrays in Variant variables and then access the array items using the Variant variable as if it were an array:
ReDim Names(100) As String, var As Variant
' Initialize the Names array (omitted).
var = Names() ' Copy the array into the Variant.
Print var(1) ' Access array items through the Variant.
You can even create an array of Variant elements on the fly using the Array function and store it in a Variant variable:
' Arrays returned by the Array() function are zero-based.
Factorials = Array(1, 1, 2, 6, 24, 120, 720, 5040, 40320, 362880, 3628800)
Likewise, you can pass an array to a procedure that expects a Variant parameter and then access the elements of the array through that parameter:
' A polymorphic function that sums the values in any array
Function ArraySum(arr As Variant) As Variant
Dim i As Long, result As Variant
For i = LBound(arr) To UBound(arr)
result = result + arr(i)
Next
ArraySum = result
End Function
The most interesting feature of the preceding routine is that it works correctly with any type of numeric one-dimensional array. It even works with String arrays, but in that case you get the concatenation of all items, not their sum. This procedure is extremely powerful and reduces the amount of code you have to write to deal with different kinds of arrays. But you should be aware that accessing array items through a Variant parameter noticeably slows down the execution. If you need the best performance, write specific routines that process specific types of arrays.
You can also pass a multidimensional array to a routine that expects a Variant parameter. In this case, you can still access the array elements through the Variants, but if you don't know at compile time how many dimensions the array has, your routine has to determine that number before proceeding. You can get this value using a trial-and-error approach:
' This routine returns the number of dimensions of the array
' passed as an argument, or 0 if it isn't an array.
Function NumberOfDims(arr As Variant) As Integer
Dim dummy as Long
On Error Resume Next
Do
dummy = UBound(arr, NumberOfDims + 1)
If Err Then Exit Do
NumberOfDims = NumberOfDims + 1
End Function
It's perfectly legal to use the function name inside a function's code as if it were a local variable, as the previous code snippet does. Often this technique lets you save a local variable and a final assignment before exiting the routine, which indirectly makes your code run slightly faster.
Here's a modified ArraySum routine that uses NumberOfDims and works with both one- and two-dimensional arrays:
Function ArraySum2(arr As Variant) As Variant
Dim i As Long, j As Long, result As Variant
' First check whether we can really work with this array.
Select Case NumberOfDims(arr)
Case 1 ' One-dimensional array
For i = LBound(arr) To UBound(arr)
result = result + arr(i)
Next
Case 2 ' Two-dimensional array
For i = LBound(arr) To UBound(arr)
For j = LBound(arr, 2) To UBound(arr, 2)
result = result + arr(i, j)
Next
Next
Case Else ' Not an array, or too many dimensions
Err.Raise 1001, , "Not an array or more than two dimensions"
End Select
ArraySum2 = result
End Function
Often, if a Variant contains an array, you don't know the basic type of that array in advance. The VarType function returns the sum of the vbArray constant (decimal 8192), plus the VarType of the data included in the array. This lets you test that the array passed to a routine is of a given type:
If VarType(arr) = (vbArray + vbInteger) Then
' Array of integers
ElseIf VarType(arr) = (vbArray + vbLong) Then
' Array of Longs
ElseIf VarType(arr) And vbArray Then
' An array of another type (just tests a bit)
End If
You can also test whether a Variant holds an array using the IsArray function. When a Variant variable holds an array, the TypeName function appends a pair of empty parentheses to its result:
Print TypeName(arr) ' Displays "Integer()"
As I've explained, you can either assign an array to a Variant variable or you can pass an array as a Variant parameter of a procedure. While the two operations look very similar, they're substantially different. To execute an assignment, Visual Basic makes a physical copy of the array. As a result, the Variant variable doesn't point to the original data but to the copy; from this point on, all the manipulations you do through the Variant variable don't affect the original array. Conversely, if you call a procedure and pass an array as a Variant parameter, no data is physically copied and the Variant simply works as an alias of the array. You can reorder array items or modify their values, and your changes are immediately reflected in the original array
Subscribe to:
Posts (Atom)