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

"NDBC" wrote:

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.

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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NDBC" wrote in message
...
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.

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.

"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NDBC" wrote in message
...
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.

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


"NDBC" wrote:

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.

"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NDBC" wrote in message
...
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.

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.



"Barb Reinhardt" wrote:

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


"NDBC" wrote:

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.

"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NDBC" wrote in message
...
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.

You could use:

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

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

NDBC wrote:

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.

"Don Guillett" wrote:


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

--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"NDBC" wrote in message
...
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.



--

Dave Peterson

It could well be that if you leave that procedure as it is and run the VBA
Code Cleaner:
http://www.appspro.com/Utilities/CodeCleaner.htm
it will run fine.

RBS


"NDBC" wrote in message
...
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.

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)


On Fri, 7 Aug 2009 15:13:01 -0700, NDBC
wrote:

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.