PDA

View Full Version : Solved: Set Variables to Nothing



mdmackillop
07-11-2006, 02:19 PM
Apart from "housekeeping", what is the purpose in setiing range and other variables to Nothing at the end of a routine. Does it really impact on performance? Why not reset Long, Integer etc. variables?
Regards
MD

Bob Phillips
07-11-2006, 02:31 PM
Apart from "housekeeping", what is the purpose in setiing range and other variables to Nothing at the end of a routine. Does it really impact on performance? Why not reset Long, Integer etc. variables?
Regards
MD

Exactly!

Some would say that it is necessary to clear memory out, but that means that they do not trust VBA to tidy up when an object variable goes out of scope. Yet they trust VBA to tidy up strings, longs etc.!

A very interesting post on the topic can be found at http://tinyurl.com/j9qkf by Matt Curland, who just happens to be the designer of Intellisense in VB.

I have alos heard some say that using automation can cause Excel not to close if objects are not explicyly cleared, but I have never encountered it.

But at the end, it takes little effort to do it, and it's far better than arguing with the style police.

MOS MASTER
07-11-2006, 02:51 PM
Hi Guys, :hi:

One of my favorite topics.. a never ending discussion!

IMO this all boils down to thinking ahead (scope wise), gaining some speed (clearing allocated memory in big subs, and try to program with less errors by hopefully not having objects (that your not using anymore) be a resource hog)

The theory: (very short)

When you create a new instance of a object, that instance exists in memory until the object variable goes out of scope, OR until you "Set it to Nothing" (explicitly)

If you build a big sub with custom objects (classes if you will) this is an important issue to consider, because it's all to easy to leave these object blogging your memory when they are no longer of any use. And these will ty your resources up without any need for it. (clearing objects is very easy .. so why not do it!)

And not only setting the object to Nothing is a good thing to do. But while you're at it remember that before clearing them out of the memory its a very good idea to CLOSE them properly too. (like database objects en files, etc...)

I've thougt my self to Always close my objects properly and always set them to nothing. (unless I forget it of course) This because I had a lot of "Automation Server" problems while Intergrading Word in several cross Office automation projects. At that time I was still using crash and burn programming techniques...

But it probably all boils down to preference at the end...:whistle:

Zack Barresse
07-11-2006, 03:02 PM
Interesting read! Thanks for that link Bob!! :yes

Bob Phillips
07-11-2006, 03:12 PM
Blimey, another newbie!


The theory: (very short)

When you create a new instance of a object, that instance exists in memory until the object variable goes out of scope, OR until you "Set it to Nothing" (explicitly)

If you build a big sub with custom objects (classes if you will) this is an important issue to consider, because it's all to easy to leave these object blogging your memory when they are no longer of any use. And these will ty your resources up without any need for it. (clearing objects is very easy .. so why not do it!)

That is probably a very good argument except:

- when was the last time that you saw any code when the coder released objects partway through a procedure. Nice theory, in practice, nah, get out of here!


And not only setting the object to Nothing is a good thing to do. But while you're at it remember that before clearing them out of the memory its a very good idea to CLOSE them properly too. (like database objects en files, etc...)

The latter yes, but the former - read the link.


I've thougt my self to Always close my objects properly and always set them to nothing. (unless I forget it of course) This because I had a lot of "Automation Server" problems while Intergrading Word in several cross Office automation projects. At that time I was still using crash and burn programming techniques...
As I said, I have heard this loads, never had it myself. But again, I ask you, do you set all longs to 0 at the end, and all strings to empty?


But it probably all boils down to preference at the end...:whistle:

Should be, but unfortunately not. I have been harangued so many times I tend to do it, but I don't believe.

MOS MASTER
07-11-2006, 03:24 PM
Blimey, another newbie!
Yes Bob I'm very new at this stuff... :)


That is probably a very good argument except:

- when was the last time that you saw any code when the coder released objects partway through a procedure. Nice theory, in practice, nah, get out of here!
True, true and very easy to say cause it's just as difficult too proove the opposite! (Like I said in the opening sentence a never ending discussion) We've all experienced strange errors and things we can't explain all that easy..

You stick by your article and I stick by my theory... (Again said before preference) :*)


The latter yes, but the former - read the link.Get me a link of MS where the same is stated please... and then I'll look up on it.. I can say loads of stuff.



As I said, I have heard this loads, never had it myself. But again, I ask you, do you set all longs to 0 at the end, and all strings to empty?
I'm going to bed real soon Now cause its night over here. But tomorrow I will try to find a project a worked on a couple of years ago with the "Server Automation" problems. A good example always works best.

And Nopes I don't clean up my longs and other variables at the end. But If I have Public variables or Private variables declared on top level I for sure will set them to there default value at the top of the procedure or function. (no need to rely on the compiler... rely on what you can for see)



Should be, but unfortunately not. I have been harangued so many times I tend to do it, but I don't believe.
That I can believe and Like I said before sigh... I started doing it when I encountered problems with it and been doing it ever since... So happy for you you've never come across these problems. :)

Will continue later.. bed time now.

geekgirlau
07-11-2006, 04:12 PM
I have been harangued so many times I tend to do it, but I don't believe.

Non-believers shall be burned at the stake after being hung, drawn and quartered, and their code mocked and scorned!! :devil2:

Actually I tend to do it out of habit, but have found that in large Access projects it can make a difference in performance.

malik641
07-11-2006, 05:51 PM
I agree with Zack, a very interesting read.


I'm new at this stuff too...so if I ask / say something dumb please don't chew me apart :ipray:


I just need some clarification. I thought that when you set Object Variables = Something they establish a connection with an object and by setting that object variable = Nothing it closes that connection. Which was the point of "Set = Nothing"....whether it be as small as a range object or connecting to another application. I never thought much about Integers or Strings....mostly because my view was that it had no connection to an object and I read that they are set back to 0 / False when leaving scope, which wouldn't need "Set = Nothing".

Maybe my views on this are misguided :think: sorry if what I'm saying sounds stupid to some of you guys...please remember I only know what I know from books and the internet (mostly this forum)....I never took any classes on this stuff.


So what Bob (and Mr. Curland) is saying is that VB does it for you? Whether it's an external application or something as small as a range object?

How can that be tested? I'm not trying to challenge, I'm just curious to try it myself.

johnske
07-11-2006, 06:54 PM
Exactly!

Some would say that it is necessary to clear memory out, but that means that they do not trust VBA to tidy up when an object variable goes out of scope. Yet they trust VBA to tidy up strings, longs etc.!

A very interesting post on the topic can be found at http://tinyurl.com/j9qkf by Matt Curland, who just happens to be the designer of Intellisense in VB.

I have alos heard some say that using automation can cause Excel not to close if objects are not explicyly cleared, but I have never encountered it.

But at the end, it takes little effort to do it, and it's far better than arguing with the style police.I've read that post several times before and it's always a very interesting read...

As a frequent victim of the style police I also tend to just bow to their demands rather than argue the toss with them. Anyway, here's what Microsoft have to say about it (via their VBA Help files)


Nothing Optional. Discontinues association of objectvar with any specific object. Assigning Nothing to objectvar releases all the system and memory resources associated with the previously referenced object when no other variable refers to it.I find the second word there interesting viz... it's specifically stated that its use is Optional

Further down we find this...



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... ..Because such variables are references to the object rather than copies of the object, any change in the object is reflected in all variables that refer to it. However, when you use the New keyword in the Set statement, you are actually creating an instance of the objectIMO, this last sentence is probably the only real justification for using Set objectvar = Nothing

Bob Phillips
07-12-2006, 02:40 AM
I just need some clarification. I thought that when you set Object Variables = Something they establish a connection with an object and by setting that object variable = Nothing it closes that connection.

Not quite right. It does not establish a connection, at least not in my understanding of the meaning of the word connection, but rather it craetes an instance of that object, reserving its memory space. This is why some say you should always set it to nothing afterwards, to release that memory. My argument, or my echoing of Matt's argument is that VB does that for you when the variable goes out of scope.


I never thought much about Integers or Strings....mostly because my view was that it had no connection to an object and I read that they are set back to 0 / False when leaving scope, which wouldn't need "Set = Nothing".

But this is the crux of what Matt is saying Joseph. Why treat string/long variables differently than object variables.


I Maybe my views on this are misguided :think: sorry if what I'm saying sounds stupid to some of you guys...please remember I only know what I know from books and the internet (mostly this forum)....I never took any classes on this stuff.

Not at all. We were all at that point at some time. Like you, I never took classes, and in many ways a class is only something telling you about his experineces and prejuduces, rather then raeding about them :yes


So what Bob (and Mr. Curland) is saying is that VB does it for you? Whether it's an external application or something as small as a range object?

Not sure what you mean by whether it is an external application. It won't disconnect an ADO connection for instance. Well actually, it just might, but that is going further than even I will put my trust.:yes. But yes, we are saying it will release object variables, just as it releases string variables.

One thing I just thought about that hasn't been raised before is implict object references. When you do

With myRange
'do something
End With

you create an implict object variable that uses memory just as an explicit object variable does. There is no way you can refernce that variable, so you couldn't explicitly release that even if you wanted to.


I How can that be tested? I'm not trying to challenge, I'm just curious to try it myself.
I think that is one of those things that you can only prove they don't work, when the build of memory allocated is so great that it causes a visible problem.

May I say that the tone of this thread is very civilised, as you would expect (and get at VBAX)> The last time I raised this argument (I do it regulalrly, just for the fun :devil2:), it goit quite heated, with one guy even saying accusing Matt Curland of being 'some bloke on a usenet thread'. Now that I did take exception too, Matt is one of my VB heroes (anyone read his Advanced Visual Basic 6?).

Bob Phillips
07-12-2006, 02:45 AM
Yes Bob I'm very new at this stuff... :)
I think you mis-understood me Moose. I was really commenting on the fact that we hadn't seen you in a long time.


You stick by your article and I stick by my theory... (Again said before preference) :*)
You must be an academic Moose, preferring theory to the real world :devil2:

Your argument is similar to a previous view I heard on this. The point is that Matt Curland is not just some casual user like you and I, he worked on the development of VB, he works for MS! And he wrote a seminal VB book, Advanced Visual Basic 6. He and Bruce McKinney are right up there.


That I can believe and Like I said before sigh... I started doing it when I encountered problems with it and been doing it ever since...
If you could turn up an example that is re-producable, that would be interesting.

Norie
07-12-2006, 06:53 AM
Straight from VBA Help.


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

MOS MASTER
07-12-2006, 01:49 PM
I think you mis-understood me Moose. I was really commenting on the fact that we hadn't seen you in a long time.
Sorry Bob! Me and my big mouth... :p Nice to see you too again buddy...



You must be an academic Moose, preferring theory to the real world :devil2:
Please read back in my original post. I've learned this in the Real Word. (read server automation problems...) :tease: Like you I'm self thought in a lot of ways. And yes I do have some education on the matter and I've read loads of books and other content on the matter. (so yes LOVE the theory too.. a regular nerd if you please!)

No really the only way to gain control over VBA (or any other programming language that is) is Do It! Do it some more... KEEP on Doing it... (And can't say it enough... our little hobby over here.. answering other peoples questions is a WIN, WIN situation for both the OP and the person answering the question.. man learned a lot that way)



Your argument is similar to a previous view I heard on this. The point is that Matt Curland is not just some casual user like you and I, he worked on the development of VB, he works for MS! And he wrote a seminal VB book, Advanced Visual Basic 6. He and Bruce McKinney are right up there.
That's fine by me Bob. I believed you the first time you said that. I've a great respect for knowledgable people but that doesn't say to me I should go with there gospel. (plauseble as it seams.. don't get me wrong here) My heroes are all that help each other. (he does that too as I can read..)

