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
Range("A1").Offset(1,1)
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.
With Workbooks("Book1.xls")
.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.
End With
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.
With Workbooks("Book1.xls").Worksheets(1)
.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.
End With
Well, that’s a big taste, so you get tomorrow off. Monday we’ll talk about events.
Lot’s of juicy info to digest here!
Plenty to keep me busy until Monday!
Bookmarked!
Great overview!
thank for your help with nothing, but could you please give me the surface area of ten common object it would be a great help.
Thanks,
your friend
Justin
I am looking to use the following code, but have it only paste VALUES.
.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.
Can you help?
No need to select but it is done in two steps
Range(“B1”).PasteSpecial xlPasteValues
Or you could do it in one step:
Or if you want to be really brief:
Doug – The bracket shorthand is fine if you want to save a couple keystrokes, but as soon as you want to use the code on a sheet which isn’t the active sheet, it’s broken. I might overdo it, but I reference everything, workbook-sheet-range. It also helps with rereading the code in a month, when you’ve forgotten what you were doing.
Simon – Use .Value on both range objects:
Range(“B1?).Value = Range(“A1?).Value
It’s like the brackets above. It’s probably okay to rely on not stating the default property, because I guess that’s not likely to change, but it’s easier to reread the code next month.
Jon – a fair point in general, but in this case you can specify worksheets within the brackets.
How do you feel about?:
[sheet3!B1].Value = [sheet2!A1].Value
End With
Now here’s an oddity. If you use:
Range(“A1?).Copy Destination:=Range(“B1:D10?)
It will copy the formula in A1 into B1:D10 as a relative range, as you would get if you used the clipboard.
But this:
[sheet2!B1:D10].Formula = [sheet2!A1].Formula
will copy the formula in A1 to B1 without adjusting the range, but all the copies of the formula in the other cells in the range are copied relative to B1. For example, if A1 is =A2+1, then cells B2 to D1 will be:
=A2+1
=B2+1
=C2+1
Doug –
1. You’re still using a shortcut (square brackets) which can get you into trouble (and the “With ActiveWorkbook” block is redundant if you use the brackets). The only time I use these shortcuts is in the Immediate Window, where it won’t return at some inopportune future time to byte me.
2. The first statement (Copy Destination) is like using copy-paste via the UI. There is no direct equivalent in the UI for the .formula = .formula statement. It’s not really an oddity, it’s the difference in behavior between copy-paste and assigning formulas directly.
And Doug,
I don’t feel those two are oddities, I would rather expect that behavior. The first one, as you said, is what you would get when doing those steps manually, copy A1, select B1:D10, paste and you get the formula relative to B1.
The second one is the same as following these steps: Select B1:D10, with B1 being the Active Cell, Type your formula (=A2+1), then press Control + Enter. The [Sheet2!A1].Formula is just a string, so that line would be the same as
[Sheet2!B1:D10].Formula = “=A2+1?
I’ve found that people often trust more the R1C1 notation in VBA to expand the relative references, so they would do instead
[Sheet2!B1:D10].FormulaR1C1 = “=R[1]C[-1]+1?
“Simon – Use .Value on both range objects:
Range(“B1?).Value = Range(“A1?).Value”
Jon, the first “.Value” seems redundant as the statement works without it, but I do agree that it is good practice and consider myself educated!
Are there any pitfalls to excluding it?
I’m with Jon on referencing everything (workbook-sheet-range). I think it helps in reading the code several months down the road. Plus, I have never gotten into testosterone coding. or “I can build that procedure using 5 characters”.
Simon –
The first .Value is not redundant. You ought to have one for each referenced range. For most of these default things, the biggest drawbacks to not using them are decreased readability and increased laziness in other things.
Mike –
I’m impressed by people who can write a formula in half the characters that I use, but most of my apps are not affected by the performance of my longer formulas, plus I can read them and figure out how they work. So I don’t try to emulate these microformulas. The megaformulas either, which combine everything into a single 1024-character string. I sure don’t mind using an extra column or two, if it helps me trace the numbers and cuts the development time in half. Worksheet space is cheap, and my brainpower and time are limited.
And while I always reference everything throughly in VBA, I use only simple prefixes on variables, one or two characters only. None of the Dim gszqwertyVariable As Object for me. I’ll use g or m if its scope is global or module, I’ll use s for string, v for variant, i (or j or k) for longs (I don’t use integers), d or n or whatever for other numerics. Controls on a form get three characters (txt, lbl, chk, cmb, lst, ref), and so does the form (frm). In fact, I always create a variable for my forms, Dim frmSettings As FSettings and Set frmSettings = New FSettings, where frmSettings is the variable (the particular instance) and FSettings is the VBComponent.
I am a newbie Excel 2003 user and would like to know simply how to have Excel multiply each cell in a range of data by 5 and then direct the returns to another column within the same sheet. Can you help me?
What’s wrong with formulas in the target range? e.g., =A1*5
I am trying to autorun macro in one workbook. How do I “saveas” the workbook without saving the macro from the original workbook? Bear with me as I am learning this.
Thanks
When I do
Workbooks(1).Worksheets(1).Range(“A7:G40?).Value = _
Workbooks(2).Worksheets(2).Range(“A7:G40?).Value
it transfers the values of the cells as I would like, but not all of the formatting. I would like the column widths, bold face and similar things to be repeated in the new workbook.
Andrew: The Value property is only one property. If you want almost all the properties, use the Copy method.
Hey that’s fantastic ,Lot to learn in excel VBA..