keeping variables in scope

[LLoyd]

I need to run a macro, allow the user to do some stuff in Excel, and then run another macro. The thing is, the second macro needs to use variables produced by the first macro and when the first macro ends the variables go out of scope and become empty. The variables are in a standard module.

Is there a way to keep VB variables in scope and allow the user to use VB, or do I have to come up with a way of using the sheets to store variables (which will make my second macro slower)?

Cheers,
LLoyd

[Tom Schreiner]

Hi
Declare your variables as Public in the general declarations part of a standard module.
The general declarations section is the very top of the module:
Example

Not
Dim YourVariable
Use this
Public YourVariable

Will keep it's value through the life of the workbook unless you change it.

Have a Nive Day!

Tom

[LLoyd]

That's what I'm doing.

[Dave Hawley]

Hi LLoyd

Make sure none of the Procedures are using the "End" statement. These will destroy public variables.

[Tom Schreiner]

Those are the rules..
Are you sure you are declaring in a standard module?

Not a sheet or work book?

It should work.

Atleast those are the rules

Tom

[AJ]

I have a feeling that Lloyds point has been missed.
Or at least if it hasn't then I have a similar question because the way I understood his post is something I've been wondering myself...

It's all very well using Public variables so that they hold firm accross subroutines and modules, but what if at any given point you want to pause the running of a macro, hand control of the spreadsheet back to the user, and then pick it up again at a later point.

Basially, a permanent way of adding a break point I guess, but in a more user friendly fashion.

Any ideas, most welcome.

Thanks
AJ

[Mark O'Brien]

You could use a simple class module to keep variables alive between handing control back to the user.

For example.

1.Insert a class module to your project, hopefully it will be called "Class1", if not, rename it to be "Class1".

1½. Insert this code into Class1:

Public vMyValue As Variant

2.Put this code in a standard module:

Dim oClass As Class1



Public Sub main()

  

  If oClass Is Nothing Then

    Set oClass = New Class1

    oClass.vMyValue = InputBox("Enter a value")

  End If



  MsgBox oClass.vMyValue



End Sub

3. Stick a commandbutton on a worksheet and set the macro to "main", or make it run "main" on the click event.

4. Click the button to input a value, do it etc. Mess around with Excel. Then click the button again.

Notes:

The object can be destroyed in three ways.

1. Close Excel
2. Set the object to nothing i.e. "Set oClass = Nothing"
3. If you don't handle errors and an error occurs.

HTH

_________________
[b] Mark O'Brien

[ This Message was edited by: Mark O'Brien on 2002-03-28 10:18 ]

[Juan Pablo González]

Following Mark's code, I created this on a empty module.

Option Explicit

Public Var As Integer

Sub T()
If Var = 0 Then
    Var = Application.InputBox("Number", "Title", Type:=1)
Else
    MsgBox Var
End If
End Sub

If, when Inputed, you set the number to anything other than 0, the Var variable will mantain its value, even if you "mess around with Excel"

[Mark O'Brien]

Yeah, I just wanted to stick in some class module stuff.

My code is best used if you're using userforms and want to keep the variable live after the userform's been unloaded.

[Juan Pablo González]

Well... yes. But, I always prefer to put those variables in Regular modules...

Anyway, that's just coding style !