Get a list of variables in VBA?

[cbrf23]

I know its possible to see a list of variables in the locals window...but can I access that list in VBA?

What I want to do is have a class that goes through the list of variables, and sets all my created objects to nothing. I consistently name my objects oName so if I can setup something like the code below that would work for me. I need help with the text in blue...

dim var as object
for each var in [COLOR=royalblue][collection of variables][/COLOR]
     if [COLOR=royalblue][variable name][/COLOR] like "o*" then
          set var = nothing
     end if
next

 

[Scott Huish]

Why not just use End?

From the help file:

When executed, the End statement resets all module-level variables and all static local variables in all modules.

 

[Norie]

I understand what you mean but to be honest I don't think error handling in VBA is up to something like that.

I could be wrong of course.

Are you trying to do this because you seem to be having some problems that might be caused by things not being cleaned up?

 

[cbrf23]

HotPepper, are you referring to End as in End Procedure (ie. End Sub, End Function, etc)? Because the problem is that even after the procedure ends, not everything is reset 100% reliably.

Norie, I ran into a "Stack out of memory" error, which crashed excel. This was while testing and debugging. I was writing code, testing it, and making changes as something would fail (no error handling in place while debugging). I believe the problem was caused by VBA not uninstantiating class objects even after execution stopped (which should have caused everything but global level variables to go out of scope, and be reset).

The file I am creating will be used on multiple workstations across the company, so I want to make sure there is no chance of that happening in a normal use situation....I just want a failsafe to clean up objects explicitly when my code is done executing.

 

[Scott Huish]

No I am referring to the End Statment all by itself

Sub test()
Dim a As Long
a=7
End
End Sub

 

[Norie]

cbrf

So there's no other way that the error could have been caused?

A loop gone haywire?

Something being instantiated each time a loop iterates.

Perhaps something to do with global/public variables?

By the way, I think I know of some code that does what you want but I can't find it.

I believe it was posted on another forum, VBAExpress.

Might even be an article there.

 

[cbrf23]

@hotpepper: Very interesting. I've never seen that, I'll give it a try!

@Norrie: No, the code I was working with was actually what was going to be used in a loop, but there was no loop written yet. And as far as globals, I only have one global variable. Its for a progress bar I created (it needs to be global so that all modules can access it and change status/percent complete as the program runs) but I wasnt using the progress bar in the test code; it was never being instantiated, so I dont see how it could be related to that.

I do have everything working, and for now I'm just setting my object variables to nothing at the end of the code that uses them. Watching the locals window, this seems to work. I would like a more elegant (and modular) solution, but for now I can do it this way.

I wish vba used the .net framework....shared methods for the win!

 

[Norie]

cbrf23

Those were just some examples I though of off the top of my head that could cause some sort of stack error.

There's plenty more kicking about if you search.

I even saw something that said it could be caused by a missing End If.

Not sure about that one.

Anyway, if you've got something that does what you want that's the important thing.

PS Isn't the .NET framework involved if you use VSTO (or is it VSTA?)?

 

[cbrf23]

http://msdn.microsoft.com/en-US/library/tsw2a11z%28v=VS.80%29.aspx

For my situation, it appears the End statement will not work. I need to clear the variables at the end of a routine and continue on with the next, or return back to the calling procedure.

Also, it appears that using End<keyword> (ie. End Sub, End Function) is supposed to do pretty much the same thing, but limited to that procedure....where as End without a keyword, applies to the whole program. Unless I misunderstand the article...</keyword>

 

[cbrf23]

VSTO...awesome. I guess that is microsofts way of moving away from VBA, but it actually makes more sense IMHO. I like the idea of publishing new versions of the underlying VSTO program to one location, which then all documents that used that program would be updated. I will have to talk to my system administrator about that.