Fully reference a worksheet by codename

Question

When I reference Excel worksheets using the CodeName property, how do I fully qualify them including the workbook reference?

I want to guard against a scenario with two different workbooks open and two sheets having the same CodeName. I use ThisWorkbook because I want to reference the workbook the code is running from.

I imagine something like this:

Dim tgWs As Worksheet
Set tgWs = ThisWorkbook.WsSummary
tgWs.Cells(1,1).Value = "Test"

where WsSummary is the sheet's codename.

Answer 1

Referring to a sheet by its codename always implies the sheet in ThisWorkbook, i.e. the workbook that contains the code you are executing.

There seems to be no straightforward way to fully qualify a sheet in a different workbook using its codename.

This function will help you do this:

Function GetSheetWithCodename(ByVal worksheetCodename As String, Optional wb As Workbook) As Worksheet
    Dim iSheet As Long
    If wb Is Nothing Then Set wb = ThisWorkbook ' mimics the default behaviour
    For iSheet = 1 To wb.Worksheets.Count
        If wb.Worksheets(iSheet).CodeName = worksheetCodename Then
            Set GetSheetWithCodename = wb.Worksheets(iSheet)
            Exit Function
        End If
    Next iSheet
End Function

Example usage:

GetSheetWithCodename("Sheet1", Workbooks("Book2")).Cells(1, 1) = "Sheet1 in Book2"
GetSheetWithCodename("Sheet1", ActiveWorkbook).Cells(1, 1) = "Sheet1 in ActiveWorkbook"
GetSheetWithCodename("Sheet1").Cells(1, 1) = "Sheet1 in ThisWorkbook" 

Note that the last line is equivalent to simply saying:

Sheet1.Cells(1, 1) = "Sheet1 in ThisWorkbook" 

because, as mentioned above, referring to a sheet by its codename always imply the sheet in ThisWorkbook.