Excel has many objects and each of those objects has many properties and methods. To list them all would take an on-line help system. Fortunately, Excel comes with an on-line help system. And don’t forget you can always use the Macro Recorder to see what you need. Here I list some common properties and methods for a few objects. This is by no means complete, it’s just meant to give you taste of what’s available.
Properties That Return Objects
Believe it or not, all objects (except maybe Application) are accessed via properties. When you use Workbooks(1), you’re actually using the Workbooks property of the Application object. That property returns a Workbooks Collection object that you can use just like the object itself. It really doesn’t matter whether you think you’re using an object or a property that returns an object, but some properties that return objects aren’t so straight forward.
The Offset property of the Range object returns another Range object. When you use Offset, you are working with a Range that’s a specified number of rows and columns away. Code like this
is incomplete. You need to do something with the new Range object, like
Range("A1").Offset(1,1).Value = 200
Since the result of the Offset property is a Range object, all the properties and methods of Range are available to use, such as the Value property in this case.
In most cases, a property that returns an object has the same name as the object itself. The Workbooks property returns the Workbooks collection object. The Worksheets property returns the Worksheets collection object. For those properties, I haven’t described them here. But some of the properties I describe do return a reference, and I’ve noted that.
The Application Object
Application.ScreenUpdating = False – Causes Excel to NOT redraw the screen. This can speed up your macros because redrawing takes some time. Be sure to set it back to True at the end of your macro.
Application.DisplayAlerts = True – Causes Excel to suppress those message boxes that you get every once in a while, like when you delete a sheet. Usually, setting this property makes Excel choose the default button for which ever message is being suppressed, although I’ve seen exceptions to this.
Application.Calculation = xlCalculationManual – Can also be xlCalculationAutomatic. This one also speeds up your macros because normally Excel recalculates every time something changes.
Application.CutCopyMode = False – If you’ve copied or cut something, setting this property to False makes it so Excel thinks there’s nothing to paste. It gets rid of those annoying “marching ants” that surround a cell that’s been copied.
Application.Visible = False – That says it all, it hides everything.
The Window Object
Windows is a collection object. Collection objects are collections that contain similar objects, like Window or Workbook objects. They have their own properties and methods, but most of the time they are used to get at a specific object. In these examples, I get at the Window object named Book1.xls. Window objects are different than Workbook objects, so don’t get them confused.
Windows("Book1.xls").Close – Closes a window. It will close the workbook if there’s only one window open for that workbook.
Windows("Book1.xls").SelectedSheets – This is one of those properties that returns an object. Specifically it returns a collection of Sheet objects that are currently selected. You’ll see it when you record a macro to print.
Windows("Book1.xls").VisibleRange – This property returns a Range object that consists of every cell that the user can see.
The Workbooks Collection Object
As discussed in Navigating, I’m omitting the Application object because it is assumed.
Workbooks.Open "C:Book1.xls" – Opens the specified workbook. If you don’t include a path (just a file name), it looks in the current directory. Open is a method but it also returns an object, the workbook that was just opened.
Workbooks.Add – Creates a new workbook and returns an object reference to it.
Workbooks.Count – Returns the number of workbooks that are currently open.
The Workbook Object
Note that these examples use the Workbooks collection object to get to a specific Workbook object. You can also use ThisWorkbook, ActiveWorkbook, or your own object variable to refer to a workbook.
Workbooks("Book1.xls").Close SaveChanges:=True – Closes the workbook. The SaveChanges argument is a handy way to to avoid the message that pops up when you try to close an unsaved workbook.
Workbooks("Book1.xls").Save – Saves the workbook. If the workbook is previously unsaved, it saves it to the current directory and adds “.xls” on to the end. It’s best to use SaveAs in that situation.
Workbooks("Book1.xls").SaveAs "C:Book2.xls" – Works just like File>Save As. If you don’t specify a new filename, the existing filename is used.
Workbooks("Book1.xls").SaveCopyAs "C:Book2.xls" – This saves a copy of the workbook to a new filename, but keeps the original workbook open, not the copy.
The Worksheet Object
Since I attempted to make a case for fully qualifying object references yesterday, I enclose these examples in a With block. Don’t infer that these examples will actually work as written, they’re just illustrative. I use the Worksheets collection object to get to a particular worksheet in these examples.
.Worksheets(1).Copy After:=.Sheets(.Sheets.Count) – Copies the worksheet to a new location. If you specify the Before or After argument, you can control where the sheet gets copied. This example copies it to the end of the same workbook. You can also specify a different workbook in those arguments. Omitting the arguments altogether creates a new workbook with the copied sheet.
.Worksheets(1).Delete – deletes the worksheet
.Worksheets(1).Paste – If there’s something in the clipboard, this method pastes it to the ActiveCell.
.Worksheets(1).PasteSpecial Format:=xlPasteValues – Same as Paste, except it’s special.
.Worksheets(1).PrintOut Copies:=2, Collate:=True – Prints the worksheet.
.Worksheets(1).Protect Password:="Mypassword" – Protects the worksheet. The password is optional.
.Worksheets(1).Unprotect Password:="Mypassword" – Unprotects the worksheet.
.Worksheets(1).UsedRange – Returns a Range object consisting of a rectangle of cells that encompasses every cell that’s used on the worksheet.
.Worksheets(1).Visible = xlSheetHidden – Hides the worksheet. Can also be xlSheetVeryHidden or xlSheetVisible. If you use xlSheetVeryHidden, the user cannot unhide the sheet from the user interface.
The Range Object
The Range object is a strange beast, but one you’ll use quite often. I kind of works like a collection object because you have to identify which range you want. But you always have to identify it, that is, Range has no properties and methods of it’s own.
Because it’s used so much, there are a lot of ways to get a Range object reference. One additional way shown here is the Cells property of the Worksheet object. Cells is not an object, but a property that returns a Range object.
.Range("A1").Value – Good old Value property. Sets the value of the range.
.Range("A1").ClearContents – Clears the contents, but not the formatting, of a range.
.Range("A1").Copy Destination:=.Range("B1") – Copies the cell to the clipboard. If you specify the optional Destination argument, it’s automatically pasted to that range. If you don’t, you would need to select a different cell and use the Paste method of the Worksheet object. You know how I feel about selecting, so use the Destination argument.
.Cells(1, 1).Delete xlShiftUp – Deletes the cell like using Edit>Delete. The other option for the argument is xlShiftToLeft.
.Cells(1, 1).End(xlDown) – Also xlUp, xlToRight, or xlToLeft. This is like hitting the End key on the keyboard and then an arrow key. It’s handy to find the next blank cell in a column or row. It returns a Range object.
.Cells(1, 1).EntireColumn – This property returns a reference to the Range object that consists of the whole column(s) where the range is. There’s also an EntireRow property.
.Range("A1:A10").FillDown – Similar to using Edit>Fill>Down. In this example, the value in A1 will be filled down through A10. Remember that the value you want to fill must be the first cell in the range.
.Range("A1").Formula = "=B1*10" – Used to insert a formula in a cell. The formula is a string.
.Range("A1").HasFormula – Returns True or False depending on whether the cell contains a formula or a constant.
.Range("A1").NumberFormat = "General" – Sets the number format of a cell like using Format>Cells>Number.
.Range("A1").Offset(1,0) – Offset returns a Range object that’s a specified number of rows and/or columns away. You can omit either argument, but I use zeros for clarity.
.Range("A1").Resize(,2) – This handy property returns a range that’s been resized. This example would return the range A1:B1. If you omit an argument (like I omitted the Row), that aspect of the range doesn’t change. Since A1 is one row, the returned range is also one row.
.Range("A1:C10").Sort – Sorts a range similar to Data>Sort.
.UsedRange.SpecialCells(xlCellTypeBlanks) – Using SpecialCells is like Edit>Goto and clicking the Special button. It doesn’t have to be used with UsedRange, but that’s a common application. You can really speed up your macros if you’re looping through a range by limiting the range with SpecialCells.
.Range("A1").Text – This returns what the user sees in the cell. Not necessarily it’s value because this returns formatting too.
Well, that’s a big taste, so you get tomorrow off. Monday we’ll talk about events.