Results 1 to 9 of 9
  1. #1
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    UDF : =GetSheetName() (Excel 2000 >)

    Hi,
    The attached WB contains a UDF I created to collect the current sheet name. It is simple enough, but I do have a question or two about it.

    Q1:
    Is it necessary to have an error handler here. Is there a possibility that this function could miscalculate?

    Q2:
    If the error handler stays, how can i return a #error? I somehow recall that you can force a error output by using a line of code.

    Tx
    Regards,
    Rudi

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF : =GetSheetName() (Excel 2000 >)

    1) When used in a worksheet formula, it should always return a valid result - after all, the formula is on a worksheet. <img src=/S/smile.gif border=0 alt=smile width=15 height=15>
    But you could use the function in VBA code too. If there is no visible workbook open, referring to ActiveSheet would cause an error.

    2) I don't see how this function could return an error in a cell - see above.

  3. #3
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UDF : =GetSheetName() (Excel 2000 >)

    Tx for the advice.

    Regarding Q2:
    Say the function is a calculative one. How will you force a #error return to the sheet by using code. Is there a built in err.number or err.description that can be used to evaluate the type of error and return #DIV/0 or #NAME or #VALUE...etc?
    Regards,
    Rudi

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF : =GetSheetName() (Excel 2000 >)

    In the first place, you must change the return type of the function to Variant, for a String, Long, Date etc. cannot be an error value:

    Function MyFunc(...) As Variant

    To return an error value, use the CVErr function with the appropriate code, for example

    MyFunc = CVErr(xlErrDiv0)

    Available constants are xlErrDiv0, xlErrNA, xlErrName, xlErrNull and xlErrNum, xlErrRef and xlErrValue.

  5. #5
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UDF : =GetSheetName() (Excel 2000 >)

    If you allow me, I would like to clarify more here!

    Can this technique be improved to return the appropriate error. In the attached is a simple function, but it ONLY returns #DIV/0. Like the formula in the orage area, i would like it to return the applicable error.
    Any ideas?

    Tx
    Regards,
    Rudi

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: UDF : =GetSheetName() (Excel 2000 >)

    For starters, use this line in the error handler section:

    Test = "Error " & Err & ": " & Err.Description

    When you recalculate the sheet, you'll see the error numbers associated with various errors. Keep notes of this.

    When you have a good idea of the errors that can occur, you can use a Select Case statement:

    EH:
    Select Case Err
    Case 11 ' division by zero
    Test = CVErr(xlErrDiv0)
    Case Else
    Test = CVErr(xlErrValue)
    End Select

    You can also check the arguments in the main part of the function, for example:

    If Not IsNumeric(Num1) Or Not IsNumeric(Num2) Then
    Test = CVErr(xlErrValue)
    ElseIf Num2 = 0 Then
    Test = CVErr(xlErrDiv0)
    Else
    Test = Num1 / Num2
    End If

  7. #7
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UDF : =GetSheetName() and Error Handling

    Brilliant. It is perfect now!
    Many tx.

    My last question...(promise...(at least in this thread...for now! <img src=/S/grin.gif border=0 alt=grin width=15 height=15>)

    While I was playing around with, and testing your error hanler responses, i can across this web page. It also eloborates on CVErr.

    BUT

    There is a large grey section 3/4 down the page entitled : Centralizing Error Handling Code. The code is written into a function procedure. I copied this code into a module and purposely made and error in my function procedure so that it jumps to my EH: error handler. The only code in my error handler was Call FileErrors. (This is the name of the centralized function.)

    When I created a function on the speadsheet, forcing it to calc with and error, my function debugged on the error handler saying it could not find FileErrors?? Can I call a function procedure as an error handler?

    Tx
    Regards,
    Rudi

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

    Re: UDF : =GetSheetName() and Error Handling

    That code contains several errors:
    - vbExalamation should be vbExclamation
    - They forgot to put ERR_ before several constants, such as BADFILENAMEORNUMBER

    You should call FileErrors with the error number as argument:

    Dim intRes As Integer
    intRes = FileErrors(Err)

    You can then decide what to do depending on the value of intRes

    But I don't think this error handler is suitable for user-defined worksheet functions, since it displays a message box in some situations. And it classifies all errors that would occur in worksheet functions as UNRECOGNIZED (but you could modify that)

  9. #9
    Gold Lounger
    Join Date
    Feb 2004
    Location
    Cape Town, RSA
    Posts
    3,444
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: UDF : =GetSheetName() and Error Handling

    <img src=/S/bingo.gif border=0 alt=bingo width=15 height=22> - of course!!! This makes sense: intRes = FileErrors(Err)

    Go figure...you are error handling error handling code.
    Cheers

    PS: Keep your <img src=/S/eyeout.gif border=0 alt=eyeout width=15 height=15> for a surprise in the Test Forum!!
    Regards,
    Rudi

Posting Permissions

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