VBA & VB6 Interface Professional Edition

Installation

The VBA/VB6 interface allows two different types of applications.

1. Using the ARulesXL add-in you can write macros/commands that call query functions and then display the results in a form specific to your application. The sample, 'Budget Analysis.xls', does this to highlight the parts of the profit and loss statement that are not meeting expectations.
2. Using the ARulesXL API from VBA in any application or VB6, you can load exported rule sets, add data and perform queries.

Note — If you want to run rule sets in Excel and prevent users from changing and/or viewing your rules, see the Runtime Excel Add-In.

API Additions & Changes

These functions are only available in ARulesXL Add-in for Excel:

• VBARQuery
• VBARQueryMore
• VBARArrayQuery

The standard ARulesXL API functions are available outside of the add-in:

• OpenRules
• CloseRules
• ClearTable
• ClearVector
• AddToTable
• AddToVector
• AddObject
• GetLS

VBARQuery

Description:

Function VBARQuery( RuleSet As Range, QueryText As String, ParamArray Cell_1_to_N() As Variant) As Variant

RuleSet

The Excel Range object where the rule set is located

QueryText

The query to run in the same format as is used for RQuery()

Cell_1_to_N()

The array of arguments to the query

Remarks:

Queries the named ruleset and returns the result.

Return Value:

The result of the query, which can be an integer, floating point number, string or array. You can use the RANGE() function in your rules to return where your RArray() and RCell() data are located.

Returns an error message on failure.

Example:

Set rules = Range("BudgetRules")
    
' This is a simple example picking up a dimension to highlight
' from the rule.
result = Application.Run("ARulesXL.xla!VBARQuery", rules, "FIND HighLight")
Range(result).Interior.ColorIndex = 3  

VBARQueryMore

Description:

Function VBARQueryMore( RuleSet As Range, QueryText As String, ParamArray Cell_1_to_N() As Variant) As Variant

RuleSet

The Excel Range object where the rule set is located

QueryText

The query to run in the same format as is used for RQuery()

Cell_1_to_N()

The array of arguments to the query

Remarks:

Queries the named ruleset using results derived during prior queries and returns the result.

Return Value:

The result of the query, which can be an integer, floating point number, string or array. You can use the RANGE() function in your rules to return where your RArray() and RCell() data are located.

Returns an error message on failure.

Example:

Set rules = Range("BudgetRules")
    
' This is a simple example picking up a dimension to highlight
' from the rule.
result = Application.Run("ARulesXL.xla!VBARQueryMore", rules, "FIND HighLight")
Range(result).Interior.ColorIndex = 3 

VBARArrayQuery

Description:

Function RArrayQuery( RuleSet As Range, First As Boolean, QueryText As String, ParamArray Cell_1_to_N() As Variant) As Variant

RuleSet

The Excel Range object where the rule set is located

First

Specifies whether this is a first time query, or to use derived values from previous queries

QueryText

The query to run in the same format as is used for RQuery()

Cell_1_to_N()

The array of arguments to the query

Remarks:

Queries the named ruleset and returns the result as a VBA array. You can call VBARQuery() just a from a cell, and it returns a variant. But, VBARQuery() doesn't know whether to format arrays as strings or arrays when called from VBA. So you can call VBARArrayQuery(), just like VBARQuery(), and know that it will return arrays formatted as arrays.

Return Value:

The result of the query as a VBA array.

Returns an error message on failure.

Example:

' Pick up the range of the rule set.
Set rules = Range("BudgetRules")
   
' The second boolean argument is whether or not this is a first time query
' or will use derived results from a previous query.
result = Application.Run("ARulesXL.xla!VBARArrayQuery", rules, True, "FIND analysis")

' Since we expect an array, a string is an error message.
vt = VarType(result)
If vt = vbString Then
    MsgBox "Error: " & result
Else
' The array contains the cells and comments indicating problems.  Annotate
' the spreadsheet with each.
    For i = LBound(result) To UBound(result)
        Range(result(i, 1)).Interior.Color = vbYellow
        Range(result(i, 1)).AddComment (result(i, 2))
    Next i
End If

Using the Amzi! Logic Server

The VB6/VBA interface to the Amzi! Logic Server is part of the ARulesXL add-in. You can call the procedures and functions in that interface as documented in the Amzi! Logic Server Reference Guide.

Samples

ARulesXL Add-In Macro/Command Sample

Open the 'Budget Analysis.xls' sample spreadsheet. A 'Budgets' menu will appear in Excel. You can use it to run the analyses. To see how the commands are implemented, use Tools | Macro | Visual Basic Editor to open the PLFunctions Module.

VB6 Advice

In order to run this sample:

1. Open the 'Product Advice.xls' spreadsheet sample and select ARulesXL | Export from the main menu to export a file named 'product_advice.axl'.
2. Copy product_advice.axl, arulesrt.dll, ar_osutilsrt.lsx, ar_mysqlrt.lsx, arulesrt.xpl and arulesrt.cfg to the same directory with the source code.
3. In VB6, open advice.vbw.
4. Add arules.bas and arulesxl.cls to the project (from the vb6_vba directory).
5. Build advice.exe and run it.

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.