 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
| HomeDiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWordDirectoryUser Groups Related Topics Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ... |
MS Office Forum / Excel / Programming / September 2007VBA Event to Restore Lost Public Variables
QUESTION: I tried to define global variables in a module with the Public statement. Unfortunately, their values were eventually lost (possibly because I clicked Excel's debug reset button or because the application that I inherited uses an End statement?). Whatever the cause, what is a good way to reinitialize them? I have a function to do that, but when would I run it? I already call it from my workbook's open event. Is there an event that runs immediately after Public variables are lost or before any other event would run? It's a large application, so I would not want to start every function with a check of my public variables. OPTIONAL READING: Normally, I would use Public Const statements to declare my constants, but this is a multi-workbook application where workbooks must be able to access one another. Today, each workbook has each other's constants embedded within it. When one workbook's constants are changed, all other workbooks must be updated accordingly and released along with it. To simplify, I want to create a sheet named GBL in each workbook to hold that workbook's constants (column-1 would be variable name; column-2 would be variable value). When one workbook needed to access another workbook, the first workbook would read the second workbook's GBL sheet and its constants (or at least the ones that the first program needed). This way, when I must change a workbook's constants, I would not have to update and release all other workbooks. Dave Stick all of your globals in a class as properties, and initialize it like so Set myApp = New clsApp and access the properties like this Range("A1").value = myApp.Name Then you just check myApp for nothing and just rerun if so If myAPp Is Nothing Then Set myApp = New clsApp  Signature--- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) > QUESTION: > I tried to define global variables in a module with the Public statement. [quoted text clipped - 22 lines] > > Dave hi variables are volitale. once code stops, they're gone. you can not store variables indefinitely like you do code. each time you run code you have to redeclare you variables. that is why you declare variables at the start of code so that vb will know what they are. Sorry. FSt1 > QUESTION: > I tried to define global variables in a module with the Public statement. [quoted text clipped - 22 lines] > > Dave I accept the fact that variables, unlike constants that are declared with Public Const, can be lost (although variables defined with the Public statement hold their value across user events and are rarely initialized). My problem is what to do about it in the situation where one workbook's constants must be shared with other workbooks that can be released to my users at different dates? It's a large application, so I don't want to check the status of those variables every time I reference them (such as at the beginning of each routine, etc.). I was hoping, perhaps not realistically, that there was a single event that could handle the check. Dave > hi > variables are volitale. once code stops, they're gone. you can not store [quoted text clipped - 45 lines] >> >> Dave An end statement would do it or possibly some kind or error. That being said there is nothing that will tell you that the values are empty except checking each one as it comes up. Yet one more reason why I avoid global variables wherever possible. SignatureHTH... Jim Thomlinson > QUESTION: > I tried to define global variables in a module with the Public statement. [quoted text clipped - 22 lines] > > Dave I agree. I am converting my workbook's constants (declared via Public Const) to variables so they can be better accessed by other workbooks that are open at the same time. That way, I will eliminate redundant definitions and I will introduce a level of indirection that allows me to upgrade one workbook and its constants without having to distribute all workbooks with the same release. Dave > An end statement would do it or possibly some kind or error. That being > said [quoted text clipped - 42 lines] >> >> Dave Anticipate the loss or at least understand it. Can the variables be stored away in worksheet cells? SignatureGary''s Student - gsnu200746 > QUESTION: > I tried to define global variables in a module with the Public statement. [quoted text clipped - 22 lines] > > Dave Yes - the variables can be stored away in worksheet cells. I am moving my constants from a global module that used Public Const declarations to a dedicated worksheet so the variables can be accessed by other open workbooks as well as the current workbook. Upon load, a function reads the worksheet names/values and, using a SELECT CASE, loads the values into Public variables. I was hoping for something more elegant. Based upon the feedback I've received here, it seems that I am doing things about as well as I can. Any other thoughts that might be useful? Dave > Anticipate the loss or at least understand it. > [quoted text clipped - 39 lines] >> >> Dave Some other ideas: - Refresh them frequently. If they are accidentally destroyed, they will be recreated during the next event, say a sheet activate event, or whatever occurs often enough for your comfort. You may also consider tying in the refresh to error handling. - Use a code workbook or addin to perform the code behind the scenes against data-only workbooks (presumably the active workbooks), and keep the public constants in the code workbook. - Get rid of the End statements. - Store them as public constants, and have your initilization macro(s) copy that module to the code workbooks as needed. > Yes - the variables can be stored away in worksheet cells. I am moving my > constants from a global module that used Public Const declarations to a [quoted text clipped - 53 lines] >>> >>> Dave Tim, I don't understand what you mean by "Store them as public constants, and have your initilization macro(s) copy that module to the code workbooks as needed." Are you saying that it is possible for one workbook to copy another workbook's module along with its Public Constants? If so, that might be the best possible solution. Public Constants are never lost, so I would only have to worry about dynamically copying constants (i.e., at runtime) when establishing a link from one workbook to another. Dave > Some other ideas: > [quoted text clipped - 67 lines] >>>> >>>> Dave Yes, but as you will see, it may or may not be the optimal solution depending on your environment. Depending on which version of Excel you are using, you may need to modify the security settings, such as trusting programmatic access to Visual Basic projects. For code on how to copy a module to another workbook see http://www.cpearson.com/excel/vbe.aspx Note: there is a warning on the page about antivirus programs that detect code that modifies the VBE, so heed it. If you run into a problem, let me know as I have discovered a simple way to throw off Norton's Bloodhound Heuristics, which may or may be applicable with other antivirus programs. > Tim, > I don't understand what you mean by "Store them as public constants, and [quoted text clipped - 79 lines] >>>>> >>>>> Dave What I normally do is declare a global boolean variable, bIsInitialized. In the initialize procedure/function where I set the values, I set bIsInitialized to True. Then, in other functions I have a line such as If Not bIsInitialized Then Call InitProcedure. > QUESTION: > I tried to define global variables in a module with the Public statement. [quoted text clipped - 22 lines] > > Dave |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.