Principles of Financial Modelling

CHAPTER 30Controlling Execution

INTRODUCTION

This chapter describes the key elements relating to the defining and controlling of the steps for the code to follow as it runs. We cover a range of topics including the core syntax and commands, and provide some selected examples of these.

CORE TOPICS IN OVERVIEW

Input Boxes and Message Boxes

The use of the InputBox, MsgBox and Application.InputBox were mentioned in the last chapter, and so are not addressed further here in detail. However, it is worth noting that the use of these approaches means that code execution is halted until some feedback is provided by the user, which can be inconvenient in cases where one wishes to complete code to run automatically and without intervention.

For…Next Loops

The use of a For…Next loop is the most basic and important approach to automate repetitive operations involving calculations. The core syntax is typically of a form such as:

For i = 1 To 1000
…Code to be executed (that involves i)
Next i

or

For i = 1 To Range("NLoops")
…Code to be executed (that involves i)
Next i

These create a looping process in which an indexation variable i starts with the value 1 and is incremented by the default of 1 each time that a step of the loop is executed (and hence takes the values 1, 2, … . in sequence).

Some basic points to note are:

• The Next statement is required to define at what point to start to repeat the operation within the loop, rather than moving to the code steps that are intended to ...