<

Go Premium for a chance to win a PS4. Enter to Win

x

Class Item Filtering Via Dynamic Scripting

Published on
13,288 Points
5,188 Views
6 Endorsements
Last Modified:

Introduction

While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library and apply it to collection filtering.  We will show you how to incorporate this into your classes.
http://www.experts-exchange.com/Q_27419023.html

Note: It is also possible to create an ADO recordset and populate it with your class properties.  You could then use the recordset's filtering capabilities to identify which items meet the filter criteria.

The ScriptControl library

For those of you who have used the Eval(Access) or Evaluate(Excel) functions, you are invoking a scripting engine.  You can use the SC library to add run-time scripting to your code.  This library allows your VB/VBA/VB.Net code to create and execute VB.Script code.

Common ScriptControl Uses
Without having to write very much code, you can add the following functionality to your compiled applications with the SC library.
Macro capabilities -- user-supplied code at run time
Calculator function -- on-the-fly math expressions

The best way to think about the SC is that it is a little VB 'engine' that runs the statements you supply it.  You can supply a single line or many routines.  The control follows the same syntax rules you would encounter in the VB classic environment.  Every statement needs to be on a new line, unless it is explicitly continued from the previous line or separated with a colon character.

The SC offers several methods to invoke code.  I've already mentioned the .Eval() method.  This article will use the .Run() method.  I could also have used the .Modules collection also provides access to the routines you have defined with the .AddCode() method.  These three methods will return results.  There is a fourth method, .ExecuteStatement() that will invoke code, but will not return results.

Implementing and Testing Class Filtering

1. ExampleClass

In order to test our filtering, we need a simple class with a few properties.  This example class has string, date, and long integer properties.  When the class is instantiated, these properties are initialized as follows:
* Field1 is a three character string, where each character is in the ("A", "B", "C") set
* Status is in the 0-5 range
* DueDate is a date from the current date to 100 days in the future.
Option Explicit
'ExampleClass Class module

Public Field1 As String
Public Status As Long
Public DueDate As Date


Private Sub Class_Initialize()
    Field1 = Chr(Int(Rnd * 3) + 65) & Chr(Int(Rnd * 3) + 65) & Chr(Int(Rnd * 3) + 65)
    Status = Int(Rnd * 6)
    DueDate = Date() + Int(Rnd * 101)
End Sub

Open in new window

Note: This class was created for the sole purpose of exercising the filtering function in this article.  You should use public properties, rather than public variables in your production classes.

2. The FilterCollection Function

While we are developing this function as a stand-alone routine, we will pass it a collection parameter and a filter/pattern string parameter.  The function will return a collection of all the items that meet the filter condition(s).  Since I am using the ExampleClass class, I'm taking advantage of Intellisense when referencing the class properties by declaring the oCC as an ExampleClass data type.  I am using the clsBuildString class I described in my Fast String Concatenate Class article.  It makes the VBScript strings (lines) easier to read.
Public Function FilterCollection(parmCol As Collection, parmFilter As String) As Collection
    Dim oCC As ExampleClass
    Dim lngLoop As Long
    Dim oVBS As Object      'alternatively New ScriptControl if Reference added
    Dim BS As New clsBuildString    'see Fast Concatenate article A_8311.html
    Dim colFiltered As New Collection
    
    Set oVBS = CreateObject("ScriptControl")
    
    BS.Add "Function CCFilter(parmCC)"
    BS.Add "  With parmCC"
    BS.Add "    CCFilter = " & parmFilter
    BS.Add "  End With"
    BS.Add "End function"
    BS.Add "Public Function IsLike(parmString, parmPattern)"
    BS.Add "    Dim RegExp"
    BS.Add "    Set RegExp = CreateObject(""vbscript.RegExp"")"    'version: VBScript_RegExp_55
    BS.Add "    RegExp.Pattern = parmPattern"
    BS.Add "    IsLike = RegExp.Test(parmString)"
    BS.Add "End Function"

    BS.Delim = vbCrLf

    oVBS.Language = "VBScript"
    oVBS.AddCode BS.Text
    
    For Each oCC In parmCol
        lngLoop = lngLoop + 1
        If oVBS.Run("CCFilter", oCC) = True Then
            'Debug.Print lngLoop, oCC.Status, oCC.Field1, oCC.DueDate    'for debugging/playing
            colFiltered.Add oCC
        End If
    Next
    Set FilterCollection = colFiltered
