Using Codename In A Loop - How To Refer?

[Lewzerrrr]

Hey,

So I've started to pick up on using codenames when writing scripts, majority of them I can pull them off or learn the workaround but in my example.. say I have 10 sheets, all codenames are sheet1, sheet2, sheet3 etc.. why does this code not work when referring? Why does it not pick up as Sheet2.Delete?

If anyone can give me guidance on codenames e.g. is it the preferred way, is it the most efficient, what are the cons to using vs sheet name and index other than it can pinpoint a sheet more accurately where sheet names and indexs can change constantly.

**Just to note, I do not want to refer by their index OR tab name.

Sub test()

Application.ScreenUpdating = False

For I = 2 To 10
  Sheet & I.Delete
Next

Application.ScreenUpdating = True

End Sub

 

[RoryA]

You can't do that simply because you cannot create variable names (which is essentially what a code name is) by concatenating two strings together. You could use an array of the actual code names and loop through that or, if you have trusted access to the VBProject, you can loop through the VBComponents using the code names as strings.

 

[Lewzerrrr]

You can't do that simply because you cannot create variable names (which is essentially what a code name is) by concatenating two strings together. You could use an array of the actual code names and loop through that or, if you have trusted access to the VBProject, you can loop through the VBComponents using the code names as strings.

Thanks, so something like

For Each sh in Sheets(Array(Sheet1.Name, Sheet2.Name, Sheet3.Name, Sheet4.Name))
'do something here
Next Sh

Which leads me to another challenge, if that full array doesn't exist e.g. Sheet2 is there but Sheet1 and Sheet3 isn't then you get a 424 Object Required error.. what's the work around on that?

 

[Kyle123]

Maybe:

Dim shtNames As Variant: shtNames = Array("Sheet1", "Sheet2", "Sheet3")

For Each ws In ThisWorkbook.Worksheets
    If UBound(Filter(shtNames, ws.CodeName)) >= 1 Then
        Debug.Print ws.CodeName
    End If
Next ws

 

[RoryA]

If the sheet might not be there, you probably shouldn't be using code names in all honesty.