In Part 1 we created some classes. In Part 2 we created some more classes, linked them, and filled them. Now we’re ready to actually produce some results. In a standard module, I write my code to generate the XML file.

Man, do I love simple code. In Part 2, I went on and on about my coding method. I start with a procedure like this and work backward to the details. At this point, I need to actually create the GenerateACH method and I know I’ll need to supply a check date. With a few exceptions, my code won’t compile until I’m done.

In Ruby on Rails, the mantra is “Keep your models heavy and your controllers light”. In my version of VBA, that translates into heavy classes and light standard modules. Procedures in standard modules should demonstrate the basic framework while the classes to the dirty detail work. In the above procedure, I build a string, sOutput, that I will eventually print to a file.

The XML file generally consists of one block of tags for each employee and a few other blocks for totals and other company information. This code loops through the employees and generates the tag blocks, all the while concatenating to sOutput.

My first compile error tells me I need a NetPay property.

Because I don’t have a NetPay property in CCheck, I have two procedures that don’t compile. I don’t necessarily fix the next compile error that comes up. In this case, for example, I’ll go write the NetPay property in CCheck.

Following this rabbit down the hole, I write the IsNetPay property in CPayrollItem.

If I have both an expense account and a liability account, I know it’s a company only expense. If either is missing, the offset must be cash, or net pay, so I include it. I may still have coded some properties that don’t yet exist, but I followed the main line through getting the net pay. At this point, I compile to find the next thing to do, and it’s global constant.

Much of this code is concatenating the XML tags with the data in between. I have a bunch of global string constants for those tags. They’re really not very interesting, but you can see them in the file if that’s your bag. After I create gsACHEDITOR, my next compile error points to GenerateACH in CEmployee.

This creates a ton of compile errors for properties that don’t yet exist. While there is generally one XML tag block for each employee, there can be up to two. If the employee has two direct deposit accounts listed, I need a separate tag block for each one. I won’t go through every property that needs to be created, but I will talk a little about how I handle multiple accounts. First, I count them for my loop.

If I have an Account2, it’s 2. If not, it’s 1. A little verbose, but very readable. The Account(lIndex) and Routings(lIndex) are pretty much the same.

It’s set up to look like an array, but I know my limit is 2, so I just return one or the other. The Amounts property is a little different. I have to pass it the net pay so it can compute which portion goes to which account.

Here’s the logic: An Amount1 of 1 means 100%. Less than one means a percentage of net pay. More than one means a fixed dollar amount. I don’t have Amount2 because that is always whatever is left over. If I’m looking for the first amount, I either take the whole dollar amount or multiply the percentage by net pay. If I’m looking for the second amount, I subtract the first amount from net pay.

I create whatever global constants I need and the compiler takes me back to CEmployees.GenerateACH and highlights the ACHTotalEditorTable property. I’ve taken my three main tag blocks at the end of the XML file (company totals, file specs, and batch totals) and put them into properties. This keeps the code cleaner and easier to read. They mostly just concatenate a bunch of constants. Nothing to see here.

One more thing to discuss. In my MUtilities standard module, I wrote this little gem

This allows my to take a tag like <batch> and turn it into </batch>;. The bTrim argument is used because sometimes a closing tag goes at the end of the line where I don’t want leading spaces, and sometimes it goes on its own line where I do.

Now my code compiles and I run it and it works. Hurray. Next time, I’ll leverage all this work into creating a payroll review sheet. Most of the heavy lifting is done. I’ll just have to add a few more properties and methods to my classes.

This file contains NACHA3.xls and the XML file.

You can download NACHA3.zip