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
1 2 |
Public CheckItemID As Long Public Amount As Double |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 |
Private mclsPayrollItem As CPayrollItem Public Property Get PayrollItem() As CPayrollItem Set PayrollItem = mclsPayrollItem End Property Public Property Set PayrollItem(clsPayrollItem As CPayrollItem) Set mclsPayrollItem = clsPayrollItem End Property |
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
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Private mlCheckID As Long Private mdtCheckDate As Date Private mlParentPtr As Long Private Declare Sub CopyMemory Lib "kernel32" Alias "RtlMoveMemory" _ (dest As Any, Source As Any, ByVal bytes As Long) Private mclsCheckItems As CCheckItems Public Property Get CheckDate() As Date: CheckDate = mdtCheckDate: End Property Public Property Let CheckDate(ByVal dtCheckDate As Date): mdtCheckDate = dtCheckDate: End Property Public Property Get CheckID() As Long: CheckID = mlCheckID: End Property Public Property Let CheckID(ByVal lCheckID As Long): mlCheckID = lCheckID: End Property Public Property Get Parent() As CChecks: Set Parent = ObjFromPtr(mlParentPtr): End Property Public Property Set Parent(obj As CChecks): mlParentPtr = ObjPtr(obj): End Property Private Function ObjFromPtr(ByVal pObj As Long) As Object Dim obj As Object CopyMemory obj, pObj, 4 Set ObjFromPtr = obj ' manually destroy the temporary object variable ' (if you omit this step you'll get a GPF!) CopyMemory obj, 0&, 4 End Function |
I’ve already created the relationship between CheckItem and PayrollItem. Now I need to create the other relationships. In CEmployee, I do this
1 2 3 4 5 6 7 |
Private mclsChecks As CChecks Private Sub Class_Initialize() Set mclsChecks = New CChecks End Sub |
And in CCheck
1 2 3 4 5 6 |
Private mclsCheckItems As CCheckItems Private Sub Class_Initialize() Set mclsCheckItems = New CCheckItems End Sub |
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
1 2 |
Public gclsEmployees As CEmployees Public gclsPayrollItems As CPayrollItems |
In CPayrollItems, I create this Fill method
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
Public Sub Fill() Dim sh As Worksheet Dim rCell As Range Dim clsPayrollItem As CPayrollItem Set sh = wshPayrollItems For Each rCell In Intersect(sh.Columns(1), sh.UsedRange).Cells If rCell.Row > 1 Then If Not IsEmpty(rCell.Value) Then Set clsPayrollItem = New CPayrollItem With clsPayrollItem .ItemName = rCell.Value .ExpenseAccount = rCell.Offset(0, 1).Value .LiabilityAccount = rCell.Offset(0, 2).Value End With Me.Add clsPayrollItem End If End If Next rCell End Sub |
It simply loops through column A and adds a PayrollItem for each row. In CEmployees, I create this Fill method
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 Sub Fill() Dim sh As Worksheet Dim rCell As Range Dim clsEmployee As CEmployee Const lNETPAY As String = 1 Set sh = wshEmployees For Each rCell In sh.Range("A2", sh.Range("A2").End(xlDown)).Cells Set clsEmployee = New CEmployee With clsEmployee .EmployeeName = rCell.Value .SSN = rCell.Offset(0, 1).Value .Account1 = rCell.Offset(0, 2).Value .Routing1 = rCell.Offset(0, 3).Value .Type1 = rCell.Offset(0, 4).Value .Amount1 = rCell.Offset(0, 5).Value .Account2 = rCell.Offset(0, 6).Value .Routing2 = rCell.Offset(0, 7).Value .Type2 = rCell.Offset(0, 8).Value End With Me.Add clsEmployee Next rCell End Sub |
Same drill as CPayrollItem. My last class to fill is CChecks. I do that in a standard module
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 |
Sub FillClasses(sh As Worksheet) Dim rCell As Range Dim clsEmployee As CEmployee Dim clsCheck As CCheck Dim clsCheckItem As CCheckItem Set gclsPayrollItems = New CPayrollItems gclsPayrollItems.Fill Set gclsEmployees = New CEmployees gclsEmployees.Fill For Each rCell In Intersect(sh.UsedRange, sh.Columns(1)) If IsDate(rCell.Value) Then Set clsEmployee = gclsEmployees.EmployeeByName(rCell.Offset(0, 1).Value) Set clsCheck = clsEmployee.CheckByDate(rCell.Value, True) Set clsCheckItem = New CCheckItem With clsCheckItem Set .PayrollItem = gclsPayrollItems.PayrollItemByName(rCell.Offset(0, 2).Value) .Amount = rCell.Offset(0, 3).Value End With clsCheck.AddCheckItem clsCheckItem End If Next rCell End Sub |
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.
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 |
Public Property Get CheckByDate(dtCheck As Date, Optional bCreate As Boolean = False) As CCheck Dim clsCheck As CCheck For Each clsCheck In Me.Checks If clsCheck.CheckDate = dtCheck Then Exit For Next clsCheck If bCreate Then If clsCheck Is Nothing Then Set clsCheck = New CCheck clsCheck.CheckDate = dtCheck Me.AddCheck clsCheck End If If clsCheck.CheckDate <> dtCheck Then Set clsCheck = New CCheck clsCheck.CheckDate = dtCheck Me.AddCheck clsCheck End If End If Set CheckByDate = clsCheck End Property |
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.
1 2 3 4 5 |
Public Sub AddCheck(clsCheck As CCheck) mclsChecks.Add clsCheck End Sub |
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.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
Sub Test_FillClasses() Dim clsEmployee As CEmployee Dim clsCheck As CCheck Dim clsCheckItem As CCheckItem FillClasses wshChecks For Each clsEmployee In gclsEmployees For Each clsCheck In clsEmployee.Checks Debug.Print clsEmployee.EmployeeName, clsCheck.CheckDate For Each clsCheckItem In clsCheck.CheckItems With clsCheckItem Debug.Print .PayrollItem.ItemName, .Amount End With Next clsCheckItem Next clsCheck Next clsEmployee End Sub |
>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
[…] This post was mentioned on Twitter by gorocube, Excel MVP. Excel MVP said: NACHA Files Part 2 http://goo.gl/fb/Ec6RC […]
In your Fill method, I see you’re using a hard coded column index:
I like to refer them to an Enum so that if I insert a sheet column, I can easily adjust the relative column positions without adding 1 to everything.