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.
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.
You can do this by adding the second Workbook as a reference, and calling the workbook by the VBA Project name.
Obviously, it's a good idea to change the VBA project name from the default 'VBAProject'!
I tested this by creating two new workbooks each with one sheet, with a value of 1 or 2 in cell A1. The VBA projects were named 'Proj1' and 'Proj2', and the worksheets' CodeNames were left as 'Sheet1'.
This is the code I used:
Sub test()
Debug.Print Proj1.Sheet1.Cells(1, 1)
Debug.Print Proj2.Sheet1.Cells(1, 1)
End Sub
Yielding an output of:
1
2
Worksheets can be referred to by their codename when the code is in the same workbook so fully qualifying is not necessary. You can't refer to another workbooks sheet by codename directly unless you loop each sheet and check the codename
so this is enough without needing to create a variable
with WsSummary
.Cells(1,1).Value = "Test"
end with
Dim wb as Workbook
Dim ws as worksheet
Set wb = "Your Workbook full path and name"
For each ws in wb.worksheets
If ws.codename = "Your Codename" then exit for
next ws
ws will now contain the reference to the worksheet in other workbook with the desired codename with no user changeable dependencies
Hope this helps
