2

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?

Improve this question
0

2 Answers 2

Reset to default
4

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

Improve this answer
Sign up to request clarification or add additional context in comments.

5 Comments

That requires authorizing VBIDE API to run though, no?
Yup it does. Let me mention that.
Excellent, this works perfectly, thanks. FYI, I have come across a similar solution by Mike Erikson, but using the index property: set ws = wb.Sheets(wb.VBProject.VBComponents(myVar).Properties("Index")) where wb is the set workbook and myVar is the variable codename
@jblack Yes that is another way to do it but the index property is very unreliable in this case unless there is only one sheet in the workbook. What if the sheets move? OR What if a sheet is hidden? What I mean is if there are 5 sheets and the 3rd sheet is hidden then the fourth sheet appears to be the 3rd sheet which obviously is incorrect :)
@SiddharthRout it seems that it works with index even when the sheet order is changed or the sheet is hidden. This being because the worksheet object is set, all references are made to the object,so if it moves then it's properties are updated accordingly. I've had a play with the scenarios you mentioned and it seems to pass all tests, even pausing the macro midway and changing sheet order updates the object properties :)
3

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.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.