Principles of Financial Modelling

CHAPTER 29Working with Objects and Ranges

INTRODUCTION

This chapter introduces the structure of objects within the Excel environment. We focus on those that are key in general financial modelling, especially:

• Referring to ranges of cells.
• Using collections of objects and hierarchies of objects.

OVERVIEW OF THE OBJECT MODEL

For many modellers, the subject of Excel objects is not one that would seem to be of immediate relevance: first, many traditional Excel models are based on arithmetic operations and functions, with objects apparently having no real role. Second, simple VBA code can be recorded or written without there appearing to be any need to refer to objects specifically. However, knowledge of the principles of Excel objects is very important to be able to write clear, flexible and robust code, as well as to access the wide set of applications that the Excel/VBA object environment allows, so that one can develop (or audit) a wider set of modelling applications.

Objects, Properties, Methods and Events

In many ways, the logic used in the Excel/VBA environment is similar to that of natural language:

• Objects are analogous to nouns. They are essentially the parts of Excel that can be seen, including cells, ranges, rows, columns, workbooks, worksheets, charts, PivotTables, text boxes, cell comment boxes, shapes, and so on.
• Properties are analogous to adjectives, describing some aspect of an object, of which there are generally several. For example, properties of a book ...