NACHA Files Bonus

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

2 thoughts on “NACHA Files Bonus

  1. Dick, I like the header in array (and related approaches) you show at the beginning. I had just noticed a similar bit of code on Debra’s site yesterday. It’s very clear.

  2. I like your idea of writing values to an array first, then dumping the array to a range all at once.

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

Leave a Reply

Your email address will not be published.