Using sheet codename as a variable

Question

Sheets have two names: codename and name

e.g.  Sheet1 (Accounts)

You can refer to this as

   Sheets("Accounts")

or Sheets(1)

or Sheet1

The first two methods are unreliable as the tab name or order may change.

The third method can be counted on, since it isn't affected by changes, but

it can't be referred to variably, at least, not such that I can figure out.

 Calling Sheets("Sheet1") will not work.

How can I reference the code name if I want to, say, do an operation on a set of sheets?

Answer 1

Hi Paul,

See if this suits:

Sub Test()

  Dim Sh As Worksheet, Wb As Workbook

  Dim NewSh As Worksheet

  Dim CodeName As String

  CodeName = "MainSheet"

  Set Wb = ThisWorkbook

  Set Sh = ShByCodename(Wb, CodeName)

  ' Add new sheet with the required CodeName

  If Sh Is Nothing Then

    Set Sh = Wb.Sheets.Add

    ' Set the codename. Note: step by step debugging does not work here!

    Sh.[_CodeName] = CodeName

    'Set Sh = ShByCodename(Wb, CodeName)

  End If

  Debug.Print Sh.Name, Sh.CodeName

End Sub

Function ShByCodename(Wb As Workbook, CodeName As String) As WorkSheet

  Dim Sh As Object

  For Each Sh In Wb.WorkSheets

    If StrComp(CodeName, Sh.CodeName) = 0 Then

      Set ShByCodename = Sh

      Exit Function

    End If

  Next

End Function

Here is also another version of  ShByCodename  function with caching of all previously searched sheets:

Function ShByCodename(Wb As Workbook, CodeName As String, Optional ResetCache As Boolean) As WorkSheet

' Return sheet object via its string codename, or Nothing if not found.

'   For speed up reason, sheets are cached in the static dictionary object.

' Arguments:

'   Wb - workbook object

'   CodeName - string codename of the sheet

'   [ResetCache] - optional flag to reset all previously cached sheet objects

  Dim cn As String, Sh As Object

  Static Dic As Object

  ' Create dictionary object only at first call of this function

  If Dic Is Nothing Then

    Set Dic = CreateObject("Scripting.Dictionary")

    Dic.CompareMode = 1

  End If

  ' Main

  With Dic

    ' Reset the cache if ResetCache = True

    If ResetCache And .Count Then .RemoveAll

    ' See if the sheet is already in the dictionary

    If .Exists(CodeName) Then

      If Not .Item(CodeName) Is Nothing Then

        ' Return the found sheet object 

        Set ShByCodename = .Item(CodeName)

        Exit Function

      End If

    End If

    ' Create cache of sheets by their codename

    For Each Sh In Wb.WorkSheets

      cn = Sh.CodeName

      Set .Item(Sh.CodeName) = Sh

      If StrComp(cn, CodeName) = 0 Then

        ' Return the found sheet object

        Set ShByCodename = Sh

        ' Comment the next line to cahce all sheets of Wb

        Exit Function

      End If

    Next

  End With

End Function

You may also get properties or call methods like this:

ShByCodename(ActiveWorkbook, CodeName).Range("A1").Value = "Hi!"

Answer 2

Excellent! Glad it's working well for you :)

Answer 3

Super.

I'll have to spend some more time on your dictionary function to get my head around it. But the first part of your answer was exactly what I was looking for.

Thanks for your help Vlad.

For completeness, here are two techniques I made using your example.

My spreadsheet has two tabs in it Codenames: Sheet1 and MyCodeName

This first example uses the function that returns the sheet asked for by codename, then does something with it:

Sub Test2()

Dim Sh As Worksheet, Wb As Workbook

  Set Wb = ThisWorkbook

                      'Just a sample of two calls. Would normally generalize to looping through a list.

  Set Sh = ShByCodename(Wb, "MyCodeName")

  MsgBox Sh.Name

  Set Sh = ShByCodename(Wb, "Sheet1")

  MsgBox Sh.Name

End Sub

Function ShByCodename(Wb As Workbook, CodeName As String) As Worksheet

Dim Sh As Object

' This function returns the Worksheet codenamed CodeName

  For Each Sh In Wb.Worksheets

    If StrComp(CodeName, Sh.CodeName) = 0 Then

      Set ShByCodename = Sh

      Exit Function

    End If

  Next

End Function

The second example calls a sub that does something to the sheet with a specific codename:

Sub Test3()

    ActivateCodeName "MyCodeName"

    ActivateCodeName "Sheet1"

End Sub

Sub ActivateCodeName(CodeName As String)

Dim Sh As Object

' This sub activates the worksheet codenamed CodeName

    For Each Sh In ThisWorkbook.Worksheets

        If StrComp(CodeName, Sh.CodeName) = 0 Then

            Sh.Activate

            MsgBox Sh.Name

            Exit Sub

        End If

    Next

End Sub

Note in Test3 I removed the Wb argument and went directly with ThisWorkbook.

(I'd have coloured the code above, but for some reason the editors "Format" drop down is not functioning.)

Answer 4

Thanks Doug,

But I was actually looking at referencing by codename, not by name.

Codename is the underlying name (by default Sheet1, Sheet2, etc.).

Name is the label that appears on the tab itself.

Name is changeable, codename is not changeable (unless access is granted to the vba module.

I am looking for a way to say sheets("sheet1").method instead if sheet1.method

Answer 5

I just noticed tht I had "Trust access to the vba" option turned on on this pc.

The any other workaround is to search for the sheet that has a codename and return the regular name.

Even if you do not like the answer, it is the truth. :-)

Andreas.

Option Explicit

Sub Main()

  Dim SName As String

  SName = SheetCodeName("shtAccounts")

  If SName = "" Then

    'Doesn't exists

  Else

    Sheets(SName).Select

  End If

End Sub

Function SheetCodeName(ByVal Codename As String, _

    Optional ByVal Wb As Workbook = Nothing) As String

  'Return the name of the sheet which has this code name

  Dim Ws As Worksheet

  If Ws Is Nothing Then Set Wb = ActiveWorkbook

  For Each Ws In Wb.Sheets

    If StrComp(Ws.Codename, Codename, vbTextCompare) = 0 Then

      SheetCodeName = Ws.Name

      Exit Function

    End If

  Next

End Function