Obviously error handling in general is a big topic, and what the best practice is depends a lot on the capabilities of the language you’re working with and how the routine you’re coding fits in with other routines. So I’ll constrain my answer to VBA (used within Excel) and library-type routines of the sort you’re describing.
Exceptions vs. Error Codes in Library Routines
In this case, I would not use a return code. VBA supports a form of exception handling that, while not as powerful as the more standard form found in C++/Java/??.NET, is pretty similar. So the advice from those languages generally applies. You use exceptions to tell calling routines that the called routine can’t do it’s job for whatever reason. You handle exceptions at the lowest level where you can do something meaningful about that failue.
Bjarne Stroustrup gives a very good explanation of why exceptions are better than error codes for this kind of situation in this book. (The book is about C++, but the principles behind C++ exception handling and VBA error handling are the same.)
http://www2.research.att.com/~bs/3rd.html
Here is a nice excerpt from Section 8.3:
When a program is composed of separate
modules, and especially when those
modules come from separately developed
libraries, error handling needs to be
separated into two distinct parts: [1]
The reporting of error conditions that
cannot be resolved locally [2] The
handling of errors detected elsewhere
The author of a library can detect
runtime errors but does not in general
have any idea what to do about them.
The user of a library may know how to
cope with such errors but cannot
detect them – or else they would be
handled in the user’s code and not
left for the library to find.
Sections 14.1 and 14.9 also address exceptions vs. error codes in a library context. (There is a copy of the book online at archive.org.)
There is probably lots more about this on stackoverflow. I just found this, for example:
Exception vs. error-code vs. assert
(There can be pitfalls involving proper management of resources that must be cleaned up when using exceptions, but they don’t really apply here.)
Exceptions in VBA
Here is how raising an exception looks in VBA (although the VBA terminology is “raising an error”):
Function AddArrays(arr1, arr2)
Dim i As Long
Dim result As Variant
' Some error finding code here, e.g.
' - Are input arrays of same size?
' - Are input arrays numeric? (can't add strings, objects...)
' - Etc.
'Assume errorsFound is a variable you populated above...
If errorsFound Then
Call Err.Raise(SOME_BAD_INPUT_CONSTANT) 'See help about the VBA Err object. (SOME_BAD_INPUT_CONSTANT is something you would have defined.)
End If
' If no errors found, do the actual work...
ReDim result(LBound(arr1) To UBound(arr1))
For i = LBound(arr1) To UBound(arr1)
result(i) = arr1(i) + arr2(i)
Next i
AddArrays = result
End Function
If this routine doesn’t catch the error, VBA will give other routines above it in the call stack a chance to (See this: VBA Error “Bubble Up”). Here is how a caller might do so:
Public Function addExcelArrays(a1, a2)
On Error Goto EH
addExcelArrays = AddArrays(a1, a2)
Exit Function
EH:
'ERR_VBA_TYPE_MISMATCH isn't defined by VBA, but it's value is 13...
If Err.Number = SOME_BAD_INPUT_CONSTANT Or Err.Number = ERR_VBA_TYPE_MISMATCH Then
'We expected this might happen every so often...
addExcelArrays = CVErr(xlErrValue)
Else
'We don't know what happened...
Call debugAlertUnexpectedError() 'This is something you would have defined
End If
End Function
What “do something meaningful” means depends on the context of your application. In the case of my caller example above, it decides that some errors should be handled by returning an error value that Excel can put in a worksheet cell, while others require a nasty alert. (Here’s where the case of VBA within Excel is actually not a bad specific example, because lots of applications make a distinction between internal and external routines, and between exceptions you expect to be able to handle and error conditions that you just want to know about but for which you have no response.)
Don’t Forget Assertions
Because you mentioned debugging, it’s also worth noting the role of assertions. If you expect AddArrays to only ever be called by routines that have actually created their own arrays or otherwise verified they are using arrays, you might do this:
Function AddArrays(arr1, arr2)
Dim i As Long
Dim result As Variant
Debug.Assert IsArray(arr1)
Debug.Assert IsArray(arr2)
'rest of code...
End Function
A fantastic discussion of the difference between assertions and exceptions is here:
Debug.Assert vs Exception Throwing
I gave an example here:
Some VBA Advice About General Array Handling Routines
Finally, as a VBA-specific note, there are VBA variants and arrays come with a number of pitfalls that must be avoided when you’re trying to write general library routines. Arrays might have more than one dimension, their elements might be objects or other arrays, their start and end indices might be anything, etc. Here is an example (untested and not trying to be exhaustive) that accounts for some of that:
'NOTE: This has not been tested and isn't necessarily exhaustive! It's just
'an example!
Function addArrays(arr1, arr2)
'Note use of some other library functions you might have...
'* isVect(v) returns True only if v is an array of one and only one
' dimension
'* lengthOfArr(v) returns the size of an array in the first dimension
'* check(condition, errNum) raises an error with Err.Number = errNum if
' condition is False
'Assert stuff that you assume your caller (which is part of your
'application) has already done - i.e. you assume the caller created
'the inputs, or has already dealt with grossly-malformed inputs
Debug.Assert isVect(arr1)
Debug.Assert isVect(arr2)
Debug.Assert lengthOfArr(arr1) = lengthOfArr(arr2)
Debug.Assert lengthOfArr(arr1) > 0
'Account for VBA array index flexibility hell...
ReDim result(1 To lengthOfArr(arr1)) As Double
Dim indResult As Long
Dim ind1 As Long
ind1 = LBound(arr1)
Dim ind2 As Long
ind2 = LBound(arr2)
Dim v1
Dim v2
For indResult = 1 To lengthOfArr(arr1)
'Note implicit coercion of ranges to values. Note that VBA will raise
'an error if an object with no default property is assigned to a
'variant.
v1 = arr1(ind1)
v2 = arr2(ind2)
'Raise errors if we have any non-numbers. (Don't count a string
'with numeric text as a number).
Call check(IsNumeric(v1) And VarType(v1) <> vbString, xlErrValue)
Call check(IsNumeric(v2) And VarType(v2) <> vbString, xlErrValue)
'Now we don't expect this to raise errors.
result(indResult) = v1 + v2
ind1 = ind1 + 1
ind2 = ind2 + 1
Next indResult
addArrays = result
End Function