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.