In this post we see how to create a standalone Excel application or form using VBA macros. The application is built on a UserForm that will show up on its own when opening the file; the Excel workbook will be hidden in the background. This is useful to create independent Excel apps or forms connected to data and/or formulas and calculations in the main Excel workbook. As an example, we will create a simple mortgage calculator in Excel. The mortgage information is directly entered in the Excel app or form, with the option to calculate the scheduled mortgage payment. The calculation is actually happening through a formula in the hidden worksheet (it could also be done with macro code instead).
Mortgage Information and Payment
The following information is the main input to calculate the mortgage payments. Each of these values is entered through the app or form and copied to the worksheet to calculate the scheduled payment.
- The Loan Amount or total amount of money being borrowed (not including any previous down payment).
- The Annual Interest Rate specifically applied to the mortgage.
- The Loan Term, which is the number of years to pay off the whole sum (generally 20 or 30, or even longer, depending on numerous factors).
- Number of Payments per Year (generally 12).
In this example, the fields are added to range B3 through C6 - the name of each parameter in column B and the value in column C (see picture below).
Then we use the Excel PMT formula to calculate the scheduled mortgage payment for the given loan amount, annual interest rate, payments per year, and total number of payments. The PMT formula accepts the following parameters:
=-PMT(InterestRate/PaymentsPerYear,NumberPayments,LoanAmount)
Where each parameter corresponds to the fields explained earlier. The number of payments is the loan term (years) multiplied by the number of payments per year. Note the negative symbol added before PMT to convert the result to a positive value (as PMT is always negative).
The following PMT formula is added to range C7 with referece to each of the other fields as indicated below. Alternatively, each field can be defined as a named range and use the names instead (more professional).
=-PMT(C4/C6,C5*C6,C3)
Find more about mortgage calculation in Excel and download the Excel Mortgage Calculator with mortgage details and amortization table in this other post: Excel MortgageCalculator
Setting-up the UserForm
In the VBA editor window, insert a UserForm and rename it as appropriate (MortgageForm in this example). Add as many labels and text boxes as needed for each of the fields described earlier (Loan amount, Interest rate, Loan term, Payments per year). Another field (label and text box) is needed to show the scheduled payment (see picture below). Then add two buttons, one to calculate the mortgage, and other to close the app. Additionally, while testing, we can add a label (or button) to show the Excel application when needed. That can be removed when the Excel standalone app is made available to the end users.
Now we have to add code to the UserForm to respond when the “Calculate” button is clicked. That command button can be renamed as “CalculatePayment”, to run the code below when clicked.
The code simply copies each of the corresponding fields in the UserForm (LoanAmount, InterestRate, LoanTerm, and PaymentsPerYear) to cells C3 through C6, and then gets the scheduled payment calculated with PMT in C7 (as explained earlier) into the respective UserForm field (SchPayment).
The “Close” button closes the UserForm, and the workbook if hidden (it may be visible while testing, in that case it does not close it). It also sets the visibility of the Excel application to true if any other workbooks are open, as the application visibility is set to false when opening the file (see next).
Display a Standalone UserForm
In order to display a standalone Excel app or form, we need to set the visibility of Excel to false when opening the file. We have to add that code to the Workbook_Open event procedure in module ThisWorkbook. Then we show the UserForm that shows on its own on the screen and serves as a standalone application or form, in this case a mortgage calculator, just like the Windows calculator shows on the screen for example.
That also hides any other open Excel files, while showing the UserForm standalone on the screen. Excel is running in the background though. That’s the reason we need to show the application again when closing the mortgage calculator if any other workbooks are open (code in Close button above).
The “Show application” link has been added for security reasons, specially, while testing. But that’s not the only way to show the application while on the standalone form. If the VBA editor window was open when launching the standalone Excel app, we could edit the code or write code in the Immediate window to show the application again. A way to avoid users doing that is locking the VBA project with a password.
The Excel standalone app or form works like any other .NET Windows app programmed for example in Visual Studio. But here, Excel is actually opened and running in the background. Such standalone Excel app may be considered when linked to data in an existing Excel workbook, or when formulas or other functionality in Excel is used in the app. However, any of that can also be done with VB in Visual Studio alone, although it is probably easier to do it with VBA in Excel though.
Download Excel Standalone Form (Mortgage Calculator)
No comments:
Post a Comment