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

 

[machopicho]

Interesting question...

 

[Norie]

I think something for this was posted some time ago by Zack Barrasse.

Here's the link to the thread http://www.mrexcel.com/forum/showthread.php?t=502112&highlight=list+variables&page=2

You'll need to find the post with the uploaded file yourself, I think it's on the first or second page.

There might be more than one post with a link, I think there might have been some problem with the original (zipped) file.

 

[cbrf23]

hmmm...

Norie, it looks like that code lists all variables by searching through the project for "dim". I guess that could work if I modify it to search for "dim*as cla_*" (my naming convention for classes...)then I could extract the object names from there....

I'd still have to write code to deal with each object then for each project i want to use it in.

I was really hoping there was like a collection of objects that had been created....or even a collection of all objects...but I cant find anything in the msdn reference for excel...

 

[cbrf23]

Well....I found this:
http://msdn.microsoft.com/en-us/library/aa224492(v=office.11).aspx

Does anyone know of something similar to that for VBA??

    For Each var In vbeProj.[UsedObjects]
        ''code to do stuff
    next

 

[Norie]

Sorry, but what are you actually trying to search for?

If all you want is a collection of the objects in Excel you can look at the Object Model Reference for Excel in MSDN.

It's definitely there, as are lists of objects, properties, methods etc.

 

[Norie]

I suppose UsedObjects will get you a list of objects but don't quite see how that ties in with variables.

Perhaps I'm missing something though.

 

[cbrf23]

Hi Norrie.

I am trying to get a list of all the objects that I instantiated for my custom classes.

I want to explicitly set them all to nothing, because I dont trust VBA to do so, and it is notoriously buggy for not properly tracking objects and releasing them when they go out of scope. Yesterday while error testing a new program, I recieved a "Stack out of memory" error. I'm almost positive it is because of this.

Anyways, I just want a garbage collection class to cleanup all my stuff whenever an error occurs or when my main routine completes.

For now, I think I will just create a cleanup collection and for each class add it to the collection on initialize. Then at the end of my routine I can set everything in that collection to nothing.

Question is, do I need to use a For Each loop, or does setting the collection to nothing also set everything inside of it to nothing?

 

[Norie]

I've always thought that VBA cleaned up quite well, though I suppose with custom classes perhaps it's different.

Couldn't you just make sure the code cleans things up as it goes along.

eg a soon as it's finished with an object it does whatever's needed.

Of course if you are talking about problems when debugging them that might be different.

Really don't know that much about this in VBA.

Actually I've just had a quick search on this and there are plenty of discussions on whether or not you need to cleanup objects.

Perhaps worth a look.

 

[cbrf23]

I've found a lot of threads on the subject. The consensus seems to be that usually VBA cleans up fine. BUT, its not completely reliable... which is why I was trying to create a backup garbage collection.

I can clean things up as the code goes...I was looking for a way to do it that would be simple to implement. Such as having a class that would just find all created objects and set them to nothing, so I could use that as a universal way of cleaning things up at any point. I was motly interested in doing so in my error handler so that if the routine doesnt complete, I can still release all those resources.