Surprised cat

So far, all of the data has been in a worksheet. Here's pseudocode for extracting data from a worksheet into an array:

While there is more data
    Get some data
    If the data meets some criteria Then
        Copy it to an array
    EndIf
Loop

In RL, data that comes from other systems, or is downloaded from the Web, might not be in a worksheet. Often it will be in a plain text file. Your program will need to extract data from that.

Here's pseudocode for extracting data from a file into an array:

While there is more data
    Get some data
    If the data meets some criteria Then
        Copy it to an array
    EndIf
Loop

Indeed.

Geeks often talk about algorithms. An algorithm is a general way of doing something, like extracting data. This:

While there is more data
    Get some data
    If the data meets some criteria Then
        Copy it to an array
    EndIf
Loop

The algorithm can be implemented in different languages, like C++, Java, PHP, VBA, Python… whatevs. The algorithm can handle data from different sources, like worksheets, files, databases… whatevs.

Of course, the details are different. But the underlying approach is the same.

Camels again

Here's the code we wrote for extracting data from a worksheet, for the camels dancing exam example:

26. 'Extract valid data from raw data.
27. 'Params:
28. '  count: number of valid data items (out)
29. '  validData: array of valid data items (out)
30. Sub extractValidData(count As Integer, validData)
31.     Dim row As Integer
32.     Dim dataStartRow As Integer
33.     Dim dataValue As String
34.     Dim dataOK As Boolean
36.     'What row the data starts in.
37.     dataStartRow = 8
38.     'Current raw data row.
39.     row = 0
40.     'Count of valid data.
41.     count = 0
42.     'Does the next row have data in it?
43.     Do While Cells(row + dataStartRow, 2) <> ""
44.         'Get the next data value.
45.         dataValue = Cells(row + dataStartRow, 2)
46.         'Validate it.
47.         'Set flag.
48.         dataOK = True
49.         'Change flag if there is an error.
50.         If Not IsNumeric(dataValue) Then
51.             dataOK = False
52.         ElseIf dataValue < 20 Or dataValue > 40 Then
53.             dataOK = False
54.         End If
55.         'Did the data pass the tests?
56.         If dataOK Then
57.             'Data is OK. Copy to extracted data array.
58.             count = count + 1
59.             validData(count) = dataValue
60.         End If
61.         'Go to the next raw data row.
62.         row = row + 1
63.     Loop
64. End Sub

Suppose the data is stored in a file instead. It's in CSV (comma-separated values) format. A sample:

1000, 35
1001, 20
1002, 38
1003, 32
1004, 32
1005, 32
1006, 22

In each line, the first number is the exam id, and the second is the score. It's the same data as before, just moved into a file.

Here's what the worksheet will look like:

The exam scores aren't on the worksheet. Instead, the user types in the name of the file with the exam scores.

Here's extractValidData, rewritten to read data from the file.

26. 'Extract valid data from raw data.
27. 'Params:
28. '  count: number of valid data items (out)
29. '  validData: array of valid data items (out)
30. Sub extractValidData(count As Integer, validData)
31.     Dim inputExamId As String
32.     Dim inputScore As String
33.     Dim dataOK As Boolean
34.     Dim fileName As String
36.     'Count of valid data.
37.     count = 0
38.     'Open the data file.
39.     fileName = Cells(7, 2)
40.     Open ThisWorkbook.Path & "\" & fileName For Input As #1
41.     'Loop while there is more data.
42.     Do While Not EOF(1)
43.         'Read a line of data.
44.         Input #1, inputExamId, inputScore
45.         'Validate the score.
46.         'Set flag.
47.         dataOK = True
48.         'Change flag if there is an error.
49.         If Not IsNumeric(inputScore) Then
50.             dataOK = False
51.         ElseIf inputScore < 20 Or inputScore > 40 Then
52.             dataOK = False
53.         End If
54.         'Did the data pass the tests?
55.         If dataOK Then
56.             'Data is OK. Copy to extracted data array.
57.             count = count + 1
58.             validData(count) = inputScore
59.         End If
60.     Loop
61.     Close #1
62. End Sub

The code is similar to what we had before. Let's look at the differences.

Opening the file

We have to tell Excel that we want it to prepare a file for reading. We didn't have to do this when the data was in the worksheet.

Excel needs to know the name and location of the data file. The user typed the file name into cell B7, so we grabbed that from the worksheet:

fileName = Cells(7, 2)

Then we tell Excel to open that file for reading:

Open ThisWorkbook.Path & "\" & fileName For Input As #1

This code assumes that the data file is in the same folder as the worksheet. That's what ThisWorkbook.Path is all about. The #1 is the file handle. From now on, the program will use #1 when it wants to refer to the file.

Our code needs to know when it has gotten to the end of the data. When extracting data from a worksheet, the program looked for an empty call, with code like this:

Do While Cells(rows + 1, 2) <> ""

The file version is a little simpler, because there is a built-in function that detects end of file:

Do While Not EOF(1)

