Problem loading userform with frames

[Dan Hatola]

I added several new controls to a userform and found that there was a
noticable increase in the time it took to load. I removed the controls one
at a time and was able to isolate the problem to two frames on the userform.
I did some additional testing with a new userform on which I only attached
frames. When I put only one frame on the userform it loaded in 15
milliseconds while two frames took 140-165 milliseconds. I know this isn't a
long delay, but it is annoying that the form does not load "instantaneously."
From a performance perspective, two frames was approximately 8-10 times
slower than one. I tested other controls and each additional control had
almost no impact on the load time of the form. Has anyone else experienced a
similar situation with frames? Is there a way to avoid/correct this? Any
help would be greatly appreciated.

Dan

 

[Robin Hammond]

Dan,

Try Rob Bovey's code cleaner utility on your project. Once forms start
getting complex, it is a huge help.

 

[Dan Hatola]

Thanks. I gave the code cleaner a try, but unfortunately nothing changed.
Even if I only have 2 frames and no other controls, the user form loads
slower. I might try to test it on other computers as well as other version
of Excel. I am running 2002 on the computer I used to create the user form.

Dan

 

[Peter T]

Indeed loading a form with even one frame takes relatively a long time, try
this

'normal Module
Public Declare Function GetTickCount Lib "kernel32.dll" () As Long

Sub test()
Dim i&, t&
Dim frm As UserForm

' test with empty form to get base time
' then only with some buttons
' then only with some frames

t = GetTickCount
For i = 1 To 50
Set frm = UserForm1
Load frm ' redundant
Unload frm
Set frm = Nothing
Next

Debug.Print GetTickCount - t

End Sub

For me, two buttons in addition to time loading an empty form was trivial,
but two frames a long time.
FWIW, 3 x longer than with only one frame.

Regards,
Peter T

 

[Dan Hatola]

Thanks for following up. I guess there is just an issue with frames and load
time of the user form.

Dan

 

[NickHK]

Dan,
Frames, along with a only couple of other standard controls, expose a
windows handle. Most others do not, they are under Excel's control.
Not sure if this is involved in the behaviour that you see, but some testing
with various controls and Spy++ may give you some insight.

NickHK