End Function

Open in new window


Property References
The convention I am using requires the property names to be preceded by a period, since they will be used inside a With...End With structure.  
Example:
.Field1
.Style
.DueDate

Open in new window

If you want to make this a bit more familiar, you can use the square bracket convention you might be used to in your database applications.
Example:
.[Field1]
.[Style]
.[DueDate]

Open in new window

A Convenience Avoided
You could add a bit of code to prepend the period character to these fields.  We could replace all "[" characters with ".[".  However, we need to prevent prepending a "." to a property reference that already begins with a ".", so we add a second Replace to handle that condition.
Example:
Replace(Replace(FilterString, "[", ".["), "..[", ".[")

Open in new window

Note: Since string pattern matching can include a left square bracket, I have not implemented this auto-prepend code.  Since my use of the SC is to minimize coding, I don't want to add unnecessary complexity just to add convenience.  To implement this would require the filter string to be parsed and processed.  Requiring a leading period isn't too onerous a requirement for the user.  If you are constructing the filter string programmatically, you can easily prepend the period character.

Literal Values and Delimiters
Since we are in the VB environment, you will need to follow the delimiter rules for literals.  
String literals need to be quote-delimited.  Since we are creating a string filter, you may use a pair of quote characters or concatenate Chr(34) characters around the literal value to compare to a string (data type) property.
Date literals need to be hash-delimited (pound sign delimited).  Now you can apply your Twitter skills to to the development environment.
Numeric literals do not need delimiters

VBScript Pattern Matching
There is no Like operator in the VBScript language.  Rather than writing a lot of VBScript code to mimic the power and flexibility of the Like operator, we can use the regular expression library.  The IsLike() function tests the supplied pattern against a string using RegExp and returns a True/False value.  There are several differences between the pattern characters used in the Like operator and RegExp.
Like  		RegExp
___________	____________
[!charlist]	[^charlist]
#     		\d
?     		[\s\S]
*     		[\s\S]*

Open in new window

The up-side of creating the IsLike() function is that we can use any RegExp pattern, which provides more powerful pattern matching than the VB/VBA Like operator.

3. Testing the Function

I populate a collection with ExampleClass items and then pass that class to the FilterCollection() function with a variety of filter strings.
Public Sub main()
    Dim colCC As New Collection
    Dim oCC As ExampleClass
    Dim lngLoop As Long
    Dim oVBS As Object      'alternatively New ScriptControl if reference added
    Dim varItem As Variant
    Dim strFilter As String
    Set oVBS = CreateObject("ScriptControl")
    
    For lngLoop = 1 To 40
        Set oCC = New ExampleClass
        colCC.Add oCC
    Next

'Different filters to test
'    strFilter = "(.[Field1] = ""ABC"") AND (.[Status] >=2)"
'    Debug.Print "***", strFilter
'    strFilter = "(instr(2, .[Field1], ""AA"")<>0 )"
'    Debug.Print "***", strFilter
'    strFilter = "(Right(.[Field1],2) = ""AA"") "
'    Debug.Print "***", strFilter
'    strFilter = "(.[DueDate] >= #1/15/2012#) And (.[DueDate] < #2/1/2012#) "
'    Debug.Print "***", strFilter
'    strFilter = "(.[Field1] = ""ABC"") AND (.[Status] >=2)"
'    Debug.Print "***", strFilter

    strFilter = "IsLike(.[Field1] , ""[\s\S]CB"")"
    Debug.Print "***", strFilter
    For Each oCC In FilterCollection(colCC, "IsLike(.[Field1] , ""[\s\S]CB"")")
        Debug.Print oCC.Status, oCC.Field1, oCC.DueDate
    Next

    Set colCC = Nothing
End Sub

Open in new window


Testing Results
***           (.[Field1] = "ABC") AND (.[Status] >=2)
 19            2            ABC           2/6/2012 
***           (instr(2, .[Field1], "AA")<>0 )
 3             3            CAA           12/24/2011 
 8             1            AAA           11/16/2011 
 13            1            BAA           2/7/2012 
 28            4            CAA           11/24/2011 
***           (Right(.[Field1],2) = "AA") 
 3             3            CAA           12/24/2011 
 8             1            AAA           11/16/2011 
 13            1            BAA           2/7/2012 
 28            4            CAA           11/24/2011 
