Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). . |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
public variables | Excel Discussion (Misc queries) | |||
Public variables | Excel Discussion (Misc queries) | |||
Public Variables | Excel Discussion (Misc queries) | |||
public but private variables | Excel Programming | |||
Public Variables with UserForms | Excel Programming |