EOF(1) is True when the end of file 1 has been reached. So the loop keeps on running as long as the end of file 1 has not been reached.

This code…

Input #1, inputExamId, inputScore

… reads two values from a line in the data file.

The only other difference is this statement just before the end of the sub:

Close #1

This closes the file, making it available for other users. Always close your files. Some languages don't care about closing, but VBA does.

This sometimes causes problems. Say the program crashes in extractValidData, after opening the file, but before closing it. Next time you run the program, Excel might complain that you can't open the file, because it is already open.

If this happens to you, then close the data file manually. Open the Immediate window (Ctrl+G). Then type the close statement as it is in the code, and hit Enter:

This will close the file.

Good thunking

To read from a file, only extractValidData needed to change​. This is good thunking. When the Boss asks for a change, only a part of the program is affected. Less writing, debugging, and screaming. Less time spent on the change. Higher productivity. Higher reliability.

Professional programmers think about these things. Having code work is not enough. The code should be easy to manage, as well. Good thunking helps with that.

Other changes

Some changes have a greater effect on the program. Suppose the Boss wants you to output the number of data errors and outliers, along with the stats. Like this:

Here's the main program now:

1. Private Sub cmdAnalyze_Click()
2.     Dim validData(1000) As Integer
3.     Dim count As Integer
4.     Dim highest As Integer
5.     Dim lowest As Integer
6.     Dim average As Single
7.     Dim dataErrors As Integer
8.     Dim outliers As Integer
10.     'Extract the valid data.
11.     extractValidData count, validData, dataErrors, outliers
12.     'Is there enough data to analyze?
13.     If count < 1 Then
14.         MsgBox "Sorry, there is not enough data to analyze."
15.         End
16.     End If
18.     'Compute stats.
19.     computeStats count, validData, highest, lowest, average
21.     'Output stats.
22.     outputStats count, highest, lowest, average, dataErrors, outliers
24. End Sub

You need some two more variables. They're computed in extractValidData, and returned to the main program. outputStats outputs them. Like the honey badger, computeStats doesn't care.

Here's the new code for extractValidData:

28. 'Extract valid data from raw data.
29. 'Params:
30. '  count: number of valid data items (out)
31. '  validData: array of valid data items (out)
32. '  dataErrors: count of data errors - non-numeric,
33. '          or out of range (out)
34. '  outliers: count of outliers (out)
35. Sub extractValidData(count As Integer, validData, _
36.         dataErrors As Integer, outliers As Integer)
37.     Dim inputExamId As String
38.     Dim inputScore As String
39.     Dim dataOK As Boolean
40.     Dim fileName As String
42.     'Count of valid data.
43.     count = 0
44.     'Init error counters.
45.     dataErrors = 0
46.     outliers = 0
47.     'Open the data file.
48.     fileName = Cells(7, 2)
49.     Open ThisWorkbook.Path & "\" & fileName For Input As #1
50.     'Loop while there is more data.
51.     Do While Not EOF(1)
52.         'Read a line of data.
53.         Input #1, inputExamId, inputScore
54.         'Validate the score.
55.         'Set flag.
56.         dataOK = True
57.         'Change flag if there is an error.
58.         If Not IsNumeric(inputScore) Then
59.             dataErrors = dataErrors + 1
60.             dataOK = False
61.         ElseIf inputScore < 0 Or inputScore > 50 Then
62.             dataErrors = dataErrors + 1
63.             dataOK = False
64.         ElseIf inputScore < 20 Or inputScore > 40 Then
65.             outliers = outliers + 1
66.             dataOK = False
67.         End If
68.         'Did the data pass the tests?
69.         If dataOK Then
70.             'Data is OK. Copy to extracted data array.
71.             count = count + 1
72.             validData(count) = inputScore
73.         End If
74.     Loop
75.     Close #1
76. End Sub

The counters are initialized (lines 45 and 46). They're incremented when an error or outlier is detected (lines 59, 62, and 65). You need to add a new range test. Recall that exam scores are from 0 to 50. Numbers outside that range are errors. However, number from 0 to 19 and 41 to 50 are not errors, they're outliers. This code counts them that way.

outputStats hardly changes:

111. 'Output statistics to worksheet.
112. 'Params:
113. '  count: number of valid data items (in)
114. '  highest: highest score (in)
115. '  lowest: lowest score (in)
116. '  average: average score (in)
117. '  dataErrors: count of data errors - non-numeric,
118. '          or out of range (out)
119. '  outliers: count of outliers (out)
120. Sub outputStats(count As Integer, highest As Integer, _
121.         lowest As Integer, average As Single, _
122.         dataErrors As Integer, outliers As Integer)
123.     Cells(11, 2) = count
124.     Cells(12, 2) = highest
125.     Cells(13, 2) = lowest
126.     Cells(14, 2) = Round(average, 1)
127.     Cells(16, 2) = dataErrors
128.     Cells(17, 2) = outliers
129. End Sub

Again, good thunking means we can make the changes without rewriting everything. Less time spent writing, debugging, and screaming. More productivity. More reliability.

Exercises

Summary