Can you get the workbook name that a range refers to in VBA?
Results 1 to 7 of 7

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

  1. #1
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

  2. #2
    MrExcel MVP shg's Avatar
    Join Date
    May 2008
    Location
    The Great State of Texas
    Posts
    21,603
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    14 Thread(s)

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

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

  3. #3
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

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

    Or even
    Code:
    Cell.Parent.Parent.Name
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  4. #4
    Board Regular BlakeSkate's Avatar
    Join Date
    Jan 2015
    Location
    Pernsylvoonia
    Posts
    453
    Post Thanks / Like
    Mentioned
    5 Post(s)
    Tagged
    0 Thread(s)

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

    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
    Code:
    Workbooks("Examples.xlsx").Activate
    or even specify the workbook as a range with

    Code:
    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
    Last edited by BlakeSkate; Apr 29th, 2019 at 03:10 PM.
    -------------------------------------------------------------------------------
    I may not give the best VBA codes, but they sure are VBA codes.
    Help me help you by posting a snapshot of your data & your expected result
    Please use [ code][ /code] tags when posting VBA as well as proper indentation

  5. #5
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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.

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    27,029
    Post Thanks / Like
    Mentioned
    461 Post(s)
    Tagged
    46 Thread(s)

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

    @BlakeSkate
    Please do not quote entire posts. It just clutters up the thread.
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    Board Regular
    Join Date
    Mar 2011
    Posts
    231
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

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

    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!

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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