PDA

View Full Version : Creating buttons is very slow



QuinRiva
07-09-2008, 12:09 AM
I have this very simlpe code to create a series of buttons:
Sub AddButton(x, y, ID)
If i < 10 Then
cname = "ViewCard0" & ID
Else
cname = "ViewCard" & ID
End If
With ActiveSheet.Buttons.Add(x * 15, y * 7.5, 60, 22.5)
.OnAction = "cmdViewCards"
.Name = cname
.Caption = Colours(ID - 1)
End With
End Sub

NB: Colours() is just a public array

I execute this code as part of a loop by simply
Call AddButton(x, y, ID)

The loop loops 12 times and creates 12 buttons. With this line commented out, it takes about 3.7 seconds to execute the loop (which is understandable, because the loop imports and resizes about 75 200kb images). But when I remove the comment and add the buttons as well, the execution times scyrockets to upwards of 17 seconds.

That would seem to indicate that adding each button takes a full '1 second'. What's going on here?

Oorang
07-09-2008, 08:11 AM
A lot of Excel Speed Issues are caused by all of the things Excel does automatically for you behind the scenes. Try running "InterfaceOff" (see below) at the start of your code, and "InterfaceOn" at the end of your code. This method will substantially speed up most Excel VBA.

Public Sub InterfaceOff()
On Error Resume Next
With Excel.Application
.Calculation = xlCalculationManual
.EnableEvents = False
.EnableSound = False
.Cursor = xlWait
.StatusBar = "Working..."
.ScreenUpdating = False
.DisplayAlerts = False
End With
End Sub

Private Sub InterfaceOn()
On Error Resume Next
With Excel.Application
.DisplayAlerts = True
.EnableEvents = True
.EnableSound = True
.Cursor = xlDefault
.Calculation = xlCalculationAutomatic
.ScreenUpdating = True
.StatusBar = False
End With
End Sub