Using Visual Basic 6
One benefit of computer programming is the capability to repeat a set of instructions reliably after they're written. The specific commands you put at the beginning and end of a set of instructions to control their repeated execution are known as loops. These commands tell the computer when and how often to perform the set of instructions. By using loops, you can write simpler programs that would have otherwise required redundant lines of code. Loops can be used to inspect arrays (discussed in Chapter 11, "Working with Arrays"), change properties of a program's controls, and do a set of tasks for as long as a certain condition exists, or skip a set of tasks until a certain condition is met.
Reading collections with loopsVisual Basic 6.0 supports other looping commands, including collection loops that use the For Each...Next statement. For more information about collections, see Chapter 26, "Making Object-Oriented Programs with Visual Basic."
Visual Basic supports two commonly used types of loops: counter loops, which perform a task a set number of times, and conditional loops, which perform a task while a specified condition exists or until a specified condition exists.
Counter loops are also known as For or For...Next loops. They're called For...Next loops because the beginning and end of the loop are defined by the For statement and the Next statement, respectively. The syntax of a For...Next loop is
For CounterVar = StartNum To EndNum [Step StepNum]
statements
Next [CounterVar]
Keyword that starts a loop
User-defined numeric variable that the loop uses as a counter
Number from which the loop starts
Keyword that separates StartNum from EndNum
Number at which the loop stops
Optional keyword that indicates that loop should step
Indicates what size step increment/decrement should be (can be negative)
Keyword that completes the loop
Identifies which CounterVar is updated by the Next keyword
At the beginning of a For...Next loop, you define a counter variable, as well as the beginning and end points of the variable's value. The first time the loop is run, the counter variable is set to the value of the beginning point. Each time the program runs through the loop, the value of the counter increments. If the Step keyword is used, the counter variable increments as dictated by the number following the Step keyword. For example, in the following statement, intCntr will increment by 2:
For intCntr = 0 To 10 Step 2
As the counter variable increments, it's checked against the value of the end point. If the counter is larger than the end point, the program skips out of the loop and onto the first statement following the loop.
If the beginning value of the loop is greater than the ending value, the loop won't execute at all, unless you set up the loop to count backward. For example, the following loop doesn't cause an error, but because the start boundary of the loop (9) is greater than the end boundary (0), the loop is ignored and the message box doesn't appear:
Stepping through the loopThe default or assumed value for the Step in a For...Next loop is a positive 1, which increments the counter. Including the step value on all For...Next loops, even when the value is 1, improves readability and aids maintenance.
ConcatenationYou concatenate a string when you add another string onto a string. An example of concatenating three strings onto one string is FullName = First & " " & Last, which performs two successive concatenations into the string variable FullName.
For intCntr = 9 To 0 `This loop will not run!
MsgBox "This is loop number: " & Cstr(intCntr)
Next intCntr
The following loop works, however, because the loop uses the Step keyword to decrement >backward (-1) from 9 to 0:
For intCntr = 9 To 0 Step -1
MsgBox "This is loop number: " & Cstr(intCntr)
Next intCntr
The counter variable is changed each time the loop reaches the Next statement. Unless otherwise specified with the Step keyword, the counter is increased by one for each loop.
Listing 10.1, the event handler for the For...Next button click event, shows a For...Next loop that exposes the counter variable within a larger string displayed in a TextBox (see Figure 10.1). As the loop progresses, the counter variable is converted to a string and inserted within strings, StrBegin and StrEnd. Those strings are concatenated onto a master "holding" string, StrMsg, which grows with each trip through the loop. A carriage return and line-break character is added to StrMsg at the end of the loop, and then the StrMsg is assigned to the Text property of txtDisplay. The loop then refreshes the form to repaint the TextBox.
01 Private Sub cmdForNext_Click()
02 Dim intCntr As Integer
03 Dim strBegin As String
04 Dim strEnd As String
05 Dim strMsg As String
06
07 `Make a phrase for the beginning of a line
08 strBegin = "This is line: "
09
10 `Make a phrase for the end of a line
11 strEnd = " of a For...Next loop"
12
13 `Make a For...Next Loop
14 For intCntr = 0 To 20
15
16 `Put the beginning of the line in place
17 strMsg = strMsg & strBegin
18
19 `Convert the counter integer to a string
20 `and place it in the middle of the string
21 `that is being constructed
22 strMsg = strMsg & CStr(intCntr)
23
24 `Add the end of the message
25 strMsg = strMsg & strEnd
26
27 `Put in a line break constant
28 strMsg = strMsg & vbCrLf
29 `Display the resulting string in the textbox
30 txtDisplay.Text = strMsg
31 Refresh `force a repaint of the screen
32 Next intCntr
33 End Sub
Documenting the end of a For...LoopFor ease of reading your program code, it's good practice to include the variable name in the Next statement (like on line 32 of Listing 10.1). This is especially important in nested loops.
FIGURE 10.1 When you set a TextBox's MultiLine property to True, make sure that you set the TextBox's ScrollBars property accordingly. Otherwise, you may not see all the text.
vbCrLf is a Visual Basic global constant that could have been substituted with the string "Chr(13) & Chr(10)". Both the VBA constant and this literal string are equivalent to a carriage return/line feed combination. For more information about using VBA constants, see Chapter 7, "Using Data Types, Constants, and Variables."
An alternative method would be to only update the screen after leaving the loop. You would move the assignment statement immediately after the Next command to prevent the program from displaying any text until the loop is completed. In the case of a long-running process, you should give users a visual indication that the program is running. In long-running loops, if you wait to the end of the loop to display the results onscreen, your users may think the program has locked up.
Although you can use any numeric variable for the counter, you need to be aware of the limits of each variable type. For example, trying to run a loop 40,000 times starting from 0 with a step of 1 and an integer variable as the CounterVar causes an overrun error during execution. This is because an integer has a maximum positive value of 32,767. Using a Long integer will solve this problem when larger loop counters are required. The limitation on Long integers is negative 2,147,483,648 to positive 2,147,483,647.
Using floating-point variables such as Singles and Doubles for a CounterVar will work. There's even the capability to support fractional Step variables, although these can become difficult to maintain and should be avoided unless required by the functionality of the statements inside the loop.
You should exercise great care when manually changing the value of the counter variable inside the loop. Never reset the value of the counter variable inside a For...Next loop, because doing so may create an infinite loop or a program lockup. For example, the following code causes the For...Loop to go into an infinite loop, because the end value of 4 is never reached:
){kind=link}
For intCntr = 0 to 4
intCntr = 0 ` This line causes an infinite loop
Next intCntr
Typically, your For...Next loop should run through all the values of the counter variable, but sometimes you'll want the loop to terminate early. To do this, simply place an Exit For statement where you want the loop to stop. This statement is typically associated within an If...Then statement.
Listing 10.2 shows an enhancement to the code in Listing 10.1. Lines 16 through 22 show an added If...Then statement at the beginning of the For...Next loop. When the loop begins, the code enters the If...Then statement to take a look at the value of the CheckBox on the form. Selecting the CheckBox on the form tells the program that the loop needs to terminate when the value of the counting variable exceeds the quantity 10 (see Figure 10.2). Therefore, if this condition exists (the CheckBox is checked), the Exit For statement--contained in the If...Then statement within the first If...Then statement--forces the program out of the For...Next loop.
01 Private Sub cmdForNext_Click()
02 Dim intCntr As Integer
03 Dim strBegin As String
04 Dim strEnd As String
05 Dim strMsg As String
06
07 `Make a phrase for the beginning of a line
08 strBegin = "This is line: "
09
10 `Make a phrase for the end of a line
11 strEnd = " of a For...Next loop"
12
13 `Make a For...Next Loop
14 For intCntr = 0 To 20
15
16 `Take a look to see if the checkbox
17 `on the form is checked
18 If chkLimit.Value = 1 Then
19 `if it is, then Exit the For statement
20 `when intCntr is greater than 10
21 If intCntr > 10 Then Exit For
22 End If
23 `Put the beginning of the line in place
24 strMsg = strMsg & strBegin
25
26 `Convert the counter integer to a string
27 `and place it in the middle of the string
28 `that is being constructed
29 strMsg = strMsg & CStr(intCntr)
30
31 `Add the end of the message
32 strMsg = strMsg & strEnd
33
34 `Put in a line break constant
35 strMsg = strMsg & vbCrLf
36 `Display the resulting string in the textbox
37 txtDisplay.Text = strMsg
38 Refresh `force a repaint of the screen
39 Next intCntr
40 End Sub
FIGURE 10.2 CheckBoxes are a good means of control when you need to set or clear a condition.
A Do...Loop is a conditional loop, which is active as long or until a condition exists. The key feature of a conditional loop is, of course, the condition (any expression that can return a True or False value). This True/False can be a return value from a function; the value of a property, such as the Value property of an OptionButton; or an expression, such as NumVal < 15. (Chapter 18, "Writing Reusable Code with Subs and Functions," discusses functions fully.) The two basic types of conditional loops are Do...While, which repeats while the condition is True, and Do...Until, which repeats until the condition is True.
A Do...While loop works pretty much as the name implies--it does something while a certain condition is true. For example, to keep adding one to MyNum while MyNum is less than 20, you would use this:
){kind=link}
Do While MyNum <20
MyNum = MyNum + 1
Loop
The keyword While in the Do...While statement tells the program that the loop will be repeated while the condition expression is true. When the condition in a Do...While loop becomes false, the program moves out of the loop and onto the next statement after the Loop statement.
The syntax for a Do...While loop is
Do While condition
statement(s)
Loop
The keyword that denotes the beginning of the loop
The keyword that denotes what kind of loop it is
The state that must exist (for example, x = 10, MyVal <> True, or y < x)
The statement(s) to execute while the loop is active
The keyword that indicates the end of the loop block
Both statements must be present
Or you can use this equally valid syntax:
Do
statement(s)
Loop While condition
As you can see in this example, the Do...While loop has two forms. The difference between the two is the placement of the condition--at the beginning of the loop or at the end.
Listing 10.3 looks similar to the For...Next loop code in Listing 10.2. When you take a closer look at Lines 13 through 41, however, you'll notice that where there was a For...Next loop, there's now a Do...While loop that runs while the value of intCntr is less than or equal to 20 (see Figure 10.3). Line 21 has also been changed from Exit For to Exit Do because the loop is now change to a Do loop. The Exit Do command is used with both types of Do loops.
When the condition statement is placed after the Loop keyword in a Do...While or Do...Until loop, the set of instructions contained between Do and Loop is always executed once. The condition is checked only after the first iteration of the loop.
01 Private Sub cmdDoWhile_Click()
02 Dim intCntr As Integer
03 Dim strBegin As String
04 Dim strEnd As String
05 Dim strMsg As String
06
07 `Make a phrase for the beginning of a line
08 strBegin = "This is line: "
09
10 `Make a phrase for the end of a line
11 strEnd = " of a Do...While loop"
12
13 `Make a Do..While Loop
14 Do While intCntr <= 20
15
16 `Take a look to see if the checkbox
17 `on the form is checked
18 If chkLimit.Value = 1 Then
19 `if it is, then Exit the Do statement
21 If intCntr > 10 Then Exit Do
22 End If
23
24 `Put the beginning of the line in place
25 strMsg = strMsg & strBegin
26
27 `Convert the counter integer to a string
28 `and place it in the middle of the string
29 `that is being constructed
30 strMsg = strMsg & CStr(intCntr)
31
32 `Add the end of the message
33 strMsg = strMsg & strEnd
34
35 `Put in a line break constant
36 strMsg = strMsg & vbCrLf
37 `Display the resulting string in the textbox
38 txtDisplay.Text = strMsg
39 Refresh `force a repaint of the screen
40 intCntr = intCntr + 1
41 Loop
42 End Sub
FIGURE 10.3 You can us Do...While to loop while intCntr is less than or equal to 20.
By placing the While condition clause in the Do statement, you tell the program that you want to evaluate the condition before you run any statements inside the loop. If the condition is True, the repetitive statements between the Do statement and the Loop statement are run. Then the program returns to the Do statement to evaluate the condition again. As soon as the condition is False, the program moves to the statement following the Loop statement.
Put conditional clauses in only one placeDon't put the While condition clause in both the Do and Loop statements because this causes an error when you try to run your program.
When the form of the loop is located with the While keyword right after the Do keyword, the statements inside the loop may never be run. If the condition is False before the loop is run the first time, the program just proceeds to the statements after the loop. To run the Do...While loop at least once, use the second form of the Do...While loop, which places the condition after the Loop statement. For example,
){kind=link}
Do
Text1.Text = Text1.Text & CStr(intCntr )
intCntr = intCntr + 1
Loop While intCntr < 10
This form of the Do...Loop tells the program that you want the loop to run at least once and then evaluate the condition to determine whether to repeat the loop.
Replace While...Wend loops with Do...WhileIf you're working on code developed by someone else, you may find a loop that starts with a While statement and ends with a Wend statement. This type of loop works the same as a Do...While loop with the While clause in the Do statement. Visual Basic still supports a While...Wend loop, but it's recommended to that you use the Do...While type of loop because it's more flexible.
When using a Do...While loop, don't forget to increment the conditional variable in your code. Unlike the For...Next loop, the conditional or counting variables in Do loops are your responsibility to manage. Also, it's good practice to initialize the loop variable rather than rely on the default. For example, the following goes on infinitely because the While condition is never changed:
Dim intCntr
intCntr = 0
txtMessage.Text = ""
Do While intCntr < 10
txtMessage.Text = txtMessage.Text _
& CStr(intCntr)
Loop
The following code loops, terminates, and displays the TextBox's text just fine:
Dim intCntr
intCntr = 0
txtMessage.Text = ""
Do While intCntr < 10
txtMessage.Text = txtMessage.Text _
& CStr(intCntr)
intCntr = intCntr + 1
Loop
The Do...Until loop is basically the same as the Do...While loop, except that the statements inside a Do...Until loop are run only as long as the condition is false--in other words, as long as the condition isn't met. When the condition becomes true, the loop terminates.
The syntax for a Do...Until loop is
Do Until condition statement(s) Loop
There are two forms of syntax for the Do...Until loop, just as there are two forms of syntax for the Do...While loop--one with the condition in the Do statement and one with the condition in the Loop statement. If you place the condition in the same line as the Do keyword, the condition is evaluated before the statements in the loop are executed. If you place the condition in the same line as the Loop keyword, the loop is run at least once before the condition is evaluated.
Listing 10.4 shows the event procedure for the cmdDoUntil button's Click event (see Figure 10.4). A Do...Until loop within the event procedure runs until the counter variable, intCntr, is greater than 20, or the Stop at 10 CheckBox is selected. This code is similar to the code for the cmdDoWhile_Click() event procedure in Listing 10.3. The only difference is that the Do...While loop is changed to a Do...Until loop (Lines 13 through 41). Also, the StrEnd string on Line 11 is changed from " of a Do...While loop" to " of a Do...Until loop". As you can see, Do...While loops and Do...Until loops are closely related. Generally, which one you use is a matter of personal style.
01 Private Sub cmdDoUntil_Click()
02 Dim intCntr As Integer
03 Dim strBegin As String
04 Dim strEnd As String
05 Dim strMsg As String
06
07 `Make a phrase for the beginning of a line
08 strBegin = "This is line: "
09
10 `Make a phrase for the end of a line
11 strEnd = " of a Do...Until loop"
12
13 `Make a Do..Until Loop
14 Do Until intCntr > 20
...
41 Loop
42 End Sub
FIGURE 10.4 The Do...Until code still checks the value of the CheckBox.
Terminate an infinite loopIf your program is running within the Visual Basic IDE, press Ctrl+Break. When you press these keys, the loop breaks at a line of code within the infinite loop. If your program isn't running within the IDE, the only way to terminate it is to end the program from within the Windows Task Manager. (For detailed information about the Task Manager, read the online documentation that comes with Windows.)
Getting comfortable using loops takes time. The more you use them, the better you'll get at identifying situations in which to use them. Loops can be a powerful programming tool, but they can cause enormous headaches if not properly written.
The following shows a programmer's worst nightmare--the infinite loop:
){kind=link}
Dim intCntr as Integer
Do While intCntr = 0
intCntr = 0
Loop
The command inside the loop sets the conditional variables and prevents the program from going beyond loop. So the code tells the program to loop forever. When the program enters this loop, it's locked into the loop; everything else waits for the loop to complete, which never happens. If you run this program within the Visual Basic IDE, be aware that infinite loops will often lock it up and you may lose any program changes you made since your last update.
The best way to avoid an infinite loop is to carefully double-check any code you write that changes the conditional or counter variables in your loops. This may seem simplistic, but the power of loops is also the danger of loops. A loop can just as quickly tie up your system doing a desired productive task as it can counting to 2 billion for no reason.
In some situations, it becomes important to repeat a subtask several times during a larger task that's also being repeated. The way you write this type of program is by including a loop inside the set of instructions being repeated by a loop. This loop-within-a-loop structure is called nesting, or a nested loop. Nested loops can be a powerful programming tool, but they're confusing to understand if they're not properly written and documented.
Listing 10.5 shows an example of multiple loops that change the Background Color property of the vpjLoops form and CheckBox during runtime. The main form and the CheckBox are changed to include all the visible background on the frame. This sample program is run from the Simple Color command button (refer to Figure 10.4).
Download this codeThe code for this example is included in the prjLoops project. To use the code without having to type it all in, you can download this project from the Web at http://www. mcp.com/info. You'll be asked to enter an ISBN; enter 078971633x, and then click the Search button to go to the Book Info page for Using VB6.
01 Private Sub cmdForColor_Click()
02 Dim lngBlu As Long
03 Dim lngGrn As Long
04 Dim lngRed As Long
05 Dim SaveBack As Long
06 `Assign intColor Variables to initial values
07 `This will display white because all colors are 255
08 lngBlu = 255
09 lngGrn = 255
10 lngRed = 255
11
12 `Save the current background color of the form
13 SaveBack = frmLoop.BackColor
14 `Both the Form and CheckBox background are the same
15
16 `Loop 255 times reducing the Red color by 1
17 For lngRed = 255 To 1 Step -1
18 `Set the background color of the form and CheckBox
19 frmLoop.BackColor = RGB(lngRed, lngGrn, lngBlu)
20 chkLimit.BackColor = frmLoop.BackColor
21 Refresh `force a repaint of the screen
22 Next lngRed
23
24 `Loop 255 times reducing the Green color by 1
25 For lngGrn = 255 To 1 Step -1
26 `Set the background color of the form and CheckBox
27 frmLoop.BackColor = RGB(lngRed, lngGrn, lngBlu)
28 chkLimit.BackColor = frmLoop.BackColor
29 Refresh `force a repaint of the screen
30 Next lngGrn
31
32 `Loop 255 times reducing the Blue color by 1
33 For lngBlu = 255 To 1 Step -1
34 `Set the background color of the form and CheckBox
35 frmLoop.BackColor = RGB(lngRed, lngGrn, lngBlu)
36 chkLimit.BackColor = frmLoop.BackColor
37 Refresh `force a repaint of the screen
38 Next lngBlu
39
40 `Loop 255 times increasing the Red color by 1
41 For lngRed = 0 To 254 Step 1
42 `Set the background color of the form and CheckBox
43 frmLoop.BackColor = RGB(lngRed, lngGrn, lngBlu)
44 chkLimit.BackColor = frmLoop.BackColor
45 Refresh `force a repaint of the screen
46 Next lngRed
47
48 `Loop 255 times increasing the Green color by 1
49 For lngGrn = 0 To 254 Step 1
50 `Set the background color of the form and CheckBox
51 frmLoop.BackColor = RGB(lngRed, lngGrn, lngBlu)
52 chkLimit.BackColor = frmLoop.BackColor
53 Refresh `force a repaint of the screen
54 Next lngGrn
55
56 `Loop 255 times increasing the Blue color by 1
57 For lngBlu = 0 To 254 Step 1
58 `Set the background color of the form and CheckBox
59 frmLoop.BackColor = RGB(lngRed, lngGrn, lngBlu)
60 chkLimit.BackColor = frmLoop.BackColor
61 Refresh `force a repaint of the screen
62 Next lngBlu
63
64 `Reset the background color on both to original
65 frmLoop.BackColor = SaveBack
66 chkLimit.BackColor = frmLoop.BackColor
67 End Sub
Make code easier to readIndenting code nested inside a loop or other structure (such as an If statement) makes the code easier to read.
The program first saves the current background color and then proceeds to remove each of the three screen colors through a set of 255 increments. The first color removed is red, then green, and finally blue. The second set of three loops does just the opposite: first it adds in the red, then green, and finally blue. The last two lines (65 and 66) put both elements back to their original background color settings.
There are six separate loops for 67 total lines of code. These loops have many lines in common, and with a little effort, you can identify those common elements.
You'll notice the use of a long integer array to accomplish the function of the inner loop. Arrays are covered in depth in Chapter 11, "Working with Arrays."
Listing 10.6 shows Listing 10.5 rewritten as two nested loops. The first loop dims the background color by color. The second, as in Listing 10.5, adds the colors back in. This sample program is run from the Nested Color command button (see Figure 10.5).
Download this codeThe code for this example is also included in the prjLoops project, which you can download from http://www.mcp.com/ info.
01 Private Sub cmdNestColor_Click()
02 Dim intColorArray(1 To 3) As Long
03 Dim SaveBack As Long
04 Dim intPass As Integer
05 Dim intHue As Integer
06 `Assign intColor Variables to initial values
07 `This will display white because all colors are 255
08 intColorArray(1) = 255 `Red
09 intColorArray(2) = 255 `Green
10 intColorArray(3) = 255 `Blue
11
12 `Save the current background color of the form
13 SaveBack = frmLoop.BackColor
14 `Both the Form and CheckBox background are the same
15
16 `Top Loop does each color in turn Red, Green, Blue
17 For intPass = 1 To 3
18 `Then Loop2 255 times reducing color by 1
19 For intHue = 255 To 1 Step -1
20 intColorArray(intPass) = intHue
21 frmLoop.BackColor = RGB(intColorArray(1), _ intColorArray(2), intColorArray(3))
22 chkLimit.BackColor = frmLoop.BackColor
23 Refresh `force a repaint of the screen
24 Next intHue
25 Next intPass
26
27 `Top Loop does each color in turn Red, Green, Blue
28 For intPass = 1 To 3
29 `Then Loop2 255 times increasing color by 1
30 For intHue = 0 To 254 Step 1
31 intColorArray(intPass) = intHue
32 frmLoop.BackColor = RGB(intColorArray(1), _ intColorArray(2), intColorArray(3))
33 chkLimit.BackColor = frmLoop.BackColor
34 Refresh `force a repaint of the screen
35 Next intHue
36 Next intPass
37
38 `Reset the background color on both to original
39 frmLoop.BackColor = SaveBack
40 chkLimit.BackColor = frmLoop.BackColor
41 End Sub
There are only 41 lines of code and only two nested loops to maintain in this new version of the program. This reduces line count by 35 percent with no loss of functionality. The key benefits that nested loops provide are consistent operation and ease of maintenance. If the same code is being used every time a loop is run, it becomes easy to make sure that it does the same tasks.
Nested loops require more effort to write and debug than simple loops. Reusable code is harder to develop than redundant code by at least a factor of 2. This topic is covered more in Chapter 18, "Writing Reusable Code with Subs and Functions."
Visual Basic's IDE makes it very tempting to write a simple loop and then cut and paste it six, seven, or 200 times in a row. Most programming tools make it very easy to copy lines of code. The challenge comes in learning to write better programs that are easier to maintain and that perform consistently.
There are more useful examples where nested loops are invaluable. The final example in this chapter is based on the MSFlexGrid ActiveX control and how it could be loaded from a text file by using a nested loop. In this example, the control isn't bound to any data source. The example program uses a simple text file that contains one field or cell per row. The nested loop is a big help here, as it can be used to step through the rows in the grid column by column. This allows the read or input statement to appear only once in the program.
Loading the MSFlxGrd.ocx fileAn enhanced version of MSFlexGrid comes with VB6. Before you can use the MSFlexGrid control in your application, you must add the MSFlxGrd.ocx file to your Microsoft Windows System directory. For more information about adding an ActiveX control to a project, see Chapter 4, "Using the Intrinsic Controls."
Listing 10.7 lists the setup commands for the MSFlexGrid control that are executed when the form loads. These commands set the width of the four columns used in the example. (The details of this are covered in Chapter 32, "Enhancing Your Programs Using the Advanced Data Controls.")
01 Private Sub Form_Load()
02 `These values are in TWIPS and set the columns on the
03 `MSFlexGrid at run time. If you change these values
04 `you may need to also readjust the size of the Control.
05 MSFlexGrid1.ColWidth(0) = 640
06 MSFlexGrid1.ColWidth(1) = 1280
07 MSFlexGrid1.ColWidth(2) = 1280
08 MSFlexGrid1.ColWidth(3) = 1440
09 End Sub
By default, the Load event fires when a form is displayed. Figure 10.5 shows the grid after it has been loaded. By using this event, the program adjusts the width of the four columns to values that fit the amount of data in each column. These were identified by trial and error, but a routine could have been written to inspect the data in each grid cell and then determine the right width for each column. This function would work like the Format menu's Column and Autofit command in Microsoft Excel.
FIGURE 10.5 When you load the MSFlexGrid ActiveX control, you can specify runtime properties.
Now that the control is set up, the actual program is straightforward. An ASCII text file contains 24 lines or rows. Each line is read in turn and loaded into the grid. Listing 10.8 shows a partial listing of the text file; the full listing can be downloaded from the Web site, along with the sample program and the actual IMPORT.TXT file.
01 StoreID
02 Location
03 Manager
04 Sales
05 1566
06 Minneapolis
07 James Archie
08 "$14,187,944"
...
21 1032
22 New York
23 Elmer Doren
24 "$5,437,104"
IMPORT.TXT is an ASCII fileThe IMPORT.TXT file was created with Microsoft Notepad and can be edited. The name of the file, its location, and the number of rows is part of the program statements and must be changed to read different filenames.
Rows 1 through 4 of Listing 10.8 are used as the column headings in the grid control. There's nothing special about these rows, other than they are first, and the background and font in the MSFlexGrid control was formatted. Rows 5 though 24 are the detailed records for each of five stores, including the store ID, location, manager's name, and total sales.
The program in Listing 10.9 shows the 39 lines of code required to accomplish the task of loading the grid. Lines 21 through 34 are the nested loop that reads each line from the file and moves it into the proper cell of the grid.
01 Private Sub cmdNestData_Click()
02 Dim strBuff As String
03 Dim intFile As Integer
04 Dim intCol As Integer
05 Dim intRow As Integer
06
07 `This message informs the user of the runtime path
08 `where the IMPORT.TXT file must be located. If
09 `the file is not found a run-time error will result.
10
){kind=link}
11 MsgBox ("The current system path is set to: " _
& CurDir & vbCrLf & "The program will run only" _
& " if: IMPORT.TXT is found at the current path")
12
13 ` The next avaialble file is assigned to intFile
14 ` and file is opened for input.
15 intFile = FreeFile
16 Open CurDir & "\IMPORT.TXT" For Input As #intFile
17
18 `The MSFlexGrid was set-up for 6 rows and 4 columns
19 ` Zero(0) is the default first value
20
21 `First Loop steps through each row
22 For intRow = 0 To 5
23 ` Second Loop steps through each column
24 For intCol = 0 To 3
25 `Reads file #intFile into variable strBuff
26 Input #intFile, strBuff
27 `Set pointer to proper column
28 MSFlexGrid1.Col = intCol
29 `Set pointer tp proper row
30 MSFlexGrid1.Row = intRow
31 `Assigns the read value to the Cell text
32 MSFlexGrid1.Text = strBuff
33 Next intCol
34 Next intRow
35
36 Close #intFile `close files as soon as you can
37 End Sub
Lines 7 through 18 in Listing 10.9 are for finding and opening the text file for input. The MessageBox displays the current default path of the local user's system. This is important because the default path is used with the file handler number given by the FileFree function to locate and open IMPORT.TXT. If the file isn't at this location, an error message will occur.
When the Click event on the Nested Data Read command button is fired, IMPORT.TXT is read row by row. Figure 10.6 show the grid control once the data has been read and loaded into the cells.
FIGURE 10.6 The nested loop reads the text file and loads the control.
© Copyright, Macmillan Computer Publishing. All rights reserved.
){kind=link}