MS Office Forum / Word / Programming / February 2005
Bizarre Looping between "End Select" and "End Sub," etc
|
|
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
|
|
|