Have you thought about buying a house? a camper? adding on?
How much would you have to borrow?
And what would the payments be?
Excel has a lot more built-in functions than Access
and guess what, you can use Excel functions in Access!
For instance, Excel has a function called PMT (payment).
You might have used this to calculate
the periodic payment for an annuity,
or the monthly payment for a loan.
Hi, this is crystal.
Let me show you how you can use Excel's PMT function in Access.
This example shows a form.
This section is for Input.
The controls have a yellow background,
because, as this form is designed,
these are the values that feed other calulations, and are required.
In column 2, you see the arguments for the PMT function.
The top 3 are calculated from what is input.
The bottom 2 are optional and can be changed if you want.
Here is a simple example.
Let's say you can get a loan for 25,000.
The annual rate is 5%.
The number of years is 10.
You will make monthly payments,
so the number of payments per year is 12.
What will those payments be?
Here is the Excel function wizard showing the 5 arguments for the function.
The first three are are required.
The last two are optional.
The arguments for the PMT function are calculated from the values that are input.
First is the periodic interest rate.
This is the annual interest rate divided by the number of payments per year.
The second parameter is the number of periods.
This will be the #Years times the #Payments/Year.
The third parameter is the present value.
When borrowing funds, this is the negative loan amount,
The 4th parameter is optional and represents the future value.
This is the cash balance after last payment is made.
The 5th parameter is also optional .
It is either 0 or 1 and indicates when payments are due.
Zero (0) means that payment is made at the end of the period,
and 1 means the payment is made at the beginning.
When you click the Calculate Payment button,
arguments are passed to the Excel PMT function,
which returns a result of 265.17
The total interest paid over the 10 years will have been 6,820.24
Flipping over to Excel, we can see the same results,
so that tells us that the calculation is working.
Let's go back to Access.
How is this possible?
That is one of the beauties of Microsoft Office.
The components all work together.
What Excel has, Access can use.
How was this done?
Let's go to the design view of the form
and see what happens when the Calculate Payment button is clicked.
Press Alt-Enter to open the Property sheet if it is not showing.
You can also use the ribbon.
Select the control you want information about, the calculate button.
Go to the Event tab of the property sheet,
and click the Builder button (...) for the [Event Procedure]
listed in the On Click property.
This takes us to the VBA that does the magic.
First, 5 variables are dimensioned to represent
each of the 5 arguments for the PMT function.
sgRate is the periodic rate.
It is a single precision number and can have decimal places.
iNPer is a whole number representing the number of payments,
or the number of periods.
curPV is the present value.
It is currency data type, which means it will carry 4 decimal places.
It will also use banker's rounding.
curFV is the future value.,
and iType is how payments will be made.
A variable for the result, curPayment,
is also dimensioned as currency.
These next statements to validate information are not necessary,
but because it is good practice to
let code trap as many errors as possible,
so required data is checked.
If the Loan Amount is null, that means it doesn't have a value.
If that is true, the focus for the user is set to that control,
and the user will see a message that data is missing.
Then the code exits.
If there is a value,
we assign the negative amount of the value to the curPV variable,
which is the present value of your loan,
and one of the arguments for the PMT function.
With Me.txtLoan helps performance.
'txtLoan' is the Name of the control that holds the loan amount.
With me.SomeObject is like picking up a ball.
Once it is in your hands, you can toss it up in the air,
bounce it, pass it to someone else ...
These are methods -- things that the ball can do.
When the ball is in your hands, you can also see its properties;
how big it is, what color it is, how heavy it is,
what it's made of, its shape, and so on.
With Me.txtLoan means that we are going to be
referencing the control with the Loan amount.
Between With and End With are words prefaced with a dot.
These represent properties or methods of the object we are using.
First we check its Value property.
Then we may invoke the SetFocus method.
If we are still here, we assign the negative of its value
to the variable for the present value of the loan.
Going back to the ball ... not using With is like
picking a ball up to see if it is the right one,
it could just be a big white rock in the grass.
When you touch it, you know if you have the ball.
So if it is the ball,
would you set it down and stand up again?
Then lean over and pick it up again,
so you could throw it to someone
who has a chance to get someone out on second base?
Of course not.
By the time you did all that, the runner would probably be safe.
Using With makes it more efficient to process
multiple actions and queries.
With Me.someObject also makes code easier to read.
This makes it clear that everything prefaced with a dot
is about this control.
Ok, off my soapbox.
The Annual interest rate needs to be known
If a value is needed, focus is set, the user gets a message, and code exits.
The number of years must also be specified.
If it is blank, focus is set, the user gets a message, and code exits.
So far, each block of code has followed the same pattern.
A control is referenced.
Its value is checked
If data is missing, the user gets a message and the code exits.
Variables may or may not be assigned.
Lastly, the number of payments must be known.
If that is missing, the code places the user where the data is missing,
gives them a message, and exits.
After data is okay, calculations are made,
and variables are assigned.
The period rate is the annual rate
divided by the number of payments per year.
This expression is wrapped in the Round function
to limit the calculated rate to 6 decimal places.
The number of periods is the number of times per year times the number per year.
This is rounded up to the next whole number by adding 0.9999,
and then taking the integer portion of the result.
If the future value control is blank,
0 is assumed since that is the default value for the PMT function,
and processing continues.
If the type is null, 0 is assumed again since
this is the default Excel uses if not all arguments are specified.
We send all five arguments,
and make the same assumptions that Excel does for the optional arguments.
Now that everything the function needs has been verified,
we can use the PMT function to calculate the result.
oExcel means that we will use Excel
WorksheetFunction means that a worksheet function will be used.
Pmt means the PMT function.
Space underscore at the end of the line
means the statement is continued on the next line.
This is done so lines are shorter,
and don't print to places where you can't see without scrolling.
The 5 variables representing each of the
5 arguments that the PMT function needs
are enclosed in parentheses.
The result of the calculation is assigned to the curPayment variable
With the textbox control for the payment result on the form,
set the value to the curPayment variable,
and set the focus there, which makes it easy for the user to copy and paste if they wish.
Next, the form is recalculated, just in case.
Then the user gets a message that the calculation is done.
Could we have skipped all that variable assignment and checking?
Yes. You can also reference the control names directly,
and go right to the PMT function, like this:
curPayment = oExcel.WorksheetFunction.Pmt
(Me.txtRate, Me.txtNPer, Me.txtPV, Me.txtFV, Me.txtType)
ok, that's great ...
but ... wait a minute, lets back up a bit.
oExcel was used but it is not dimensioned here.
So how does Access know what it represents?
Good question.
To find that out, right-click on the oExcel variable name
and select Definition from the shortcut menu.
This moves you to the top of the module,
where you see that oExcel is dimensioned as an object.
Dim oExcel As Object 'Excel.Application
Excel.Application, afterward, is commented
so you know what it will be, since Object is not specific;
its used for late-binding.
oExcel is an object so it starts with "o"
It represents the Excel application --
could have been more wordy in the name and specified "application" too,
but simply decided to call it oExcel.
It is dimensioned at the top of the module so that
all procedures in the code behind the payment form can see its value.
It is more efficient to set the Excel object reference once,
and keep using that while the form is open.
This like checking out a book from the library
so you can open it up at home as opposed to running to the library everytime you want to look something up
... or, if looking up things online,
it's like keeping a page open so you can read more later,
versus having to find and navigate again.
If this is the first time this function is being used,
or the Excel reference has been lost for another reason,
the Excel reference is set.
This takes a little bit of time,
which is why the code reuses it when it can.
Set oExcel = Nothing
To clean up memory being used,
the Excel object variable is released, if it set,
when the form is unloaded
But wait, wait, wait wait ...
If we created an instance of Excel,
don't we need to quit that?
Yes, we do.
So before we can set the (Excel) object variable to nothing,
we also must Quit Excel.
Click Reset to clear the Input values and start again.
If you borrow 30,000 at 6%
and pay it back monthly over 5 years,
the payment each time will be will be 579.98
A lot can go wrong.
What if everything isn't filled?
If a formula references a cell with bad data in Excel,
you don't know where the error is,
just that something is wrong and the function can't be calculated.
Because we are using VBA and not directly passing what the user entered,
we can identify problems before errors happen.
This code has a lot of data validation.
What if the number of payments per year is missing?
The user is put where required data is missing and they can fill it.
What if the numer of years is not filled out?
What is there is no interest rate?
What if the loan amount is missing?
It is a lot more friendly to be told what is wrong
and to be placed where data is missing;
as opposed to just telling you, "there is some error".
In summary, you've had an in-depth review of Excel's PMT function.
You've seen how to use an Excel worksheet function in Access.
You saw how to validate data so bad information is trapped.
You learned about using With and End With
so code performs better and is easier to read.
Get the free download.
It's a cool tool, and you have the source code so you can see exactly what is happening,
and change it if you wish, and learn.
http://www.MsAccessGurus.com/Tool/ExcelPMT.htm
Thanks for joining me,
through sharing, we will all get better ~ crystal