Declare and Set Public variables
 

I am trying to declare and set a public variable. The variable is
Workbook, and I am trying to set the active workbook as a variable to
be used in other macros without having to reset this.

I am thinking something like Public DPE As Workbook = Activeworkbook
or thisworkbook.

Any help will be greatly appreciated,
Jay

Declare and Set Public variables
 

Public DPE As Workbook

Set DPE = Activeworkbook



On 28 Sty, 15:01, jlclyde wrote:
I am trying to declare and set a public variable. *The variable is
Workbook, and I am trying to set the active workbook as a variable to
be used in other macros without having to reset this.

I am thinking something like Public DPE As Workbook = Activeworkbook
or thisworkbook.

Any help will be greatly appreciated,
Jay

Declare and Set Public variables
 

You cannot assign a value to a variable in the variable declarations.
Thus, code like

Public WB As Workbook = ActiveWorkbook

will fail because it assigns the value in the declaration. You need to
separate the declaration from the assignment. In a regular code
module, enter the following in the Declarations section (before and
outside of any procedure declaration):

Public DPE As Workbook

Then, you'll have to assign the ActiveWorkbook to the variable. You
can do this anywhere, but if you want to do it when the workbook
opens, use the following in the ThisWorkbook code module:

Private Sub Workbook_Open()
Set DPE = ActiveWorkbook
End Sub

This is rather pointless because the ActiveWorkbook will be the
workbook that contains the code. You can always reference the workbook
that contains the code, regardless of what workbook happens to be
active, with ThisWorkbook. E.g.,

Debug.Print ThisWorkbook.Name

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)


On Wed, 28 Jan 2009 06:01:19 -0800 (PST), jlclyde
wrote:

I am trying to declare and set a public variable. The variable is
Workbook, and I am trying to set the active workbook as a variable to
be used in other macros without having to reset this.

I am thinking something like Public DPE As Workbook = Activeworkbook
or thisworkbook.

Any help will be greatly appreciated,
Jay