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
