AdvancedFilter Wrapper Engine

for VBA Enthusiasts

A while back ago I promised to produce my engine for the advancedfilter wrapper. Well, at the prodding of Hugo I finally put it together. I took the code from my VB.NET project and converted it to VBA. Man, that reminds of why I love .NET so much! If you ever get a chance to learn .NET of VBA definitely take the opportunity. At first it is difficult but then it gets to the point where you don’t want to go back. [Read More]


AdvancedFilter Class Just so everyone knows. I’m going to postpone the AdvancedFilter series until I start working on it in .NET, that way I’ll remember more about what I was doing before. I’ll start working on that when I finish up the Time Card application and the Chart Manipulator. Time Card I probably won’t be finishing up the beta version of the time card until early April. I’m currently debugging the UDF function “SumDate” which is pretty central to the time card. [Read More]

AdvancedFilter Class Part 3

Creating the Sub Class

In order to get the nice drop down menu we’ll need a second class I’ll call “SearchTerms.” This class is also necessary to store each individual search term entered into the class. Included in this class will be:

  1. Search Term
  2. Header
  3. Header Operator
  4. Match Type

' SearchTerms
' Description: This class contains the search information.
' Authors: Jon Nyman,
' Change Overview
' Date Comment
' --------------------------------------------------------------
' 03/02/2012 Initial version
Option Explicit
'Class Variable Declarations
Private msSearchTerm As String
Private msHeader As String
Private meHeaderOperator As HeaderOperator
Private meMatchType As MatchType

'Class Property Procedures

Property Get SearchTerm() As String
    SearchTerm = msSearchTerm
End Property Property 

Let SearchTerm(ByVal sItem As String)
    msSearchTerm = sItem
End Property

Property Get Header() As String
    Header = msHeader
End Property 

Property Let Header(ByVal sHeader As String)
    msHeader = sHeader
End Property

Property Get Header_Operator() As HeaderOperator
    Header_Operator = meHeaderOperator
End Property

Property Let Header_Operator(ByVal eHeaderOperator As HeaderOperator)
    meHeaderOperator = eHeaderOperator
End Property

Property Get Match_Type() As MatchType
    Match_Type = meMatchType
End Property

Property Let Match_Type(ByVal eMatchType As MatchType)
    meMatchType = eMatchType
End Property

AdvancedFilter Class Part 2

Determining What Options We Want

The first thing we’ll want to do is determine what macro options we would like in the class. The options I currently have are: Range to filter (optional – can use current range instead). Filter location (optional – if you are using an add-in then you can place in there, otherwise can put in temporary sheet). Filter action (copy to new range or filter in place (default)). Get unique items from a column (will need to use different method than AdvancedFilter). [Read More]

Wrapping a Class Around the AdvancedFilter Method

Classes are a tool in object-oriented programming that make programming much simpler. One class that I have developed in VBA is a class that makes using the AdvancedFilter method extremely easy and useful. In fact, I like it so much that I stopped using the regular find method. In the coming weeks I will demonstrate how this was done.  This is how the AdvancedFilter method works:  Use a predefined range to filter. [Read More]