Excel tab sheet names vs. Visual Basic sheet names

Question

It seems that Visual Basic can not reference sheets according to user-modified sheet names. The worksheet tabs can have their names changed, but it seems that Visual Basic still thinks of the worksheet names as Sheet1, etc., despite the workbook tab having been changed to something useful.

I have this:

TABname = rng.Worksheet.Name  ' Excel sheet TAB name, not VSB Sheetx name.

but I would like to use sheet names in Visual Basic routines. The best I could come up so far is to Select Case the Worksheet Tab vs. Visual Basic names, which doesn't make my day.

Visual Basic must know the Sheet1, Sheet2, etc., names. How can I get these associated with the Excel tab names so that I don't have to maintain a look-up table which changes with each new sheet or sheet tab re-naming?

Answer 1

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.

Answer 2

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.

Answer 3

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).