Friday, September 26, 2014

Shortcoming of Object Model Affects SUBTOTAL()-like UDFs

Introduction

In three other posts (AVERAGE() UDF excluding hidden columns...plus SUM(), COUNT(), etc. , SUBTOTAL()-like UDF for rows with hidden columnsSUBTOTAL()-like UDF for cases when Rows and Columns may be hidden) UDFs were presented that complemented and replaced the SUBTOTAL() worksheet function.  As often happens, with these came an unwanted discovery.

Observation: Formulas don't always update

There is one thing these functions won't do that I would have hoped.  They do not recalculate every time a change is made  even though Application.Volatile is the first line of each.   

Investigation

Further observation showed that when hiding/showing one or more columns the formulas using the UDFs did not update.  However, they update when data changes and when rows are hidden, whether using filters or the Hide row option.

By putting breakpoints on all of the Worksheet and Workbook then hiding/showing rows and then columns I discovered that 
hiding/showing rows triggers Worksheet's Calculate event, which then triggers the formulas using these functions to update. However, hiding/showing columns does not trigger the Calculate event.

Conclusion: A shortcoming of the object model

My conclusion is there is an unpublished event for hiding/showing rows that was implemented in support of SUBTOTAL() but no complementary event for hiding/showing columns.  

As to the why one and not the other triggers the Calculate event, it seems because SUBTOTAL() depends on an Event to keep the results updated but there is no need for a complementary event.

Resolution: There are options

That leaves us with alternatives to updating after hiding/showing columns.
  • Of course, the user could always click the Calculate button but that's a matter of being in the right ribbon and is a pain in the neck to remember.
  • It would also be possible to add a control button, putting Application.Calculate in it's click event.  I have an aversion to controls on worksheets.
  • A third method would be to add Application.Calculate to the worksheet's SelectionChange event handler.  This might be a hindrance, i.e. if the sheet has many formulas it would slow down work.
  • Fourth would be to add an Event to catch when a Column is hidden or shown. I only say this theoretically for two reasons: I don't understand well how to do this; I'm not certain it can be done.

Recommendation

My current recommendation is to Name the area with the functions something like "ResultsArea" then include the following code.

The Trigger

In the Workbook module, call a subroutine to do the calculation for a Named range
Private Sub Workbook_SheetSelectionChange(ByVal Sh As Object, ByVal Target As Range)
   Call RecalculateNamedRange(Sh, Target, "ResultsArea")
End Sub

Recalculation

Add the subroutine that invokes the calculating the Named range, if it exists.
Public Sub RecalculateNamedRange(ws As Worksheet, rngTarget As Range, strName As String)
    Dim nm As Name
    Set nm = RangeByName(ws, strName)
  
    If Not nm Is Nothing Then
        If Not Intersect(rngTarget, nm.RefersToRange) Is Nothing Then
            ' Limit calculation to just the affected range
            nm.RefersToRange.Calculate
        End If
    End If
End Sub

Controlling Recalculation

In order to perform the recalculation, the Named range is found and returned by the following function.  If (a) a Name is found that matches the second argument and (b) the Name refers to a range.  Otherwise, the function returns Nothing.
Public Function RangeByName(ws As Worksheet, strName As String) As Name
    Dim nm As Name
    Dim rngTest As Range
    If ThisWorkbook.Names.count > 0 Then ' Avoids error is there are no Names in the workbook
        ' First priority is a Name scoped to the worksheet _
          because it is possible to have the same Name for each worksheet
        For Each nm In Names
            If nm.Name = ws.Name & "!" & strName Then Exit For
        Next nm
        If nm Is Nothing Then
            For Each nm In Names
                If nm.Name = strName Then Exit For
            Next nm
        End If
    End If
    On Error GoTo EXIT_RangeByName
    Set rngTest = nm.RefersToRange
    Set RangeByName = nm
EXIT_RangeByName:
    On Error GoTo 0
End Function