CODEWITHSUNDEEP
excelvba
jblack
jblack

Reputation: 576

Excel VBA - Indirectly reference a sheet by it's CodeName

Is there a way to indirectly reference a sheet by it's CodeName without looping through every sheet to test it's CodeName? For example,

Method 1: The following code does not work (would be ideal if it did though):

Dim ws As Worksheet

Set ws = "mySheet" & myVar

Method 2: But this works (if you hardcode the CodeName in):

Dim ws As Worksheet

Set ws = mySheetExample

Method 3: And this works (for a dynamic approach):

Dim ws As Worksheet

For Each sh In ThisWorkbook.Worksheets
    If sh.CodeName = "mySheet" & myVar Then
        'do something
    End If
Next

which is a really long winded way of doing things, which brings me to my question, why does the first method not work?

Upvotes: 2

Views: 2178

Answers (2)

Siddharth Rout
Siddharth Rout

Reputation: 149335

It will not work that way because you are trying to assign a string to a worksheet object. Select Case is the way to go. OR

Try this

Set ws = Sheets(ThisWorkbook.VBProject.VBComponents("mySheet" & myVar).Properties("Name").Value)

A more elaborate example

Option Explicit

Sub Sample()
    Dim ws As Worksheet
    Dim myVar As Long
    Dim shName As String

    myVar = 1

    shName = "Sheet" & myVar
    Set ws = Sheets(ThisWorkbook.VBProject.VBComponents(shName).Properties("Name").Value)

    Debug.Print ws.CodeName
End Sub

For this to work, enable "Trust access to the VBA project"

  1. Start Microsoft Excel.
  2. Open a workbook.
  3. Click File and then Options.
  4. In the navigation pane, select Trust Center.
  5. Click Trust Center Settings....
  6. In the navigation pane, select Macro Settings.
  7. Ensure that Trust access to the VBA project object model is checked.
  8. Click OK.

If you are not aware what Trust access to the VBA project object model is then you may read about it in Enable or disable macros in Office files

Upvotes: 4

Mathieu Guindon
Mathieu Guindon

Reputation: 71227

You're missing the point of the code name property.

The idea is that it gives you a compile-time, project-global programmatic identifier you can use whenever you need to refer to a particular specific worksheet in ThisWorkbook (the workbook hosting your VBA code).

  • Method 1 doesn't work because the expression "mySheet" & myVar evaluates to a String value, and you're trying to Set-assign that string into a Worksheet object reference. VBA can't read your mind here, it sees a string on the right-hand side, a Worksheet variable on the left-hand side, and has no idea what that means.

  • Method2 is making a locally-scoped copy of an identifier that is already project-global: it's completely redundant and uselessly confusing indirection, don't do that.

  • Method3 would be better implemented as a function:

    Public Function GetSheetByCodeName(ByVal value As String) As Worksheet
    Dim sheet As Worksheet
    For Each sheet In ThisWorkbook.Worksheets
        If sheet.CodeName = value Then
            Set GetSheetByCodeName = sheet
            Exit Function
        End If
    Next
    Err.Raise 9 '"subscript out of range", specified name was not found.
End Function

Still, taking something that's compile-time validated, and making it runtime-validated, feels backwards. It's a long-winded way of doing things, because you're supposed to be consuming that identifier as an.. identifier:

Sheet1.Range("SomeRange").Value = 42

Worksheets that exist in ThisWorkbook at compile-time, shouldn't need any kind of complicated logic to extract - they're right there.

Upvotes: 3

Related Questions