EXERCISE TOPIC▼
VBA EXERCISES▼
EXCEL VBA MACROS EXERCISES▼
Excel VBA Macros | Looping over collections 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.
You can learn how to do this exercise if you attend one of more of the courses listed below!
| Software ==> | Excel VBA Macros (52 exercises) |
| Version ==> | Any version of Excel |
| Topic ==> | Looping over collections (11 exercises) |
| Level ==> | Harder than average |
| Classroom ==> | Excel VBA macros / Fast track Excel VBA |
| Online ==> | Excel VBA macros / Fast track Excel VBA |
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
wsCalculator.Copy _
Before:=OtherWorkbook.Worksheets(1)
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.