Hi,
I'd like to declare a variable once in an referenced xla file, in a
way that all the individual subs in the referring workbook can give it
a local value.
Is this possible?

Regards, Poniente

On 9/29/2011 4:11 AM, Poniente wrote:
Hi,
I'd like to declare a variable once in an referenced xla file, in a
way that all the individual subs in the referring workbook can give it
a local value.
Is this possible?
Regards, Poniente

The first answer posted on this page may work for you:

.... create a simple xla called George:
Public harry As Variant
Public Sub setHarry(x)
harry = x
End Sub
Public Function getHarry()
getHarry = harry
End Function
I installed the xla. Then I created Alice.xls with a text box that
called setHarry when it changed and a cell with =getHarry() in it. I
kept it really simple:
Private Sub TextBox1_Change()
Run "george.xla!setHarry", TextBox1
End Sub
I then made a copy of Alice.xls as Bob.xls and ran them both. As
expected, if either workbook changes Harry, both workbooks see the result.

If this approach will work for you and you don't need to set the
variable value from VBA you could try using a constant and assigning its
value in the xla file and removing the set... function.

Private Const harry as string = "abcde12345"

http://stackoverflow.com/questions/3...iple-workbooks

If you create a variable which is globally accessible it can only have
one value at any given time. If you need local values then you need
local declarations....

Tim

On Sep 29, 4:11*am, Poniente wrote:
Hi,
I'd like to declare a variable once in an referenced xla file, in a
way that all the individual subs in the referring workbook can give it
a local value.
Is this possible?

Regards, Poniente