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

Hi.  I didn't think this would work.

Maybe it's new with Excel 2016?   I don't know.

Sub Demo()

    Dim S As String

    S = "shtAccounts"

    ' Just go to the first sheet

    Sheets(1).Activate

    ' Now go to the desired sheet

    ThisWorkbook.VBProject.VBComponents(S).Activate

    ' Do Stuff

End Sub

Answer 3

Sub Test()

  Dim Ws As Worksheet

  For Each Ws In Worksheets

    If Ws.CodeName = "shtAccounts" Then

      Ws.Range("A1") = "This is shtAccounts"

    End If

  Next

End Sub

Answer 4

Yeah, I know it is the same object.

But say I have a list of sheets I want to operate on. I want to use the code name, because the name changes depending on the user language. (Accounts in english, comptes in french).

The code name is shtAccounts

If I want a function to run on a list of codenames, I'd like to do this:

say Range A1:A2 contains shtAccounts and shtPolicies

function dosomething

   for each item in Range("A1:A2")

        sheets(item.value).color = blue

  next

end function

This doesn't work because item.value contains "shtAccounts" but the only acceptable values for sheets is "comptes" (or "Accounts" on an english pc)

Answer 5

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

Till the first user deletes the sheet and copies a new one from a different file. .-)

At this point, you can throw your code into the next garbage can.

Anyway, it doesn't matter how you call the object, a sheet is a sheet. Means

Sheets(1).Select

Sheets("Sheet1").Select

Sheet1.Select

is the same (object). And you can use any variable you like as usual, assign the object with SET

dim Ws as WorkSheet

set Ws = Worksheets("Whatever")

set Ws = Sheet1

Andreas.