Re: Setting Workbooks As Public Object Variables
Yes, the two macros and the public declarations are in the same file--I think this is also same project.
What is my code doing (what is intended): First Macro Prompts user to pick a data file, sets the object references, and pastes worksheet names and a couple other values from data file into calculations file. Second macro will eventually take a user selection made by control tool and paste the data from the appropriate worksheet of the data file into the calculation file.
How is my code doing (are things working??): Hard to tell. I worked for IBM when they issued last version of lotus 1-2-3. It would go haywire and do things, even outside of macros, that were just plain wrong. I think that is what you mean by loss of state. In excel, I find it hard to know if problems are an Excel problem or that I am not tuned in to particulars of VBA. For instance below is code samples where I tried a variety of scenarios for selecting a range in a worksheet, and use the autocomplete to check myself. N
Set test_wbk = Application.ThisWorkbook 'gives no autocomplete option when "r" hit--no indication that range is a valid selection worksheets.r worksheets(1).r worksheets("data select").r ' specify workbook -- gives no autocomplete option when "r" hit With test_wbk worksheets.r worksheets(1).r worksheets("sheet 1").r End With 'Add workbook name (with or w/o assigning to a variable -- Gives autocomplete suggestion, but one of them is not "range" test_wbk.worksheets.r r = test_wbk.worksheets.r 'Add wbook, specify sheet -- gives no autocomplete option when "r" hit--specifying a worksheet by name or index seems to fail test_wbk.worksheets(1).r test_wbk.worksheets("Data Select").r 'Use activesheet instead of worksheet -- gives no autocomplete option when "r" hit testval = Application.ActiveSheet 'BUT, This works worksheets("Data Select").Range("D4").Value = Dat_Fil k = test_wbk.worksheets("Presentation Rollup").Range("A54").Value End Sub
[hr]*[/hr] Auto Merged Post Until 24 Hrs Passes;[dl]*[/dl]sorry, I hit the wrong key and cut off the reply. In the code sample, None of the entries ever give ".range" as a valid autocomplete, but I am certain I have seen it as an autocomplete choice before. I don't know if Excel has "gone to lunch" or if I don't appreciate the very specific nuances of how Visual basic is organized. It sure doesn't seem as obvious as it is made to sound in books.
Anyway, for the public object variable, thanks for your thoughts and I am willing to try any ideas--I had tried moving public variable declarations to new module based on another post...seemed to work in the first macro, but didn't carry over to when I tried to access in second macro. That's when I tried using "static."