As Always MS put a great deal of content on there websites and its a PITA that the content is very often in contradiction with previous content you've browsed on the same matter.

Like I said I've thought myself todo this always because of the problems I had with it in the past. And because it's considerd good programming practise (yes read that part :) ) And I do think that MS didn't create the keyword "Nothing" for the fun of it.

So for now I keep on using it as I do allready and will continu todo so until I'm fully convinced of the lack of use of it. (A great article by MS with some good samples would help a big deal... If they exists..)



If you could turn up an example that is re-producable, that would be interesting.
Had very little time today to search for my faulty automation program but will do ASAP.

Thanx for this interesting converstation my friend. :thumb

malik641
07-12-2006, 04:14 PM
Not quite right. It does not establish a connection, at least not in my understanding of the meaning of the word connection, but rather it craetes an instance of that object, reserving its memory space. This is why some say you should always set it to nothing afterwards, to release that memory. My argument, or my echoing of Matt's argument is that VB does that for you when the variable goes out of scope. I'm sorry. By connection I really meant a reference.


Not sure what you mean by whether it is an external application. It won't disconnect an ADO connection for instance. Well actually, it just might, but that is going further than even I will put my trust.:yes. But yes, we are saying it will release object variables, just as it releases string variables. I meant like if your are creating an instance of Word in Excel or something like this. And I'd have to say that no, I'm pretty sure it doesn't close connections for you. DRJ recently had a thread about a connection to a DB that he forgot to close a connection in one part of his code, and it was giving him problems.

