Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
DiscussionsAccessExcelInfoPathOutlookPowerPointPublisherWord
DirectoryUser Groups
Related Topics
Outlook ExpressInternet ExplorerWindowsMS Server ProductsMore Topics ...

MS Office Forum / Word / Programming / February 2005

Tip: Looking for answers? Try searching our database.

Bizarre Looping between "End Select" and "End Sub," etc

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Garth Robbins - 09 Feb 2005 17:16 GMT
Thank you very much in advance to anyone who can explain this to me. I've
created a series of procedures to convert text I have in Word into a format
with tabs that can be recognized as columns by Excel. The purpose of this
particular portion of the code is to review the text line by line to put the
paragraphs in numeric order. (In the sample below, it will move the line
number 5 up to between the 4 and 6, then move on.)  As I debug this
application, I don't understand why the VBA editor keeps looping back to
"End Select" or "End Sub" when I don't want it to. I see this in other subs
as well.

I simply want the application to end the current sub when it reaches my "88"
(which I use to indicate the end of the document) and step back into the
other portions of my code beyond what I pasted here. Further, I don't
understand how VBA decides how many times to loop around in this fashion.
Finally, after pushing F8 20 or 30 times when it should have already ended
my sub, the sub eventually goes from bouncing between "End Select" and "End
Sub" back to other lines of code in this sub which causes additional
problems. Again, I just want the sub to end. I thought I was using If-Thens
wrong, so converted to Select-Cases, but the problem persists.

In case someone can test my code, here is the text you can run the sub on:
0 NANCY

1 BAYS

2 1502 SUMMER RUN DR. UNIT 205

4 FLORISSANT

6 63033

5 Missouri

11 BAYS

20 1991

88

The code is below. To test, first position the cursor at the top of that
text, then step into the macro "ReOrderRowsV2."

Many Thanks,

Garth

Carlsbad, CA

760-929-9715 home

__________Here's the code___________

Dim Var(2) As Integer

Sub ReOrderRowsV2()
Select Case Var(1)
Case "0" To "77"
 SetNextCharEqualToVar1ForReOrder
 RunOrderingIfThens
End Select
End Sub

Sub RunOrderingIfThens()

Select Case Var(1)
Case 0
   GoToNextRow
   Var(0) = Var(1)
   ReOrderRowsV2

Case 77
   GoToNextRow
   Var(1) = 0
   Var(0) = 0
   ReOrderRowsV2

Case "1" To "76"
    If Var(0) - Var(1) > 0 Then
    MoveParUp
    End If
  GoToNextRow
  ReOrderRowsV2
End Select
End Sub

Sub SetNextCharEqualToVar0ForReOrder()
Select Case Var(1)
Case "0" To "77"
   On Error Resume Next
   selectNextNumber
   Var(0) = Selection.Text
End Select
End Sub

Sub SetNextCharEqualToVar1ForReOrder()
Select Case Var(1)
Case "0" To "77"
On Error Resume Next
   selectNextNumber
   Var(0) = Var(1)
   Var(1) = Selection.Text
End Select
End Sub

Sub GoToNextRow()
   Selection.MoveDown Unit:=wdParagraph, Count:=1
End Sub

Sub selectNextNumber()
   On Error Resume Next
   Selection.find.ClearFormatting
   With Selection.find
       .Text = " "
       .Replacement.Text = "^p"
       .Forward = True
       .Wrap = wdFindAsk
       .Format = False
       .MatchCase = False
       .MatchWholeWord = False
       .MatchWildcards = False
       .MatchSoundsLike = False
       .MatchAllWordForms = False
   End With
   Selection.find.Execute
   Selection.moveleft Unit:=wdCharacter, Count:=1
   Selection.moveleft Unit:=wdWord, Count:=1, Extend:=wdExtend
End Sub

