Declare and Set Public variables

[jlclyde]

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

 

[Jarek Kujawa]

Public DPE As Workbook

Set DPE = Activeworkbook

 

[Chip Pearson]

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)