Excel tab sheet names vs. Visual Basic sheet names

In the Excel object model a Worksheet has 2 different name properties:

Worksheet.Name
Worksheet.CodeName

the Name property is read/write and contains the name that appears on the sheet tab. It is user and VBA changeable

the CodeName property is read-only

You can reference a particular sheet as Worksheets("Fred").Range("A1") where Fred is the .Name property or as Sheet1.Range("A1") where Sheet1 is the codename of the worksheet.

This will change all worksheet objects' names (from the perspective of the VBA editor) to match that of their sheet names (from the perspective of Excel):

Sub ZZ_Reset_Sheet_CodeNames()
'Changes the internal object name (codename) of each sheet to it's conventional name (based on it's sheet name)

    Dim varItem As Variant

    For Each varItem In ThisWorkbook.VBProject.VBComponents
        'Type 100 is a worksheet
        If varItem.Type = 100 And varItem.Name <> "ThisWorkbook" Then
            varItem.Name = varItem.Properties("Name").Value
        End If
    Next
End Sub

It is important to note that the object name (codename) "(Name)" is being overridden by the property name "Name", and so it must be referenced as a sub-property.

Actually "Sheet1" object / code name can be changed. In VBA, click on Sheet1 in Excel Objects list. In the properties window, you can change Sheet1 to say rng.

Then you can reference rng as a global object without having to create a variable first. So debug.print rng.name works just fine. No more Worksheets("rng").name.

Unlike the tab, the object name has same restrictions as other variables (i.e. no spaces).