Hello,
I would like that when my spread sheet opens, I set a constant global variable so that when the rest of the VBA code runs, it can access it from anywhere
Here below is in the "ThisWorkbook" file whcih should execute right awaya as the excel file opens
Please Login or Register to view this content.
And here is some VBA code I execute via a button which should see the "NoOfParts" variable. But it doesn't????
Please Login or Register to view this content.
In a separate standard module add this line:
You can then get rid of this line in your current code:Please Login or Register to view this content.
BSBPlease Login or Register to view this content.
Hi all. @RustyNail, you might also consider moving your sub procedures to (the same?) standard module. Sheet and Workbook modules are traditionally reserved for event procedures and Class-related stuff.
http://www.cpearson.com/excel/codemods.htm
Clicking the Add Reputation star below helpful posts is a great way to show your appreciation.
Please mark your threads as SOLVED upon conclusion (Thread Tools above Post # 1). - Lee
Originally Posted by leelnich
You mean move my setup sub in the same file as my other code...
if so, how do I get it to execute as soon as my workbook
Opens???
Yes but then how do I set parts minus 1 .... VBA doesn’t acceptIn a separate standard module add this line:
You can then get rid of this line in your current code:Please Login or Register to view this content.
BSBPlease Login or Register to view this content.
Code Im public space....
If you want that code to execute when the workbook opens, you must either put it in a Workbook_Open event procedure (which DOES belong in the ThisWorkbook module), or call it from same....OR:Please Login or Register to view this content.BTW, there's no logical reason to define PARTS at the procedure level in this scenario unless you're calculating its value in multiple steps. If you need to retain its value in memory, declare it at the module level.Please Login or Register to view this content.
Last edited by leelnich; 02-28-2018 at 02:57 PM.
Hello leelnich,
I do your first one, but when VBA code runs in another sheet, NoOfParts is empty when its supposed to equal to 8 ????
here's what I do:
Please Login or Register to view this content.
thanks for your reply
Last edited by RustyNail; 02-28-2018 at 07:34 PM.
Public variables MUST be declared in a standard code module, not in a class module (ThisWorkbook, a sheet module, or a custom class).
Entia non sunt multiplicanda sine necessitate
This in the ThisWorkbook Object module:...and the rest, especially the variable declaration, in a standard (BAS) module:Please Login or Register to view this content.To create a new BAS module, use the Insert menu, or press ALT+I, ALT+M.Please Login or Register to view this content.
Check this link for a discussion of VBA variable Scope:
http://www.cpearson.com/excel/Scope.aspx
Last edited by leelnich; 02-28-2018 at 08:15 PM.
Ok thanks leelnich 👍
You're welcome! If concluded, please mark your thread as SOLVED (Thread Tools above post #1). Regards – Lee
Why use a variable at all if you are going to set it to a fixed value in the Workbook_Open event? Simply put
at the top of a normal module and remove the Open event code.Please Login or Register to view this content.
Don
Please remember to mark your thread 'Solved' when appropriate.
True IF the value isn't changed later... but why would you assume that?
I based the assumption on the fact the OP stated "I set a constant global variable"
OOPS!
...and quite a reasonable conclusion it is, sir!
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks