NACHA Files Part 3
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.
Set gshData = wshChecks
FillClasses gshData
gclsEmployees.GenerateACH #1/21/2011#
End Sub
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.
Dim clsEmployee As CEmployee
Dim sOutput As String
Dim sFile As String, lFile As Long
Dim lSeq As Long
Dim dNetPay As Double
sFile = ThisWorkbook.Path & Application.PathSeparator & Format(dtCheck, “yyyymmdd”) & “.wrk”
dNetPay = Me.NetPay(dtCheck)
sOutput = gsACHEDITOR & vbNewLine
For Each clsEmployee In Me
sOutput = sOutput & clsEmployee.GenerateACH(dtCheck, lSeq)
Next clsEmployee
lSeq = lSeq + 1
sOutput = sOutput & Me.ACHTotalEditorTable(dtCheck, lSeq, dNetPay)
sOutput = sOutput & Me.FileSpec
sOutput = sOutput & Me.BatchTotal(lSeq, dNetPay)
sOutput = sOutput & TagClose(gsACHEDITOR)
lFile = FreeFile
Open sFile For Output As lFile
Print #lFile, sOutput
Close lFile
End Sub
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.
Dim clsEmployee As CEmployee
Dim clsCheck As CCheck
Dim dReturn As Double
For Each clsEmployee In Me
Set clsCheck = clsEmployee.CheckByDate(dtCheck)
If Not clsCheck Is Nothing Then
dReturn = dReturn + clsCheck.NetPay
End If
Next clsEmployee
NetPay = dReturn
End 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.
Dim clsCheckItem As CCheckItem
Dim dReturn As Double
For Each clsCheckItem In Me.CheckItems
If clsCheckItem.PayrollItem.IsNetPay Then
dReturn = dReturn + clsCheckItem.Amount
End If
Next clsCheckItem
NetPay = dReturn
End Property
Following this rabbit down the hole, I write the IsNetPay property in CPayrollItem.
IsNetPay = (Len(Me.ExpenseAccount) = 0 Or Len(Me.LiabilityAccount) = 0)
End Property
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.
Dim sReturn As String
Dim clsCheck As CCheck
Dim i As Long
Set clsCheck = Me.CheckByDate(dtCheck)
For i = 1 To Me.AccountCount
lSeq = lSeq + 1
sReturn = sReturn & gsACHEDTBL & vbNewLine & gsHOLD & vbNewLine
sReturn = sReturn & gsBATCH & gsBATCHNUM & TagClose(gsBATCH, True) & vbNewLine
sReturn = sReturn & gsNAME & Me.EmployeeName & TagClose(gsNAME, True) & vbNewLine
sReturn = sReturn & gsACCT & Me.Accounts(i) & TagClose(gsACCT, True) & vbNewLine
sReturn = sReturn & gsID & vbNewLine
sReturn = sReturn & gsDISC & vbNewLine
sReturn = sReturn & gsAMT & Format(Me.Amounts(i, clsCheck.NetPay), gsFMTDBL) & TagClose(gsAMT, True) & vbNewLine
sReturn = sReturn & gsRTG & Me.Routings(i) & TagClose(gsRTG, True) & vbNewLine
sReturn = sReturn & gsEFFDTE & Format(dtCheck, gsFMTDATE) & TagClose(gsEFFDTE, True) & vbNewLine
sReturn = sReturn & gsTRANS & Me.AcctTypes(i) & TagClose(gsTRANS, True) & vbNewLine
sReturn = sReturn & gsFREE & vbNewLine
sReturn = sReturn & gsSEQ & lSeq & TagClose(gsSEQ, True) & vbNewLine
sReturn = sReturn & TagClose(gsACHEDTBL) & vbNewLine
Next i
GenerateACH = sReturn
End Property
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.
Dim lReturn As Long
If Len(Me.Account2) = 0 Then
lReturn = 1
Else
lReturn = 2
End If
AccountCount = lReturn
End Property
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.
Dim sReturn As String
If lIndex = 1 Then
sReturn = Me.Account1
Else
sReturn = Me.Account2
End If
Accounts = sReturn
End Property
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.
Dim dReturn As Double
If lIndex = 1 Then
If Me.Amount1 > 1 Then ‘it’s a whole dollar amount
dReturn = Me.Amount1
Else
dReturn = Round(dNetPay * Me.Amount1, 2)
End If
Else
dReturn = dNetPay – Me.Amounts(1, dNetPay)
End If
Amounts = dReturn
End Property
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
If bTrim Then
TagClose = Replace(Trim(sInput), “< “, “</”, 1, 1)
Else
TagClose = Replace(sInput, “<”, “</”, 1, 1)
End If
End Function
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
