Lily and Jeremy. You want to take a crack at this?

OK. Take data from the worksheet, write it out, except without the part-time NH1ers.

How do you want to thunk about this?

Huh?

What are the big chunks?

I'm thinking maybe two chunks:

• Read the data into an array, filtering out the records we don't want.
• Write the array to a file.

What do you think?

OK, except for one thing. Here's the worksheet.

We've only read data for one column. There are four!

Oh, yeah. Uh, Tara, what do we do?

There are a few ways to do it. The easiest is to create one array for each column. When you add data to one array, add to them all.

Oh, OK! I think I get it.

Let's get started with the main program. I'll add a Run button… there. Now I'll just start with this:

Private Sub cmdRun_Click()
    'Read data from worksheet into arrays.

    'Write CSV data file from arrays.
End Sub

OK, that's a start.

Oh, I remember how we do this now. I'll add the calls to the first subs. We'll need an array for each data column. EIDs, names, statuses, and plans.

Private Sub cmdRun_Click()
    'Read data from worksheet into arrays.
    readFilterData eids, lastNames, employmentStatuses, plans
    'Write CSV data file from arrays.
End Sub

How does that look?

Not bad, though remember we don't know how many rows there are. We'll need our code to find the end of the data, like we did before, and return the number of rows to the main program.

So, I guess that means four counts, one for each column?

Hmm, maybe not. Every column has the same number of rows. So we should get by with one count variable. Use it for all the columns.

Hey, that's right? So maybe this:

Private Sub cmdRun_Click()
    'Read data from worksheet into arrays.
    readFilterData eids, lastNames, employmentStatuses, plans, >>numRows<<
    'Write CSV data file from arrays.
End Sub

That look's good. But let's declare the variables, and add the second call to the main program.

(Typity, typity)

Private Sub cmdRun_Click()
    Dim eids(500) As String
    Dim lastNames(500) As String
    Dim employmentStatuses(500) As String
    Dim plans(500) As String
    'Read data from worksheet into arrays.
    readFilterData eids, lastNames, employmentStatuses, plans, numRows
    'Write CSV data file from arrays.
    writeCsvFile eids, lastNames, employmentStatuses, plans, numRows
End Sub

I'm guessing at the 500. We can always change it, if it isn't enough.

How's that look?

Great! But why did you make eids an array of strings? They're numbers.

Yeah, but we're not going to do any calculations on them. We could make them numbers instead, if we make them Long rather than Integer. They need to be Long, because they're more than 32,767.

I get it now. Let's leave it as it is.

How about adding stubs and comments for the Subs? Help keep us on track.

Private Sub cmdRun_Click()
    Dim eids(500) As String
    Dim lastNames(500) As String
    Dim employmentStatuses(500) As String
    Dim plans(500) As String
    'Read data from worksheet into arrays.
    readFilterData eids, lastNames, employmentStatuses, plans, numRows
    'Write CSV data file from arrays.
    writeCsvFile eids, lastNames, employmentStatuses, plans, numRows
End Sub

'Read data from the worksheet into arrays, filtering as we go.
'Params:
'  eids: array of employee ids (output).
'  lastNames: array of last names (output).
'  employmentStatuses: array of full-time/part-time statuses (output).
'  plans: array of health plan codes (output).
'  numRows: number of rows read (output).
Sub readFilterData(eids, lastNames, employmentStatuses, plans, numRows As Integer)

End Sub

'Write data to CSV file.
'Params:
'  eids: array of employee ids (input).
'  lastNames: array of last names (input).
'  employmentStatuses: array of full-time/part-time statuses (input).
'  plans: array of health plan codes (input).
'  numRows: number of rows read (input).
Sub writeCsvFile(eids, lastNames, employmentStatuses, plans, numRows As Integer)

End Sub

Well, how's that?

I'm not sure how to write the arrays as params in a Sub's signature. I put:

Sub readFilterData(eids, lastNames, ... numRows As Integer)

Tara, how do you do it?

You can leave it like that, without a type, and VBA will figure it out. Or you can declare the type of each array like this:

Sub readFilterData(eids() As String, lastNames() As String, ... numRows As Integer)

The empty parens tell VBA that you're passing arrays.

Either way is fine.

Let's try the input sub. Here's the code so far:

'Read data from the worksheet into arrays, filtering as we go.
'Params:
'  eids: array of employee ids (output).
'  lastNames: array of last names (output).
'  employmentStatuses: array of full-time/part-time statuses (output).
'  plans: array of health plan codes (output).
'  numRows: number of rows read (output).
Sub readFilterData(eids() As String, lastNames() As String, _
        employmentStatuses() As String, plans() As String, numRows As Integer)

