On Error handling

[JenniferMurphy]

This web page,

http://www.cpearson.com/excel/ErrorHandling.htm

has this to say about On Error GoTo 0

The first form, On Error Goto 0, is the default mode in VBA. This indicates that when a run time error occurs VBA should display its standard run time error message box, allowing you to enter the code in debug mode or to terminate the VBA program. When On Error Goto 0 is in effect, it is the same as having no enabled error handler. Any error will cause VBA to display its standard error message box.

In my code, it isn't working. This line causes a Value error in the calling cell.

Parms = Split(PArgs(i), "=")

VBA does not "display its standard run time message box" as claimed above. I discovered by trapping the error that it is actually an Error 448 (missing parameter) caused by passed a null (,,) argument. Am I doing something wrong or is the text wrong?



Second question. Since the above is not working, I decided to write my own error handler. Here's my code:

Public Function WtdRtg(p1, p2, ParamArray PArgs())

 . . .

On Error GoTo PAErrorHandler:

Dim i As Integer                'Loop index
Dim Parms() As String           'Array for splitting parameters
For i = 0 To UBound(PArgs, 1)   'Loop thru the parameter array
  Parms = Split(PArgs(i), "=")    'Split off the next parameter

 . . .

Next i
GoTo PAErrorHandlerDone

PAErrorHandler: Stop
MsgBox "Invalid paramarray parameter (#" & i + 1 & ")"
WtdRtg = CVErr(xlErrValue)
Exit Function
PAErrorHandlerDone:

 . . .

End Function

Is there a better way to handle this situation?

 

[Kyle123]

No, you don't get debug error dialogs when calling UDFs from the worksheet

 

[Norie]

Why not 'handle' the error yourself?

You could do that by checking PArgs for null values.

For pindex = LBound(PArgs) To UBound(PArgs)
    If IsNull(PArgs(pindex)) Then
        ' return appropriate value to function to indicate error
        Exit Function
    End If
Next pindex

 

[JenniferMurphy]

No, you don't get debug error dialogs when calling UDFs from the worksheet

Ok, thanks

 

[JenniferMurphy]

Why not 'handle' the error yourself?

You could do that by checking PArgs for null values.

Yeah, that's probably a better solution. Thanks

 

[JenniferMurphy]

Why not 'handle' the error yourself?

You could do that by checking PArgs for null values.

For pindex = LBound(PArgs) To UBound(PArgs)
    If IsNull(PArgs(pindex)) Then
        ' return appropriate value to function to indicate error
        Exit Function
    End If
Next pindex

It turns out that 'handling' the error myself is a bit more complicated than you suggest.

Here's a little test function:

Function TestParamArray(ParamArray pargs()) As String

Dim i As Integer                        'Loop index
Dim Parms() As String                   'Array for splitting parameters

If UBound(pargs) < 0 Then
  MsgBox "Null Paramarray parameter ()"
  TestParamArray = "Null ()"
  Exit Function
End If

For i = LBound(pargs) To UBound(pargs)  'Loop thru the parameter array
  If IsError(pargs(i)) Then
    MsgBox "Null Paramarray parameter (,)"
    TestParamArray = "Null (,)"
    Exit Function
  End If
  MsgBox "ParamArray parameter #" & i + 1 & "=" & pargs(i)
  Parms = Split(pargs(i), "=")            'Split off the next parameter
Next i

TestParamArray = "OK"
End Function

