Word goes "non-responsive" during macro run

[muyBN]

I have a macro which, literally, every other time I run it, it makes the VB
editor and Word hang and go non-responsive. I would understand if it were
this unpredictable with different macros or target files or even pieces of
text that I work on; but it puzzles me since it's always the same
circumstances that produce varying results. Any ideas on how to prevent this?

 

[Jean-Guy Marcil]

muyBN was telling us:
muyBN nous racontait que :

I have a macro which, literally, every other time I run it, it makes
the VB editor and Word hang and go non-responsive. I would understand
if it were this unpredictable with different macros or target files
or even pieces of text that I work on; but it puzzles me since it's
always the same circumstances that produce varying results. Any ideas
on how to prevent this?

What Word version?
What is your macro actually doing?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[muyBN]

Thanks for your assistance.

The Word version I'm using is 2003.

What the macro is doing wouldn't really seem relevant here due to the fact
that it does its job fine once then the next time it doesn't; but regardless,
I will post a description here. Be forewarned that it might be kind of
complicated to follow; I come up with some things that others would never
dream of (nor would they want to, for fear of nightmares).

What I do is copy text from employment-related Web ads so I can apply for
the positions advertised. I then have my macro extract certain data and place
it into a Word table, then I convert it to records in Access, using the
fields as variables in my cover letter and resume.

For example, to find the title of the position being offered, I set up an
array of search variables such as "position, job, title," etc. When each of
these is found, whichever one has a colon (":") within a certain amount of
characters after it, I will assume that the trimmed text after the colon, to
the end of the paragraph, will be the job title.

Another example is for the e-mail address, if found. Mr. Macro searches for
the "@" sign. While it's found, the macro adds each character to the left and
right of the @ to the variable string, as long as it's not a space, paragraph
mark or line break.

 

[Jean-Guy Marcil]

muyBN was telling us:
muyBN nous racontait que :

Thanks for your assistance.

The Word version I'm using is 2003.

What the macro is doing wouldn't really seem relevant here due to the

Of course it is.
If all your macros were behaving that way, then I would agree.
But it seems that it is only the one macro that does that. So the answer is
in the macro.

fact that it does its job fine once then the next time it doesn't;
but regardless, I will post a description here. Be forewarned that it
might be kind of complicated to follow; I come up with some things
that others would never dream of (nor would they want to, for fear of
nightmares).

What I do is copy text from employment-related Web ads so I can apply
for the positions advertised. I then have my macro extract certain
data and place it into a Word table, then I convert it to records in
Access, using the fields as variables in my cover letter and resume.

For example, to find the title of the position being offered, I set
up an array of search variables such as "position, job, title," etc.
When each of these is found, whichever one has a colon (":") within a
certain amount of characters after it, I will assume that the trimmed
text after the colon, to the end of the paragraph, will be the job
title.

Another example is for the e-mail address, if found. Mr. Macro
searches for the "@" sign. While it's found, the macro adds each
character to the left and right of the @ to the variable string, as
long as it's not a space, paragraph mark or line break.

It seems you are doing lots of text/string manipulations.

Are you using the Selection object?
If you are, then don't. Use a Range object instead.

Also, are you using "ActiveDocument" or a document object set to the target
document.

Finally, it looks as though you are manipulating IE and Access with the
Macro?

How are you instantiating the Access (and/or IE) object(s)?

Have you debugged the macro step by step until you find the line preceding
the hang?
Try to do it often enough so that it hangs few times.
Is it always the same line of code?

--
Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Dave Lett]

