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,

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

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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads