Excel crashes when running a loop to disable controls

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

Okay everyone, when I run this subroutine, Excel just crashes. I am trying to run through a loop to disable every control id in the array. I'm thinking what is happening is it is entering an infinite loop, but I set a breakpoint on the first line with a for statement, and it still crashes, before it gets to there. So, my other guess is it's a problem with my array and or variant defining.

Anyone have an idea?

P.S. Running this code will crash your Excel.

Answers


Try this:

Public Sub OptionsDisable()

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 myElement

End Sub

When you Dim an array like this: iArray(5), you're not creating an array with a single element 5. You're basically creating an upper bound limit of items to put inside the array. When you start doing iArray(x,y,z), you're confusing Excel and asks it to create a LOT of dimensions with insane upper bounds.

Basically, you were creating arrays wrong. The above should work. Alternatives like Split should as well. :)

Let us know if this helps.


Try this code:

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

When you're using Dim iArray(21, 3181, 292, 3125, 855, 1576, 293, 541, 3183, 294, 542, 886, 887, 883, 884) As Long Excel doesn't initialize array with values as you expect, but tries to create array with 15 dimmensions


Need Your Help

Pyqt docks get hidden when window minimized and restored

windows pyqt4 restore minimize dock

When I minimize the application window on Windows XP and restore it later, the dock will be hidden. This has to do with view menu which has toggles to set visibility and of course is connected by s...

split working for-loop calling JSON in 2 parts with jquery/javascript

javascript jquery html arrays json

I have a working loop for a JSON call which loads the data.root.offer[0].region call into a div with class .region within another div .carousel It looks like this: