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.
TV says
Dang, I need to brush up on VBA. I didn’t realize reassigning the input variable inside the subfunction also reassigns the input variable in the calling function. That’s a handy way to pass multiple outputs. I figured it would need to be a global variable for that to work.
Jon Peltier says
I didn’t mention it, and I should have, that by default a value is passed into a procedure ByRef, which means a reference to the memory containing the value is passed. This memory is changed, so when execution returns to the main procedure, the referenced memory contains the updated value.
You can pass value variables ByVal, which means just a copy of the value is passed, and it is lost when execution reverts to the calling procedure.
Someone correct me if I’m wrong, but I think object variables are always passed ByRef.
JP says
I believe you’re correct Jon, but I can’t find the page in PED that mentions it. I only found the bit on page 63 that talks about ByRef and ByVal. But I do believe it’s mentioned later in the text.
I like the boolean approach best, especially for the straightforward routines that either should work or completely fail. Then just test for that in your calling procedure.
Jon Peltier says
There is a difference in how objects behaved when passed ByVal or ByRef, and it’s explained in Objects and ByVal or ByRef | O’Reilly Media. But it took a complex example to show this difference. For the most part, in simple cases, there is no measurable difference.
Bob Phillips says
I think that all good experienced developers, of which we all know that you are one, should always explicitly declare whether a parameter is being passed ByVal or ByRef. Helping to spread good practices is part of the role IMO .
As I mentioned in DDoE, I have gotten into the habit of rarely passing a function result back as a function result, I rather pass all results back (I often require more than one, even if only a non-critical return code in addition to the functional value) as ByRef parameters. My function then passes back whether it was a critical failure or not. For example
and call it like so
If Not MyFunction("Bob", mpAge, mpSex) then 'handle this error Exit Function End IF 'now process the mpAge and mpSex variables returnedJon Peltier says
Bob –
You’re right, I should start showing ByVal and ByRef. In fact, I should probably start using them in my own code. I’ll start tomorrow.
Anonymous says
Thank you for tidying up my code, presumably there are tags to do that, what are they [code]…[/code]?
You didn’t spot that I missed the Function keyword in the Function signature though
Jon Peltier says
Public Function MyFunction would have sounded redundant even if I had noticed (O Captain, my Captain), but it’s fixed now.
The code uses html tags (<pre> </pre>), none of the square bracket tags, because I really don’t like how any of those plugins work. Until I get four hundred comments a day, it’s not too burdensome to fix it myself. Those of us with OCD have lots of energy for that sort of thing.
Doug Glancy says
I notice that Bob sets the function to true at the beginning and changes the value to false in the error handler, whereas Jon explicitly sets it to false and uses Goto to break out of the function before it turns true if there’s an error. I’m used to doing it Jon’s way (although I’ve stopped explicitly setting the function to false). What are the advantages of your way, Bob?
Jon Peltier says
Doug –
I’l wait for Bob’s take on this, but I think it’s a kind of half-empty, half-full situation. I don’t think there’s any real reason to do it one way or the other. It just makes sense in my mind not to claim success until you’ve ruled out failure.
Doug Glancy says
I thought about this some more, and it seems like starting at False makes it easier to return the reasons that the function fails, either runtime or validation errors.
Jon Peltier says
With every test you have an opportunity to define a piece of information to pass back to the calling routine. I don’t think where you start makes much difference.