Menu
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
828
Excel VBA Range Object For example, the range property in VBA is used to refer to specific rows or columns while writing the code. The code “Range(“A1:A5”). Value=2” returns the number 2 in the range A1:A5. In VBA, macros are recorded.
The Parent property of an object returns the parent object of the object. For example, the parent of a Range object is a Worksheet object. The parent of a Worksheet object is a Workbook object. The parent of a Workbook is the Application object.
If the Excel VBA Range object you want to refer to is a single cell, the syntax is simply “Range(“Cell”)”. For example, if you want to make reference to a single cell, such as A1, type “Range(“A1″)”.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