End Sub

Now what? Hmm… you know, we just saw how to read data from a worksheet into an array, in the last lesson. I'm going to grab some code from there, and paste it here. Then adjust it.

'Read data from the worksheet into arrays, filtering as we go.
'Params:
'  eids: array of employee ids (output).
'  lastNames: array of last names (output).
'  employmentStatuses: array of full-time/part-time statuses (output).
'  plans: array of health plan codes (output).
'  numRows: number of rows read (output).
Sub readFilterData(eids() As String, lastNames() As String, _
        employmentStatuses() As String, plans() As String, numRows As Integer)
    Dim rowSource As Integer
    Dim rowDestination As Integer
    rowSource = 0
    rowDestination = 0
    Do While Cells(rowSource + 1, 1) <> ""
        'Is the data above 250?
        If Cells(rowSource + 1, 1) > 250 Then
            'Copy value to destination.
            rowDestination = rowDestination + 1
            sales(rowDestination) = Cells(rowSource + 1, 1)
        End If
        rowSource = rowSource + 1
    Loop
End Sub

Lil, do you remember how this works?

Aye! rowSource is a counter, tracking which row in the data source (the worksheet) we're getting data from.

rowDestination is a counter as well, but it tracks where we put data in the destination (the array).

The While loops until it finds an empty cell. If the data in a cell passes the filter (the If statement), then it gets put into the array.

This should be easy to change. How about this:

'Read data from the worksheet into arrays, filtering as we go.
'Params:
'  eids: array of employee ids (output).
'  lastNames: array of last names (output).
'  employmentStatuses: array of full-time/part-time statuses (output).
'  plans: array of health plan codes (output).
'  numRows: number of rows read (output).
Sub readFilterData(eids() As String, lastNames() As String, _
        employmentStatuses() As String, plans() As String, numRows As Integer)
    Dim rowSource As Integer
    Dim rowDestination As Integer
    Dim eid As String
    Dim lastName As String
    Dim employmentStatus As String
    Dim plan As String
    'Init row counters.
    rowSource = 0
    rowDestination = 0
    Do While Cells(rowSource + 1, 1) <> ""
        rowSource = rowSource + 1
        'Get a row's data.
        eid = Cells(rowSource, 1)
        lastName = Cells(rowSource, 2)
        employmentStatus = Cells(rowSource, 3)
        plan = Cells(rowSource, 4)
        'Save this one?
        If Not (employmentStatus = "p" And plan = "NH1") Then
            'Copy value to destination.
            rowDestination = rowDestination + 1
            eids(rowDestination) = eid
            lastNames(rowDestination) = lastName
            employmentStatuses(rowDestination) = employmentStatus
            plans(rowDestination) = plan
        End If
    Loop
    numRows = rowDestination
End Sub

I added variables like eid and lastName to put the data in each row into. Made it easier to think about what the code does. Changed the filter test. Used all four arrays. Not much else changed.

Let me try the output sub. We know how many data rows there are in the arrays, so we can use a For loop. Something like this:

'Write data to CSV file.
'Params:
'  eids: array of employee ids (input).
'  lastNames: array of last names (input).
'  employmentStatuses: array of full-time/part-time statuses (input).
'  plans: array of health plan codes (input).
'  numRows: number of rows read (input).
Sub writeCsvFile(eids() As String, lastNames() As String, _
        employmentStatuses() As String, plans() As String, numRows As Integer)
    Dim index As Integer
    Open ThisWorkbook.Path & "\hr-db.csv" For Output As #1
    For index = 1 To numRows
        >>Write out eids(index), lastNames(index), employmentStatuses(index), plans(index)<<<
    Next index
    Close #1
End Sub

But how do you output data in the CSV format, with all those commas? Seems like there should be an easier way.

Tara, is there?

Indeed! Checkout the Write statement. That's what it does.

(Google, Google. Read, read.)

Oh, man! That makes it so easy!

'Write data to CSV file.
'Params:
'  eids: array of employee ids (input).
'  lastNames: array of last names (input).
'  employmentStatuses: array of full-time/part-time statuses (input).
'  plans: array of health plan codes (input).
'  numRows: number of rows read (input).
Sub writeCsvFile(eids() As String, lastNames() As String, _
        employmentStatuses() As String, plans() As String, numRows As Integer)
    Dim index As Integer
    Open ThisWorkbook.Path & "\hr-db.csv" For Output As #1
    For index = 1 To numRows
        >>Write #1, eids(index), lastNames(index), employmentStatuses(index), plans(index)<<
    Next index
    Close #1
End Sub

Write rocks CSV files!