Variables, Parameters and Functions
Exercise 4.01

Exercise 4.01

The aim of this exercise is to use variables to calculate a new value for each item in a list.

Files Needed

Click here to download the file needed for this exercise.


  1. Extract and open the Highest Grossing Movies.xlsm workbook.  You'll find a worksheet containing a list of films and a button which runs a subroutine called Process_Film_List:
Basic worksheet

Currently, clicking the button simply selects cell A5 and moves down the list of films using a Do Until loop.  Your task is to add the code which calculates a value to write into the Money per Minute column.

  1. Open the VBE and find the subroutine called Process_Film_List in Module1.
  2. Declare three variables within the subroutine according to the table below:
Variable Name Data Type
FilmGross Double
FilmMinutes Integer
MoneyPerMinute Double
  1. Within the Do Until loop, add instructions to read the value from the Worldwide Gross column into the FilmGross variable and the value from the Run Time column into the FilmMinutes variable.
  2. Within the Do Until loop, add an instruction which divides the FilmGross variable by the FilmMinutes variable and reads the result into the MoneyPerMinute variable.
  3. Within the Do Until loop, add an instruction which writes the value of the MoneyPerMinute variable into the Money per Minute column in the worksheet.
  4. Run the subroutine and check that it works:

Click the button on the worksheet and check that a value appears in column E for each film.

  1. Save and close the workbook.

Answer Files

Click here to download a file containing a suggested answer.

This page has 0 threads Add post