Table column sum generates error

I have a worksheet containing a table. The following function returns the total of any column.

Function ColSum(col As Integer)

    Dim lo As ListObject
    Dim TotalSP As Integer
    
    
    Application.Volatile
    
    Sheets("ContractAuth").Activate
    Set lo = ActiveSheet.ListObjects(1)
    ColSum = Application.WorksheetFunction.Sum(lo.DataBodyRange.Columns(col))
    
End Function

I find that often the cell holding the result of this function has the error '#VALUE' and I have to press F9 to force calculation, although automatic calculation is already on. This appears to happen when a change is made to another worksheet in the same workbook - I have to return to the worksheet containing the table and press F9.

Perhaps you could handle the error with something like this.

Copy to clipboard
Function ColSum(col As Integer) As Variant
Dim lo As ListObject
Dim TotalSP As Variant
    
   Application.Volatile
    
    Set lo = Sheets("ContractAuth").ListObjects(1)
    TotalSP = Application.Sum(lo.DataBodyRange.Columns(col))
    
    If Not IsError(TotalSP) Then
        ColSum = TotalSP
    Else
        ColSum = "#N/A"
    End If

End Function

Hi,
It would be much better design to pass the table to the function as well. Then you would not need to make the function volatile or to hardcode sheet names.

Function ColSum(tbl As Range, col As Integer)

    ColSum = Application.WorksheetFunction.Sum(tbl.Columns(col))
    
End Function

and call it using =ColSum(tablename, 1) for example.

Many thanks - that does the trick.

Also greatly reduces recalc time.