keeping variables in scope

LLoyd

New Member
Joined
Mar 20, 2002
Messages
4
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
 

Excel Facts

Shade all formula cells
To shade all formula cells: Home, Find & Select, Formulas to select all formulas. Then apply a light fill color.
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
 
Upvote 0
Hi LLoyd

Make sure none of the Procedures are using the "End" statement. These will destroy public variables.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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:<pre>
Public vMyValue As Variant</pre>


2.Put this code in a standard module:<pre>
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</pre>

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

_________________<font color = green> Mark O'Brien
This message was edited by Mark O'Brien on 2002-03-28 10:18
 
Upvote 0
Following Mark's code, I created this on a empty module.

Code:
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"
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,114,002
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top