Set objects at module level?

  • Thread starter Thread starter Ed
  • Start date Start date
E

Ed

I have a module with several Subs. All the Subs will refer to the
same objects. I know how to Dim the object references at the top of
the module, but then I have to Set them in each Sub. Is there a way
to set workbook, worksheet and range objects for the entire module?

Ed
 
Hi Ed,
I have a module with several Subs. All the Subs will refer to the
same objects. I know how to Dim the object references at the top of
the module, but then I have to Set them in each Sub. Is there a way
to set workbook, worksheet and range objects for the entire module?
Click to expand...

Setting them in one sub will set them for all, so make sure your entry
sub checks whether the object variables have been set, if not, set
them.

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com
 
Hi, Jan.
Setting them in one sub will set them for all, so make sure your entry
sub checks whether the object variables have been set, if not, set
them.
Click to expand...

And all this time, I thought the objects vanished with every End Sub!
So if I'm doing a lot of Subs in THisWorkbook module, I can DIm and
Set objects in a Workbook_Open sub and they will live in that module
as long as the workbook is open?

Ed
 
Hi Ed,
So if I'm doing a lot of Subs in THisWorkbook module, I can DIm and
Set objects in a Workbook_Open sub and they will live in that module
as long as the workbook is open?
Click to expand...

Exactly. As long as you don't press "End" on a runtime error...

Regards,

Jan Karel Pieterse
Excel MVP
http://www.jkp-ads.com
Member of:
Professional Office Developer Association
www.proofficedev.com
 
Back
Top