CODEWITHSUNDEEP
excelvbafiltering
user1787425
user1787425

Reputation: 33

Filtering a range in VBA

I am having a problem using a filtered range in VBA. If I use a macro to filter the range, then use another to call a Subtotal worksheet function to find a minimum values in the first column, I get the expected minimum for the filtered range; this is what I want. This is shown here:

Sub TestFilterTrans()
    Call FilterTrans("111Wall")
End Sub

Sub TestMinDate()
    MsgBox MinTransDate()
End Sub

Sub FilterTrans(Inv As String)
    Range("TransLabels").Select
    Selection.AutoFilter
    ActiveSheet.Range("Trans").AutoFilter Field:=2, Criteria1:=Inv
End Sub

Function MinTransDate()
    MinTransDate = Application.WorksheetFunction.Subtotal(5,     Worksheets("Transactions").Range("TransDates"))
End Function

However, if, from the sheet, I call a function to first invoke the filter procedure, and then call a function to find the minimum, it selects the minimum over the whole range, as if unfiltered, shown here:

Function IncepDate(Inv As String)
    Dim d As String
    Call FilterTrans(Inv)
    d = MinTransDate()
    Call FilterOff
    IncepDate = d
End Function

Can anyone tell me why the filter does not seem to work in the second case?

Upvotes: 3

Views: 3162

Answers (1)

David Zemens
David Zemens

Reputation: 53663

A Function is not supposed to manipulate the worksheet object. There are several questions on that topic here, but the consensus is that calling subroutines from within worksheet functions to manipulate the worksheet object is a bad idea.

I stepped through some similar code to try and replicate this.

The code executes without error, but if you step through, you will see that the autofilter is never actually applied.

I am reasonably certain this is by design, although an error/warning message would be nice.

My previous comment on a similar question:

Cells reference not working in Excel VBA 2010 Function

It is not possible to manipulate worksheet cells from a UDF called from the worksheet.

More information here:

https://stackoverflow.com/a/15647054/1467082

And here:

http://www.excel-it.com/UDF.htm

generally, subroutines can manipulate the worksheet, and functions cannot.

The exception is that functions called from within a subroutine(sometimes) can, however this is probably a bad habit to use a function for anything other than returning values to the Subroutine.

And, plenty of documentation about this from Microsoft:

http://support.microsoft.com/kb/170787

A user-defined function called by a formula in a worksheet cell cannot change the environment of Microsoft Excel. This means that such a function cannot do any of the following:

  • Insert, delete, or format cells on the spreadsheet.
  • Change another cell's value.
  • Move, rename, delete, or add sheets to a workbook.
  • Change any of the environment options, such as calculation mode or screen views.
  • Add names to a workbook.
  • Set properties or execute most methods.

The purpose of user-defined functions is to allow the user to create a custom function that is not included in the functions that ship with Microsoft Excel. The functions included in Microsoft Excel also cannot change the environment. Functions can perform a calculation that returns either a value or text to the cell that they are entered in. Any environmental changes should be made through the use of a Visual Basic subroutine.

During calculation, Excel examines the precedents of the cell that contains a user-defined function. If not all precedents have been calculated so far during the calculation process, Excel eventually calls the user-defined function and passes a Null or Empty cell to the function. Excel then makes sure that enough calculation passes occur for all precedents to be calculated. During the final calculation pass, the user-defined function is passed the current values of the cells. This can cause the user-defined function to be called more frequently than expected, and with unexpected arguments. Therefore, the user-defined function may return unexpected values.

For correct calculation, all ranges that are used in the calculation should be passed to the function as arguments. If you do not pass the calculation ranges as arguments, instead of referring to the ranges within the VBA code of the function, Excel cannot account for them within the calculation engine. Therefore, Excel may not adequately calculate the workbook to make sure that all precedents are calculated before calculating the user-defined function.

Upvotes: 1

Related Questions