Public variables and separate library files
 

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 ("Befo" & 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…
Befo0
Within:
After:0
Befo1
Within:
After:1
Befo2
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).

Public variables and separate library files
 

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.

-----Original Message-----
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 ("Befo" & 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.
Befo0
Within:
After:0
Befo1
Within:
After:1
Befo2
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).
.

Public variables and separate library files
 

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.

--
Regards,
Tom Ogilvy

"Richard" wrote in message
om...
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 ("Befo" & 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.
Befo0
Within:
After:0
Befo1
Within:
After:1
Befo2
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).