Sub MoveParUp()
   Selection.moveleft Unit:=wdCharacter, Count:=1
   Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
   Selection.Cut
   Selection.MoveUp Unit:=wdLine, Count:=1
   Selection.PasteAndFormat (wdPasteDefault)
   If Var(0) > 2 Then
       Selection.MoveUp Unit:=wdLine, Count:=2
   End If
   SetNextCharEqualToVar0
   Selection.moveleft Unit:=wdCharacter, Count:=1
   GoToNextRow
   selectNextNumber
   RunOrderingIfThens
End Sub

Sub selectNextNumber()
   On Error Resume Next
   Selection.find.ClearFormatting
   With Selection.find
       .Text = " "
       .Replacement.Text = "^p"
       .Forward = True
       .Wrap = wdFindAsk
       .Format = False
       .MatchCase = False
       .MatchWholeWord = False
       .MatchWildcards = False
       .MatchSoundsLike = False
       .MatchAllWordForms = False
   End With
   Selection.find.Execute
   Selection.moveleft Unit:=wdCharacter, Count:=1
   Selection.moveleft Unit:=wdWord, Count:=1, Extend:=wdExtend
End Sub
Greg Maxey - 10 Feb 2005 04:51 GMT
Garth,

I can't answer you specific question.  It could be a result of entering an
leaving so many different routines.  I worked with your code a bit to see if
I could cut down on the number of routines.  It is far from perfect because
I hardly know what I am doing to start with.  Anyway, you might have a look
and see if you can apply it to your application.  It sucessfully sorted the
sample you provide.  I kept you end of list marker "88", but I didn't
understand the signifiacne of 0-77 so I replaced all that with an IsNumeric
funtion.

Private Var(2) As Integer
Private EndRoutine As Boolean
Sub Start()
Var(1) = 0
EndRoutine = False
CompareAndReorder
End Sub

Sub CompareAndReorder()

Do While IsNumeric(Var(1))
 selectNextNumber
 If EndRoutine = True Then Exit Sub
 Var(0) = Var(1)
 Var(1) = Selection.Text
 Do While Var(0) - Var(1) > 0
   Selection.MoveLeft Unit:=wdCharacter, Count:=1
   Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
   Selection.Cut
   Selection.MoveUp Unit:=wdLine, Count:=1
   Selection.Paste
   If Var(0) > 2 Then
       Selection.MoveUp Unit:=wdLine, Count:=2
   End If
   'select next number and set comparator
   selectNextNumber
   If EndRoutine = True Then Exit Sub
   Var(0) = Selection.Text
   Selection.MoveLeft Unit:=wdCharacter, Count:=1
   'Go to next row
   Selection.MoveDown Unit:=wdParagraph, Count:=1
   selectNextNumber
   If EndRoutine = True Then Exit Sub
   On Error Resume Next
   Var(1) = Selection.Text
   On Error GoTo 0
 Loop
 If EndRoutine = True Then Exit Sub
 'Go to next row
 Selection.MoveDown Unit:=wdParagraph, Count:=1
Loop
End Sub

Sub selectNextNumber()
 On Error Resume Next
 Selection.Find.ClearFormatting
 With Selection.Find
   .Text = " "
   .Forward = True
   .Wrap = wdFindStop
   .Format = False
   .MatchCase = False
   .MatchWholeWord = False
   .MatchWildcards = False
   .MatchSoundsLike = False
   .MatchAllWordForms = False
 End With
 Selection.Find.Execute
 Selection.MoveLeft Unit:=wdCharacter, Count:=1
 Selection.MoveLeft Unit:=wdWord, Count:=1, Extend:=wdExtend
 'Exit on reaching end of list marker
 If Selection.Text = "88" Then
   EndRoutine = True
     Exit Sub
   End If
End Sub

Signature

Greg Maxey/Word MVP
A Peer in Peer to Peer Support

> Thank you very much in advance to anyone who can explain this to me.
> I've created a series of procedures to convert text I have in Word
[quoted text clipped - 159 lines]
>    Selection.moveleft Unit:=wdWord, Count:=1, Extend:=wdExtend
> End Sub
Greg Maxey - 10 Feb 2005 04:55 GMT
Garth,

