store variables in vba

**mcinnes01** · 01-11-2011, 10:27 AM

Hi,

I am working on a multistage excel form, and one part of the process it prints the excel sheet to word as a picture and gives you the option to print it. It then saves the word doc in a folder and closes word. The files name is variable dependant upon the date and the individual it relates to.

I need a way that I can pull up this document again in another module, I know you can set constants/variables with dim and set, but is there a way of setting these globably? if thats the word, for this project so they can be referenced in various modules?

**romperstomper** · 01-11-2011, 10:31 AM

You can:
1. Declare the variable as Public at the top of a module (before any routines)
2. Write the data to a cell or defined name
3. Pass the variable directly to the other routine, if possible.

**mcinnes01** · 01-11-2011, 10:41 AM

If you declare a variable as Public does that work for all functions within that module or all modules?

Also point 3, passing the variable through, how do you do this and which method do you think is the best?

**romperstomper** · 01-11-2011, 10:44 AM

Public works for all modules in that project.

As a general rule you should keep the scope of variables as small as possible. Which is best of those three depends on how you are using the variable. If the routine that creates the variable is not calling the other routine that needs to use it, then 3 is out, so either use 1 or 2. 2 is safer in terms of the variable not losing state (i.e. losing its value) if unhandled errors occur or you start debugging code. Depends if that is an issue.