Refernece another workbook

[Dryver14]

When you reference another workbook and add it to the references, is that reference then available to all excel workbooks or just the one you referenced from?

 

[JLGWhiz]

When you reference another workbook and add it to the references, is that reference then available to all excel workbooks or just the one you referenced from?

The question is very ambiguous since it does not specify whether the reference is in Excel formulas or VBA code and does not difine how it is referenced, whether by object variable or by name.

 

[Dryver14]

No worries the context is that I am starting to learn VBA from a book for a change, I have reached the creating formulas stage and it is suggesting that if the workbook that contains the formula is referenced then it doesn't need to be open to access the formula.

As most of what I do is at home I should think placing a formula module in my personal workbook would suffice but should I do a works program then I am trying to understand referencing

 

[JLGWhiz]

No worries the context is that I am starting to learn VBA from a book for a change, I have reached the creating formulas stage and it is suggesting that if the workbook that contains the formula is referenced then it doesn't need to be open to access the formula.

As most of what I do is at home I should think placing a formula module in my personal workbook would suffice but should I do a works program then I am trying to understand referencing

In a formula, If your workbook name is 'Data', you would reference it like: [Data.xlsx]Sheets1!A1
Workbook = "Data.xlsx"
Worksheet = "Sheet1"
Range = "A1"
In VBA you could Dim wb As Workbook, sh As Worksheet, rng As Range

Set wb = Workbooks("Data")
Set sh = wb.Sheets("Sheet1")
Set rng = sh.Range("A1")

You can now use wb anywhere in the code of that procedure to mean Workbooks("Data")
You can use sh anywhere in the code of that procedure to mean Workbooks("Data").Sheets("Sheet1")
You can use rng anywhere in the code of that procedure to mean Workbooks("Data").Sheets("Sheet1").Range("A1")

I don't know if I have ansered your question because I really don't understand what you are trying to determine. But maybe it helped.

 

[Dryver14]

Any information helps, I think I answered my own question I was actually referring to the reference Library, adding the workbook to the reference library so the function is accessible without opening the book.

My belief is that all workbooks on the pc refer to the same library and so any workbooks would have access to that function without specifically having the code open the workbook to reference it.

Looking back at my question I can see why the ambiguity, sorry

 

[JLGWhiz]

Any information helps, I think I answered my own question I was actually referring to the reference Library, adding the workbook to the reference library so the function is accessible without opening the book.

My belief is that all workbooks on the pc refer to the same library and so any workbooks would have access to that function without specifically having the code open the workbook to reference it.

Looking back at my question I can see why the ambiguity, sorry

I think you might be doing what most of us did when we first began to take Excel seriously and that is to confuse the references to Excel and Windows Application (the underlying software that makes it work) with the products of the Applications like Workbooks, Arrays, UserForms, etc. It takes a while to get it straight but once you do, you can do a lot of stuff in a very short period of time that had previously taken hours and even days to do. Good luck.

Regards, JLG