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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Public Sub GenerateReview() 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
Public Property Get WriteReview(dtCheck As Date) As Variant 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 8) 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, 8) = clsCheck.CompanyContributions End 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
Public Property Get GrossPay() As Double 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
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 |
Public Property Get IsGrossPay() As Boolean 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.