Class Item Filtering Via Dynamic Scripting
Published:
Browse All Articles > Class Item Filtering Via Dynamic Scripting
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.https://www.experts-exchange.com/questions/27419023/Create-a-Filter-on-a-custom-Collection-VBA.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
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
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
Example:
.[Field1]
.[Style]
.[DueDate]
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, "[", ".["), "..[", ".[")
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]*
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
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
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
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
https://www.experts-exchange.com/A_2140.html
https://www.experts-exchange.com/A_1336.html
https://www.experts-exchange.com/A_4318.html
I used the BuildString class I introduced in this article.
https://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.
https://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"
=-=-=-=-=-=-=-=-=-=-=-=-=-
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!
=-=-=-=-=-=-=-=-=-=-=-=-=-
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (0)