Check that thread out here (http://www.vbaexpress.com/forum/showthread.php?t=5770).


One thing I just thought about that hasn't been raised before is implict object references. When you do

With myRange
'do something
End With

you create an implict object variable that uses memory just as an explicit object variable does. There is no way you can refernce that variable, so you couldn't explicitly release that even if you wanted to. Didn't know that. Awesome, now I know exactly how With End structures work :thumb


And I think I'll take a look at that "Advanced Visual Basic 6" book. Thanks Bob :)

Bob Phillips
07-12-2006, 04:25 PM
And I think I'll take a look at that "Advanced Visual Basic 6" book. Thanks Bob :)

Well if you do, enjoy it, but be aware, it is not easy reading. It is not a Walkenbach type book, it is heavy, heavy!

Bob Phillips
07-12-2006, 04:37 PM
... too many things to mention specifically



Hey Moose,

Again, not a dig at you. You are right to be sceptical, but you should be just as sceptical of anything MS publish as anything else (just take a look at this piece of rubbish http://msdn2.microsoft.com/en-us/library/microsoft.office.tools.excel.worksheet.parent.aspx), but life is far too short for us to be able to verify everything we ever hear or read, some things we need to have faith in. Our faith parameters may be different (I am very sceptical about anything MS say, but not an anti-MS as an example), but usually they arise from experinece. My experience of Matt Curland is that I trust him more than most MS technical writers.

Oh, and by the way, do you really think they created the keyword Nothing so that you could use it to set a variable to that? What about

If myObj Is Nothing Then
....

My last word on the subject. I am not advocating that anyone doesn't set to Nothing at the end. I think as Johnske said, it is a personal preference, so go with yours. All I did was to respond to a question by Malcolm, giving him my honest opinion (knowing that it would be a view held by few :devil2:). No-one here has convinced me otherwise yet, and I am sure few have been convinced otherwise. I personally would have more respect for anybody that clears an object variable once it is done with rather than waiting to the end to clear them all down at the end (the latter is how I do it when I do :whistle:), but I won't hold my breath.

johnske
07-12-2006, 05:40 PM
...One thing I just thought about that hasn't been raised before is implict object references. When you do

With myRange
'do something
End With

you create an implict object variable that uses memory just as an explicit object variable does. There is no way you can refernce that variable, so you couldn't explicitly release that even if you wanted to...No need to release it, it's not bound, it's being referenced directly. This is similar...


Option Explicit
Sub DoThis()
Dim Sheet As Worksheet
'(Sheet now refers the entire worksheets collection)

'now we're going to use the object variable 'Sheet' to
'look at the sheets collection but we're going to
'ignore any sheets that are not worksheets...
For Each Sheet In Sheets
Debug.Print Sheet.Name
Next
Debug.Print vbNewLine

'now we're going to SET the object variable 'Sheet'
'so that it refers only to one specific worksheet
Set Sheet = Sheets("Sheet2")
'check this...
Debug.Print Sheet.Name
Debug.Print vbNewLine

'the object variable 'Sheet' indeed now refers ONLY to Sheet2.
'However, we now want to use this variable to look at
'all the sheets in the worksheet collection, so do we
'need to Discontinue the association of Sheet with
'this specific object? If there IS a need, we'd better
'put Set Sheet = Nothing as the next line, if there's
'no need, this next bit will work fine without it...
For Each Sheet In Worksheets
Debug.Print Sheet.Name
Next
Debug.Print vbNewLine

'well that worked, so which sheet does the object variable
''Sheet' refer to now? Let's see...
If Not Sheet Is Nothing Then
Debug.Print Sheet.Name
Else
Debug.Print "Sheet Is Nothing"
End If

'What the... Sheet Is Nothing? Does this mean that
'the object variable 'Sheet' was automatically set to nothing
'by Visual Basic at some point?
'
'Yes it does, it was automatically set to nothing at the point
'where we decided to use "For Each Sheet In Worksheets"
'and it is still nothing after that loop so there's no need to
'"release" 'Sheet' from memory - it's already been done.
'
'i.e. "For Each Sheet In Worksheets" does not take up
'any system resources and does not need to be released
'because it was referencing the collection directly...
End Sub

johnske
07-12-2006, 07:15 PM
Oh - and here's one for the style police to think about...


Sub DoThis2()
'
Dim Sheet As Worksheet
'(Sheet now refers to the entire worksheets collection)
'
'now we're going to SET the object variable 'Sheet'
'so that it refers only to one specific worksheet
Set Sheet = Sheets("Sheet2")
'check this...
Debug.Print Sheet.Name
Debug.Print vbNewLine
'the object variable 'Sheet' indeed now refers ONLY to Sheet2.
'
'now let's finish this procedure without setting Sheet = Nothing
End Sub
'
'According to the style police the object variable 'Sheet' has not
'been released and is somehow taking up memory and resources
'in a similar manner to a public variable (and is not Nothing).
'
'According to others, the association is automatically discontinued
'and 'Sheet' was set = to nothing when "End Sub" is reached...
'
'We can test this by simply running the next procedure (Test)
'
Sub Test()
'
Dim Sheet As Worksheet
'
If Not Sheet Is Nothing Then
Debug.Print Sheet.Name
Else
Debug.Print "Sheet Is Nothing"
End If
'
'We get "Sheet Is Nothing" - Q.E.D.
'
End Sub


Of course if the object variable is a public variable then there may indeed be an argument for setting it equal to nothing, and, if it's only to be used near the beginning of a very long procedure and is not then required for the rest of it, there is another argument for setting it equal to nothing.

But to religiously set all object variables to nothing at the end of the procedure? - I don't think so, if you can't trust Visual Basic to do it's own 'tidying up' (along with all its other default actions) then I might ask "why are you using something so unreliable that you don't trust"? :devil2:

mdmackillop
07-12-2006, 11:19 PM
Thanks for all the interesting and varied opinions.
As as result I will probably:
1. Set module variables to Nothing at the end of a routine when I remember to do so.
2. Set module variables to Nothing when finished with them in long code structures.
3. Set Public variables to Nothing to avoid unexpected consequences.
4. Not be pedandic about contributors setting variables to Nothing in KB entries.
5. Sleep happy with the thought that if I forgot to release a variable, then the VBA gods will have taken pity on the little creature and done it for me.
Thanks to all.
Malcolm

fumei
07-13-2006, 09:37 AM
Just would like to point out that
Sub DoThis2()
'
Dim Sheet As Worksheet
'(Sheet now refers to the entire worksheets collection)
'
'now we're going to SET the object variable 'Sheet'
'so that it refers only to one specific worksheet
Set Sheet = Sheets("Sheet2")
'check this...
Debug.Print Sheet.Name
Debug.Print vbNewLine
'the object variable 'Sheet' indeed now refers ONLY to Sheet2.
'
'now let's finish this procedure without setting Sheet = Nothing
End Sub
'
'According to the style police the object variable 'Sheet' has not
'been released and is somehow taking up memory and resources
'in a similar manner to a public variable (and is not Nothing).
'
'According to others, the association is automatically discontinued
'and 'Sheet' was set = to nothing when "End Sub" is reached...
'
'We can test this by simply running the next procedure (Test)
'
Sub Test()
'
Dim Sheet As Worksheet
'
If Not Sheet Is Nothing Then
Debug.Print Sheet.Name
Else
Debug.Print "Sheet Is Nothing"
End If
'
'We get "Sheet Is Nothing" - Q.E.D.is not a good test. In Sub DoThis2, Sheet is declared, and when the Sub DoThis2 terminates so does the name. It is declared as a NEW "Sheet" in Sub Test. There is no connection between them. They are independent variables, and each is assigned its own memory address. So of course Sheet in Sub Test comes back as "Sheet is Nothing" - that Sheet never had a value given to it.

There is no doubt that the name Sheet in DoThis2 is removed. Once out of scope there is no Sheet. That is why you can't get anything for it in another procedure. There IS no Sheet anymore. The Sheet in Test is a completely different thing, with a completely different address allocation. The compiler does not say...oh, gee there once WAS a variable Sheet, gosh let's use it again. So if you try and get a value for the NEW Sheet - and one has not been given, of course it comes back as "Sheet is Nothing". Nothing was given to it. So the "test" (as a test of the previous Sheet) is not a test. You CAN'T test for the previous Sheet. It can not be referenced...it is out of scope.

The issue - if there really is one - is whether the actual memory address blocks are released. We KNOW the name is.

As for those who bring in the question, why don't you reset Long, String etc variables?

Excuse me people....but have you actually even tried?????? There is a damn good reason why we don't reset Long, String etc variables...

YOU BLOODY WELL CAN'T!

Long, String etc are NOT objects. Repeat...they are NOT objects. So stop thinking about them in the same way as using Set = object. They are different beasties.
Sub GetReal()
Dim i As Long
Dim strBlahBlah As String

i = 12345
strBlahBlah = "What a load of crap!"
Set i = Nothing
Set strBlahBlah = Nothing
End Subwill give a compile error "Object required."

So...yeah...there is a reason we don't set Long, String to Nothing. They are NOT objects, and you can't set them to nothing.

On the other handSet Sheet = Sheets("Sheet2") DOES make an object, and can be set to nothing.

So. The questions really is. Does VBA actually clean up OBJECT variables the same way it cleans up NON-OBJECT variables?

Who the hell knows? I sure don't. But I know I try to use Set = Nothing for objects (because I can), and I don't use lngNumber = Nothing...because I can't.

The argument about HEY! why not reset Long, Strings etc is moot. Toss it. I am surprised Matt Curland used it, as it is totally a bogus red herring.

lngNumber = 123458 and
lngNumber = 0

makes no difference at all. So resetting Long to 0, or String to "" means squat.

Set myObject = OBJECT and
Set myObject = Nothing

does mean something.

I am not coming down on either side vis-a-vis explicitly setting objects to Nothing. I simply do not know enough. Personally, I do. But the Long, String issue is a non-issue. Changing the value (to 0, or "") simply does not extinquish the memory allocation. Setting an object = Nothing DOES extinquish the memory allocation....or so they say.....

:rotflmao:

mdmackillop
07-13-2006, 09:49 AM
Hi Gerry,
Welcome to the Excel forum.
Nice point about long/string. Sometimes the obvious is hard to see!
Regards
Malcolm

MOS MASTER
07-13-2006, 10:19 AM
4. Not be pedandic about contributors setting variables to Nothing in KB entries.

Hi Malcom, :)

I do hope you reconsider that part. Cause clearing Objects explicitly with the keyword Nothing is still considerd good programming practise. And we do try to spread that kind of gospel with our knowledgebase...

We have talked about this subject before in the approvers forum but I can't find the thread al that easily but IMO we agreed on checking for correct closing and clearing of object references. (amongst things) A lot of KB's are checked on that issue.

Of course the decesion is up to you. :*)