Actually the Start routing is redundant as well.

Just stick the two line at the beginning of the the ComareAndReorder macro:

Sub CompareAndReorder()
Var(1) = 0
EndRoutine = False
Do While IsNumeric(Var(1)) .............

Signature

Greg Maxey/Word MVP
A Peer in Peer to Peer Support

> Thank you very much in advance to anyone who can explain this to me.
> I've created a series of procedures to convert text I have in Word
[quoted text clipped - 159 lines]
>    Selection.moveleft Unit:=wdWord, Count:=1, Extend:=wdExtend
> End Sub
Garth Robbins - 10 Feb 2005 20:56 GMT
Wow! Thank you, Greg, for putting all that time into it. I have to spend
some time understanding what you're recommending. (You can tell I'm new to
VBA.) To answer your question, though, I use 0 through 77 to replace the
original text string labels that appear in the emailed output from website
forms:
0 = First_Name
1 = Last_Name
2 = Address_1
etc.

I wanted to convert those labels to numbers to be able to calculate how many
tabs to insert. "First_Name" is column 1 in Excel. "Last_Name" is column 2.
The challenge was that, not only can one of the above fields be missing
(such as they didn't provide a Last Name), but they can be in different
orders (Last Name might be before First Name in some of the emails). The
tabs I insert have to reflect this. Unfortunately, my client modified the
online forms in this fashion over time, but also gathered thousands of
records along the way that we still want to capture.

So the code I shared below is to address the second issue (the information
not coming in the numeric order I want).

Does this impact your recommendations?

Thanks!!
Garth

> Garth,
>
[quoted text clipped - 171 lines]
>>    Selection.moveleft Unit:=wdWord, Count:=1, Extend:=wdExtend
>> End Sub
Greg Maxey - 10 Feb 2005 21:31 GMT
Garth,

No problem.  I am just taking baby steps myself.  Your grasp of moving the
selection around is noteable ;-).  For your specific application the code I
sent you should work as all of your numbers are expected to be less than
"88" which I have used as an escape from the loop.  With that said, why
isn't "78" your upper limit.  I looked at the code again this morning and
realized that since we are sorting paragraphs that lead of with numbers then
we should seach for a number (not a space)  so change Find.Text = " " to
Find.Text = "0-9]{1,}" and set MatchWildCars = True.  With this we can
eliminate two more manipuations of the selection.

As far as your coding, I personally don't see the advantage of stepping out
to another Sub or Function unless you are passing a variable,or the
sub/funtion is large and repeated.  There wasn't anything to be gained when
you stepped out to move to the next line.  In fact the sub and the calls to
the subs took more code than just the steps in the main macro.  Here is what
I have now.

Private Var(2) As Integer
Private EndRoutine As Boolean
Sub CompareAndReorder()
Var(1) = 0
EndRoutine = False
Do While IsNumeric(Var(1))
 selectNextNumber
 If EndRoutine = True Then Exit Sub
 Var(0) = Var(1)
 Var(1) = Selection.Text
 Do While Var(0) - Var(1) > 0
   Selection.MoveLeft Unit:=wdCharacter, Count:=1
   Selection.MoveDown Unit:=wdLine, Count:=1, Extend:=wdExtend
   Selection.Cut
   Selection.MoveUp Unit:=wdLine, Count:=1
   Selection.Paste
   If Var(0) > 2 Then
       Selection.MoveUp Unit:=wdLine, Count:=2
   End If
   'select next number and set comparator
   selectNextNumber
   If EndRoutine = True Then Exit Sub
   Var(0) = Selection.Text
   Selection.MoveLeft Unit:=wdCharacter, Count:=1
   'Go to next row
   Selection.MoveDown Unit:=wdParagraph, Count:=1
   selectNextNumber
   If EndRoutine = True Then Exit Sub
   On Error Resume Next
   Var(1) = Selection.Text
   On Error GoTo 0
 Loop
 If EndRoutine = True Then Exit Sub
 'Go to next row
 Selection.MoveDown Unit:=wdParagraph, Count:=1
