Should VB variants be set to nothing?

I always SET object = nothing at the end of a procedure.

Should I do the same with variants, given that they occupy lots of memory?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

When a procedure goes out of scope, all variable storage within it's scope should be freed and returned to the resource pool. That includes objects as well as variants.  Explicitly clearing them at the end of a procedure is a legacy practice to compensate for memory leaks in VB.  It's my understanding that the product is no longer so-afflicted and that the explicit reference may actually be detrimental to the garbage collection process.

I'd like to hear from other experts on this.
Mike TomlinsonMiddle School Assistant TeacherCommented:
"at the end of a procedure"

If the variables are local to that procedure then they will be automatically garbage collected anyways!

Setting these variables to nothing  ~may~ help the process along a little faster but it isn't necessary.  I wouldn't worry about it unless your machine has very little RAM or you are calling this sub in rapid succession MANY times...

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
TimHudspithAuthor Commented:
Idle Mind - I'm interested in your last point.

My question stems from a pivot application I've been having problems with. I have a couple of pivots on several pages. The user clicks an item in one pivot, the clicked cell value becomes the pagefield value of the next pivot. This second pivot therefore refreshes to display a drilldown of the item clicked in pivot 1. The second pivot, having lost its formatting - as is the way with pivots - is then subjected to a sequence of formatting procedures to tidy it back up to its original condition.

For weeks this thing crashed Excel again and again and I just coudn't work out why. The reason was my failure to set range objects to nothing. I've never bothered to do this in other apps, however this application can receive hundreds of clicks in a session, so the build-up of range objects without being set to nothing was causing memory leak.

I've been through every procedure in the workbook with a fine toothcomb and set every object to nothing at the close of a procedure. The performance is now near-perfect, but it still crashes Excel in one particular place: a procedure that deals with the labelrange of a datafield. There are five datafields; I loop through each's labelrange, applying different formatting depending on the value - it crashes on the fifth datafield. But only after really pushing the application hard for 5 minutes - clicking non-stop in rapid succession to ensure it's indestructible (the users are an impatient bunch - I cannot put it on release if there is any chance of it crashing).

So I'm left wondering where on earth the remaining memory leak can be.
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

It's an ancient practice.  But you're using ancient languages.  Freeing memory can't hurt.
Mike TomlinsonMiddle School Assistant TeacherCommented:
I've done very little coding in the Excel environment sorry...   =\

Are these "range objects" declared locally in the sub? a global level?...or are these application constructs you  access direclty thru the Excel framework?
TimHudspithAuthor Commented:
The range objects are declared locally, including ByVal object arguments.
TimHudspithAuthor Commented:
Idle_Mind - can you expand on your point about rapid succession?
Mike TomlinsonMiddle School Assistant TeacherCommented:
I was more thinking along the lines of a sub repeatedly called by a looping structure...
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic Classic

From novice to tech pro — start learning today.