declare worksheet as constant

I have 3 worksheet in my workbook. I have declared them ws1 = .... ws2 = ..... ws3=.......

I have to constantly set them as i use them in most modules. How can i set them once at the begining and then reference them after.
j8547Asked:
Who is Participating?
 
ExcelGuideConsultantCommented:
it should work, here also another explanation:
Public or Global variables - all procedures in all modules can see & use them. Declared in the General Declarations section of a module using the keyword 'Public'. Even though you can declare public variables in a form module, I would declare all public variables in a standard module (because it is loaded when the application starts & is available throughout the application). Form modules are only available once the form has be loaded & until it is unloaded. So, that could be A source of your problem....

Anyway, I once used another solution and used the following site (document properties):
http://www.cpearson.com/excel/docprop.aspx

Hope it will help you out, good luck
0
 
ExcelGuideConsultantCommented:
did you already try:
Public ws1, ws2, ws3

at the beginning of your code, so something like
Public ws1, ws2, ws3
Sub yourcode()
{yoour code
ws1 = "sheet1"
}
End Sub
0
 
j8547Author Commented:
I have Public ws1, ws2, ws3 at the top. Then in my code i have set ws 1 = thisworkbook.worksheet......
the problem is i have to do this in every Sub. If i say use ws 1 without setting it again i get an error. I was looking to only set it once and thats it. Can this be done
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.