VBA

Tutorial Index     Rules & Decision Tables     Column Queries     Arrays     Tables & Pattern Matching     Lists     VBA     Multiple Rule Sets     Array Queries

loans_07_vba.xls

In this example, two buttons are provided for the user to push. One highlights the loans that can be resold to Ace, and the other the loans that can be resold to Acme.

VBA code is necessary for this example because a cell function in Excel cannot modify cells other than the one it is in.

The application looks like this when the Ace button is pushed:

loans_07_vba.xls

When a table is read into the rule engine using RArray(), the cell addresses of each data element are remembered as well.

The RANGE() function is used to get those cell addresses back. This is mainly useful when calling RQuery() from VBA.

RANGE() can be used with a single element, such as RANGE( loans[203, 'Amount'] ) or it can be used with a whole row or column by using an * for one of the indices as in RANGE( loans[203, *] ).

Before going to VBA, it is often a good technique to use RQuery() in cells away from the main display for testing. In this case RQuery() is called with "FIND ResaleCells['Ace']" to see if it's working.

Using RQuery() to test a rule

It works as expected, returning a list of the ranges of the desired rows.

Two call this function from VBA, there are two buttons, one for each investor, that call a function that will call RQuery() from VBA.

Option Explicit

Private Sub Ace_Click()
    HighLight "Ace"
End Sub

Private Sub Acme_Click()
    HighLight "Acme"
End Sub

There are two versions of the RQuery() call for VBA. One returns a single value, just as RQuery() does; the other returns a VB array constructed from a result which is a list. The two entry points in ARulesXL.xls are:

• VBARQuery - returns a single value
• VBARArrayQuery - returns a two dimensional VB array constructed from a list result, or a list of lists result. In the case of a simple list, as in this example, the second dimension of the VB array has the single value 1.

The arguments for each are:

• The range of the rule set being called
• A boolean indicating if this is a first call to the rule set (TRUE) or a subsequent call that make use of intermediate results from a previous call.
• A string containing the query

This is the actual code in the sample:

Public Sub HighLight(Investor As String)
Dim result As Variant
Dim rules, data As Range
Dim i, vt As Integer

    ' The data range, better as a named range for a general purpose application.
    Set data = Range("C5:G19")
    ' Reset the data cell colors
    data.Font.Color = &H999999       ' grey
    data.Interior.Color = &HCCFFCC   ' light green
    
    ' The name of a rule set is it's Excel range name as well.
    Set rules = Range("ResaleRules")
    
    ' VBARArrayQuery formats a list result as a VBA array so it can be easily processed by VBA code.
    result = Application.Run("ARulesXL.xla!VBARArrayQuery", rules, True, "FIND ResaleCells['" & Investor & "']")

    ' Since we expect an array, a string is an error message.
    vt = VarType(result)
    If vt = vbString Then
        MsgBox "Error: " & result
    Else
        ' The result array is two dimensional, but in this case there is only one column.
        For i = LBound(result, 1) To UBound(result, 1)
            Range(result(i, 1)).Interior.Color = &H99FFFF ' light yellow
            Range(result(i, 1)).Font.Color = &H0          ' black
        Next i
    End If
    
End Sub

Copyright ©2005-7 Amzi! inc. All Rights Reserved. ARulesXL and Amzi! are trademarks or registered trademarks of Amzi! Microsoft and Excel are trademarks or registered trademarks of Microsoft Corporation.