Use Excel's PMT function in Access!

use Excel worksheet functions in Access! This example shows you how to use the PMT function to calculate the payment for a loan.

What Excel has, Access can use! Learn how to use Excel functions like PMT in Access. This happens because VBA can reference Excel, and use what it has.

Excel's PMT (payment) function is discussed in detail. If you are in a cell, you can be prompted for arguments ... how do those translate what is needed if you are in Access? How do you calculate the function arguments from the data that you have?

In Excel, as you type a formula, you are prompted for arguments, and you can pick cells. There is no error checking. What is something needed is missing? How can you know what the problem is? By using VBA, we can trap problems before run-time errors happen ... step in and make things more friendly.

Although it is not necessary, we validate data just to make it nicer for the user who might not know what is wrong if an error happens. If data is okay, we assign values and calculations to variables. To enhance performance and readibility, the code uses With and End With blocks. Big discussion on this -- so hopefully it will be clear as mud in the end (smile).

In our PMT example, the data entry form has required and optional data that can be specified.

Required:
Loan Amount,
Annual Interest Rate,
Number of Years,
and Number of Payments Per Year
Optional:
future value (default is zero, 0)
type - when payments are due

The PMT function need calculations from the above inputs -- see how this is done for:
periodic interest rate
number of periods
present value

Go to the design view of the form and inspect properties. Launch the code builder to trace what happens when the Calculate button is clicked.

Using VBA, we can validate data so that if the value is null (missing!), the user focus will be set to the control that needs to be filled, the user gets a message, and the code exits. Otherwise, that value or a calculation involving it, is assigned to a variable.

See how Excel can be referenced so that itsa functionality can be used. Here is the statement that runs Excel's PMT function, using variables that represent the information needed, and returns the result to the curPayment variable:

curPayment = oExcel.WorksheetFunction.Pmt _
(sgRate, iNPer, curPV, curFV, iType)
WHERE:
oExcel is the Excel application object
Pmt is the PMT Worksheet Function
sgRate, iNPer, curPV, curFV, and iType are variables that hold values for each of the function arguments

We also go into a discussion of using With Me.someObject to enhance performance and readability. The term 'object' hangs a lot of people up -- hopefully this lesson can help make things more clear. An object is something. A ball is an object with methods such as toss and bounce; and properties such as size, color, and weight.

See how to Dim an object variable for Excel and then set the reference to it using CreateObject. Pass what Excel needs to calculate a payment using the PMT function.

In VBA, learn how to find the definition of a variable. See how dimensioning a variable at the top of a module in VBA enables all the procedures in that module to see its value.

See how to enhance performance by reusing the Excel object and then releasing it when the form is closed by using the Form_Unload event.

Test the data validation and see how messages are displayed when required data is missing. Identify problems before errors happen. It is more friendly to be told what is wrong and to be placed where data is missing.

Download free from the Tools page of MsAccessGurus.com:
http://www.msaccessgurus.com/tool/ExcelPMT.htm

* let's connect *
Do you need help? You know your business. I know how to teach, write programs, and develop applications. If you have a budget and want control of your project, let's connect and build it together!

visit MsAccessGurus and contact me today!
http://msaccessgurus.com

I look forward to hearing from you ~

have an awesome day,
crystal