Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Lounger
    Join Date
    Apr 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Error Handler (VBA )

    Hi,

    In an excel Workbook, I am trying to insert two sheets - SV, EV. If these do not exist, then error handling is required I am trying to put two error handling conditions in my code as different code is required on these worksheets. My code is like:

    ....code....
    On error go to nosuchsheetSV
    ...code....
    NoSuchSheetSV:
    Sheets.Add.Name = "SV"
    ....code....

    On error go to nosuchsheetEV
    NoSuchSheetEV:
    Sheets.Add.Name = "EV"
    ....code....

    My problem is that while the first part (SV) works fine, the second part (EV) error handling does not work and it gives Run Time Error 9: Subscript out of range

    Any help will be appreciatied.

    Thanks

  2. #2
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Error Handler (VBA )

    Do you do a Resume after handling the error?

    If not then have a look at this technet article for basic guidelines on how to write an error handler.

    StuartR

  3. #3
    Lounger
    Join Date
    Apr 2005
    Posts
    32
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Handler (VBA )

    Hi

    I do not directly resume after handling. On receiving the error, the control shifts to the error handling statement and executes the code written therein.

  4. #4
    Plutonium Lounger
    Join Date
    Nov 2001
    Posts
    10,550
    Thanks
    0
    Thanked 7 Times in 7 Posts

    Re: Error Handler (VBA )

    The important thing is that you Resume from the first error before declaring the new error handler for the second one.

    StuartR

  5. #5
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts

    Re: Error Handler (VBA )

    Hi,
    I think what you need is something like this:

    <pre>On Error Goto ErrHandler
    strSheet = "SV"
    Set wks = Worksheets(strSheet)
    .. do whatever
    strSheet = "EV"
    Set wks = Worksheets(strSheet)
    .. do whatever


    ErrHandler:
    Set wks = Worksheets.Add
    wks.Name = strSheet
    Resume Next

    </pre>


    HTH
    Regards,
    Rory
    Microsoft MVP - Excel.

  6. #6
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Handler (VBA )

    In addition to the other replies, you can do this without an error handling routine:

    <code>
    Dim oSh As Worksheet
    On Error Resume Next
    Set oSh = Nothing
    Set oSh = Worksheets(SV)
    If oSh Is Nothing Then
    Set oSh = Sheets.Add(After:=Sheets(Sheets.Count))
    oSh.Name = "SV"
    End If
    Set oSh = Nothing
    Set oSh = Worksheets(EV)
    If oSh Is Nothing Then
    Set oSh = Sheets.Add(After:=Sheets(Sheets.Count))
    oSh.Name = "EV"
    End If
    On Error GoTo 0
    </code>
    Legare Coleman

  7. #7
    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 (VBA )

    > If these do not exist, then error handling is required

    Not so.

    Error handling is not required as the sample code below shows.
    Besides which, as this thread evidents, error-handling is tricky.
    Staying in control of the situation is not tricky.

    I'll accept that the dearth of "exists" native functions is a sad fact of life, but not that we need "On Error" handling to circumvent the inadequacies of VBA design.


    <pre>Public Function blnSheetExists(strSheetName)
    Dim wks As Worksheet
    For Each wks In Application.ActiveWorkbook.Worksheets
    If strSheetName = wks.Name Then
    blnSheetExists = True
    Exit For
    Else
    End If
    Next wks
    'Sub TESTblnSheetExists()
    ' If blnSheetExists("Sheet4") Then
    ' MsgBox "sheet exists"
    ' Else
    ' MsgBox "sheet DOES NOT exist"
    ' End If
    'End Sub
    End Function</pre>


  8. #8
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Handler (VBA )

    I'm too lazy to do a timing test (and I'm not an Excel user), but my gut tells me Mr. Coleman's existence test is likely to be faster than a VB loop through the open worksheets. I'm curious as to whether you disagree or maybe just missed the Coleman post.

  9. #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: Error Handler (VBA )

    > but my gut tells me
    Steve, that's an odd place to keep your logic, but you are absolutely right. Also about laziness. It would be a waste of time to even time the code.

    In the first place, my point was that "On Error" is not required here; that is a false premise. On Error can be used, and to good effect (as the thread shows), but we ought not to lapse into thinking "On Error" whenever any sort of problem arises.

    Mr. Coleman (I had to work out who that was) his test IS going to be faster than a loop. No doubt about it in my mind, either. Interesting to note, though, that the code first has to disable error testing with an On Error Resume Next. Heh heh. The test for Nothing is substituting one test for another.

    In the second place, about timing.
    Neraly 40 years ago I enhanced an assembler (Autocoder) loop in a corporate payroll. BHP. Largest private employer in the country. My boss asked me how much time I'd saved. 11.5 microseconds, I think it was. Bob Moon had me multiply that by 26 payroll periods in a year and multiply THAT by the number of BHP employees, nationwide. It is a lesson I (obviously) never forgot.

    I'm prepared to bet you, oh, let's see, one Regular Expression to a Field Code of your choice that the time spent discussing On Error in this thread far outweighs all the time that could ever be spent using my loop against all the forum readers who might actually make use of it. I can hear your teeth grinding from here.

    Bottom Line: Our correspondent has spent more time trying to resolve On Error before their original post to the thread than could ever be lost by just avoiding the On Error in the first place; perhaps by a loop such as mine.

    Yrs in abject humility etc etc.

  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 (VBA )

    >without an error handling routine:
    <font color=448800>[pedant]But not without Error Handling[/pedant]</font color=448800>
    (grin)

  11. #11
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    5,894
    Thanks
    0
    Thanked 84 Times in 80 Posts

    Re: Error Handler (VBA )

    Well, if we're going to be picky...
    "the code first has to disable error testing with an On Error Resume Next."
    On Error Resume Next does not disable error testing - it just tells you to carry on at the next statement rather than go to a separate part of the code. The Err.Number still changes, after all. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>
    Regards,
    Rory
    Microsoft MVP - Excel.

  12. #12
    Platinum Lounger
    Join Date
    Nov 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    5,016
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Error Handler (VBA )

    Here's another take on it, without the need for any error handling.
    <pre>Option Explicit

    Public Function GetRef(strSheetName As String) As Worksheet

    <font color=448800>'Returns a reference to a named worksheet.
    'Creates the worksheet if it doesn't already exist.</font color=448800>

    Dim wks As Worksheet
    Dim idx As Long

    With Application.ThisWorkbook

    For Each wks In .Worksheets
    If wks.Name = strSheetName Then idx = wks.Index: Exit For
    Next wks

    If idx = 0 Then
    Set GetRef = Worksheets.Add(After:=Sheets(Sheets.Count))
    GetRef.Name = strSheetName
    Else
    Set GetRef = .Worksheets(idx)
    End If

    End With

    End Function
    </pre>

    Use it as:
    Set myRef = GetRef("EV")

    or simply:
    GetRef "EV"

    I adapted this from some "bulletproofing" I'd done with Word's Document Variable collection. Since Worksheets() is just a collection too, the .Add() method can be similarly prevented from generating errors. Returning an object reference to Worksheets(strSheetName) is just an optional bonus.

    Alan

  13. #13
    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 (VBA )

    > Well, if we're going to be picky...
    I wasn't being picky; I was being pedantic (grin)

  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 (VBA )

    > without the need for any error handling.
    An elegant solution that provides extra value.
    All done without Error- Handling.
    My point in <post:=618,091>post 618,091</post:> is vindicated: Error handling is not mandatory.
    Surprise: Often when we avoid the cheap route of error handling, we gain real benefits.
    Thanks.

  15. #15
    5 Star Lounger st3333ve's Avatar
    Join Date
    May 2003
    Location
    Los Angeles, California, USA
    Posts
    705
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Error Handler (VBA )

    Well, I don't keep all my logic there. Just the overflow. <img src=/S/grin.gif border=0 alt=grin width=15 height=15>

    When I first read your BHP story I assumed that multiplying 11.5 microseconds as you described added up to a substantial period of time but, based on the rest of your post, I assume it was the opposite lesson that was learned.

    Point well made, but on the other hand, if you pick up a technique like Coleman's and then use it over and over (e.g., by putting it in a blnSheetExists function that you call whenever appropriate), the savings can eventually add up. Because your post followed Coleman's, I was just curious as to whether there was a potential glitch or downside to his approach that you hadn't spelled out in your post.

    On your general point, I have a mild preference for avoiding error-handling "solutions" to the problem of "missing" VB functions, but only because it feels more straightforward and will generally make the code easier to follow. (On the other hand, if the error-handling code is in something like a blnSheetExists function that's really minimal and that, once created, I may well never look at again, the magnitude of the "easier-to-follow code" consideration gets pretty tiny, no?)

    But I don't share your inclination to avoid error handling unless you basically can't avoid it. I guess I make a distinction between the language designer and the language user in this regard. I definitely agree with you that the ideal language designer would build in enough "Exists" and other functions that the language user would rarely be tempted to resort to error-handling approaches to common tasks that don't really involve "errors." On the other hand, to the extent that the language designer falls short of that ideal, I'd say I feel much less strongly than you that the ideal language user should still go out of his or her way (whenever possible) to avoid what you call the "cheap" resort to error-handling.

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
  •