Using VBA

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

The TxlReport class as well as the TxlDataSource class represents items of the DataSources collection. Both have two published properties - MacroAfter and MacroBefore. These properties are used to call public VBA procedures. So their values (they are strings) must contain the full names of public VBA procedures (they are called macros in Excel), saved along with the template workbook. We use macros to build Excel charts in our own work. But you can make anything you want, or rather whatever Excel is capable of. And Excel can make lots of nice things.

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 in ModuleName.ProcedureName format 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? QDemo.dpr form: frmVBA1; unit: fVBA1; template: tVBA1.xls

We took Quick Start as an example. We opened the template workbook, added a module with the Module1 name into the workbook and created a public procedure named according to tradition HelloWorld - we called MsgBox to display the "Hello, world!" message. Then we saved the template and closed it. As we said earlier, we must supply XL Report with the full name of the procedure. This name is Module1.HelloWorld. We put it into the MacroAfter property of the TxlReport instance. Now you can see the result - just run the report.

The main question in usung 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? QDemo.dpr form: frmVBA2; unit: fVBA2; template: tVBA2.xls

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

First, a dataset: we took the first pivot table example as a base. We wrote the following SQL statement in the SQL property of qryAll:

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 at your right. The macro (BuildChart is its name) creating this chart was recorded in Excel (Tools|Macro|Record New Macro), saved in one of tVBA2.xls modules and corrected in order to work with the AllRange. We think that the most interesting part of this code is the statement:

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

Passing parameters to VBA procedures

Where? QDemo.dpr form: frmVBA3; unit: fVBA3; template: tVBA3.xls

We used static criteria in the previous example. Now we want to show you how you can make it dynamic. We added the edit field edCriteria to the form. Then we corrected the procedure by adding the Criteria as string parameter and changing the code to let AutoFilter use this parameter. Full code can be found in the template. Here is the problem: how do we supply the BuildChart procedure with the value from the edCriteria field? You have to use the OnMacro event of the TxlReport instance. The code:

The astounding number of parameters was dictated by the syntax of the Application.Run method of the Excel Type Library in case of early binding. Parameters must be passed by position. We only had to pass one parameter in our example.

Time to look back - XL Report and VBA

Summing up:

• 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.
• TxlReport and TxlDataSource classes both have MacroBefore and MacroAfter properties to indicate the full name of the VBA procedures to be invoked.
• MacroBefore is triggered before the report generation (TxlReport) and before the data transfer (TxlDataSource).
• MacroAfter is triggered after the report generation (TxlReport) and after the data transfer (TxlDataSource).
• The OnMacro events of these two classes must be used to pass parameters to the called VBA procedures.
• You can automate your routine tasks using Macro Recorder in Excel.