Results 1 to 11 of 11
  1. #1
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Controlling VBA run-time errors (2002/2003)

    I have code that instantiates Excel, opens a workbook, makes some changes, saves the workbook and closes Excel. What I am looking for is a way to control error handling that occurs within Excel. For example if the workbook that my code opens has some bad code within it that triggers a run-time error I would like to handle the error in my code so that no user intervention is required.

    I can use either VB.Net or VB6 for my code. I'd prefere to use VB.Net using Interop for calling the Excel COM object but if there is a VB6 only soloution I am open to it. I had considered .Net Delegates but if I understand them correctly I can't assign a .Net delegate to a method within any of the Excel code modules.

    Any ideas?

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Controlling VBA run-time errors (2002/2003)

    You need to be more specific. Some generic code is:

    <pre>Sub ExampleCode()
    on error GoTo Erhandler

    'Your code here

    ExitHandler:
    'Add any "clean up code"
    Exit sub

    ErrHandler:
    MsgBox Err.Description
    Resume ExitHandler
    End Sub</pre>


    Add any code in the "errhandler" to handle specific errors. This will just give a message, but won't go into debug mode.

    Steve

  3. #3
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Controlling VBA run-time errors (2002/2003)

    What you are describing is error handling within Excel. I need to control error handling outside of Excel so that any run-time error that occurs within in Excel will pass control up to my app that loaded Excel so I can prevent it from displaying on the screen. The goal is to prevent any run-time errors from causing Excel to halt and await for user intervention to proceed.

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Controlling VBA run-time errors (2002/2003)

    I guess I am confused. In your original post you said:
    <hr>What I am looking for is a way to control error handling that occurs within Excel<hr>

    and after providing that info you say:
    <hr>What you are describing is error handling within Excel. I need to control error handling outside of Excel<hr>

    Could you be more specific about what you need? What program do you want error handling in (and if not excel you may need to post in a different forum)?

    Steve

  5. #5
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Controlling VBA run-time errors (2002/2003)

    Using VB I have created a program that takes several pieces of info from a table in an SQL Server database (such as workbook name/location), creates a csv file of values based on info in the database table, loads Excel programatically and opens the Excel workbook specified in the database table. At this point there are 2 files open within Excel, the csv file and the specified workbook. The workbook has code within it's Workbook_Open event that checks to see if there is a csv file open that is structured in a specific way and if it finds such a csv then several additional pieces of code/macros in the workbook are executed. These additional macros use the values in the csv file to make some changes to the workbook and then save a copy of the workbook in a specified location.

    Everything works great so long as no one has altered/added any code/macros in the workbook. If someone does add/alter code in the workbook then the potential for a run-time error can occur. A good example of this is a macro that adds a new worksheet to the workbook and assigns it a name that is identical to a worksheet in the workbook. If this code executes then Excel halts and displays a run-time error message. What I need is a way to prevent Excel from doing this by capturing the error and handling it from within my VB6 applications code.

    The easiest soloution is to prevent anyone from adding to or altering the code in the workbook but that is not possible so I am left looking for an alternative. The ultimate goal is to prevent as many potential run-time erros from occurring which stop Excel and requires manual user intervention. If a run-time error does occur I would want to handle it within my VB6 apps code by stopping the processes that would normally occur, closing Excel and then recording the error message and associated details within a table in my database. This way no manual user intervention is ever required should a run-time error occur.

    Thanks
    Ed

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Controlling VBA run-time errors (2002/2003)

    The code can handle that and more. You can any routine you want in the handler and have different messages and routines run based on whatever errors you can anticipate. You can have all errors pop up a message or do different things with each one.

    Check out the MS Article Basic error handling techniques or google on something like "on error goto" handling excel or even searh the excel/vb board for on error goto for example code.

    Steve

  7. #7
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling VBA run-time errors (2002/2003)

    So why not move all code into your VB application and do it all from there, using the Excel application object? That way you can trap all errors the way you are used to in your own programming environment.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

  8. #8
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Controlling VBA run-time errors (2002/2003)

    You can't use the Error Handling techniques you refer to because Excel does not raise erors back up to the VB6 code that is calling it. If a run-time error occurs within Excel it does not get raised back up to the VB code. If the code were all within in Excel then this would work.

  9. #9
    3 Star Lounger
    Join Date
    Apr 2002
    Location
    Texas, USA
    Posts
    208
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Controlling VBA run-time errors (2002/2003)

    Excel is only one object that the VB6 code manipulates. Other parts work with Word, Crystal Reports and Adobe. Placing error handling within the workbook would require doing so to every workbook that might be called and it still would not address handling errors raised by the other Office products that the VB code calls. I have placed this within the Excel forumn as Excel users seem to push macros/code harder then the other Office products with the exception of Acces and my app does not use Access.

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Controlling VBA run-time errors (2002/2003)

    If you put the "excel code" into a function and call the function to do a task you can have it return FALSE it had an error or even pass a number (eg 0 = error-free, and other numbers for the err type)

    Steve

  11. #11
    Platinum Lounger
    Join Date
    Feb 2001
    Location
    Weert, Limburg, Netherlands
    Posts
    4,812
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Controlling VBA run-time errors (2002/2003)

    You obviously misunderstood my suggestion.

    I meant to say: Don't put the code in Excel at all, put it all in VB(6) and do your error handling in there. MUCH easier to maintain too.
    Jan Karel Pieterse
    Microsoft Excel MVP, WMVP
    www.jkp-ads.com
    Professional Office Developers Association

Posting Permissions

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