More Frustration Over My Dismal Understanding of Error Handling

[Greg]

Hello,

Jezebel and Max Moor introduced me to a new use of the Split function
today. I was experimenting with it to create a bit of code to
rearrange text.

I have the sample text:

James Miller
Margaret E. Smith
John R. Richland Sr

which I want to format as
Miller, James
Smith, Margarete E.
Richland, John R., III

I was able to do this easy enough one line at a time using selection.
However when I graduated to a For Each oPara approach my On Error
statements start acting up. If I run the following code, the first
line is properly formatted then I get a run-time error 9 "subscript out
of range" when the code attempts to format the second line. I expected
this error, that is why I used the On Error Statement.

I know that a similiar error is being generated when the first line is
processed. Apparently I am not properly instructing the routine to
clear or reset the error handler. I thought that was the purpose of
the the line Err.Clear, but appartently I am wrong.

Thanks.

Sub Test
Dim oPara As Word.Paragraph
Dim oRng As Word.Range
Dim pRef1 As String
Dim pRef2 As String
Dim pRef3 As String
Dim pRef4 As String

For Each oPara In ActiveDocument.Paragraphs
Set oRng = oPara.Range
oRng.MoveEnd wdCharacter, -1
pRef1 = Split(oRng)(0)
pRef2 = Split(oRng)(1)
On Error GoTo Construct1
pRef3 = Split(oRng)(2)
On Error GoTo Construct2
pRef4 = Split(oRng)(3)
GoTo Construct3

Construct1:
Err.Clear
oRng.Text = pRef2 & ", " & pRef1
GoTo Finished
Construct2:
Err.Clear
oRng.Text = pRef3 & ", " & pRef1 & " " & pRef2
GoTo Finished
Construct3:
If InStr("SRSrJRJrIIIV", pRef4) > 0 Then
Err.Clear
oRng.Text = pRef3 & ", " & pRef1 & " " & pRef2 & ", " & pRef4
GoTo Finished
End If
Finished:
Next
End Sub

 

[Helmut Weber]

Hi Greg,

try
On Error GoTo -1
instead of
Err.Clear

If I understood why the one way works and the other doesn't,
I would at least try to explain.

Greetings from Bavaria, Germany

Helmut Weber, MVP
"red.sys" & chr(64) & "t-online.de"
Word XP, Win 98
http://word.mvps.org/
Keep your VBA code safe, sign the ClassicVB petition www.classicvb.org

 

[Greg]

Helmut,

After posting, I noticed that Resume Finished would work (marginally).
I will have a look at your suggestion, but I have since abandoned that
approach for one not using Error Handlers:

Sub Test()
Dim oPara As Word.Paragraph
Dim oRng As Word.Range
Dim pRef0 As String
Dim pRef1 As String
Dim pRef2 As String
Dim pRef3 As String
Dim pRef4 As String
Dim i As Long

For Each oPara In ActiveDocument.Paragraphs
Set oRng = oPara.Range
oRng.MoveEnd wdCharacter, -1
i = UBound(Split(oRng)) + 1
Select Case i
Case Is > 1
pRef1 = Split(oRng)(0)
pRef2 = Split(oRng)(1)
Select Case i
Case Is < 3
oRng.Text = pRef2 & ", " & pRef1
Case Is < 4
pRef3 = Split(oRng)(2)
If InStr("SR.Sr.JR.Jr.IIIVIII", pRef3) > 0 Then
oRng.Text = pRef2 & ", " & pRef1 & ", " & pRef3
Else
oRng.Text = pRef3 & ", " & pRef1 & " " & pRef2
End If
Case Is = 4
pRef3 = Split(oRng)(2)
pRef4 = Split(oRng)(3)
If InStr("SR.Sr.JR.Jr.IIIVIII", pRef4) > 0 Then
oRng.Text = pRef3 & ", " & pRef1 & " " & pRef2 _
& ", " & pRef4
Else
oRng.Text = pRef4 & ", " & pRef1 & " " & pRef2 _
& " " & pRef3
End If
Case Else
MsgBox oRng & " contains too many elements" _
& " for this procedure to rearrange."
End Select
Case Else
End Select
Next
End Sub

Seems to work nicely on the following trial text:

John R. Richland Sr.
Margaret E. Smith
Robert Smith
Joan S. Jordan
Earl E. F. Hutton
John Strickland IV
E. Susie Jones
Joe
James Miller
James Earl Eustis Beauregard IV

 

[Greg]

