jwise
06-08-2007, 09:08 AM
I'm having some trouble understanding when to create/destroy objects. My basic thought was to destroy (use ... = Nothing) for anything I created (Set ...). However, when I do this, I am getting myself in trouble.
I have a workbook with several worksheets. To perform this update, I will create an additional worksheet, or perhaps as many as 12 sheets. This is "weekly data" that is being summarized into "monthly data", and depending on how long it's been since the last update, a variable number of monthly worksheets will be created.
I hesitated on including this code but could not figure out any other way to properly illustrate this problem. The worksheet called "wsData" contains most of the information that will go into the new month's worksheet (wsMonth). Some additional and crucial data is in the wsControl worksheet. Subroutine "UpdateMonth" is the entry point, and this sub calls "BuildMonth".
The first call of BuildMonth goes as expected. None of the objects are needed in "UpdateMonth". UpdateMonth can look at wsControl (it is not named in this sub because it is the ACTIVE worksheet), and determine if it needs to build a monthly summary (wsMonth). After BuildMonth returns to UpdateMonth, UpdateMonth continues to scan its "active worksheet". However, as soon as it uses the "Cells(x,y)" reference to explicit cells, I get object errors.
Since the three worksheet objects were all created in BuildMonth, I destroyed them at the end of the routine. The destruction of wsControl is causing the problem. I have tried several ways to no avail.
To summarize this tedious description, the first routine uses the ACTIVE worksheet, and calls BuildMonth. BuildMonth creates a new worksheet by copying a template. It then copies data from both the original worksheet (which is no longer ACTIVE), and the wsData worksheet, building the wsMonthly worksheet. When BuildMonth returns to its caller, MonthUpdate is unable to use its object ("Cells(x,y)").
The code:
Sub MonthUpdate()
Dim i As Integer
Dim j As Integer
Dim MonthNum As Integer
Dim StartNum As Integer
Dim EndNum As Integer
For i = 4 To 15 'For 12 months, starting at row 4
If Cells(i, 11) = "Y" Then 'Already done this month?
GoTo skip_it 'Yes- head out!
End If
For j = Cells(i, 12) To Cells(i, 13) 'Get ready to check individual week
If Cells(j, 8) = "N" Then 'Week not done?
GoTo skip_it 'Week is not done, skip month
End If
Next j 'Loop through all the weeks for the month
MonthNum = i - 3
StartNum = Cells(i, 12)
EndNum = Cells(i, 13)
Call BuildMonth(MonthNum, StartNum, EndNum) 'Go Build this month
skip_it:
Next i 'Loop through all months
End Sub
'-----------------------
Private Sub BuildMonth(MonthNum As Integer, StartRow As Integer, EndRow As Integer)
Dim wsControl As Worksheet
Dim wsMonth As Worksheet
Dim wsData As Worksheet
Dim wsName As String
Dim MonthOf As String
Dim Mo As String
Dim numWeeks As Integer
Dim rcMsg As Integer
Dim ColPos(6) As Integer
rcMsg = MsgBox("BuildMonth Version 1.1 ***Module 1***")
Set wsControl = ActiveSheet
wsName = wsControl.Cells(MonthNum + 3, 10) 'Get month name
'numWeeks = wsControl.Cells(55, 2) 'amount of data
MonthOf = "Month of "
ColPos(1) = 2 'Look at the target worksheet
ColPos(2) = 3 'These 3 pairs are the column
ColPos(3) = 5 'numbers. This handles the nasty
ColPos(4) = 6 'problem of whether the first
ColPos(5) = 8 'week of year is a pay day.
ColPos(6) = 9
Worksheets("Templ3").Copy after:=Sheets(Sheets.Count) 'Copy template
ActiveSheet.Name = wsName 'Change Name to MonYY
Set wsMonth = ActiveSheet
'Mo = wsDate.Cells(i, 10) 'Get the month
MonthOf = MonthOf & Month(MonthNum) & " " & wsControl.Cells(2, 2) 'Change 3 char month to full
wsMonth.Cells(2, 1) = MonthOf 'Place full month Name & Year
XPos = 1
If wsControl.Cells(StartRow, 4) = "P" Then
XPos = 2 'Start in the correct box
End If
DataName = "DATA" & wsControl.Cells(24, 2) 'Build data sheet name
Set wsData = Worksheets(DataName) 'Access DATA sheet
'Set wsData = Activate.Sheets(DataName) 'access data sheet
For i = StartRow To EndRow 'Process 1st to last row in month
col = ColPos(XPos) 'Starting Box Position
wsMonth.Cells(3, col) = wsControl.Cells(i, 4) 'Get first week's date
wsMonth.Cells(4, col) = wsData.Cells(i, 3) 'Line 4
wsMonth.Cells(5, col) = wsData.Cells(i, 4) 'Line 5
wsMonth.Cells(10, col) = wsData.Cells(i, 5) 'Line 10
wsMonth.Cells(11, col) = wsData.Cells(i, 6) 'Line 11
wsMonth.Cells(16, col) = wsData.Cells(i, 7) 'Line 16
wsMonth.Cells(17, col) = wsData.Cells(i, 8) 'Line 17
wsMonth.Cells(39, col) = wsData.Cells(i, 9) 'Line 39
wsMonth.Cells(41, col) = wsData.Cells(i, 10) 'Line 41
wsMonth.Cells(42, col) = wsData.Cells(i, 11) 'Line 42
wsMonth.Cells(43, col) = wsData.Cells(i, 12) 'Line 43
wsMonth.Cells(45, col) = wsData.Cells(i, 13) 'Line 45
wsMonth.Cells(46, col) = wsData.Cells(i, 14) 'Line 46
XPos = XPos + 1 'Move to next box
Next i
wsControl.Cells(MonthNum + 3, 11) = "Y" 'Mark month as done
Set wsData = Nothing
Set wsMonth = Nothing
ActiveSheet = wsControl 'Reset as entered
End Sub
As you can see, I tried to fix this by resetting the Active worksheet, and this is invalid. I still can not make the VBA help systems tell me how to do this sort of thing. The first attempt simply had a third "Set wsControl = Nothing" statement, and this generated the error in MonthUpdate because its object for "Cells..." was gone.
I would think the solution to this would tell me how to restore the object structures as they were on first entry to BuildMonth.
TIA
PS... The hard drive on this machine failed and I spent a tremendous amount of time recovering. What fun!
I have a workbook with several worksheets. To perform this update, I will create an additional worksheet, or perhaps as many as 12 sheets. This is "weekly data" that is being summarized into "monthly data", and depending on how long it's been since the last update, a variable number of monthly worksheets will be created.
I hesitated on including this code but could not figure out any other way to properly illustrate this problem. The worksheet called "wsData" contains most of the information that will go into the new month's worksheet (wsMonth). Some additional and crucial data is in the wsControl worksheet. Subroutine "UpdateMonth" is the entry point, and this sub calls "BuildMonth".
The first call of BuildMonth goes as expected. None of the objects are needed in "UpdateMonth". UpdateMonth can look at wsControl (it is not named in this sub because it is the ACTIVE worksheet), and determine if it needs to build a monthly summary (wsMonth). After BuildMonth returns to UpdateMonth, UpdateMonth continues to scan its "active worksheet". However, as soon as it uses the "Cells(x,y)" reference to explicit cells, I get object errors.
Since the three worksheet objects were all created in BuildMonth, I destroyed them at the end of the routine. The destruction of wsControl is causing the problem. I have tried several ways to no avail.
To summarize this tedious description, the first routine uses the ACTIVE worksheet, and calls BuildMonth. BuildMonth creates a new worksheet by copying a template. It then copies data from both the original worksheet (which is no longer ACTIVE), and the wsData worksheet, building the wsMonthly worksheet. When BuildMonth returns to its caller, MonthUpdate is unable to use its object ("Cells(x,y)").
The code:
Sub MonthUpdate()
Dim i As Integer
Dim j As Integer
Dim MonthNum As Integer
Dim StartNum As Integer
Dim EndNum As Integer
For i = 4 To 15 'For 12 months, starting at row 4
If Cells(i, 11) = "Y" Then 'Already done this month?
GoTo skip_it 'Yes- head out!
End If
For j = Cells(i, 12) To Cells(i, 13) 'Get ready to check individual week
If Cells(j, 8) = "N" Then 'Week not done?
GoTo skip_it 'Week is not done, skip month
End If
Next j 'Loop through all the weeks for the month
MonthNum = i - 3
StartNum = Cells(i, 12)
EndNum = Cells(i, 13)
Call BuildMonth(MonthNum, StartNum, EndNum) 'Go Build this month
skip_it:
Next i 'Loop through all months
End Sub
'-----------------------
Private Sub BuildMonth(MonthNum As Integer, StartRow As Integer, EndRow As Integer)
Dim wsControl As Worksheet
Dim wsMonth As Worksheet
Dim wsData As Worksheet
Dim wsName As String
Dim MonthOf As String
Dim Mo As String
Dim numWeeks As Integer
Dim rcMsg As Integer
Dim ColPos(6) As Integer
rcMsg = MsgBox("BuildMonth Version 1.1 ***Module 1***")
Set wsControl = ActiveSheet
wsName = wsControl.Cells(MonthNum + 3, 10) 'Get month name
'numWeeks = wsControl.Cells(55, 2) 'amount of data
MonthOf = "Month of "
ColPos(1) = 2 'Look at the target worksheet
ColPos(2) = 3 'These 3 pairs are the column
ColPos(3) = 5 'numbers. This handles the nasty
ColPos(4) = 6 'problem of whether the first
ColPos(5) = 8 'week of year is a pay day.
ColPos(6) = 9
Worksheets("Templ3").Copy after:=Sheets(Sheets.Count) 'Copy template
ActiveSheet.Name = wsName 'Change Name to MonYY
Set wsMonth = ActiveSheet
'Mo = wsDate.Cells(i, 10) 'Get the month
MonthOf = MonthOf & Month(MonthNum) & " " & wsControl.Cells(2, 2) 'Change 3 char month to full
wsMonth.Cells(2, 1) = MonthOf 'Place full month Name & Year
XPos = 1
If wsControl.Cells(StartRow, 4) = "P" Then
XPos = 2 'Start in the correct box
End If
DataName = "DATA" & wsControl.Cells(24, 2) 'Build data sheet name
Set wsData = Worksheets(DataName) 'Access DATA sheet
'Set wsData = Activate.Sheets(DataName) 'access data sheet
For i = StartRow To EndRow 'Process 1st to last row in month
col = ColPos(XPos) 'Starting Box Position
wsMonth.Cells(3, col) = wsControl.Cells(i, 4) 'Get first week's date
wsMonth.Cells(4, col) = wsData.Cells(i, 3) 'Line 4
wsMonth.Cells(5, col) = wsData.Cells(i, 4) 'Line 5
wsMonth.Cells(10, col) = wsData.Cells(i, 5) 'Line 10
wsMonth.Cells(11, col) = wsData.Cells(i, 6) 'Line 11
wsMonth.Cells(16, col) = wsData.Cells(i, 7) 'Line 16
wsMonth.Cells(17, col) = wsData.Cells(i, 8) 'Line 17
wsMonth.Cells(39, col) = wsData.Cells(i, 9) 'Line 39
wsMonth.Cells(41, col) = wsData.Cells(i, 10) 'Line 41
wsMonth.Cells(42, col) = wsData.Cells(i, 11) 'Line 42
wsMonth.Cells(43, col) = wsData.Cells(i, 12) 'Line 43
wsMonth.Cells(45, col) = wsData.Cells(i, 13) 'Line 45
wsMonth.Cells(46, col) = wsData.Cells(i, 14) 'Line 46
XPos = XPos + 1 'Move to next box
Next i
wsControl.Cells(MonthNum + 3, 11) = "Y" 'Mark month as done
Set wsData = Nothing
Set wsMonth = Nothing
ActiveSheet = wsControl 'Reset as entered
End Sub
As you can see, I tried to fix this by resetting the Active worksheet, and this is invalid. I still can not make the VBA help systems tell me how to do this sort of thing. The first attempt simply had a third "Set wsControl = Nothing" statement, and this generated the error in MonthUpdate because its object for "Cells..." was gone.
I would think the solution to this would tell me how to restore the object structures as they were on first entry to BuildMonth.
TIA
PS... The hard drive on this machine failed and I spent a tremendous amount of time recovering. What fun!