Publically set Object Variable

[ilya2004]

Hello,
I am trying to store some worksheet names to variables so that I can easily refer to them in all my sub routines in different modules.

It seems to me that there is not "official" way to permanently store an object variable name to be used by different sub routines, but does anyone have any quick-fix way for me to do this?
-Ilya

 

[shadow12345]

Define the varailable outside of the Subs inside of a module... eg this one for a path name



Public Const filepaths As String = "\\Wpltsz31\fvcimport\Settlements\Options\"

Sub testing
'a bit of code
End sub

Then you can call "Filepaths" in any sub

 

[ilya2004]

so in this case, lets say I have a worksheet called "User Data" that I would like to be able to reference as "wUD" in every routine, how would that look like in your code?

 

[shg]

At the top of a code module, just below Option Explicit,

Public wksUD as Worksheet

You can reference that in any module in the Project.

I'd suggest that you have one module that initializes all public variables.

 

[ilya2004]

I'm a little unclear. Doing this would only declare the variable type globally, I want to permanently tie the variable to a specific worksheet.

 

[shg]

In the procedure where you initialize variables,

Set wksUD = Worksheets("User Data")

 

[ilya2004]

I tried to do it that way, but for some reason as soon as I take the declaration out of the running module and put it into a separate module (that I run in the current one), I get an object error. That's what prompted me to post the question.

 

[RoryA]

as soon as I take the declaration out of the running module and put it into a separate module (that I run in the current one)

Can you explain exactly what you mean by that?

 

[shg]

It has to be in the same Project (i.e., in the same workbook) as other code that uses it, and it must be in a code module (not a sheet module or ThisWorkbook), and you must not declare it in any other procedure, but other than that, it should work fine.

If it doesn't, post your code with an explanation of where it appears.

Howdy, Rory.

 

[RoryA]

Hola, amigo.