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

    END statement by itself (Word 2003 et al.)

    For the first time in my life AFAICR, I've used an "END" statement by itself, without it being "End Function" or "End Subroutine" or "End Block" or End-anything.

    I encountered problems with some Word/VBA code that accumulated statistics and called Excel repeatedly to build charts.
    The first run of the macro goes fine. A second run collapses with problems with undefined objects.
    I know, I know I need to know more about Objects ......

    I found that (VBE) choosing Run, Reset got around the problem, so I placed an "End" immediately before the "End Sub", and the problem is submerged.
    The error is still there and I must deal with it, but for the next 24 hours I can get on with life.

    <pre>Public Function RunQuantifierFromFolder(strFolder As String)
    ''' For each document in a given folder tree
    '' accumulate count in an array
    Call TallySections(strFolder) ' explore folder, tally, sort array
    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
    xl.Application.Visible = True
    Dim wkb As excel.Workbook
    Set wkb = xl.Workbooks.Add
    Call ReportTallys(wkb, "Bookmarks", strArTallyBookmarks)
    (snip!)
    Call ReportTallys(wkb, "Tables", strArTallyTables)
    wkb.Saved = True
    wkb.Close
    xl.Application.Quit
    Set xl = Nothing
    Selection.HomeKey Unit:=wdStory
    On Error Resume Next
    Application.Run "MrUse.Macros.EnableSaves"
    On Error GoTo 0
    End
    End Function</pre>


  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: END statement by itself (Word 2003 et al.)

    Hi Chris
    Would there be any value in:

    Set wbk = Nothing

    before the statement

    <hr>Set xl = Nothing<hr>
    Regards
    Don

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

    Re: END statement by itself (Word 2003 et al.)

    > Set wbk = Nothing
    Thanks Don, and I apologise for the delay in thanking you.

    I tried the =Nothing, made no difference.
    I then set about carefully making sure that I closed/quit and set=Nothing everything in a properly nested sequence. Same result. "Object variable not set". I tried splicing a few DoEvents about - mother always said it was the essence of good cooking in VBA - same result.

    I'm going to lick my wounds and see what's going on. The failure occurs in a "With wbk" chunk of code, so i ought to be able to isolate the problem.

  5. #4
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,870
    Thanks
    0
    Thanked 79 Times in 75 Posts

    Re: END statement by itself (Word 2003 et al.)

    Chris,
    I don't know if this is relevant (and without seeing the rest of the code it is impossible to tell what might be the culprit) but you have instantiated the xl variable as an Excel.Application but then use <code>xl.application.visible</code> and <code>xl.application.quit</code>. Does it help if you change those lines to <code>xl.visible</code> and <code>xl.quit</code>?
    Regards,
    Rory
    Microsoft MVP - Excel.

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

    Re: END statement by itself (Word 2003 et al.)

    Thanks, Rory. At first I thought you were on to something, and the new code ran flawlessly, then I realised I had still in place the "END" statement. Once I removed that, it baulked.

    I have stripped it down as bare as I dare. The attached file holds this code:<pre>Public Sub RQFF()
    Dim xl As Object
    Set xl = CreateObject("Excel.Application")
    xl.Visible = True
    '''
    Dim wkb As Excel.Workbook
    Set wkb = xl.Workbooks.Add
    ActiveCell.FormulaR1C1 = 7 ' Run time error '91' on second call.
    wkb.Saved = True
    wkb.Close
    Set wkb = Nothing ' <post:=548,776>post 548,776</post:>
    '' DoEvents
    '''
    xl.Quit
    Set xl = Nothing
    '' DoEvents
    '''
    Selection.HomeKey Unit:=wdStory
    End Sub</pre>

    I run the macro twice; the first time it executes and terminates, but the second run fails with a run-time error 91 in Excel 2000.

  7. #6
    Super Moderator
    Join Date
    Jan 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    3,503
    Thanks
    3
    Thanked 139 Times in 132 Posts

    Re: END statement by itself (Word 2003 et al.)

    Your code gave me the same problem in Word 2000 so it wasn't just your machine.

    Try replacing the ActiveCell line with something less 'selection dependent' such as
    wkb.Worksheets(1).Cells(1, 1) = 7

    I have no idea why your activecell method only works once but at least it can be avoided in this case.
    Andrew Lockton, Chrysalis Design, Melbourne Australia

  8. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: END statement by itself (Word 2003 et al.)

    When using Automation, you should NEVER EVER use unqualified references to objects in the application you're automating. In this example, ActiveCell is not necessarily an object in the xl object, since you don't say so explicitly. Referring to ActiveCell unqualified starts a new instance of Excel. If you look at the Processes tab of the Task Manager after running the code once, you'll see that an instance of Excel is still running. The second time round, ActiveCell refers to this instance of Excel, not to the new instance you created. The End statement kills the instance of Excel, but not in a "decent" way. If you change the line to

    xl.ActiveCell.FormulaR1C1 = 7

    the problem will not occur - there will be no extra instance of Excel.

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

    Re: END statement by itself (Word 2003 et al.)

    Hans, thanks for the foolproof solution (but see also my response to Andrew).
    It works well.

    > you should NEVER EVER use unqualified references to objects in the application you're automating.
    I must have missed this in my first reading of the help files (grin!)
    I see too by <!post=Your GetObject post,510785>Your GetObject post<!/post> that there is a better way of doing what I'm trying to do.
    My real code obtained a workbook object and passed that down three or four levels. My patch augments the calls with the XL, object, but I suspect it needn't be so.

    I had always assumed by seeing "Parent" in the Local variables information that any object created of another object knew who its parent was, and so (in my case) would obviously be an Excel object.

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

    Re: END statement by itself (Word 2003 et al.)

    > Your code gave me the same problem in Word 2000 so it wasn't just your machine

    Thanks for this response, Andrew.
    It places me in an awkward position: If it's not the computer, then it must be ....? I hate to drag you down to my level of ignorance!
    Hans's response hit the nail on the head, and gave me something else to think about, too.

  11. #10
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: END statement by itself (Word 2003 et al.)

    Hi Hans
    I am surprised that xl.Quit didn't close Excel. Can you help me understand why it didn't?

    T.I.A.
    Regards
    Don

  12. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 16 Times in 16 Posts

    Re: END statement by itself (Word 2003 et al.)

    The unqualified reference to ActiveCell prevents Excel from quitting. I won't pretend to understand the technical details, but this is precisely the kind of problem that occurs with unqualified references.

  13. #12
    Silver Lounger
    Join Date
    Jul 2001
    Location
    Ottawa, Ontario, Canada
    Posts
    1,609
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: END statement by itself (Word 2003 et al.)

    I Should have tested that myself. <img src=/S/blush.gif border=0 alt=blush width=15 height=15>
    Thank you for the explanation Hans.
    Regards
    Don

Posting Permissions

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