This chapter shows you how to start working with the Excel object model, the architecture underlying Excel. It also shows you how to perform common actions with the most immediately useful Excel objects. These objects include the Workbooks
collection and the Workbook
object, the ActiveCell
object, and Range
objects. You'll also see how to set options in Excel.
In this chapter you will learn to do the following:
Work with workbooks
Work with worksheets
Work with the active cell or selection
Work with ranges
Set options
It's not crucial to understand how the entire Excel object model fits together in order to work with VBA in Excel, but most people find that knowing the main objects in the object model is helpful. And often the code examples in the Help system's object model reference are invaluable—showing you how and where to employ objects in your own programming.
To see the Excel object model reference, follow these steps:
Launch or activate Excel, and then press Alt+F11 to launch or activate the VBA Editor.
Press F1 in the editor to launch Help.
If necessary, click the book icon (second from the right in the row of icons in the Excel Help dialog box). This opens the table of contents pane within the Help dialog box.
In the table of contents, click Excel Object Model Reference. You'll now see the whole collection of syntax specifications, useful descriptions, and code examples, as shown in Figure 22.1.
Excel exposes (makes available for your use in code) various creatable objects, meaning that you can employ most of the important objects in its object model without explicitly going through (mentioning) the Application
object. For most programming purposes, these creatable objects are the most commonly used objects. Here's a list:
The Workbooks
collection contains the Workbook
objects that represent all the open workbooks. Within a workbook, the Sheets
collection contains the Worksheet
objects that represent the worksheets and the Chart
objects that represent chart sheets. On a sheet, the Range
object gives you access to ranges, which can be anything from an individual cell to a complete worksheet. Because it's creatable, you need not write Application.Workbooks
in your code. You can leave off the Application
and merely write Workbooks
.
The ActiveWorkbook
object represents the currently active workbook.
The ActiveSheet
object represents the active worksheet.
The Windows
collection contains the Window
objects that represent all the open windows.
The ActiveWindow
object represents the active window. When using this object, be sure to check that the window it represents is the type of window you want to manipulate because the object returns whatever window currently has the focus.
The ActiveCell
object represents, you guessed it, the active cell. This object is especially valuable for simple procedures (for example, those that compute values or correct formatting) that work on a cell selected by the user.
In many of your Excel procedures, you'll need to manipulate workbooks: creating new workbooks, saving workbooks in various locations and formats, opening existing workbooks, closing workbooks, and printing workbooks. To do so, you work with the Workbooks
collection, which contains a Workbook
object for each open workbook in Excel.
To create a new workbook, use the Add
method with the Workbooks
collection. The syntax is as follows:
Workbooks.Add(Template)
Here, Template
is an optional Variant argument that specifies how to create the workbook. The following subsections discuss the available options.
To create a blank workbook (as if you'd clicked the File tab on the Ribbon, then clicked the New button), omit the Template
argument:
Workbooks.Add
The new workbook receives the number of sheets specified in the Excel Options dialog box (click the File tab on the Ribbon, then choose Options to display the When Creating New Workbooks section of the dialog box—you'll see a field where you can adjust the Include This Many Sheets option.
You can get or set this value in VBA by using the SheetsInNewWorkbook
property of the Application
object. For example, the following macro declares an Integer variable named mySiNW
, stores the current SheetsInNewWorkbook
property in it, sets the SheetsInNewWorkbook
property to 12
, creates a new workbook (with those 12 worksheets), and then restores the SheetsInNewWorkbook
setting to its previous value:
Sub MVBA_New_Workbook_with_12_Sheets() Dim mySiNW As Integer mySiNW = Application.SheetsInNewWorkbook Application.SheetsInNewWorkbook = 12 Workbooks.Add Application.SheetsInNewWorkbook = mySiNW End Sub
To create a workbook based on a template, specify the full path and name of the template file. For example, the following statement creates a new workbook based on the template Balance Sheet.xlt
in a network folder \server emplateexcel
:
Workbooks.Add Template:= "\server emplateexcelBalance Sheet.xlt"
To create a workbook based on an existing workbook, specify the full name and path of the workbook file. For example, the following statement creates a new workbook based on the existing workbook named Personnel.xlsx
in the C:Business
folder:
Workbooks.Add Template:= "C:BusinessPersonnel.xlsx"
You can also create a workbook that contains a single chart, macro sheet, or worksheet by using the constants shown in Table 22.1 with the Template
argument.
For example, the following statement creates a workbook containing a single chart sheet:
Workbooks.Add Template:=xlWBATChart
The first time you save a workbook, you must specify the path and filename to use (this is the SaveAs
option). After that, you can save the workbook under the same name or specify a different path, name, format, or all three (this is the Save
option).
To save a workbook for the first time, or to save a workbook using a different path, name, or format, use the SaveAs
method. The syntax is as follows:
expression
.SaveAs
(FileName, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AccessMode, ConflictResolution, AddToMru, TextCodepage, TextVisualLayout, Local)
The components of the syntax are as follows:
expression is a required expression that returns a Workbook
object.
FileName
is an optional Variant argument that specifies the name for the workbook. If you omit FileName
, VBA uses the current folder and the default filename of Book
n.xlsx
for a workbook, where n is the next available number (for example, Book5.xlsx
).
VBA uses the default file format, which is specified in the Options dialog box's Save page. (Click the File tab on the Ribbon, then click Options to display the Options dialog box, then click the Save button on the left. You'll see a Save Files in This Format drop-down list.)
You can get and set the default save format by using the DefaultSaveFormat
property of the Application
object. For example, the following statement sets the default save format to xlNormal
, the "Excel Workbook" format:
Application.DefaultSaveFormat = xlNormal
FileFormat
is an optional Variant argument that specifies the format in which to save the workbook. Table 22.2 lists the XlFileFormat
constants for specifying commonly used formats.
Password
is an optional Variant argument that you can use to supply the password that is to be required to open the workbook (the "password to open"). Password
is case sensitive. If the user can't provide the password, Excel won't open the workbook.
WriteResPassword
is an optional Variant argument that you can use to supply the password that is required to open the workbook in a writable form (the "password to modify"). WriteResPassword
is case sensitive. If the user can't provide the password, Excel will open the workbook as read-only.
ReadOnlyRecommended
is an optional Variant argument that you can set to True
to have Excel recommend that the user open the document as read-only. Such recommendations typically carry little force, and you'll do better to protect the workbook with a "password to modify."
CreateBackup
is an optional Variant argument that you can set to True
to make Excel automatically create a backup of the workbook. The default setting is False
.
AccessMode
is an optional argument that you can use to specify whether the workbook is shared or is in exclusive mode. Specify xlExclusive
for exclusive mode, xlShared
for shared mode, and xlNoChange
to leave the access mode unchanged (this is the default setting).
ConflictResolution
is an optional argument that you can use to specify how to resolve any conflicting changes to the workbook. Use xlLocalSessionChanges
to accept the changes in the current Excel session, xlOtherSessionChanges
to accept the other user's or users' changes, and xlUserResolution
to display the Resolve Conflicts dialog box so that the user can choose how to resolve the conflicts.
AddToMru
is an optional Variant argument that you can set to True
to add the workbook to the list of recently used files at the bottom of the File menu. The default setting is False
.
TextCodePage
and TextVisualLayout
are optional Variant arguments used in international versions of Excel (not in U.S. English Excel).
Local
is an optional Variant that controls whether the language used is that of Excel (True
) or of VBA (False
). (You'll seldom need to use Local
.)
Table 22.2. XlFileFormat
constants for widely used formats
Constant | Saves Document As |
---|---|
| A normal workbook |
| An XML spreadsheet |
| A single-file web page |
| A web page |
| A template |
| An Excel workbook for Excel versions 95 and later |
For example, the following statement saves the active workbook in the current folder under the name Salaries.xlsx
and using the default save format:
ActiveWorkbook.SaveAs FileName:="Salaries.xlsx"
The following statement saves the open workbook named Schedule.xlsx
under the name Building Schedule.xlsx
in the folder named \server2Public
using the Microsoft Excel 97–2003 & 5.0/95 format (from Excel 2003):
ActiveWorkbook.SaveAs Filename:="\server2PublicBuilding Schedule.xls", _ FileFormat:=xlExcel9795
Once a workbook has been saved, you can just save it again with the same name by using the Save
method. For a Workbook
object, the Save
method takes no arguments. For example, the following statement saves the workbook named Data Book.xlsx
:
Workbooks("Data Book.xlsx").Save
The Workbooks
collection doesn't have a Save
method, but you can save all open workbooks by using a loop such as that shown in the following subroutine:
Sub Save_All_Workbooks() Dim myWorkbook As Workbook For Each myWorkbook In Workbooks myWorkbook.Save Next myWorkbook End Sub
Note that if any of the currently opened workbooks have not been previously saved, and if they include any macros, a security message will be displayed when this procedure executes. Users are told that they must agree to save the potentially dangerous executable content in a macro-enabled file format (.xlsm
). However, if the file has already been saved with the .xlsm
filename extension, no message is displayed. If you want to suppress such messages, you can insert the following code at the start of this procedure:
Application.DisplayAlerts = False
However, be sure to set the DisplayAlerts
property back to True
as soon as you can in the code. This particular warning message is quite useful as a reminder to the user—so you likely won't want to suppress it.
To open a workbook, use the Open
method with the Workbooks
collection. The syntax is as follows:
expression
.Open(FileName, UpdateLinks, ReadOnly, Format, Password,
WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter,
Editable, Notify, Converter, AddToMru, Local, CorruptLoad)
The components of the syntax are as follows:
expression is a required expression that returns a Workbooks
collection. Often, you'll want to use the Workbooks
collection itself.
FileName
is a required String argument that supplies the path and name of the workbook to open.
UpdateLinks
is an optional Variant that controls how Excel updates any links in the workbook. If you leave out this argument, the user is prompted to specify how to update the links. Table 22.3 shows the values and their effects. If Microsoft Excel is opening a file in the WKS, WK1, or WK3 format and the UpdateLinks
argument is 2
, Microsoft Excel generates charts from the graphs attached to the file. If the argument is 0
, no charts are created.
ReadOnly
is an optional Variant that you can set to True
to open the workbook as read-only. The default is False
.
Format
is an optional Variant that you can use to specify the delimiter character when opening a text file. Use 1
for tabs, 2
for commas, 3
for spaces, 4
for semicolons, 5
for no delimiter character, and 6
for a delimiter you specify using the Delimiter
argument.
Password
is an optional Variant argument that you can use to provide the password required to open the workbook (the "password to open"). Password
is case sensitive. If you omit Password
and a password is required, Excel prompts the user for it.
WriteResPassword
is an optional Variant argument that you can use to provide the password required to open the workbook in a writable form (the "password to modify"). WriteResPassword
is case sensitive. If you omit WriteResPassword
and a password is required, Excel prompts the user for it.
IgnoreReadOnlyRecommended
is an optional Variant argument that you can set to True
to have Excel ignore a read-only recommendation on the workbook.
Origin
is an optional Variant argument that you can use when opening a text file to specify the operating system used to encode it and thus how to treat carriage-return/line-feed characters and character encoding. Use xlWindows
to indicate Windows, xlMacintosh
to indicate Mac OS, or xlMSDOS
to indicate DOS.
Delimiter
is an optional Variant argument you can use with a Format
value of 6
to specify one delimiter character to use when opening a text file.
Editable
is an optional Variant argument that you can set to True
when FileName
specifies a template to open the template itself rather than start a workbook based on the template (False
). Editable
also applies to Excel 4.0 add-ins: True
opens the add-in in a visible window, while False
opens the add-in hidden. However, you can't employ this option with add-ins created in Excel 5.0 or later.
Notify
is an optional Variant argument that you can set to True
to have Excel add the workbook to the notification list when someone else has the workbook open for editing and VBA requests the workbook. Excel then notifies the user when the workbook becomes available. If you specify Notify:=False
, opening the workbook fails if someone else has the workbook open.
Converter
is an optional Variant argument that you can use to specify the first file converter to use when opening a file.
AddToMru
is an optional Variant argument that you can set to True
to add the workbook to the list of recently used files at the bottom of the File menu. The default setting is False
.
Local
is an optional Variant that controls whether the language used is that of Excel (True
) or of VBA (False
). (You'll seldom need to use Local
.)
CorruptLoad
is an optional Variant that you can use to control how Excel handles corruption it encounters when opening the workbook. Use xlNormalLoad
to use normal behavior—first, opening the workbook as usual; second, repairing the file if there's a problem; and third, recovering the data from the workbook. Use xrRepairFile
to go straight to the repair stage or xlExtractData
to go straight to the recovery stage.
Table 22.3. Values for the UpdateLinks
argument
Value | Effect |
---|---|
(if you omit this argument) | Excel prompts the user to decide how to update links. |
| User specifies how links are to be updated. |
| Links are never updated for this workbook when it's opened. |
| Excel always updates links for this workbook when opening it. |
For example, the following statement opens the workbook named Expenses.xlsx
stored in the C:Business
folder without updating links:
Workbooks.Open Filename:= "C:BusinessExpenses.xlsx", UpdateLinks:=0
The following statement opens the workbook named Plan.xlsx
stored in the D:Planning
folder, providing the password for opening the workbook:
Workbooks.Open Filename:="D:PlanningPlan.xlsx", Password:="s@cur1ng!"
The following statement opens the text file named Data13.txt
in the folder z: ransfer
using an exclamation point (!
) as the delimiter character:
Workbooks.Open _ Filename:="z: ransferData13.txt", Format:=6, Delimiter:="!"
To close a workbook, use the Close
method with the appropriate Workbook
object. The syntax is as follows:
expression
.Close(SaveChanges, Filename, RouteWorkbook)
The components of the syntax are as follows:
expression is a required expression that returns a Workbook
object or the Workbooks
collection.
SaveChanges
is an optional Variant argument that lets you specify whether to save any unsaved changes in the workbook (True
) or not (False
). If you omit the SaveChanges
argument, Excel prompts the user to save any workbook that contains unsaved changes.
Filename
is an optional Variant that you can use to specify the filename under which to save the workbook if it contains changes. In most cases, it's best to use the SaveAs
method to save the workbook under a different name before you use the Close
method to close it.
RouteWorkbook
is an optional Variant argument that you can set to True
to route the workbook to the next recipient on its routing slip or False
to refrain from routing the workbook. If the workbook has no routing slip attached, RouteWorkbook
has no effect.
For example, the following statement closes the active workbook without saving changes:
ActiveWorkbook.Close SaveChanges:=False
To close all open workbooks, use the Close
method with the Workbooks
collection:
Workbooks.Close
The Close
method takes no arguments. Excel prompts you to save any workbook that contains unsaved changes. If such prompts will be inconvenient in a procedure, use a loop (for example, a For Each... Next
loop with the Workbooks
collection) to close each open workbook individually, using the SaveChanges
argument to control whether Excel saves or discards any unsaved changes.
To determine whether a workbook is shared, check its MultiUserEditing
property. This is a read-only Boolean property.
To share a workbook, use the SaveAs
method (discussed in "Saving a Workbook for the First Time or as a Different File," earlier in this chapter) to save the file using the xlShared
value for the AccessMode
argument.
For example, the following statements share the workbook named Brainstorming.xlsx
if it is not already shared:
With Workbooks("Brainstorming.xlsx") If MultiUserEditing = False Then .SaveAs Filename:=.FullName, AccessMode:=xlShared End If End With
To protect a workbook, use the Protect
method with the appropriate Workbook
object. The syntax is as follows:
expression
.Protect(Password, Structure, Windows)
The components of the syntax are as follows:
expression is a required expression that returns a Workbook
object.
Password
is an optional Variant argument that specifies the password for unprotecting the workbook. Password
is case sensitive. You'll almost always want to supply Password
—if you don't, anybody who can open your workbook can unprotect it.
Structure
is an optional Variant argument that you can set to True
to protect the workbook's structure (how the worksheets are positioned relative to each other) or leave at its default setting, False
.
Windows
is an optional Variant argument that you can set to True
to protect the workbook windows or omit to leave the windows unprotected.
For example, the following statement protects the structure and windows of the active workbook with the password 0llsecurd
:
ActiveWorkbook.Protect Password:="0llsecurd", Structure:=True, Windows:=True
The ActiveWorkbook
object returns a Workbook
object that represents the active workbook (whichever workbook currently has the focus in the Excel window). The ActiveWorkbook
object behaves like a Workbook
object and is very useful in procedures that users execute (put another way, macros that users run) after opening the workbook that they want to manipulate.
If no workbook is open, there is no ActiveWorkbook
object, so any code that tries to use the ActiveWorkbook
object returns an error. Users can run macros when no workbook is open in Excel, so it's a good idea to verify that at least one workbook is open before trying to execute code that assumes there is an active workbook. One option is to check that the ActiveWorkbook
object is not Nothing
before running the code, as in the following example:
If ActiveWorkbook Is Nothing
Then
MsgBox "Please open a workbook and click in it before running this macro." _
& vbCr & vbCr & "This macro will now end.", _
vbOKOnly + vbExclamation, "No Workbook Is Open"
End
End If
It's also a good idea to check that the workbook your code assumes is the active workbook actually is the active workbook. This problem can easily occur when a procedure starts with the active workbook and then creates a new workbook to work in; the new workbook becomes the active workbook, and from this point on, the code may start running on the wrong workbook.
If there's any doubt about which workbook you're working with, declare a Workbook
object variable and use that object variable in your code rather than the ActiveWorkbook
object. For example, the following statements declare a Workbook
object variable and assign the ActiveWorkbook
object to it, so that subsequent code can work with the object variable:
Dim myWorkbook As Workbooks Set myWorkbook = ActiveWorkbook With myWorkbook 'actions here End With
Most workbooks you need to manipulate via VBA will contain one or more worksheets, so many procedures will need to work with worksheets—inserting them, deleting them, copying or moving them, or simply printing the appropriate range from them.
Each worksheet is represented by a Sheet
object. The Sheet
objects are contained within the Sheets
collection.
To insert a worksheet into a workbook, use the Add
method with the Sheets
collection. The syntax is as follows:
expression
.Add(Before, After, Count, Type)
The components of the syntax are as follows:
expression is a required expression that returns a Sheets
collection. Often, you'll want to use the Sheets
collection itself.
Before
is an optional Variant argument that specifies the sheet before which to add the new sheet. After
is an optional Variant argument that specifies the sheet after which to add the new sheet. Typically, you'll want to specify either Before
or After
, but not both. You can also omit both arguments to make Excel insert the new sheet before the active worksheet.
Count
is an optional Variant argument that specifies how many sheets to add. If you omit Count
, VBA uses the default value, 1
.
Type
is an optional Variant that specifies the type of sheet to insert. The default is xlWorksheet
, a standard worksheet. You can also insert a chart sheet (xlChart
), an Excel 4 macro sheet (xlExcel4MacroSheet
), or an Excel 4 international macro sheet (xlExcel4IntlMacroSheet
).
For example, the following statements declare a Worksheet
object variable named mySheet
, insert a worksheet before the first sheet in the first open workbook and assign the new sheet to mySheet
, and then set the Name
property of mySheet
to Summary
(the Name
property controls the text that appears on the worksheet's tab):
Dim mySheet As Worksheet Set mySheet = Workbooks(1).Sheets.Add(before:=Sheets(1)) mySheet.Name = "Summary"
The following statements insert two chart sheets after the last worksheet in the active workbook. The chart sheets receive default names, such as Chart1
and Chart2
:
ActiveWorkbook.Sheets.Add _ After:=Sheets(Sheets.Count), Count:=2, Type:=xlChart
To delete a worksheet, use the Delete
method of the appropriate Sheet
object. The Delete
method takes no arguments. For example, the following statement deletes the worksheet named Summary
from the workbook referenced by the myWorkbook
object variable:
myWorkbook.Sheets("Summary").Delete
If you delete a worksheet, you lose any data stored on that worksheet, so Excel asks the user to confirm the deletion by default (see Figure 22.2). If you need to avoid this user interaction—for example, in a procedure that adds a worksheet without the user's knowledge, uses it to manipulate data, and then deletes it—you can turn off alerts in Excel by setting the DisplayAlerts
property of the Application
object to False
before deleting the worksheet and then turning alerts back on again:
Application.DisplayAlerts = False myWorkbook.Sheets("Summary").Delete Application.DisplayAlerts = True
To copy a worksheet, use the Copy
method of the appropriate Sheet
object. To move a worksheet, use the Move
method. The syntax is as follows:
expression
.Copy(Before, After)expression
.Move(Before, After)
Here, expression is a required expression that returns a Worksheet
object. Before
is an optional Variant argument that specifies the sheet before which to place the copy or the moved sheet. After
is an optional Variant argument that specifies the sheet after which to place it:
Typically, you'll want to specify either Before
or After
, but not both.
You can specify another workbook by name to copy or move the worksheet to another workbook.
You can also omit both arguments to make Excel create a new workbook containing the copied or moved sheet. The new workbook becomes the active workbook, so you can use the ActiveWorkbook
object to start working with it or to assign it to an object variable.
For example, the following statement copies the worksheet named Costs – Materials
in the workbook named Building Schedule.xlsx
, placing the copy after the last of the current worksheets in the workbook:
Workbooks("Building Schedule.xlsx").Sheets("Costs - Materials").Copy, _ After:=Sheets(Sheets.Count)
The following statement moves the worksheet named Homes
from the workbook named Planning.xlsx
to the workbook named Building Schedule.xlsx
, inserting the worksheet before the first existing worksheet in the workbook:
Workbooks("Planning.xlsx").Sheets("Homes").Move
, _
Before:=Workbooks("Building Schedule.xlsx").Sheets(1)
To print a worksheet, use the PrintOut
method with the appropriate Worksheet
object.
The syntax for the PrintOut
method is as follows:
expression
.PrintOut(From, To, Copies, Preview, ActivePrinter,
PrintToFile, Collate, PrToFileName, IgnorePrintAreas)
The components of the syntax are as follows:
expression is a required expression that returns the appropriate Worksheet
object or other object to which the PrintOut
method applies.
From
is an optional Variant argument that specifies the number of the page at which to start printing. Omit From
to start printing at the beginning of the object. Note that From
and To
refer to the pages in the printout, not to the overall number of pages that the object would take up.
To
is an optional Variant argument that specifies the number of the page at which to stop printing. Omit To
to print to the end of the object.
Copies
is an optional Variant argument that specifies the number of copies to print. If you omit Copies
, Excel prints one copy.
Preview
is an optional Variant argument that you can set to True
to display the object in Print Preview before printing it. Set Preview
to False
, or simply omit this argument, to print the object without previewing it. Use the PrintPreview
method to display an object in Print Preview without printing it.
ActivePrinter
is an optional Variant argument that you can use to specify the printer on which to print.
PrintToFile
is an optional Variant argument that you can set to True
to make Excel print to a print file rather than a printer. When printing to a file, you can use the PrToFileName
property to specify the filename or omit it and have Excel prompt the user for the filename.
Collate
is an optional Variant argument that you can set to True
to have Excel print multiple copies for collation rather than printing all the copies of one page, all the copies of the next, and so on.
PrToFileName
is an optional Variant argument that you can use with PrintToFile:=True
to specify the filename of the print file.
IgnorePrintAreas
is an optional Variant argument. Set to False
, this argument prints the entire specified print area; when it's True
, the entire object is printed and any print area is ignored. A print area can be defined in Excel and is useful as a way of printing only a specified range of cells. Once specified, the print area is retained by Excel until you either clear it or specify a new print area. You define a print area by selecting the cells you want to print, then clicking the Ribbon's Page Layout tab. Click the Print Area option in the Page Setup area of the Ribbon.
The following statement prints two copies of each page of the first worksheet in the active workbook, collating the pages:
ActiveWorkbook.Sheets(1).Printout Copies:=2, Collate:=True
The following statement prints the first two pages of the worksheet named Summary
in the workbook named Planning.xlsx
to a file named Planning Summary.prn
in the network folder \server o_print
:
Workbooks("Planning.xlsx").Sheets("Summary").PrintOut From:=1, To:=2, _ PrintToFile:=True, _ PrToFileName:="\server o_printPlanning Summary.prn"
To protect a worksheet, use the Protect
method with the appropriate Worksheet
object. The syntax is as follows:
expression
.Protect(Password, DrawingObjects, Contents, Scenarios,
UserInterfaceOnly, AllowFormattingCells, AllowFormattingColumns,
AllowFormattingRows, AllowInsertingColumns, AllowInsertingRows,
AllowInsertingHyperlinks, AllowDeletingColumns, AllowDeletingRows,
AllowSorting, AllowFiltering, AllowUsingPivotTables)
The components of the syntax are as follows:
expression is a required expression that returns a Worksheet
object.
Password
is an optional Variant argument that specifies the password for unprotecting the worksheet. Password
is case sensitive. You'll almost always want to supply Password
to prevent unauthorized people from unprotecting the workbook.
DrawingObjects
is an optional Variant argument that you can set to True
to protect shapes in the worksheet. The default setting is False
.
Contents
is an optional Variant argument that protects the locked cells when set to True
, its default value. Set Contents
to False
to leave the locked cells unprotected.
Scenarios
is an optional Variant argument that protects scenarios when set to True
, its default value.
UserInterfaceOnly
is an optional Variant argument that you can set to True
to leave macros unprotected while protecting the user interface. The default value is False
.
AllowFormattingCells, AllowFormattingColumns
, and AllowFormattingRows
are optional Variant arguments that you can set to True
to allow the formatting of cells, columns, and rows, respectively. The default value for each argument is False
.
AllowInsertingColumns, AllowInsertingRows
, and AllowInsertingHyperlinks
are optional Variant arguments that you can set to True
to allow the user to insert columns, rows, and hyperlinks, respectively. The default value for each argument is False
.
AllowDeletingColumns
and AllowDeletingRows
are optional Variant arguments that you can set to True
to allow the user to delete columns or rows, respectively, where every cell in the column or row is unlocked. The default setting is False
.
AllowSorting
is an optional Variant argument that you can set to True
to allow the user to sort unlocked cells on the protected worksheet. The default setting is False
.
AllowFiltering
is an optional Variant argument that you can set to True
to allow the user to set filters or change filter criteria (but not enable or disable an autofilter) on a protected worksheet. The default setting is False
.
AllowUsingPivotTables
is an optional Variant argument that you can set to True
to allow the user to work with pivot tables on the protected worksheet. The default value is False
.
For example, the following statement protects the worksheet referenced by the object variable myWorksheet
using the password no1gets1n
:
myWorksheet.Protect Password:="no1gets1n"
The following statement protects the myWorksheet
worksheet with the same password but allows the formatting of cells and allows the sorting of unlocked cells:
myWorksheet.Protect Password:="no1gets1n", AllowFormattingCells:=True, _ AllowSorting:=True
The ActiveSheet
object returns the active worksheet in a specified workbook or (if you don't specify a workbook) the active worksheet in Excel.
If no sheet is active, ActiveSheet
returns Nothing
. Before executing code that depends on there being an active sheet, it's a good idea to check, as in this example:
If ActiveSheet Is Nothing Then End
In a procedure that manipulates a selection that the user has made, you'll typically work with either the active cell or the selection. The active cell is always a single cell, but the selection can encompass multiple cells or other objects.
The ActiveCell
property of the Application
object or the Window
object returns a Range
object that represents the active cell in the Excel application or in the specified window. If you use ActiveCell
without specifying the window, VBA returns the active cell in the active window.
For example, the following statement returns the address of the active cell in the active workbook:
ActiveCell.Address
The following statement returns the text in the active cell in the first window open on the workbook named Planning.xlsx
:
MsgBox Workbooks("Planning.xlsx").Windows(1).ActiveCell.Text
If no worksheet is active, or if a chart sheet is active, there is no active cell. If you try to access ActiveCell
, VBA returns an error. So before using code that assumes there is an active cell, check that ActiveCell
is not Nothing
:
If ActiveCell Is Nothing Then End
To return the value of the active cell, use the Value
property. For example, the following statement sets the value of the active cell to 25
:
ActiveCell.Value = 25
And the following statement retrieves the value of the active cell:
MsgBox ActiveCell.Value
The ActiveCell
object is often convenient to work with in your code, so sometimes you'll want to make a different cell the active cell in order to work with it via the ActiveCell
object. To make a cell the active cell, use the Activate
method with the appropriate Range
object. For example, the following statement makes cell L7 the active cell in the worksheet identified by the object variable myWorksheet
:
myWorksheet.Range("B5").Activate
Often, you'll need to move the active cell to a different range a specified number of rows or columns away (in other words, to an address relative to the active cell—as opposed to an absolute address, such as C12). To do so, use the Offset
property of the active cell object, specifying the number of rows with the RowOffset
argument and the number of columns with the ColumnOffset
argument. Use a positive offset to move the active cell right or down and a negative offset to move the active cell left or up. For example, the following statement moves the active cell up two rows (RowOffset:=-2
) and four columns to the right (ColumnOffset:=4
):
ActiveCell.Offset(RowOffset:=-2, ColumnOffset:=4).Activate
In procedures that the user triggers (macros), it's often a good idea to return the active cell to where it was when the user started the procedure. To do so, you can store the location of the active cell and then return it to the stored location after your procedure is finished with its tasks. Here's an example:
Set myActiveCell = ActiveCell Set myActiveWorksheet = ActiveSheet Set myActiveWorkbook = ActiveWorkbook 'take actions here myActiveWorkbook.Activate myActiveWorksheet.Activate myActiveCell.Activate
You can work with the range of cells around the active cell by using the CurrentRegion
property to return the CurrentRegion
object. The current region extends from the active cell to the first blank row above and below and to the first blank column to the left and right. In other words, if there are no blank rows or columns in the entire worksheet, then the region is all the cells in the worksheet.
For example, the following statements use the Font
property of the CurrentRegion
object to set the font of the current region to 12-point Times New Roman with no bold or italic:
With ActiveCell.CurrentRegion.Font .Name = "Times New Roman" .Size = 12 .Bold = False .Italic = False End With
In macros designed to be run by a user, you will often need to work with cells that the user has selected. For example, a user might select a range of cells and then run a macro to manipulate the contents of the range.
To work with the range the user has selected, use the RangeSelection
property of the appropriate Window
object. For example, you might assign the RangeSelection
property to a range so that you could work with it in a macro and then select it again at the end of the macro, leaving the user ready to work with their selection again. Here's an example:
Dim myMacroRange As RangeSet myMacroRange = ActiveWindow.RangeSelection
With myMacroRange 'take actions on the range here End WithmyMacroRange.Activate
Within a worksheet, you'll often need to manipulate ranges of cells. You can work with absolute ranges (ranges for which you specify the absolute addresses of the cells you want to affect, such as C12) or ranges relative to the active cell, where you merely describe an offset.
You can either specify a range by using the Range
property or create a named range by using the Names
collection. Excel also provides the UsedRange
property for working with the used range on a worksheet and the SpecialCells
method of the Range
object for working with cells that meet specific criteria.
To work with a range of cells, use the Range
property of the appropriate Worksheet
object to specify the cells. For example, the following statement sets the value of cell C12 on the active worksheet to 44
:
ActiveSheet.Range("C12").Value = "44"
To create a named range, use the Add
method with the Names
collection. The syntax is as follows:
expression
.Add(Name, RefersTo, Visible, MacroType, ShortcutKey,
Category, NameLocal, RefersToLocal, CategoryLocal, RefersToR1C1,
RefersToR1C1Local)
The components of the syntax are as follows:
expression is a required expression that returns a Names
object.
Name
is an optional Variant argument that specifies the name to assign to the named range. Name
is required if you don't specify the NameLocal
argument (later in this list). The name cannot be a cell reference, nor can it contain spaces.
RefersTo
is an optional Variant argument that specifies the range for the named range. You need to specify RefersTo
unless you use the RefersToLocal
argument, the RefersToR1C1
argument, or the RefersToR1C1Local
argument.
Visible
is an optional Variant argument that you can omit, set to True
to have Excel make the name visible in the user interface (in the Go To dialog box, the Paste Name dialog box, and other locations), or set to False
to make the name hidden.
MacroType
is an optional Variant argument that you can use to assign a macro type to the range: 1
for a user-defined Function
procedure, 2
for a Sub
procedure, and 3
or omitted for no macro.
ShortcutKey
is an optional Variant argument that specifies the shortcut key for a command macro assigned to the named range.
Category
is an optional Variant argument that specifies the category of the macro or function specified by MacroType
. You can specify one of the categories used by the Function Wizard, or specify another name to have Excel create a new category with that name.
NameLocal
is an optional Variant argument that specifies the name for the range in the local language. Use NameLocal
when you omit Name
.
RefersToLocal
is an optional Variant argument that specifies the range for the named range. Use RefersToLocal
when you omit RefersTo, RefersToR1C1
, and RefersToR1C1Local
.
CategoryLocal
is an optional Variant argument that you use to specify the category of the macro or function specified by MacroType
. Use CategoryLocal
when you omit Category
.
RefersToR1C1
is an optional Variant argument that specifies the range for the named range using R1C1 notation. Use RefersToR1C1
when you omit RefersTo, RefersToLocal
, and RefersToR1C1Local
.
RefersToR1C1Local
is an optional Variant argument that specifies the range for the named range using R1C1 notation in the local language. Use RefersToR1C1Local
when you omit RefersTo, RefersToLocal
, and RefersToR1C1
.
For example, the following statement defines a range named myRange
that refers to the range A1:G22 on the worksheet named Materials
in the workbook named Building Schedule.xlsx
:
Workbooks("Building Schedule.xlsx").Names.Add
Name:= "myRange", _
RefersTo:="=Materials!$A$1:$G$22"
To delete a named range, use the Delete
method with the appropriate Name
object. For example, the following statement deletes the range named myRange
in the workbook named Building Schedule.xlsx
:
Workbooks("Building Schedule.xlsx").Names("myRange").Delete
To work with a named range, specify the name with the Range
object. For example, the following statements set the row height of the rows in the named range myRange
to 20 points and applies 16-point Arial font to the cells:
With Range("myRange") .RowHeight = 20 .Font.Name = "Arial" .Font.Size = "16" End With
If you need to work with all the cells on a worksheet, but not with any unoccupied areas of the worksheet, use the UsedRange
property. For example, the following statement autofits all the columns in the used range in the active worksheet:
ActiveSheet.UsedRange.Columns.AutoFit
If you need to work with only some types of cells on a worksheet or in a range, use the SpecialCells
method of the Range
object to return the cells you need. The syntax is as follows:
expression
.SpecialCells(Type, Value)
These are the components of the syntax:
expression is a required expression that returns a Range
object.
Type
is a required argument that specifies which cells you want. Table 22.4 lists the constants you can use.
Value
is an optional Variant argument that you can use when Type
is xlCellTypeConstants
or xlCellTypeFormulas
to control which cells Excel includes. Table 22.5 shows the constants and what they return.
Table 22.4. Constants for the Type
argument for the SpecialCells
method
Constant | Returns This Kind of Cell |
---|---|
| All formats |
| Cells that use validation |
| Empty |
| Containing notes |
| Containing constants |
| Containing formulas |
| The last cell in the used range |
| Having the same format |
| Containing the same validation criteria |
| All visible |
Table 22.5. Constants for the Value
argument for the SpecialCells
method
Constant | Returns Cells Containing |
---|---|
| Errors |
| Logical values |
| Numbers |
| Text formulas |
For example, the following statement activates the last cell in the worksheet referenced by the object variable myWorksheet
:
myWorksheet.Cell.SpecialCells(Type:=xlCellTypeLastCell).Activate
The following statement identifies all the cells that contain formulas resulting in errors in the active worksheet:
ActiveSheet.Cells.SpecialCells(Type:=xlCellTypeFormulas, _ Value:=xlErrors).Activate
Unlike with Word, in which most of the options that you find in the Word Options dialog box (click the File tab, then click Options) are available through the Options
object, most of Excel's options are in the Application
object. Workbook-specific properties that appear in the Excel Options dialog box, however, are accessed through the appropriate Workbook
object.
The following sections show three examples of setting widely useful options in the Application
object.
In complex worksheets that perform many calculations, you may need to turn off automatic calculation so that a procedure can enter data quickly without the calculations taking place.
To do so, set the Calculation
property of the Application
object to xlCalculationManual
, enter the data, and then set the Calculation
property back to its previous value:
Dim varAutoCalculation As Variant varAutoCalculation = Application.Calculation Application.Calculation = xlCalculationManual 'enter the data here Application.Calculation = xlCalculationAutomatic
Sometimes you may find it useful to clear all the entries from recently displayed documents (shown when you click the File tab on the Ribbon, then click Recent). Perhaps, for example, your macro creates some temporary files that you want to delete.
You can do this by setting the Maximum
property of the RecentFiles
object to 0
. After doing so, you likely want to restore the user's previous setting, as the following example illustrates:
Dim myMax As Long With Application.RecentFiles myMax = .Maximum 'store the user's preference, currently in effect .Maximum = 0 .Maximum = myMax End With
After you execute this code and then click the File tab on the Ribbon and click Recent, no files will be displayed in the Recent Documents list.
Workbook-specific options include the following:
Security options (such as those shown in the following section and the sidebar "Setting Passwords and Read-Only Recommendations for a Workbook")
Whether to update remote references in the workbook (the Boolean UpdateRemoteReferences
property) and whether to save external link values (the Boolean SaveLinkValues
property)
Whether to use AutoRecover (the Boolean EnableAutoRecover
property)
Whether to accept labels in formulas (the Boolean AcceptLabelsInFormulas
property) and whether to use the 1904 date system (the Boolean Date1904
property)
You often need to create a new, blank workbook in a procedure (as if you'd interactively clicked the File tab on the Ribbon, then clicked the New button). And writing code that accomplishes this is not difficult. It requires only two words.
What code would you write to create a new, blank notebook?
Most workbooks you access via VBA will contain one or more worksheets, so most procedures will need to work with worksheets—inserting, deleting, copying, or moving them, or simply printing the appropriate range from them.
Name the object you use in VBA code to represent a worksheet.
In a procedure that manipulates a selection that the user has made, you'll typically work with either the active cell or the current selection.
What is the difference between the active cell and a selection?
Within a worksheet, you'll often need to manipulate ranges of cells. Excel includes a special kind of range—represented by the UsedRange
property.
What is unique about UsedRange
?
Word employs an Options
object to contain most of the options that you find in the Word Options dialog box (click the File tab on the Ribbon, then click Options). Excel uses a different object to contain its options.
From which object do you access most of Excel's options?
3.141.37.10