vba - 运行循环以禁用控件时 Excel 崩溃

Question

Public Sub OptionsDisable()

    Dim myControls As CommandBarControls
    Dim ctl As CommandBarControl
    Dim iArray(21, 3181, 292, 3125, 855, 1576, 293, 541, 3183, 294, 542, 886, 887, 883, 884) As Long
    Dim myElement As Variant

    For Each myElement In iArray
        Set myControls = CommandBars.FindControls _
            (Type:=msoControlButton, ID:=myElement)
        If Not myControls Is Nothing Then
            For Each ctl In myControls
                ctl.Enabled = False
            Next ctl
        End If
    Next
End Sub

大家好，当我运行这个子程序时，Excel 就崩溃了。我试图通过一个循环来禁用数组中的每个控件 id。我在想正在发生的事情是它正在进入一个无限循环，但是我在第一行用 for 语句设置了一个断点，它仍然崩溃，在它到达那里之前。所以，我的另一个猜测是我的数组和/或变体定义有问题。

有人有想法吗？

附言运行此代码将使您的 Excel 崩溃。

Answer 1

试试这个代码:

Public Sub OptionsDisable1()

    Dim myControls As CommandBarControls
    Dim ctl As CommandBarControl
    Dim iArray As Variant
    Dim myElement As Variant

    iArray = Array(21, 3181, 292, 3125, 855, 1576, 293, 541, 3183, 294, 542, 886, 887, 883, 884)

    For Each myElement In iArray
        Set myControls = CommandBars.FindControls _
            (Type:=msoControlButton, ID:=myElement)
        If Not myControls Is Nothing Then
            For Each ctl In myControls
                ctl.Enabled = False
            Next ctl
        End If
    Next
End Sub

当您使用 Dim iArray(21, 3181, 292, 3125, 855, 1576, 293, 541, 3183, 294, 542, 886, 887, 883, 884) As Long Excel 不会像您期望的那样使用值初始化数组，而是尝试创建具有 15 个维度的数组