Declaration of Global Objects

[stonee74]

Hey there

I am working on an application with VBA and excel, so far is everything working fine.
I've got all my code behind one single form.
Since i started to add an additional dialog, i had problems with my object called ws.

i declared in a module:
Public ws as Object

in UserForm_Initialze:
set ws = Worksheets(1)

i want to use object ws everywhere in my project.
Where do i have to "set ws = Worksheets(1)"???

in open workbook event it does not work as well!

any help is greatlty appreciated!

stonee

 

[rmikesmith]

Stonee,

By definition, the code inside Workbook_Open will run before any Userform initialization code. Therefore, if you want to reference your ws variable inside Workbook_Open, you need to use

Set ws = Worksheets(1)

there. Once assigned, you should be able to reference ws in your other procedures (something to keep in mind, however: if an error occurs, you could loose this reference. It doesn't hurt to re-assign this at the beinning of any procedure that will use it). I would also recommend using an explicit worksheet name rather than an index, which could easily change if sheets are inserted or deleted.

HTH

M. Smith