CODEWITHSUNDEEP
excelvba
Charitoo
Charitoo

Reputation: 1872

How to refer to the workbook where a function is used in Excel VBA?

I have created a user-defined function called SheetAtIndex that returns the name of the sheet at the specified index in a workbook.

At first I used ActiveWorkbook.Sheets(Index).Name to get the name of the sheet. Anytime I switch to another open workbook the results became messed up. No surprise there.

ThisWorkbook.Sheets(Index).Name worked for some time till I realized I have to duplicate the code in every workbook that needs the UDF. In addition every file that needs the function must be saved as a macro-enable workbook

Final decision: Put the function in my personal macro book.

Problem: How to reference the workbook/worksheet where the function will be called from?

Upvotes: 5

Views: 1128

Answers (1)

chris neilsen
chris neilsen

Reputation: 53135

A UDF can get a reference to the cell that called it with Application.ThisCell

Once you have a reference to the calling cell, you can get the worksheet (.WorkSheet) and workbook (.WorkSheet.Parent) from there

Function SheetAtIndex(Index As Variant) As String
    SheetAtIndex = Application.ThisCell.Worksheet.Parent.Worksheets(Index).Name
End Function

Or, to get a reference to the Worksheet

Function SheetAtIndex(Index As Variant) As Worksheet
    Set SheetAtIndex = Application.ThisCell.Worksheet.Parent.Worksheets(Index)
End Function

Upvotes: 6

Related Questions