public declaration

  • Thread starter Thread starter kevin
  • Start date Start date
K

kevin

Hi

I am writting several modules. The modules refer to a lot
of the same spreadsheets within a single workbook. For
each module I have been declaring the different sheets.
for instance
Dim wsMe as worksheet
set wsMe = thisworkbook.worksheets("Me")

Basically I am repeting the code in every module
Can I declare things like this just once and then not have
to include it in every module

Thanks in advance
 
Hi Kevin,

If you are referencing worksheets in the same workbook where your code
resides, you don't need to declare any variables at all. The Excel object
model provides a CodeName for each worksheet that can be used to reference
the worksheet from anywhere in the workbook.

If you look at the list of worksheet in your workbook in the Project
window of the Visual Basic Editor, you will see two names listed, similar to
this:

--Sheet1 (Me)

In this case, "Sheet1" is the CodeName and "Me" is the sheet tab name. If
you want to reference the Me worksheet anywhere in your code, you simply use
its CodeName. For example:

Sheet1.Range("A1").Value = 10

You can also change the CodeName in the Properties window so that it's the
same as the sheet tab name, which will make your code easier to understand.

--
Rob Bovey, MCSE, MCSD, Excel MVP
Application Professionals
http://www.appspro.com/

* Please post all replies to this newsgroup *
* I delete all unsolicited e-mail responses *
 
Back
Top