VBA Reset all variables

skorpionkz · Feb 26, 2014

Hi guys,

I have code with about 10 modules (each has different number of Sub procedures and lots of variables).

One of the modules has Launcher code which call other procedures.

It all works ok for most users on their laptops, but now I have 1 user (and bad luck that this user is my manager) who has strange things happening on his laptop with my code.

The code does not clear the variables after it finishes the work.

Example, if I have variable "lBisRow" that check if string (let say Product) exist and if so then take row number of it.
then next time User run the code again, and there is no Product String the "lBisRow" still will have value instead of being empty.

Is there is a way to clear all variables before End Sub?

hippiehacker · Feb 26, 2014

how are your Variables declared

here you can check the lifetime scope of variables Excel VBA Variables Scope and Lifetime. Scope and Lifetime of Excel VBA Variables

VB6/VBA uses deterministic approach to destoying objects. Each object stores number of references to itself. When the number reaches zero, the object is destroyed.

Object variables are guaranteed to be cleaned (set to <code>Nothing</code>) when they go out of scope, this decrements the reference counters in their respective objects. No manual action required.
There are only two cases when you want an explicit cleanup:

When you want an object to be destroyed before its variable goes out of scope (e.g., your procedure is going to take long time to execute, and the object holds a resource, so you want to destroy the object as soon as possible to release the resource).
When you have a circular reference between two or more objects.
If <code>objectA</code> stores a references to <code>objectB</code>, and <code>objectB</code> stores a reference to <code>objectA</code>, the two objects will never get destroyed unless you brake the chain by explicitly setting <code>objectA.ReferenceToB = Nothing</code> or <code>objectB.ReferenceToA = Nothing</code>.

The code snippet you show is wrong. No manual cleanup is required. It is even harmful to do a manual cleanup, as it gives you a false sense of more correct code.

If you have a variable at a class level, it will be cleaned/destroyed when the class instance is destructed. You can destroy it earlier if you want (see item <code>1.</code>).
If you have a variable at a module level, it will be cleaned/destroyed when your program exits (or, in case of VBA, when the VBA project is reset). You can destroy it earlier if you want (see item <code>1.</code>).

Access level of a variable (public vs. private) does not affect its life time.

skorpionkz · Feb 26, 2014

hippiehacker said:
If you have a variable at a module level, it will be cleaned/destroyed when your program exits (or, in case of VBA, when the VBA project is reset). You can destroy it earlier if you want (see item <code>1.</code>).

This is the problem
Some how even restarting of whole Application (in this case excel) does not help.
I don't know how to explain this better. This situation never happened on other laptops, just on this one. Code is exactly the same that other users have and for purpose of the test I run it on the same document.

My laptop run code smoothly but on my manager laptop it act as in example from post #1.

I have variables declared on the Module level.

chirag050675 · Dec 13, 2018

skorpionkz said:
This is the problem
Some how even restarting of whole Application (in this case excel) does not help.
I don't know how to explain this better. This situation never happened on other laptops, just on this one. Code is exactly the same that other users have and for purpose of the test I run it on the same document.

My laptop run code smoothly but on my manager laptop it act as in example from post #1 .

I have variables declared on the Module level.

Dear All

Just Put "End" before "End Sub"

trick is here

[FONT=&quot]http://www.vbforums.com/showthread.php?353302-Clear-all-variables-Please[/FONT]

Regards,

Chirag Raval

VBA Reset all variables

skorpionkz

Well-known Member

Excel Facts

hippiehacker

Well-known Member

skorpionkz

Well-known Member

chirag050675

Board Regular

Similar threads

Forum statistics

Share this page

VBA Reset all variables

skorpionkz

Well-known Member

Excel Facts

hippiehacker

Well-known Member

skorpionkz

Well-known Member

chirag050675

Board Regular

Similar threads

Forum statistics

Share this page

We've detected that you are using an adblocker.

Which adblocker are you using?

Disable AdBlock

Disable AdBlock Plus

Disable uBlock Origin

Disable uBlock