PDA

View Full Version : Userform code module too big, how to reduce size



xltrader100
11-01-2012, 12:18 AM
With Excel 11, I have a userform whose code module has grown too large (280k), as evidenced by random crashes of Excel (Excel must close now…). I've done the usual code cleaning, removed everything from the module that I reasonably could, and everything left has a good claim for staying there, i.e. the code refers to controls and vars that are children of this same form. It wouldn't make sense to move this code off the form.

So, I'm looking for ideas on how I can lighten up the code load in this module. The form in question covers the full screen and is kind of the container where everything else happens, (hence the pile of code) so it's not really practical to try to split it up physically.

This must be a well trodden problem, so are there known ways to attack this? (aside from Don't do that)

snb
11-01-2012, 12:44 AM
Without you showing us any code at all that's a paradoxical question.

Using a class to handle events in the userform could be very beneficial to reducing code.

Experience shows that rereading code after a while may show possibilities to reduce code to 5% of what you started with.

dantzu
11-01-2012, 05:34 AM
Optimizing the code definitely helps a lot.

xltrader100
11-01-2012, 06:24 AM
I went through the code shrinkage process before coming here and the code has been tightened up until it squeaks. There are already 5 classes in the form that do indeed consolidate a bunch of code, plus all of the constants and most of the variables have been made public and moved into their own module, plus I've factored out every routine that's used more than once into a callable sub, but I"m still left with 280K in the form module. I just thought there might be other known ways to handle this, because my project is kind of dead in the water if Excel won't run it reliably any more.

Aflatoon
11-01-2012, 06:33 AM
You appear to have already done or ruled out every option I can think of. Why are you so sure that it's the size of the form module that is the cause of your random crashes? (I assume you are only considering the size of the frm file, not the frx)

snb
11-01-2012, 06:33 AM
It's hard to advice if you do not show some sample code.....

xltrader100
11-01-2012, 12:41 PM
The reason I'm focusing on the size of the module as the cause of the crashing is just a guess, but it's based on a lot of reports of a module size limit of 64K of compiled code. It's hard to pin this 64k number down to an authoritative source, and everybody just repeats this same mythical number. It's referred to as a "soft limit", and it must be very soft because my program has been running well for months now with a 250k form module, and only became erratic when the code got up to 280k.
http://www.dailydoseofexcel.com/archives/2004/10/13/module-size-limits/

Here are some other size limits I came across for VB6, but VBA should be similar.
http://msdn.microsoft.com/en-us/library/aa240819%28VS.60%29.aspx
http://msdn.microsoft.com/en-us/library/aa240865.aspx

Paul_Hossler
11-01-2012, 01:28 PM
FWIW, I also found that keeping ALL modules < 64KB is best

Do you really need all that code in the user from module?

Can you break out the 'processing' code from the UF code that responds to UF events, etc.?

So if there's a Button_Click, just have the event call a Sub in a standard module?

Paul

xltrader100
11-01-2012, 03:11 PM
I already have class modules that respond to (almost) all the events generated by controls on the UF, but the subs that get called to handle these events all live in the UF module because they all do stuff on the UF.

This is a very graphics oriented program with no worksheet formulas at all, and this one big fullscreen UF gets all the action.

While I'm still looking for other ideas, the next thing I'm going to try is to delete about 20 frames belonging to the big UF, and substitute a new UF for each frame. These new UF"s will run on their own code, and so get this code out of the big UF module. I'll probably add one more clsUserforms module to handle the events formerly handled by clsFrames.

Bob Phillips
11-01-2012, 05:22 PM
Just because they do stuff on the UF doesn't mean they HAVE to be in the UF, they could be in procedures in another module, you would just need to pass the UF object (Me) when calling them.

xltrader100
11-01-2012, 07:36 PM
I had considered that method early on, before I got into the problem and found out how serious it was, and I rejected it probably because it's something I usually go out of my way not to do. But with the lack of good alternatives, this method suddenly looks much more attractive, and I thank you for pointing it out. It would involve the least amount of work and have the best chance of success, so that will be my next try. If it works I'll call this solved.

Paul_Hossler
11-02-2012, 09:23 AM
You can also just pass the control or controls if you wanted to

Simple example

UF code:


Option Explicit
Private Sub TextBox1_Change()
Call Update_Label(Me.TextBox1, Me.Label1)
End Sub


Private Sub TextBox2_Change()
Call Update_Label(Me.TextBox2, Me.Label2)
End Sub


Private Sub TextBox3_Change()
Call Update_Label(Me.TextBox3, Me.Label1)
Call Update_Label(Me.TextBox3, Me.Label2)
End Sub



Standard module:


Option Explicit

Sub Update_Label(TB As msforms.TextBox, LAB As msforms.Label)
LAB.Caption = UCase(TB.Value)
End Sub


Paul

xltrader100
11-02-2012, 06:54 PM
Thanks, Paul. I found several places where that's exactly what's needed.