+ Reply to Thread
Results 1 to 3 of 3

Managing errors in VBA

  1. #1

    Managing errors in VBA

    I am in the process of building an excel template which links
    automatically to an excel database. A macro is involved in transferring
    some of the data to the database. 15 different people will have a
    template and it is possible that 2 of them may try to update the
    database at exactly the same time. This would result in a macro error.
    Is it possible that instead of showing the error and taking the user
    into VBA, a flag could show up in the template telling the user to try
    again in 5 minutes?

    Any help would be gratefully received!

    James


  2. #2
    Tom Ogilvy
    Guest

    RE: Managing errors in VBA

    sounds like something you would incorporate in your code in terms of addin
    code to see if the database can be updated before trying to update it.
    Without knowing how you are trying to update the database, it would be hard
    to provide specifics. If you want to find if a workbook on a shared drive is
    opened by another user you could use

    http://support.microsoft.com?kbid=138621
    XL: Macro Code to Check Whether a File Is Already Open

    http://support.microsoft.com?kbid=291295
    XL2002: Macro Code to Check Whether a File Is Already Open

    http://support.microsoft.com?kbid=213383
    XL2000: Macro Code to Check Whether a File Is Already Open

    --
    Regards,
    Tom Ogilvy


    "[email protected]" wrote:

    > I am in the process of building an excel template which links
    > automatically to an excel database. A macro is involved in transferring
    > some of the data to the database. 15 different people will have a
    > template and it is possible that 2 of them may try to update the
    > database at exactly the same time. This would result in a macro error.
    > Is it possible that instead of showing the error and taking the user
    > into VBA, a flag could show up in the template telling the user to try
    > again in 5 minutes?
    >
    > Any help would be gratefully received!
    >
    > James
    >
    >


  3. #3
    Mark
    Guest

    Re: Managing errors in VBA


    [email protected] wrote:
    > I am in the process of building an excel template which links
    > automatically to an excel database. A macro is involved in transferring
    > some of the data to the database. 15 different people will have a
    > template and it is possible that 2 of them may try to update the
    > database at exactly the same time. This would result in a macro error.
    > Is it possible that instead of showing the error and taking the user
    > into VBA, a flag could show up in the template telling the user to try
    > again in 5 minutes?
    >
    > Any help would be gratefully received!
    >
    > James



    Here is an example


    Sub Example()

    Dim SomeStr As String
    Dim NewRng As Range

    On Error GoTo ExitQuietly
    Set NewRng = Application.ActiveCell
    'Do something that will produce an error
    Do
    SomeStr = NewRng
    Set NewRng = NewRng.Offset(-1, 0)
    'MsgBox NewRng.Address
    Loop
    Exit Sub
    ExitQuietly:

    If Err.Number = 1004 Then
    MsgBox Err.Description & vbLf & "This has been a test, but try
    again in 5 minutes.", vbOKOnly
    Else
    Resume
    End If

    End Sub

    In this case, I knew the error code to expect when I tried to refer to
    an object that didn't exist. So in that case ERR# 1004 I displayed a
    msg similar to the one you would want to. All other errors will simply
    skip through and resume. I assume this is what you want to do. Hope
    that helped...


+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1