J-Walk compiled a list of songs with animals in the title. That data screams for analysis, doesn’t it?

The vertebrates (phylum Chordata) kicked ass and mammals (class Mammalia) dominated. But Birds (class Aves) were no slouch either, although many of those songs only references “bird”.

The most popular species is dog (Canis lupus). Oddly missing is human (Homo sapiens). The biggest contributing song was Pigs, Sheep, and Wolves by Paul Simon. The three titles that didn’t quite make it into kingdom Anamalia were Puff the Magic Dragon, Werewolves of London, and The Unicorn Song. I let the Monkees in as primates against my better judgment.

Mammals with only one entry: skunk, weasel, walrus, raccoon, bear, llama, reindeer, beaver, muskrat, mouse, kangaroo, sloth, and mole. Smallest phyla (at one) were Porifera (sponge), Mollusca (octopus), and Cridaria (coral).

Can you think of a better use of Excel than this?

You can download AnimalSongs.zip

Just when you thought the class module fun was over, it’s not. As long as I have all this class infrastructure, I can leverage that into making some reports. As you are no doubt aware, employees get pissed off when you make errors on their paychecks. Therefore, a review report prior to making the ACH file is in order.

There are couple of constructs in this code that I use quite a bit. I put the column headers in an array and write that array to a range. I like how it lists the column headers on one line from left to right. It makes adding or removing columns easy to maintain.

The other construct is to create a property that returns an array, resize a range based on that array, and write the array to that resized range. If I change my property, the only other code I need to change is vaTitles. Everything else is dynamic.

Clean and easy to read. Of course I have to write a bunch of properties to make it work. Let’s look at one of them. In CCheck

Most of the other properties are structured just like this. I loop through the CheckItems, make sure they apply (in this case via IsGrossPay), and add up the Amount properties. In CPayrollItem

Most of my Is* properties either check the name of the PayrollItem or check for the existence of ExpenseAccount or LiabilityAccount. This is not an optimal way to do it. I’d rather have this data in the raw data and read it in as a property. If it was in the PayrollItem table, it would be easier to maintain. In the project that inspired this example, I didn’t have control over the raw data, so I had to make due. If a PayrollItem that should be considered “wages” doesn’t have the word “Salary” in its name, the code breaks. If I were doing it again, I might look for a different way to handle this part.

After I write all the properties I referenced in WriteReview and all the Is* properties in CPayrollItem, my code compiles, runs, and produces this.

Using class modules, my code is clean, easy to read, self documenting, and easy to modify if the situation warrants. If my employee, checks, and/or payroll item data moves from Excel tables to an Access database, I only have to change my Fill methods to fill the classes from a different source. Alternatively, if the format of my output (ACH file or Payroll reivew) changes, I only have to change the properties and procedures that generate the output. The classes act as a wall between the input and output. When one changes, the other is unaffected.

You can download NACHA4.zip

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

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 NACHA.zip

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

You can download NACHA2.zip

There is a product that some banks use, like my bank, called Premier ACH. It’s hosted on the bank’s website. It allows you to enter the data for an ACH transactions (Automated Clearing House, think direct deposit) and creates a properly formatted NACHA file for you. You can submit a NACHA file to your bank and they will initiate an ACH transaction for you. It’s a two step process: create a wrk file and turn it into a NACHA file. The wrk file is an XML file.

The goal of this series of posts is to turn Excel data into a properly formatted wrk file. The Excel data is fake for this example, but it’s meant to approximate the format you might get when you export certain reports out of Quickbooks. It isn’t exact because creating relational fake data is a real pain. But it’s close and it will teach you the necessary skills to manipulate the data in…wait for it…custom class modules. Who didn’t see that coming?

A sample of the Excel data:

And a sample of the XML (wrk) file:

Here are the basics steps I took:

1. Identify objects and relationships
2. Create class modules
3. Write code to fill classes
4. Write code to create XML file
5. Augment class modules until it compiles
6. Bonus: Create a payroll review sheet

Lately, I write most of my code in this fashion. Once the basic class modules are created, I write the main procedure. The main procedure usually includes one or two methods of my custom objects and provides the framework for what I want to accomplish. There are a couple of advantages, and one big disadvantage, to coding this way. I’m not advocating it as great method, I’m just saying it’s how I’ve been working lately. So far, I like it.

The first major advantage is the aforementioned framework it provides. By coding from finish to start, I know exactly what my result should look like before I get into the details. The other advantage is I never code anything I don’t need. Like test driven development, this method forces me to only write properties and methods in my classes that I need. A short disclaimer: This example was abstracted from a larger, more complex application, so you may see an occasional unused property.

That big disadvantage? The code doesn’t compile until I’ve written a boat load. Some, like me, have advocated that you should never be more than a few lines from code that compiles. This method could not be more contrary to that advice.

Let’s get on with it. I have Employees, PayrollItems, and Checks. I think everyone knows what employees and checks are, so I won’t belabor those. A PayrollItem is an entity that controls how a check applies to the general ledger. Examples of PayrollItems are Salary, Federal Withholding, and State Unemployment. I need one more object to link Checks and PayrollItems. For example, Salary is a payroll item, but the amount assigned to Salary will certainly vary by employee and may even vary by check. To accommodate that, I create a CheckItem object. The CheckItem will hold the variable data of the PayrollItem for a particular check. As for relationships

• Employee has many Checks
• Check has many CheckItems
• CheckItem has one PayrollItem

Let’s start by building CEmployee. First, we’ll look at the data

I have five employees with a name and social security number. Each employee has one or two direct deposit accounts. I insert a class into my project, name it CEmployee, and enter the columns as public variables.

Then I run my Public to Private and Create Parent macros and get this

I do the same thing for my PayrollItems table. It looks like this

Next time, I’ll set up the CheckItem and Check classes and fill them all. If you can’t wait that long…

You can download NACHA.zip