Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: keeping variables in scope

  1. #1
    New Member
    Join Date
    Mar 2002
    Location
    Cambridge, UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  2. #2
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  3. #3
    New Member
    Join Date
    Mar 2002
    Location
    Cambridge, UK
    Posts
    4
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    That's what I'm doing.

  4. #4
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi LLoyd

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



  5. #5
    Board Regular
    Join Date
    Mar 2002
    Location
    Cincinnati, Ohio, USA
    Posts
    6,824
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  6. #6
    Board Regular
    Join Date
    Mar 2002
    Location
    =ActiveCell.Address
    Posts
    478
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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

  7. #7
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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 ]

  8. #8
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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"

    Regards,

    Juan Pablo González
    http://www.juanpg.com

  9. #9
    MrExcel MVP Mark O'Brien's Avatar
    Join Date
    Feb 2002
    Location
    Columbus, OH, USA
    Posts
    3,530
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    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.

  10. #10
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Bogota, Colombia
    Posts
    11,959
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

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

    Anyway, that's just coding style !
    Regards,

    Juan Pablo González
    http://www.juanpg.com

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •