Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2003

    Unanswered: Global Variables for two workbooks

    Dear recipient:

    I use global variables in a module in VBA for excel.
    I need use same variables for other workbook.
    How declare this variables for use two workbooks?

  2. #2
    Join Date
    Jul 2003
    Plainsboro, NJ


    Workbooks and Projects can reference another Project or Workbook by first selecting the "Tools" menu in the Visual Basic Editor (VBE) and then Selecting "References...".

    For example, in the VBE you would

    (1) Select the Workbook (.xls) or Project (.xla) that needs access to another Project.

    (2) Click on the "Tools" menu and choose "References..."

    (3) In the list box, check off the name of the Project that contains the variables to which it needs access.

    (4) Then click <OK> and don't forget to save your Project/Workbook so the setting stays.

    But mind you, there are more complexities than this.

    (1) Normally you don't want to give public access to variables like this. Constants or User Defined Types, maybe, but for variables, it's not a great idea.

    If you need a Project or Module to report it's status (say Available/Offline, Initialized/UnInitialized, Count, or whatever) then it's probably better to make a a function that returns this status instead of having a variable that holds the value. This would pretty well mimick an object-oriented structure.

    If it's public Data (more than public "variables") then these values might be best placed in a .CSV file or .XLS file and accessed by the calling parties. Values can be changed while in session and then saved at the end of the session -- unlike a variable that simply resides in RAM and loses it's value once the session is over.

    (2) The Project or Workbook that contains the variables in question might be best put in the Excel Startup directory so that it is always available to the Workbooks that need access to it.

    This is not technically necessary, however, as opening a Workbook that is referencing another Workbook or Project will compel Excel to open the refereneced workbook/project automatically, if it can find it.

    Excel can find it if either (1) the referenced workbook/project has not moved since it was last referenced, or (2) the referencing and referenced workbooks/projects both (or all) reside in the same directory (even if they all moved since last use, they are together, so it is found automatically).

    This differs from formulas in Cells, referencing other Workbooks. Such "links" can be updated (and Excel will prompt you if you want to update, typically when the Workbook is opened) but the referenced workbook in question would not be opened. The data would be pulled into the referencing Workbook according to the data as of its last calculation.

    Sorry so wordy, I hope this helps!

    -- Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts