| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Scope of Public Variable
I want to create a Public variable upon the opening or reactivation of a
workbook. Public wbVLA08 = Application.ActiveWorkbook Then at various points in several modules I activate a different workbook, do something there, and then want to make sure the original workbook is activated. So I'd use: wbVLA08.Activate When a routine is called by another macro in which the Public variable has been set, I get flagged on it saying object hasn't been set. "Run-time error 91: Object variable or With block variable not set" Do I need to declare the variable in every module in which it is used? Do I need to set in each module? Do I need to make it static? If I understand it, I can't set in "This Workbook" which is what I'd like to do. How can I do this? |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scope of Public Variable
Your syntax
Public wbVLA08 = Application.ActiveWorkbook is wrong because you cannot initialize a variable in its declaration. You need to initialize in some initialization procedure, such as the Auto_Open macro or the Workbook_Open event procedure. For example, Public wbVLA08 As Workbook Sub Auto_Open() Set wbVBA08 = ActiveWorkbook End Sub Once initialized, it will retain its value until you change it or the workbook is closed. You can then Activate that workbook at any time in code with code like the following: wbVLA08.Activate Do I need to declare the variable in every module in which it is used? No, declare it once in a standard code module (not the ThisWorkbook module or a Sheet module or a class module), outside of and before any procedure. Do I need to set in each module? Do I need to make it static? No, declare it once. Public variables are "static" by definition. If I understand it, I can't set in "This Workbook" which is what I'd like to do. I'm not sure what the question is here. You can't declare it in the ThisWorkbook module (well, you can, but shouldn't). In code, ThisWorkbook always refers to the workbook containing the code, regardless of what workbook happens to be active at some time. You may be able to simplify your code to merely using ThisWorkbook to return to the workbook containing the code. E.g., ThisWorkbook.Activate -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... I want to create a Public variable upon the opening or reactivation of a workbook. Public wbVLA08 = Application.ActiveWorkbook Then at various points in several modules I activate a different workbook, do something there, and then want to make sure the original workbook is activated. So I'd use: wbVLA08.Activate When a routine is called by another macro in which the Public variable has been set, I get flagged on it saying object hasn't been set. "Run-time error 91: Object variable or With block variable not set" Do I need to declare the variable in every module in which it is used? Do I need to set in each module? Do I need to make it static? If I understand it, I can't set in "This Workbook" which is what I'd like to do. How can I do this? |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scope of Public Variable
Further to Chip, it's not clear to me whether you're trying to use the
variable from code in another workbook, so... Public means visible throughout a given VB project. Another project (aka workbook) cannot see it unless it sets a reference to the workbook (Tools, References). -- Jim Rech Excel MVP |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scope of Public Variable
Does it matter that my code is spread over several modules?
My code now is ThisWorkbook is: 'enables any name for file so long as it starts with VL-A08 Public wbVLA08 As Workbook Private Sub Workbook_Open() On Error Resume Next Set wbVLA08 = ActiveWorkbook Application.Run "'DRKScenarios.xls'!CreateMenu" End Sub There are three modules that use this variable in six macros. (I'm not the only author of this code). Does that make any difference? Should I also have this in Workbook_WindowsActivate? Should I have a Set wbVLA08 = Nothing in the Workbook_BeforeClose and the Workbook_WindowDeactivate? "Chip Pearson" wrote in message ... Your syntax Public wbVLA08 = Application.ActiveWorkbook is wrong because you cannot initialize a variable in its declaration. You need to initialize in some initialization procedure, such as the Auto_Open macro or the Workbook_Open event procedure. For example, Public wbVLA08 As Workbook Sub Auto_Open() Set wbVBA08 = ActiveWorkbook End Sub Once initialized, it will retain its value until you change it or the workbook is closed. You can then Activate that workbook at any time in code with code like the following: wbVLA08.Activate Do I need to declare the variable in every module in which it is used? No, declare it once in a standard code module (not the ThisWorkbook module or a Sheet module or a class module), outside of and before any procedure. Do I need to set in each module? Do I need to make it static? No, declare it once. Public variables are "static" by definition. If I understand it, I can't set in "This Workbook" which is what I'd like to do. I'm not sure what the question is here. You can't declare it in the ThisWorkbook module (well, you can, but shouldn't). In code, ThisWorkbook always refers to the workbook containing the code, regardless of what workbook happens to be active at some time. You may be able to simplify your code to merely using ThisWorkbook to return to the workbook containing the code. E.g., ThisWorkbook.Activate -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... I want to create a Public variable upon the opening or reactivation of a workbook. Public wbVLA08 = Application.ActiveWorkbook Then at various points in several modules I activate a different workbook, do something there, and then want to make sure the original workbook is activated. So I'd use: wbVLA08.Activate When a routine is called by another macro in which the Public variable has been set, I get flagged on it saying object hasn't been set. "Run-time error 91: Object variable or With block variable not set" Do I need to declare the variable in every module in which it is used? Do I need to set in each module? Do I need to make it static? If I understand it, I can't set in "This Workbook" which is what I'd like to do. How can I do this? |
|
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scope of Public Variable
When running I get a "'Runtime error '424' Object Required". At this point
my active workbook is a newly saved XLS file and I want to reactivate the wbVLA08. There is no other place in any of the code where I am setting wbVLA08. I haven't closed the wbVLA08 but was not the active workbook. Would "Static" help or make a difference? "Chip Pearson" wrote in message ... It doesn't matter how many modules there are, assuming that all the modules are part of the same workbook. Should I also have this in Workbook_WindowsActivate? Should I have a Set wbVLA08 = Nothing in the Workbook_BeforeClose and the Workbook_WindowDeactivate? Probably not, but it isn't clear what you are trying to accomplish. You don't need to set the variable to Nothing in any case, unless you need to specifically test for this condition, which you most likely don't. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... Does it matter that my code is spread over several modules? My code now is ThisWorkbook is: 'enables any name for file so long as it starts with VL-A08 Public wbVLA08 As Workbook Private Sub Workbook_Open() On Error Resume Next Set wbVLA08 = ActiveWorkbook Application.Run "'DRKScenarios.xls'!CreateMenu" End Sub There are three modules that use this variable in six macros. (I'm not the only author of this code). Does that make any difference? Should I also have this in Workbook_WindowsActivate? Should I have a Set wbVLA08 = Nothing in the Workbook_BeforeClose and the Workbook_WindowDeactivate? "Chip Pearson" wrote in message ... Your syntax Public wbVLA08 = Application.ActiveWorkbook is wrong because you cannot initialize a variable in its declaration. You need to initialize in some initialization procedure, such as the Auto_Open macro or the Workbook_Open event procedure. For example, Public wbVLA08 As Workbook Sub Auto_Open() Set wbVBA08 = ActiveWorkbook End Sub Once initialized, it will retain its value until you change it or the workbook is closed. You can then Activate that workbook at any time in code with code like the following: wbVLA08.Activate Do I need to declare the variable in every module in which it is used? No, declare it once in a standard code module (not the ThisWorkbook module or a Sheet module or a class module), outside of and before any procedure. Do I need to set in each module? Do I need to make it static? No, declare it once. Public variables are "static" by definition. If I understand it, I can't set in "This Workbook" which is what I'd like to do. I'm not sure what the question is here. You can't declare it in the ThisWorkbook module (well, you can, but shouldn't). In code, ThisWorkbook always refers to the workbook containing the code, regardless of what workbook happens to be active at some time. You may be able to simplify your code to merely using ThisWorkbook to return to the workbook containing the code. E.g., ThisWorkbook.Activate -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... I want to create a Public variable upon the opening or reactivation of a workbook. Public wbVLA08 = Application.ActiveWorkbook Then at various points in several modules I activate a different workbook, do something there, and then want to make sure the original workbook is activated. So I'd use: wbVLA08.Activate When a routine is called by another macro in which the Public variable has been set, I get flagged on it saying object hasn't been set. "Run-time error 91: Object variable or With block variable not set" Do I need to declare the variable in every module in which it is used? Do I need to set in each module? Do I need to make it static? If I understand it, I can't set in "This Workbook" which is what I'd like to do. How can I do this? |
|
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scope of Public Variable
For this specific variable, it sounds like you are setting a reference to
the workbook containing the code itself. There is already an automatic variable you can use ThisWorkbook If you want a public variable visible throughout the project, it should be declared in a general module rather than the ThisWorkbook module (don't confuse with the ThisWorkbook object reference of which I just spoke). You can then set it in any module/routine, such as the workbook_Open event and it will be visible to all modules in the project. It should not be declared anywhere else. -- Regards, Tom Ogilvy "Dkline" wrote in message ... Does it matter that my code is spread over several modules? My code now is ThisWorkbook is: 'enables any name for file so long as it starts with VL-A08 Public wbVLA08 As Workbook Private Sub Workbook_Open() On Error Resume Next Set wbVLA08 = ActiveWorkbook Application.Run "'DRKScenarios.xls'!CreateMenu" End Sub There are three modules that use this variable in six macros. (I'm not the only author of this code). Does that make any difference? Should I also have this in Workbook_WindowsActivate? Should I have a Set wbVLA08 = Nothing in the Workbook_BeforeClose and the Workbook_WindowDeactivate? "Chip Pearson" wrote in message ... Your syntax Public wbVLA08 = Application.ActiveWorkbook is wrong because you cannot initialize a variable in its declaration. You need to initialize in some initialization procedure, such as the Auto_Open macro or the Workbook_Open event procedure. For example, Public wbVLA08 As Workbook Sub Auto_Open() Set wbVBA08 = ActiveWorkbook End Sub Once initialized, it will retain its value until you change it or the workbook is closed. You can then Activate that workbook at any time in code with code like the following: wbVLA08.Activate Do I need to declare the variable in every module in which it is used? No, declare it once in a standard code module (not the ThisWorkbook module or a Sheet module or a class module), outside of and before any procedure. Do I need to set in each module? Do I need to make it static? No, declare it once. Public variables are "static" by definition. If I understand it, I can't set in "This Workbook" which is what I'd like to do. I'm not sure what the question is here. You can't declare it in the ThisWorkbook module (well, you can, but shouldn't). In code, ThisWorkbook always refers to the workbook containing the code, regardless of what workbook happens to be active at some time. You may be able to simplify your code to merely using ThisWorkbook to return to the workbook containing the code. E.g., ThisWorkbook.Activate -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... I want to create a Public variable upon the opening or reactivation of a workbook. Public wbVLA08 = Application.ActiveWorkbook Then at various points in several modules I activate a different workbook, do something there, and then want to make sure the original workbook is activated. So I'd use: wbVLA08.Activate When a routine is called by another macro in which the Public variable has been set, I get flagged on it saying object hasn't been set. "Run-time error 91: Object variable or With block variable not set" Do I need to declare the variable in every module in which it is used? Do I need to set in each module? Do I need to make it static? If I understand it, I can't set in "This Workbook" which is what I'd like to do. How can I do this? |
|
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scope of Public Variable
What line of code is causing the Object Required message?
Would "Static" help or make a difference? "Static" is not allowed in this context, as global variables are, by definition, static. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... When running I get a "'Runtime error '424' Object Required". At this point my active workbook is a newly saved XLS file and I want to reactivate the wbVLA08. There is no other place in any of the code where I am setting wbVLA08. I haven't closed the wbVLA08 but was not the active workbook. Would "Static" help or make a difference? "Chip Pearson" wrote in message ... It doesn't matter how many modules there are, assuming that all the modules are part of the same workbook. Should I also have this in Workbook_WindowsActivate? Should I have a Set wbVLA08 = Nothing in the Workbook_BeforeClose and the Workbook_WindowDeactivate? Probably not, but it isn't clear what you are trying to accomplish. You don't need to set the variable to Nothing in any case, unless you need to specifically test for this condition, which you most likely don't. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... Does it matter that my code is spread over several modules? My code now is ThisWorkbook is: 'enables any name for file so long as it starts with VL-A08 Public wbVLA08 As Workbook Private Sub Workbook_Open() On Error Resume Next Set wbVLA08 = ActiveWorkbook Application.Run "'DRKScenarios.xls'!CreateMenu" End Sub There are three modules that use this variable in six macros. (I'm not the only author of this code). Does that make any difference? Should I also have this in Workbook_WindowsActivate? Should I have a Set wbVLA08 = Nothing in the Workbook_BeforeClose and the Workbook_WindowDeactivate? "Chip Pearson" wrote in message ... Your syntax Public wbVLA08 = Application.ActiveWorkbook is wrong because you cannot initialize a variable in its declaration. You need to initialize in some initialization procedure, such as the Auto_Open macro or the Workbook_Open event procedure. For example, Public wbVLA08 As Workbook Sub Auto_Open() Set wbVBA08 = ActiveWorkbook End Sub Once initialized, it will retain its value until you change it or the workbook is closed. You can then Activate that workbook at any time in code with code like the following: wbVLA08.Activate Do I need to declare the variable in every module in which it is used? No, declare it once in a standard code module (not the ThisWorkbook module or a Sheet module or a class module), outside of and before any procedure. Do I need to set in each module? Do I need to make it static? No, declare it once. Public variables are "static" by definition. If I understand it, I can't set in "This Workbook" which is what I'd like to do. I'm not sure what the question is here. You can't declare it in the ThisWorkbook module (well, you can, but shouldn't). In code, ThisWorkbook always refers to the workbook containing the code, regardless of what workbook happens to be active at some time. You may be able to simplify your code to merely using ThisWorkbook to return to the workbook containing the code. E.g., ThisWorkbook.Activate -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... I want to create a Public variable upon the opening or reactivation of a workbook. Public wbVLA08 = Application.ActiveWorkbook Then at various points in several modules I activate a different workbook, do something there, and then want to make sure the original workbook is activated. So I'd use: wbVLA08.Activate When a routine is called by another macro in which the Public variable has been set, I get flagged on it saying object hasn't been set. "Run-time error 91: Object variable or With block variable not set" Do I need to declare the variable in every module in which it is used? Do I need to set in each module? Do I need to make it static? If I understand it, I can't set in "This Workbook" which is what I'd like to do. How can I do this? |
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scope of Public Variable
if wbVLA08 is the workbook running the code:
ThisWorkbook.Activate -- Regards, Tom Ogilvy "Dkline" wrote in message ... When running I get a "'Runtime error '424' Object Required". At this point my active workbook is a newly saved XLS file and I want to reactivate the wbVLA08. There is no other place in any of the code where I am setting wbVLA08. I haven't closed the wbVLA08 but was not the active workbook. Would "Static" help or make a difference? "Chip Pearson" wrote in message ... It doesn't matter how many modules there are, assuming that all the modules are part of the same workbook. Should I also have this in Workbook_WindowsActivate? Should I have a Set wbVLA08 = Nothing in the Workbook_BeforeClose and the Workbook_WindowDeactivate? Probably not, but it isn't clear what you are trying to accomplish. You don't need to set the variable to Nothing in any case, unless you need to specifically test for this condition, which you most likely don't. -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... Does it matter that my code is spread over several modules? My code now is ThisWorkbook is: 'enables any name for file so long as it starts with VL-A08 Public wbVLA08 As Workbook Private Sub Workbook_Open() On Error Resume Next Set wbVLA08 = ActiveWorkbook Application.Run "'DRKScenarios.xls'!CreateMenu" End Sub There are three modules that use this variable in six macros. (I'm not the only author of this code). Does that make any difference? Should I also have this in Workbook_WindowsActivate? Should I have a Set wbVLA08 = Nothing in the Workbook_BeforeClose and the Workbook_WindowDeactivate? "Chip Pearson" wrote in message ... Your syntax Public wbVLA08 = Application.ActiveWorkbook is wrong because you cannot initialize a variable in its declaration. You need to initialize in some initialization procedure, such as the Auto_Open macro or the Workbook_Open event procedure. For example, Public wbVLA08 As Workbook Sub Auto_Open() Set wbVBA08 = ActiveWorkbook End Sub Once initialized, it will retain its value until you change it or the workbook is closed. You can then Activate that workbook at any time in code with code like the following: wbVLA08.Activate Do I need to declare the variable in every module in which it is used? No, declare it once in a standard code module (not the ThisWorkbook module or a Sheet module or a class module), outside of and before any procedure. Do I need to set in each module? Do I need to make it static? No, declare it once. Public variables are "static" by definition. If I understand it, I can't set in "This Workbook" which is what I'd like to do. I'm not sure what the question is here. You can't declare it in the ThisWorkbook module (well, you can, but shouldn't). In code, ThisWorkbook always refers to the workbook containing the code, regardless of what workbook happens to be active at some time. You may be able to simplify your code to merely using ThisWorkbook to return to the workbook containing the code. E.g., ThisWorkbook.Activate -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... I want to create a Public variable upon the opening or reactivation of a workbook. Public wbVLA08 = Application.ActiveWorkbook Then at various points in several modules I activate a different workbook, do something there, and then want to make sure the original workbook is activated. So I'd use: wbVLA08.Activate When a routine is called by another macro in which the Public variable has been set, I get flagged on it saying object hasn't been set. "Run-time error 91: Object variable or With block variable not set" Do I need to declare the variable in every module in which it is used? Do I need to set in each module? Do I need to make it static? If I understand it, I can't set in "This Workbook" which is what I'd like to do. How can I do this? |
|
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Scope of Public Variable
Tom,
Thank you. My problem is solved. Works first time, everytime. Don "Tom Ogilvy" wrote in message ... For this specific variable, it sounds like you are setting a reference to the workbook containing the code itself. There is already an automatic variable you can use ThisWorkbook If you want a public variable visible throughout the project, it should be declared in a general module rather than the ThisWorkbook module (don't confuse with the ThisWorkbook object reference of which I just spoke). You can then set it in any module/routine, such as the workbook_Open event and it will be visible to all modules in the project. It should not be declared anywhere else. -- Regards, Tom Ogilvy "Dkline" wrote in message ... Does it matter that my code is spread over several modules? My code now is ThisWorkbook is: 'enables any name for file so long as it starts with VL-A08 Public wbVLA08 As Workbook Private Sub Workbook_Open() On Error Resume Next Set wbVLA08 = ActiveWorkbook Application.Run "'DRKScenarios.xls'!CreateMenu" End Sub There are three modules that use this variable in six macros. (I'm not the only author of this code). Does that make any difference? Should I also have this in Workbook_WindowsActivate? Should I have a Set wbVLA08 = Nothing in the Workbook_BeforeClose and the Workbook_WindowDeactivate? "Chip Pearson" wrote in message ... Your syntax Public wbVLA08 = Application.ActiveWorkbook is wrong because you cannot initialize a variable in its declaration. You need to initialize in some initialization procedure, such as the Auto_Open macro or the Workbook_Open event procedure. For example, Public wbVLA08 As Workbook Sub Auto_Open() Set wbVBA08 = ActiveWorkbook End Sub Once initialized, it will retain its value until you change it or the workbook is closed. You can then Activate that workbook at any time in code with code like the following: wbVLA08.Activate Do I need to declare the variable in every module in which it is used? No, declare it once in a standard code module (not the ThisWorkbook module or a Sheet module or a class module), outside of and before any procedure. Do I need to set in each module? Do I need to make it static? No, declare it once. Public variables are "static" by definition. If I understand it, I can't set in "This Workbook" which is what I'd like to do. I'm not sure what the question is here. You can't declare it in the ThisWorkbook module (well, you can, but shouldn't). In code, ThisWorkbook always refers to the workbook containing the code, regardless of what workbook happens to be active at some time. You may be able to simplify your code to merely using ThisWorkbook to return to the workbook containing the code. E.g., ThisWorkbook.Activate -- Cordially, Chip Pearson Microsoft MVP - Excel Pearson Software Consulting, LLC www.cpearson.com "Dkline" wrote in message ... I want to create a Public variable upon the opening or reactivation of a workbook. Public wbVLA08 = Application.ActiveWorkbook Then at various points in several modules I activate a different workbook, do something there, and then want to make sure the original workbook is activated. So I'd use: wbVLA08.Activate When a routine is called by another macro in which the Public variable has been set, I get flagged on it saying object hasn't been set. "Run-time error 91: Object variable or With block variable not set" Do I need to declare the variable in every module in which it is used? Do I need to set in each module? Do I need to make it static? If I understand it, I can't set in "This Workbook" which is what I'd like to do. How can I do this? |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Name Scope | Excel Worksheet Functions | |||
| Set Public Variable on Open | Excel Discussion (Misc queries) | |||
| Public variable | New Users to Excel | |||
| public variable | Excel Programming | |||
| Scope of variable includes all Form _and_ Code modules?? | Excel Programming | |||
![Dkline[_2_] is offline](https://www.excelbanter.com/images/SultanThemeVB3R/statusicon/user_offline.gif)
Linear Mode
