Set variables that persist in entire workbook?

[mrbusto71]

Howdy!

I have a question that I found answers to online, but the answers I found confused me more than helped me.

I have a workbook I built out a ton of code for. It has 12 sheets (Jan-Dec), and each sheet has 5 buttons. I set variables for names on these buttons as follows:

Please Login or Register  to view this content.

So since each sheet has 5 buttons, I had to set these names a total of 60 times. Is there a way I can just set them once? It'd make life easier if staffing changes occur to just go change one set of variables vs. having to update 60, but I'm not sure how to do that.

Also, and this isn't as important, but is there a way for a separate, open workbook, to call the variables from this one? The code in this other workbook will be using the exact same set of variables, but since it's on a completely different workbook I didn't know if it was possible.

Thanks for the help! All the answers I've received from this forum have slowly helped me increase my VBA knowledge to the point that I'm finally writing my own code (albeit slowly), so I can't wait to learn about this too!

[mikeTRON]

Yeah so instead of defining them statically in the code, I would have a very hidden tab that houses all the references.
So instead of

Please Login or Register  to view this content.

you can have

Please Login or Register  to view this content.

Actually... I would take that a step further and likely assign a named range to the cell in Excel so you can just refer to it in the code instead of B2.

Please Login or Register  to view this content.

assuming you have the cell defined as Name1

[Neil_]

Agree with Mike as there are likely to be lots of Employees, so a table to store them in would be for the best. If you have one or two constants, you can declare them in a separate module. In the VB Editor, insert module and place your constants there. To declare a global constant, you would use this format:-

Please Login or Register  to view this content.

You have to remove all the 'Dim' declarations of this variable throughout your code after making it a global constant. As long as you have typed them exactly, you can remove them using find and replace (Ctrl H)

[Norie]

Just curious, but how/where/when are you going to use these variables in connection with the buttons?

[mrbusto71]

Just curious, but how/where/when are you going to use these variables in connection with the buttons?

Each button calls up to 40 different workbooks to retrieve specific data from them and copies that data into this doc. So this doc is like a "Master Doc" where all employee information for a given week will be reviewable before saving (rather than going to each employee doc and copying the data manually).

I figured setting the names in the code as shortcuts would make life easier by being able to update the values in the code rather than updating the names manually (i.e. ft1 = "John Doe" and never having to update the ft1 the code calls for rather than replacing "John Doe" if a staffing change occurs).

It sounds like it may be better to simplify it even further and list the names on the sheet and just refer to cells, so I may look into that instead.

[mrbusto71]

Agree with Mike as there are likely to be lots of Employees, so a table to store them in would be for the best. If you have one or two constants, you can declare them in a separate module. In the VB Editor, insert module and place your constants there. To declare a global constant, you would use this format:-

Please Login or Register  to view this content.

You have to remove all the 'Dim' declarations of this variable throughout your code after making it a global constant. As long as you have typed them exactly, you can remove them using find and replace (Ctrl H)

I might go the other route by listing in cells, but just for my own knowledge base, would I put this code on another sheet, or would I do it on the ThisWorkbook sheet in VB editor?

[Norie]

Still not sure how you are using the names/variables but it does sound like a list on a worksheet might be a better idea.

That would be far easier to maintain and would avoid the user having to go into the code to make changes.

[Neil_]

As I said in my answer, in the VB Editor, insert a module and place your constants there. Globals must be placed in a module.

[mrbusto71]

Still not sure how you are using the names/variables but it does sound like a list on a worksheet might be a better idea.

That would be far easier to maintain and would avoid the user having to go into the code to make changes.

Here's a snippet of code to demonstrate:

Please Login or Register  to view this content.

So here, 2 workbooks are set, the employee workbook and the Master. Anywhere you see ft1 or lft1, I'm using those declared variables to avoid using a name, that way I only have to update the name in one area rather than going through each section of code and updating names that way.

Hopefully that makes sense, I'm still new to VBA so I'm learning where to balance standard Excel functions with VBA things. I can definitely see where using a list on a sheet is better.

Is there an argument I can add that would ignore blank cells if I go that route? In case someone accidentally deletes a name, I'd prefer it skip the blank and keep going rather than get hung up because it can't find a value to match.

[mrbusto71]

Neil, thanks! Sorry, I didn't know it was as simple as Insert > Module. d'oh!

Appreciate it

[Neil_]

Also, been mixing my terms. Its a Public Contstant, not a Global Variable. You can declare Global Variables in a module like this

Please Login or Register  to view this content.

Whilst Public constants are relatively benign, Global variables can cause problems when debugging code, especially for other users, so are notorious and disliked by many. They do however prove invaluable at times. Google 'Global Variables problems' to see what I mean.

[mikeTRON]

Still not sure how you are using the names/variables but it does sound like a list on a worksheet might be a better idea.
That would be far easier to maintain and would avoid the user having to go into the code to make changes.

This is generally why I keep my constants in a worksheet, so people not as familiar with code can go see what is being used (especially when there is a rollover YEAR change like when they need to change 2016 to 2017).
This way I THINK they bother me less for help.