Programming error - Procedure too large

  • Thread starter Thread starter NDBC
  • Start date Start date
N

NDBC

I have a private subroutine that has just started giving me this error when I
tried to run it. The only thig i changed was the format of the time variables
and it is not really that large. The debugger just highlights the sub and end
sub lines. What can cause this problem.
 
Sorry to waste your time. I just did a search and found out I need to break
it into smaller subs that call each other.

Thanks
 
It's VERY possible your code can be greatly streamlined. Care to show it to
us?
 
Don you are exactly right. I already know how to reduce it to 1/5 it's size
but that's not to say there isn't even more efficiencies to be gained. I have
5 text boxes in a form and at the moment I evaluate each box exactly the same
way but I have sections of code for each box when I could just have a loop
stepping by one.

The boxes are called rider1 through to rider 5. I am having trouble with
this code at the moment

If Rider5.Value < 100 Or IsNumeric(Rider5) = False Then

I have now got

For Box = 5 to 1 step -1

If "Rider" & box.Value < 100 Or IsNumeric("Rider" & box) = False Then

This does not work. I just thought maybe it needs to be in brackets.

Any ideas.
 
HAVE YOU tried something like this?

Option Explicit

Sub TextBoxTest()
Dim myShape As Excel.Shape
Dim aWS As Excel.Worksheet
Dim Box As Long

Set aWS = ActiveSheet

For Box = 5 To 1 Step -1
Set myShape = Nothing
On Error Resume Next
Set myShape = aWS.Shapes("Rider" & Box)

If Not myShape Is Nothing Then
'Do what you'd do when you find the shape
End If

Next Box

End Sub
 
Thanks Barb but I didn't make myself very clear at all. The boxes I refer too
are textboxes on a form (userform1) where numbers are entered. They are not
shapes. I appologise for my slackness resulting in your wasted time.
 
You could use:

me.controls("Rider" & box).value

The Me keyword refers to the object that owns the code--in this case, your
userform.
 
As a general rule of good coding practice, if you hit VBA's limit on
procedure size, you have long passed the reasonable size for an
individual procedure. It is like getting a speeding ticket for going
150 mph in a 35 zone.

Cordially,
Chip Pearson
Microsoft Most Valuable Professional
Excel Product Group, 1998 - 2009
Pearson Software Consulting, LLC
www.cpearson.com
(email on web site)
 
Back
Top