Helmut,

Yes that method does work in the sample I posted originally. I have
seen a construction like the below example before, but I have never
used GoTo -1. I tried using On Error GoTo 0 before I tried your
suggestion and it didn't work either. Reading the help file it says
"Use Clear to explicitly clear the Err object" so I still can't
understand why Err.Clear wouldn't work in this case.

On Error Resume Next
'Do something
On Error GoTo 0

What does On Error Goto -1 mean?

Hopefully someone with a firm understanding of Errors and Error
Handling will help clear up why Err.Clear doens't work!

 

[Chuck]

Hi Greg

I'm not a big fan of using error statements for decision processing because
they can only function if a problem is created and then you have to clean up
the mess (at a minimum err.clear) which can cause more problems. I prefer
avoiding problems if possible with explicit statements. I got your code to
work by replacing the error handling with If...Then statements checking
UBound(Split(oRng)) as below:

Sub Test()
Dim oPara As Word.Paragraph
Dim oRng As Word.Range
Dim pRef1 As String
Dim pRef2 As String
Dim pRef3 As String
Dim pRef4 As String

For Each oPara In ActiveDocument.Paragraphs
Set oRng = oPara.Range
oRng.MoveEnd wdCharacter, -1

If Len(oRng.Text) < 1 Then
Exit For 'necessary to exit loop at end of document
End If

pRef1 = Split(oRng)(0)
pRef2 = Split(oRng)(1)
If UBound(Split(oRng)) < 2 Then
GoTo Construct1
End If
pRef3 = Split(oRng)(2)
If UBound(Split(oRng)) < 3 Then
GoTo Construct2
End If
pRef4 = Split(oRng)(3)
GoTo Construct3

Construct1:
oRng.Text = pRef2 & ", " & pRef1
GoTo Finished
Construct2:
oRng.Text = pRef3 & ", " & pRef1 & " " & pRef2
GoTo Finished
Construct3:
If InStr("SRSrJRJrIIIV", pRef4) > 0 Then
oRng.Text = pRef3 & ", " & pRef1 & " " & pRef2 & ", " & pRef4
GoTo Finished
End If
Finished:
Next
End Sub

 

[Greg]

Chuck,

Thanks. I agree and opted for a Select Case route. Still I would like
to understand why I was seeing the behaviour I did and how On Error
Goto -1 works.

 

[Greg Maxey]

Helmut,

I did some digging around in Google.

Consider these two routines:

Sub Test1()
On Error GoTo Handler
ActiveDocument.Variables("test").Delete
Handler:
MsgBox Err.Number
Err.Clear
MsgBox Err.Number
On Error Resume Next
ActiveDocument.Variables("test").Delete
MsgBox "Got passed it!"
End Sub

Sub Test2()
On Error GoTo Handler
ActiveDocument.Variables("test").Delete
Handler:
MsgBox Err.Number
On Error GoTo -1
MsgBox Err.Number
On Error Resume Next
ActiveDocument.Variables("test").Delete
MsgBox "Got passed it!"
End Sub

It appears that On Error Goto-1 both clears the Err properties and enables
an On Error Resume Next statement within and error trap.

 

[Chuck]

Hi Greg

I saw your select case post after I posted my reply. Good solution.

Have you seen this MSKB article:
http://support.microsoft.com/default.aspx?scid=kb;en-us;202019

I did a google groups search of the phrase "on error goto -1" (with quotes)
in microsoft.public.word.vba.general and came up with a couple of references
from Helmut to its being undocumented but reliable.

Other than that seems to be a mystery...

 

[Greg]

Chuck,

Thanks. I did a bit of google searching also. It appears On Error
Goto -1 was in the Word Basic tool kit.

I hadn't seen the KB article. but it supports what I have found. The
GoTo-1 will both clear error properties and enable an On Error Resume
Next statement inside an error trap:

Sub Test2()
On Error GoTo Handler
ActiveDocument.Variables("test­").Delete
Handler:
MsgBox Err.Number
On Error GoTo -1
MsgBox Err.Number
On Error Resume Next
ActiveDocument.Variables("test­").Delete
MsgBox "Got passed it!"
End Sub

and reset an error trap:

Sub Test2()
Dim i As Long
For i = 1 To 5
On Error GoTo Handler
Err.Raise 6
ReEnter:
MsgBox i

Next
Exit Sub
Handler:
MsgBox Err.Number
On Error GoTo -1
MsgBox Err.Number
GoTo ReEnter
End Sub