CODEWITHSUNDEEP
vbaexcel
brietsparks
brietsparks

Reputation: 5016

VBA Excel: return the parent workbook of a range

How do I return the name of the Workbook that contains a given range?

Range.Parent gives the parent sheet.... is the parent workbook possible? Something to the effect of Range.ParentWorkbook

Upvotes: 5

Views: 18265

Answers (2)

nkatsar
nkatsar

Reputation: 1660

Coming late to the party, note that this can be also done in one line (tested in Excel 2010):

myRange.Worksheet.Parent.name

Macro Man's GetWorkbookName() function would be simply:

Private Function GetWorkbookName(myRange As Excel.Range) As String
    GetWorkbookName = myRange.Worksheet.Parent.name
End Function

example use is the same:

Sub Foo()
    Dim parentWorkbookName As String

    parentWorkbookName = GetWorkbookName(Range("A1"))
    MsgBox parentWorkbookName
End Sub

Upvotes: 6

SierraOscar
SierraOscar

Reputation: 17637

findwindow's comment as an example (post is community wiki - not taking credit for it)

You could create a UDF like so that takes a Range as an argument:

Private Function GetWorkbookName(myCell As Excel.Range) As String
    Dim a As Excel.Worksheet
    Set a = myCell.Parent
    GetWorkbookName = a.Parent.Name
End Function

example use:

Sub Foo()
    Dim parentWorkbookName As String

    parentWorkbookName = GetWorkbookName(Range("A1"))
    MsgBox parentWorkbookName
End Sub

Upvotes: 0

Related Questions