Set objects at module level?

[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

 

[Jan Karel Pieterse]

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?

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

 

[Ed]

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.

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

 

[Jan Karel Pieterse]

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?

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