vba - Excel crashes when running a loop to disable controls -

public sub optionsdisable()      dim mycontrols commandbarcontrols     dim ctl commandbarcontrol     dim iarray(21, 3181, 292, 3125, 855, 1576, 293, 541, 3183, 294, 542, 886, 887, 883, 884) long     dim myelement variant      each myelement in iarray         set mycontrols = commandbars.findcontrols _             (type:=msocontrolbutton, id:=myelement)         if not mycontrols nothing             each ctl in mycontrols                 ctl.enabled = false             next ctl         end if     next end sub 

okay everyone, when run subroutine, excel crashes. trying run through loop disable every control id in array. i'm thinking happening is entering infinite loop, set breakpoint on first line statement, , still crashes, before gets there. so, other guess it's problem array , or variant defining.

anyone have idea?

p.s. running code crash excel.

try this:

public sub optionsdisable()  dim mycontrols commandbarcontrols dim ctl commandbarcontrol dim iarray variant dim myelement variant  iarray = array(21, 3181, 292, 3125, 855, 1576, 293, 541, 3183, 294, 542, 886, 887, 883, 884)      each myelement in iarray         set mycontrols = commandbars.findcontrols _             (type:=msocontrolbutton, id:=myelement)         if not mycontrols nothing             each ctl in mycontrols                 ctl.enabled = false             next ctl         end if     next myelement  end sub 

when dim array this: iarray(5), you're not creating array single element 5. you're creating upper bound limit of items put inside array. when start doing iarray(x,y,z), you're confusing excel , asks create lot of dimensions insane upper bounds.

basically, creating arrays wrong. above should work. alternatives split should well. :)

let know if helps.