5. Sleep happy with the thought that if I forgot to release a variable, then the VBA gods will have taken pity on the little creature and done it for me.
Thanks to all.


Amen to that brother!

MOS MASTER
07-13-2006, 10:24 AM
YOU BLOODY WELL CAN'T!
Please STOP Gerry you're killing ME! :rotflmao:


Hi Gerry,
Welcome to the Excel forum.


Priceless... :rotflmao:

Bob Phillips
07-13-2006, 10:25 AM
We have talked about this subject before in the approvers forum but I can't find the thread al that easily but IMO we agreed on checking for correct closing and clearing of object references. (amongst things) A lot of KB's are checked on that issue.

One reason I don't do KBs, I got fed up of getting them returned for insignificant matters, and being forced to use Americanisms.

fumei
07-13-2006, 10:27 AM
Killing you? Well I certainly don't want that.

Bob Phillips
07-13-2006, 10:38 AM
As for those who bring in the question, why don't you reset Long, String etc variables?

Excuse me people....but have you actually even tried?????? There is a damn good reason why we don't reset Long, String etc variables...

YOU BLOODY WELL CAN'T!


That was a typo on my part, I meant strings and arrays, so used to writing strings and longs I was in auto.

And you BLOODY WELL CAN clear strings, as the larger they are, the more memory they take. Ditto arrays.


So...yeah...there is a reason we don't set Long, String to Nothing. They are NOT objects, and you can't set them to nothing.

The only one to mention setting a string to nothing so far is you!


So. The questions really is. Does VBA actually clean up OBJECT variables the same way it cleans up NON-OBJECT variables?

Who the hell knows? I sure don't.

But do you know it doesn't? Don't bother answering that, you might rant on about modules not being strings.


The argument about HEY! why not reset Long, Strings etc is moot. Toss it. I am surprised Matt Curland used it, as it is totally a bogus red herring.

He didn't re Longs, as I said above it was my typo.

MOS MASTER
07-13-2006, 10:39 AM
One reason I don't do KBs, I got fed up of getting them returned for insignificant matters, and being forced to use Americanisms.

I'm sorry you feel that way but I can totally relate to what you're saying. (A PITA though we lost you in that way cause you're are a very knowledgable person....)

Perhaps things will change in the future. :)
</IMG>

Bob Phillips
07-13-2006, 10:43 AM
I'm sorry you feel that way but I can totally relate to what you're saying. (A PITA though we lost you in that way cause you're are a very knowledgable person....)

Perhaps things will change in the future. :)
</IMG>

Oddly enough, I hadn't noticed before, but my VBA ranking is greater than that possible for someone with only 3 KBs :think:

lucas
07-13-2006, 10:45 AM
So if it works without it why do it. Couldn't the same be said for Option Explicit?

MOS MASTER
07-13-2006, 10:48 AM
Oddly enough, I hadn't noticed before, but my VBA ranking is greater than that possible for someone with only 3 KBs :think:

I did notice that before... Wouldn't know why...:whistle:

MOS MASTER
07-13-2006, 10:52 AM
So if it works without it why do it. Couldn't the same be said for Option Explicit?