As Jean-Guy mentioned, It seems you are doing lots of text/string
manipulations.
Since you are apparently finding the @ symbol and extending the range of
that in two directions inside some type of loop, I would want to make sure
that the logic of the loop actually allows you to escape the loop (i.e.,
make sure that the routine isn't stuck in an endless loop).

Dave

 

[muyBN]

Thanks, but the function to find e-mail is well-tested. The loop always stops
as long as there is a space, paragraph, line break, semi-colon or comma (code
shown below). I will, however, check more carefully for errors in other
loops. When I F8 through the whole thing (including function below), there
are no problems.



Function jbGetEMail(strParaMark As String, strE As String) As String
Dim intCnt As Integer

With Selection.Find
.ClearFormatting
.Font.Hidden = False
End With
DocHome
SdnFmt ("@")
BmkCreate ("strE")
With Selection
While .Find.Found And strE = ""
.MoveLeft
.MoveRight Extend:=wdExtend
While Selection <> " " And Selection <> Chr(13) And Selection <>
Chr(11) And Selection <> Chr(10) And Selection <> "," And Selection <> ";"
strE = strE & Selection
.MoveRight
.MoveRight Extend:=wdExtend
Wend
BmkGoTo ("strE")
.MoveLeft
.MoveLeft Extend:=wdExtend
While Selection <> " " And Selection <> strParaMark And
Selection <> "," And Selection <> ";"
strE = Selection & strE
.MoveLeft
.MoveLeft Extend:=wdExtend
Wend
.MoveLeft
Sdn (strE)
If InStr(strE, ".") <> 0 And InStr(strE, "@") <> 0 Then
BmkCreate ("strE")
strE = Trim(Selection)
.Font.Hidden = True
Else
strE = ""
BmkDelete ("strE")
End If
.MoveRight
SdnFmt ("@")
Wend
End With
strE = Trim(strE)
If Right(strE, 1) = "." Then
strE = Left(strE, Len(strE) - 1)
End If
jbGetEMail = strE
End Function

 

[Dave Lett]

Hi again,

So, your routine
1) finds the @ symbol (bookmarks it) and moves one character at a time to
the RIGHT while performing a check
2) selets the bookmark (i.e., the @ symbol), move one character at a time to
the LEFT of the @ symbol while performing a check (and prefixes the new text
to the email address)
3) after testing for a valid email address, you move the selection to the
RIGHT so that it's at the very beginning of the email address.
4) You then run SdnFmt ("@") again (which I presume is finding the @ symbol)

That LOOKS like and endless loop because it would select the next @ symbol
in the document and the cursor is in front of the email address that you
just parsed, so you would always be finding the same @ symbol. However, I
don't know the details of SdnFmt ("@"), and if you say that it doesn't
create an endless loop, I will defer to your hands-on experience with your
routine(s).

However, if you're really trying to optimize how well your routine performs,
I would suggest that you use the range object to get your email address
instead of the selection object. In fact, you MIGHT be able to use a
wildcard search for email address (to get the whole address in the selection
and avoid having to extend the selection to the left or right). Make a copy
of one of the documents that has a lot of email addresses in it and see if
the following could work:

Dim oRng As Range
With Selection.Find
.ClearFormatting
.Text = "[A-z0-9.]{2,}\@[A-z0-9.]{2,}"
.MatchWildcards = True
Do While .Execute
Set oRng = Selection.Range
If Right(oRng.Text, 1) = "." Then
oRng.MoveEnd Unit:=wdCharacter, Count:=-1
End If
MsgBox oRng.Text
Loop
End With

If this meets your needs, it will be a lot more efficient than what you
currently have.

HTH,
Dave

 

[muyBN]

Jean-Guy: It seems you are doing lots of text/string manipulations. Are you
using the Selection object? If you are, then don't. Use a Range object
instead.

Bryan: I will give you some of the code snippets below where I am using
"Selection" and "ActiveDocument." I have never much used "Range" so I would
appreciate suggestions on how to convert the code.

Jean-Guy: Also, are you using "ActiveDocument" or a document object set to
the target document?

Bryan: I'm using ActiveDocument (not objects) to establish file and path
name variables. I'm not real experienced at creating and using objects.

Jean-Guy: Finally, it looks as though you are manipulating IE and Access
with the Macro.

Bryan: In this macro, I'm not actually doing any IE or Access manipulations.
I've already copied and pasted plain text from the IE ads into a Word file
then I "manually" bring in the results through the Access import utility.

Jean-Guy: Have you debugged the macro step by step until you find the line
preceding the hang? Try to do it often enough so that it hangs a few times.
Is it always the same line of code?

Bryan: Actually, the whole macro runs perfectly when I run it line-by-line
or a few lines at a time. I would hesitate to post the whole document and/or
macro (and attendant functions) in this forum--mainly because I don't want to
take up a lot of space.


Below is some of the code that uses "Selection" and "ActiveDocument"
(snippets are separated by *****):

