variable = worksheet (=>expected identifier)

Question

First of all, sorry for my bad English

So I have a macro that sets the worksheets of my workbook as variables names to prevent having to debug alert when a user uses the workbook and changes the name or the position of a sheet.

For example, if my sheet's name is "BDD" when I code I will be referring to this sheet as Sheets("'BDD").xxxxxx or sheets(1).xxxxxxx then if a user changes the name of the sheet my macros will not work anymore

That's why in worbook_open I set:

WB00 = activeworkbook
WB00WS01 = first sheet
WB00WS02 = second sheet

on and on

Therefore, when I code I refer to it as WB00WS01.xxxx

So, my macro is:

    Sub initWB00()
        Set WB00 = ActiveWorkbook
        For Each W In WB00.Worksheets
            On Error Resume Next
            If W.CodeName = "Sheet1" Then
                Set WB00WS01 = W
            End If
            If W.CodeName = "Feuil2" Then
                Set WB00WS02 = W
            End If
            If W.CodeName = "Feuil3" Then
                Set WB00WS03 = W
            End If
            If W.CodeName = "Feuil4" Then
                Set WB00WS04 = W
            End If
            If W.CodeName = "Feuil5" Then
                Set WB00WS05 = W
            End If
            If W.CodeName = "Feuil6" Then
                Set WB00WS06 = W
            End If
            If W.CodeName = "Feuil7" Then
                Set WB00WS07 = W
            End If
            If W.CodeName = "Feuil8" Then
                Set WB00WS08 = W
            End If
            If W.CodeName = "Feuil9" Then
                Set WB00WS09 = W
            End If
            If W.CodeName = "Feuil10" Then
                Set WB00WS10 = W
            End If
        Next W
    End Sub

It works as it is, but as you can see, it's pretty limited (if I want to go up to WB00WS100 for example) and it's very trivial

Then, I had the idea to loop a variable "i" that will give the number of the sheet "WB00WS" & i = "sheet" & I

Sub initWB00()
Dim i As Integer
    Set WB00 = ActiveWorkbook
    For Each W In WB00.Worksheets
        For i = 1 To 20
        If W.CodeName = "Feuil" & i Then
            Set "WB00WS0"&i = W
        End If
        Next i
    Next W
End Sub

If I do it like that I have the expected identifier error

I assume it's because I've put a string and a variable to the left of equality but otherwise I can't imagine how can I write a loop that makes what I need to

Do you have any idea?

Thanks in advance !!

Answer 1

Soooooooooooooooo Thanks to @FaneDuru I've found a solution ! I just had to remove the Global appelation at the start of my code (I had Global WB00WS01, Global WB00WS2...) and it worked !

My only problem left is that somewhere in my code I Set WB00 as ThisWorkbook and i cant refer to it later in my code. Even when I Dim WB00 as Workbook in the header of my code. When I use WB00.Windows(1).VisibleRange(2, 2).Top i have an undefined variable error

Anyway, thanks to all of you for helping me out !!

Answer 2

Please, try using of the next approach. You will run the code once and all the sheets code names will be changed. Then, you must only use them:

Sub initWB00()
    Dim i As Long, w As Worksheet, WB00 As Workbook
    
    Set WB00 = ActiveWorkbook 'if the active workbook is not ThisWorkbook, it will change the names of he active workbook sheets.
                              'but they can be used only running codes inside them
                              'In order to not have such problems, you should use ThisWorkbook instead of ActiveWorkbook 
    For Each w In WB00.Worksheets
        For i = 1 To 20
            If w.CodeName = "Feuil" & i Then
                ChangeCodeName w, WB00, "WB00WS0" & i
            End If
        Next i
    Next w
End Sub

Private Sub ChangeCodeName(sh As Worksheet, WB As Workbook, strCodeName As String)
  Dim shCModule As Object
  Set shCModule = WB.VBProject.VBComponents(sh.CodeName)
  shCModule.Name = strCodeName
End Sub

Answer 3

This is one method for populating an array with references to worksheets and emulates the attempts you have made in your code. However, the point made by Raymond Wu is possibly the more correct solution.

Option Explicit

Public Function initWB00(ByRef ipWB As Workbook) As Variant
    
    Dim myWorksheetArray As Variant
    ReDim myworksheet(1 To ipWB.Worksheets.Count)
    
    Dim myItem As Variant
    Dim mySheet As Worksheet
    For Each myItem In ipWB.Worksheets
    
        ' The next line just allow intellisense
        Set mySheet = myItem
        If InStr(mySheet.CodeName, "Sheet1") Then
        
            Set myWorksheetArray(1) = mySheet
            
        Else
        
            Set myWorksheetArray(CLng(Split(mySheet.CodeName, "l")(1))) = mySheet
        
        End If
        
    Next
    
    initWB00 = myWorksheetArray
    
End Function