COUNTA: When nothing becomes something, a cautionary tale

I was testing my FILTERIFS() function (returns a subset array of data filtered by multiple criteria, like COUNTIFS on steroids) when (thanks to my cunning regression test with conditional formatting that highlights the errors) I found that it was giving the wrong answer when embedded inside a COUNTA function.

After an hour or two trying to debug the function in Excel 2010 I went back to my original test case in Excel 2003, and it gave the correct answer. Obviously something must have changed in the code, so I use WinMerge to compare the 2 versions of the code (on is VB6 and the other is VBA). But the code is identical!

It turns out that COUNTA in Excel 2010 behaves differently to all previous Excel versions. (The air turned blue for several minutes when I discovered this).
In previous versions COUNTA does not count empty cells and empty variant arrays, but in Excel 2010 it decides to count empty variant arrays. Here is a test UDF.

Function ReturnEmptyVar() As Variant
Dim vArr(1 To 5, 1 To 1) As Variant
ReturnEmptyVar = vArr
End Function

When you enter =COUNTA(ReturnEmptyVar()) you get 5 in Excel 2010 but zero in Excel 2007 and previous versions.

Bypass: return a reference to an empty cell

Peter Thornton suggested using an actual empty cell instead of a variant. This gets a little trickier because I want the UDF to either return an array of values or a reference to an empty cell, depending on how the filtering criteria results. So the UDF has to look something like this (assuming you have defined name called EmptyCell that refers to an empty cell):

Function ReturnEmpty(blEmpty As Boolean) As Variant
Dim vArr(1 To 10, 1 To 1) As Variant
Dim j As Long
If blEmpty Then
Set ReturnEmpty = ThisWorkbook.Names("EmptyCell").RefersToRange
Else
For j = LBound(vArr) To UBound(vArr)
vArr(j, 1) = Rnd()
Next j
ReturnEmpty = vArr
End If
End Function

The code uses SET to put a reference into the ReturnEmpty variant if blEmpty is true, otherwise it assigns a variant array to ReturnEmpty. If you leave out the SET then the value of the empty cell gets assigned to ReturnEmpty rather than the reference.

If you enter =COUNTA(ReturnEmpty(TRUE)) you get zero in all versions of Excel, and =COUNTA(ReturnEmpty(FALSE)) gives 10 in all versions of Excel.

So it looks like the problem is solved: but unfortunately thats not true!


Function ReturnEmpty2(blEmpty As Boolean) As Variant
Dim vArr(1 To 10, 1 To 1) As Variant
Dim j As Long
If blEmpty Then
Set ReturnEmpty = ThisWorkbook.Names("EmptyCell").RefersToRange
Else
vArr(1, 1) = 1
vArr(4, 1) = 1
ReturnEmpty = vArr
End If
End Function

=COUNTA(ReturnEmpty2(FALSE)) returns 2 in Excel 2007 and previous versions, but returns 10 in Excel 2010.

The moral of the story: setting up a regression test so that its easy to cross-test different versions of Excel and different versions of your code is the way to go!

Do you create and keep Regression tests for your code?

This entry was posted in UDF, VBA. Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s