Excel Annoyances

VBA ANNOYANCES

MEET OBJECT-ORIENTED PROGRAMMING

The Annoyance:

I read somewhere that Visual Basic for Applications (VBA), the macro programming language that underlies Office programs such as Excel, uses an object-oriented programming model. What the heck is that?

The Fix:

Object-oriented programming is a metaphor that helps you organize the things in your programming environment. Five basic elements form the basis of object-oriented programming:

• Objects, which represent things (such as worksheets, workbooks, cell ranges, and so on)

• Properties, which describe objects (such as a worksheet’s name, or a cell’s value)

• Methods, which refers to which procedures an object knows how to do (such as save a workbook or activate a worksheet)

• Events, which are what an object knows how to respond to (such as activating a cell or saving a workbook)

• Collections, which are sets of objects (such as all the worksheets in a workbook or all the cells in a range)

You concatenate an object’s properties and methods using dot notation: when you name the object, follow it with a period and then type the property or method you want to call. Here are two examples:

    Worksheet("Sheet1").Name = "January"
    Range("A1").Calculate

The first statement sets the name of the worksheet called Sheet 1 to January, while the second statement uses the Calculate method to make Excel recalculate the result of the formula in cell A1.

Programming in VBA is just like programming in any other language: there’s a learning curve. ...