Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


declare worksheet as constant

Posted on 2009-02-16
Medium Priority
Last Modified: 2013-11-25
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.
Question by:j8547
  • 2
LVL 17

Expert Comment

ID: 23648775
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

Author Comment

ID: 23648909
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
LVL 17

Accepted Solution

ExcelGuide earned 400 total points
ID: 23649037
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):

Hope it will help you out, good luck

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Suggested Courses
Course of the Month11 days, 22 hours left to enroll

564 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question