Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about scoping variables
Excel 2000
Windows 2k Pro I'll try to be brief: I have a workbook that when it opens closes all other open workbooks. Not very flexible, I know, but down the line I'll add the appropriate warnings and whistles. Anyway, once in this workbook the user may be prompted to open another workbook so that they can copy and paste data from that workbook to the original workbook. There are (at the time of this writing) three standard modules and three userforms involved also, in addition to some code in the ThisWorkbook module. What I'm having a hard time with is being able to keep track of which workbook is which, and it may be that I need to activate or select a workbook from either a standard module or a userform module. I know how to set a variable equal to the active workbook, but often times the userform module won't recognize it or it will work once then not again after that. I know this must be a variable scope issue, but I'm confused on where the variables should be declared. I know that Public variables must be declared in a standard module, but I'm having limited success with that. For arguments sake the workbook variables are MyWB and TempWB, with MyWB being the original, and there will never be more than two workbooks open at a time. So, what is the best way to declare these workbook variables so that ANY module can access them? Or have I bitten off more than I can chew? All hints and advice greatly appreciated. -gk- |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Question about scoping variables
in a standard module (as you say)
Public MyWB as Workbook Publc TempWB as Workbook Sub OpenBook() sStr = "C:\My Documents\MyFile.xls" set TempWb = Workbooks.Open(sStr) set MyWB = Thisworkbook ' workbook containing the code End Sub You have to set the variables so they have values. Make sure you don't have any plain END statements in your code. This resets variables. Don't hit the reset button in the VBE. This clears you variables as well. -- Regards, Tom Ogilvy TBA wrote in message ... Excel 2000 Windows 2k Pro I'll try to be brief: I have a workbook that when it opens closes all other open workbooks. Not very flexible, I know, but down the line I'll add the appropriate warnings and whistles. Anyway, once in this workbook the user may be prompted to open another workbook so that they can copy and paste data from that workbook to the original workbook. There are (at the time of this writing) three standard modules and three userforms involved also, in addition to some code in the ThisWorkbook module. What I'm having a hard time with is being able to keep track of which workbook is which, and it may be that I need to activate or select a workbook from either a standard module or a userform module. I know how to set a variable equal to the active workbook, but often times the userform module won't recognize it or it will work once then not again after that. I know this must be a variable scope issue, but I'm confused on where the variables should be declared. I know that Public variables must be declared in a standard module, but I'm having limited success with that. For arguments sake the workbook variables are MyWB and TempWB, with MyWB being the original, and there will never be more than two workbooks open at a time. So, what is the best way to declare these workbook variables so that ANY module can access them? Or have I bitten off more than I can chew? All hints and advice greatly appreciated. -gk- |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Not at all clear on use of variables and/or object variables | Excel Discussion (Misc queries) | |||
Lookup Question with multiple dynamic variables | Excel Worksheet Functions | |||
Lookup Question Based upon 2 Variables | Excel Worksheet Functions | |||
general question about variables | Excel Discussion (Misc queries) | |||
Using variables in a name | Excel Discussion (Misc queries) |