|

Access: Use Excel with a Wrapper Function

By Crystal Long
May 1, 2018
0 comments

Last month, we used VBA to calculate a monthly loan payment on a form using Excel’s PMT function, but that code was limited to run only in that form. What if you wanted to be able to use it elsewhere? With a wrapper function, you only need to create it once, and then you can use it anywhere that’s appropriate in the database.

 

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.

 

Crystal Long teaches and develops with Microsoft Access, Excel, and Office and specializes in remote training. She connects and helps as your project is built. Visit www.MSAccessGurus.com for information. Share your perspective! Add your comments about the article below.
0 No Comments

You may also like