Printer Friendly

USE EXCEL WITH A WRAPPER FUNCTION.

Last month, we used VBA to calculate a monthly loan payment on a form using Excels 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.

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. Next comes the GetExcelPMT function declaration, 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.

Initially, an error handler is defined so the code can exit without the user realizing there's a problem, and zero (0) is assigned for the default return value. 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. Finally, Excel's PMT function is called. The calculated result is assigned to the function return value, and the code exits.

SQL

Here's the SQL statement for the qOffers query:

SELECT Offers.OfferlD, 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, O f fe rs. Nb r Yrs * O ff e rs. Nb rPerYr 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 LTnload event, you can call ExcelClose so that it runs (and closes Excel) when the form is closed.
Figure 2
Option Compare Database
Option Explicit

Dim oExcel As Object
'--GetExcelPMT
Public Function GetExcelPMT( _
 psgRate As Single _
 , piNPer As Integer _
 , pcurPV As Currency _
 , Optional pcurFV As Currency = 0 _
 , Optional piType As Integer = 0 _
 ) As Currency
 On Error GoTo Proc_Err
 GetExcelPMT = 0
 If oExcel Is Nothing Then
  Set oExcel = CreateObject("Excel.Application")
 End If
 GetExcelPMT = oExcel.WorksheetFunction.Pmt ( _
  psgRate, piNPer, pcurPV, pcurFV, piType)
Proc_Exit:
 On Error Resume Next
 Exit Function
Proc_Err:
 Resume Proc_Exit
End Function
'--ExcelClose
Public Sub ExcelClose ()
 On Error Resume Next
 If oExcel Is Nothing Then Exit Sub
 oExcel.Quit
 Set oExcel = Nothing
End Sub


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.

Caption: Figure 1
COPYRIGHT 2018 Institute of Management Accountants
No portion of this article can be reproduced without the express written permission from the copyright holder.
Copyright 2018 Gale, Cengage Learning. All rights reserved.

Article Details
Printer friendly Cite/link Email Feedback
Title Annotation:Access
Author:Long, Crystal
Publication:Strategic Finance
Geographic Code:1USA
Date:May 1, 2018
Words:935
Previous Article:CONSOLIDATIONS IN EXCEL.
Next Article:BUILDING CONFIDENCE IN NONFINANCIAL REPORTING: The keys are effective internal controls and technology enablement.
Topics:

Terms of use | Privacy policy | Copyright © 2021 Farlex, Inc. | Feedback | For webmasters