Declaring a local variable once for all subs

  • Thread starter Thread starter Poniente
  • Start date Start date
P

Poniente

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
 
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
Click to expand...

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/3777446/vba-global-variables-multiple-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
 
Back
Top