How to Trigger a Error from a VBA function

Question

I need to trigger(return) an error event from a VBA function, then the calling function of this function can trigger On Error Go to call. E.g

function Test()
 On Error Go to myError:
       TestErr()
 Exit Function

 myerror:
    Test = "Error Triggered"
End Function

Function TestErr()
    ?? 'How to Trigger error here
End Function

Thank You

Russel Yang · Accepted Answer · 2010-06-16 17:24:19Z

23

Err.Raise 5, "optional error source" , "optional error description"

MSDN reference http://msdn.microsoft.com/en-us/library/aa164019%28office.10%29.aspx#odc_tentipsvba_topic3

edited Jun 16, 2010 at 17:24

answered Jun 16, 2010 at 6:09

Russel Yang

2,7013 gold badges21 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Dick Kusleika Over a year ago

I think there should be two commas after the '5'. The second argument is for Source, the third for Description.

bielawski Over a year ago

According to the referenced page it should be: Err.Raise vbObjectError + 5, "optional error source" , "optional error description". But either way I don't see my custom error on the worksheet. I used this to raise an error when the user passes incompatible arguments to a custom function. The cell just reports "A value used in the formula is of the wrong data type" if I enter values that invoke the 'Err.Raise' code. Neither source nor description are visible. Not sure I see the point. 1/0 does the same.

T.J.L. Over a year ago

@bielawski The two parameters are used with the error handler in VBA, via Err.Source and Err.Description. Using the built in error code of 5 would be appropriate if the error was "Invalid procedure call or argument" (which is the Err.Description that goes with Err.Number 5).

user180100 · Accepted Answer · 2010-06-16 06:09:58Z

9

Dirty way: 1 / 0

answered Jun 16, 2010 at 6:09

user180100

2 Comments

A9S6 Over a year ago

Haha!! I initially thought about this when I saw the question

ivan_pozdeev Over a year ago

The message will be unhelpful.

Dick Kusleika · Accepted Answer · 2010-06-16 14:58:01Z

4

Not what you asked, but note that if you want to return an error to a cell from a UDF, use CVErr. Like

Test = CVErr(xlErrNA)

to return #NA

answered Jun 16, 2010 at 14:58

Dick Kusleika

33.3k4 gold badges56 silver badges81 bronze badges

Collectives™ on Stack Overflow

How to Trigger a Error from a VBA function

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related