VBA provides you with some statements (like Dim) and some functions (like Now). You can make some pretty nice procedures with just native VBA stuff. When you use VBA in Excel, most of your macros will be using objects. If they didn’t, you really wouldn’t need to be using Excel for your task. So what are objects?
VBA exposes what is called an object model – namely Excel’s object model. The object model is a big hierarchy of objects that you can manipulate, thus manipulating aspects of Excel. Objects are things. A cell is an object, a worksheet is an object, and a workbook is an object. There are tons of objects in Excel’s object model.
Objects have properties and methods. Properties are like characteristics of the objects. Methods are like actions of the object. How about an analogy?
You are an object, a person object. You have properties like eye color, hair color and height. You have methods like speak, run and sleep.
A cell is an object also, a Range object. It has properties like Value, Address, and HasFormula. It has methods like ClearContents, Activate, and Copy.
Properties hold values that define the object. Which cell? Check the Address property. What’s in the cell? Check the Value property. Does the cell contain a formula? Check the HasFormula property. Some properties can be changed directly, some indirectly, and some not at all. The Address property cannot be changed. Cell A1 will have the Address of $A$1 no matter what you do. You can select a different cell, but that doesn’t change the address of cell A1. Value is a read/write property. You can set it directly like
Range("A1").Value = 10
You can also change the Value property of a cell by executing a method. This method removes the Value property (Sets it to Empty)
Name is a property of the Workbook object. Every workbook has a name, but you can’t set the Name property directly. Name is a property that can only be changed via a method. This doesn’t work
ThisWorkbook.Name = "MyBook.xls"
PersonObject.HairColor = Red
If you want to change the Name property of a Workbook object, you have to use a method. Similarly, you can’t change your hair color just by thinking about it, you have to do something. You have to use the DyeHair method. (You can also use the Age method, but you can’t set hair color – it’s always gray.)
Many of the methods simply change properties. Simply may not be the right word because some methods change a LOT of properties. Take the Calculate method.
It does a lot of work recalculating the worksheet, but all you really care about is that it’s changing (or at least checking) the Value property of every cell on the sheet. On the other hand, the Save method of the Workbook object does a heck of a lot more than just change the Saved property to True, it writes the file to disk. That’s pretty important.
Remember this: Objects are things in Excel; Properties are characteristics that define those objects; and methods are actions that the objects take and that change a few properties along the way.
Monday, we’ll look at recording macros using Excel’s Macro Recorder. Recording is one of the best ways to determine which object, property and/or method you need.