Set Statement in Excel

  • Thread starter Thread starter LewisR
  • Start date Start date
L

LewisR

I'm using a public constant to assign the "DiDR" as a worksheet object.
I'm then setting this at the procedural level to the specific
worksheet. However, my application contains numerous procedures
attached to 3 Sheets, Open Workbook, and 3 Modules. Is there anyone I
can use a global set statement (and then set = nothing) at the Close
Workbook state? It seems as if I need to write the set statement on
each sheet with macros. Thanks.
 
With a Public declared variable ........ eg.
Public DiDR As Worksheet
you only need to 'Set' it once at the beginning of the macro. Obviousl
you have to Set again if you want to change it.

In a large project it is a good idea to use a separate module (withou
code) to declare the Public variables. I would Set these, with others
at the very beginning of a macro - probably within a special Su
Initialise () routine. There seems little use having a Public variabl
that is not Set early on. This is also useful when debugging cod
because, this can be called to allow individual subroutines to b
tested on their own.

I don't think it would be necessary to Set it to Nothing at the en
because, in theory, Excel does this on closing the workbook. You ar
probably thinking of declaring objects referring to *external
applications, such as Word and Access, when it *is* necessary
 
To Brian (who responded to my original query).
Thanks for your reply. I have tried to SET at the separate modul
where the public/constants are declared but the SET does not pass t
any subroutines or procedures attached to different worksheets. That'
the problem. Any ideas? Thanks
 
Public ( & other project level Dim statements) go outside all cod
modules (on their own at the top of a module). eg.
Public DiDR As Worksheet

Set statements have to be within a code module and, obviously, ru
before you can actually use them.

eg. Set DiDr = Workbooks("Book1.xls").Worksheets("DiDr")

Public variables are then available to all VBA modules in all offic
applications
 
Back
Top