Results 1 to 2 of 2
  1. #1
    3 Star Lounger
    Join Date
    Nov 2001
    Posts
    342
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Call procedure should remember variable contents (2003)

    I am creating a macro in Excel and want the code of the original procedure to call another procedure. The other procedure tests to see if it has been called by the first procedure or not. The help file states that variables created within a module (as these are) are available to all procedures within that module. This is not working for me.

    Consider the following code:

    Sub junk()
    Dim sunny, pams
    pams = MsgBox("Do you want to go to St Jose?", vbYesNo, "Go?")
    If pams = vbYes Then
    sunny = 1
    rabbit
    End If
    Exit Sub
    MsgBox "Not going"
    End Sub


    Sub rabbit()
    If sunny = 1 Then
    MsgBox "We are going to St Jose!"
    End If
    MsgBox "Since Pams is no, then we don't go"
    End Sub

    The

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Call procedure should remember variable contents (2003)

    This is because you have declared sunny and pams at the procedure level (within junk) instead of at the module level. To make variables available throughout a module, you must declare them as Private at the top of the module, before all Subs and Functions:

    Private sunny, pams

    Sub junk()
    pams = ...
    ...
    End Sub

    Sub rabbit()
    If sunny = 1 then
    ...
    End Sub

    If you use the keyword Public instead of Private, the variables can be used in other modules in the same workbook too.

Posting Permissions

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