And here are some results testing this code
[TABLE="class: grid, width: 450"]
<tbody>[TR]
[TD="align: center"]R/C[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D
[/TD]
[/TR]
[TR]
[TD="align: center"]4[/TD]
[TD="align: center"]OK[/TD]
[TD]C4: =TestParamArray("aa", "bb", "cc")[/TD]
[/TR]
[TR]
[TD="align: center"]5[/TD]
[TD="align: center"]OK[/TD]
[TD]C5: =TestParamArray(1,2,3)[/TD]
[/TR]
[TR]
[TD="align: center"]6[/TD]
[TD="align: center"]Null (,)[/TD]
[TD]C6: =TestParamArray("aa", ,"bb", "cc")[/TD]
[/TR]
[TR]
[TD="align: center"]7[/TD]
[TD="align: center"]Null ()[/TD]
[TD]C7: =TestParamArray()[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]Null (,)[/TD]
[TD]C8: =TestParamArray(,)[/TD]
[/TR]
[TR]
[TD="align: center"]9[/TD]
[TD="align: center"]OK[/TD]
[TD]C9: =TestParamArray("")[/TD]
[/TR]
[TR]
[TD="align: center"]10[/TD]
[TD="align: center"]Null (,)[/TD]
[TD]C10: =TestParamArray(null)[/TD]
[/TR]
</tbody>[/TABLE]

Even so, I think this is better than using On Error.

I think I got all of the cases. Do you see any others?

 

[JenniferMurphy]

The above solution was not quite complete. It did not allow for any required parameters before the ParamArray parameters. Here is what I think is a more complete solution.

Function TestParamArray(P1, ParamArray pargs()) As String

Dim i As Integer                        'Loop index
Dim Parms() As String                   'Array for splitting parameters

If IsError(P1) Then
  TestParamArray = "Error: Missing p1"
  Exit Function
End If
  
TestParamArray = "P1=(" & P1 & ")"     'Start result string

If UBound(pargs) < 0 Then     'No paramarray parameters
  TestParamArray = TestParamArray & " No ParamArray parameters"
  Exit Function
End If

For i = LBound(pargs) To UBound(pargs)  'Loop thru the parameter array
  If IsError(pargs(i)) Then
    TestParamArray = "Error: " & TestParamArray & " Null(,)"
    Exit Function
  End If
  Parms = Split(pargs(i), "=")            'Split off the next parameter
  TestParamArray = TestParamArray & " PA(" & i + 1 & ")=(" & pargs(i) & ")"
Next i

End Function

And here is the test data

[TABLE="class: grid, width: 850"]
<tbody>[TR]
[TD]R/C[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]Test Call[/TD]
[TD]Expression[/TD]
[TD]Comments[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]#VALUE![/TD]
[TD]C5: =TestParamArray()[/TD]
[TD]Function never gets called[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]Error: Missing p1[/TD]
[TD]C6: =TestParamArray(,)[/TD]
[TD]Missing required P1[/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Error: Missing p1[/TD]
[TD]C7: =TestParamArray(null)[/TD]
[TD]Missing required P1[/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Error: P1=(0) Null(,)[/TD]
[TD]C8: =TestParamArray(0,)[/TD]
[TD]Null ParamArray parameter[/TD]
[/TR]
[TR]
[TD]9
[/TD]
[TD]Error: P1=(0) Null(,)[/TD]
[TD]C9: =TestParamArray(0,null)[/TD]
[TD]Null ParamArray parameter[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Error: P1=(aa) Null(,)[/TD]
[TD]C10: =TestParamArray("aa", ,"bb", "cc")[/TD]
[TD]Null ParamArray parameter[/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD]Error: P1=(aa) PA(1)=(bb) PA(2)=(cc) Null(,)[/TD]
[TD]C11: =TestParamArray("aa","bb", "cc",)[/TD]
[TD]Null ParamArray parameter[/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD]P1=(0) No ParamArray parameters[/TD]
[TD]C13: =TestParamArray(0)[/TD]
[TD]No ParamArray parameters[/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD]P1=() No ParamArray parameters[/TD]
[TD]C14: =TestParamArray("")[/TD]
[TD]No ParamArray parameters[/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD]P1=(0) PA(1)=()[/TD]
[TD]C15: =TestParamArray(0,"")[/TD]
[TD]1 ParamArray parameter[/TD]
[/TR]
[TR]
[TD]16
[/TD]
[TD]P1=(0) PA(1)=(aa)[/TD]
[TD]C16: =TestParamArray(0,"aa")[/TD]
[TD]1 ParamArray parameter[/TD]
[/TR]
[TR]
[TD]17[/TD]
[TD]P1=(0) PA(1)=(aa) PA(2)=(bb) PA(3)=(cc)[/TD]
[TD]C17: =TestParamArray(0,"aa", "bb", "cc")[/TD]
[TD]3 ParamArray parameters[/TD]
[/TR]
[TR]
[TD]18[/TD]
[TD]P1=(0) PA(1)=(1) PA(2)=(2) PA(3)=(3)[/TD]
[TD]C18: =TestParamArray(0,1,2,3)[/TD]
[TD]3 ParamArray parameters[/TD]
[/TR]
</tbody>[/TABLE]

Please let me know if you see any errors or omissions or a better way.