How to perform calculations on (excel) filtered ListObject without UI being affected?

See the previous post here: How to perform Calculations on (excel) ListObject.DataBodyRange.SpecialCells(xlCellTypeVisible)

I want to perform a calculation on a ListObject ("format as table") without any UI interaction.

The calculation is easy:

function calculation()
   Dim quotebook As ListObject

   ActiveWorkbook.ActiveSheet.Range(myRange).Select    '< Problem starts here

   Set quotebook= ActiveWorkbook.Worksheets.Item("quotebook").ListObjects("BookData")
   With quotebook
       Debug.Print .DataBodyRange.Columns(9).SpecialCells(xlCellTypeVisible).Count
       calculation = Format(Application.WorksheetFunction.Average(Selection), "$#,##0.00")
   end with

But the problem is on the UI, the column is selected, the focus moves, and that just complicates things. How do I perform the calculation on the column "myRange" in the listobject or table "quotebook", without any effects on the UI?

Get Average of Filtered Column

SpecialCells

• The following will fail (return "$0,00") if a cell contains an error value...

Function GetAverageSpecialCells( _
    ByVal tbl As ListObject, _
    ByVal tblColumn As Long) _
As String

    If tbl Is Nothing Then Exit Function
    If tblColumn < 0 Or tblColumn > tbl.ListColumns.Count Then Exit Function
    
    On Error Resume Next
    Dim rg As Range: Set rg = tbl.DataBodyRange.Columns(tblColumn) _
        .SpecialCells(xlCellTypeVisible)
    On Error GoTo 0
    
    Dim Result As Double
    If Not rg Is Nothing Then
        On Error Resume Next
        Result = Application.Average(rg)
        On Error GoTo 0
    End If
    
    GetAverageSpecialCells = Format(Result, "$#,##0.00")
    
End Function

Sub GetAverageSpecialCellsTEST()
    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("QuoteBook")
    Dim tbl As ListObject: Set tbl = ws.ListObjects("BookData")
    Dim Col As Long: Col = 9
    Debug.Print GetAverageSpecialCells(tbl, Col)
End Sub

SubTotal

• ... while the following will fail (return "$0,00") even if not all cells contain numbers.

Function GetAverageSubTotal( _
    ByVal tbl As ListObject, _
    ByVal tblColumn As Long) _
As String

    If tbl Is Nothing Then Exit Function
    If tblColumn < 0 Or tblColumn > tbl.ListColumns.Count Then Exit Function
    
    Dim Result As Double
    On Error Resume Next
    Result = Application.Subtotal(10, tbl.DataBodyRange.Columns(tblColumn))
    On Error GoTo 0
    
    GetAverageSubTotal = Format(Result, "$#,##0.00")
    
End Function

Sub GetAverageSubTotalTEST()
    Dim wb As Workbook: Set wb = ActiveWorkbook
    Dim ws As Worksheet: Set ws = wb.Worksheets("QuoteBook")
    Dim tbl As ListObject: Set tbl = ws.ListObjects("BookData")
    Dim Col As Long: Col = 9
    Debug.Print GetAverageSubTotal(tbl, Col)
End Sub