Solved: Set Variables to Nothing

[johnske]

With all due respect, I think that with the exception of Norie, most of you seem to be missing a very important point here...

Specifically, there has been a great deal of focus in the discussion on "creating an instance" of something when the statement Set MyObjVar = 'something' is used and we then have to get rid of this "instance" of the object.

I originally posted quotes from the VBA help files alluding to the incorrectness of that all-encompassing idea (Reference: Set Statement). and I'll go though this bit by bit

Quote = "Generally, when you use Set to assign an object reference to a variable, no copy of the object is created for that variable. Instead, a reference to the object is created."


Got that? Generally No - repeat No copy of the object is created, so there is no instance that has been created. There's merely a reference created that points us in the direction of the actual object. When we read further down we find the single exception to the general rule...

Quote = "However, when you use the New keyword in the Set statement, you are actually creating an instance of the object."


i.e. It's only when the New keyword is used in conjunction with the Set statement that an instance of the object is created. As I said before, this is the only time that you could even perhaps, just maybe, justify the use of Set MyObjVar = Nothing as a catch-all at the end of a procedure.


But (as Gerry pointed out) at the end of the procedure a variable that's been declared as a procedure level variable automatically goes out of scope - and the object variable is set to nothing anyway. This is alluded to in the previous part of the same help file (Viz: the use of Nothing is optional).

However, the use of Set MyObjVar = Nothing can be justified (for purposes of conserving resources) for cases such as when we're using an object in the very early part of a very long procedure (or, for a public object variable).

[mdmackillop]

A practical problem.
I modified the questioner's code in this example, but this solution exhausts my PC resources and won't complete (I get to Depot 50). Without changing the methodology, is there a way to release the resources to allow completion. We have a solution (thanks to Norie) using a filter, so it's just the resource thing I'm interested in.
Regards
Malcolm

[johnske]

Does this do what's intended?

[mdmackillop]

Hi John,
Your revision creates separate workbooks, which is nor required, but you have changed the methodology to avoid the copying (as was done in the actual solution). If this was not an option, how do I release the resources to work with the code as written?
Regards
Malcolm

[johnske]

Oh, ok - one workbook...

Problem is that while it's very very slow because of all the selecting, I'm not getting an 'out of resources' message on my 64MB RAM and it does complete. I don't think this has anything to do with the object variables as such, I think the (resources) problem may be connected with having too much info on the clipboard so I'd use an Application.CutCopyMode = False after each copy, or, bypass the clipboard by changing it to ...

[VBA]
For DepN = LBound(DNum) To UBound(DNum)
WkbDest.Sheets.Add after:=Sheets(Sheets.Count)
WkbDest.ActiveSheet.Name = Dep(DepN)
With WksSource
.Activate
.Cells(1, 1).AutoFilter Field:=1, Criteria1:=DNum(DepN)
.UsedRange.Copy WkbDest.Sheets(Dep(DepN)).Cells(1, 1)
.ShowAllData
End With
Next DepN
[/VBA]Noting that SpecialCells always refers to the UsedRange and in the example given there are only visible cells i.e. it returns the same thing

[mdmackillop]

Hi John,
So the solution is to dump 704 MB RAM in the bin and have a little more patience.
Regards
Malcolm

[johnske]

All I'm saying is that I couldn't duplicate the problem on my (smaller) machine in an earlier office version (2K, and I think you have 2003?) and there's no way the object variables used could chew up enough resources to cause such a problem.