Working with Objects and Ranges
Michael J. Rees · 2018
This chapter introduces the structure of objects within the Excel environment. It focuses on those that are key in general financial modelling, especially: Referring to ranges of cells; and using collections of objects and hierarchies of objects. The use of the Set statement is of fundamental importance when working with Excel objects. The Set statement can be used not only to overcome this ambiguity, and is in fact generally necessary, especially when the left-hand side of an “=” statement is an object. Within VBA code, it is almost always preferable to use named ranges instead of direct cell references. As well as being more robust if the worksheet structure were to change (new rows or columns added), their use generally creates more transparency. There are several situations that arise, which affect the appropriate point at which a name should be assigned to a range. The chapter discusses these situations.