PDA

View Full Version : Programming for the null case



Jennifer
03-10-2013, 03:20 PM
I am writing two little Excel macros.
IsAType checks for valid data in a list of cells. It is passed a data type ("Date", "Num") and a list of cell references. It checks each cell to see if it contains valid data of the specified type. If they all do, it returns "TRUE". Otherwise, it returns "FALSE".
IsNotEmpty checks for empty cells. It is passed a list of cell references. It checks each cell to see if it is empty. If all cells are empty, it returns "TRUE". Otherwise, it returns false.My question is what should I do if it gets passed a null list? Does it make more sense to return TRUE (even though no cells passed the test) or FALSE (even though none failed it).

In my case, I will probably check for the null case and return an error. But I was wondering if there is some general programming principle about what to do with null cases.

Would the null case be True because no cells failed the test? That's how the macro is coded. I start out with a TRUE result and return FALSE as soon as I find a cell that fails the test? If so, then the null case would return TRUE

Or would the null case be false because no cells passed the test (even though none failed, either).

I am leaning toward the first one if I don't make it an error.

GarysStudent
03-10-2013, 03:46 PM
Testing for bad inputs is usually good (especially if you have a strategy for handling the occurrence). For example:

Function whereis(rng As Range) As String
whereis = rng.Parent.Name & rng.Address
End Function

will work just fine if the caller is:

Sub MAIN()
Dim r As Range
Dim s As String
Set r = ActiveCell
s = whereis(r)
MsgBox s
End Sub

but if the Set is missing bad things happen. Why keep the "bad thing" is secret.

Issue an error message.

SamT
03-10-2013, 04:56 PM
My question is what should I do if it gets passed a null list? Does it make more sense to return TRUE (even though no cells passed the test) or FALSE (even though none failed it).

The answer depends on the usage. If you're writing UDF's for use in formulas;
=IF(IsAType("Date", A1:B32),ThenDoTHis,ElseDoThat)
and the user fails to input a range reference, Excel will raise an error at the formula bar. Always assumming you didn't make the argument optional.

If you're planning to use them as macros, you definately want an instructive MsgBox to popup on any errors.

If you're writing them for use in other VBA procedures, you have to decide what types of situations your main procedure will encounter that can cause it to leave the argument null.

If the only situation is that you mis-coded the procedure, then a temporary MsgBox in the function can make sense. Otherwise, you may want the functions to return a VbTristate or an MsoTristate, and add code to your main to interpret the results.
Function IsNotEmpty(RangeToCheck As Range)As VbTristate
DoThingsHere
End Function

Sub MainProcedure()
Select Case IsNotEmpty(Rng)
Case vbTrue
DoTrueStuff
Case vbFalse
DoFalseStuff
Case vbUseDefault
DoNullStuff
End Select
End Sub


You can write your own Enum
Enum MyTristate
myFalse = 0
myTrue = 1
myNull = -1
End Enum

Function IsNotEmpty(RangeToCheck As Range) As MyTristate
and Test in the Main Procedure for those.

I'm using VBA5, YMMV

Jennifer
03-10-2013, 05:17 PM
Thanks, Sam, some excellent points.

The argument list is a PafamArray, so it is indeed optional.

SamT
03-10-2013, 05:45 PM
Test before call:
If UBound(ParamArray) > 0 Then Result = Function(ParamArray)

Use a Flag

Dim MyFlag As Boolean
MyFlag = False

LoopToAddItemsToParamArray
If ItemToAdd exists Then MyFlag = True
.
.
.
Loop

If MyFlag Then Function(ParamArray)