Hi, is it possible to set the value of a variable on the Workbook_Open event? How should I do it?
Thanks guys!![]()
Hi, is it possible to set the value of a variable on the Workbook_Open event? How should I do it?
Thanks guys!![]()
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
Trevor Shuttleworth - Retired Excel/VBA Consultant
I dream of a better world where chickens can cross the road without having their motives questioned
'Being unapologetic means never having to say you're sorry' John Cooper Clarke
I was interested in this thread because I have always had issues with Public variables, for that reason I actually use named cells on a worksheet to hold, have values manipulated and referred to in my modules
Take the following code example ("Test=here") lines are break points to see the results. After running the sub Setvars I can run either of the proceeding subs and get the expected result, but only once, for example if I run the first sub FetchVariables I get the right result, if I then run the second sub TestAgain I get the result Empty ???? what am I doing wrong?
![]()
Public Firstval: Public SecondVal Sub Setvars() Firstval = 20 SecondVal = "Testing" End Sub Sub FetchVariables() x = Firstval y = SecondVal test = here End Sub Sub TestAgain() Z = Firstval * 10 w = SecondVal & " Again" test = here End Sub
Any state loss (caused for example by unhandled errors or resetting your project) will clear public variables.
Last edited by romperstomper; 07-23-2015 at 03:07 AM.
Everyone who confuses correlation and causation ends up dead.
@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.
output:![]()
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
![]()
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.
There are currently 1 users browsing this thread. (0 members and 1 guests)
Bookmarks