Re: Public variables and separate library files

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Tom Ogilvy (twogilvy_at_msn.com)
Date: 09/28/04 

Date: Tue, 28 Sep 2004 07:05:06 -0400

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" <ra3@sbcglobal.net> wrote in message
news:3a1fe86a.0409272157.2411c159@posting.google.com...
> 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).

Relevant Pages

  • Re: Referencing Dynamic Range
    ... Sub copyrange() ... MsgBox ... How do I reference an ActiveCell? ... active cell instead of specifying a cell below? ...
    (microsoft.public.excel.programming)
  • RE: Referencing Dynamic Range
    ... Sub copyrange() ... MsgBox ... How do I reference an ActiveCell? ... active cell instead of specifying a cell below? ...
    (microsoft.public.excel.programming)
  • Re: How to refer to a class property while in that class. (Solved)
    ... MsgBox c1.Token ... Class dataProc ... Public Sub DoMe() ... in VB to reference a VB Form from within itself ...
    (microsoft.public.scripting.vbscript)
  • Re: VB6 With/End With - how to pass the object itself?
    ... there would be a call for every item that is replaced by the use of With/End ... waste time creating the temp. ... dispatch table, use a look-up to find the 'class' reference, and then ... converts it to 'excode', and one of the things that happens in excode ...
    (microsoft.public.vb.general.discussion)
  • Re: ACCESS97, try to relink reference to another MDB
    ... when you move your application to the second machine, the reference to ... Dim ref As Reference ... Set ref = Application.References ... Dim temp$, resp ...
    (comp.databases.ms-access)