[Resolved] Simple Excel Request

[Bartender]

My code works quite well, the first time it's run. Every subsequent time it screws up.
All I have to do is hit the reset button in the VBA editor to refresh my code, which is fine for me, but the users won't have the knowledge to do that.
Can I add code to make it so that the first or last thing my sub does is the equivalent of hitting that "Reset" button?
What else can I do?
Thanks for your help.

[Ecniv]

Er - if your code is erroring after working fine then you are doing something wrong somewhere.

Perhaps you can find out where it is going wrong, setting allreferences to null to release memory etc?

By reset you mean the stop button?

[Bartender]

Oh, I know I'm doing something wrong somewhere. What is the command to clear all variables, if there is one?

And, yes, the stop button: Play, Pause, and Stop buttons which have the tooltips "Run Sub/UserForm", "Break", and "Reset", respectively. If I could just, in effect, hit that stop button in between each use of the program, I'd be golden---'til the next bug.

[Ecniv]

Well at hte bottom of your sub, if you have used any references to open other sheets or workbooks, just put

set <variable> = nothing

Where <variable> is your variable name holding a reference to an object.
Same if you use recordsets or ranges or any object.

The stop button I was going to suggest using the commandbars (office objects) but that button is not available in excel, only vb editor, where I guess you do not want the users to go.

[Bartender]

Right, the users won't have the knowledge or patience to go into the VB Editor.
Is there any command to,say,

Set AllVariables = 0, or nothing?

[RobDog888]

You need to destroy all objects individually. The string, date, and numeric vars get destroyed when the code execution goes
out of scope or out of the sub.

Dim oWB As Excel.Workbook
Dim oSheet As Excel.Sheet
Dim .Range As Excel.Range
Dim oCell As Excel.Cell
 
'do your stuff to initialize them and process, etc.
 
'...
'...
'...
 
'Clean up...
Set oCell = Nothing
Set oRange = Nothing
Set oSheet = Nothing
Set oWB = Nothing
'...

[JustinLabenne]

Sorry to jump in here, but do you have to hit the reset button after running your code because it goes into debug mode? It may be relevant to post your code, especially if it's a recorded code, as there should be no reason why you should have to reset your code all the time because it error's out.

Depending on the type of variables you have involved, unless your creating new instances of applications, etc.. your variables, if declared at procedure level should be empty when the code is finished executing. They may not be because the code appears to me, to be running into an error of some sort.

[Bartender]

Thanks Robb,
I'll try clearing all the variables manually as you suggest.

Justin,
No, the code completes as it should the first time I run it, every time.
In my code I have a 'best case' condition saved, and a 'second best' condition saved. If I run the same code a second time, what HAD been happening was that the best and second best cases were becoming the same number, which, if the best case was not cleared, I guess would happen with my code. Whats happening now is I'm getting an overflow error, because a Rng.Cells.Count becomes 65531 rather than 2-9 like it should be. Strange.

[Bartender]

Justin, Rob, Ecniv,

Thanks for all your help, this is my first resolved thread, so hooray!
When I re-examined the code to reply to Justin, I saw the problem I called "strange" above, and investigated....
It turned out that I HAD coded it so that everything would reset like I thought I had, except I had missed resetting one very important value and so I had:

If ImportantVal=0 Then
'Do many important things
End If
'but it never did this after the first run because ImportantVal had a value persisting in it for some reason
'So I added, right at the end
ImportantVal=0
Exit Sub

And now, it doesn't need to be reset in between.
Now, If only I could get my other issue up and running...