Scope of Public Variable

  • Thread starter Thread starter Dkline
  • Start date Start date
D

Dkline

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?
 
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?
Click to expand...

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?
Click to expand...

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.
Click to expand...

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 (e-mail address removed)
 
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).
 
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?
 
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?
Click to expand...

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 (e-mail address removed)
 
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?
 
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.
 
What line of code is causing the Object Required message?
Would "Static" help or make a difference?
Click to expand...

"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 (e-mail address removed)
 
Back
Top