"sub too long" error ? what can i do ?

**teacher_rob** · 11-19-2011, 08:48 PM

So i've spent all day working on my user interface, thanks to alot of help from you guys, and just a few minutes ago i got the "sub too long" error... i'm not sure if there is an easier way to do what i'm trying to do with my userform, but here's the situation...

teacher selects name from combobox1, which then populates a list of classes in combobox 2. the selection of combo box 2 highlights the frames on the userform corresponding to the students in the class... there are 12 frames in total...

hoping to make my userform as user-error-free as i can, i coded it such that there is code for each frame (and the corresponding 9 option buttons and labels within) to either stay default color, or turn completely red...

so there is alot of code going on... it does exactly as i intend it to, but now i'm getting a compile error only about 70% of the way through all the classes...

any thoughts ? i could post some code or pix of the userform if it helps.

thanks so much everyone !

-rob

**solnajeff** · 11-19-2011, 09:49 PM

Hi

The limit for procedures in Excel is 64k lines of code and above that you will get a compile error.

Without seeing any code it is difficult to comment, but is it necessary to code each item individually, could you not loop through them and apply the formatting?

Regards

Jeff

**foxguy** · 11-19-2011, 10:26 PM

I can't imagine any code that can't be broken up into smaller subs.
Inside your long sub you should be able to find a stretch of code that can stand by itself. Just put all that code into a separate sub and call it from your main sub.

Sub Sub_1()
    'Line 1
    'Line 2
    'etc.
    'Line 39
    If <sometest> then
        'Line 41
        'Line 42
        'etc.
        'Line 87
    Else
        'Line 89
        'etc.
        'line 107
    End If
    'More Lines
End Sub

Convert to:

Sub Sub_1()
    'Line 1
    'Line 2
    'etc.
    'Line 39
    If <sometest> then
        Sub_2
    Else
        Sub_3
    End If
    'More Lines
End Sub
Sub Sub_2
    'Line 41
    'Line 42
    'etc.
    'Line 87
End Sub
Sub Sub_3
    'Line 89
    'etc.
    'line 107
End Sub

You'll probably need to pass parameters or make public variables or make Sub_2 and Sub_3 into functions. But it cant be done.