Results 1 to 7 of 7
  1. #1
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Generating errors for Excel worksheet functions

    What's the best way of returning an error to an Excel worksheet from a user defined function?

    For example, if a parameter is out of range, I'd like to return a #VALUE! error to the worksheet. I can certainly do this as text, but not if the return type isn't String. I could refrain from checking the parameter's validity and let Excel/VBA generate the error, but then some unusual cases may slip through (eg: a million recursions).

    Is there another, better way? And if so, what are the codes for #VALUE! and #N/A! ?

    Thanks in advance,

    Paulius

  2. #2
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating errors for Excel worksheet functions

    Look up the Raise method of the error object. You can use it to raise an error code to the calling routine.
    Charlotte

  3. #3
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Generating errors for Excel worksheet functions

    Charlotte wrote:
    >Look up the Raise method of the error object. You can use it to raise an error code to the calling routine.

    Except that the "calling routine" is the Excel application.

    Although this might be a perfectly good answer for a VBA procedure (thanks), it's lacking some important information when applied to an Excel/VBA function.

    For example, where's the list of standard error codes? Which ones should I use to generate #VALUE! and #N/A! errors in the worksheet? Or do Excel built-in functions handle these errors in a different way?

    Any advances?

    Paulius

  4. #4
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Generating errors for Excel worksheet functions

    I see, you're trying to actually generate errors within the cells in the worksheet, not just within code, right? In that case, you might be better advised to post the question in the Excel forum rather than here. In most cases, people are trying to avoid seeing those errors in worksheets, rather than to specifically create them.
    Charlotte

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

    Re: Generating errors for Excel worksheet functions

    The following should get you started on what you want to do:

    <pre>Public Function ReturnError() As Variant
    ReturnError = CVErr(xlValue)
    End Function
    </pre>

    Legare Coleman

  6. #6
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Dublin, Ireland, Republic of
    Posts
    2,697
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Generating errors for Excel worksheet functions

    Paulius,

    There is a series of xlConstants for errors, e.g. xlErrDiv0, xlErrNA. You need to use these rather than creatinng strings to indicate an error. "#N/A" returned as a string from a function would not return a correct value in say a ISNA() function call. Instead set the return value to = CVErr(xlErrNA), or whatever is appropriate. Any of the following an be preceded by xlErr, :-
    Div0, NA , Name , Null , Num , Ref , Value.

    Andrew C

  7. #7
    Star Lounger PStepanas's Avatar
    Join Date
    Jan 2001
    Location
    Melbourne, Australia
    Posts
    90
    Thanks
    4
    Thanked 2 Times in 2 Posts

    Re: Generating errors for Excel worksheet functions

    Excellent!

    Big thank you to Legare and Andrew!

    Paulius

Posting Permissions

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