Compile error when referring to sheet using codename

Question

This post https://stackoverflow.com/a/65020762/4928763 describes referring to a worksheet using its codename like this...

With Sheet1   
    .[a1] = Sheet1.Name
End With

and... .CodeName as Sheet3 in Sheet3.Range("A1")

so I would expect to be able to activate a sheet using...

With Sheet1   
    .activate
End With

or even just using...

Sheet3.activate

but when trying this I get a compile error: Variable not defined What am I doing wrong?

Just to be clear - In my case the sheet has Sheet.Name = "Monthly Sales" and Sheet.Codename = "wsMonthlySales" and in the project exporer the name is displayed as wsMonthlySales (Monthly Sales)

I can activate the sheet using Sheets("Monthly Sales").Activate but I would like to use something like wsMonthlySales.Activate as descrived above but get "Variable not defined" error when trying to compile

Code is in a .xlam file... Worksheets are in a different .xlsm file Is that what you asking? — Gavin, Jul 04 '23 at 14:23
That's your problem, codenames are effectively variables scoped local to their workbook. [see here](https://stackoverflow.com/questions/61354314/using-sheet-codename-with-workbook) — CDP1802, Jul 04 '23 at 14:31
Oh!... oops. Thank you so much - I have been reading so much about this and have never seen that before. Now I see comments in the links you provided saying to just not use codenames altogether - advice taken :) — Gavin, Jul 04 '23 at 14:48
Using codenames is useful if it's in the same workbook as your code. It can be good insurance against people changing the tab names... — Tim Williams, Jul 04 '23 at 15:51

VBasic2008 · Answer 1 · 2023-07-04T17:17:15.427

Use the Code Name to Reference a Worksheet in Another Workbook

An Example (Utilization)

Sub RefSheetByCodeNameTEST()

    ' Define constants.
 
    Const WORKBOOK_NAME As String = "Monthly.xlsx"
    Const WORKSHEET_CODE_NAME As String = "wsMonthlySales"

    ' Reference the workbook.

    Dim wb As Workbook:
    On Error Resume Next
        Set wb = Workbooks(WORKBOOK_NAME)
    On Error GoTo 0
    
    If wb Is Nothing Then
        MsgBox "Workbook """ & WORKBOOK_NAME & """ is not open.", _
            vbCritical
        Exit Sub
    End If
    
    ' Reference the worksheet.
    
    Dim ws As Worksheet: ' or use 'wsMonthlySales' instead of 'ws'
    Set ws = RefWorkSheetByCodeName(wb, WORKSHEET_CODE_NAME)
    
    If ws Is Nothing Then
        MsgBox "Worksheet with the code name """ & WORKSHEET_CODE_NAME _
            & """ not found.", vbCritical
        Exit Sub
    End If
        
    ' The worksheet is referenced. Continue using 'ws' with e.g.:
        
    MsgBox "The tab name of the worksheet is """ & ws.Name & """.", _
        vbInformation

End Sub

The Function (Worksheets)

Function RefWorkSheetByCodeName( _
    ByVal wb As Workbook, _
    ByVal WorkSheetCodeName As String) _
As Worksheet
    Dim ws As Worksheet
    For Each ws In wb.Worksheets
        If StrComp(ws.CodeName, WorkSheetCodeName, vbTextCompare) = 0 Then
            Set RefWorkSheetByCodeName = ws
            Exit For
        End If
    Next ws
End Function

The Function (Sheets (Including Charts))

Function RefSheetByCodeName( _
    ByVal wb As Workbook, _
    ByVal SheetCodeName As String) _
As Object
    Dim sh As Object
    For Each sh In wb.Sheets
        If StrComp(sh.CodeName, SheetCodeName, vbTextCompare) = 0 Then
            Set RefSheetByCodeName = sh
            Exit For
        End If
    Next sh
End Function

score 0 · Answer 2 · answered Jul 04 '23 at 17:11

Codename is a String. But there is also a class object with the same name, which inherits from Worksheet. Suppose we have a workbook named ANSWERS.xlsm that has Project name: "VBAProjectAns". Suppose this workbook has a sheet named Sheet1 and Codename "SHEET01". We want to use the codename SHEET01 through another book, even OTHERBOOK.xlsm which must have a different Project name, even: "VBAProjectOther". So the only way I know is the following: We open both books, go to OTHERBOOK.xlsm and from VB in References we select by checking VBAProjectAns. Then in the code we can refer to SHEET01 as follows:

' code in OTHERBOOK.xlsm
Sub Test()
    VBAProjectAns.SHEET01.Activate
End Sub

We store the books and close them. If you try to close ANSWERS.xlsm first, it won't let you with a message that it is in use, so you close OTHERBOOK.xlsm first. If you now open OTHERBOOK.xlsm again, ANSWERS.xlsm will automatically open as well. So you can somehow use the Codename of the sheets of another book, as long as you "marry" the books.

Compile error when referring to sheet using codename

2 Answers2

Use the Code Name to Reference a Worksheet in Another Workbook