VBA Loop Through Array of Shape Names-CodePudding

I am trying to loop through an array list of shapes, but I keep getting the error "Subscript out of range" when the function, ShapeExists is encountered. The code runs fine if I execute the ShapeExists function, by itself. What am I doing wrong?

 Sub Workbook_Open() 
    Debug.Print "Begin Workbook_Open sub." 
    Dim ws As Worksheet 
    Dim wsCount As Integer 
    Dim shapeList As Variant 
    Dim element As Variant 
     
    CheckFirstRun = True 
     
    wsCount = Sheets.Count 
    Debug.Print "Number of Sheets: " & wsCount 

    shapeList = Array("exportData", "InitializeData")

        Debug.Print "Making sure the Initilize and Export buttons are hidden." 
        For i = 1 To wsCount 
            For Each element In shapeList 
                Debug.Print "Working to hide shape " & element 
                If ShapeExists(element) = True Then 
                    Debug.Print element & " exists. Working to hide." 
                    HideShapes (element) 
                End If 
            Next element 
            'Sheets(i).Shapes("exportData").Visible = False 
            'Sheets(i).Shapes("InitializeData").Visible = False 
        Next i  
    Debug.Print "End Workbook_Open sub." 
End Sub 
  
Sub HideShapes(shapeName As Variant) 
    Sheets(i).Shapes(shapeName).Visible = False 
End Sub 
  
Function ShapeExists(shapeName As Variant) As Boolean 
    Dim sh As Shape 
    For Each sh In Sheets(i).Shapes 
        If sh.Name = shapeName Then ShapeExists = True 
        Debug.Print ShapeExists 
    Next sh 
End Function

CodePudding user response：

Note that while you can add a Global variable i, it's generally considered bad practice and it would be better to change your subroutines to add a parameter so you can pass in the sheet number as well as the shape name:

Sub HideShapes(wsNumber As Long, shapeName As Variant) 
    ThisWorkBook.WorkSheets(wsNumber).Shapes(shapeName).Visible = False 
End Sub

Then call like:

HideShapes i, element 'no parentheses!

Also note it's good to always be explicit about which workbook you mean to affect (even though it's not strictly necessary in the ThisWorkbook code module)

Likewise Sheets vs Worksheets - the first also includes Chart sheets if present.

Here's a slightly different approach:

Sub Workbook_Open()
    Dim ws As Worksheet, wb As Worksheet
    Dim shapeList As Variant
    Dim element As Variant
     
    Debug.Print "Begin Workbook_Open sub."
    'CheckFirstRun = True
    shapeList = Array("exportData", "InitializeData")
    
    For Each ws In ThisWorkbook.Worksheets 'easier loop
        For Each element In shapeList
            On Error Resume Next  'ignore error if no matching shape
            ws.Shapes(element).Visible = False
            On Error GoTo 0       'stop ignoring errors
        Next element
    Next ws
    Debug.Print "End Workbook_Open sub."
End Sub