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:
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.
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)
Else
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.
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.
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
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.
I thought that Data Consolidate in Excel could work with multiple sheets or files and compare labels from the spreadsheets.
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
kanti
http://www.dailydoseofexcel.com/excel/PR_Control.zip
I don’t know how much help it will be, but there you go. No warranties, and all that.
Thanks Dick, I will now try to understand the code.