Today in Daily Dose of Excel, Dick wrote about Code Construction. One of his topics was to pull pieces of a routine out into a separate procedure, particularly if that piece of a routine included fiddling with On Error Resume Next and On Error Goto 0.
Dick’s example was to replace this section of code:
On Error Resume Next mcolMyCollection.Add oMyObject, CStr(oMyObject.ID) On Error GoTo ErrorHandler
with this single line
AddToColl mcolMyCollection, oMyObject, CStr(oMyObject.ID)
that calls a small subroutine:
Public Sub AddToColl(col As Collection, vToAdd As Variant, sKey As String)
On Error Resume Next
col.Add vToAdd, sKey
On Error GoTo 0
End Sub
This takes the interrupted error handling out of the main code, preventing a distracted programmer from turning it off altogether.
My suggestion was to turn the AddToColl subroutine into a function, which returns information to the calling sub. It might be a Boolean to indicate whether the action within the procedure was successful, or a Long (a User Defined Type) to indicate what error may have occurred, or as AlexJ suggested, a string containing an error message.
I often take a compound approach, where the function returns True or False to signify success, and an argument passes back to the calling procedure with an applicable message.
The following function is an example. It accepts the address of a range and returns a message as arguments, and returns True if the range meets the criteria.
Function NumericRangeValidator(sAddress As String, sMessage As String) As Boolean
Dim rTest As Range
' false until it passes all tests
NumericRangeValidator = False
On Error Resume Next
Set rTest = Range(sAddress)
On Error GoTo 0
' is address a valid range
If rTest Is Nothing Then
sMessage = "Invalid address."
GoTo ExitFunction
End If
' is there anything in the range
If WorksheetFunction.CountA(rTest) = 0 Then
sMessage = "Empty range."
GoTo ExitFunction
End If
' is the range completely populated with numbers
If WorksheetFunction.Count(rTest) < rTest.Rows.Count * rTest.Columns.Count Then
sMessage = "Range not fully populated with numbers."
GoTo ExitFunction
End If
' if execution got this far, it's a valid range
NumericRangeValidator = True
ExitFunction:
End Function
The message describes the problem with the range (invalid address, empty range, etc.), and only if all tests are passed is the value of the function True.
The following example shows how to use such a function. An If statement tests the value of the function. If it is true, the range is processed further by the calling code (in this case it performs a simple sum). If the function is false, the error message passed back by the function is displayed.
Sub TestNumericRangeValidator()
Dim strAddress As String, strMessage As String
strAddress = Selection.Address
If NumericRangeValidator(strAddress, strMessage) Then
' process the successful range
strMessage = "The sum of values in the range is " & WorksheetFunction.Sum(Range(strAddress))
MsgBox strMessage, vbExclamation
Else
' show the message
MsgBox strMessage, vbCritical
End If
End Sub
This approach is very flexible. The routine can be called from multiple places in a larger application. Each calling location can perform a different action based on the success of the function. The code above added the values, another routine could send it to a chart. If the range is not validated, the code above showed a message box, but it could have simply skipped the message box and died quietly. Or it could have shown the message in a label on a user form to explain to the user why the range they selected on the form cannot be used.
The benefit of passing arguments as well as an actual value for the function is I don’t have to test whether a string matches some stored string to determine whether the range was validated.
