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]

Updates

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, www.spreadsheetbudget.com
'
' 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]