This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	Excel VBA Macros (52 exercises)
Topic ==>	Looping over collections (11 exercises)
Level ==>	Harder than average
Subject ==>	VBA training

Loop over a set of mortgage deals and create an output file which compares them.

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.

Before you can do this exercise, you'll need to download and unzip this file .

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. This might buy a small shed in London ...

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 coloured 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.