ALL SOFTWARE EXERCISES
- EXERCISES HOME PAGE (942)
- Access 2010 (66)
- Access VBA Macros (17)
- Advanced VBA (29)
- DAX (0)
- Excel (37)
- Excel 2010 (83)
- Excel 2013 (10)
- Excel VBA Macros (49)
- Power BI - Excel 2013 (25)
- Power BI - Excel 2016 (24)
- Power BI Desktop (26)
- PowerPivot 2010 (26)
- Report Builder (42)
- SQL (192)
- SSAS - multidimensional (21)
- SSAS - tabular (29)
- SSIS Integration Services (40)
- SSRS 2012 (53)
- SSRS 2016 (43)
- Visual Basic (45)
- Visual C Sharp (65)
- WPF - Visual C# (20)
EXCEL VBA MACROS EXERCISES
Exercise: Compare Mortgage Deals
This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.
The answer to the exercise will be included and explained if you attend the course listed below!
You need a minimum screen resolution of about 700 pixels width to see our exercises. This is because they contain diagrams and tables which would not be viewable easily on a mobile phone or small laptop. Please use a larger tablet, notebook or desktop computer, or change your screen resolution settings.
Open the file called Mortgage calculator.xlsm and enter some values at the top of the Inputs sheet.
Enter the amount you want to borrow and how many years you'd like to repay the loan.
Create a subroutine which begins by copying the values you have entered into the appropriate cells on the Calculator sheet.
Copy the values into these pale yellow cells on the Calculator sheet.
Add code to create a new workbook. You'll find it useful to capture a reference to this workbook in a variable.
Dim OtherWorkbook As Workbook
Set OtherWorkbook = Workbooks.Add
Add a For Each loop which loops through the list of mortgage deals on the Inputs sheet. Copy the details of each deal into the appropriate cells on the Calculations sheet.
Copy the details for each deal into the pale yellow cells.
After setting the details for a deal within the loop, copy the Calculations worksheet so that it becomes the first worksheet in the workbook you created earlier. Use the code below to guide you:
'copy a worksheet to another workbook
Still within the For Each loop, change the name of the copied worksheet to match the reference number of the deal.
Your worksheet names should look like this.
After the loop has finished, add code which deletes Sheet1 from the output workbook.
To prevent a warning message appearing when you do this, add this line before you delete the worksheet:
Application.DisplayAlerts = False
Add code which saves the output workbook and then closes it.