Loop
End Sub
Sub selectNextNumber()
 On Error Resume Next
 Selection.Find.ClearFormatting
 With Selection.Find
   .Text = "[0-9]{1,}"
   .Forward = True
   .Wrap = wdFindStop
   .Format = False
   .MatchCase = False
   .MatchWholeWord = False
   .MatchWildcards = True
   .MatchSoundsLike = False
   .MatchAllWordForms = False
 End With
 Selection.Find.Execute
 'Exit on reaching end of list marker
 If Selection.Text = "88" Then
   EndRoutine = True
     Exit Sub
   End If
End Sub

Signature

Greg Maxey/Word MVP
A Peer in Peer to Peer Support

> Wow! Thank you, Greg, for putting all that time into it. I have to
> spend some time understanding what you're recommending. (You can tell
[quoted text clipped - 202 lines]
>>>    Selection.moveleft Unit:=wdWord, Count:=1, Extend:=wdExtend
>>> End Sub
Garth Robbins - 10 Feb 2005 22:19 GMT
Right. I forgot to mention that, while I used 88 to reflect the end of the
document I'm scanning to convert (the "input"), I used 77 to denote a break
from one record (one respondent) to another. It could have been any other
number. The only thing is allowing for enough 0 through X numbers to
accomodate the multiple fields/columns. I doubt I'll see more than 30 tops
on this project, but I was playing it safe.

Let me know if you'd like me to upload a text file of the original webform
output that I run this code on for testing, or my complete macro so you can
see where this piece fits in. I understand if not. :)

By the way, there are two small things that drive me nuts and I haven't been
able to figure out:
1.  How do you watch the variables as you debug without havng to repeatedly
click the expand signs in the "View Locals" window?
2. Is there a way I can keep my application from hanging Word every time
something goes wrong? Perhaps a piece of code that tell it to abort
everything if the same instruction is repeated 30+ times, which normally
shouldn't happen. Sometimes it hangs because an unlimited number of tabs are
being inserted, other times it's looping indefinitely, etc. (It's not always
clear.)

Thank you,
Garth

> Garth,
>
[quoted text clipped - 280 lines]
>>>>    Selection.moveleft Unit:=wdWord, Count:=1, Extend:=wdExtend
>>>> End Sub
Greg Maxey - 11 Feb 2005 02:42 GMT
Garth,

I have never used the locals window.  Never heard of before you mentioned
it.  I use F8 to step through the code one line at a time.

I think I will pass on the offer to view your code.  My brain is fragged
right now, and I will be travelling soon for a few weeks.

At far as the loops, I wish I had a good answer.  If I see a circular loop
occuring I press CTRL+Break to get out of it.  Sometimes I put in a bit of
code to break out e.g., in the piece you and I have worked on finding "88"
is the breakout.  Otherwise the search will conctiniue in a cirucular loop.

I like to dabble in VBA, but I have a lot to learn.  Maybe one of the
heavies will come along and enlighten us both :-).  Good luck with your
project.  I enjoyed the challenge and was impressed how you worked through
all of those routines and case events.   Like I say, my brain is fragged.

Signature

Greg Maxey/Word MVP
A Peer in Peer to Peer Support

> Right. I forgot to mention that, while I used 88 to reflect the end
> of the document I'm scanning to convert (the "input"), I used 77 to
[quoted text clipped - 313 lines]
>>>>>    Selection.moveleft Unit:=wdWord, Count:=1, Extend:=wdExtend
>>>>> End Sub
Garth Robbins - 11 Feb 2005 04:52 GMT
Good deal. I've learned a lot from you and will try out your code. Many
thanks again and bon voyage, mon ami.

Garth

> Garth,
>
[quoted text clipped - 334 lines]
>>>>>>    Selection.moveleft Unit:=wdWord, Count:=1, Extend:=wdExtend
>>>>>> End Sub
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.