Can you get the workbook name that a range refers to in VBA?

[TomCon]

I had a VBA debugging problem that i finally solved with difficulty.

Along the way, i wanted to find out what workbook a Range object was referring to, but it seems that Range does not have a Workbook property. So, i wanted to verify that, and ask if there is any way i could have gotten at the name of the workbook that the Range object refers to.

For instance if i have a loop

For Each cell In Range(rngaddr)...

When stopped in debugging, in the immediate window i can try this
?cell.address
$L$6
?cell.worksheet.name
daily
?cell.workbook.name
[Property does not exist!]

My code problem was that cell object was apparently referring to the same cell of a different [open] workbook than i thought it was referring to (same sheet name exists in another open workbook). it took me a while to figure out, though.

If this property does not exist, do you know a way i could find out what workbook a Range object is referring to?

Thanks!

 

[shg]

cell.address(external:=True)
cell.worksheet.parent.name

 

[Fluff]

Or even

Cell.Parent.Parent.Name

 

[DataBlake]

I'm assuming that both workbooks have the same range name?
otherwise it will try to access that range in whichever workbook is active
you can use

Workbooks("Examples.xlsx").Activate

or even specify the workbook as a range with

Dim wb As Workbook
set wb = ThisWorkbook

that will save the current workbook as a variable
and then you can put the name of the current workbook in a cell or something by making it a string

Edit: i think i misread

 

[TomCon]

Thank you. Yes, that does it! Would have saved me alot of time to know that sooner, as i suspected that problem but it took me a long time to solve it in the face of uncertainty.

 

[Fluff]

@BlakeSkate
Please do not quote entire posts. It just clutters up the thread.

 

[TomCon]

It was indeed a context issue. When calling one Sub to another, the Range object gets the workbook context in the caller, not by setting active window in the called Sub.

I called with this.
Windows("D4p-Enter.xlsx").Activate
Call copy_fmla(Range("daily!L6"))

Sub Copy_fmla is defined thus
Sub copy_fmla(rng As Range)

Within copy_fmla I have this
Windows("D4_p_TRK.xlsm").Activate
Sheets(shtnam).Select
i = 0
For Each cell In rng

So, it turns out that the Activate statement still does not change the context of the object rng. I wanted the range to refer to D4_p_TRK.

This now makes sense to me...but at the time I did not realize it. So, thanks for all the comments!