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
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Exactly!Originally Posted by mdmackillop
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.
Hi Guys,
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...
_________
Groetjes,
Joost Verdaasdonk
M.O.S. Master
Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
(I don't answer questions asked through E-mail or PM's)
Interesting read! Thanks for that link Bob!!
Regards, Zack Barresse
Check out the KB! :|: BOARD TAGS: WHAT ARE THEY AND HOW DO I USE THEM
What is a Microsoft MVP? | Free Microsoft Courses | My Book on Excel Tables
Blimey, another newbie!
That is probably a very good argument except:Originally Posted by MOS MASTER
- 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!
The latter yes, but the former - read the link.Originally Posted by MOS MASTER
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?Originally Posted by MOS MASTER
Should be, but unfortunately not. I have been harangued so many times I tend to do it, but I don't believe.Originally Posted by MOS MASTER
Yes Bob I'm very new at this stuff...Originally Posted by xld
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..Originally Posted by xld
You stick by your article and I stick by my theory... (Again said before preference)
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.Originally Posted by xld
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.Originally Posted by xld
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)
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.Originally Posted by xld
Will continue later.. bed time now.
_________
Groetjes,
Joost Verdaasdonk
M.O.S. Master
Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
(I don't answer questions asked through E-mail or PM's)
Non-believers shall be burned at the stake after being hung, drawn and quartered, and their code mocked and scorned!!I have been harangued so many times I tend to do it, but I don't believe.
Actually I tend to do it out of habit, but have found that in large Access projects it can make a difference in performance.
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
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 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.
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
I've read that post several times before and it's always a very interesting read...Originally Posted by xld
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)
I find the second word there interesting viz... it's specifically stated that its use is OptionalNothing 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.
Further down we find this...
IMO, this last sentence is probably the only real justification for using Set objectvar = NothingGenerally, 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 object
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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.Originally Posted by malik641
But this is the crux of what Matt is saying Joseph. Why treat string/long variables differently than object variables.Originally Posted by malik641
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 themOriginally Posted by malik641
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.. But yes, we are saying it will release object variables, just as it releases string variables.Originally Posted by malik641
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 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.Originally Posted by malik641
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 ), 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?).
I think you mis-understood me Moose. I was really commenting on the fact that we hadn't seen you in a long time.Originally Posted by MOS MASTER
You must be an academic Moose, preferring theory to the real worldOriginally Posted by MOS MASTER
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.
If you could turn up an example that is re-producable, that would be interesting.Originally Posted by MOS MASTER
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
Sorry Bob! Me and my big mouth... Nice to see you too again buddy...Originally Posted by xld
Please read back in my original post. I've learned this in the Real Word. (read server automation problems...) 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!)Originally Posted by xld
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)
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..)Originally Posted by xld
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..)
Had very little time today to search for my faulty automation program but will do ASAP.Originally Posted by xld
Thanx for this interesting converstation my friend.
_________
Groetjes,
Joost Verdaasdonk
M.O.S. Master
Mark your thread solved, when it has been, by hitting the Thread Tools dropdown at the top of the thread.
(I don't answer questions asked through E-mail or PM's)
I'm sorry. By connection I really meant a reference.Originally Posted by xld
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.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.. But yes, we are saying it will release object variables, just as it releases string variables.
Check that thread out here.
Didn't know that. Awesome, now I know exactly how With End structures workOne 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.
And I think I'll take a look at that "Advanced Visual Basic 6" book. Thanks Bob
New to the forum? Check out our Introductions section to get to know some of the members here. Feel free to tell us a little about yourself as well.
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!Originally Posted by malik641
Hey Moose,Originally Posted by MOS MASTER
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/lib...t.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 ). 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 ), but I won't hold my breath.
No need to release it, it's not bound, it's being referenced directly. This is similar...Originally Posted by xld
[vba]
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
[/vba]
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
Oh - and here's one for the style police to think about...
[vba]
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
[/vba]
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"?
You know you're really in trouble when the light at the end of the tunnel turns out to be the headlight of a train hurtling towards you
The major part of getting the right answer lies in asking the right question...
Made your code more readable, use VBA tags (this automatically inserts [vba] at the start of your code, and [/vba ] at the end of your code) | Help those helping you by marking your thread solved when it is.
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
MVP (Excel 2008-2010)
Post a workbook with sample data and layout if you want a quicker solution.
To help indent your macros try Smart Indent
Please remember to mark threads 'Solved'
Just would like to point out thatis 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.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.
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.
[vba]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 Sub[/vba]will 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 hand[vba]Set Sheet = Sheets("Sheet2") [/vba]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.....