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 columns, SUBTOTAL()-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