A wrapper function is written in VBA and calls another program. In this case, it’s defined as a public function within a standard module so that it can be used universally throughout the database, such as in the expression for a calculated field in a query. Information is sent to the wrapper function as arguments (or parameters). The wrapper function then returns that result.
QUERY
Figure 1 shows the qOffers query in Datasheet view. It shows fields from the Offers table and contains some calculated fields to determine information about funding offers. The Payments column, with mostly blue text, contains the payment calculated by GetExcelPMT, a wrapper function that calls Excel’s PMT function. Columns with red text are the intermediate calculations. The rest of the columns are fields stored in the Offers table.
How are those colors appearing in a query? That was done by setting the Format property for each column in the query design. Ordinarily, the calculated payment would be negative since the loan amount is positive. To get a negative value to show without a sign, and be colored blue, this format is used: [Magenta]$ #,##0.00;[Blue]$ #,##0.00;"-0-". The code is divided into separate parts delimited by a semicolon. The first part, [Magenta]$ #,##0.00, specifies how positive numbers will appear. Next, [Blue]$ #,##0.00 is the format for negative numbers, and "-0-" is for zero values.
The Rate column displays values as a percentage, so the format code [Red]0.#### % is used. This tells Access to display the value as a percent, be colored red, and display zero before the decimal point for values less than one. The number of periods, NPer, uses [Red]0 to be colored red and show whatever number is calculated.
VBA
Open Microsoft Visual Basic for Applications and add the code in Figure 2 for GetExcelPMT and for the ExcelClose sub.
Let’s take a closer look at the code. Directly below the Option statements at the top of the module, oExcel is defined as an object so that it can be seen and reused by all procedures in the module.
Option Compare Database
Option Explicit
Dim oExcel As Object
Next comes the GetExcelPMT function declaration with the function name, specifying the same five parameters that are used in Excel’s PMT function: periodic rate, number of periods, present value, future value, and payment type. The return value data type is also specified.
Public Function GetExcelPMT( _
psgRate As Single _
, piNPer As Integer _
, pcurPV As Currency _
, Optional pcurFV As Currency = 0 _
, Optional piType As Integer = 0 _
) As Currency
Initially, an error handler is set up so the code can exit without the user realizing there’s a problem, and zero (0) is assigned for the default return value.
On Error GoTo Proc_Err
GetExcelPMT = 0
Then the oExcel object variable is tested to see if it has a value. If it’s nothing, Excel is opened. The Excel application is represented by oExcel, which is reused so subsequent calls are faster.
If oExcel Is Nothing Then
Set oExcel = CreateObject("Excel.Application")
End If
Finally, Excel’s PMT function is called. The calculated result is assigned to the function return value, and the code exits.
GetExcelPMT = oExcel.WorksheetFunction.Pmt ( _
psgRate, piNPer, pcurPV, pcurFV, piType)
Proc_Exit:
On Error Resume Next
Exit Function
SQL
Here’s the SQL statement for the qOffers query:
SELECT Offers.OfferID, Offers.CatCD, Offers.OfferName, Offers.Amount, Offers.RateAnnual, Offers.NbrYrs,
Offers.NbrPerYr,
GetExcelPMT( [Rate], [NPer], Offers.Amount, Offers.FV, Offers.TypePmt ) AS Payment,
Offers.RateAnnual/Offers.NbrPerYr AS Rate,
Offers.NbrYrs*Offers.NbrPerYr AS NPer,
Offers.FV, Offers.TypePmt
FROM Offers
ORDER BY Offers.CatCD, Offers.OfferName;
The calculated “Payment” field sends five arguments to the GetExcelPMT wrapper function. The first two arguments, Rate (periodic rate) and NPer (number of periods), are also calculated fields. The other parameters are fields in the Offers table: Amount (the loan amount), FV (future value), and TypePMT (payment type). The list is sorted by category code and then the offer name. Notice that it’s okay for an intermediate calculation to be defined in the statement after a column where it’s referenced.
CLEAN UP OBJECT VARIABLES
The ExcelClose sub quits Excel and clears the Excel object variable, but note that GetExcelPMT doesn’t call the sub. Once the query is run, Excel will stay open in the background until it’s closed. This will make future processing faster. (To check this, you can open the Windows Task Manager to see Excel still running as a background process.) One way to clean things up is to put a command button on a form to open the query. In that form’s Unload event, you can call ExcelClose so that it runs (and closes Excel) when the form is closed.
Download this month’s database: SF1805_Wrapper-Function.
SF SAYS
A wrapper function that interfaces with Excel can be defined in VBA and then used in an expression for a calculated field in a query and elsewhere.
May 2018
