[vba] Excel tab sheet names vs. Visual Basic sheet names

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?

Using the sheet codename was the answer I needed too to stop a series of macros falling over - ccampj's answer above mirrors this solution (with screen pics)

I have had to resort to this, but this has issues with upkeep.

Function sheet_match(rng As Range) As String  ' Converts Excel TAB names to the required VSB Sheetx names.
  TABname = rng.Worksheet.Name                ' Excel sheet TAB name, not VSB Sheetx name. Thanks, Bill Gates.
' Next, match this Excel sheet TAB name to the VSB Sheetx name:
   Select Case TABname 'sheet_match
      Case Is = "Sheet1": sheet_match = "Sheet1"  ' You supply these relationships
      Case Is = "Sheet2": sheet_match = "Sheet2"
      Case Is = "TABnamed": sheet_match = "Sheet3" 'Re-named TAB
      Case Is = "Sheet4": sheet_match = "Sheet4"
      Case Is = "Sheet5": sheet_match = "Sheet5"
      Case Is = "Sheet6": sheet_match = "Sheet6"
      Case Is = "Sheet7": sheet_match = "Sheet7"
      Case Is = "Sheet8": sheet_match = "Sheet8"
   End Select
End Function

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

There are (at least) two different ways to get to theWorksheet object

• via the Sheets or Worksheets collections as referenced by DanM
• by unqualified object names

When a new workbook with three worksheets is created there will exist four objects which you can access via unqualified names: ThisWorkbook; Sheet1; Sheet2; Sheet3. This lets you write things like this:

Sheet1.Range("A1").Value = "foo"

Although this may seem like a useful shortcut, the problem comes when the worksheets are renamed. The unqualified object name remains as Sheet1 even if the worksheet is renamed to something totally different.

There is some logic to this because:

• worksheet names don't conform to the same rules as variable names
• you might accidentally mask an existing variable

For example (tested in Excel 2003), create a new Workbook with three worksheets. Create two modules. In one module declare this:

Public Sheet4 As Integer

In the other module put:

Sub main()

Sheet4 = 4

MsgBox Sheet4

End Sub

Run this and the message box should appear correctly.

Now add a fourth worksheet to the workbook which will create a Sheet4 object. Try running main again and this time you will get an "Object does not support this property or method" error

Perhaps I am wrong, but you can open a workbook, and select a worksheet and change its property (Name) to whatever you need it to be. This overrides the "Sheetx" naming convention. These names are also displayed in the VBA Editor.

How to do this manually: 1. Select the sheet in a workbook (I tend to create templates). 2. Set its tab name to whatever you like ("foo"). 3. Click on the Developer menu (which you previously enabled, right?). 4. Locate "Properties" and click on it, bringing up that worksheet's properties window. 5. The very first item in the Alphabetic listing is (Name) and at the right of (Name) is "Sheetx".
6. Click on that field and change it (how about we use "MyFav"). 7. Close the properties window. 8. Go to the Visual Basic editor. 9. Review the sheets in the workbook you just modified. 10. Observe that the MicroSoft Excel Objects shows the name you just changed "MyFav", and to the right of that, in parenthesis, the worksheet tab name ("foo").

You can change the .CodeName programmatically if you would rather. I use non-Sheet names to facilitate my template manipulation. You are not forced to use the generic default of "Sheetx".

You should be able to reference sheets by the user-supplied name. Are you sure you're referencing the correct Workbook? If you have more than one workbook open at the time you refer to a sheet, that could definitely cause the problem.

If this is the problem, using ActiveWorkbook (the currently active workbook) or ThisWorkbook (the workbook that contains the macro) should solve it.

For example,

Set someSheet = ActiveWorkbook.Sheets("Custom Sheet")

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.

This a very basic solution (maybe I'm missing the full point of the question). ActiveSheet.Name will RETURN the string of the current tab name (and will reflect any future changes by the user). I just call the active sheet, set the variable and then use it as the Worksheets' object. Here I'm retrieving data from a table to set up a report for a division. This macro will work on any sheet in my workbook that is formatted for the same filter (criteria and copytorange) - each division gets their own sheet and can alter the criteria and update using this single macro.

Dim currRPT As String
ActiveSheet.Select
currRPT = (ActiveSheet.Name)
Range("A6").Select
Selection.RemoveSubtotal
Selection.AutoFilter
Range("PipeData").AdvancedFilter Action:=xlFilterCopy, CriteriaRange:=Range _
    ("C1:D2"), CopyToRange:=Range("A6:L9"), Unique:=True
Worksheets(currRPT).AutoFilter.Sort.SortFields.Clear
Worksheets(currRPT).AutoFilter.Sort.SortFields.Add Key:= _
    Range("C7"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
    xlSortNormal

I think I may have an alternative solution. It's a little ugly, but it seems to work.

Function GetAnyNameValue(NameofName) As String
    Dim nm, ws, rng As String
    nm = ActiveWorkbook.Names(NameofName).Value
    ws = CStr(Split(nm, "!")(0))
    ws = Replace(ws, "'", "")
    ws = Replace(ws, "=", "")
    rng = CStr(Split(nm, "!")(1))
    GetAnyNameValue = CStr(Worksheets(ws).Range(rng).Value)
End Function