Hi All
I am declaring a two public variables at module level and wish to use SET Statement.
I am trying to use SET statement at module level / this work book level but i get a error message stating invalid outside procedure
My Query where can i set the value so that i can use WB1/WS1 across different sheets / procedures
[/CODE]
Public wb1 As Workbook
Public ws1 As Worksheet
Set wb1 = ThisWorkbook
Set ws1 = wb1.Sheets("Sheet2")
[/CODE]
Never confuse a single defeat to the final defeat.
Edit:
I should have my coffee before posting.
The variable has to be declared at the top of the module and the set statements have to be in a sub or function, as far as I know.
Last edited by skywriter; 12-02-2022 at 11:04 AM.
Click the * Add Reputation button in the lower left hand corner of this post to say thanks.
Don't forget to mark this thread SOLVED by going to the "Thread Tools" drop down list above your first post and choosing solved.
Useless variables again ...
Hi !
As creating a workbook variable is often useless to just create a worksheet variable !
Like Set Ws1 = ThisWorkbook.Sheets("Sheet2") …
As obviously ThisWorkbook is already the workbook variable object so not necessary to duplicate it !
As the sheet is within Thisworkbook object so creating a worksheet variable is useless as already exists its CodeName Sheet2,
exactly like a worksheet variable …
As both ThisWorkbook & sheets CodeNames are already Public at project level so already available for any VBA procedure !
Thank you skywriter, marc for your suggestions.
Probably the simplest way is to add a function to your module which sets the variables
Please Login or Register to view this content.
Then in your routines you can check them
Please Login or Register to view this content.
Re: Variable at Module level
Again : useless object variables ! As the simplest way is still to just use ThisWorkbook and sheets CodeNames …
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks