Xtreme Visual Basic Talk

Xtreme Visual Basic Talk (http://www.xtremevbtalk.com/index.php)
-   Excel (http://www.xtremevbtalk.com/forumdisplay.php?f=78)
-   -   resetting public variables in VBA (http://www.xtremevbtalk.com/showthread.php?t=192690)

patfinegan 10-08-2004 04:27 PM

resetting public variables in VBA
 
Is there a simple way to clear all public variables in VBA? Or even identify them without having to specify them by name?

Alternatively, is there a VBA command that replicates the Run/Reset menu item in the VBA window?

Thanks in advance.

Kluz 10-08-2004 04:48 PM

Quote:

Originally Posted by patfinegan
Alternatively, is there a VBA command that replicates the Run/Reset menu item in the VBA window?

What exactly are you trying to? If you want to run a specific macro through code just type in the name of the macro you wish to run:
Code:
Sub TestMacro() MsgBox "Ignore this messagebox" End Sub Sub TestingTheTestMacro() TestMacro 'runs the above macro End Sub
You can also look up the Call Statement in the VBA help files.

Resetting the Public variables...hmmm... I don't know of a way to do them all at once based upon their publicness. Each variable type starts out with a default value, eg.: numeric variables = 0, Strings = an empty string (""), Boolians = False, Objects = Nothing. Maybe write a seperate sub to reset them and call that sub every time you need them reset. That's only advantageous if you're doing it several times in the course of your program.

patfinegan 10-08-2004 05:06 PM

I have a large form-based VBA application (Monte Carlo experiment) that contains several virtual data cubes. These cubes can be sliced, diced and presented a thousand ways.

Every now and then, the user wants to use a different source file or change the dimensions of the Monte Carlo experiment. This isn't a problem when the public variables are empty, because the code figures out the dimensions from the source data or, through a series of forms, queries the user.

On the other hand, most of the procedures assume that if a public or passed variable is not empty, the procedure can execute. It is only when a variable is empty or unintialized that the dimension-setting algorithms pounce in.

Right now, for that to happen, I have to create a macro that crashes, and instruct the user beforehand to select "End". This resets everything and he or she can proceed merrily on his or her way.

Obviously, I could also list and redim every public variable when a user decides to fundamentally alter the model's dimensions. So my question remains:

1. Is there a way to automatically identify all the public variables in a VBA project (obviously I can do this manually).
2. Is there an equivalent to the Run/Reset menu command in VBA, since this does everything I need, and then some.
3. Is there a way to redim public variables as a class?

herilane 10-09-2004 11:57 AM

Have you considered creating a class module for your experiment? Move all those experiment-specific public variables and related code to the class module. Resetting the variables then becomes as easy as "Set objExperiment = New clsExperiment".

Mike Rosenblum 10-09-2004 01:23 PM

Definately an unusual request!

But if you insist:
Code:
Sub ResetAll() End End Sub
Frankly, this is the only valid use of the 'End' command that I could possibly imagine. So, if you do use this here, please promise to never, ever use this again, ok?

This only works in VBA because VBA is, effectively, a design-time environment. If you use the 'End' command within a Compiled VB 6.0 program or COM DLL, the program aborts without properly shutting down resources.

But although it works for this, there is no micro-control here. It erases absolutely ALL variables. This is rarely desireable (although it seems that it is for you). If in the future you wish to reset an entire set of variables (but not absolutely all of them) then I would take Herilane's advice to create a Class for your experiment. As she states, resetting them all would then be as simple as Set MyExperiment = New clsExperiment.

patfinegan 10-09-2004 01:43 PM

Re: Use of Class Module
 
Sounds perfect but I'm lost. I've only used class models to assign macros to controls created during run-time, and am not sure what to do in this context.

To keep matters simple (sorry, I am a consultant from the punchcard era, not a programmer), assume I have just one public variable, a custom Type variable (that's probably not important, but I want to be sure since most of my data cubes are custom type variables):
Code:
Type StringList List() as String End Type Public myList() as StringList
I redim the .List variable with the following procedure:
Code:
Sub RedimStringList(byRef whatList() as StringList, byVal x as integer, byVal y as integer) Redim whatList(x).List(1 to y) End Sub
My test procedure:
Code:
Sub Test() 'Dim a variable and then "undim" it using a class module. Redim myList(1 to 1) Call RedimStringList(myList,1,5) Debug.Print Ubound(myList(1).List) 'Answer: 5 Set objClass1 = New Class1 On Error Goto ErrorHandler Debug.Print Ubound(myList(1).List) ErrorHandler: Select Case Err Case 9 Debug.Print "Yay, it worked! My variable is empty!" End Select End Sub
OK, so what, if anything, should go on the class module and how? And then what special declarations do I need to make on the normal module? I apologize in advance if this is really basic stuff. Also, can anyone point me to a SIMPLE tutorial so I don't feel like such a bonehead? Thanks.

patfinegan 10-09-2004 01:48 PM

Re: End Command
 
Very cool and VERY dangerous. I like it. But I'd prefer the class module approach if someone can walk me through it.

Dennis DVR 10-09-2004 03:03 PM

since you want to take herilane's advice, you have 2 options. 1 you should convert your UDT to a class by defining each variable inside the UDT to a property let and get which is a better approach (IMHO). 2 keep you UDT in the Module (BAS) and declare them as Public Type and then create a class module and make a property using a friend modifier (not public) that will return/accept a UDT.

patfinegan 10-09-2004 03:31 PM

Quote:

Originally Posted by duane
make a property using a friend modifier (not public) that will return/accept a UDT.

Not sure what you mean. Sorry for being so dense.

herilane 10-09-2004 05:18 PM

1 Attachment(s)
I've never had anything but pain and trouble when I've tried to combine UDTs with classes... so I can't give you any example for that. But here's an example of a simple StringList class and how you to use it.

patfinegan 10-09-2004 05:47 PM

Thank you for the example. I haven't tried it yet on Excel 2003 (no Internet connection on that PC), but I get a compilation error in Excel 97 because of the following line of the class module (doesn't accept the final "()"):

Code:
Public Property Get List() As String()

Will report back if I encounter the same error in Excel 2003. I'd really like to understand this, so I can't thank you enough for your time.

From a practical standpoint, I created a procedure that redims all my public variables (I listed each one manually in the code -- apparently no way for VBA to identify them automatically). This is certainly adequate, probably uses less overhead than a class module, and, most important, is very straightforward. But I'd like to understand the class module example because, well, I just should. Thanks again.

Mike Rosenblum 10-09-2004 06:18 PM

1 Attachment(s)
Ok, unfortunately, I think we have to take this in two stages... The good news is that you want to learn, so this is good to hear. :) I could just give you a quick fix... but before using Object Oriented Programming (OOP) to handle your unusual request, let's look at how to use simple OOP in a more standard manner.

Let's invent a "String Class". To do so, we add a Class Molule and then right-clicking on it, we choose Properties and change it's name from "Class1" to "clsString". We then add our code (which I'll disclose later). If the Class Module is made correctly, you would be able to run code that looks as follows:
Code:
Sub Main() Dim oStr As clsString Set oStr = New clsString oStr.Value = "Hello" MsgBox "oStr.Value = " & oStr.Value ' Returns "Hello" MsgBox "oStr.Lengh = " & oStr.Length ' Returns 5 oStr.Append(" Dolly") MsgBox "oStr.Value = " & oStr.Value ' Returns "Hello Dolly" MsgBox "oStr.Lengh = " & oStr.Length ' Returns 11 oStr.Value = "Done" MsgBox "oStr.Value = " & oStr.Value ' Returns "Done" MsgBox "oStr.Lengh = " & oStr.Length ' Returns 4 End Sub
The above creates a New clsString instance and assigns it to oStr. Then we can use oStr to do our various commands. The code within 'clsString' in this case is as follows:
Code:
Option Explicit Dim m_Str As String ' ================================================================== ' Init & Terminate: ' ------------------------------------------------------------------ Private Sub Class_Initialize() ' Class_Initialize() is called when New clsString is created: ' Overkill in this case, but we initialize our variables: m_Str = vbNullString End Sub Private Sub Class_Terminate() ' Terminate Event is called when the clsString is Set = Nothing. ' Overkill, for this, but we clean-up before closing: m_Str = vbNullString End Sub ' ================================================================== ' Properties & Methods ' ------------------------------------------------------------------ Sub Append(AppendString As String) m_Str = m_Str & AppendString End Sub Sub Prepend(PrependString As String) m_Str = PrependString & m_Str End Sub Sub Clear() ' Same as calling clsStr.Value = vbNullString m_Str = vbNullString End Sub Property Get Length() As Long Length = Len(m_Str) End Property Property Get Value() As String Value = m_Str End Property Property Let Value(NewValue As String) m_Str = NewValue End Property
Give it a look play with it and when comfortable, we can help you with how to use this for your needs here.

Or if you prefer to learn, we can teach you how to make .Value a default property (so you can do commands like oStr = "Hello" without having to explicitly use '.Value') and how to create an Enumerator so that your class can support commands such as 'For Each <Object> In <MyCollectionObject>' type stuff. Well, maybe we should let this stuff go for now, but be aware that all the *usual* OOP stuff that you are used to can be implemented in your own Classes, just ask when you want to learn this stuff.

Anyway, a working project is attached and let us know when you are ready to move onto the next step...

patfinegan 10-09-2004 08:20 PM

Very helpful. Thanks. But what if I want to simultaneously clear, append or "prepend" (new word?) multiple string variables? The example below adds a second variable, oStr2:
Code:
Sub Main() Dim oStr As clsString Dim oStr2 As clsString Set oStr = New clsString Set oStr2 = New clsString oStr.Value = "Hello" MsgBox "oStr.Value = " & oStr.Value ' Returns "Hello" MsgBox "oStr.Lengh = " & oStr.Length ' Returns 5 oStr2.Value = "Hello again" MsgBox "oStr2.value = " & oStr2.Value oStr.Append (" Dolly") MsgBox "oStr.Value = " & oStr.Value ' Returns "Hello Dolly" MsgBox "oStr.Lengh = " & oStr.Length ' Returns 11 oStr.Value = "Done" Set oStr = New clsString MsgBox "oStr.Value = " & oStr.Value ' Returns "" MsgBox "oStr.Lengh = " & oStr.Length ' Returns 0 MsgBox "oStr2.value = " & oStr2.Value ' Still returns "Hello again" ' Would really prefer a universal "clear" End Sub

It seems like I would still have to clear each public variable separately. Wouldn't the most direct route be to create a public Type variable to act as the wrapper for all my public variables. I could then redim that wrapper in one line:
Code:
Option Explicit Public Type PublicVar 'Wrapper for all my public variables (actual list MUCH larger) CO() As String BU() As String NoSims As Integer firstDate As Date RandSeed() As Double End Type Public PV() As PublicVar Sub test() 'Populate PV with arbitrary test data. 'Example has multiple data types because my model does too. Dim i As Integer, j As Integer ReDim PV(1 To 1) With PV(1) ReDim .CO(1 To 2) .CO(1) = "Enron" .CO(2) = "Worldcom" ReDim .BU(1 To UBound(.CO), 1 To 2) .BU(1, 1) = "Gas" .BU(2, 1) = "Hot Air" .BU(2, 1) = "All Talk" .BU(2, 2) = "Hot Air" .NoSims = 1000 .firstDate = Now() ReDim .RandSeed(1 To UBound(.BU, 1), 1 To UBound(.BU, 2)) For i = 1 To UBound(.RandSeed, 1) For j = 1 To UBound(.RandSeed, 2) .RandSeed(i, j) = Rnd() Next j Next i End With 'Test to make sure PV is populated. Debug.Print PV(1).BU(2, 2) 'Clear all my public variables with one line of code. ReDim PV(0) 'Test result. On Error GoTo Errorhandler Debug.Print PV(1).BU(2, 2) 'Should route to error handler. Errorhandler: Select Case Err Case 9 Debug.Print "Yay. PV is EMPTY !!" End Select End Sub

Thanks again. Please be blunt if there are good reasons to avoid Type variables or if I am missing the broader implications of the class module example (I'm sure I am).

Mike Rosenblum 10-09-2004 08:51 PM

Ok, actually, I like your thinking... Basically the strategy for using a Class was to be the same. I just could not bear to use a Class to do this "trick" without explaining to you (at least a little bit) how classes are supposed to be used.

Your usage of a User Defined Type (UDT) is excellent. There are other ways to reset your UDT, however, that will make your code a little easier to read.

You created a 'PV(1 to 1) As PublicVar' and then to reset it you call 'ReDim PV(0)'. This is fine. (I think 'ReDim PV(1)' without using the Preserve Keyword would do the same, not 100% sure here.)

Another, cleaner way to erase an Array is to use the Erase keyword, as in:
Code:
Erase PV
But whether using ReDim or Erase, in either case you are using an Array aritficially. That is, you don't really need an Array, but an Array can be "Erased", and so you are making due.

You can do something a little cleaner however. Let's look at the following:
Code:
Public Type PublicVar 'Wrapper for all my public variables (actual list MUCH larger) CO() As String BU() As String NoSims As Integer firstDate As Date RandSeed() As Double End Type Public PV As PublicVar
The above is substantially the same as yourcode, except that PV is no longer an Array. Do what you want with PV.NoSims, etc, and then when you need to reset your PV, you can reset your PV by calling:
Code:
Call PublicVar_Reset(PV)
The code for PublicVar_Reset() is as follows:
Code:
Sub PublicVar_Reset(PV As PublicVar) Dim EmptyPV As PublicVar PV = EmptyPV End Sub
I think this approach should be best for you and easiest to understand.

Using a Class to do this, by the way, would be extremely similar, virtually identical. But I'll leave this for the next post...

-- Mike

Mike Rosenblum 10-09-2004 09:05 PM

Ok, you know what, there is no next post... conceptually you could create a Class and just Copy-Paste in your Variables. However since a few of your variables are Arrays(), these cannot be dropped into a Class directly and would have to be wrapped with a Property Get/Let procedure.

This is not catastrophically difficult, but compared to the ease of using the UDT's as you've constructed it, it would really be a crime to even bother. Still, I would print out the clsString example I gave above to help you get your feet wet with OOP. To read further on the subject of making your own classes, you can have a read of This Thread particularly Posts #7 through #18...

-- Mike

patfinegan 10-09-2004 09:19 PM

Neat. Keep an empty "publicVar" wrapper variable in reserve and just set my active wrapper equal to it whenever I want it emptied. Perfect. Thanks!!

patfinegan 10-09-2004 09:20 PM

And I will study class modules, at least until my brain hurts...

Mike Rosenblum 10-09-2004 09:20 PM

Quote:

Originally Posted by patfinegan
Neat. Keep an empty "publicVar" wrapper variable in reserve and just set my active wrapper equal to it whenever I want it emptied. Perfect. Thanks!!

Yep, exactly. ;)


Quote:

Originally Posted by patfinegan
And I will study class modules, at least until my brain hurts...

LOL, sounds like a plan..! :p


Good luck! :)

herilane 10-10-2004 04:04 AM

Quote:

Originally Posted by patfinegan
And I will study class modules, at least until my brain hurts...

A good resource for that:
http://www.visualbasicbooks.com/progVB6samplepg16.html
A free sample chapter from Francesco Balena's book "Programming Microsoft Visual Basic 6.0" - Classes and objects.


All times are GMT -6. The time now is 01:00 AM.

Powered by vBulletin® Version 3.8.6
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
All site content is protected by the Digital Millenium Act of 1998. Copyright©2001-2011 MAS Media Inc. and Extreme Visual Basic Forum. All rights reserved.
You may not copy or reproduce any portion of this site without written consent.