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.
Dim vaWrite As Variant
Dim rWrite As Range
Dim vaTitles As Variant
Set gshData = wshChecks
wshReview.UsedRange.ClearContents
vaTitles = Array(“Employee”, “Gross Pay”, “Fed WH”, “State WH”, “Deductions”, “Net Pay”, “ER Tax”, “ER Cont”)
FillClasses gshData
wshReview.Range(“a1″).Resize(1, UBound(vaTitles) + 1).Value = vaTitles
vaWrite = gclsEmployees.WriteReview(#1/21/2011#)
Set rWrite = wshReview.Range(“A2″).Resize(UBound(vaWrite, 1), UBound(vaWrite, 2))
rWrite.Value = vaWrite
End Sub
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.
Dim aReturn() As Variant
Dim clsEmployee As CEmployee
Dim clsCheck As CCheck
Dim i As Long
ReDim aReturn(1 To Me.CheckCount(dtCheck), 1 To
For Each clsEmployee In Me
Set clsCheck = clsEmployee.CheckByDate(dtCheck)
If Not clsCheck Is Nothing Then
i = i + 1
aReturn(i, 1) = clsEmployee.EmployeeName
aReturn(i, 2) = clsCheck.GrossPay
aReturn(i, 3) = clsCheck.FederalWH
aReturn(i, 4) = clsCheck.StateWH
aReturn(i, 5) = clsCheck.Deductions
aReturn(i, 6) = clsCheck.NetPay
aReturn(i, 7) = clsCheck.CompanyTaxes
aReturn(i,
= clsCheck.CompanyContributionsEnd If
Next clsEmployee
WriteReview = aReturn
End Property
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
Dim clsCheckItem As CCheckItem
Dim dReturn As Double
For Each clsCheckItem In Me.CheckItems
If clsCheckItem.PayrollItem.IsGrossPay Then
dReturn = dReturn + clsCheckItem.Amount
End If
Next clsCheckItem
GrossPay = dReturn
End Property
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
IsGrossPay = Me.IsBonus Or Me.IsWages Or Me.IsCommission
End Property
Public Property Get IsBonus() As Boolean
Const sBONUS As String = “Bonus”
IsBonus = Me.ItemName = sBONUS
End Property
Public Property Get IsCommission() As Boolean
Const sCOM As String = “Commission”
IsCommission = Me.ItemName = sCOM
End Property
Public Property Get IsWages() As Boolean
Const sSALARY As String = “Salary”
IsWages = InStr(1, Me.ItemName, sSALARY) > 0
End Property
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
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.
I like your idea of writing values to an array first, then dumping the array to a range all at once.