Public variables and separate library files

[Richard]

How does one make a variable available to other routines stored in a
separate library-type file? I thought using "Public", but in the
simple example below the variable "temp" is null within the called
subroutine…

Calling file…
Public temp As Integer
Sub Master()
Workbooks.Open ("\Temp\Library1.xls")
temp = 0
Again:
MsgBox ("Before:" & temp)
Application.Run "Library1.xls!TestSub"
MsgBox ("After:" & temp)
temp = temp + 1
GoTo Again
End Sub
File "\Temp\Library1.xls"…
Sub TestSub()
MsgBox ("Within:" & temp)
End Sub
Output produced…
Before:0
Within:
After:0
Before:1
Within:
After:1
Before:2
Within:
After:2
etc.

If I add a "Public" statement at the top of the Library1.xls module, I
get zeros instead of nulls within the called routine. (It works
correctly if I concatenate the two routines into the same file, but
this of course defeats the purpose of having separate libraries of
code).

 

[Guest]

hi,
not sure exactly what you're up to but you might use a
trick i have used on occasions. dump the variable into a
cell. then you can use the value in other routines. it
some files(not many) i have a dump zone.

 

[Tom Ogilvy]

You would need to create a reference from the file needing to use the
variable to the file containing the variable. If you do create the
reference, then if you open the file containing the reference, the file
referenced will also open and you can not close the file referenced until
the file with the reference is closed.

An alternative is to have a function in the file containing the variable
return the value of the variable, then call that function using
Application.Run. This would be a one way path, however.

Essentially, variables are local to the workbook in which they are
contained.