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

Dim myname As Worksheet

Set myname = Sheets.Add

myname.Name = "myname"

myname.Range("A1") = "The name of this sheet is myname."

Answer 3

Great!  Glad it helped.

Just to throw this out, the codenames are actual objects.  So, if the names can be hard-wired in code, then this general idea would work:

Sub Demo()

    MsgBox Sheet1.Name

    MsgBox Sheet2.Name

    MsgBox shtAccounts.Name

    shtAccounts.Range("A1") = "Hello"

End Sub

The line:

MsgBox shtAccounts.Name

Returned for me:  "Sheet3"

Answer 4

Dana, Sad news.

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

When I turn it off (which is the default), I get an error message on the first line.

Darned.

You'd think that access would be allowed for things that re just read only.

Answer 5

Thanks Dana.

That is exactly what I was looking for.  I just tried it and it works with Excel 2010.

Here is my sample. In the spreadsheet with two tabs (default codenames sheet1 and sheet2, but the tabs are renamed with other names):

Sub UseCodeName()

    ThisWorkbook.VBProject.VBComponents("Sheet1").Activate

    MsgBox ThisWorkbook.VBProject.VBComponents("Sheet1").Name

    With ThisWorkbook.VBProject.VBComponents("Sheet2")

        .Activate

        MsgBox .Name

    End With

End Sub