Observation: Formulas don't always update
Conclusion: A shortcoming of the object model
Resolution: There are options
- 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.
RecommendationMy current recommendation is to Name the area with the functions something like "ResultsArea" then include the following code.
The TriggerIn 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
RecalculationAdd 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 NameSet nm = RangeByName(ws, strName)If Not nm Is Nothing ThenIf Not Intersect(rngTarget, nm.RefersToRange) Is Nothing Then' Limit calculation to just the affected rangenm.RefersToRange.CalculateEnd IfEnd IfEnd Sub
Controlling RecalculationIn 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 NameDim nm As NameDim rngTest As RangeIf 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 worksheetFor Each nm In NamesIf nm.Name = ws.Name & "!" & strName Then Exit ForNext nmIf nm Is Nothing ThenFor Each nm In NamesIf nm.Name = strName Then Exit ForNext nmEnd IfEnd IfOn Error GoTo EXIT_RangeByNameSet rngTest = nm.RefersToRangeSet RangeByName = nmEXIT_RangeByName:On Error GoTo 0End Function