Page 1 of 2 12 LastLast
Results 1 to 15 of 22
  1. #1
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Error handler (W97, WinNT4)

    Following on from a post in the Word forum ( with sample document, found <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showthreaded.pl?Cat=&Board=wrd&Number=114062&page= &view=&sb=&o=&vc=1#Post114062>here</A>), can anyone tell me why, in the following section of a sub, the error handler works once (and skips a section where the document name doesn't exist) but doesn't work when it happens the next time? I've checked the help on error handlers but I can't see anything except the suggestion to put 'on error goto 0' in to deactivate an active error handler (which I have included, as you can see).

    <pre>On Error GoTo nextDoc4

    Documents.Open FileName:=activePath + "ch-03.doc", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _
    Format:=wdOpenFormatAuto
    aCount3 = ActiveDocument.Words.Count
    ActiveDocument.Saved = True
    ActiveDocument.Close

    nextDoc4:
    On Error GoTo 0
    On Error GoTo nextDoc5

    Documents.Open FileName:=activePath + "ch-04.doc", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _
    Format:=wdOpenFormatAuto
    aCount4 = ActiveDocument.Words.Count
    ActiveDocument.Saved = True
    ActiveDocument.Close

    nextDoc5:
    On Error GoTo 0
    On Error GoTo nextDoc6

    Documents.Open FileName:=activePath + "ch-05.doc", ConfirmConversions:=False, _
    ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
    Revert:=False, WritePasswordDocument:="", WritePasswordTemplate:="", _
    Format:=wdOpenFormatAuto
    aCount5 = ActiveDocument.Words.Count
    ActiveDocument.Saved = True
    ActiveDocument.Close
    </pre>


    I seem to be posting an awful lot of queries recently, so apologies for taking so much time - but any help would be appreciated!

    Many thanks
    Beryl M


  2. #2
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Error handler (W97, WinNT4)

    In fact, the code has changed quite a bit since the original post, so I'm reattaching the doc here. Many thanks!
    Attached Files Attached Files
    Beryl M


  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Error handler (W97, WinNT4)

    Hi Beryl,
    I think the attached will work for you - I've only done some very quick testing on it. You may want to rejig the code a little for your exact needs.
    I recall somewhere in this forum there was a thread about a dll that allows you to access file properties without having to open them - you might want to look into that as I suspect it would speed this up a lot!
    Hope that helps.
    Attached Files Attached Files
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Error handler (W97, WinNT4)

    Wow! Talk about shortening the code! I really must try to get to grips with this looping business - it's at times like these that the fact that I'm self taught really becomes obvious!

    And yes, there is indeed a thread on accessing files without opening them - I found it <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=vb&Number=110777&page=&view =&sb=&o=&vc=1>here</A>. For once the search words I used found the right answer first time! (Not at all a common occurrence, I can assure you!).

    I won't have a chance to check your code over for a while yet, but many thanks anyway - I'm sure it'll work a treat! <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Beryl M


  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Error handler (W97, WinNT4)

    IMHO, self-taught is the best way to be - if only for the joy of looking at stuff you wrote a few years ago and wondering what on earth you thought you were doing! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Of course at some point, you do need to get some books and plough through them... <img src=/S/brickwall.gif border=0 alt=brickwall width=25 height=15> <img src=/S/confused.gif border=0 alt=confused width=15 height=20> <img src=/S/read.gif border=0 alt=read width=19 height=33> <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> <img src=/S/compute.gif border=0 alt=compute width=40 height=20> <img src=/S/groovin.gif border=0 alt=groovin width=21 height=21>.
    Regards,
    Rory

    Microsoft MVP - Excel

  6. #6
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Error handler (W97, WinNT4)

    Love the smilie sequence!

    I did plough through some books to start with, but I must admit to not having done so since - and that was a couple of years ago now ...
    Beryl M


  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Error handler (W97, WinNT4)

    The other day I found a copy of VBA Developers Handbook 2nd Ed by Getz & Gilbert in a bookshop for
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    Silver Lounger
    Join Date
    Jun 2001
    Location
    Morden, Surrey, United Kingdom
    Posts
    1,838
    Thanks
    3
    Thanked 0 Times in 0 Posts

    Re: Error handler (W97, WinNT4)

    Just had a look at the Departmental bookshelf, and we've got a copy! Not second edition, though.

    Guess what I'm going to be reading on the train for the next zillion weeks ... <img src=/S/doze.gif border=0 alt=doze width=15 height=15>

    Many thanks!
    Beryl M


  9. #9
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Error handler (W97, WinNT4)

    Well, Rory took a little shortcut by ignoring all errors completely! Expedient, but doesn't help you understand what's going on.

    After an error is trapped, you cannot re-enable error trapping with On Error Goto 0, although that would be very nice. Instead, you have to use Resume. In your original code example, you could do this:

    <pre>On Error GoTo nextDoc2
    Documents.Open FileName:=activePath + "ch-01.doc", ConfirmConversions:=False, ReadOnly:=False, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:=wdOpenFormatAuto
    aCount1 = ActiveDocument.Words.Count
    ActiveDocument.Saved = True
    ActiveDocument.Close

    nextDoc2:
    Resume label2A
    label2A:
    On Error GoTo nextDoc3</pre>

    But as you noted, the loop is much more efficient. The problem with this code is that you are not actually learning about any errors:

    <pre>On Error Resume Next
    For lngIndex = 0 To 60
    Set docCurrent = Documents.Open(FileName:=activePath + "ch-" & _
    Format((lngIndex + 1), "00") & ".doc", ConfirmConversions:=False, ReadOnly:=False, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:=wdOpenFormatAuto)
    With docCurrent
    aCount(lngIndex) = .BuiltInDocumentProperties(wdPropertyWords)
    .Saved = True
    .Close
    End With
    Set docCurrent = Nothing
    Next lngIndex</pre>


    Since you probably only want to blow off "file not found" errors, the "safer" method would be as follows:

    <pre>For lngIndex = 0 To 60
    On Error Resume Next ' set "quiet" error handling
    Set docCurrent = Documents.Open(FileName:=activePath + "ch-" & _
    Format((lngIndex + 1), "00") & ".doc", ConfirmConversions:=False, ReadOnly:=False, _
    AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
    WritePasswordDocument:="", WritePasswordTemplate:="", Format:=wdOpenFormatAuto)
    Select Case Err.Number
    Case 0 ' no error
    On Error GoTo 0 ' reset normal error handling
    With docCurrent
    aCount(lngIndex) = .BuiltInDocumentProperties(wdPropertyWords)
    .Saved = True
    .Close
    End With
    Set docCurrent = Nothing
    Case 5174 ' file not found
    'clear the error and continue
    Err.Clear
    Case Else ' something unexpected
    MsgBox Err.Number & ": " & Err.Description, _
    vbCritical + vbOKOnly, "Error!"
    Exit Sub
    End Select
    Next lngIndex</pre>

    Hope this helps.

  10. #10
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error handler (W97, WinNT4)

    Since you are still in help mode (grin) I ***hate*** using On Error, but I have lowered my standards in an attempt to get things working.

    I understand the comment about how one can only trap one error, but I'm not sure how to trap a different error, or at least, errors at different points in the procedure.

    My code (reproduced below) is stymied. For the time being I can't determine a method for detecting a procedure type, so I thought, why not try one type, if it causes an error, cascade to the next type. If that 2nd type works, all is well, else, trigger an error to go to try the third type, then the fourth.

    Clearly, since it doesn't work, I'm mis-using the techniques.

    Could you please point me in the right direction?
    <pre>' I HATE using On Error
    Try_vbext_pk_Proc:
    Err.Clear
    On Error GoTo Try_vbext_pk_Get
    strAr(1, U2) = objMod.CodeModule.ProcStartLine(strName, vbext_pk_Proc)
    strAr(2, U2) = objMod.CodeModule.ProcCountLines(strName, vbext_pk_Proc)
    strAr(3, U2) = objMod.CodeModule.Lines(strAr(1, U2), strAr(2, U2))
    intLine = intLine - strAr(2, U2)
    ReDim Preserve strAr(3, U2 + 1)
    GoTo DoneItAll
    Try_vbext_pk_Get:
    Err.Clear
    On Error GoTo Try_vbext_pk_Let
    strAr(1, U2) = objMod.CodeModule.ProcStartLine(strName, vbext_pk_Get)
    strAr(2, U2) = objMod.CodeModule.ProcCountLines(strName, vbext_pk_Get)
    strAr(3, U2) = objMod.CodeModule.Lines(strAr(1, U2), strAr(2, U2))
    intLine = intLine - strAr(2, U2)
    ReDim Preserve strAr(3, U2 + 1)
    GoTo DoneItAll
    Try_vbext_pk_Let:
    Err.Clear
    On Error GoTo Try_vbext_pk_Set
    strAr(1, U2) = objMod.CodeModule.ProcStartLine(strName, vbext_pk_Let)
    strAr(2, U2) = objMod.CodeModule.ProcCountLines(strName, vbext_pk_Let)
    strAr(3, U2) = objMod.CodeModule.Lines(strAr(1, U2), strAr(2, U2))
    intLine = intLine - strAr(2, U2)
    ReDim Preserve strAr(3, U2 + 1)
    GoTo DoneItAll
    Try_vbext_pk_Set:
    Err.Clear
    On Error GoTo Stumped
    strAr(1, U2) = objMod.CodeModule.ProcStartLine(strName, vbext_pk_Set)
    strAr(2, U2) = objMod.CodeModule.ProcCountLines(strName, vbext_pk_Set)
    strAr(3, U2) = objMod.CodeModule.Lines(strAr(1, U2), strAr(2, U2))
    intLine = intLine - strAr(2, U2)
    ReDim Preserve strAr(3, U2 + 1)
    GoTo DoneItAll
    Stumped:
    MsgBox "stumped!"
    DoneItAll:
    </pre>



    The code as it stands detects the first time (during iteration of a loop) that a procedure is NOT a type vbext_pk_Proc and drops through to the next section Try_vbext_pk_Get which happens to be correct.

    The code loops within the procedure (for the next name) and now it raises an error on the line
    <pre>strAr(1, U2) = objMod.CodeModule.ProcStartLine(strName, vbext_pk_Proc)
    </pre>

    when I really expect the On Error GoTo Try_vbext_pk_Get to have taken effect.

  11. #11
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Error handler (W97, WinNT4)

    Resume. After an On Error Goto is triggered, no new error trapping can be handled until you Resume. You are not using Resume. You cannot trick the interpreter using Err.Clear.

    Okay, the problem is clear, but what is the best solution? Assuming you know the error number, you can use the inline error checking technique as demonstrated above. On Error Resume Next, then after running the potentially suspect line, use an If structure to decide how to proceed.

    (This could well be a completely stupid question, but can you tell from the name of the procedure and module what kind of procedure it is, or do you need to know what type it is to get its name?)

  12. #12
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error handler (W97, WinNT4)

    Isn't the first line of a procedure its name? In that case, all you have to do is find the first line of the procedure and look for telltale words like "Property Let", etc.
    Charlotte

  13. #13
    Super Moderator jscher2000's Avatar
    Join Date
    Feb 2001
    Location
    Silicon Valley, USA
    Posts
    23,112
    Thanks
    5
    Thanked 93 Times in 89 Posts

    Re: Error handler (W97, WinNT4)

    I guess Chris knows the names of the procedures somehow; there doesn't appear to be a convenient repository of them from my brief scan of the help/object browser.

    Here's a second take on error checking. Yes, I am using numeric constants for the procedure type, but it makes looping feasible.

    <pre>Sub SeekProcedure()
    Dim intCounter As Integer, lngStartLine As Long
    On Error Resume Next
    For intCounter = 0 To 3
    lngStartLine = VBE.ActiveVBProject.VBComponents("Module1").CodeMo dule. _
    ProcStartLine("MyProcedure", intCounter)
    If Err.Number = 0 Then
    Exit For
    Else
    Err.Clear
    End If
    Next
    On Error GoTo 0
    If lngStartLine > 0 Then
    MsgBox "Procedure starts on line " & lngStartLine
    Else
    MsgBox "Procedure not found."
    End If
    End Sub</pre>


  14. #14
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error handler (W97, WinNT4)

    > look for telltale words like "Property Let",

    I could program this, but VBA provides a means of finding the name - it just doesn't hang together!

    In other code I have located the starting line of a procedure, I know it's name, and I can adjust resreved words - changing "Function" to "Public Function" and the like, Use Trim and so on.

    It just doesn't seem right to have to loop through every line of a module to see if it represents the first line of a procedure when VBE, in theory, provides such a technique.

    Please note too that this code has been running flawlessly ('though I say so myself) for over two years. It cleans up templates like a dream.

    I'm running into problems now because I have Class modules; I've not had to process them before.

    The logic of the whole thing works well; I just have problems locating the starting line of procedures in Class Modules.

    I hope that makes sense. I'm trying to say "This isn't an barnd new piece of code that I'm trying to get working; it's a facet of data I've not met before".

  15. #15
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Yilgarn region of Toronto, Ontario
    Posts
    5,453
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error handler (W97, WinNT4)

    >Resume. After an On Error Goto is triggered, no new error trapping can be handled until you Resume. You are not using Resume. You cannot trick the interpreter using Err.Clear.


    OK. I'm still totally lost. I have attached the Procedure in its latest modified form. It still doesn't trap the errors correctly.

    Visualise the module - series of properties then a series of methods. I work backwards, thus processing the methods until I hit the first (actually the last in the series) of the property statements. The attached code handles that well. It triggers the On Error GoTo Try_vbext_pk_Get and deals with the first propert procedure it has found, and loops (backwards) to get the next(previous?) procedure. On this pass through the loop, it tiggers a run-time error, that is, my On Error GoTo Try_vbext_pk_Get doesn't work this time through.

    If I knew how to attach a bottle of home-preserved gingered peaches I'd do so and include my template for you to look at.

    I have two problems

    (1) I don't understand Error Trapping

    (2) The ProcStartLine seems to break down, or be incomplete.




    > use an If structure to decide how to proceed.

    As the attachment shows, I don't mind a clumsy IF procedure as a means of getting the code working. I can always find a neater method later on.



    > can you tell from the name of the procedure and module what kind of procedure it is, or do you need to know what type it is to get its name?)

    You need to know the type. The ProcStartLine requires that you specify the type (as in vbext_pk_Proc, vbext_pk_Get etc), but there's no means (AFAIK) of determining the type until you know where the procedure is.

    I can vaguely understand the reasoning given in the Help files - since there can be a Get and a Let for a property, there can be two procedures (Get and Let) for the one identifier, therefore, for the identifier we must specify what type of procedure we want (the Get onE or the Let one). I just haven';t managed to put it all together.

    This weekend I might try to re-write the procedure. I just told Charlotte that i didn't want to loop through the code module one line at a time, but I see that I'm doing that anyway.

    Charlotte was right when (I think) she said that there's no easy way to obtain a directory of all the procedure names within a module. If there were, the ProcStartLine Method might be easier with which to work.
    Attached Files Attached Files

Page 1 of 2 12 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •