excel - 循环移动 Excel 文件中的按钮，不包括某些工作表

Question

我有一个由几张纸组成的 Excel 电子表格。在每张纸上我都有一个 Button 1 .
为了将此按钮移动到 Range("D9:E11")在所有工作表中，我使用以下 VBA 来引用解决方案 here :

Sub Sample()
    MoveButton Sheet1, "Button 1", True
End Sub

Sub MoveButton(sh As Worksheet, btnName As String, Optional AllSheets As Boolean)
    Dim Range_Position As Range
    Dim ws As Worksheet

    Set Range_Position = sh.Range("D9:E11")

    If AllSheets = True Then
        For Each ws In ThisWorkbook.Sheets
            With ws.Buttons(btnName)
                .Top = Range_Position.Top
                .Left = Range_Position.Left
                .Width = Range_Position.Width
                .Height = Range_Position.Height
                .Text = "Button"
            End With
        Next ws
    Else
        With sh.Buttons(btnName)
            .Top = Range_Position.Top
            .Left = Range_Position.Left
            .Width = Range_Position.Width
            .Height = Range_Position.Height
            .Text = "Button"
        End With
    End If
End Sub

到目前为止，这一切都完美无缺。

但是，现在可能会发生某些工作表(例如 Sheet3 和 Sheet5 )没有 Button 1 .
在这种情况下，我得到 runtime error 1004如果我使用上面的 VBA。
因此，我正在寻找方法来检查 Button 1存在于工作表中，如果不存在，则 VBA 应继续到下一个工作表。
在 With ws.Buttons(btnName) 行之前是这样的:

If Shapes("Button 1").Exists Then
Run VBA
Else
End If

我尝试以多种方式将上面的 VBA 代码与此函数结合起来，但目前无法使其工作。
你知道如何解决它吗？

Answer 1

它可以通过模仿“try..catch”构造来解决。在错误检查中使用总是不好的，但在 VBA 中没有那么多选项。

像这样的功能应该适合你:

Public Function isBtnExists(Optional ws As Worksheet = Nothing, Optional btnName As String = "Button 1") As Boolean
    If ws Is Nothing Then
        Set ws = ActiveSheet
    End If
   'turn off errors'
    On Error Resume Next
    Dim q As Object
    'trying to assign button to a variable, if it doesn't exist - error number will appear in global Err object' 
    Set q = ws.Buttons(btnName)
    'by checking the error we know exists button or not'
    isBtnExists = (Err.Number = 0)
    'dismiss "On Error Resume Next" not really needed here but in some cases VBA can behave weirdly, so it is better to keep it'
    On Error GoTo -1
End Function

在您的代码中将是这样的:

 For Each ws In ThisWorkbook.Sheets
      If isBtnExists(ws) Then
          With ws.Buttons(btnName)
              .Top = Range_Position.Top
              .Left = Range_Position.Left
              .Width = Range_Position.Width
              .Height = Range_Position.Height
              .Text = "Button"
          End With
      End If
  Next ws