VBA Procedure too large

[kidwispa]

Hi all,

I currently have a piece of code that runs fine on my pc (Office 2010, Windows 7 OS), however when my boss tries to execute the code from his pc he gets a "Procedure too large" message appear. He also uses Office 2010 but has a different OS (XP Professional 5.1 SP3)

2 questions:

1. Is this why he is getting this error?
2. What can I do to solve this?

Any help would be greatly appreciated!!!

 

[Hermanito]

A single module should never be above 64k. You can try to export your module and check in Explorer how big it is.
Also, there is a limit on the number of lines a single procedure can hold (I think). How many lines does that procedure of yours have?
You should try to cut your code into smaller pieces and functions spread over different modules, don't put all code in one big procedure in one module...

Why this occurs for your boss on XP and not for you on 7, I cannot tell... no idea.

 

[Ruddles]

The 64k limit is on the 'p-code' - the compiled code - rather than the source code, so checking the size of the exported file won't be much help, I'm afraid.

However I agree with Hermanito's suggestion that your module should be subdivided into smaller modules. This is a good idea in any case as it makes your project more manageable.

I've written projects consisting of thousands of lines of code and I've never encountered this problem, but then I tend to place groups of related subroutines and functions in separate modules.

 

[kidwispa]

The 64k limit is on the 'p-code' - the compiled code - rather than the source code, so checking the size of the exported file won't be much help, I'm afraid.

However I agree with Hermanito's suggestion that your module should be subdivided into smaller modules. This is a good idea in any case as it makes your project more manageable.

I've written projects consisting of thousands of lines of code and I've never encountered this problem, but then I tend to place groups of related subroutines and functions in separate modules.

Thanks for the suggestions, however I'm still relatively new to VBA and wouldn't even know where to begin splitting this code, as I pulled it together with the help of the users on here!!!

The code in question is about 1,000 lines long and is shown in the "UserForm5 (code)" bit (so not in a separate module).... what it does is pull results into 109 different textboxes on a userform based on selection made in a combobox at the top of the form. With 5 different criteria in the combobox (Quarter 1,2,3,4 & Total Year) that means there is 545 different results to consider. Added to this I have conditional formatting on 6 of the textboxes (grand totals) and it makes for some quite lengthy code...

There is a lot of replication within the code - for example

criteria1 = ComboBox2.Text
If criteria1 = "1" Then
If TextBox11.Text = "Agent1" Then
TextBox20.Text = Worksheets("QtrData").Range("G6").Text
TextBox27.Text = Worksheets("QtrData").Range("K6").Text
TextBox31.Text = Worksheets("QtrData").Range("P6").Text
TextBox35.Text = Worksheets("QtrData").Range("T6").Text
TextBox39.Text = Worksheets("QtrData").Range("Z6").Text
TextBox43.Text = Worksheets("QtrData").Range("AC6").Text

This is then repeated for each agent (18 in total), then the whole lot is repeated for each quarter and again for total year... I'm sure there is a more efficient way of doing this but I'm not sure what it is...

I hope this makes sense

Thanks again for your help

 

[Ruddles]

Replicated code is one of the first candidates for optimisation.

For example:-

TextBox20.Text = Worksheets("QtrData").Range("G6").Text
TextBox27.Text = Worksheets("QtrData").Range("K6").Text
TextBox31.Text = Worksheets("QtrData").Range("P6").Text
TextBox35.Text = Worksheets("QtrData").Range("T6").Text
TextBox39.Text = Worksheets("QtrData").Range("Z6").Text
TextBox43.Text = Worksheets("QtrData").Range("AC6").Text

can be removed en bloc to a separate procedure in a separate module.

Alternatively it could be replaced by:-

Call SheetToBox("G6",20)
Call SheetToBox("K6",27)
...etc

and a separate module would contain:-

Public Sub BoxToSheet(ByVal aRange As String, ByVal aBox As Integer)
 
    [COLOR=blue]UserForm1.Controls("TextBox" & aBox).Text = Worksheets("QtrData").Range(aRange).Text[/COLOR]
 
End Sub

"G6" and 20 are called 'parameters'. When you do Call SheetToBox("G6",20), aRange becomes "G6", aBox becomes 20, and the bit in blue above becomes:-

    [COLOR=blue]UserForm1.Controls("TextBox20").Text = Worksheets("QtrData").Range("G6").Text[/COLOR]

which is what you're trying to achieve.

Okay, you don't save much in your original coding but it allows you to put the SheetToBox subroutine in a separate module, and if you apply this technique to other areas of repetitive code, your p-code will soon start shrinking in size and you may find that your source code becomes more readable.

My next two evenings are fairly busy but if you still need help then, I'll be happy to look over your code over the weekend. I'll PM you my home email address.

 

[kidwispa]

Thahnks so much for your help Ruddles - I'll attempt your suggestions tomorrow morning when I'm back in the office....

 

[Hermanito]

Ruddles: about the 64k limit, are you sure it's the compiled size?

Because I have worked on a big VBA project where I had one particularly 'busy' userform with lots of controls (multipages within multipages etcetera) and eventhandlers. I noticed that if the exported filesize exceeded 64k, I ran into all kinds of very weird stuff... So, as long as I kept that behemoth of a userform under the 64k limit when exported, I was safe.

Is there any way to find out the compiled size of a module or userform?

 

[Ruddles]

Ruddles: about the 64k limit, are you sure it's the compiled size?

There certainly is a 64k limit on the p-code (packed binary code) - see http://support.microsoft.com/kb/112860 (search for Module Code Segment). This article states VB3: I can't find anything newer or anything relating specifically to VBA, but I'm quite happy to be corrected.

I suppose the question is: is there a similar limit on the source code? I can't find any reference to this but that's not to say there isn't one.

For reference, I've just checked a module containing 980 lines of code and it weighs in at 36k.

Is there any way to find out the compiled size of a module or userform?

Not as far as I'm aware... I guess if you compiled it using a standalone version of VB or VB.Net, you might be able to check the size of the p-code (which I think is termed the 'object module', although it's a while since I did any 'proper' VB programming).

 

[Hermanito]

Hmm, I see...
I did not yet have the error that the OP had, I tend to break my code up in pretty small chunks (that is: pretty-small chunks, not pretty small-chunks ) over lots of modules.

 

[kidwispa]

Hi again,

I've been through the code and replaced the code as suggested in Ruddles' earlier post using the SheetToBox function. With this in mind, I was wondering how I would move the following code (starting from the first "IF TextBox...) into a separate module in a similar fashion...

If TextBox167.Text = "  GRAND TOTAL" Then
Call SheetToBox("G21", 165)
If TextBox165.Text <> "-" Then
If TextBox165.Text <> "" Then
If TextBox165.Text <= 1 Then TextBox165.BackColor = RGB(0, 255, 0)
If TextBox165.Text <= 1 Then TextBox165.ForeColor = RGB(0, 0, 0)
If TextBox165.Text > 1 Then TextBox165.BackColor = RGB(255, 0, 0)
If TextBox165.Text > 1 Then TextBox165.ForeColor = RGB(0, 0, 0)
End If
End If
If TextBox165.Text = "-" Or TextBox165.Text = "" Then TextBox165.BackColor = RGB(128, 128, 128)
If TextBox165.Text = "-" Or TextBox165.Text = "" Then TextBox165.ForeColor = RGB(0, 0, 0)

The formatting is only used on a few of the textboxes (TBs 166,165,164,163,162 & 161)

If I could move this I think this might shorten my code enough for it to work on my boss's pc...

Thanks again for all the help from Ruddles and Hermanito... really appreciate you taking the time out to help me...