Consolidating Timesheets

PHD did a post recently about consolidating data. I do this every other week with timesheets, so I thought I’d share my story.

I get timesheets as Excel workbooks via email and I save them to a special folder. I open them one-by-one and record the data on a summary sheet. Here’s the main entry point:

Sub SummarizePayroll()
    Dim vaFiles As Variant
    Dim wb As Workbook
    Dim i As Long
    Dim rControl As Range
    Dim clsTimeSheet As CTimeSheet
    vaFiles = Application.GetOpenFilename(, , , , True)
    If IsArray(vaFiles) Then
        For i = LBound(vaFiles) To UBound(vaFiles)
            Application.ScreenUpdating = False
            Set wb = Workbooks.Open(vaFiles(i))
            ‘Fill a class
           Set clsTimeSheet = GetTimeData(wb)
            ‘Find the right line on the summary sheet
           Set rControl = GetControlRecord(clsTimeSheet.LastNameFirst)
            ‘Write the time data
           If Not rControl Is Nothing Then
                rControl.Offset(0, 1).Resize(1, 6).Value = clsTimeSheet.TimeArray
            End If
            wb.Close True
            Application.ScreenUpdating = True
        Next i
    End If
    Application.ScreenUpdating = True
End Sub

It’s a typical scheme for my code: create a class, read data in, calculate, push data out. The clsTimeSheet class has the property LastNameFirst. This is a read-only property. The employee name is stored in the class as it appears on the timesheet (first name first), and LastNameFirst is used to match that to what’s recorded on the summary sheet.

Public Property Get LastNameFirst() As String
    Dim lSpace As Long
    Dim sTemp As String
    lSpace = InStrRev(msEEName, ” “)
    If lSpace > 0 Then
        sTemp = Mid(msEEName, lSpace + 1, Len(msEEName))
        sTemp = sTemp & “, “ & Left$(msEEName, lSpace – 1)
        sTemp = msEEName
    End If
    LastNameFirst = sTemp
End Property

What makes this a read-only property is that there isn’t a Let statement, only a Get statement. You can’t store the name in the last name first format, but you can retrieve it in that format. This isn’t a foolproof algorithm for switching names around, but with only 25 employees I don’t need foolproof.

Private Function GetTimeData(wb As Workbook) As CTimeSheet

    Dim j As Long
    Dim clsTimeSheet As CTimeSheet
    Dim ws As Worksheet
    Set clsTimeSheet = New CTimeSheet
    Set ws = wb.Sheets(1)
    With clsTimeSheet
        .EEName = ws.Range(“D2”).Value
        .Floating = ws.Range(“P21”).Value
        .Vacation = ws.Range(“P18”).Value
        .Holiday = ws.Range(“P19”).Value
        .Sick = ws.Range(“P20”).Value
        .TotalHours = ws.Range(“P22”).Value
        .Period = ws.Range(“D3”).Value2
    End With
    Set GetTimeData = clsTimeSheet
End Function

Here I’m just pulling data out of predefined ranges. For example, I know sick hours are stored in P20, so I hardcoded the address. The proper thing to do would be to make named ranges. “Do as I say…” and all that.

Finally, I spit the data back out via another read-only property, TimeArray.

Public Property Get TimeArray() As Variant
    Dim aTemp(1 To 1, 1 To 6) As Double
    aTemp(1, 1) = Me.Regular
    aTemp(1, 2) = mdOvertime
    aTemp(1, 3) = mdVacation
    aTemp(1, 4) = mdSick
    aTemp(1, 5) = mdHoliday
    aTemp(1, 6) = mdFloating
    TimeArray = aTemp
End Property

I like having these read-only properties that return arrays. I only write this to a range, so I create a two-dimensional, base-one array with 1 row and 6 columns. In other applications, I need an array for a range and one for a listbox. The listbox will return a base-zero array and I usually make a separate read-only property for that.

When I wrote the TimeArray property, I definitely liked working with the module level variables (like mdVacation). I don’t do that anymore. Now I would write

aTemp(1, 3) = Me.Vacation

That should be slower, which is why I preferred accessing the variable directly. It has to be slower. Yet, when I use the property rather than the variable, I notice no difference. I guess the apps I write just aren’t big enough for it to make a difference. And if I can’t tell the difference, I’ll take the autocomplete and increased readability. And in case you’re wondering, Regular is another read-only property so there’s no variable to read there.

Posted in Uncategorized

4 thoughts on “Consolidating Timesheets

  1. Hi Dick,

    I am trying to get a handle on Classes in Excel VBA. Is it possbile to share all the code for this so that I can see a real-life example of Classes in action.

    Thanks and regards


Leave a Reply

Your email address will not be published. Required fields are marked *