Creating a collection or array of CommandButtons

[fabiomoraesmachado]

Hello fellows, this is my first post so even though I read the recommendations I am sorry if I make any posting mistakes.

I was assigned to optimize a very old Excel 2003 / VBA file. This is a huge file with literally thousands of controls (Labels, Textboxes, ListBoxes, CommandButtons, etc). It has the option to change the control's caption language from Spanish (Spain) and Spanish (LatinAmerica) with only a subset of texts/captions being actually changed. Today the code has a For Each <control> In UserformX.Controls</control> strategy to do it, but since there are so many controls it hangs almost 15 seconds before finishing the task. (I tested it and I am sure the hanging is caused by this piece of code)
I would like to create some sort of CommandButton collection or array in which I could loop through specifically instead of looping through all the controls.
My idea is to have something like:

For Each Combobox In Comboboxes
<do stuff="" here="">
Next Combobox

I don't mind it being a complex solution with class creation or whatever as long as it gets the job done much faster. I don't need a full code, just a sample code or a reference to the theme / solution.

Thanks in advance.

Friendly regards,
Fabio.</do>

 

[AFPathfinder]

This is just speculation, but I don't think Excel keeps a collection of specific control types, which is why you won't be able to loop through only a certain type of control without your own collection or dictionary. The collection that Excel keeps for controls is the OLEObjects, which contain all objects for a sheet.

I wouldn't think a loop through all objects to check for name or type would be that slow (even if thousands exist in the collection) unless changes are being made to many of them (especially to a workbook with formulas and/or triggered VBA events).

To test this out, just have a sub that loops through all controls and counts how many of a certain type there is. Since it wouldn't be causing any recalculations or events, that should give you a good idea if the number of controls is causing the wait.