On Error handling

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,306
Office Version
365, 2016, 2007
Platform
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?
 

Some videos you may like

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,566
Office Version
365
Platform
Windows
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
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,306
Office Version
365, 2016, 2007
Platform
Windows
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

Well-known Member
Joined
Jul 23, 2011
Messages
1,306
Office Version
365, 2016, 2007
Platform
Windows
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?
 

JenniferMurphy

Well-known Member
Joined
Jul 23, 2011
Messages
1,306
Office Version
365, 2016, 2007
Platform
Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,100,193
Messages
5,473,071
Members
406,845
Latest member
JohnR123

This Week's Hot Topics

Top