VBA Codenames

Numerous Excel courses, blog articles and tutorials published online advocate the exclusive use of sheet CodeNames in VBA. However, as demonstrated in this article, Codenames have numerous pitfalls, which are surprisingly overlooked by its avid supporters.

What Are Sheet Codenames

Sheet (document) modules have a property called CodeName, which is how the object is know internally to VBA. Indeed, if this feature is used by the developer, VBA code can always refer to a sheet (worksheet or chart), even if the sheet is renamed from Excel by a user. For example:

A worksheet is named 'Sales-2012'
Sheet CodeName is 'Sales'
User renames the worksheet to 'Sales-2013'

Sheets("Sales-2012").Activate

The above code will fail with a runtime error, because
there is no longer a sheet named 'Sales-2012'.

Sales.Activate

However, the above code will continue to work, because VBA still knows that worksheet by its 'Sales' CodeName

Codename Pitfall: Sheet Deleted

Now, let's see what happens, to an Excel application (tested up-to 2013 version), if a worksheet CodeName is used in VBA and the worksheet is deleted from Excel by the user or by VBA code:

Worksheet deleted from Excel by user: Compile Error - VBA code won't run at all !!
Worksheet deleted by code, while is running: Excel will crash !!!

VBA Compilation Error
VBA code won't run, if a sheet is deleted by the user, while is referenced by CodeName in VBA

Excel stops working
Excel will crash, if a worksheet is deleted by code, while VBA is running

Codename Pitfall: How To Rename A Sheet Codename?

Wrong:
ThisWorkbook.Sheets(strOldName).CodeName = strNewName
The sheet CodeName property is Read-only. So, the code above is invalid.

Correct:
ThisWorkbook.VBProject.VBComponents(Sheets(strOldName).CodeName).Name = strNewName
However, the VBE should be not be locked.

Renaming a Sheet CodeName fails, if the VBE project is protected!

Reference to a library cannot be added, if a sheet codename with the same name already exists in the workbook. For example, if a sheet codename is named as 'Scripting', reference to 'Microsoft Scripting Runtime' cannot be added.

Life is rarely black and white and neither VBA is so. Using CodeNames in VBA is not for the casual user. Codenames problems summarized:

CodeNames can make your code immune to sheet renaming, but could crash Excel or stop VBA dead in its tracks, if a sheet is deleted.
Sheet CodeNames cannot be renamed inside locked projects!
The codename of a newly created sheet cannot be used, until the project is re-compiled.

CodeNames are not a straightforward replacement for the lack of error-handling in VBA.

Proper error handling methods can address issues, such as sheet renaming or deletion by users. For more information on how we implement error-handling in Excel, please read our article here.
Security techniques in Excel can prevent users from renaming or deleting sheets. Please read our articles about Excel protection & passwords here.

Function to test if an Excel sheet codename exists

Function SheetCodeNameExists(oWB As Workbook, sCodeName As String) As Boolean
     
    Dim oSht As Object
     
    For Each oSht In oWB.Sheets
        If oSht.CodeName = sCodeName Then
            SheetCodeNameExists = True
            Exit For
        End If
    Next
     
End Function
 
Sub Test()
 
    Debug.Print SheetCodeNameExists(ActiveWorkbook, "Sheet1")
    Debug.Print SheetCodeNameExists(ActiveWorkbook, "Sheet0")
 
End Sub

Function to reference a sheet codename in any other workbook

Option Explicit
 
Function GetSheetFromCodeName(oWB As Workbook, sCodename As String) As Object
 
    Dim oSht As Object
 
    For Each oSht In oWB.Sheets
         
        If oSht.CodeName = sCodename Then
            Set GetSheetFromCodeName = oSht
            Exit For
        End If
         
    Next oSht
     
End Function
 
Sub Test()
 
    Dim oSht As Object
 
    Set oSht = GetSheetFromCodeName(ActiveWorkbook, "Sheet3")
     
    If Not oSht Is Nothing Then
        '....
    End If
 
End Sub

VBA Sheet Codenames Explained

What Are Sheet Codenames

Codename Pitfall: Sheet Deleted

Codename Pitfall: How To Rename A Sheet Codename?