Create/Clean-up of Objects

[jwise]

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!

[Bob Phillips]

In principle, if you destroy all objects at the end of a procedure that within the scope of that procedure, you should be okay.

You destroy wsData and wsMonth which are within BuildMonth's scope, so you should be okay (although you could always destroy wsControl as well).

What you don't tell us is what difficulty you are getting into.

Also, why don't you pass the working sheet as a parameter to BuildMonth, rather than recreating the object within there?

[jwise]

Thanks again for your suggestion. I do admit that I do not fully understand it. I will look into this more when I return, as I am leaving for a week.

[jwise]

I guess I hit the wrong key... I did not finish from before.

I fixed the problem by adding the third "Set wsControl = Nothing" and then adding a "Worksheets("DATE").Activate"


Set wsData = Nothing
Set wsMonth = Nothing
Set wsControl = Nothing

Worksheets("DATE").Activate 'Reset as entered


This is the substitted code.

The problem I was having was on return to MonthUpdate, as soon as the "Cells(x,y)" was encountered, I received "object errors". I looked at it and was pretty sure that it was because BuildMonth defined the three objects for the three worksheets, and later destroyed them. So on return to MonthUpdate, the object was gone for "Cells". Does this sound correct?

The real problem was I could not use the documentation to find the correct way to make the "DATE" worksheet ACTIVE again. I pulled out all my reference texts and got nowhere. So I continued reading Shepherd's text (with the 20 page explanation of the Excel object model) and found an explanantion of "how to use the Object Browser". I played with this, searching on "Activate" until I found the "Worksheets("DATE").Activate". This worked...

So I am back to my usual gripe: There must be a better way to navigate/use the online documentation that I am unaware of.

Thanks again for your assistance.

[JonPeltier]

ActiveSheet = wsControl 'Reset as entered


As you can see, I tried to fix this by resetting the Active worksheet, and this is invalid.
The syntax you need is:
Set ActiveSheet = wsControl
I have been told that it's counterproductive to set everything = Nothing at the end of your routines, because VBA does this automatically. Yet many other sources implore you to do so. I still do usually, but more as a reminder to myself the programmer that I'm done with these things.

[johnske]

As Jon has said, there is no real need to set a local object variable equal to nothing. (i.e. when using the syntax Set objectvar = objectexpression).

This type of local variable is merely a reference or 'pointer' to an existing object and doesn't create a new instance of that object, so there is nothing there that needs to be specifically destroyed - just let Visual Basic do its job
A local variable is normally destroyed when the procedure in which it is declared is finished executing.

However, a different situation arises when Set is used in conjunction with the keyword New to automate an application (e.g. Set objOL = New Outlook.Application). In that case the object variable creates a hidden instance of the object that can cause problems if it's not dealt with properly, so the hidden object should always be set equal to Nothing when you're finished with it as Nothing is then used to
explicitly destroy an application-level object variable used to automate another application

If you read the excerpt quoted below carefully you'll see that Set objectvar = Nothing is only intended for that one particular case (i.e. application-level objects) and is not really intended to be used with local object variables. i.e. 'Nothing' is only meant to be used when you have used Set objectvar = New objectexpression to automate another hidden application.

Note that the keyword New can also be used to create local object variables such as Collections, and while Set objectvar = Nothing is certainly not intended for such cases, if you are in doubt, or can't remember when Set objectvar = Nothing should or shouldn't be used, an easy general rule to follow is to set every object variable created in conjunction with the keyword New equal to Nothing when finished with the variable, and if the New keyword isn't used ... just don't bother about it - you're only wasting time.


Quotes from: MS Visual Basic Programmers Guide (2000 - Chapter 7 "Office Object Models")] (http://www.microsoft.com/technet/prodtechnol/office/office2000/proddocs/opg/part2/ch04.mspx)


Shutting Down an Object Created by Using Automation
A local variable is normally destroyed when the procedure in which it is declared is finished executing.

However, it is good programming practice to explicitly destroy an application-level object variable used to automate another application by setting it equal to the Nothing keyword. Doing this frees any remaining memory used by the variable.

For some Application objects, you may also have to use the object's Quit method to completely destroy an object variable and free up the memory it is using. As a general rule, it's safest to do both: Use the Quit method and then set the object variable equal to the Nothing keyword.

Note For the OfficeBinder object, use the Close method instead of the Quit method and then set the object variable equal to the Nothing keyword.

[JonPeltier]

Thanks for clarifying my statement. Let me add a couple comments.

Apparently setting a local object variable = Nothing at the end of a procedure is akin to setting a local numerical variable = 0 or a local string variable = "". It doesn't make sense, because it will be destroyed anyway.

Application-level objects created using CreateObject should also be explicitly Quit if necessary then Set = Nothing.

Many times during operation of a procedure you want to check the status of an object variable. You can re-initialize such a variable using Set MyVar = Nothing.