Global Scope for Variables

[DougRobertson]

Hello,

I am trying to get a Variable to be accessible between 2 workbooks, and it appears that the Global scope is what I'm looking for. However, that doesn't appear to work for me.
Do I need to declare this at the beginning and above the first Sub Procedure of the first Module of the first Workbook of the code being used?
Also, do I need to use the "Option Explicit" code for this to work across 2 workbooks?

The declaration code I am using is:

Public iDISTRIBUTIONListFolder As String

Also, is it possible to use multiple variable names in one declaration, such as:

Public iDISTRIBUTIONListFolder, iCOMPUTERName As String

Thank you in advance for any and all help on this!

~ DWR

 

[Domenic]

Do I need to declare this at the beginning and above the first Sub Procedure of the first Module of the first Workbook of the code being used?

Yes, you need to declare your public variable at the top of a regular module before the first procedure. Now, let's say that your first workbook contains the public variable and that you'd like your second workbook to access it. In your second workbook, you'll need to set a reference to your first workbook (Visual Basic Editor>Tools>Reference). Before setting your reference, though, make sure that you give your first workbook a unique project name (VBE>Tools>VBAProject Properties>General tab>Project Name). This way you'll be able to identify the workbook when trying to set a reference to it.

By the way, are you calling a procedure that exists in the second workbook, and want that procedure to have access to the public variable?

Also, do I need to use the "Option Explicit" code for this to work across 2 workbooks?

No, Option Explicit simply forces the explicit declaration of variables.

Also, is it possible to use multiple variable names in one declaration, such as:

Public iDISTRIBUTIONListFolder, iCOMPUTERName As String

Yes, but in your example iDISTRIBUTIONListFolder is being declared as a Variant, not a String. To declare it as a String, you'll need to do so like this Public iDISTRIBUTIONListFolder as String, iCOMPUTERName As String.

Hope this helps!

 

[DougRobertson]

By the way, are you calling a procedure that exists in the second workbook from your first workbook?

Yes. Here's the process:
Step 1: Type "Ctrl + m" to execute my Personal Macro Director, which lives in the hidden Personal Excel workbook on my computer. I set the Shortcut.
Step 2: The main purpose of my Personal Macro Director is only to open the Company Macro Director template, then Run that Macro.
Step 3: The Company Macro Director then opens the Specific Task Macro template, then Runs that Macro.

We have about 20 staff computers, and I install the same Personal Macro Director on each computer's Personal Excel workbook, with the same Shortcut, so that as I create and update our company Macros, I don't have to touch anyone's computer. Those Macros are only there so that they can all just use "Ctrl + m" to initiate the Personal Macro Director, and Run the Company Macro Director, which lives in a shared company folder. From there, based on parameters of open and Active Excel workbooks on the screen, the Company Macro Director figures out which Specific Task Macro from the shared company folder to open and Run.

But from what you're telling me, I'd have to place all the Public declarations at the top of everyone's Personal Macro Director in order to get all variables still to be filled, to be available to all the subsequent Macros, correct? Is there any way these could go at the top of the Company Macro Director (the second level Macro)?

I just want to double-check to make sure.

Thanks for helping with this!

 

[Jaafar Tribak]

Alternatively, if you don't want to set a reference to the first workbook , you could do the following :

Declare the variables as Public at the top of the ThisWorkbook Module as follows:

Public Var1 as Long, Var2 as String

Private Sub InitiateVars()
    Var1 = 3
    Var2 = "Hi"
End Sub

and then, you can access those variables from the calling workbook by fully qualifying the variables as follows :

MsgBox Workbooks("ServerWorkbook.xlsm").Var1
MsgBox Workbooks("ServerWorkbook.xlsm").Var2

Where ServerWorkbook.xlsm is the name of the workbook where the variables are declared.... Change this name as required.


Optional:

To further shorten this process as well as the editing , you can define 2 functions in the calling workbook (one for reading and one for writing) like this :

Public Function GetVarValue(ByVal VarName As String)
    GetVarValue = CallByName(Workbooks("ServerWorkbook.xlsm"), VarName, VbGet)
End Function

Public Sub SetVarValue(ByVal VarName As String, ByVal NewVal As Variant)
    Call CallByName(Workbooks("ServerWorkbook.xlsm"), VarName, VbLet, NewVal)
End Sub

And then you can simply read & set the variables from the calling workbook as follows :

MsgBox GetVarValue("Var1")
MsgBox GetVarValue("Var2")

SetVarValue "Var1", 100
SetVarValue "Var2", "Hello"

 

[Jaafar Tribak]

But from what you're telling me, I'd have to place all the Public declarations at the top of everyone's Personal Macro Director in order to get all variables still to be filled, to be available to all the subsequent Macros, correct? Is there any way these could go at the top of the Company Macro Director (the second level Macro)?
I just want to double-check to make sure.

If the personal workbook is the one setting or reading the variables before the Company Macro Director workbook is opened , then it is not possible.

 

[DougRobertson]

Hi Jaafar,

If the Personal Macro Director (first level Macro) has no variables, and all variable setting is done only after the Company Macro Director (second level Macro) is opened, would it work to declare all declarations at the top of the Company Macro Director?

Thanks to you too!

 

[Jaafar Tribak]

Hi Jaafar,

If the Personal Macro Director (first level Macro) has no variables, and all variable setting is done only after the Company Macro Director (second level Macro) is opened, would it work to declare all declarations at the top of the Company Macro Director?

I guess, in that case it should work .. Can you not give it a test just to make sure it works ?

PS: My apologies to @Domenic for jumping the gun.

 

[DougRobertson]

That sounds encouraging - I'll give it a try tomorrow and let you know.