Just wondering

[Ken McLennan]

G'day there One and All,

I have a workbook with 4 worksheets. In a general code module I've
declared 4 Public variables thusly:

Public aSht as Worksheet
Public sSht... etc.

In my code I then use Set to assign each worksheet to its
variable.

Set aSht = Worksheets("Analysis")

This works fine, however I've found that since I have no control
over which section of the code will run first (determined by the user's
options) I've had to assign the variables more than once, at the start
of several different routines.

Is there anywhere I can put the assignments where I need only use
them once?

I thought of the Workbook_Open() section of ThisWorkbook, but
that's not going to work as it runs before the sheets are loaded.

Any ideas? It's not a biggy, as my code runs successfully and this
may just have to be. I just thought there may be a more elegant &
efficient way to assign them.

See ya
Ken McLennan
Qld, Australia

 

[Tom Ogilvy]

In a general module, not the thisworkbook module, put in your public
variables

then in the ThisworkbookModule

Private Sub workbook_open()

set aSht = thisworkbook.Worksheets("Sheet1")
set sSht = Thisworkbook.Worksheets("Sheet2")
etc.

end Sub

It should work.

 

[Ken McLennan]

G'day there Tom,

In a general module, not the thisworkbook module, put in your public
variables

then in the ThisworkbookModule

Private Sub workbook_open()

set aSht = thisworkbook.Worksheets("Sheet1")
set sSht = Thisworkbook.Worksheets("Sheet2")
etc.

end Sub

It should work.

Yuppers!!

It worked like a charm. Thanks for your advice, Tom. Much
appreciated.

See ya
Ken McLennan
Qld, Australia