If I want to set a global variable when I open my workbook which will be used in code on the individual spreadsheets, how would I do this? I want to set the time the workbook is opened to a variable (constant) and then compare that time to current time on each calculation in the worksheets.
I'm using Excel 2000.
Thanks
ChemistB
Hello ChemistB,
You can do this 2 ways. Declare your variable in the Declarations section of either ThisWorkbook or in any standard VBA module in your project.
Sincerely,
Leith Ross
Good evening ChemistB
Easiest thing is to show you an example.
Open the attached file and allow macros to run.
An event procedure will fire straight away and will show the current time (hh:mm:ss).
Next click on Button1, which is a standard macro, which will show the same value (carried across from the ThisWorkbook.
Now click on Button2 which will update the variable (I've used "a" dimmed as Date) to the time it is now and display that.
HTH
DominicB
Please familiarise yourself with the rules before posting. You can find them here.
I have this (simplified) in my workbook code
and this in my worksheet code
Please Login or Register to view this content.
But when I step through my worksheet code, Starttime is empty.Please Login or Register to view this content.
ChemistB
Last edited by ChemistB; 05-27-2008 at 05:10 PM.
Hi ChemistB
Take a look at the workbook I uploaded.
The global variable must be outside the normal routine.
HTHPlease Login or Register to view this content.
DominicB
Thanks for your help. I got it now.
ChemistB
Alas, it's still not working. I have set "Startdate" as a public variable in both the workbook and worksheet (where I have code) and various combinations. Why isn't "Startdate" keeping it's value?
If it "protects" on you, the password is calculate.
Thanks in advance!
ChemistB
Hi ChemistB
From the VBE go to Insert > Module.
Paste this line into the empty module.
Delete this line from your ThisWorkbook module and your Sheet1 module.Please Login or Register to view this content.
HTH
DominicB
Interesting, so Excel looks at the modules first and foremost even when you're not running any macros?
Thanks for your help, Dominic!
ChemistB
I don't think this is strictly true.so Excel looks at the modules first and foremost
The scope of the variable in relation to the codes location is what is used.
Your example had the variable Startdate declared in 2 locations. Thisworkbook and Sheet1.
So the routine Workbook_Open() in Thisworkbook used the variable declared at the top of the Thisworkbook module.
In Sheet1 the variable declared at the top of Sheet1 code module was used in the Worksheet_Change event.
Both of the following would have been valid syntax and returned the value contained by each variable.
Thisworkbook.StartDate
Sheet1.StartDate
If you modified Sheet1 code to and change the sheet first time will get protected and after removing protection the code will work as intended.
Please Login or Register to view this content.
If you add this code to a standard code module.
Run before changing sheet1 and you can see the held values of both variables.
Please Login or Register to view this content.
Thanks Andy! I'm learning, bit by bit.
ChemistB
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks