A command that clears all data stored??

[troelsi]

Dear experts!

I was just wondering if there is a command that clears all data stored in both local and global variables? It would come in pretty handy, that way I wouldn't have to reset or redim all my variables and arrays.


Thank you

[Bob Phillips]

Local variables are cleared upon exiting the procedure that they are local to.

There is no one command that I know of to clear the globals. I don't use global variables, I have an application class, and declare my globals as properties of that class. If you did this, you could clear the class and that would clear all the app variables.

[troelsi]

Thanks for your reply XLD.

[xluser2007]

Local variables are cleared upon exiting the procedure that they are local to.

There is no one command that I know of to clear the globals. I don't use global variables, I have an application class, and declare my globals as properties of that class. If you did this, you could clear the class and that would clear all the app variables.
Hi Bob,

As the OP seems to have finished with his original query, I would like to understand better what you mean by the above.

Do you mean that you don't declare global variables as follows:

Public i as long
If so, or if otherwise, could you please explain what you mean by application class and how you declare variables in that class?

this is just so i get into the correct habit of coding.

regards,

[Bob Phillips]

It is not a matter of correct/incorrect, it is more a matter of personal style/preference, and the control of the code.

But to answer the question ...

You are correct, I try to avoid declaring a Public (or Global) variable. What I would do is to have a class, and declare the property like so


Private mmName As String

Public Property Get Name() As String
Name = mmName
End Property
Public Property Let Name(ByVal pName As String)
mmName = pName
End Property

then you would prime the class and load the variable like this


Set App = New clsApplication
App.Name = "Bob"


and you retrieve the property like so


MsgBox App.Name


As well as providing the encapsulation (probably a bad word in overall terms, but it says what I want to say), the loading of variables gives you an opportunity to validate them, adapt others in the class, and so on. For example, say we have a pay grade and grade A gets $10,000, grade B gets $15,000, grade C $20,000 and so on, we would add these properties to the class



Public Property Get Grade() As String
Grade = mmGrade
End Property
Public Property Let Grade(ByVal pGrade As String)
mmGrade = pGrade
Select Case mmGrade
Case "A": mmSalary = 10000
Case "B": mmSalary = 15000
Case "C": mmSalary = 20000
End Select
End Property

Public Property Get Salary() As String
Salary = mmSalary
End Property


and you would set grade and check salary like so




App.Grade = "B"
MsgBox App.Salary


Note that the Salary doesn't have a Let property, in other words the salary cannot be set from outside of the class, the class has full control over it - this is what I mean by me having control.

Name, Grade and Salary are not really good properties for my App class, they would really be part of a Person or Employee class, App would have things like AppTitle, AppVersion, and any other variables peculiar to the app, but it is a simple one to illustrate my points.

One thing to note, I would need one Public/Global variable, App. This has to be public so that it doesn't get destroyed when leaving a particular procedure.

Talking of being destroyed, I can quickly clear all of those variables/properties if I so wish with




Set App = Nothing


There is so much more flexibility in classes that I could talk about, but that is enough to cover the question.

[xluser2007]

Bob,

First off thanks for explaining my query in such detail. I'm sure it helped out a lot of other people as well.

As a relatively new user I thought this process of defining a new class would be easy to implement :). After reading your post, I find I'm still unsure on simple things such as variables like "clsApplication" are they pre-defined in VBA for example? Also I'm just getting used to Collections, in particular when code is written like

Dim obj as New Collection
It's confusing to understand when to use the words "New" when declaring objects at this stage.

Seems like I have to write a lot more posts, and ask the Gurus of VBAX more queries, before I can start using advanced methodologies like this.

Will keep you posted with more queries as I understand better.

kind regards,

[Bob Phillips]

clsApplication is not a variable, it is the name of my class. App is the variable of type clsApplication. A class is an object, just as a collection is, which is why you New it when you want to create a new instance of that class/object.

You always New an object when you want a ... well, New instance of that object. So if you want to create a new collection, you New it. If you want a new application class, you New it. You don't New objects like ranges because you cannot create a range, just use a range within the Excel maintained ones. Also, you don't New an objkect like a worksheet, because a worksheet is part of the worksheets collection, so you need to Add to the collection.

Classes are confusing at first, and they seem like more work than simple procedures and the like, and people wonder what they do that can't be done with say modularisation. But they are so much more flexible when you get into them. As I often say, you don't get classes until you get them (slightly amusing, but no help whatsoever).

Classes get even better when you have a collection class that collects a number of instances of another class. For example, in my previous post I said that the class I was demonstrating was more porperly a Person or Employee class. If you had many employees, you would create a new instance for each employee and load it, and then add that instance to a collection class, lets say Employees (revolutionary stuff eh?). You can then use all the good collection stuff to manage the Employees class, such as enumerating them, picking out by name, etc (although you do have to build all of these methods yourself).