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!!!

 

[Ruddles]

This isn't too tricky to do but I notice in the code you posted you've started three If..Then blocks but only closed two of them. I'm reluctant to offer you any code changes if I can't match up the starts and ends of the blocks.

Check your code:-

[COLOR=magenta][B]If TextBox167.Text = "  GRAND TOTAL" Then
[/B][/COLOR]Call SheetToBox("G21", 165)
[COLOR=red][B]If TextBox165.Text <> "-" Then
[/B][/COLOR][COLOR=blue][B]If TextBox165.Text <> "" Then
[/B][/COLOR]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)
[COLOR=blue][B]End If
[/B][/COLOR][COLOR=red][B]End If
[/B][/COLOR]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)

If you can confirm where the missing End If should go, I'll let you have some replacement code.

 

[kidwispa]

This isn't too tricky to do but I notice in the code you posted you've started three If..Then blocks but only closed two of them. I'm reluctant to offer you any code changes if I can't match up the starts and ends of the blocks.

Check your code:-

[COLOR=magenta][B]If TextBox167.Text = "  GRAND TOTAL" Then[/B][/COLOR]
Call SheetToBox("G21", 165)
[COLOR=red][B]If TextBox165.Text <> "-" Then[/B][/COLOR]
[COLOR=blue][B]If TextBox165.Text <> "" Then[/B][/COLOR]
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)
[COLOR=blue][B]End If[/B][/COLOR]
[COLOR=red][B]End If[/B][/COLOR]
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)

If you can confirm where the missing End If should go, I'll let you have some replacement code.

Hi Ruddles,

Thanks again for getting back to me. The reason an End If is missing is because this part of the code (from the "Call SheetToBox line) is then replicated for each of the other text boxes mentioned in my previous post, and then the End If is added at the end. So I guess for this particular example, the extra End If would go at the end...

 

[Ruddles]

Oay, let's imagine for the sake of this exercise that the first If TextBox167.Text = " GRAND TOTAL" Then and the Call SheetToBox("G21", 165) statements aren't included in this discussion.

Option 1: move all of that code into a separate module, wrap it in a pair of Public Sub DoBox165() and End Sub lines, and then where you removed the code put in Call DoBox165. Repeat for other boxes as required.

Option 2 (neater, in my opinion): if the processing is the same for all those text boxes, you could make the code more general purpose, like so:-

[FONT=Fixedsys]Sub SetTextBox(ByVal [COLOR=red]aBox[/COLOR] As Integer)[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  If UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text <> "-" Then
    If UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text <> "" Then
      If UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text <= 1 Then UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).BackColor = RGB(0, 255, 0)
      If UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text <= 1 Then UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).ForeColor = RGB(0, 0, 0)
      If UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text > 1 Then UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).BackColor = RGB(255, 0, 0)
      If UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text > 1 Then UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).ForeColor = RGB(0, 0, 0)
    End If
  End If
  If UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text = "-" Or UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text = "" Then UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).BackColor = RGB(128, 128, 128)
  If UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text = "-" Or UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).Text = "" Then UserForm1.Controls("TextBox" & [COLOR=red]aBox[/COLOR]).ForeColor = RGB(0, 0, 0)[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]End Sub
[/FONT]

Then in place of the original code you would do Call SetTextBox(165). To process TextBox165, etc. You can name the actual Sub whatever you like as long as you Call it by its correct name. I've highlighted the code where the text box number is passed in and used just for the purpose of clarity (hopefully).

In fact that code can be made slightly neater (I think) by using a With..End With block:-

[FONT=Fixedsys]Sub SetTextBox(ByVal aBox As Integer)[/FONT]
[FONT=Fixedsys][/FONT] 
[FONT=Fixedsys]  With UserForm1.Controls("TextBox" & aBox)
    If [COLOR=red].[/COLOR]Text <> "-" Then
      If [COLOR=red].[/COLOR]Text <> "" Then
        If [COLOR=red].[/COLOR]Text <= 1 Then [COLOR=red].[/COLOR]BackColor = RGB(0, 255, 0)
        If .Text <= 1 Then [COLOR=red].[/COLOR]ForeColor = RGB(0, 0, 0)
        If [COLOR=red].[/COLOR]Text > 1 Then [COLOR=red].[/COLOR]BackColor = RGB(255, 0, 0)
        If [COLOR=red].[/COLOR]Text > 1 Then [COLOR=red].[/COLOR]ForeColor = RGB(0, 0, 0)
      End If
    End If
    If [COLOR=red].[/COLOR]Text = "-" Or [COLOR=red].[/COLOR]Text = "" Then [COLOR=red].[/COLOR]BackColor = RGB(128, 128, 128)
    If [COLOR=red].[/COLOR]Text = "-" Or [COLOR=red].[/COLOR]Text = "" Then [COLOR=red].[/COLOR]ForeColor = RGB(0, 0, 0)
  End With[/FONT]

[FONT=Fixedsys]End Sub[/FONT]

Again, I've highlighted in red the dots which mean "use the object specified in the With clause to reference this property".

Once again I'd recommend you make these changes to a copy of your workbook - in fact make uniquely named 'security copies' ever 10-15 minutes or so, so that you can roll back to a known working version when you make a mistake which stops everything functioning correctly. This happens to everyone sooner or later!

 

[RoryA]

I'd probably pass the control itself as the argument.

 

[kidwispa]

Thanks a million Ruddles, I've done exactly what you've suggested and it works a treat!!!

 

[stam]

this error is very annoying. i guess i have no choice but to break it up into smaller pieces.