我正在嘗試遍歷形狀的陣列串列，但是當遇到函式 ShapeExists 時，我不斷收到錯誤“下標超出范圍”。如果我自己執行 ShapeExists 函式，代碼運行良好。我究竟做錯了什么？
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
uj5u.com熱心網友回復：
請注意，雖然您可以添加 Global variable i，但通常認為這是不好的做法，最好更改子例程以添加引數，這樣您就可以傳入圖紙編號和形狀名稱：
Sub HideShapes(wsNumber As Long, shapeName As Variant)
ThisWorkBook.WorkSheets(wsNumber).Shapes(shapeName).Visible = False
End Sub
然后像這樣呼叫：
HideShapes i, element 'no parentheses!
另請注意，最好始終明確說明您要影響哪個作業簿（即使在 ThisWorkbook 代碼模塊中并非絕對必要）
同樣Sheetsvs Worksheets- 第一個還包括圖表表（如果存在）。
這是一種略有不同的方法：
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
轉載請註明出處，本文鏈接：https://www.uj5u.com/yidong/433013.html
下一篇：For回圈創建串列