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.

The answer to the exercise will be included and explained if you attend the course listed below!

Software ==> Excel VBA Macros  (52 exercises)
Version ==> Any version of Excel
Topic ==> Looping over collections  (11 exercises)
Level ==> Harder than average
Course ==> Excel VBA macros
Before you can do this exercise, you'll need to download and unzip this file (if you have any problems doing this, click here for help).

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 values

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 to calculator

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 deal details

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.

Rename sheets

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.

This page has 0 threads Add post