Excel VBA Autofilters inside functions

Question

Sub TurnAutoFilterOn()
'check for filter, turn on if none exists
  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A1").AutoFilter
  End If
End Sub

Works well and turns on the AutoFilter.

Function Req(ByVal MCode As String) As Integer
    TurnAutoFilterOn
End Function

Doesn't work.

Function Req(ByVal MCode As String) As Integer
'check for filter, turn on if none exists
  If Not ActiveSheet.AutoFilterMode Then
    ActiveSheet.Range("A1").AutoFilter
  End If
End Function

Doesn't work.

Is excel vba autofilters supposed to be working only under SUBs and not in Functions?

Answer 1

The above commenters are right regarding updating the workbook (i.e. any cells) from a function invoked by a cell - it is not allowed/supported.

Excel provides a workbook reclaculation model in which it can precompute inter-cell dependencies based on the cell formulas. This allows for a (relatively) efficient propagation of changes from their original sources to the cells that depend upon them. It propagates changes repeatedly (i.e. recursively) until they've been propagated to cells that are not referenced in other formulas, when the workbook relaculation is completed. It does NOT allow cell formulas to modify any cells in the workbook; if it were to support that it would effectively invalidate (or at least dramatically weaken) the pre-computed formula-based dependency analysis, and require another calculation model (that would likely much less efficient). (Circular cell references (direct or indirectly) are also problematic for this method, which makes history functions a bit tricky.)

However, what you can do is record some data in a VBA data structure to save for later use (in the example below, the very simple public gx, but such data structure can by almost of any complexity). You can then use that recorded data after a workbook recalculation using events. The Worksheet Change Event is way to run some code after the calculation (you write subroutine Worksheet_Calculate and put it in the worksheet), at a time when it will be OK to modify the cells. There is also a Workbook_SheetCalculation which goes in the code for ThisWorkbook, which might be of interest.

In "ThisWorkbook":

Private Sub Workbook_SheetCalculate(ByVal Sh As Object)
    MsgBox "gx=" & gx
    Application.Worksheets("Sheet1").Range("A1") = gx
End Sub

In "Module1":

Public gx As Long

Function MyFormula(x As Long) As String
   gx = x
   MyFormula = "hello"
End Function

In Sheet1, cell A5:

=MyFormula(A4)

You'll get a pop up in a context where gx was set to the number in A4 (showing the storing of data from the run of the formula), and, modifying a worksheet as well. Now, modify A4 with another number and you'll see the results because changing A4 triggers a recalculation.

(Note that you may also be interested in the Workbook_SheetChange event as an alternative to the SheetCalculate event.)