Difference between worksheet display names and code names in Excel (VBA)

Welcome to Logically Proven blog.

This post demonstrates the difference between  worksheet code names and worksheet display name. And also explains which name is better to use in the VBA (Visual Basic for Applications) code.

In an excel workbook, every worksheet has two names - display name and code name.

Display name:

Display name is the name visible to every user and every user is allowed to edit the display name of the worksheet by default (if workbook is not protected).

Code name:

Code name is the another name of a worksheet which is not visible to every user. The code name is visible only in the code window of workbook application. Press Alt+F11 to view the code names of worksheets in the code window.

The another way to open the code window is go to Developer tab in the excel ribbon, click on Visual Basic as shown in the picture below.

If you don't see the developer tab, follow this (Microsoft Excel 2010)- select excel file menu (office symbol on the top left) -> select Excel Options -> Select Popular Section -> check the button "Show Developer tab in the Ribbon" and press OK as shown in the picture below -

Once you press the Alt+F11, the following code window opens where you will find the code names of the worksheets.

To view the properties window press F4 or in the code window select View menu and click on Properties Window.

Then select any one of the sheets, you get all the properties of the sheet and you can edit the code name of the sheet in the (Name) column. This name is only visible in the code window but not to the every user.

Press Alt+F11 again to switch between code window and excel workbook.

Understand why it is important to use code names in your code?

Consider you are writing VBA code to perform some tasks with the worksheets in a workbook.
In your code you are referring to a worksheet with a display name as shown below. Consider the code name and display name of worksheet is Sheet1.

Sub test()
    
    ThisWorkbook.Sheets("Sheet1").Range("A1").Value = "Hello,"
    ThisWorkbook.Sheets("Sheet1").Range("B1").Value = "this"
    ThisWorkbook.Sheets("Sheet1").Range("C1").Value = "is a"
    ThisWorkbook.Sheets("Sheet1").Range("D1").Value = "test code."
    
End Sub

This code works very well until unless user is not changing the display name of a worksheet.

What happens if user changes the display name of a worksheet. You will get the following error message because the application is unable to find the display name of a worksheet. (considering without error handling for better explanation)

In this case, it is an extra work to search all your code and replace the display names of the sheets. This is a bad solution to replace because if user changes again the display name. Thus it is never ending process.

The permanent solution is not to use display names of a worksheet in the code. The optimal solution is to use code names or getting the index of a worksheet by searching the code names as shown below.

Using code names directly in the code:

Sub test()
    
    Sheet1.Range("A1").Value = "Hello,"
    Sheet1.Range("B1").Value = "this"
    Sheet1.Range("C1").Value = "is a"
    Sheet1.Range("D1").Value = "test code."
    
End Sub

This code works fine even the user renames the display name of a worksheet. But it is better not to refer the worksheet so many times. Unnecessarily the memory size of application grows. If you are using just one or two times in method/function it is okay. If you want to refer more times initialize an worksheet object as shown below.

Using index of a worksheet by searching code names:

Sub test()
    
    'declaring the variable of type worksheet
    Dim wksInput As Worksheet
         
    'initialize the object
    Set wksInput = ThisWorkbook.Sheets(GetWorksheetIndex("Sheet1"))
    
    'with keyword can be used as below
    With wksInput
        .Range("A1").Value = "Hello,"
        .Range("B1").Value = "this"
        .Range("C1").Value = "is a"
        .Range("D1").Value = "test code."
    End With
  
  'release the object before exit
  Set wksInput = Nothing
    
End Sub

Here we are getting the index of a worksheet by searching the code name of a worksheet "GetWorksheetIndex". And we are using worksheet object wksInput for speeding the application. Never forget to release the worksheet object if you don't require any more to speed up the application.

GetWorksheetIndex function:

Function GetWorksheetIndex(strSheetName As String) As Integer

    Dim intCounter As Integer
    Dim intIndex As Integer
    Dim wkb As Workbook

    On Error GoTo err_handler
    Set wkb = ThisWorkbook

    intIndex = 0
    
      'iterate through all worksheets in a workbook
       For intCounter = 1 To wkb.Worksheets.Count
          If UCase(wkb.Worksheets(intCounter).CodeName) = UCase(strSheetName) Then
             intIndex = intCounter
             Exit For
          End If
       Next intCounter

    GetWorksheetIndex = intIndex

endthis:
    Set wkb = Nothing
    Exit Function

err_handler:
    MsgBox Err.Description, vbOKOnly, "Error in GetWorksheetIndex"
    Resume endthis
End Function

Even user changes the display name of a worksheet, we never run into errors because we are using code name of a worksheet. In case if you want to change the code name of a worksheet, it is required to change the code names in the code else you will run into runtime errors. Please use error handlers for the smooth running of your application as shown in the GetWorksheetIndex function.


Please write your comments if you find anything is incorrect or do you want to share more information about the topic discussed above.


Logically Proven
Learn, Teach, Share

Karthik Byggari

Author & Editor

Computer Science graduate, Techie, Founder of logicallyproven, Love to Share and Read About pprogramming related things.