Public Variable

Declare your Public variable in a normal module before any code in that module (ideally, in its own module with any other public variables).

Then just refer to it in the Workbook Open event handler.


Regards, TMS

Any state loss (caused for example by unhandled errors or resetting your project) will clear public variables.

@Catman50: Not sure what you mean. You're not actually changing the values in subsequent subroutines. However, if you cause a break to check the values it will reset those values.

Option Explicit

Public Firstval: Public SecondVal

Sub main()
    Setvars
    FetchVariables
    TestAgain
    FetchVariables
    TestAgain
    FetchVariables
    TestAgain
    FetchVariables
    TestAgain
End Sub

Sub Setvars()
    Firstval = 20
    SecondVal = "Testing"
    Debug.Print "1 ", Firstval, SecondVal
End Sub

Sub FetchVariables()
Dim x, y, test, here
    x = Firstval
    y = SecondVal
    test = here
    Debug.Print "2 ", x, y
    Firstval = Firstval + 10
End Sub
Sub TestAgain()
Dim Z, w, test, here
    Z = Firstval * 10
    w = SecondVal & " Again"
    test = here
    Debug.Print "3 ", Z, w
End Sub

output:

1              20           Testing
2              20           Testing
3              300          Testing Again
2              30           Testing
3              400          Testing Again
2              40           Testing
3              500          Testing Again
2              50           Testing
3              600          Testing Again

Unfortunately your post does not comply with Rule 2 of our Forum RULES. Do not post a question in the thread of another member -- start your own thread.

If you feel an existing thread is particularly relevant to your need, provide a link to the other thread in your new thread.

Old threads are often only monitored by the original participants. New threads not only open you up to all possible participants again, they typically get faster response, too.