NACHA Files Part 2

In Part 1, I created the CEmployee and CPayrollItem classes and their parents. For CPayrollItem, I used ItemName as the property for the first column because I didn’t want to name it the same as the class name, PayrollItem. Next, I need to create the CCheck and CCheckItem classes.

For CCheckItem, I only need store an Amount. Every other property of the CheckItem will be inherited from the PayrollItem. So I add a class module, name it CCheckItem, and put this in it

Convert those to properties and create a parent class. I have a one-to-one relationship between CheckItem and PayrollItem, so I don’t need a collection in CheckItem. I only need to refer to one instance of PayrollItem. That’s just another property that I add manually.

CheckItem is done. My Checks table isn’t really a table of checks, but a table of CheckItems.

In my CCheck class, I need to store the check date and that’s it. The name column will be a reference to the CEmployee class. The PayrollItem will be a reference to the CheckItem class. And the Amount column will come from CheckItem as well. So my CCheck code is pretty simple to start. After I convert my public variables and create a parent class, it looks like this

I’ve already created the relationship between CheckItem and PayrollItem. Now I need to create the other relationships. In CEmployee, I do this

And in CCheck

I know I’ll need some code to actually fill the instances, but I’ll let the code drive that rather than do it now. Finally for today, I need to fill the classes. I’ll create Fill methods in CEmployees and CPayrollItems. First, I’ll create two global variables in a standard module

In CPayrollItems, I create this Fill method

It simply loops through column A and adds a PayrollItem for each row. In CEmployees, I create this Fill method

Same drill as CPayrollItem. My last class to fill is CChecks. I do that in a standard module

At this point, the code no longer compiles. EmployeeByName, CheckByDate, PayrollItemByName, and AddCheckItem do not exist in their respective classes. I typed them because I knew what I wanted. I wanted to retrieve the employee by his name. I knew I would have to supply the name as an argument. So I typed the property call how I thought it would look, not worrying that there is no underlying property to support it. Now that I have code that doesn’t compile, I set about creating the underlying properties so that it will compile (and hopefully be functional).

I won’t go through all of these properties. The ‘ByName properties simply loop through a collection until it finds a match. I do want to show the CheckByDate property because I did something a little different. Because I can’t control the layout of my check data (It sort of comes from Quickbooks), I don’t have properly relational data. In other words, I don’t have a Checks table and a CheckItems table that are linked by a key. In CheckByDate, I added an additional Boolean argument that allows me to create an instance of the check if it doesn’t exist. As I move through the table, Elijah Robinson won’t have a check dated 1/7/11, so it’s created. At line 3 of my data, however, that check exists and I simply append the information.

If bCreate is True and the check was not found, a new check is created and added to the Employee class. Note that bCreate is optional and defaults to False. That way I can use it as I would a similar property that didn’t have that option and get Nothing back if it doesn’t exist. At this point in the code, I know checks won’t exist because I’m creating them.

By writing CheckByDate, I now have another layer of code that doesn’t compile. I still have property calls in FillClasses that I haven’t written yet. Put now I have property calls in CheckByDate that I haven’t written. I wrote Me.Checks and Me.AddCheck fully aware that they don’t exist. This is where the finsih-to-start model gets a little hairy. I start to feel uneasy because I can’t hold all of this pending information in my brain at once. I have to trust that the compiler will tell me when I’m done.

I write the Checks property, which simply returns the mclsChecks variable I defined earlier. Then I compile. The next error is AddCheck, so I write it.

My CreateParent utility already included an add method, so this one is OK. I recompile and it takes me back to FillClasses and tells me there is no such thing as PayrollItemByName. I write it and recompile. AddCheckItem is the next victim. Once that is written, my code compiles and all is right with the world. When my code compiles, that’s my trigger to write a test.

>Now I’m back to having code that doesn’t compile. My Check class doesn’t have a CheckItems property because I haven’t needed it yet. All it does is return a private variable, so I write it. Now my test code works and I can move on to the next high level procedure.

In Part 1, I defined my data structure and set up some basic class modules. Here, I create additional class module and link them together (mostly). When I write my Fill procedures, I call properties and methods that I need, regardless of whether they exist. While this leaves me with code that won’t compile for an extended period of time, it ensures that I don’t write any properties or methods that I don’t need.

Next time, I’ll write my top level procedure to generate the XML file. Then I’ll keep writing properties and methods until the code compiles.

If you want to see the finished product

You can download

If you want to see the code at this point in the tutorial

You can download

2 thoughts on “NACHA Files Part 2

  1. In your Fill method, I see you’re using a hard coded column index:

    I like to refer them to an Enum so that if I insert a sheet column, I can easily adjust the relative column positions without adding 1 to everything.

Posting code? Use <pre> tags for VBA and <code> tags for inline.

Leave a Reply

Your email address will not be published.