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.

Related Posts:

• Get Open or Save-As Filename
• Robust VBA Save-As Technique
• File Name Functions
• Build an Excel Add-In 4 – Create the Dialog
• UDF to Calculate an Arbitrary Formula
• Alternative to Excel’s Flaky RefEdit Control
• Extend Range to Add New Series (VBA)
• Build an Excel Add-In 2 – Enhanced Functionality
• Label Each Series in a Chart
• VBA to Split Data Range into Multiple Chart Series