***           (.[DueDate] >= #1/15/2012#) And (.[DueDate] < #2/1/2012#) 
 1             1            CAB           1/25/2012 
 5             5            CCA           1/16/2012 
 7             1            BCA           1/25/2012 
 12            5            BCA           1/16/2012 
 16            4            CAB           1/16/2012 
 18            0            AAB           1/26/2012 
 27            5            BAC           1/15/2012 
 33            1            AAC           1/16/2012 
 39            1            CBC           1/28/2012
***           IsLike(.[Field1] , "[\s\S]CB") -- equivalent to Like "?CB"
 16            2            BCB           2/2/2012 
 23            3            CCB           12/31/2011 
 29            4            CCB           1/17/2012 
 30            1            ACB           12/29/2011 
 31            0            CCB           1/4/2012 

Open in new window

Note: Since the class properties are based on the Rnd function values, they will differ from test to test.

Implementing the Filter Feature in Your Classes

In your collection classes, you will have a private collection variable, some methods for adding new items, deleting items, and a NewEnum function allowing code to iterate the collection with a For Each…Next statement.  We will provide a FilteredItems function that returns a collection containing items from our private collection variable that meet the filter parameter condition(s).  In the code below, I am representing your private collection variable with m_col.
Option Explicit
Private m_col As Collection

Public Function FilteredItems(ByVal parmFilter As String) As Collection
    Dim oCC As Variant
    Dim lngLoop As Long
    Dim oVBS As Object                'alternatively New ScriptControl if Reference added
    Dim BS As New clsBuildString    'see Fast Concatenate article A_8311.html
    Dim colFiltered As New Collection
    
    Set oVBS = CreateObject("ScriptControl")
    
    BS.Add "Function CCFilter(parmCC)"
    BS.Add "  With parmCC"
    BS.Add "    CCFilter = " & parmFilter
    BS.Add "  End With"
    BS.Add "End function"
    BS.Add "Public Function IsLike(parmString, parmPattern)"
    BS.Add "    Dim RegExp"
    BS.Add "    Set RegExp = CreateObject(""vbscript.RegExp"")"    'version: VBScript_RegExp_55
    BS.Add "    RegExp.Pattern = parmPattern"
    BS.Add "    IsLike = RegExp.Test(parmString)"
    BS.Add "End Function"

    BS.Delim = vbCrLf

    oVBS.Language = "VBScript"
    oVBS.AddCode BS.Text
    
    For Each oCC In m_col
        lngLoop = lngLoop + 1
        If oVBS.Run("CCFilter", oCC) = True Then
            colFiltered.Add oCC
        End If
    Next
    Set FilteredItems = colFiltered
End Function

Open in new window


Addendum and References

Here is a Microsoft knowlegebase article on the different SC code invocation methods.
http://support.microsoft.com/kb/184740

Comparison of VB/VBA Like operator with RegEx
http://msdn.microsoft.com/en-us/library/ms235204.aspx

EE articles on RegEx
http://www.experts-exchange.com/A_2140.html
http://www.experts-exchange.com/A_1336.html
http://www.experts-exchange.com/A_4318.html

I used the BuildString class I introduced in this article.
http://www.experts-exchange.com/A_8311.html

If you want to read more about classes and get a utility to help you build your class, read this excellent matthewspatrick article.
http://www.experts-exchange.com/A_3802.html

If the BuildString class is not used, the FilteredItems() function would need to create the code with traditional string concatenation.
Example:
    oVBS.Language = "VBScript"
    oVBS.AddCode "Function CCFilter(parmCC)" & vbCrLf & _
		 "  With parmCC" & vbCrLf & _
		 "    CCFilter = " & parmFilter & vbCrLf & _
		 "  End With" & vbCrLf & _
		 "End function" & vbCrLf & _
		 "Public Function IsLike(parmString, parmPattern)" & vbCrLf & _
		 "    Dim RegExp" & vbCrLf & _
		 "    Set RegExp = CreateObject(""vbscript.RegExp"")" & vbCrLf & _ 
		 "    RegExp.Pattern = parmPattern" & vbCrLf & _
		 "    IsLike = RegExp.Test(parmString)" & vbCrLf & _
		 "End Function"

Open in new window


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
6
Comment
Author:aikimark
0 Comments

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Join & Write a Comment

Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Next Article:

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month