Steve it's NOT I repeat NOT proven yet that it does always work without it. But I agree it's a mather of preference if you use it.

And No Option Explicit is a totaly different subject and again relates to good programming practices and it helps you in many ways (example: by forcing you to declare your variables) writing your code. :)

lucas
07-13-2006, 10:55 AM
I don't see where anything has been proven here one way or the other......The subject was good programing practice

MOS MASTER
07-13-2006, 10:58 AM
I don't see where anything has been proven here one way or the other......The subject was good programing practice

If you mean it in that way the you should use it cause its considered good programming practice. :)

Zack Barresse
07-13-2006, 10:59 AM
So if it works without it why do it.
I think that's the underlying question here. I think that article by Matt Curland was very informative and educational. It will probably change some coding habits of mine and provoke some other testings to be completed. :whistle:


Couldn't the same be said for Option Explicit?
I agree with Joost, it's not the same.


Thanks for such a thought provoking post MD! And to all those contributing, this truly is a great thread!

lucas
07-13-2006, 11:06 AM
I'm just saying that our forum is directed towards folks learning(myself included). So shouldn't we be using top of the line methods as examples. Lots of code posted here without option explicit but when we publish to the kb its a different story. Hopefully this thread will help us sort and define what is good programing practice and we can come to a consensus on the subject.

Bob Phillips
07-13-2006, 12:07 PM
I'm just saying that our forum is directed towards folks learning(myself included). So shouldn't we be using top of the line methods as examples. Lots of code posted here without option explicit but when we publish to the kb its a different story. Hopefully this thread will help us sort and define what is good programing practice and we can come to a consensus on the subject.

Steve,

I think I agree with you on the first part. Neither Option Explicit nor setting to Nothing will give a problem ... in 99.9% of the cases (for serious developers).

But I know for a fact that Option Explicit save me making an error of wrongly using a variable that I haven't declared, and therby giving me a logic problem that might be very difficult to track down.

Others are saying that not setting object variables to nothing can also casue problems. Again, not every time perhaps, but again I would imagine that such a problem would be a bugger to track down.

As such, it could be argued that both are good examples of defensive programming. To my mind, that is nothing to do with style, but it is to do with programming practice. I definitely use Option Explicit implicitly, and I tend to also set object variables to Nothing, but not as religiously, because it doesn't hurt, and if it saves a problem down the line, then I am ahead of the game.

Bob Phillips
07-13-2006, 12:08 PM
I think that's the underlying question here. I think that article by Matt Curland was very informative and educational. It will probably change some coding habits of mine and provoke some other testings to be completed.

Then you should get hold of his book, it will turn your coding world upside down.

And if you do get it, and you then understand it, perhaps you could explain it to me :doh:

Bob Phillips
07-13-2006, 12:10 PM
Steve it's NOT I repeat NOT proven yet that it does always work without it.

It never will be, always is infinite. Can only prove the converse.

MOS MASTER
07-13-2006, 12:21 PM
As such, it could be argued that both are good examples of defensive programming. To my mind, that is nothing to do with style, but it is to do with programming practice. I definitely use Option Explicit implicitly, and I tend to also set object variables to Nothing, but not as religiously, because it doesn't hurt, and if it saves a problem down the line, then I am ahead of the game.

On this I agree in FULL! :)

MOS MASTER
07-13-2006, 12:23 PM
It never will be, always is infinite. Can only prove the converse.

The holy grail will come to us eventually! : pray2: (let's hope one of us stumbles up on it soon...)

johnske
07-13-2006, 04:19 PM
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
07-13-2006, 11:22 PM
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
07-14-2006, 01:07 AM
Does this do what's intended?

mdmackillop
07-14-2006, 02:11 AM
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
07-14-2006, 02:54 AM
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 ...


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
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
07-14-2006, 11:42 AM
Hi John,
So the solution is to dump 704 MB RAM in the bin and have a little more patience.
Regards
Malcolm

johnske
07-14-2006, 02:16 PM
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.