Excel macros

Home Products Downloads Registered users Support Prices Order Primary Subscription

<< Previous

Table of contents

Next >>

If you cannot complete your task by the methods described earlier you should consider using Excel VBA. That way you will get all of Excel's power at your fingertips.

Both the XLReport object and the XLRDatasource object (see Programmer's Reference) have two properties - MacroAfter and MacroBefore. These properties are used to call public VBA procedures. Their values (they are strings) must contain the full names of public VBA procedures from a template workbook in ModuleName.ProcedureName format.

To invoke VBA procedures you must add a module in a template workbook and create a public procedure in it. If you specify the module and procedure names in the MacroBefore and/or MacroAfter properties then these procedures will be invoked automatically.

Note
Excel may issue warnings about macros with the examples in this section. Don't disable macros when you open these workbooks.

Simple example

Where? form: frmVBA1; template: VBA1.xls

We added a module (Module1) to the QuickStart.xls template workbook and created a public procedure named HelloWorld - we called MsgBox to display the "Hello, world!" message. As we said earlier, we must supply Active XL Report with the full name of the procedure. This name is Module1.HelloWorld. We put it into the MacroAfter property of the XLReport control. Now you can see the result - just run the report.

The main question in using such procedures is how can you access the data in these macros? It's simple. You make use of NoRange-datasources' named cells and Range-datasources' named ranges.

Excel charts

Where? form: frmVBA2; template: VBA2.xls

Here are totals for customers that have orders totaling more than $70,000. Let's look at how it was done.

First, a datasource: we took the first pivot table example as a base. We used the following SQL statement as a record source:

SELECT Customer.Company, Sum(Orders.ItemsTotal) AS ItemsTotal,

Sum(Orders.AmountPaid) AS AmountPaid

FROM Customer INNER JOIN Orders ON Customer.CustNo = Orders.CustNo
GROUP BY Customer.Company
ORDER BY Customer.Company

This query returns company names along with the summed values of the ItemsTotal and AmountPaid fields out of the Order table. Since we used a prepared example, we had to change the template and create a macro in it. You can see the template in the figure. The macro (BuildChart is its name) creating this chart was recorded in Excel (Tools|Macro|Record New Macro), saved in one of VBA2.xls modules and corrected in order to work with the AllRange. We think that the most interesting part of this code is the statement:

SrcRange.AutoFilter Field:=3, Criteria1:=">70000", Operator:=xlAnd

This line sets a condition for the AutoFilter created for this range by Active XL Report.

Time to look back - Excel macros

Summing up:

Active XL Report can invoke VBA procedures from the template workbook. These procedures must be placed in a standalone module(s) and must be declared public.
XLReport and XLRDatasource objects both have MacroBefore and MacroAfter properties to indicate the full name of the VBA procedures to be invoked. The name must be given in ModuleName.ProcedureName format.
MacroBefore is triggered before the report generation (XLReport) or before the data transfer (XLRDatasource).
MacroAfter is triggered after the report generation (XLReport) or after the data transfer (XLRDatasource).
You can automate your routine tasks using Macro Recorder in Excel.