With Selection
.TypeText (strText)
strText = ""
DocHome
Sdn ("^p")
If Selection.Find.Found Then strParaMark = Selection
DocHome
.Document.SaveAs (strTargetPath & "\jbWork.doc")
'in work document, search for job name
intCnt = 0
varArr = Array("Position", "Job Title", "^pJob")
While intCnt < 3 And strJ = ""
DocHome
Sdn (varArr(intCnt))
'when one of the terms is found, create a bookmark
If .Find.Found Then
BmkCreate ("strJ")
.MoveRight
.MoveLeft
Sdn (":")
If .Find.Found Then
.MoveRight
.Extend
.MoveDown unit:=wdParagraph
.MoveLeft
.EscapeKey
If Len(Selection) < 60 Then
strJ = Trim(Selection)
BmkCreate ("strJ")
End If
End If
End If
intCnt = intCnt + 1
Wend
BmkGoTo ("here")
varArr = ""
End With

*****

While intCnt <= intOpenFiles
On Error Resume Next
If InStr(ActiveDocument.Name, "Orig") <> 0 Then
intSource = intCnt
strSourceFile = ActiveDocument.Name
If InStr(strSourceFile, "Ed") <> 0 Then blnEdu = True
End If
If ActiveDocument.Name = "jb1DB.doc" Then
blnInd = True
blnEdu = False
Else
If InStr(ActiveDocument.Name, "Ed") <> 0 Then
blnEdu = True
End If
End If
intTarget = intCnt
strTargetFile = ActiveDocument.Name
strTargetPath = ActiveDocument.Path
'check to see if work document is in this window; if so, then close
it without saving (run CloseDoc)
If LCase(ActiveDocument.Name) = "jbwork.doc" And intCnt <=
intOpenFiles Then CloseDoc
If ActiveDocument.Name = strSourceFile Or intOpenFiles = 1 Then
intCnt = intOpenFiles + 50
Wend

 

[muyBN]

Dave, this looks good. Jean-Guy (in posts above) also suggested using ranges.
I'll work with what you've suggested and let you know. As I told him, I use
Selection all the time and haven't much experience with the Range object, but
it's time I start a-learnin' it.

BTW, following is my "SdnFmt" subroutine. It basically searches for
pre-defined formatted text without all the built-in parameters of the "With
Selection.Find".


Sub SdnFmt(strPassTxt As String)
With Selection.Find
.Text = strPassTxt
.Forward = True
.Wrap = wdFindStop
.MatchCase = False
.MatchWholeWord = False
.Format = True
End With
Selection.Find.Execute
End Sub


--
Bryan

Hi again,

So, your routine
1) finds the @ symbol (bookmarks it) and moves one character at a time to
the RIGHT while performing a check
2) selets the bookmark (i.e., the @ symbol), move one character at a time to
the LEFT of the @ symbol while performing a check (and prefixes the new text
to the email address)
3) after testing for a valid email address, you move the selection to the
RIGHT so that it's at the very beginning of the email address.
4) You then run SdnFmt ("@") again (which I presume is finding the @ symbol)

That LOOKS like and endless loop because it would select the next @ symbol
in the document and the cursor is in front of the email address that you
just parsed, so you would always be finding the same @ symbol. However, I
don't know the details of SdnFmt ("@"), and if you say that it doesn't
create an endless loop, I will defer to your hands-on experience with your
routine(s).

However, if you're really trying to optimize how well your routine performs,
I would suggest that you use the range object to get your email address
instead of the selection object. In fact, you MIGHT be able to use a
wildcard search for email address (to get the whole address in the selection
and avoid having to extend the selection to the left or right). Make a copy
of one of the documents that has a lot of email addresses in it and see if
the following could work:

Dim oRng As Range
With Selection.Find
.ClearFormatting
.Text = "[A-z0-9.]{2,}\@[A-z0-9.]{2,}"
.MatchWildcards = True
Do While .Execute
Set oRng = Selection.Range
If Right(oRng.Text, 1) = "." Then
oRng.MoveEnd Unit:=wdCharacter, Count:=-1
End If
MsgBox oRng.Text
Loop
End With

If this meets your needs, it will be a lot more efficient than what you
currently have.

HTH,
Dave

 

[muyBN]

I finally found the apparent problem: a while statement that should have
exited when a counter accrued to the number of open files. I wasn't
incrementing the counter. But how strange that on exactly every other run of
the macro this would hang up the macro and on the other it wouildn't.

Thanks again for your assistance; I got some decent instruction on using
ranges and creating objects. I'll try to incorporate more of these into my
macros.