On Error handling

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
2,482
Office Version
  1. 365
Platform
  1. Windows
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.
Code:
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:
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?
 

Excel Facts

Select a hidden cell
Somehide hide payroll data in column G? Press F5. Type G1. Enter. Look in formula bar while you arrow down through G.
Why not 'handle' the error yourself?

You could do that by checking PArgs for null values.

Code:
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
 
Upvote 0
Why not 'handle' the error yourself?

You could do that by checking PArgs for null values.

Code:
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:

Code:
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
R/CCD
4OKC4: =TestParamArray("aa", "bb", "cc")
5OKC5: =TestParamArray(1,2,3)
6Null (,)C6: =TestParamArray("aa", ,"bb", "cc")
7Null ()C7: =TestParamArray()
8Null (,)C8: =TestParamArray(,)
9OKC9: =TestParamArray("")
10Null (,)C10: =TestParamArray(null)

<tbody>
</tbody>

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

I think I got all of the cases. Do you see any others?
 
Upvote 0
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.

Code:
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

R/CCDE
4Test CallExpressionComments
5#VALUE!C5: =TestParamArray()Function never gets called
6Error: Missing p1C6: =TestParamArray(,)Missing required P1
7Error: Missing p1C7: =TestParamArray(null)Missing required P1
8Error: P1=(0) Null(,)C8: =TestParamArray(0,)Null ParamArray parameter
9
Error: P1=(0) Null(,)C9: =TestParamArray(0,null)Null ParamArray parameter
10Error: P1=(aa) Null(,)C10: =TestParamArray("aa", ,"bb", "cc")Null ParamArray parameter
11Error: P1=(aa) PA(1)=(bb) PA(2)=(cc) Null(,)C11: =TestParamArray("aa","bb", "cc",)Null ParamArray parameter
12
13P1=(0) No ParamArray parametersC13: =TestParamArray(0)No ParamArray parameters
14P1=() No ParamArray parametersC14: =TestParamArray("")No ParamArray parameters
15P1=(0) PA(1)=()C15: =TestParamArray(0,"")1 ParamArray parameter
16
P1=(0) PA(1)=(aa)C16: =TestParamArray(0,"aa")1 ParamArray parameter
17P1=(0) PA(1)=(aa) PA(2)=(bb) PA(3)=(cc)C17: =TestParamArray(0,"aa", "bb", "cc")3 ParamArray parameters
18P1=(0) PA(1)=(1) PA(2)=(2) PA(3)=(3)C18: =TestParamArray(0,1,2,3)3 ParamArray parameters

<tbody>
</tbody>

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

Forum statistics

Threads
1,213,487
Messages
6,113,943
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top