Programming error - Procedure too large

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.
 
N

NDBC

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
 
D

Don Guillett

It's VERY possible your code can be greatly streamlined. Care to show it to
us?
 
N

NDBC

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.
 
B

Barb Reinhardt

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
 
N

NDBC

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.
 
D

Dave Peterson

You could use:

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

The Me keyword refers to the object that owns the code--in this case, your
userform.
 
C

Chip Pearson

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)
 
Top