Manual de Referencia VBA Excel

Aplicaciones y Objetos

Application Workbooks Sheets WorkSheets Range Windows Cells Columns Rows

Collections Comments Workbooks WorkSheets Ranges Sheets

Objects Application Workbook Sheets WorkSheet Range Areas Comment

Application Objeto Represents the entire Microsoft Excel application.

Remarks The Application object contains:  Application-wide settings and options.  Methods that return top-level objects, such as ActiveCell, ActiveSheet, and so on.

Example Use the Application property to return the Application object. The following example applies the Windows property to the Application object. Application.Windows("book1.xls").Activate

The following example creates a Microsoft Excel workbook object in another application and then opens a workbook in Microsoft Excel. Set xl = CreateObject("Excel.Sheet") xl.Application.Workbooks.Open "newbook.xls"

Many of the properties and methods that return the most common userinterface objects, such as the active cell (ActiveCell property), can be used without the Application object qualifier. For example, instead of writing Application.ActiveCell.Font.Bold = True

You can write ActiveCell.Font.Bold = True

Events Name

Description

AfterCalculate

The AfterCalculate event occurs when all pending refresh activity (both synchronous and asynchronous) and all of the resultant calculation activities have been completed.

NewWorkbook

Occurs when a new workbook is created.

ProtectedViewWindowActivate

Occurs when a Protected View window is activated.

ProtectedViewWindowBeforeClose

Occurs immediately before a Protected View window or a workbook in aProtected View window closes.

ProtectedViewWindowBeforeEdit

Occurs immediately before editing is enabled on the workbook in the specifiedProtected View window.

ProtectedViewWindowDeactivate

Occurs when a Protected View window is deactivated.

ProtectedViewWindowOpen

Occurs when a workbook is opened in a Protected View window.

ProtectedViewWindowResize

Occurs when any View window is resized.

SheetActivate SheetBeforeDelete

SheetBeforeDoubleClick

Occurs when any worksheet is doubleclicked, before the default double-click action.

SheetBeforeRightClick

Occurs when any worksheet is rightclicked, before the default right-click action.

SheetCalculate

Occurs after any worksheet is recalculated or after any changed data is plotted on a chart.

SheetChange

Occurs when cells in any worksheet are changed by the user or by an external link.

SheetDeactivate

Occurs when any sheet is deactivated.

SheetFollowHyperlink

Occurs when you click any hyperlink in Microsoft Excel. For worksheet-level events, see the Help topic for the FollowHyperlink event.

SheetLensGalleryRenderComplete

Occurs after a callout gallery’s icons (dynamic & static) have finished rendering.

SheetPivotTableAfterValueChange

Occurs after a cell or range of cells inside a PivotTable are edited or recalculated (for cells that contain formulas).

SheetPivotTableBeforeAllocateChanges

Occurs before changes are applied to a PivotTable.

SheetPivotTableBeforeCommitChanges

Occurs before changes are committed against the OLAP data source for a PivotTable.

Protected

Occurs when any sheet is activated.

SheetPivotTableBeforeDiscardChanges

Occurs before changes to a PivotTable are discarded.

SheetPivotTableUpdate

Occurs after the sheet of the PivotTable report has been updated.

WorkbookBeforeClose

Occurs immediately before any open workbook closes.

SheetSelectionChange

Occurs when the selection changes on any worksheet (doesn't occur if the selection is on a chart sheet).

WorkbookBeforePrint

Occurs before any open workbook is printed.

WorkbookBeforeSave SheetTableUpdate

Occurs when a table on a worksheet is updated.

Occurs before any open workbook is saved.

WorkbookBeforeXmlExport WindowActivate

Occurs when any workbook window is activated.

Occurs before Microsoft Excel saves or exports XML data from the specified workbook.

WindowDeactivate

Occurs when any workbook window is deactivated.

WorkbookBeforeXmlImport

WindowResize

Occurs when any workbook window is resized.

Occurs before an existing XML data connection is refreshed, or new XML data is imported into any open Microsoft Excel workbook.

WorkbookDeactivate

Occurs when any open workbook is deactivated.

WorkbookModelChange

Occurs when the data model is updated.

WorkbookNewChart

Occurs when a new chart is created in any open workbook.

WorkbookNewSheet

Occurs when a new sheet is created in any open workbook.

WorkbookOpen

Occurs when a workbook is opened.

WorkbookPivotTableCloseConnection

Occurs after a PivotTable connection has been closed.

report

WorkbookPivotTableOpenConnection

Occurs after a PivotTable connection has been opened.

report

WorkbookActivate

Occurs when any workbook is activated.

WorkbookAddinInstall

Occurs when a workbook is installed as an add-in.

WorkbookAddinUninstall

Occurs when any add-in workbook is uninstalled.

WorkbookAfterSave

Occurs after the workbook is saved.

WorkbookAfterXmlExport

Occurs after Microsoft Excel saves or exports XML data from the specified workbook.

WorkbookAfterXmlImport

Occurs after an existing XML data connection is refreshed, or new XML

data is imported into Microsoft Excel workbook.

any

open

Methods Name

Description

ActivateMicrosoftApp

Activates a Microsoft application. If the application is already running, this method activates the running application. If the application isn't running, this method starts a new instance of the application.

AddCustomList

Adds a custom list for custom autofill and/or custom sort.

Calculate

Calculates all open workbooks, a specific worksheet in a workbook, or a specified range of cells on a worksheet, as shown in the following table.

CalculateFull

CalculateFullRebuild

CentimetersToPoints

Forces a full calculation of the data in all open workbooks. For all open workbooks, forces a full calculation of the data and rebuilds the dependencies. Converts a measurement from centimeters to points (one point equals 0.035 centimeters).

DisplayXMLSourcePane

Opens the XML Source task pane and displays the XML map specified by theXmlMap argument.

DoubleClick

Equivalent to double-clicking the active cell.

Evaluate

Converts a Microsoft Excel name to an object or a value.

FindFile

Displays the Open dialog box.

GetCustomListContents

Returns a custom list (an array of strings).

GetCustomListNum

Returns the custom list number for an array of strings. You can use this method to match both built-in lists and customdefined lists.

GetOpenFilename

Displays the standard Open dialog box and gets a file name from the user without actually opening any files.

GetSaveAsFilename

Displays the standard Save As dialog box and gets a file name from the user without actually saving any files.

Goto

Selects any range or Visual Basic procedure in any workbook, and activates that workbook if it’s not already active.

CheckAbort

Stops recalculation in a Microsoft Excel application.

ConvertFormula

Converts cell references in a formula between the A1 and R1C1 reference styles, between relative and absolute references, or both. Variant.

Help

Displays a Help topic.

InchesToPoints

Converts a measurement from inches to points.

Deletes a custom list.

InputBox

Displays a dialog box for user input. Returns the information entered in the dialog box.

DeleteCustomList

Intersect

MacroOptions

Returns a Range object that represents the rectangular intersection of two or more ranges.

SendKeys

Sends keystrokes to the active application.

Undo

Cancels the last user-interface action.

Corresponds to options in the Macro Options dialog box. You can also use this method to display a user defined function (UDF) in a built-in or new category within the Insert Function dialog box.

Union

Returns the union of two or more ranges.

Volatile

Marks a user-defined function as volatile. A volatile function must be recalculated whenever calculation occurs in any cells on the worksheet. A nonvolatile function is recalculated only when the input variables change. This method has no effect if it's not inside a user-defined function used to calculate a worksheet cell.

Wait

Pauses a running macro until a specified time. Returns True if the specified time has arrived.

OnKey

Runs a specified procedure when a particular key or key combination is pressed.

OnRepeat

Sets the Repeat item and the name of the procedure that will run if you choose theRepeat command after running the procedure that sets this property.

OnTime

Schedules a procedure to be run at a specified time in the future (either at a specific time of day or after a specific amount of time has passed).

OnUndo

Sets the text of the Undo command and the name of the procedure that’s run if you choose the Undo command after running the procedure that sets this property.

Quit

Quits Microsoft Excel.

RecordMacro

Records code if the macro recorder is on.

Repeat

Repeats the last user-interface action.

Run

Runs a macro or calls a function. This can be used to run a macro written in Visual Basic or the Microsoft Excel macro language, or to run a function in a DLL or XLL.

Properties Name

Description

ActiveCell

Returns a Range object that represents the active cell in the active window (the window on top) or in the specified window. If the window isn't displaying a worksheet, this property fails. Read-only.

ActiveChart

Returns a Chart object that represents the active chart (either an embedded chart or a chart sheet). An embedded chart is considered active when it's either selected or activated. When no chart is active, this property returnsNothing.

ActiveEncryptionSession

Returns a Long that represents the encryption session associated with the active document. Read-only.

ActivePrinter

Returns or sets the name of the active printer. Read/write String.

ActiveProtectedViewWindow

Returns a ProtectedViewWindow object that represents the activeProtected View window (the window on top). Read-only. Returns Nothingif there are no Protected View windows open. Read-only

ActiveSheet

Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active.

ActiveWindow

Returns a Window object that represents the active window (the window on top). Readonly. Returns Nothing if there are no windows open.

ActiveWorkbook

Returns a Workbook object that represents the workbook in the active window (the window on top). Read-only. Returns Nothing if there are no windows open or if either the Info window or the Clipboard window is the active window.

AddIns

Returns an AddIns collection that represents all the add-ins listed in theAdd-Ins dialog box (Add-Ins command on the Developer tab). Read-only.

AddIns2

Returns an AddIns2 collection that represents all the add-ins that are currently available or open in Microsoft Excel, regardless of whether they are installed. Read-only

AlertBeforeOverwriting

True if Microsoft Excel displays a message before overwriting nonblank cells during a drag-and-drop editing operation. Read/write Boolean.

AltStartupPath

Returns or sets the name of the alternate startup folder. Read/write String.

AlwaysUseClearType

Returns or sets a Boolean that represents whether to use ClearType to display fonts in the menu, ribbon, and dialog box text. Read/write Boolean.

Application

When used without an object qualifier, this property returns an Applicationobject that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Application object that represents the creator of the specified object (you can use this property with an OLE Automation object to return the application of that object). Read-only.

ArbitraryXMLSupportAvailable

Returns a Boolean value that indicates whether the XML features in Microsoft Excel are available. Read-only.

CellDragAndDrop

True if dragging and dropping cells is enabled. Read/write Boolean.

AskToUpdateLinks

True if Microsoft Excel asks the user to update links when opening files with links. False if links are automatically updated with no dialog box. Read/writeBoolean.

Cells

Returns a Range object that represents all the cells on the active worksheet. If the active document is not a worksheet, this property fails.

AutoCorrect

Returns an AutoCorrect object that represents the Microsoft Excel AutoCorrect attributes. Read-only.

ChartDataPointTrack

True will cause all charts in newly created documents to use the cell reference tracking behavior.Boolean

AutomationSecurity

Returns or sets an MsoAutomationSecurity constant that represents the security mode Microsoft Excel uses when programmatically opening files. Read/write.

Charts

Returns a Sheets collection that represents all the chart sheets in the active workbook.

Columns

Returns a Range object that represents all the columns on the active worksheet. If the active document isn't a worksheet, the Columns property fails.

True if entries in cells formatted as percentages aren’t automatically multiplied by 100 as soon as they are entered. Read/write Boolean.

COMAddIns

Returns the COMAddIns collection for Microsoft Excel, which represents the currently installed COM add-ins. Read-only.

AutoRecover

Returns an AutoRecover object, which backs up all file formats on a timed interval.

CommandBars

Returns a CommandBars object that represents the Microsoft Excel command bars. Read-only.

CalculateBeforeSave

True if workbooks are calculated before they're saved to disk (if theCalculation property is set to xlManual). This property is preserved even if you change the Calculation property. Read/write Boolean.

ConstrainNumeric

True if handwriting recognition is limited to numbers and punctuation only. Read/write Boolean.

ControlCharacters

True if Microsoft Excel displays control characters for right-to-left languages. Read/write Boolean.

CopyObjectsWithCells

True if objects are cut, copied, extracted, and sorted with cells. Read/writeBoolean.

AutoPercentEntry

Calculation

Returns or sets a XlCalculation value that represents the calculation mode.

Caption

Returns or sets a String value that represents the name that appears in the title bar of the main Microsoft Excel window.

Cursor

Returns or sets the appearance of the mouse pointer in Microsoft Excel. Read/write XlMousePointer.

CustomListCount

Returns the number of defined custom lists (including built-in lists). Read-only Long.

CutCopyMode

DataEntryMode

DecimalSeparator

DefaultFilePath

Returns or sets the status of Cut or Copy mode. Can be True, False, or anXLCutCopyMode constant, as shown in the following tables. Read/writeLong. Returns or sets Data Entry mode, as shown in the following table. When in Data Entry mode, you can enter data only in the unlocked cells in the currently selected range. Read/write Long. Sets or returns the character used for the decimal separator as a String. Read/write. Returns or sets the default path that Microsoft Excel uses when it opens files. Read/write String.

DisplayDocumentActionTaskPane

Set to True to display the Document Actions task pane; set to False to hide the Document Actions task pane. Read/write Boolean.

DisplayDocumentInformationPanel

Returns or sets a Boolean that represents whether the document properties panel is displayed. Read/write Boolean.

DisplayFormulaAutoComplete

Gets or sets whether to show a list of relevant functions and defined names when building cell formulas. Read/write Boolean.

DisplayFormulaBar

True if the formula bar is displayed. Read/write Boolean.

DisplayFullScreen

True if Microsoft Excel is in full-screen mode. Read/write Boolean.

DisplayFunctionToolTips

True if function ToolTips can be displayed. Read/write Boolean.

DisplayInsertOptions

True if the Insert Options button should be displayed. Read/write Boolean.

DefaultSaveFormat

Returns or sets the default format for saving files. For a list of valid constants, see the FileFormat property. Read/write Long.

DisplayNoteIndicator

True if cells containing notes display cell tips and contain note indicators (small dots in their upper-right corners). Read/write Boolean.

DisplayAlerts

True if Microsoft Excel displays certain alerts and messages while a macro is running. Read/write Boolean.

DisplayPasteOptions

True if the Paste Options button can be displayed. Read/write Boolean.

DisplayClipboardWindow

Returns True if the Microsoft Office Clipboard can be displayed. Read/writeBoolean.

DisplayRecentFiles

True if the list of recently used files is displayed in the UI. Read/writeBoolean.

DisplayCommentIndicator

Returns or sets the way cells display comments and indicators. Can be one of the XlCommentDisplayMode constants.

DisplayScrollBars

True if scroll bars are visible for all workbooks. Read/write Boolean.

DisplayStatusBar

True if the status bar is displayed. Read/write Boolean.

FileValidation

Returns or sets how Excel will validate files before opening them. Read/write

EditDirectlyInCell

True if Microsoft Excel allows editing in cells. Read/write Boolean.

FileValidationPivot

Returns or sets how Excel will validate the contents of the data caches for PivotTable reports. Read/write

EnableAutoComplete

True if the AutoComplete feature is enabled. Read/write Boolean.

FindFormat

Sets or returns the search criteria for the type of cell formats to find.

FixedDecimal

All data entered after this property is set to True will be formatted with the number of fixed decimal places set by the FixedDecimalPlaces property. Read/write Boolean.

FixedDecimalPlaces

Returns or sets the number of fixed decimal places used when theFixedDecimal property is set to True. Read/write Long.

FormulaBarHeight

Allows the user to specify the height of the formula bar in lines. Read/writeLong.

GenerateGetPivotData

Returns True when Microsoft Excel can get PivotTable report data. Read/write Boolean.

GenerateTableRefs

The GenerateTableRefs property determines whether the traditional notation method or the new structured referencing notation method is used for referencing tables in formulas. Read/write.

EnableCancelKey

Controls how Microsoft Excel handles CTRL+BREAK (or ESC or COMMAND+PERIOD) user interruptions to the running procedure. Read/write XlEnableCancelKey.

EnableCheckFileExtensions EnableEvents

True if events are enabled for the specified object. Read/write Boolean.

ErrorCheckingOptions

Returns an ErrorCheckingOptions object, which represents the error checking options for an application.

ExtendList

True if Microsoft Excel automatically extends formatting and formulas to new data that is added to a list. Read/write Boolean.

FileConverters

Returns information about installed file converters. Returns null if there are no converters installed. Read-only Variant.

FileDialog

Returns a FileDialog object representing an instance of the file dialog.

Height

FileExportConverters

Returns a FileExportConverters collection that represents all the file converters for saving files available to Microsoft Excel. Readonly.

Returns or sets a Double value that represents tThe height, in points, of the main application window.

Interactive

True if Microsoft Excel is in interactive mode; this property is usually True. If you set the this property to False, Microsoft Excel will block all input from the keyboard and mouse (except

input to dialog boxes that are displayed by your code). Read/write Boolean. International

MouseAvailable

True if a mouse is available. Readonly Boolean.

MoveAfterReturn

True if the active cell will be moved as soon as the ENTER (RETURN) key is pressed. Read/write Boolean.

MoveAfterReturnDirection

Returns or sets the direction in which the active cell is moved when the user presses ENTER. Read/write XlDirection.

MultiThreadedCalculation

Returns a MultiThreadedCalculation object that controls the multi-threaded recalculation settings. Read-only.

Name

Returns a String value that represents the name of the object.

Names

True if documents formatted for the standard paper size of another country/region (for example, A4) are automatically adjusted so that they're printed correctly on the standard paper size (for example, Letter) of your country/region. Read/write Boolean.

Returns a Names collection that represents all the names in the active workbook. Readonly Names object.

NewWorkbook

Returns a NewFile object.

OnWindow

Returns or sets the maximum amount of change between each iteration as Microsoft Excel resolves circular references. Read/write Double.

Returns or sets the name of the procedure that’s run whenever you activate a window. Read/write String.

Parent

Returns the parent object for the specified object. Read-only.

Path

Returns a String value that represents the complete path to the application, excluding the final separator and name of the application.

Returns information about the current country/region and international settings. Read-only Variant.

IsSandboxed

Returns True if the specified workbook is open in a Protected Viewwindow. Read-only

Iteration

True if Microsoft Excel will use iteration to resolve circular references. Read/write Boolean.

Left

Returns or sets a Double value that represents the distance, in points, from the left edge of the screen to the left edge of the main Microsoft Excel window.

LibraryPath

MapPaperSize

MaxChange

MergeInstances

Returns the path to the Library folder, but without the final separator. Read-only String.

MaxIterations

Returns or sets the maximum number of iterations that Microsoft Excel can use to resolve a circular reference. Read/write Long.

MeasurementUnit

Specifies the measurement unit used in the application. Read/writexlMeasurementUnit.

PathSeparator

Returns the path separator character ("\"). Read-only String.

PivotTableSelection

True if PivotTable reports use structured selection. Read/write Boolean.

PreviousSelections

Returns an array of the last four ranges or names selected. Each element in the array is a Range object. Read-only Variant.

PrintCommunication

Specifies whether communication with the printer is turned on. BooleanRead/write

PromptForSummaryInfo

True if Microsoft Excel asks for summary information when files are first saved. Read/write Boolean.

ProtectedViewWindows

QuickAnalysis

Range

Ready

RecentFiles

RecordRelative

Returns a ProtectedViewWindows collection that represents all theProtected View windows that are open in the application. Read-only Returns a QuickAnalysis object that represents the Quick Analysis options of the application. Returns a Range object that represents a cell or a range of cells. Returns True when the Microsoft Excel application is ready; False when the Excel application is not ready. Read-only Boolean. Returns a RecentFiles collection that represents the list of recently used files. True if macros are recorded using relative references; False if recording is absolute. Read-only Boolean.

ReferenceStyle

Returns or sets how Microsoft Excel displays cell references and row and column headings in either A1 or R1C1 reference style. Read/writeXlReferenceStyle.

RegisteredFunctions

Returns information about functions in either dynamic-link libraries (DLLs) or code resources that were registered with the REGISTER or REGISTER.ID macro functions. Readonly Variant.

ReplaceFormat

Sets the replacement criteria to use in replacing cell formats. The replacement criteria is then used in a subsequent call to the Replace method of the Range object.

RollZoom

True if the IntelliMouse zooms instead of scrolling. Read/write Boolean.

Rows

Returns a Range object that represents all the rows on the active worksheet. If the active document isn’t a worksheet, the Rows property fails. ReadonlyRange object.

ScreenUpdating

True if screen updating is turned on. Read/write Boolean.

Selection

Returns the selected object in the active window for an Application object.

Sheets

Returns a Sheets collection that represents all the sheets in the active workbook. Readonly Sheets object.

SheetsInNewWorkbook

Returns or sets the number of sheets that Microsoft Excel automatically inserts into new workbooks. Read/write Long.

ShowChartTipNames

True if charts show chart tip names. The default value is True. Read/writeBoolean.

SmartArtLayouts

Returns the set of SmartArt layouts that are currently loaded in the application. Read-only

ShowChartTipValues

True if charts show chart tip values. The default value is True. Read/writeBoolean.

SmartArtQuickStyles

Returns the set of SmartArt quick styles which are currently loaded in the application. Readonly

ShowDevTools

Returns or sets a Boolean that represents whether the Developer tab is displayed in the ribbon. Read/write Boolean.

StandardFont

Returns or sets the name of the standard font. Read/write String.

StandardFontSize

Returns or sets the standard font size, in points. Read/write Long.

StartupPath

Returns the complete path of the startup folder, excluding the final separator. Readonly String.

StatusBar

Returns or sets the text in the status bar. Read/write String.

TemplatesPath

Returns the local path where templates are stored. Read-only String.

ThisCell

Returns or sets a Boolean that represents whether Mini toolbars displays when a user selects text. False if Mini toolbars are displayed. Read/writeBoolean.

Returns the cell in which the user-defined function is being called from as aRange object.

ThisWorkbook

Returns True (default is False) when the New Workbook task pane appears for a Microsoft Excel application. Read/write Boolean.

Returns a Workbook object that represents the workbook where the current macro code is running. Read-only.

ThousandsSeparator

Sets or returns the character used for the thousands separator as a String. Read/write.

ShowToolTips

True if ToolTips are turned on. Read/write Boolean.

Top

SmartArtColors

Returns the set of color styles that are currently loaded in the application. Read-only

Returns or sets a Double value that represents the distance, in points, from the top edge of the screen to the top edge of the main Microsoft Excel window.

ShowMenuFloaties

ShowQuickAnalysis

ShowSelectionFloaties

ShowStartupDialog

Returns or sets a Boolean that represents whether to display Mini toolbarswhen the user right-clicks in the workbook window. False if Mini toolbarsare displayed. Read/write Boolean. Controls whether the Quick Analysis contextual user interface is displayed on selection. TRUE means the Quick Analysis button will show. Corresponds to the Show Quick Analysis options on selection checkbox located in theFile menu, Options, Excel Options, and then General tab. Read/Write.Boolean.

TransitionMenuKey

Returns or sets the Microsoft Excel menu or help key, which is usually "/". Read/write String.

Visible

Returns or sets a Boolean value that determines whether the object is visible. Read/write.

TransitionNavigKeys

True if transition navigation keys are active. Read/write Boolean.

WarnOnFunctionNameConflict

UsableHeight

Returns the maximum height of the space that a window can occupy in the application window area, in points. Read-only Double.

The WarnOnFunctionNameConflict property , when set to True, raises an alert if a developer tries to create a new function using an existing function name. Read/write Boolean.

Watches

Returns a Watches object representing a range which is tracked when the worksheet is recalculated.

Width

Returns or sets a Double value that represents the distance, in points, from the left edge of the application window to its right edge.

UsableWidth

Returns the maximum width of the space that a window can occupy in the application window area, in points. Read-only Double.

UsedObjects

Returns a UsedObjects object representing objects allocated in a workbook. Read-only

UserControl

True if the application is visible or if it was created or started by the user.False if you created or started the application programmatically by using theCreateObject or GetObject functions, and the application is hidden. Read/write Boolean.

Windows

Returns a Windows collection that represents all the windows in all the workbooks. Readonly Windows object.

WindowState

Returns or sets the state of the window. Read/write XlWindowState.

UserLibraryPath

Returns the path to the location on the user’s computer where the COM add-ins are installed. Read-only String.

Workbooks

Returns a Workbooks collection that represents all the open workbooks. Read-only.

WorksheetFunction UserName

Returns or sets the name of the current user. Read/write String.

Returns the WorksheetFunction object. Read-only.

Worksheets UseSystemSeparators

True (default) if the system separators of Microsoft Excel are enabled. Read/write Boolean.

Value

Returns a String value that represents the name of the application.

For an Application object, returns a Sheets collection that represents all the worksheets in the active workbook. For a Workbook object, returns aSheets collection that represents all the worksheets in the specified workbook. Readonly Sheets object.

Application. Workbooks Returns a Workbooks collection that represents all the open workbooks. Read-only.

Syntax expression .Workbooks expression A variable that represents an Application object.

Remarks Using this property without an object qualifier is equivalent to using Application.Workbooks . The collection returned by the Workbooks property doesn’t include open addins, which are a special kind of hidden workbook. You can, however, return a single open add-in if you know the file name. For example, Workbooks("Oscar.xla") will return the open add-in named "Oscar.xla" as a Workbook object.

A workbook displayed in a protected view window is not a member of the Workbooks collection. Instead, use theWorkbook property of the ProtectedViewWindow object to access a workbook that is displayed in a protected view window.

Example This example activates the workbook Book1.xls. Workbooks("BOOK1").Activate

This example opens the workbook Large.xls. Workbooks.Open filename:="LARGE.XLS"

T his example saves changes to and closes all workbooks except the one that’s running the example. For Each w In Workbooks If w.Name <> ThisWorkbook.Name Then w.Close savechanges:=True End If Next w

Use the Workbooks property to return the Workbooks collection. The following example closes all open workbooks. Workbooks.Close

Use the Add method to create a new, empty workbook and add it to the collection. The following example adds a new, empty workbook to Microsoft Excel.

Methods Name

Description

Add

Creates a new workbook. The new workbook becomes the active workbook.

CanCheckOut

True if Microsoft Excel can check out a specified workbook from a server. Read/write Boolean.

CheckOut

Returns a String representing a specified workbook from a server to a local computer for editing.

Close

Closes the object.

Open

Opens a workbook.

OpenDatabase

Returns a Workbook object representing a database.

OpenText

Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.

OpenXML

Opens an XML data file. Returns a Workbook object.

Workbooks.Add

Use the Open method to open a file. This creates a new workbook for the opened file. The following example opens the file Array.xls as a read-only workbook. Workbooks.Open FileName:="Array.xls", ReadOnly:=True

Properties Name

Description

Application

When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Applicationobject that represents the creator of the specified object (you can use this property with an OLE Automation object to return the application of that object). Read-only.

Count

Returns a Long value that represents the number of objects in the collection.

Creator

Returns a 32-bit integer that indicates the application in which this object was created. Readonly Long.

Item

Returns a single object from a collection.

Parent


Workbooks Object A collection of all the Workbook objects that are currently open in the Microsoft Excel application.

Remarks For more information about using a single Workbook object, see the Workbook object.

Example Use the Workbooks property to return the Workbooks collection. The following example closes all open workbooks. Workbooks.Close

Use the Add method to create a new, empty workbook and add it to the collection. The following example adds a new, empty workbook to Microsoft Excel. Workbooks.Add

Use the Open method to open a file. This creates a new workbook for the opened file. The following example opens the file Array.xls as a read-only workbook. Workbooks.Open FileName:="Array.xls", ReadOnly:=True

Methods

Properties

Name

Description

Name

Description

Add

Creates a new workbook. The new workbook becomes the active workbook.

Application

CanCheckOut

True if Microsoft Excel can check out a specified workbook from a server. Read/write Boolean.

CheckOut

Returns a String representing a specified workbook from a server to a local computer for editing.


Count


Close

Closes the object.

Open

Opens a workbook.

Creator

Returns a 32-bit integer that indicates the application in which this object was created. Read-only Long.

OpenDatabase

Returns a Workbook object representing a database.

Item


OpenText

Loads and parses a text file as a new workbook with a single sheet that contains the parsed text-file data.

Parent

Returns the parent object for the specified object. Readonly.

OpenXML

Opens an XML data file. Returns a Workbook object.

Workbook Object Represents a Microsoft Excel workbook.

Remarks The Workbook object is a member of the Workbooks collection. The Workbooks collection contains all the Workbook objects currently open in Microsoft Excel.

ThisWorkbook Property The ThisWorkbook property returns the workbook where the Visual Basic code is running. In most cases, this is the same as the active workbook. However, if the Visual Basic code is part of an add-in, the ThisWorkbookproperty won’t return the active workbook. In this case, the active workbook is the workbook calling the add-in, whereas the ThisWorkbook property returns the add-in workbook. If you’ll be creating an add-in from your Visual Basic code, you should use the ThisWorkbook property to qualify any statement that must be run on the workbook you compile into the add-in.

Example Use Workbooks(index), where index is the workbook name or index number, to return a single Workbook object. The following example activates workbook one. Workbooks(1).Activate

The index number denotes the order in which the workbooks were opened or created. Workbooks(1) is the first workbook created, and Workbooks(Workbooks.Count) is the last one created. Activating a workbook doesn’t change its index number. All workbooks are included in the index count, even if they’re hidden.

The Name property returns the workbook name. You cannot set the name by using this property; if you need to change the name, use the SaveAs method to save the workbook under a different name. The following example activates Sheet1 in the workbook named Cogs.xls (the workbook must already be open in Microsoft Excel).

Events Name

Description

Activate

Occurs when a workbook, worksheet, chart sheet, or embedded chart is activated.

The ActiveWorkbook property returns the workbook that’s currently active. The following example sets the name of the author for the active workbook.

AddinInstall

Occurs when the workbook is installed as an add-in

ActiveWorkbook.Author = "Jean Selva"

AddinUninstall

Occurs when the workbook is uninstalled as an add-in.

AfterSave

Occurs after the workbook is saved.

AfterXmlExport

Occurs after Microsoft Excel saves or exports XML data from the specified workbook.

AfterXmlImport

Occurs after an existing XML data connection is refreshed or after new XML data is imported into the specified Microsoft Excel workbook.

BeforeClose

Occurs before the workbook closes. If the workbook has been changed, this event occurs before the user is asked to save changes.

BeforePrint

Occurs before the workbook (or anything in it) is printed.

BeforeSave

Occurs before the workbook is saved.

BeforeXmlExport

Occurs before Microsoft Excel saves or exports XML data from the specified workbook.

Workbooks("Cogs.xls").Worksheets("Sheet1").Activate

BeforeXmlImport

Occurs before an existing XML data connection is refreshed or before new XML data is imported into a Microsoft Excel workbook.

Deactivate

Occurs when the chart, worksheet, or workbook is deactivated.

ModelChange

Occurs after the Excel data model is changed.

NewChart

Occurs when a new chart is created in the workbook.

SheetBeforeRightClick

Occurs when any worksheet is rightclicked, before the default right-click action.

SheetCalculate

Occurs after any worksheet is recalculated or after any changed data is plotted on a chart.

SheetChange

Occurs when cells in any worksheet are changed by the user or by an external link.

SheetDeactivate

Occurs when any sheet is deactivated.

SheetFollowHyperlink

Occurs when you click any hyperlink in Microsoft Excel. For worksheet-level events, see the Help topic for the FollowHyperlink event.

SheetLensGalleryRenderComplete

Occurs when a callout gallery’s icons (dynamic & static) have completed rendering for a worksheet.

NewSheet

Occurs when a new sheet is created in the workbook.

Open

Occurs when the workbook is opened.

PivotTableCloseConnection

Occurs after a PivotTable report closes the connection to its data source.

PivotTableOpenConnection

Occurs after a PivotTable report opens the connection to its data source.

SheetPivotTableAfterValueChange

RowsetComplete

The event is raised when the user either drills through the recordset or invokes the rowset action on an OLAP PivotTable.


SheetPivotTableBeforeAllocateChanges


SheetPivotTableBeforeCommitChanges


SheetPivotTableBeforeDiscardChanges


SheetPivotTableChangeSync

Occurs after changes to a PivotTable.

SheetActivate

Occurs when any sheet is activated.

SheetBeforeDelete SheetBeforeDoubleClick

Occurs when any worksheet is doubleclicked, before the default double-click action.

SheetPivotTableUpdate

Occurs after the sheet of the PivotTable report has been updated.

SheetSelectionChange

Occurs when the selection changes on any worksheet (doesn't occur if the selection is on a chart sheet).

SheetTableUpdate

Occurs after the sheet table has been updated.

Sync

This object or member has been deprecated, but it remains part of the object model for backward compatibility. You should not use it in new applications.

WindowActivate

Occurs when any workbook window is activated.

WindowDeactivate

Occurs when any workbook window is deactivated.

WindowResize

Occurs when any workbook window is resized.

Methods Name

Description

AcceptAllChanges

Accepts all changes in the specified shared workbook.

Activate

Activates the first window associated with the workbook.

AddToFavorites

Adds a shortcut to the workbook or hyperlink to the Favorites folder.

ApplyTheme

Applies the specified theme to the current workbook.

BreakLink

Converts formulas linked to other Microsoft Excel sources or OLE sources to values.

CanCheckIn

True if Microsoft Excel can check in a specified workbook to a server. Read/writeBoolean.

ChangeFileAccess

Changes the access permissions for the workbook. This may require an updated version to be loaded from the disk.

ChangeLink

Changes a link from one document to another.

CheckIn

Returns a workbook from a local computer to a server, and sets the local workbook to read-only so that it cannot be edited locally. Calling this method will also close the workbook.

CheckInWithVersion

Saves a workbook to a server from a local computer, and sets the local workbook to

read-only so that it cannot be edited locally.

GetWorkflowTemplates

Returns the collection of WorkflowTemplate objects for the specified workbook.

HighlightChangesOptions

Controls how changes are shown in a shared workbook.

LinkInfo

Returns the link date and update status.

LinkSources

Terminates a review of a file that has been sent for review using the SendForReviewmethod.

Returns an array of links in the workbook. The names in the array are the names of the linked documents, editions, or DDE or OLE servers. Returns Empty if there are no links.

LockServerFile

Locks the workbook on the server to prevent modification.

Assigns the current user exclusive access to the workbook that's open as a shared list.

MergeWorkbook

Merges changes from one workbook into an open workbook.

NewWindow

Creates a new window or a copy of the specified window.

OpenLinks

Opens the supporting documents for a link or links.

PivotCaches

Returns a PivotCaches collection that represents all the PivotTable caches in the specified workbook. Read-only.

Post

Posts the specified workbook to a public folder. This method works only with a Microsoft Exchange client connected to a Microsoft Exchange server.

PrintOut

Prints the object.

Close

Closes the object.

DeleteNumberFormat

Deletes a custom number format from the workbook.

EnableConnections

EndReview

ExclusiveAccess

ExportAsFixedFormat

FollowHyperlink

ForwardMailer

GetWorkflowTasks

The EnableConnections method allows developers to programmatically enable data connections within the workbook for the user.

The ExportAsFixedFormat method is used to publish a workbook to either the PDF or XPS format. Displays a cached document, if it’s already been downloaded. Otherwise, this method resolves the hyperlink, downloads the target document, and displays the document in the appropriate application. You have requested Help for a Visual Basic keyword used only on the Macintosh. For information about this keyword, consult the language reference Help included with Microsoft Office Macintosh Edition. Returns the collection of WorkflowTask objects for the specified workbook.

PrintPreview

Shows a preview of the object as it would look when printed.

review, notifying them that a reviewer has completed review of the workbook.

Protect

Protects a workbook so that it cannot be modified.

ResetColors

Resets the color palette to the default colors.

ProtectSharing

Saves the workbook and protects it for sharing.

RunAutoMacros

PurgeChangeHistoryNow

Removes entries from the change log for the specified workbook.

RefreshAll

Refreshes all external data ranges and PivotTable reports in the specified workbook.

Runs the Auto_Open, Auto_Close, Auto_Activate, or Auto_Deactivate macro attached to the workbook. This method is included for backward compatibility. For new Visual Basic code, you should use the Open, Close, Activate and Deactivate events instead of these macros.

Save

Saves changes to the specified workbook.

RejectAllChanges

Rejects all changes in the specified shared workbook.

SaveAs

Saves changes to the workbook in a different file.

RemoveDocumentInformation

Removes all information of the specified type from the workbook.

SaveAsXMLData

Exports the data that has been mapped to the specified XML schema map to an XML data file.

RemoveUser

Disconnects the specified user from the shared workbook.

SaveCopyAs

Reply

You have requested Help for a Visual Basic keyword used only on the Macintosh. For information about this keyword, consult the language reference Help included with Microsoft Office Macintosh Edition.

Saves a copy of the workbook to a file but doesn't modify the open workbook in memory.

SendFaxOverInternet

Sends a worksheet as a fax to the specfied recipients.

SendForReview

Sends a workbook in an e-mail message for review to the specified recipients.

SendMail

Sends the workbook by using the installed mail system.

SendMailer

You have requested Help for a Visual Basic keyword used only on the Macintosh. For information about this keyword, consult

ReplyAll

ReplyWithChanges

You have requested Help for a Visual Basic keyword used only on the Macintosh. For information about this keyword, consult the language reference Help included with Microsoft Office Macintosh Edition. Sends an e-mail message to the author of a workbook that has been sent out for

the language reference Help included with Microsoft Office Macintosh Edition. SetPasswordEncryptionOptions

Sets the options for encrypting workbooks using passwords.

ToggleFormsDesign

The ToggleFormsDesign method is used to toggle Excel into Design Mode when using forms controls.

Properties Name

Description

AccuracyVersion

Specifies whether certain worksheet functions use the latest accuracy algorithms to calculate their results. Read/write

ActiveChart

Returns a Chart object that represents the active chart (either an embedded chart or a chart sheet). An embedded chart is considered active when it's either selected or activated. When no chart is active, this property returns Nothing.

Unprotect

Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected.

UnprotectSharing

Turns off protection for sharing and saves the workbook.

ActiveSheet

UpdateFromFile

Updates a read-only workbook from the saved disk version of the workbook if the disk version is more recent than the copy of the workbook that is loaded in memory. If the disk copy hasn't changed since the workbook was loaded, the in-memory copy of the workbook isn't reloaded.

Returns an object that represents the active sheet (the sheet on top) in the active workbook or in the specified window or workbook. Returns Nothing if no sheet is active.

ActiveSlicer

Returns an object that represents the active slicer in the active workbook or in the specified workbook. Returns Nothing if no slicer is active. Read-only.

UpdateLink

Updates a Microsoft Excel, DDE, or OLE link (or links).

Application

XmlImport

Imports an XML data file into the current workbook.

XmlImportXml

Imports an XML data stream that has been previously loaded into memory. Excel uses the first qualifying map found or if the destination range is specified, Excel will automatically list the data.

When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Application object that represents the creator of the specified object (you can use this property with an OLE Automation object to return the application of that object). Read-only.

AutoUpdateFrequency

Returns or sets the number of minutes between automatic updates to the shared workbook. Read/write Long.

AutoUpdateSaveChanges

Colors

Returns or sets colors in the palette for the workbook. The palette has 56 entries, each represented by an RGB value. Read/write Variant.

CommandBars

Returns a CommandBars object that represents the Microsoft Excel command bars. Read-only.

Returns a DocumentProperties collection that represents all the built-in document properties for the specified workbook. Readonly.

ConflictResolution

Returns or sets the way conflicts are to be resolved whenever a shared workbook is updated. Read/write XlSaveConflictResolution.

Returns the information about the version of Excel that the workbook was last fully recalculated by. Read-only Long.

Connections

The Connections property establishes a connection between the workbook and an ODBC or an OLEDB data source and refreshes the data without prompting the user. Readonly.

ConnectionsDisabled

Disables the external connections or links in the workbook. Read-only

Container

True will cause all charts in the current document to track the actual data point to which it’s attached. False will revert back to tracking the index of the data point.Boolean Read/Write

Returns the object that represents the container application for the specified OLE object. Read-only Object.

ContentTypeProperties

Returns a MetaProperties collection that describes the metadata stored in the workbook. Read-only.

Charts

Returns a Sheets collection that represents all the chart sheets in the specified workbook.

CreateBackup

True if a backup file is created when this file is saved. Read-only Boolean.

CheckCompatibility

Controls whether or not the compatibility checker is run automatically when the workbook is saved. Read/write Boolean.

Creator


CodeName

Returns the code name for the object. Readonly String.

CustomDocumentProperties

Returns or sets a DocumentProperties collection that

BuiltinDocumentProperties

CalculationVersion

True if current changes to the shared workbook are posted to other users whenever the workbook is automatically updated. False if changes aren’t posted (this workbook is still synchronized with changes made by other users). The default value is True. Read/write Boolean.

CaseSensitive

True if the workbook distinguishes between upper and lower case when comparing content. Read-only Boolean

ChangeHistoryDuration

Returns or sets the number of days shown in the shared workbook's change history. Read/write Long.

ChartDataPointTrack

represents all the custom document properties for the specified workbook. CustomViews

Returns a CustomViews collection that represents all the custom views for the workbook.

CustomXMLParts

Returns a CustomXMLParts collection that represents the custom XML in the XML data store. Read-only.

Date1904

True if the workbook uses the 1904 date system. Read/write Boolean.

DefaultPivotTableStyle

Specifies the table style from the TableStyles collection that is used as the default style for PivotTables. Read/write.

DefaultSlicerStyle

Specifies the style from the TableStyles object that is used as the default style for slicers. Read/write.

DefaultTableStyle

Specifies the table style from the TableStyles collection that is used as the default TableStyle. Read/write Variant.

DefaultTimelineStyle

The name of the default slicer style of the workbook. Variant. Read/Write

DisplayDrawingObjects

Returns or sets how shapes are displayed. Read/write Long.

DisplayInkComments

A Boolean value that determines whether ink comments are displayed in the workbook. Read/write Boolean.

DocumentInspectors

Returns a DocumentInspectors collection that represents the Document Inspector

modules for the specified workbook. Readonly. DocumentLibraryVersions

Returns a DocumentLibraryVersions collection that represents the collection of versions of a shared workbook that has versioning enabled and that is stored in a document library on a server.

DoNotPromptForConvert

Returns or sets if the user should be prompted to convert the workbook if the workbook contains features that are not supported by versions of Excel earlier than Excel 2007. Read/write Boolean.

EnableAutoRecover

Saves changed files, of all formats, on a timed interval. Read/write Boolean.

EncryptionProvider

Returns a String specifying the name of the algorithm encryption provider that Microsoft Office Excel 2007 uses when encrypting documents. Read/write.

EnvelopeVisible

True if the e-mail composition header and the envelope toolbar are both visible. Read/write Boolean.

Excel4IntlMacroSheets

Returns a Sheets collection that represents all the Microsoft Excel 4.0 international macro sheets in the specified workbook. Read-only.

Excel4MacroSheets

Returns a Sheets collection that represents all the Microsoft Excel 4.0 macro sheets in the specified workbook. Read-only.

Excel8CompatibilityMode

The Excel8CompatibilityMode property provides developers with a way to check if the workbook is in compatibility mode. Readonly Boolean.

FileFormat

Returns the file format and/or type of the workbook. Read-only XlFileFormat.

Final

ForceFullCalculation

FullName

IsInplace

Returns or sets a Boolean that indicates whether a workbook is final. Read/writeBoolean.

True if the specified workbook is being edited in place. False if the workbook has been opened in Microsoft Excel for editing. Readonly Boolean.

KeepChangeHistory

True if change tracking is enabled for the shared workbook. Read/write Boolean.

Returns or sets the specified workbook to forced calculation mode. Read/write.

ListChangesOnNewSheet

True if changes to the shared workbook are shown on a separate worksheet. Read/write Boolean.

Mailer


Returns the name of the object, including its path on disk, as a string. Read-onlyString.

FullNameURLEncoded

Returns a String indicating the name of the object, including its path on disk, as a string. Read-only.

HasPassword

True if the workbook has a protection password. Read-only Boolean.

Model

HasVBProject

Returns a Boolean that represents whether a workbook has an attached Microsoft Visual Basic for Applications project. Readonly Boolean.

Returns the top level Model object which is the one Data Model for the workbook. Readonly

MultiUserEditing

True if the workbook is open as a shared list. Read-only Boolean.

HighlightChangesOnScreen

True if changes to the shared workbook are highlighted on-screen. Read/writeBoolean.

Name

Returns a String value that represents the name of the object.

IconSets

This property is used to filter data in a workbook based on a cell icon from theIconSet collection. Read-only.

Names

Returns a Names collection that represents all the names in the specified workbook (including all worksheet-specific names). Read-only Names object.

InactiveListBorderVisible

A Boolean value that specifies whether list borders are visible when a list is not active. Returns True if the border is visible. Read/write Boolean.

Parent


Password

Returns or sets the password that must be supplied to open the specified workbook. Read/write String.

IsAddin

True if the workbook is running as an add-in. Read/write Boolean.

PasswordEncryptionAlgorithm

Returns a String indicating the algorithm Microsoft Excel uses to encrypt passwords for the specified workbook. Read-only.

PrecisionAsDisplayed

True if calculations in this workbook will be done using only the precision of the numbers as they’re displayed. Read/write Boolean.

PasswordEncryptionFileProperties

True if Microsoft Excel encrypts file properties for the specified password-protected workbook. Read-only Boolean.

ProtectStructure

True if the order of the sheets in the workbook is protected. Read-only Boolean.

ProtectWindows PasswordEncryptionKeyLength

Returns a Long indicating the key length of the algorithm Microsoft Excel uses when encrypting passwords for the specified workbook. Read-only.

True if the windows of the workbook are protected. Read-only Boolean.

PublishObjects

Returns the PublishObjects collection. Readonly.

ReadOnly

Returns True if the object has been opened as read-only. Read-only Boolean.

ReadOnlyRecommended

True if the workbook was saved as read-only recommended. Read-only Boolean.

RemovePersonalInformation

True if personal information can be removed from the specified workbook. The default value is False. Read/write Boolean.

Research

Returns a Research object that represents the research service for a workbook. Read-only.

RevisionNumber

Returns the number of times the workbook has been saved while open as a shared list. If the workbook is open in exclusive mode, this property returns 0 (zero). Read-only Long.

Saved

True if no changes have been made to the specified workbook since it was last saved. Read/write Boolean.

SaveLinkValues

True if Microsoft Excel saves external link values with the workbook. Read/writeBoolean.

PasswordEncryptionProvider

Path

Permission

Returns a String specifying the name of the algorithm encryption provider that Microsoft Excel uses when encrypting passwords for the specified workbook. Read-only. Returns a String that represents the complete path to the workbook/file that this workbook object respresents. Returns a Permission object that represents the permission settings in the specified workbook.

PersonalViewListSettings

True if filter and sort settings for lists are included in the user's personal view of the shared workbook. Read/write Boolean.

PersonalViewPrintSettings

True if print settings are included in the user's personal view of the shared workbook. Readwrite Boolean.

PivotTables

Returns an object that represents a collection of all the PivotTable reports on a worksheet. Read-only.

ServerPolicy

Returns a ServerPolicy object that represents a policy specified for a workbook stored on a server running SharePoint Server 2007 or later. Read-only.

ServerViewableItems

Allows a developer to interact with the list of published objects in the workbook that are shown on the server. Read-only.

SharedWorkspace


Sheets

Returns a Sheets collection that represents all the sheets in the specified workbook. Readonly Sheets object.

ShowConflictHistory

True if the Conflict History worksheet is visible in the workbook that's open as a shared list. Read/write Boolean.

ShowPivotChartActiveFields

This property controls the visibility of the PivotChart Filter Pane. Read/writeBoolean.

ShowPivotTableFieldList

True (default) if the PivotTable field list can be shown. Read/write Boolean.

Signatures

Returns the digital signatures for a workbook. Read-only.

SlicerCaches

Returns the SlicerCaches object associated with the workbook. Read-only.

SmartDocument

Returns a SmartDocument object that represents the settings for a smart document solution. Read-only.

Styles

Returns a Styles collection that represents all the styles in the specified workbook. Readonly.

Sync


TableStyles

Returns a TableStyles collection object for the current workbook that refers to the styles used in the current workbook. Read-only.

TemplateRemoveExtData

True if external data references are removed when the workbook is saved as a template. Read/write Boolean.

Theme

Returns the theme applied to the current workbook. Read-only.

UpdateLinks

Returns or sets an XlUpdateLink constant indicating a workbook's setting for updating embedded OLE links. Read/write.

UpdateRemoteReferences

True if Microsoft Excel updates remote references in the workbook. Read/writeBoolean.

UserStatus

Returns a 1-based, two-dimensional array that provides information about each user who has the workbook open as a shared list. Readonly Variant.

UseWholeCellCriteria

True if the workbook uses search patterns that match the entire content of a cell. Readonly Boolean.

UseWildcards

True if the workbook enables wildcards for character string comparisons and searching. Read-only Boolean

VBASigned

True if the Visual Basic for Applications project for the specified workbook has been digitally signed. Read-only Boolean.

VBProject

Returns a VBProject object that represents the Visual Basic project in the specified workbook. Read-only.

WebOptions

Returns the WebOptions collection, which contains workbook-level attributes used by Microsoft Excel when you save a document as a Web page or open a Web page. Read-only.

Windows

Returns a Windows collection that represents all the windows in the specified workbook. Read-only Windows object.

Worksheets

Returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.

WritePassword

Returns or sets a String for the write password of a workbook. Read/write.

WriteReserved

True if the workbook is write-reserved. Readonly Boolean.

WriteReservedBy

Returns the name of the user who currently has write permission for the workbook. Readonly String.

XmlMaps

Returns an XmlMaps collection that represents the schema maps that have been added to the specified workbook. Read-only.

XmlNamespaces

Returns an XmlNamespaces collection that represents the XML namespaces contained in the specified workbook. Read-only.

Application. Sheets When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application..

Syntax expression .Application expression A variable that represents a Sheets object.

Example This example displays a message about the application that created myObject. Set myObject = ActiveWorkbook If myObject.Application.Value = "Microsoft Excel" Then MsgBox "This is an Excel Application object." Else MsgBox "This is not an Excel Application object." End If

Sheets Collection A collection of all the sheets in the specified or active workbook.

Remarks The Sheets collection can contain Chart or Worksheet objects. The Sheets collection is useful when you want to return sheets of any type. If you need to work with sheets of only one type, see the object topic for that sheet type.

Example Use the Sheets property to return the Sheets collection. The following example prints all sheets in the active workbook. Sheets.PrintOut

Use the Add method to create a new sheet and add it to the collection. The following example adds two chart sheets to the active workbook, placing them after sheet two in the workbook. Sheets.Add type:=xlChart, count:=2, after:=Sheets(2)

Use Sheets(index), where index is the sheet name or index number, to return a single Chart or Worksheet object. The following example activates the sheet named "sheet1." Sheets("sheet1").Activate

Use Sheets(array) to specify more than one sheet. The following example moves the sheets named "Sheet4" and "Sheet5" to the beginning of the workbook. Sheets(Array("Sheet4", "Sheet5")).Move before:=Sheets(1)

Sheets Object A collection of all the sheets in the specified or active workbook.

Methods Name

Description

Add

Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet.

Add2

This method is only implemented for the Charts collection object and will produce a run time error if used on the Sheets and Worksheets objects.

Copy

Copies the sheet to another location in the workbook.

Delete

Deletes the object.

FillAcrossSheets

Copies a range to the same area on all other worksheets in a collection.

Move

Moves the sheet to another location in the workbook.

PrintOut

Prints the object.

PrintPreview


Select

Selects the object.

Properties Name

Description

Application


Count


Creator


HPageBreaks

Returns an HPageBreaks collection that represents the horizontal page breaks on the sheet. Read-only.

Item


Parent


Visible

Returns or sets a Variant value that determines whether the object is visible.

VPageBreaks

Returns a VPageBreaks collection that represents the vertical page breaks on the sheet. Read-only.

Application. WorkSheets For an Application object, returns a Sheets collection that represents all the worksheets in the active workbook. For a Workbook object, returns a Sheets collection that represents all the worksheets in the specified workbook. Read-only Sheets object.

Syntax expression .Worksheets expression A variable that represents an Application object.

Remarks Using this property without an object qualifier returns all the worksheets in the active workbook. This property doesn’t return macro sheets; use the Excel4MacroSheets property or the Excel4IntlMacroSheets property to return those sheets.

Example This example displays the value in cell A1 on Sheet1 in the active workbook. MsgBox Worksheets("Sheet1").Range("A1").Value

This example displays the name of each worksheet in the active workbook. For Each ws In Worksheets MsgBox ws.Name Next ws

This example adds a new worksheet to the active workbook and then sets the name of the worksheet. Set newSheet = Worksheets.Add newSheet.Name = "current Budget"

WorkSheets Object A collection of all the Worksheet objects in the specified or active workbook. Each Worksheet object represents a worksheet.

Remarks The Worksheet object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets).

Example Use the Worksheets property to return the Worksheets collection.The following example moves all the worksheets to the end of the workbook. Worksheets.Move After:=Sheets(Sheets.Count)

Use the Add method to create a new worksheet and add it to the collection. The following example adds two new worksheets before sheet one of the active workbook. Worksheets.Add Count:=2, Before:=Sheets(1)

Use Worksheets(index), where index is the worksheet index number or name, to return a single Worksheet object. The following example hides worksheet one in the active workbook. Worksheets(1).Visible = False

Methods

Properties

Name

Description

Name

Description

Add

Creates a new worksheet, chart, or macro sheet. The new worksheet becomes the active sheet.

Application

Add2

This method is only implemented for the Charts collection object and will produce a run time error if used on the Sheets and Worksheets objects.


Copy


Count


Delete

Deletes the object.

Creator


FillAcrossSheets

Copies a range to the same area on all other worksheets in a collection.

HPageBreaks


Move


Item


PrintOut

Prints the object.

Parent


PrintPreview


Visible

Returns or sets a Variant value that determines whether the object is visible.

Select

Selects the object.

VPageBreaks


WorkSheet Object Represents a worksheet.

Remarks The Worksheet object is a member of the Worksheets collection. The Worksheets collection contains all the Worksheet objects in a workbook. The Worksheet object is also a member of the Sheets collection. The Sheets collection contains all the sheets in the workbook (both chart sheets and worksheets).

Example Use Worksheets(index), where index is the worksheet index number or name, to return a single Worksheet object. The following example hides worksheet one in the active workbook. Worksheets(1).Visible = False

The worksheet index number denotes the position of the worksheet on the workbook’s tab bar. Worksheets(1) is the first (leftmost) worksheet in the workbook, and Worksheets(Worksheets.Count) is the last one. All worksheets are included in the index count, even if they’re hidden. The worksheet name is shown on the tab for the worksheet. Use the Name property to set or return the worksheet name. The following example protects the scenarios on Sheet1. Dim strPassword As String strPassword = InputBox ("Enter the password for the worksheet") Worksheets("Sheet1").Protect password:=strPassword, scenarios:=True

When a worksheet is the active sheet, you can use the ActiveSheet property to refer to it. The following example uses the Activate method to activate Sheet1, sets the page orientation to landscape mode, and then prints the worksheet. Worksheets("Sheet1").Activate

Events Name

Description

Activate

Occurs when a workbook, worksheet, chart sheet, or embedded chart is activated.

ActiveSheet.PageSetup.Orientation = xlLandscape ActiveSheet.PrintOut BeforeDelete BeforeDoubleClick

Occurs when a worksheet is double-clicked, before the default double-click action.

BeforeRightClick

Occurs when a worksheet is right-clicked, before the default right-click action.

Calculate

Occurs after the worksheet is recalculated, for the Worksheet object.

Change

Occurs when cells on the worksheet are changed by the user or by an external link.

Deactivate

Occurs when the chart, worksheet, or workbook is deactivated.

FollowHyperlink

Occurs when you click any hyperlink on a worksheet. For application- and workbooklevel events, see the SheetFollowHyperlink event andSheetFollowHyperlink event.

LensGalleryRenderComplete

Occurs when a callout gallery’s icons (dynamic & static) have completed rendering.

PivotTableAfterValueChange


PivotTableBeforeAllocateChanges


PivotTableBeforeCommitChanges


PivotTableBeforeDiscardChanges


PivotTableChangeSync

Occurs after changes to a PivotTable.

PivotTableUpdate

Occurs after a PivotTable report is updated on a worksheet.

SelectionChange

Occurs when the selection changes on a worksheet.

TableUpdate

Occurs after a Query table connected to the Data Model is updated on a worksheet.

Methods Name

Description

Activate

Makes the current sheet the active sheet.

Calculate


ChartObjects

Returns an object that represents either a single embedded chart (a ChartObject object) or a collection of all the embedded charts (a ChartObjects object) on the sheet.

CheckSpelling

Checks the spelling of an object.

CircleInvalid

Circles invalid entries on the worksheet.

ClearArrows

Clears the tracer arrows from the worksheet. Tracer arrows are added by using the auditing feature.

ClearCircles

Clears circles from invalid entries on the worksheet.

Copy


Delete

Deletes the object.

Evaluate

Converts a Microsoft Excel name to an object or a value.

ExportAsFixedFormat

Exports to a file of the specified format.

Move


OLEObjects

Returns an object that represents either a single OLE object (an OLEObject ) or a collection of all OLE objects (an OLEObjects collection) on the chart or sheet. Readonly.

Paste

Pastes the contents of the Clipboard onto the sheet.

PasteSpecial

Pastes the contents of the Clipboard onto the sheet, using a specified format. Use this method to paste data from other applications or to paste data in a specific format.

PivotTables

PivotTableWizard

Returns an object that represents either a single PivotTable report (a PivotTable object) or a collection of all the PivotTable reports (a PivotTables object) on a worksheet. Read-only. Creates a new PivotTable report. This method doesn’t display the PivotTable Wizard. This method isn’t available for OLE DB data sources. Use the Add method to add a PivotTable cache, and then create a PivotTable report based on the cache.

PrintOut

Prints the object.

PrintPreview


Protect

Protects a worksheet so that it cannot be modified.

ResetAllPageBreaks

Resets all page breaks on the specified worksheet.

SaveAs

Saves changes to the chart or worksheet in a different file.

Scenarios

Returns an object that represents either a single scenario (a Scenario object) or a collection of scenarios (a Scenarios object) on the worksheet.

Select

Selects the object.

SetBackgroundPicture

Sets the background graphic for a worksheet.

ShowAllData

Makes all rows of the currently filtered list visible. If AutoFilter is in use, this method changes the arrows to "All."

ShowDataForm

Displays the data form associated with the worksheet.

Unprotect

Removes protection from a sheet or workbook. This method has no effect if the sheet or workbook isn't protected.

XmlDataQuery

Returns a Range object that represents the cells mapped to a particular XPath. Returns Nothingif the specified XPath has not been mapped to the worksheet, or if the mapped range is empty.

XmlMapQuery

Returns a Range object that represents the cells mapped to a particular XPath. Returns Nothingif the specified XPath has not been mapped to the worksheet.

Properties Name

Description

Application


active document isn't a worksheet, the Columns property fails. Comments

Returns a Comments collection that represents all the comments for the specified worksheet. Read-only.

ConsolidationFunction

Returns the function code used for the current consolidation. Can be one of the constants of XlConsolidationFunction. Read-only Long.

ConsolidationOptions

Returns a three-element array of consolidation options, as shown in the following table. If the element is True, that option is set. Read-only Variant.

AutoFilter

Returns an AutoFilter object if filtering is on. Read-only.

AutoFilterMode

True if the AutoFilter drop-down arrows are currently displayed on the sheet. This property is independent of the FilterMode property. Read/write Boolean.

ConsolidationSources

Returns an array of string values that name the source sheets for the worksheet's current consolidation. Returns Empty if there's no consolidation on the sheet. Read-only Variant.

Cells

Returns a Range object that represents all the cells on the worksheet (not just the cells that are currently in use).

Creator


CircularReference

Returns a Range object that represents the range containing the first circular reference on the sheet, or returns Nothing if there's no circular reference on the sheet. The circular reference must be removed before calculation can proceed.

CustomProperties

Returns a CustomProperties object representing the identifier information associated with a worksheet.

DisplayPageBreaks

True if page breaks (both automatic and manual) on the specified worksheet are displayed. Read/write Boolean.

DisplayRightToLeft

True if the specified worksheet is displayed from right to left instead of from left to right. False if the object is displayed from left to right. Read-only Boolean.

CodeName

Columns

Returns the code name for the object. Read-only String. Returns a Range object that represents all the columns on the active worksheet. If the

EnableAutoFilter

True if AutoFilter arrows are enabled when user-interface-only protection is turned on. Read/write Boolean.

ListObjects

Returns a collection of ListObject objects in the worksheet. Readonly ListObjectscollection.

EnableCalculation

True if Microsoft Excel automatically recalculates the worksheet when necessary.False if Excel doesn't recalculate the sheet. Read/write Boolean.

MailEnvelope

Rrepresents an e-mail header for a document.

Name

Returns or sets a String value that represents the object name.

Names

Returns a Names collection that represents all the worksheet-specific names (names defined with the "WorksheetName!" prefix). Readonly Names object.

Next

Returns a Worksheet object that represents the next sheet.

Outline

Returns an Outline object that represents the outline for the specified worksheet. Read-only.

PageSetup

Returns a PageSetup object that contains all the page setup settings for the specified object. Read-only.

Parent


Previous

Returns a Worksheet object that represents the next sheet.

Returns a Hyperlinks collection that represents the hyperlinks for the worksheet.

PrintedCommentPages

Returns the number of comment pages that will be printed for the current worksheet. Read-only

Returns a Long value that represents the index number of the object within the collection of similar objects.

ProtectContents

True if the contents of the sheet are protected. This protects the individual

EnableFormatConditionsCalculation

Returms or sets if conditional formats will will occur automatically as needed. Read/write Boolean.

EnableOutlining

True if outlining symbols are enabled when user-interface-only protection is turned on. Read/write Boolean.

EnablePivotTable

True if PivotTable controls and actions are enabled when user-interface-only protection is turned on. Read/write Boolean.

EnableSelection

Returns or sets what can be selected on the sheet. Read/write XlEnableSelection.

FilterMode

True if the worksheet is in the filter mode. Read-only Boolean.

HPageBreaks


Hyperlinks

Index

cells. To turn on content protection, use the Protect method with the Contentsargument set to True. Readonly Boolean. ProtectDrawingObjects

True if shapes are protected. To turn on shape protection, use the Protectmethod with the DrawingObjects argument set to True. Read-only Boolean.

Shapes

Returns a Shapes collection that represents all the shapes on the worksheet. Read-only.

Sort

Returns a Sort object. Read-only.

StandardHeight

Returns the standard (default) height of all the rows in the worksheet, in points. Readonly Double.

Protection

Returns a Protection object that represents the protection options of the worksheet.

StandardWidth

Returns or sets the standard (default) width of all the columns in the worksheet. Read/write Double.

ProtectionMode

True if user-interface-only protection is turned on. To turn on user interface protection, use the Protect method with the UserInterfaceOnly argument set toTrue. Read-only Boolean.

Tab

Returns a Tab object for a worksheet.

TransitionExpEval

True if Microsoft Excel uses Lotus 1-2-3 expression evaluation rules for the worksheet. Read/write Boolean.

TransitionFormEntry

True if Microsoft Excel uses Lotus 1-2-3 formula entry rules for the worksheet. Read/write Boolean.

Type

Returns an XlSheetType value that represents the worksheet type.

UsedRange

Returns a Range object that represents the used range on the specified worksheet. Read-only.

Visible

Returns or sets an XlSheetVisibility value that determines whether the object is visible.

VPageBreaks


ProtectScenarios

True if the worksheet scenarios are protected. Read-only Boolean.

QueryTables

Returns the QueryTables collection that represents all the query tables on the specified worksheet. Read-only.

Range

Returns a Range object that represents a cell or a range of cells.

Rows

Returns a Range object that represents all the rows on the specified worksheet. Readonly Range object.

ScrollArea

Returns or sets the range where scrolling is allowed, as an A1-style range reference. Cells outside the scroll area cannot be selected. Read/write String.

Propiedad Worksheet.Cells (Excel) Devuelve un objeto Range que representa todas las celdas de la hoja de cálculo (no solo las celdas que están actualmente en uso).

Sintaxis

expresión .Cells

expresión Variable que representa un objeto WorkSheet.

Propiedad Worksheet.Columns Devuelve un objeto Range que representa todas las columnas de la hoja de cálculo activa. Si el documento activo no es una hoja de cálculo, la propiedad Columns devuelve un error.

Sintaxis


Observaciones Dado que la propiedad Item es la propiedad predeterminada del objeto Range , puede especificar el índice de fila y columna inmediatamente después de la palabra clave de las celdas . Para obtener más información, vea la propiedad Item y los ejemplos de este tema. Si se usa esta propiedad sin un calificador de objeto, se devuelve un objeto Range que representa todas las celdas de la hoja activa.

Ejemplo En este ejemplo se establece el tamaño de fuente para la celda C5 de Sheet1 en 14 puntos.

expresión .Columns

Observaciones El uso de esta propiedad sin un calificador de objeto equivale a usar ActiveSheet.Columns. Si se aplica a un objeto Range que es una selección de varias áreas, la propiedad únicamente devuelve las columnas de la primera área del rango. Por ejemplo, si el objeto Range tiene dos áreas, A1:B2 y C3:D4, Selection.Columns.Count devuelve 2, no 4. Si desea utilizar esta propiedad en un rango que puede contener una selección de varias áreas, compruebe Areas.Count para determinar si el rango contiene más de un área. En ese caso, ejecute un bucle sobre cada área del rango.

Ejemplo

Worksheets("Sheet1").Cells(5, 3).Font.Size = 14

En este ejemplo se da formato de negrita a la fuente de la columna uno (columna A) de Sheet1.

En este ejemplo se borra la fórmula de la celda uno de Sheet1.

Worksheets("Sheet1").Columns(1).Font.Bold = True

Worksheets("Sheet1").Cells(1).ClearContents

En este ejemplo se establece en Arial de 8 puntos la fuente y el tamaño de fuente de todas las celdas de Sheet1. With Worksheets("Sheet1").Cells.Font

Propiedad Worksheet.Comments (Excel) Devuelve una colección de Comments que representa todos los comentarios de la hoja de cálculo especificada. Solo lectura.

.Name = "Arial" .Size = 8 End With

Sintaxis

expresión .Comments expresión Variable que representa un objeto WorkSheet.

En el ejemplo de código siguiente se establece el nombre de la hoja de cálculo activa de igual a la fecha actual.

Ejemplo Este ejemplo elimina todos los comentarios agregados por Jean Selva a la hoja activa. For Each c in ActiveSheet.Comments If c.Author = "Jean Selva" Then c.Delete Next

VBA ' This macro sets today's date as the name for the current sheet Sub NameWorksheetByDate() Range("D5").Select Selection.Formula = "=text(now(),""mmm dd yyyy"")" Selection.Copy

Propiedad Worksheet.EnableAutoFilter True si están habilitadas las flechas de filtro automático cuando se activa la protección de solo interfaz de usuario. Boolean de lectura y escritura.

Sintaxis

expresión .EnableAutoFilter

Ejemplo Este ejemplo activa las flechas de Autofiltro en una hoja de cálculo protegida. ActiveSheet.EnableAutoFilter = True ActiveSheet.Protect contents:=True, userInterfaceOnly:=True

Propiedad Worksheet.Name (Excel) Devuelve o establece un valor de tipo String que representa el nombre del objeto. expresión .Name expresión Variable que representa un objeto WorkSheet.

Ejemplos

Application.CutCopyMode = False Selection.Columns.AutoFit ActiveSheet.Name = Range("D5").Value Range("D5").Value = ""


Sintaxis

Selection.PasteSpecial Paste:=xlValues

End Sub

Propiedad Worksheet.Next (Excel) Devuelve un objeto Worksheet que representa la hoja siguiente.

Sintaxis

expresión .Next expresión Variable que representa un objeto WorkSheet.

Observaciones Si el objeto es un rango, esta propiedad emula a la tecla TAB, aunque la propiedad devuelve la celda siguiente sin seleccionarla. En una hoja protegida, esta propiedad devuelve la siguiente celda desbloqueada. En una hoja desprotegida, siempre devuelve la celda situada inmediatamente a la derecha de la celda especificada.

Ejemplo En este ejemplo se selecciona la siguiente celda desbloqueada de Sheet1. Si Sheet1 no está protegida, ésta será la celda situada inmediatamente a la derecha de la celda activa. Worksheets("Sheet1").Activate ActiveCell.Next.Select

En una hoja protegida, esta propiedad devuelve la celda anterior no bloqueada. En una hoja desprotegida, la propiedad siempre devuelve la celda situada inmediatamente a la izquierda de la celda especificada.

Ejemplo En este ejemplo se selecciona la celda anterior no bloqueada de Sheet1. Si Sheet1 no está protegida, será la celda situada inmediatamente a la izquierda de la celda activa. Worksheets("Sheet1").Activate ActiveCell.Previous.Select

Propiedad Worksheet.Protection Devuelve un objeto Protection que representa las opciones de protección de la hoja de cálculo.

Sintaxis

expresión .Protection expresión Variable que representa un objeto WorkSheet.

Ejemplo Este ejemplo protege la hoja de cálculo activa; a continuación, determina si se pueden insertar columnas en la hoja de cálculo y comunica al usuario este estado. Sub CheckProtection()

Propiedad Worksheet.Previous (Excel) Devuelve un objeto Worksheet que representa la hoja anterior.

Sintaxis

ActiveSheet.Protect ' Check the ability to insert columns on a protected sheet. 'Notify the user of this status. If ActiveSheet.Protection.AllowInsertingColumns = True Then

expresión .Previous

MsgBox "The insertion of columns is allowed on this _


protected worksheet." Else

Observaciones Si el objeto es un rango, esta propiedad emula la acción de presionar las teclas MAYÚS+TAB; no obstante, a diferencia de la combinación de teclas, la propiedad devuelve la celda anterior sin seleccionarla.

MsgBox "The insertion of columns is not allowed on _ this protected worksheet." End If End Sub

Propiedad Worksheet.Range (Excel) Devuelve un objeto Range que representa una celda o un rango de celdas.

Sintaxis

expresión .Range(Cell1, Cell2) expresión Variable que representa un objeto WorkSheet.

Parámetros

Cell2

Ejemplo En este ejemplo se establece el valor de la celda A1 de Sheet1 como 3.14159. Worksheets("Sheet1").Range("A1").Value = 3.14159

Nombre Cell1

da, Selection.Range("B1") devuelve la celda D3 porque es el valor relativo al objeto Range devuelto por la propiedad Selection. Por otro lado, el código ActiveSheet.Range("B1") siempre devuelve la celda B1.

Descripción Obligatorio

Opcional

Nombre del rango. Debe ser una referencia de estilo A1 en el lenguaje de la macro. Puede incluir un operador de rango (dos puntos), de intersección (espacio) o de unión (coma). Admite signos de dólar, pero no se tendrán en cuenta. Se puede usar un nombre local definido en cualquier parte del rango. Si se usa un nombre, se supone que está en el lenguaje de la macro. Celda de las esquinas superior izquierda e inferior derecha del rango. Puede ser un objeto Range que contiene una sola celda, una columna completa o una fila completa; o bien una cadena que hace referencia a una sola celda en el lenguaje de la macro.

En este ejemplo se crea una fórmula en la celda A1 de Sheet1. Worksheets("Sheet1").Range("A1").Formula = "=10*RAND()"

En este ejemplo se ejecuta un bucle sobre las celdas A1:D10 de Sheet1.Si una de las celdas tiene un valor menor que 0.001, el código sustituye el valor por 0 (cero). For Each c in Worksheets("Sheet1").Range("A1:D10") If c.Value < .001 Then c.Value = 0 End If Next c

En este ejemplo se ejecuta un bucle en el rango denominado "TestRange" y se muestra el número de celdas vacías del rango. numBlanks = 0 For Each c In Range("TestRange") If c.Value = "" Then numBlanks = numBlanks + 1

Observaciones Si no hace referencia a ningún objeto, esta propiedad es una abreviatura de ActiveSheet.Range (devuelve un rango de la hoja activa; si la hoja activa no es una hoja de cálculo, la propiedad no funciona). Cuando se aplica a un objeto Range, los valores de la propiedad son relativos al objeto Range. Por ejemplo, si la celda C3 está selecciona-

End If Next c MsgBox "There are " & numBlanks & " empty cells in this range"

En este ejemplo se establece en cursiva el estilo de fuente de las celdas A1:C5 de Sheet1.El ejemplo usa la sintaxis 2 de la propiedad Range. Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3))._ Font.Italic = True

Propiedad Worksheet.Rows (Excel) Devuelve un objeto Range que representa todas las filas de la hoja de cálculo especificada. Objeto Range de solo lectura.

En este ejemplo se muestra el número de filas de la selección de Sheet1.Si se ha seleccionado más de un área, el ejemplo ejecuta un bucle en cada área. Worksheets("Sheet1").Activate areaCount = Selection.Areas.Count

Sintaxis

expresión .Rows


Observaciones El uso de esta propiedad sin un calificador de objeto equivale a usar ActiveSheet.Rows.

If areaCount <= 1 Then MsgBox "The selection contains " & _ Selection.Rows.Count & " rows." Else i = 1 For Each a In Selection.Areas MsgBox "Area " & i & " of the selection contains " & _ a.Rows.Count & " rows."

Si se aplica a un objeto Range que es una selección de varias áreas, la propiedad únicamente devuelve las filas de la primera área del rango. Por ejemplo, si el objeto Range tiene dos áreas, A1:B2 y C3:D4,Selection.Rows.Count devuelve 2, no 4. Si desea utilizar esta propiedad en un rango que puede contener una selección múltiple, compruebe Areas.Count para determinar si el rango es una selección múltiple. En ese caso, ejecute un bucle sobre cada área del rango, tal como se muestra en el tercer ejemplo.

Ejemplo En este ejemplo se elimina la fila tres de Sheet1. Worksheets("Sheet1").Rows(3).Delete

En este ejemplo se eliminan las filas de la región actual de la hoja de cálculo uno en las que el valor de la celda uno de la fila es el mismo que el valor de la celda uno de la fila anterior. For Each rw In Worksheets(1).Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = last Then rw.Delete last = this Next

i = i + 1 Next a End If

Application. Range Returns a Range object that represents a cell or a range of cells.

Syntax expression .Range(Cell1, Cell2) expression .[Cell1, Cell2] expression A variable that represents an Application object.

Parameters Name

Required/Optional

Cell1

Required

Cell2

Optional

Remarks When used without an object qualifier, this property is a shortcut for ActiveSheet.Range (it returns a range from the active sheet; if the active sheet isn’t a worksheet, the property fails). When applied to a Range object, the property is relative to the Range object. For example, if the selection is cell C3, then Selection.Range("B1") returns cell D3 because it’s relative to the Range object returned by the Selection property. On the other hand, the code ActiveSheet.Range("B1") always returns cell B1.

Example This example sets the value of cell A1 on Sheet1 to 3.14159. Worksheets("Sheet1").Range("A1").Value = 3.14159

This example creates a formula in cell A1 on Sheet1. Worksheets("Sheet1").Range("A1").Formula = "=10*RAND()"

This example loops on cells A1:D10 on Sheet1. If one of the cells has a value less than 0.001, the code replaces that value with 0 (zero). For Each c in Worksheets("Sheet1").Range("A1:D10") If c.Value < .001 Then c.Value = 0 End If Next c

This example loops on the range named "TestRange" and displays the number of empty cells in the range. numBlanks = 0 For Each c In Range("TestRange") If c.Value = "" Then numBlanks = numBlanks + 1 End If Next c MsgBox "There are " & numBlanks & " empty cells in this range"

This example sets the font style in cells A1:C5 on Sheet1 to italic. The example uses Syntax 2 of the Range property. Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3)). _ Font.Italic = True

Ranges Object A collection of Range objects.

Properties Name

Description

Application

When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Applicationobject that represents the creator of the specified object. Read-only.

Count

Returns the number of objects in the collection. Readonly Long.

Creator


Item

Returns a Range object that represents a range of items in a workbook. Read-only.

Parent


Range Object Represents a cell, a row, a column, a selection of cells containing one or more contiguous blocks of cells, or a 3-D range.

Remarks The following properties and methods for returning a Range object are described in the examples section:  Range property  Cells property  Range and Cells  Offset property  Union method

Example Use Range(arg), where arg names the range, to return a Range object that represents a single cell or a range of cells. The following example places the value of cell A1 in cell A5. Worksheets("Sheet1").Range("A5").Value = _ Worksheets("Sheet1").Range("A1").Value

The following example fills the range A1:H8 with random numbers by setting the formula for each cell in the range. When it’s used without an object qualifier (an object to the left of the period), the Range property returns a range on the active sheet. If the active sheet isn’t a worksheet, the method fails. Use the Activate method to activate a worksheet before you use the Range property without an explicit object qualifier. Worksheets("Sheet1").Activate Range("A1:H8").Formula = "=Rand()" sheet

'Range is on the active

The following example clears the contents of the range named Criteria.

If you use a text argument for the range address, you must specify the address in A1-style notation (you cannot use R1C1-style notation).

Use expression.Cells(row, column), where expression is an expression that returns a Range object, and row and column are relative to the upper-left corner of the range, to return part of a range. The following example sets the formula for cell C5.

Worksheets(1).Range("Criteria").ClearContents

Worksheets(1).Range("C5:C10").Cells(1, 1).Formula = "=Rand()"

Use Cells(row, column) where row is the row index and column is the column index, to return a single cell. The following example sets the value of cell A1 to 24.

Use Range(cell1, cell2), where cell1 and cell2 are Range objects that specify the start and end cells, to return a Range object. The following example sets the border line style for cells A1:J10.

Worksheets(1).Cells(1, 1).Value = 24

The following example sets the formula for cell A2. ActiveSheet.Cells(2, 1).Formula = "=Sum(B1:B5)"

Although you can also use Range("A1") to return cell A1, there may be times when the Cells property is more convenient because you can use a variable for the row or column. The following example creates column and row headings on Sheet1. Be aware that after the worksheet has been activated, the Cells property can be used without an explicit sheet declaration (it returns a cell on the active sheet).

Although you could use Visual Basic string functions to alter A1-style references, it is easier (and better programming practice) to use the Cells(1, 1) notation. Sub SetUpTable() Worksheets("Sheet1").Activate For TheYear = 1 To 5 Cells(1, TheYear + 1).Value = 1990 + TheYear Next TheYear For TheQuarter = 1 To 4 Cells(TheQuarter + 1, 1).Value = "Q" & TheQuarter Next TheQuarter End Sub

Be aware that the period in front of each occurrence of the Cells property. The period is required if the result of the preceding With statement is to be applied to the Cells property—in this case, to indicate that the cells are on worksheet one (without the period, the Cells property would return cells on the active sheet). With Worksheets(1) .Range(.Cells(1, 1), _ .Cells(10, 10)).Borders.LineStyle = xlThick End With

Use Offset(row, column), where row and column are the row and column offsets, to return a range at a specified offset to another range. The following example selects the cell three rows down from and one column to the right of the cell in the upper-left corner of the current selection. You cannot select a cell that is not on the active sheet, so you must first activate the worksheet. VA Worksheets("Sheet1").Activate 'Can't select unless the sheet is active Selection.Offset(3, 1).Range("A1").Select

Use Union(range1, range2, ...) to return multiple-area ranges—that is, ranges composed of two or more contiguous blocks of cells. The following example creates an object defined as the union of ranges A1:B2 and C3:D4, and then selects the defined range. Dim r1 As Range, r2 As Range, myMultiAreaRange As Range Worksheets("sheet1").Activate Set r1 = Range("A1:B2") Set r2 = Range("C3:D4") Set myMultiAreaRange = Union(r1, r2) myMultiAreaRange.Select

If you work with selections that contain more than one area, the Areas property is useful. It divides a multiple-area selection into individual Range objects and then returns the objects as a collection. You can use the Count property on the returned collection to verify a selection that contains more than one area, as shown in the following example. Sub NoMultiAreaSelection() NumberOfSelectedAreas = Selection.Areas.Count If NumberOfSelectedAreas > 1 Then MsgBox "You cannot carry out this command " & _ "on multi-area selections" End If End Sub

Methods Name

Description

Activate

Activates a single cell, which must be inside the current selection. To select a range of cells, use the Select method.

AddComment

Adds a comment to the range.

AdvancedFilter

Filters or copies data from a list based on a criteria range. If the initial selection is a single cell, that cell's current region is used.

BorderAround

Adds a border to a range and sets the Color, LineStyle, and Weight properties for the new border. Variant.

Calculate


CalculateRowMajorOrder

Calculates a specfied range of cells.

CheckSpelling

Checks the spelling of an object.

Clear

Clears the entire object.

AllocateChanges

Performs a writeback operation for all edited cells in a range based on an OLAP data source.

ClearComments

Clears all cell comments from the specified range.

ApplyNames

Applies names to the cells in the specified range.

ClearContents

Clears the formulas from the range.

ApplyOutlineStyles

Applies outlining styles to the specified range.

ClearFormats

Clears the formatting of the object.

AutoComplete

Returns an AutoComplete match from the list. If there’s no AutoComplete match or if more than one entry in the list matches the string to complete, this method returns an empty string.

ClearHyperlinks

Removes all hyperlinks from the specified range.

ClearNotes

Clears notes and sound notes from all the cells in the specified range.

AutoFill

Performs an autofill on the cells in the specified range.

ClearOutline

Clears the outline for the specified range.

ColumnDifferences AutoFilter

Filters a list using the AutoFilter.

Returns a Range object that represents all the cells whose contents are different from the comparison cell in each column.

AutoFit

Changes the width of the columns in the range or the height of the rows in the range to achieve the best fit.

Consolidate

Consolidates data from multiple ranges on multiple worksheets into a single range on a single worksheet. Variant.

Copy

Copies the range to the specified range or to the Clipboard.

AutoOutline

Automatically creates an outline for the specified range. If the range is a single cell, Microsoft Excel creates an outline for the entire sheet. The new outline replaces any existing outline.

CopyFromRecordset

Copies the contents of an ADO or DAO Recordset object onto a worksheet, beginning at the upper-left corner of the specified range. If the Recordset object contains fields with OLE objects in them, this method fails.

CopyPicture

Copies the selected object to the Clipboard as a picture. Variant.

CreateNames

Creates names in the specified range, based on text labels in the sheet.

Cut

Cuts the object to the Clipboard or pastes it into a specified destination.

DataSeries

Creates a data series in the specified range. Variant.

Delete

Deletes the object.

DialogBox

Displays a dialog box defined by a dialog box definition table on a Microsoft Excel 4.0 macro sheet. Returns the number of the chosen control, or returns False if the user clicks theCancel button.

Dirty

Designates a range to be recalculated when the next recalculation occurs.

DiscardChanges

Discards all changes in the edited cells of the range.

EditionOptions


ExportAsFixedFormat

Exports to a file of the specified format.

FillDown

Fills down from the top cell or cells in the specified range to the bottom of the range. The contents and

formatting of the cell or cells in the top row of a range are copied into the rest of the rows in the range. FillLeft

Fills left from the rightmost cell or cells in the specified range. The contents and formatting of the cell or cells in the rightmost column of a range are copied into the rest of the columns in the range.

FillRight

Fills right from the leftmost cell or cells in the specified range. The contents and formatting of the cell or cells in the leftmost column of a range are copied into the rest of the columns in the range.

FillUp

Fills up from the bottom cell or cells in the specified range to the top of the range. The contents and formatting of the cell or cells in the bottom row of a range are copied into the rest of the rows in the range.

Find

Finds specific information in a range.

FindNext

Continues a search that was begun with the Find method. Finds the next cell that matches those same conditions and returns a Range object that represents that cell. This does not affect the selection or the active cell.

FindPrevious

Continues a search that was begun with the Find method. Finds the previous cell that matches those same conditions and returns a Range object that represents that cell. Doesn’t affect the selection or the active cell.

FlashFill

TRUE indicates that the Excel Flash Fill feature has been enabled and active.

FunctionWizard

Starts the Function Wizard for the upper-left cell of the range.

Group

Insert

When the Range object represents a single cell in a PivotTable field’s data range, the Groupmethod performs numeric or date-based grouping in that field.

PrintOut

Prints the object.

PrintPreview


Inserts a cell or a range of cells into the worksheet or macro sheet and shifts other cells away to make space.

RemoveDuplicates

Removes duplicate values from a range of values.

RemoveSubtotal

Removes subtotals from a list.

Replace

Returns a Boolean indicating characters in cells within the specified range. Using this method doesn’t change either the selection or the active cell.

RowDifferences

Returns a Range object that represents all the cells whose contents are different from those of the comparison cell in each row.

Run

Runs the Microsoft Excel macro at this location. The range must be on a macro sheet.

Select

Selects the object.

SetPhonetic

Creates Phonetic objects for all the cells in the specified range.

Show

Scrolls through the contents of the active window to move the range into view. The range must consist of a single cell in the active document.

ShowDependents

Draws tracer arrows to the direct dependents of the range.

ShowErrors

Draws tracer arrows through the precedents tree to the cell that’s the source of the error, and returns the range that contains that cell.

ShowPrecedents

Draws tracer arrows to the direct precedents of the range.

InsertIndent

Adds an indent to the specified range.

Justify

Rearranges the text in a range so that it fills the range evenly.

ListNames

Pastes a list of all nonhidden names onto the worksheet, beginning with the first cell in the range.

Merge

Creates a merged cell from the specified Range object.

NavigateArrow

NoteText

Navigates a tracer arrow for the specified range to the precedent, dependent, or error-causing cell or cells. Selects the precedent, dependent, or error cells and returns a Rangeobject that represents the new selection. This method causes an error if it's applied to a cell without visible tracer arrows. Returns or sets the cell note associated with the cell in the upper-left corner of the range. Read/write String. Cell notes have been replaced by range comments. For more information, see the Comment object.

Parse

Parses a range of data and breaks it into multiple cells. Distributes the contents of the range to fill several adjacent columns; the range can be no more than one column wide.

PasteSpecial

Pastes a Range from the Clipboard into the specified range.

Sort

Sorts a range of values.

SortSpecial

Uses East Asian sorting methods to sort the range, a PivotTable report, or uses the method for the active region if the range contains only one cell. For example, Japanese sorts in the order of the Kana syllabary.

Speak

Causes the cells of the range to be spoken in row order or column order.

SpecialCells

Returns a Range object that represents all the cells that match the specified type and value.

SubscribeTo


Subtotal

Creates subtotals for the range (or the current region, if the range is a single cell).

Table

Creates a data table based on input values and formulas that you define on a worksheet.

TextToColumns

Parses a column of cells that contain text into several columns.

Ungroup

Promotes a range in an outline (that is, decreases its outline level). The specified range must be a row or column, or a range of rows or columns. If the range is in a PivotTable report, this method ungroups the items contained in the range.

UnMerge

Separates a merged area into individual cells.

Properties Name

Description

AddIndent

Returns or sets a Variant value that indicates if text is automatically indented when the text alignment in a cell is set to equal distribution (either horizontally or vertically.)

Address

Returns a String value that represents the range reference in the language of the macro.

AddressLocal

Returns the range reference for the specified range in the language of the user. Read-only String.

AllowEdit

Returns a Boolean value that indicates if the range can be edited on a protected worksheet.

Application


Areas

Returns an Areas collection that represents all the ranges in a multiple-area selection. Read-only.

Borders

Returns a Borders collection that represents the borders of a style or a range of cells (including a range defined as part of a conditional format).

Cells

Returns a Range object that represents the cells in the specified range.

Characters

Returns a Characters object that represents a range of characters within the object text. You can use the Characters object to format characters within a text string.

Column

Returns the number of the first column in the first area in the specified range. Read-only Long.

DisplayFormat

Returns a DisplayFormat object that represents the display settings for the specified range. Read-only

Columns

Returns a Range object that represents the columns in the specified range.

End

ColumnWidth

Returns or sets the width of all columns in the specified range. Read/write Variant.

Returns a Range object that represents the cell at the end of the region that contains the source range. Equivalent to pressing END+UP ARROW, END+DOWN ARROW, END+LEFT ARROW, or END+RIGHT ARROW. Read-only Range object.

EntireColumn

Returns a Range object that represents the entire column (or columns) that contains the specified range. Read-only.

EntireRow

Returns a Range object that represents the entire row (or rows) that contains the specified range. Read-only.

Errors

Allows the user to to access error checking options.

Font

Returns a Font object that represents the font of the specified object.

FormatConditi ons

Returns a FormatConditions collection that represents all the conditional formats for the specified range. Read-only.

Formula

Returns or sets a Variant value that represents the object's formula in A1-style notation and in the macro language.

Comment

Returns a Comment object that represents the comment associated with the cell in the upper-left corner of the range.

Count


CountLarge

Returns a value that represents the number of objects in the collection. Read-only Variant.

CurrentArray

CurrentRegion

If the specified cell is part of an array, returns a Range object that represents the entire array. Read-only. Returns a Range object that represents the current region. The current region is a range bounded by any combination of blank rows and blank columns. Read-only.

Dependents

Returns a Range object that represents the range containing all the dependents of a cell. This can be a multiple selection (a union of Range objects) if there’s more than one dependent. Read-onlyRange object.

FormulaArray

Returns or sets the array formula of a range. Returns (or can be set to) a single formula or a Visual Basic array. If the specified range doesn't contain an array formula, this property returnsnull. Read/write Variant.

DirectDepende nts

Returns a Range object that represents the range containing all the direct dependents of a cell. This can be a multiple selection (a union of Range objects) if there’s more than one dependent. Read-only Range object.

FormulaHidden

Returns or sets a Variant value that indicates if the formula will be hidden when the worksheet is protected.

FormulaLocal

Returns or sets the formula for the object, using A1-style references in the language of the user. Read/write Variant.

FormulaR1C1

Returns or sets the formula for the object, using R1C1-style notation in the language of the macro. Read/write Variant.

DirectPreceden ts

Returns a Range object that represents the range containing all the direct precedents of a cell. This can be a multiple selection (a union of Range objects) if there’s more than one precedent. Read-only Range object.

FormulaR1C1L ocal

Returns or sets the formula for the object, using R1C1-style notation in the language of the user. Read/write Variant.

ListHeaderRows

Returns the number of header rows for the specified range. Read-only Long.

HasArray

True if the specified cell is part of an array formula. Readonly Variant.

ListObject

Returns a ListObject object for the Range object. Readonly ListObject object.

HasFormula

True if all cells in the range contain formulas; False if none of the cells in the range contains a formula; null otherwise. Readonly Variant.

LocationInTable

Returns a constant that describes the part of the PivotTable report that contains the upper-left corner of the specified range. Can be one of the following XlLocationInTable. constants. Read-only Long.

Height

Returns or sets a Variant value that represents the height, in points, of the range.

Locked

Returns or sets a Variant value that indicates if the object is locked.

Returns or sets a Variant value that indicates if the rows or columns are hidden.

MergeArea

Returns a Range object that represents the merged range containing the specified cell. If the specified cell isn’t in a merged range, this property returns the specified cell. Readonly Variant.

MergeCells

True if the range contains merged cells. Read/write Variant.

Name

Returns or sets a Variant value that represents the name of the object.

Next

Returns a Range object that represents the next cell.

Hidden

HorizontalAlign ment

Returns or sets a Variant value that represents the horizontal alignment for the specified object.

Hyperlinks

Returns a Hyperlinks collection that represents the hyperlinks for the range.

ID

Returns or sets a String value that represents the identifying label for the specified cell when the page is saved as a Web page.

IndentLevel

Returns or sets a Variant value that represents the indent level for the cell or range. Can be an integer from 0 to 15.

NumberFormat

Returns or sets a Variant value that represents the format code for the object.

Interior

Returns an Interior object that represents the interior of the specified object.

NumberFormat Local

Returns or sets a Variant value that represents the format code for the object as a string in the language of the user.

Item

Returns a Range object that represents a range at an offset to the specified range.

Offset

Returns a Range object that represents a range that’s offset from the specified range.

Left

Returns a Variant value that represents the distance, in points, from the left edge of column A to the left edge of the range.

Orientation

Returns or sets a Variant value that represents the text orientation.

OutlineLevel

Returns or sets the current outline level of the specified row or column. Read/write Variant.

ReadingOrder

Returns or sets the reading order for the specified object. Can be one of the following constants:xlRTL (right-toleft), xlLTR (left-to-right), or xlContext. Read/write Long.

PageBreak

Returns or sets the location of a page break. Can be one of the following XlPageBreak constants:xlPageBreakAutomatic, xlP ageBreakManual, or xlPageBreakNone. Read/write Long.

Resize

Resizes the specified range. Returns a Range object that represents the resized range.

Parent


Row

Returns the number of the first row of the first area in the range. Read-only Long.

Phonetic

Returns the Phonetic object, which contains information about a specific phonetic text string in a cell.

RowHeight

Returns or sets the height of the first row in the range specified, measured in points. Read/writeVariant.

Returns a PivotCell object that represents a cell in a PivotTable report.

Rows

Returns a Range object that represents the rows in the specified range. Read-only Range object.

Returns a PivotField object that represents the PivotTable field containing the upper-left corner of the specified range.

ServerActions

Specifies the actions that can be performed on the SharePoint server for a Range object.

Returns a PivotItem object that represents the PivotTable item containing the upper-left corner of the specified range.

ShowDetail

True if the outline is expanded for the specified range (so that the detail of the column or row is visible). The specified range must be a single summary column or row in an outline. Read/writeVariant. For the PivotItem object (or the Range object if the range is in a PivotTable report), this property is set to True if the item is showing detail.

ShrinkToFit

Returns or sets a Variant value that indicates if text automatically shrinks to fit in the available column width.

SparklineGroups

Returns a SparklineGroups object that represents an existing group of sparklines from the specified range. Read-only

Style

Returns or sets a Variant value, containing a Style object, that represents the style of the specified range.

Summary

True if the range is an outlining summary row or column. The range should be a row or a column. Read-only Variant.

PivotCell

PivotField

PivotItem

PivotTable

Returns a PivotTable object that represents the PivotTable report containing the upper-left corner of the specified range.

Precedents

Returns a Range object that represents all the precedents of a cell. This can be a multiple selection (a union of Range objects) if there's more than one precedent. Read-only.

PrefixCharacter

Returns the prefix character for the cell. Read-only Variant.

Previous

Returns a Range object that represents the next cell.

QueryTable

Returns a QueryTable object that represents the query table that intersects the specified Rangeobject.

Range

Returns a Range object that represents a cell or a range of cells.

Text

Returns or sets the text for the specified object. Readonly String.

Top

Returns a Variant value that represents the distance, in points, from the top edge of row 1 to the top edge of the range.

UseStandardHeight

True if the row height of the Range object equals the standard height of the sheet. Returns Nullif the range contains more than one row and the rows aren’t all the same height. Read/writeVariant.

UseStandardWidth

True if the column width of the Range object equals the standard width of the sheet. Returnsnull if the range contains more than one column and the columns aren’t all the same width. Read/write Variant.

Validation

Returns the Validation object that represents data validation for the specified range. Read-only.

Value

Returns or sets a Variant value that represents the value of the specified range.

Value2

Returns or sets the cell value. Read/write Variant.

VerticalAlignm ent

Returns or sets a Variant value that represents the vertical alignment of the specified object.

Width

Returns a Variant value that represents the width, in units, of the range.

Worksheet

Returns a Worksheet object that represents the worksheet containing the specified range. Read-only.

WrapText

Returns or sets a Variant value that indicates if Microsoft Excel wraps the text in the object.

Cómo seleccionar celdas y rangos mediante

Método Activate Cells Application.Goto Offset Range Resize Select Sheets Workbooks End CurrentRegion

Propiedad ActiveSheet ActiveWorkbook Columns.Count Rows.Count Selection

:

Argumentos ninguno rowIndex, columnIndex reference, scroll rowOffset, columnOffset cell1 cell1, cell2 rowSize, columnSize ninguno index (o sheetName) index (o bookName) direction ninguno

Uso especificar la hoja activa especificar el libro activo número de columnas en la selección número de filas en la selección referirse al rango seleccionado

Cómo seleccionar una celda en la hoja activa a. ActiveSheet.Cells(5, 4).Select b. ActiveSheet.Range("D5").Select

Cómo seleccionar una celda en otra hoja del mismo libro a. Application.Goto ActiveWorkbook.Sheets("Sheet2").Cells(6, 5) b. Application.Goto (ActiveWorkbook.Sheets("Sheet2").Range("E6")) c. Sheets("Sheet2").Activate ActiveSheet.Cells(6, 5).Select

Cómo seleccionar un rango de celdas en la hoja activa Para seleccionar el rango C2:D10 en la hoja activa: a. ActiveSheet.Range(Cells(2, 3), Cells(10, 4)).Select b. ActiveSheet.Range("C2:D10").Select c. ActiveSheet.Range("C2", "D10").Select

Cómo seleccionar un rango de celdas en otra hoja del mismo libro Para seleccionar el rango D3:E11 en otra hoja del mismo libro: a. Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3:E11") b. Application.Goto ActiveWorkbook.Sheets("Sheet3").Range("D3", "E11") c. Sheets("Sheet3").Activate ActiveSheet.Range(Cells(3, 4), Cells(11, 5)).Select

Cómo seleccionar una celda en una hoja de un libro diferente Para seleccionar la celda F7 en una hoja de un libro diferente: a. Application.Goto Workbooks("BOOK2.XLS").Sheets("Sheet1").Cells(7, 6) b. Application.Goto _ Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("F7") c. Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate ActiveSheet.Cells(7, 6).Select

Cómo seleccionar un rango de celdas en una hoja de un libro diferente Para seleccionar el rango E4:F12 en una hoja de otro libro: a. Application.Goto _ Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4:F12") b. Application.Goto _ Workbooks("BOOK2.XLS").Sheets("Sheet1").Range("E4", "F12") c. Workbooks("BOOK2.XLS").Sheets("Sheet1").Activate ActiveSheet.Range(Cells(4, 5), Cells(12, 6)).Select

Cómo seleccionar un rango con nombre en la hoja activa Para seleccionar el rango con nombre "Test" de la hoja activa: a. Range("Test").Select b. Application.Goto "Test" Si se asigna el nombre a una variable: c. Dim MyVar as String MyVar = “Test” Range(MyVar).Select Application.Goto MyVar

Cómo seleccionar un rango con nombre en otra hoja del mismo libro Para seleccionar el rango con nombre "Test" en otra hoja del mismo libro: a. Application.Goto Sheets("Sheet1").Range("Test") b. Sheets("Sheet1").Activate Range("Test").Select

Cómo seleccionar una celda relativa a la celda activa Para seleccionar una celda que se encuentra cinco filas más abajo y cuatro columnas a la izquierda de la celda activa: a. ActiveCell.Offset(5, -4).Select Para seleccionar una celda que se encuentra dos filas más arriba y tres columnas a la derecha de la celda activa: b. ActiveCell.Offset(-2, 3).Select

Cómo seleccionar una celda relativa a otra celda (que no sea la celda activa) Para seleccionar una celda que se encuentra cinco filas más abajo y cuatro columnas a la derecha de la celda C7: a. ActiveSheet.Cells(7, 3).Offset(5, 4).Select b. ActiveSheet.Range("C7").Offset(5, 4).Select

Cómo seleccionar un rango de celdas separadas por un rango específico Cómo seleccionar un rango con nombre en una hoja de un libro diferente Para seleccionar el rango con nombre "Test" en una hoja de otro libro: a. Application.Goto _ Workbooks("BOOK2.XLS").Sheets("Sheet2").Range("Test") b. Workbooks("BOOK2.XLS").Sheets("Sheet2").Activate Range("Test").Select

Para seleccionar un rango de celdas que sea del mismo tamaño que el rango con nombre "Test" pero que estén desplazadas cuatro filas más abajo y tres columnas a la derecha: a. ActiveSheet.Range("Test").Offset(4, 3).Select Si el rango con nombre está en otra hoja (que no sea la activa), active primero esa hoja y, a continuación, seleccione el rango: b. Sheets("Sheet3").Activate ActiveSheet.Range("Test").Offset(4, 3).Select

Cómo seleccionar un rango especificado y cambiar el tamaño de la selección

Cómo seleccionar la intersección de dos o más rangos especificados

Para seleccionar el rango con nombre "Database" y, a continuación, extender la selección cinco filas: a. Range("Database").Select Selection.Resize(Selection.Rows.Count + 5, _ Selection.Columns.Count).Select

Para seleccionar la intersección de los dos rangos con nombre "Test" y "Sample", puede utilizar el ejemplo siguiente, Tenga en cuenta que ambos rangos deben estar en la misma hoja para que este ejemplo funcione:

Cómo seleccionar un rango especificado, desplazarlo y cambiar su tamaño Para seleccionar un rango cuatro filas más abajo y tres columnas a la derecha del rango con nombre "Database" e incluir dos filas y una columna más que el rango con nombre: a. Range("Database").Select Selection.Offset(4, 3).Resize(Selection.Rows.Count + 2, _ Selection.Columns.Count + 1).Select

Cómo seleccionar la unión de dos o más rangos especificados Para seleccionar la unión (es decir, el área combinada) de los dos rangos con nombre "Test" y "Sample": a. Application.Union(Range("Test"), Range("Sample")).Select Tenga en cuenta que ambos rangos deben estar en la misma hoja para que este ejemplo funcione. Observe también que el método Union no funciona entre diferentes hojas. Por ejemplo, esta línea funciona correctamente: b. Set y = Application.Union(Range("Sheet1!A1:B2"), _ Range("Sheet1!C3:D4")) pero esta línea devuelve un mensaje de error c. Set y = Application.Union(Range("Sheet1!A1:B2"), _ Range("Sheet2!C3:D4"))

a. Application.Intersect(Range("Test"), Range("Sample")).Select

Cómo seleccionar la última celda de una columna de datos contiguos Para seleccionar la última celda de una columna contigua, utilice el ejemplo siguiente: a. ActiveSheet.Range("a1").End(xlDown).Select

Cómo seleccionar la celda en blanco en la parte inferior de una columna de datos contiguos Para seleccionar la celda situada debajo de un rango de celdas contiguas, utilice el ejemplo siguiente: a. ActiveSheet.Range("a1").End(xlDown).Offset(1,0).Select

Cómo seleccionar un rango completo de celdas contiguas en una columna Para seleccionar un rango de celdas contiguas en una columna, utilice uno de los ejemplos siguientes: a. ActiveSheet.Range("a1", ActiveSheet.Range("a1").End(xlDown)).Select b. ActiveSheet.Range("a1:" & ActiveSheet.Range("a1"). _ End(xlDown).Address).Select

Cómo seleccionar un rango rectangular de celdas Para seleccionar un rango de celdas rectangular alrededor de una celda, utilice el método CurrentRegion. El rango seleccionado mediante el método CurrentRegion es un área limitada por cualquier combinación de filas en blanco y columnas vacías. A continuación se muestra un ejemplo de cómo utilizar el método CurrentRegion: a. ActiveSheet.Range("a1").CurrentRegion.Select Este código seleccionará las celdas entre A1 y C4. Otros ejemplos para seleccionar el mismo rango de celdas son los siguientes: b. ActiveSheet.Range("a1", _ ActiveSheet.Range("a1").End(xlDown).End(xlToRight)).Select

Cómo seleccionar un rango completo de celdas que no sean contiguas en una columna Para seleccionar un rango de celdas que no sean contiguas en una columna, utilice uno de los ejemplos siguientes: a. ActiveSheet.Range("a1",ActiveSheet.Range("a65536"). _ End(xlUp)).Select b. ActiveSheet.Range("a1:" & ActiveSheet.Range("a65536"). _ End(xlUp).Address).Select

c. ActiveSheet.Range("a1:" & _ ActiveSheet.Range("a1").End(xlDown).End(xlToRight).Address).Select

En algunos casos, es posible que desee seleccionar las celdas entre A1 y C6. En este ejemplo, el método CurrentRegion no funcionará porque hay una línea en blanco en la fila 5. Los ejemplos siguientes seleccionarán todas las celdas: a. lastCol = ActiveSheet.Range("a1").End(xlToRight).Column lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row ActiveSheet.Range("a1", ActiveSheet.Cells(lastRow, lastCol)).Select b. lastCol = ActiveSheet.Range("a1").End(xlToRight).Column lastRow = ActiveSheet.Cells(65536, lastCol).End(xlUp).Row ActiveSheet.Range("a1:" & _ ActiveSheet.Cells(lastRow, lastCol).Address).Select

Cómo seleccionar varias columnas no contiguas de longitud variable

Propiedad Range.End Sintaxis

expresión .End(Direction) expresión Variable que representa un objeto Range. Direction

Queremos seleccionar las celdas con información de las columnas A y C.

Nombre

Descripción

xlDown

Hacia abajo.

xlToLeft

Hacia la izquierda.

xlToRight

Hacia la derecha.

xlUp

Hacia arriba.

StartRange = "A1" EndRange = "C1" Set a = Range(StartRange, Range(StartRange).End(xlDown)) Set b = Range(EndRange, Range(EndRange).End(xlDown)) Union(a,b).Select Cuando este código se utilice con la tabla de ejemplo, se seleccionarán las celdas A1:A3 y C1:C6.

Ejemplo Este ejemplo selecciona la celda del principio de la columna B en la región que contiene la celda B4. Range("B4").End(xlUp).Select Este ejemplo selecciona la celda del final de la fila 4 en la región que contiene la celda B4. Range("B4").End(xlToRight).Select Este ejemplo amplía la selección desde la celda B4 hasta la última celda de la fila cuatro que contenga datos. Worksheets("Sheet1").Activate Range("B4", Range("B4").End(xlToRight)).Select

Propiedad Range.Areas

Propiedad Range.Cells

Devuelve una colección de Areas que representa todos los intervalos de una selección de varias áreas.

Devuelve un objeto Range que representa las celdas del rango especificado.

Sintaxis

Sintaxis

expresión .Cells

expresión .Areas expresión Variable que representa un objeto Range.

expresión Variable que representa un objeto Range.

Observaciones

Ejemplo Este ejemplo muestra un mensaje si el usuario intenta ejecutar un comando cuando están seleccionadas varias áreas. El ejemplo debe ejecutarse desde una hoja de cálculo.

Puesto que la propiedad Item es la propiedad predeterminada del objeto Range, puede especificar el índice de fila y columna inmediatamente después de la palabra clave Cells. Para obtener más información, vea la propiedad Item y los ejemplos de dicho tema.

If Selection.Areas.Count > 1 Then MsgBox "Cannot do this to a multi-area selection." End If

Propiedad Range.Borders Devuelve una colección Borders que representa los bordes de un estilo o de un rango de celdas.

Si se usa esta propiedad sin un calificador de objeto, se devuelve un objeto Range que representa todas las celdas de la hoja activa.

Ejemplo En este ejemplo se establece el estilo de fuente de las celdas A1:C5 de Sheet1 como cursiva. Worksheets("Sheet1").Activate Range.Item(Cells(1, 1), Cells(5, 3)).Font.Italic = True

Sintaxis

expresión .Borders expresión Variable que representa un objeto Range.

Ejemplo Borde fino de color rojo para el borde inferior de la celda B2 de Sheet1. Sub SetRangeBorder()

En este ejemplo se examina una columna de datos denominados "myRange".Si una celda tiene el mismo valor que la celda situada inmediatamente sobre él, en el ejemplo se muestra la dirección de la celda que contiene los datos duplicados. Set r = Range("myRange") For n = 1 To r.Rows.Count If r.Cells(n, 1) = r.Cells(n + 1, 1) Then

With Worksheets("Sheet1").Range("B2").Borders(xlEdgeBottom)

MsgBox "Duplicate data in " & _

.LineStyle = xlContinuous .Weight = xlThin .ColorIndex = 3 End With End Sub

r.Cells(n + 1, 1).Address End If Next n

Propiedad Range.Characters

Propiedad Range.Column

Devuelve un objeto Characters que representa un rango de caracteres de texto del objeto. Puede usar el objeto Characters a formato de caracteres dentro de una cadena de texto.

Devuelve el número de la primera columna del primer área del rango especificado. Long de sólo lectura.

Sintaxis

Sintaxis

expresión .Column

expresión .Characters(Start, Length)



Parámetros

Observaciones

Nombre

Necesario/Opcional

Descripción

Start

Opcional

Primer carácter que se debe devolver. Si el argumento es 1 o no se especifica, la propiedad devuelve un rango de caracteres que empieza con el primer carácter.

Length

Opcional

Número de caracteres que se deben devolver. Si no se especifica este argumento, la propiedad devuelve el resto de la cadena (todo lo que esté después del carácter Start).

Column A devuelve 1, column B devuelve 2, y así sucesivamente. Para devolver el número de la última columna del rango, use la expresión siguiente: myRange.Columns(myRange.Columns.Count).Column

Ejemplo Este ejemplo establece en 4 puntos el ancho de las columnas alternas de Sheet1. For Each col In Worksheets("Sheet1").Columns If col.Column Mod 2 = 0 Then col.ColumnWidth = 4 End If

Observaciones El objeto Characters no es una colección.

Ejemplo En este ejemplo se da formato de negrita al tercer carácter de la celda A1 de Sheet1. With Worksheets("Sheet1").Range("A1") .Value = "abcdefg" .Characters(3, 1).Font.Bold = True End With

Next col

Propiedad Range.Columns Devuelve un objeto Range que representa las columnas del rango especificado.

Sintaxis

expresión .Columns


Observaciones El uso de esta propiedad sin un calificador de objeto equivale a usar ActiveSheet.Columns.

En este ejemplo se muestra el número de columnas de la selección de Sheet1.Si se ha seleccionado más de un área, el ejemplo ejecuta un bucle en cada área. Worksheets("Sheet1").Activate areaCount = Selection.Areas.Count If areaCount <= 1 Then MsgBox "The selection contains " & _ Selection.Columns.Count & " columns." Else For i = 1 To areaCount MsgBox "Area " & i & " of the selection contains " & _

Si se aplica a un objeto Range que es una selección de varias áreas, la propiedad únicamente devuelve las columnas de la primera área del rango. Por ejemplo, si el objeto Range tiene dos áreas, A1:B2 y C3:D4, Selection.Columns.Count devuelve 2, no 4. Si desea utilizar esta propiedad en un rango que puede contener una selección de varias áreas, compruebe Areas.Count para determinar si el rango contiene más de un área. En ese caso, ejecute un bucle sobre cada área del rango.

Ejemplo En este ejemplo se establece como 0 (cero) el valor de todas las celdas de la columna uno del rango denominado "myRange". Range("myRange").Columns(1).Value = 0

Selection.Areas(i).Columns.Count & " columns." Next i End If

Propiedad Range.ColumnWidth Devuelve o establece el ancho de las columnas del rango especificado. Variant de lectura y escritura.

Sintaxis

expresión .ColumnWidth expresión Variable que representa un objeto Range.

Observaciones Una unidad de ancho de columna equivale al ancho de un carácter con el estilo Normal. En las fuentes proporcionales se utiliza el ancho del carácter 0 (cero). Utilice la propiedad Width para devolver el ancho de una columna en puntos. Si todas las columnas en el rango tienen el mismo ancho, lo devolverá la propiedad ColumnWidth. Si las columnas del rango tienen anchos diferentes, la propiedad devolverá Null.

Ejemplo

Ejemplos

Este ejemplo dobla el ancho de la columna A de Sheet1.

En este ejemplo se muestra el número de columnas de la selección de Sheet1. El código comprueba también de una selección de varias áreas; Si lo hay, el código se repite en las áreas de la selección de varias áreas.

With Worksheets("Sheet1").Columns("A") .ColumnWidth = .ColumnWidth * 2 End With

Sub DisplayColumnCount() Dim iAreaCount As Integer Dim i As Integer

Propiedad Range.Comment Devuelve un objeto Comment que representa el comentario asociado a la celda de la esquina superior izquierda del rango.

Worksheets("Sheet1").Activate iAreaCount = Selection.Areas.Count If iAreaCount <= 1 Then

Sintaxis

MsgBox "The selection contains " & _

expresión .Comment expresión Variable que representa un objeto Range.

Selection.Columns.Count & " columns." Else For i = 1 To iAreaCount MsgBox "Area " & i & _

Propiedad Range.Count

"of the selection contains " & _

Devuelve un valor Long que representa el número de objetos de la colección.

Sintaxis

Selection.Areas(i).Columns.Count _ & " columns."

expresión .Count expresión Variable que representa un objeto Range.

Notas La propiedad Count es funcionalmente la misma que la propiedad CountLarge , excepto en que la propiedad Count generará un error de desbordamiento si el rango especificado no tiene más de 2,147,483,647 celdas (uno menos de 2048 columnas). Sin embargo, la propiedad CountLarge , puede controlar rangos hasta el tamaño máximo para una hoja de cálculo, que es 17,179,869,184 celdas.

Next i End If End Sub

Propiedad Range.CurrentArray Si la celda especificada es parte de una matriz, devuelve un objeto Range que representa la matriz completa. Solo lectura.

En este ejemplo se supone que Sheet1 contiene una tabla con una fila de encabezado.El ejemplo selecciona la tabla sin seleccionar la fila de encabezado.Para poder ejecutar el ejemplo, la celda activa debe estar en la tabla. Set tbl = ActiveCell.CurrentRegion

Sintaxis

expresión .CurrentArray expresión Variable que representa un objeto Range.

Ejemplo En este ejemplo se supone que la celda activa es la A1 de Sheet1 y que forma parte de una matriz que incluye las celdas A1:A10. El ejemplo selecciona las celdas A1:A10 de Sheet1.

tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select

Propiedad Range.EntireColumn Devuelve una opción de objeto de Range que representa la columna completa (o columnas) que contiene el rango especificado

ActiveCell.CurrentArray.Select

Sintaxis


Propiedad Range.CurrentRegion Devuelve un objeto Range que representa la región actual. La región actual es un rango delimitado por cualquier combinación de filas y columnas en blanco. Solo lectura.

Sintaxis

expresión .CurrentRegion expresión Variable que representa un objeto Range.

Observaciones Esta propiedad es útil para muchas operaciones que amplían automáticamente la selección para incluir toda la región actual, por ejemplo, el método AutoFormat . Esta propiedad no se puede utilizar en hojas de cálculo protegidas.

Ejemplo Este ejemplo selecciona la región actual de Sheet1. Worksheets("Sheet1").Activate ActiveCell.CurrentRegion.Select

expresión .EntireColumn

Ejemplo Este ejemplo establece el valor de la primera celda de la columna que contiene la celda activa. El ejemplo debe ejecutarse desde una hoja de cálculo. ActiveCell.EntireColumn.Cells(1, 1).Value = 5

Propiedad Range.EntireRow Devuelve una opción de objeto de Range que representa el toda la fila (o filas) que contiene el rango especificado.

Sintaxis

expresión .EntireRow expresión Variable que representa un objeto Range.

Ejemplo Este ejemplo establece el valor de la primera celda de la fila que contiene la celda activa. ActiveCell.EntireRow.Cells(1, 1).Value = 5

Propiedad Range.FormulaArray

Propiedad Range.FormulaHidden

Devuelve o establece la fórmula de matriz de un rango. Devuelve una sola fórmula o una matriz de Visual Basic (o puede establecerse como ellas). Si el rango especificado no contiene una fórmula de matriz, esta propiedad devolverá null. Variant de lectura y escritura.

Devuelve o establece un valor de tipo Variant que indica si la fórmula se ocultará cuando la hoja de cálculo esté protegida.

Sintaxis

Sintaxis

expresión .FormulaHidden expresión Variable que representa un objeto Range.

expresión .FormulaArray expresión Variable que representa un objeto Range.

Observaciones Si usa esta propiedad para escribir una fórmula de matriz, la fórmula deberá estar en el estilo F1C1, no en el estilo de referencia A1 (vea el segundo ejemplo). La propiedad FormulaArray también tiene un límite de 255 caracteres.

Ejemplo Este ejemplo escribe el número 3 como una constante matricial en las celdas A1:C5 de Sheet1. Worksheets("Sheet1").Range("A1:C5").FormulaArray = "=3"

Este ejemplo escribe la fórmula de matriz =SUM(R1C1:R3C3) en las celdas E1:E3 de Sheet1. Worksheets("Sheet1").Range("E1:E3").FormulaArray = _ "=Sum(R1C1:R3C3)"

Observaciones Esta propiedad devuelve True si la fórmula va a estar oculta cuando el libro esté protegido, Null si el rango especificado contiene algunas celdas con FormulaHidden igual a True y algunas celdas conFormulaHidden igual a False. Es importante no confundir esta propiedad con la propiedad Hidden . La fórmula no se ocultará si el libro está protegido y la hoja de cálculo no es, pero sólo si la hoja de cálculo está protegida.

Ejemplo En este ejemplo se ocultan las fórmulas de las celdas A1 y B1 de Sheet1 cuando la hoja de cálculo está protegida. Sub HideFormulas() Worksheets("Sheet1").Range("A1:B1").FormulaHidden = True End Sub

Propiedad Range.FormulaR1C1

Propiedad Range.FormulaR1C1Local

Devuelve o establece la fórmula del objeto mediante la notación de estilo R1C1 en el lenguaje de la macro. Valor Variant de lectura y escritura.

Devuelve o establece la fórmula del objeto mediante la notación de estilo R1C1 en el idioma del usuario. Valor Variant de lectura y escritura.

Sintaxis

Sintaxis

expresión .FormulaR1C1 expresión Variable que representa un objeto Range.

expresión .FormulaR1C1Local expresión Variable que representa un objeto Range.

Observaciones

Observaciones

Si la celda contiene una constante, esta propiedad la devolverá. Si está vacía, devolverá una cadena vacía. Si la celda contiene una fórmula, la propiedad devolverá la fórmula en forma de cadena, con el mismo formato en que se presentaría en la barra de fórmulas (incluido el signo igual).

Si la celda contiene una constante, esta propiedad la devolverá. Si está vacía, devolverá una cadena vacía. Si la celda contiene una fórmula, la propiedad devolverá la fórmula en forma de cadena, con el mismo formato en que se presentaría en la barra de fórmulas (incluido el signo igual).

Si se define el valor o la fórmula de una celda como una fecha, Microsoft Excel comprueba si dicha celda ya tiene uno de los formatos numéricos de fecha u hora. De lo contrario, cambia el formato numérico al formato numérico de fecha corto predeterminado.

Si se define el valor o la fórmula de una celda como una fecha, Microsoft Excel comprueba si dicha celda ya tiene uno de los formatos numéricos de fecha u hora. De lo contrario, cambia el formato numérico al formato numérico de fecha corto predeterminado.

Si el rango tiene una o dos dimensiones, se puede definir la fórmula conforme a una matriz de Visual Basic de las mismas dimensiones. Asimismo, es posible escribir la fórmula en una matriz de Visual Basic. Si se define la fórmula de un rango de varias celdas, se llenan todas las celdas del rango con la fórmula.

Si el rango tiene una o dos dimensiones, se puede definir la fórmula conforme a una matriz de Visual Basic de las mismas dimensiones. Asimismo, es posible escribir la fórmula en una matriz de Visual Basic.

Ejemplo En este ejemplo se establece una fórmula para la celda B1 de Sheet1. Worksheets("Sheet1").Range("B1").FormulaR1C1 = "=SQRT(R1C1)"

Si se define la fórmula de un rango de varias celdas, se llenan todas las celdas del rango con la fórmula.

Ejemplo Supongamos que escribe la formula =SUM(A1:A10) en la celda A11 de la hoja de cálculo uno, con la versión en inglés de EE.UU. de Microsoft Excel. Si abre el libro en un equipo en el que se usa la versión en alemán y ejecuta el ejemplo siguiente, éste mostrará la fórmula =SUMME(Z1S1:Z10S1) en un cuadro de mensaje. MsgBox Worksheets(1).Range("A11").FormulaR1C1Local

Propiedad Range.HasFormula

Propiedad Range.Hidden

True si todas las celdas del rango contienen fórmulas; False si ninguna de las celdas del rango contiene una fórmula; null en los demás casos. Variant de sólo lectura.

Devuelve o establece un valor de tipo Variant que indica si las filas o columnas están ocultas.

Sintaxis

Sintaxis expresión .HasFormula

expresión .Hidden expresión Variable que representa un objeto Range.


Observaciones

Ejemplo Este ejemplo solicita al usuario que seleccione un rango de Sheet1. Si todas las celdas del rango seleccionado contienen una fórmula, el ejemplo mostrará un mensaje. Worksheets("Sheet1").Activate Set rr = Application.InputBox( _ prompt:="Select a range on this worksheet", _ Type:=8)

Establezca esta propiedad en True para ocultar una fila o columna. El rango especificado debe abarcar la totalidad de una columna o una fila. Es importante no confundir esta propiedad con la propiedad FormulaHidden .

Ejemplo En este ejemplo se oculta la columna C de Sheet1. Worksheets("Sheet1").Columns("C").Hidden = True

If rr.HasFormula = True Then MsgBox "Every cell in the selection contains a formula" End If

Propiedad Range.HorizontalAlignment Devuelve o establece un valor de tipo Variant que representa la alineación horizontal del objeto especificado.

Propiedad Range.Height Devuelve o establece un valor de tipo Variant que representa el alto, en puntos, del rango.

Sintaxis

expresión .Height expresión Variable que representa un objeto Range.

Sintaxis

expresión .HorizontalAlignment expresión Variable que representa un objeto Range.

Observaciones El valor de esta propiedad se puede establecer en una de las siguientes constantes:

xlCenter

xlJustify

xlDistributed

xlLeft

xlRight

Propiedad Range.Item

Observaciones

Devuelve un objeto Range que representa un rango desplazado con respecto al rango especificado.

La sintaxis 1 usa como argumentos de índice un número o letra de columna. Para obtener más información sobre la sintaxis, vea el objeto Range. Los argumentos RowIndex y ColumnIndex son desplazamientos relativos. Es decir, si especifica el valor 1 para RowIndex se devuelven las celdas de la primera fila del rango, no de la primera fila de la hoja de datos. Por ejemplo, si la selección es la celda C3, Selection.Cells(2, 2) devuelve la celda D4 (puede usar la propiedad Item para indizar fuera del rango original).

Sintaxis

expresión .Item(RowIndex, ColumnIndex) expresión Variable que representa un objeto Range.

Parámetros Nombre

Necesario/Opcional

Descripción

RowIndex

Obligatorio

Número de índice de la celda a la que se desea obtener acceso, por orden de izquierda a derecha y después hacia abajo. Range.Item(1) devuelve la celda superior izquierda del rango; Range.Item(2) devuelve la celda inmediatamente a la derecha de la celda superior izquierda.

ColumnIndex

Opcional

Número o una cadena que indica el número de columna de la celda a la que se desea obtener acceso, empezando por 1 o "A" para la primera columna del rango.

Ejemplo En este ejemplo se rellena el rango A1:A10 de Sheet1 en función del contenido de la celda A1. Worksheets("Sheet1").Range.Item("A1:A10").FillDown

Propiedad Range.Left Devuelve un valor de tipo Variant que representa la distancia, en puntos, desde el borde izquierdo de la columna A hasta el borde izquierdo del rango.

Sintaxis

expresión .Left expresión Variable que representa un objeto Range.

Observaciones Si el rango no es continuo, se usa la primera área. Si el rango tiene más de una columna de ancho, se usa la columna del extremo izquierdo del rango.

Propiedad Range.ListHeaderRows

Propiedad Range.LocationInTable

Devuelve el número de filas de encabezado para el rango especificado. Long de sólo lectura.

Devuelve una constante que describe la parte del informe PivotTable que contiene la esquina superior izquierda del rango especificado. Puede ser una de las siguientes XlLocationInTable. constantes. Read-only largo.

Sintaxis

expresión .ListHeaderRows expresión Variable que representa un objeto Range.

Sintaxis

expresión .LocationInTable expresión Variable que representa un objeto Range.

Observaciones Antes de usar esta propiedad, utilice la propiedad CurrentRegion para localizar los límites del rango.

Observaciones

Ejemplo

XlLocationInTable puede ser una de estas constantes XlLocationInTable.

Este ejemplo establece la variable rTbl como el rango representado por la región actual de la celda activa, sin incluir filas de encabezado.

xlRowHeader

Set rTbl = ActiveCell.CurrentRegion ' remove the headers from the range

xlColumnHeader

iHdrRows = rTbl.ListHeaderRows If iHdrRows > 0 Then ' resize the range minus n rows Set rTbl = rTbl.Resize(rTbl.Rows.Count - iHdrRows) ' and then move the resized range down to ' get to the first non-header row Set rTbl = rTbl.Offset(iHdrRows) End If

xlPageHeader xlDataHeader xlRowItem xlColumnItem xlPageItem xlDataItem xlTableBody

Ejemplo

Propiedad Range.Locked (Excel)

Este ejemplo muestra un cuadro de texto que describe la ubicación de la celda activa en el informe de tabla dinámica.

Devuelve o establece un valor de tipo Variant que indica si el objeto está bloqueado.

Worksheets("Sheet1").Activate

Sintaxis

Select Case ActiveCell.LocationInTable


Case Is = xlRowHeader MsgBox "Active cell is part of a row header" Case Is = xlColumnHeader MsgBox "Active cell is part of a column header" Case Is = xlPageHeader MsgBox "Active cell is part of a page header"

expresión .Locked

Observaciones Esta propiedad devuelve True si el objeto está bloqueado, False si el objeto se puede modificar cuando la hoja esté protegida o Null si el rango especificado contiene celdas bloqueadas y desbloqueadas.

Case Is = xlDataHeader MsgBox "Active cell is part of a data header" Case Is = xlRowItem MsgBox "Active cell is part of a row item" Case Is = xlColumnItem MsgBox "Active cell is part of a column item"

Ejemplo En este ejemplo se desbloquean las celdas A1:G37 de Sheet1 para que puedan modificarse si la hoja está protegida. Worksheets("Sheet1").Range("A1:G37").Locked = False Worksheets("Sheet1").Protect

Case Is = xlPageItem MsgBox "Active cell is part of a page item" Case Is = xlDataItem MsgBox "Active cell is part of a data item" Case Is = xlTableBody MsgBox "Active cell is part of the table body" End Select

Propiedad Range.Name Devuelve o establece un valor de tipo Variant que representa el nombre del objeto.

Sintaxis

expresión .Name


Observaciones El nombre de un objeto Range es un objeto Name.

Ejemplo

Propiedad Range.Offset

Crea un nombre a Sheet2!$A$1:$F$50

Devuelve un objeto Range que representa un rango desplazado con respecto al rango especificado.

Sub MakeName() ActiveWorkbook.Names.Add Name:="PTable", _ RefersTo:="Sheet2!$A$1:$F$50" End Sub

Sintaxis

expresión .Offset(RowOffset, ColumnOffset) expresión Variable que representa un objeto Range.

Crea un nombre empleando la selección activa como referencia. Sub MakeName_Selection()

Parámetros Nombre

Necesario/Opcional

Descripción

RowOffset

Opcional

Número de filas (positivo, negativo o cero) que se va a desplazar el rango. Los valores positivos desplazan hacia abajo y los negativos, hacia arriba. El valor predeterminado es 0.

ColumnOffset

Opcional

Número de columnas (positivo, negativo o cero) que se va a desplazar el rango. Los valores positivos desplazan hacia la derecha y los negativos, hacia la izquierda. El valor predeterminado es 0.

Sheets("Sheet2").Activate Range("A1").CurrentRegion.Select ActiveWorkbook.Names.Add Name:="PTable", _ RefersTo:=Selection End Sub

Propiedad Range.Next (Excel) Devuelve un objeto Range que representa la celda siguiente.

Sintaxis

expresión .Next


Observaciones Si el objeto es un rango, esta propiedad emula a la tecla TAB, aunque la propiedad devuelve la celda siguiente sin seleccionarla. En una hoja protegida, esta propiedad devuelve la siguiente celda desbloqueada. En una hoja desprotegida, siempre devuelve la celda situada inmediatamente a la derecha de la celda especificada.

Ejemplo En este ejemplo se activa la celda situada tres columnas a la derecha y tres columnas más abajo que la celda actual de Sheet1. Worksheets("Sheet1").Activate ActiveCell.Offset(rowOffset:=3, columnOffset:=3).Activate

En este ejemplo se supone que Sheet1 contiene una tabla con una fila de encabezado. El ejemplo selecciona la tabla sin seleccionar la fila de encabezado. Debe haber una celda activada en algún lugar de la tabla antes de ejecutar el ejemplo.

Este ejemplo elimina los dos saltos de página establecidos en los ejemplos anteriores. Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakNone Worksheets("Sheet1").Columns("J").PageBreak = xlNone

Set tbl = ActiveCell.CurrentRegion tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _ tbl.Columns.Count).Select

Propiedad Range.Parent (Excel) Devuelve el objeto primario del objeto especificado. Sólo lectura.

Propiedad Range.PageBreak

Sintaxis

Devuelve o establece la ubicación de un salto de página. Puede ser una de las siguientes constantes XlPageBreak:  xlPageBreakAutomatic, xlPageBreakManual o xlPageBreakNone.

Ejemplo

Sintaxis

expresión .PageBreak expresión Variable que representa un objeto Range.

Observaciones Esta propiedad puede devolver la ubicación de un salto de página manual o automático, pero sólo puede asignar la ubicación de los saltos de página manuales (sólo puede establecerse como xlPageBreakManual o como xlPageBreakNone). Para eliminar todos los saltos de página manuales de una hoja de cálculo, defina Cells.PageBreak en xlPageBreakNone.

Ejemplo Este ejemplo establece un salto de página manual encima de la fila 25 de Sheet1. Worksheets("Sheet1").Rows(25).PageBreak = xlPageBreakManual

Este ejemplo establece un salto de página manual a la izquierda de la columna J de Sheet1. Worksheets("Sheet1").Columns("J").PageBreak = _ xlPageBreakManual

expresión .Parent expresión Variable que representa un objeto Range.

En este ejemplo se muestra el nombre del objeto primario de una propiedad de documento. Debe pasar un objeto DocumentProperty válido al procedimiento. Sub DisplayParent(dp as DocumentProperty) MsgBox dp.Parent.Name End Sub

Propiedad Range.PivotCell (Excel) Devuelve un objeto PivotCell que representa una celda de un informe de tabla dinámica.

Sintaxis

expresión .PivotCell expresión Variable que representa un objeto Range.

Ejemplo

Propiedad Range.PivotItem

Este ejemplo determina el nombre de la tabla dinámica en la que se encuentra el objeto PivotCell y se lo comunica al usuario. Este ejemplo supone que existe una tabla dinámica en la hoja de cálculo activa, y que la celda A3 se encuentra en la tabla dinámica.

Devuelve un objeto PivotItem que representa el elemento de tabla dinámica que contiene la esquina superior izquierda del rango especificado.

Sintaxis

Sub CheckPivotCell()

expresión .PivotItem expresión Variable que representa un objeto Range.

Application.Range("A3").PivotCell.Parent

Ejemplo

End Sub

Propiedad Range.PivotField Devuelve un objeto PivotField que representa el campo de tabla dinámica que contiene la esquina superior izquierda del rango especificado.

Sintaxis

expresión .PivotField expresión Variable que representa un objeto Range.

Ejemplo En este ejemplo se muestra el nombre del campo de tabla dinámica que contiene la celda activa.

En este ejemplo se muestra el nombre del elemento de tabla dinámica que contiene la celda activa de Sheet1. Worksheets("Sheet1").Activate MsgBox "The active cell is in the item " & _ ActiveCell.PivotItem.Name

Propiedad Range.PivotTable (Excel) Devuelve un objeto PivotTable que representa el informe de tabla dinámica que contiene la esquina superior izquierda del rango especificado.

Sintaxis

expresión .PivotTable expresión Variable que representa un objeto Range.

Worksheets("Sheet1").Activate MsgBox "The active cell is in the field " & _ ActiveCell.PivotField.Name

Ejemplo En este ejemplo se establece la página actual del informe de tabla dinámica de Sheet1 como la página denominada "Canada". Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable pvtTable.PivotFields("Country").CurrentPage = "Canada"

En este ejemplo se determina el informe de tabla dinámica asociado con el gráfico Sales de la hoja de cálculo activa y, a continuación, se establece la página denominada "Oregon" como página actual del informe de tabla dinámica. Set objPT = _ ActiveSheet.Charts("Sales").PivotLayout.PivotTable objPT.PivotFields("State").CurrentPageName = "Oregon"

Propiedad Range.Previous (Excel)

Propiedad Range.Range

Devuelve un objeto Range que representa la celda siguiente.

Devuelve un objeto Range que representa una celda o un rango de celdas.

Sintaxis

Sintaxis

expresión .Previous expresión Variable que representa un objeto Range.

expresión .Range(Cell1, Cell2) expresión Variable que representa un objeto Range.

Parámetros

Observaciones Si el objeto es un rango, esta propiedad emula la acción de presionar las teclas MAYÚS+TAB; no obstante, a diferencia de la combinación de teclas, la propiedad devuelve la celda anterior sin seleccionarla. En una hoja protegida, esta propiedad devuelve la celda anterior no bloqueada. En una hoja desprotegida, la propiedad siempre devuelve la celda situada inmediatamente a la izquierda de la celda especificada.

Nombre

Descripción

Cell1

Obligatorio

Nombre del rango. Debe ser una referencia de estilo A1 en el lenguaje de la macro. Puede incluir un operador de rango (dos puntos), de intersección (espacio) o de unión (coma). Admite signos de dólar, pero no se tendrán en cuenta. Se puede usar un nombre local definido en cualquier parte del rango. Si se usa un nombre, se supone que está en el lenguaje de la macro.

Cell2

Opcional

Celda de las esquinas superior izquierda e inferior derecha del rango. Puede ser un objeto Range que contiene una sola celda, una columna completa o una fila completa; o bien una cadena que hace referencia a una sola celda en el lenguaje de la macro.

Ejemplo En este ejemplo se selecciona la celda anterior no bloqueada de Sheet1. Si Sheet1 no está protegida, será la celda situada inmediatamente a la izquierda de la celda activa. Worksheets("Sheet1").Activate ActiveCell.Previous.Select

Observaciones Si no hace referencia a ningún objeto, esta propiedad es una abreviatura de ActiveSheet.Range (devuelve un rango de la hoja activa; si la hoja activa no es una hoja de cálculo, la propiedad no funciona). Cuando se aplica a un objeto Range, los valores de la propiedad son relativos al objeto Range. Por ejemplo, si la celda C3 está seleccionada, Selection.Range("B1") devuelve la celda D3 porque es el valor relativo al objeto Range devuelto por la propiedad Selection. Por otro lado, el código ActiveSheet.Range("B1") siempre devuelve la celda B1.

Ejemplo En este ejemplo se establece el valor de la celda A1 de Sheet1 como 3.14159.

En este ejemplo se establece en cursiva el estilo de fuente de las celdas A1:C5 de Sheet1.El ejemplo usa la sintaxis 2 de la propiedad Range. Worksheets("Sheet1").Range(Cells(1, 1), Cells(5, 3))._ Font.Italic = True

Propiedad Range.Resize (Excel) Cambia el tamaño del rango especificado. Devuelve un objeto Range que representa el rango al que se ha cambiado de tamaño.

Sintaxis

expresión Expresión que devuelve un objeto Range.

Worksheets("Sheet1").Range("A1").Value = 3.14159

En este ejemplo se crea una fórmula en la celda A1 de Sheet1. Worksheets("Sheet1").Range("A1").Formula = "=10*RAND()"

En este ejemplo se ejecuta un bucle sobre las celdas A1:D10 de Sheet1. Si una de las celdas tiene un valor menor que 0.001, el código sustituye el valor por 0.

expresión .Resize(RowSize, ColumnSize)

Parámetros Nombre

Descripción

RowSize

Opcional

Número de filas en el nuevo rango. Si este argumento se omite, el número de filas del rango no cambiará.

ColumnSize

Opcional

Número de columnas del nuevo rango. Si este argumento se omite, el número de columnas del rango no cambiará.

For Each c in Worksheets("Sheet1").Range("A1:D10") If c.Value < .001 Then c.Value = 0 End If Next c

En este ejemplo se ejecuta un bucle en el rango denominado "TestRange" y se muestra el número de celdas vacías del rango. numBlanks = 0 For Each c In Range("TestRange")

Ejemplo Este ejemplo cambia el tamaño de la selección de Sheet1 para ampliarla una fila y una columna.

If c.Value = "" Then

Worksheets("Sheet1").Activate

numBlanks = numBlanks + 1

numRows = Selection.Rows.Count

End If

numColumns = Selection.Columns.Count

Next c MsgBox "There are " & numBlanks & " empty cells in this range"

Selection.Resize(numRows + 1, numColumns + 1).Select

En este ejemplo se supone que Sheet1 contiene una tabla con una fila de encabezado.El ejemplo selecciona la tabla sin seleccionar la fila de encabezado.Para poder ejecutar el ejemplo, la celda activa debe estar en la tabla.

Propiedad Range.Rows (Excel)

Set tbl = ActiveCell.CurrentRegion

Sintaxis

tbl.Offset(1, 0).Resize(tbl.Rows.Count - 1, _

Devuelve un objeto Range que representa las filas del rango especificado. Objeto Range de solo lectura. expresión .Rows expresión Variable que representa un objeto Range.

tbl.Columns.Count).Select

Observaciones

Propiedad Range.Row Devuelve el número de la primera fila de la primera área del rango. Long de solo lectura.

Sintaxis

expresión .Row expresión Variable que representa un objeto Range.

Ejemplo Este ejemplo establece en 4 puntos el alto de las filas alternativas de Sheet1.

El uso de esta propiedad sin un calificador de objeto equivale a usar ActiveSheet.Rows. Si se aplica a un objeto Range que es una selección de varias áreas, la propiedad únicamente devuelve las filas de la primera área del rango. Por ejemplo, si el objeto Range tiene dos áreas, A1:B2 y C3:D4,Selection.Rows.Count devuelve 2, no 4. Si desea utilizar esta propiedad en un rango que puede contener una selección múltiple, compruebe Areas.Count para determinar si el rango es una selección múltiple. En ese caso, ejecute un bucle sobre cada área del rango, tal como se muestra en el tercer ejemplo.

For Each rw In Worksheets("Sheet1").Rows If rw.Row Mod 2 = 0 Then rw.RowHeight = 4 End If Next rw

Ejemplo En este ejemplo se elimina la fila tres de Sheet1. Worksheets("Sheet1").Rows(3).Delete

En este ejemplo se eliminan las filas de la región actual de la hoja de cálculo uno en las que el valor de la celda uno de la fila es el mismo que el valor de la celda uno de la fila anterior. For Each rw In Worksheets(1).Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = last Then rw.Delete last = this Next

En este ejemplo se muestra el número de filas de la selección de Sheet1.Si se ha seleccionado más de un área, el ejemplo ejecuta un bucle en cada área. Worksheets("Sheet1").Activate areaCount = Selection.Areas.Count

Observaciones Al definir un rango de celdas con el contenido de un archivo de hoja de cálculo XML, sólo se usan los valores de la primera hoja del libro. No se puede definir un rango de celdas no contiguas en formato de hoja de cálculo XML.

If areaCount <= 1 Then MsgBox "The selection contains " & _ Selection.Rows.Count & " rows."

Ejemplo En este ejemplo se establece el valor de la celda A1 de Sheet1 como 3.14159.

Else

Worksheets("Sheet1").Range("A1").Value = 3.14159

i = 1 For Each a In Selection.Areas MsgBox "Area " & i & " of the selection contains " & _ a.Rows.Count & " rows."

En este ejemplo se ejecuta un bucle sobre las celdas A1:D10 de Sheet1.Si una de las celdas tiene un valor menor que 0.001 el código sustituye el valor por 0. For Each c in Worksheets("Sheet1").Range("A1:D10")

i = i + 1

If c.Value < .001 Then

Next a

c.Value = 0

End If

End If Next c

Propiedad Range.Value Devuelve o establece un valor de tipo Variant que representa el valor del rango especificado.

Propiedad Range.Value2 (Excel)

Sintaxis

Sintaxis

expresión .Value(RangeValueDataType)


Parámetros Nombre RangeValueDataType

Descripción Opcional

Tipo de datos del valor del rango. Puede ser una constante xlRangeValueDataType.

Devuelve o establece el valor de la celda. Variant de lectura y escritura. expresión .Value2 expresión Variable que representa un objeto Range.

Observaciones La única diferencia que hay entre esta propiedad y la propiedad Value es que Value2 no utiliza los tipos de datos Currency y Date. Puede devolver valores que tengan el formato de estos tipos de datos como números de punto flotante usando el tipo de datos Double.

Ejemplo

Propiedad Range.Worksheet (Excel)

En este ejemplo se utiliza la propiedad Value2 para sumar los valores de dos celdas.

Devuelve un objeto Worksheet que representa la hoja de cálculo que contiene el rango especificado. Solo lectura.

Range("a1").Value2 = Range("b1").Value2 + Range("c1").Value2

Sintaxis


Propiedad Range.Width (Excel) Devuelve un valor de tipo Variant que representa el ancho, en unidades, del rango.

Sintaxis

expresión .Width expresión Variable que representa un objeto Range.

Ejemplo

expresión .Worksheet

Ejemplo Este ejemplo muestra el nombre de la hoja de cálculo que contiene la celda activa. El ejemplo debe ejecutarse desde una hoja de cálculo. MsgBox ActiveCell.Worksheet.Name

Sub CentrarImagen()

Este ejemplo muestra el nombre de la hoja de cálculo que contiene el rango denominado "testRange".

Dim Fotos As Object

MsgBox Range("testRange").Worksheet.Name

Dim celda As Range Set celda = Cells(5, "F") Set Fotos = ActiveSheet.Shapes.Range(Array("1 Picture")) 'asignamos un Ancho a la columna de la celda F5, proporcianada al ancho de la Imagen celda.ColumnWidth = Fotos.Width / 10.67111364 With Fotos .Top = celda.Top 'reducimos el alto y ancho de la foto .Width = .Width / 1.5 .Height = .Height / 1.5 'se centra horizontalmente en la celda indicada .Left = celda.Left + (celda.Width - Fotos.Width) / 2 'damos altura a la fila igual al alto final de la imagen celda.EntireRow.RowHeight = .Height End With End Sub

Application.Windows A collection of all the Window objects in Microsoft Excel.

Remarks The Windows collection for the Application object contains all the windows in the application, whereas the Windowscollection for the Workbook object contains only the windows in the specified workbook.

Example Use the Windows property to return the Windows collection. The following example cascades all the windows that are currently displayed in Microsoft Excel. Windows.Arrange arrangeStyle:=xlCascade

Use the NewWindow method to create a new window and add it to the collection. The following example creates a new window for the active workbook. ActiveWorkbook.NewWindow

Use Windows(index), where index is the window name or index number, to return a single Window object. The following example maximizes the active window. Note that the active window is always Windows(1). Windows(1).WindowState = xlMaximized

Methods

Properties

Name

Description

Name

Description

Arrange

Arranges the windows on the screen.

Application

BreakSideBySide

Ends side-by-side mode if two windows are in side-by-side mode. Returns a Boolean value that represents whether the method was successful.

CompareSideBySideWith

Opens two windows in side-by-side mode. Returns a Boolean value.

ResetPositionsSideBySide

Resets the position of two worksheet windows that are being compared side by side.

When used without an object qualifier, this property returns an Application object that represents the Microsoft Excel application. When used with an object qualifier, this property returns an Application object that represents the creator of the specified object (you can use this property with an OLE Automation object to return the application of that object). Readonly.

Count


Creator


Item


Parent


SyncScrollingSideBySide

True enables scrolling the contents of windows at the same time when documents are being compared side by side. False disables scrolling the windows at the same time.

Propiedad Window.ActiveCell (Excel) Devuelve un objeto Range que representa la celda activa de la ventana activa (la ventana superior) o de la ventana especificada. Si la ventana no contiene una hoja de cálculo, esta propiedad devuelve un error. Sólo lectura.

Sintaxis

expresión .ActiveCell expresión Variable que representa un objeto Window.

.Italic = True End With

Propiedad Window.Caption (Excel) Devuelve o establece un valor Variant que representa el nombre que aparece en la barra de título de la ventana del documento.

Sintaxis

expresión Variable que representa un objeto Window.

Observaciones Si no se especifica un calificador de objeto, esta propiedad devuelve la celda activa de la ventana activa. Celda activa no es lo mismo que selección. La celda activa es una sola celda de la selección actual. La selección puede contener más de una celda, pero sólo una es la celda activa. Todas las expresiones siguientes devuelven la celda activa y son equivalentes: ActiveCell Application.ActiveCell ActiveWindow.ActiveCell Application.ActiveWindow.ActiveCell

Ejemplo En este ejemplo se usa un cuadro de mensaje para mostrar el valor de la celda activa. Puesto que la propiedad ActiveCell produce un error si la hoja activa no es una hoja de cálculo, el ejemplo activa Sheet1 antes de usar la propiedad ActiveCell. Worksheets("Sheet1").Activate MsgBox ActiveCell.Value

En este ejemplo se cambia el formato de fuente de la celda activa.

expresión .Caption

Observaciones Cuando establece el nombre, puede usarlo como colección Windows (como se muestra en el ejemplo).

.Bold = True

para

la

Ejemplo En este ejemplo se establece "Consolidate Balance Sheet" como nombre de la primera ventana del libro activo. Después, dicho nombre se usa como índice de esa ventana en la colección Windows. ActiveWorkbook.Windows(1).Caption

=

"Consolidated

Sheet" ActiveWorkbook.Windows("Consolidated Balance Sheet") _ .ActiveSheet.Calculate

Propiedad Window.Selection (Excel) Devuelve la ventana correspondiente a un objeto Windows.

Sintaxis

expresión .Selection expresión Variable que representa un objeto Window.

Worksheets("Sheet1").Activate With ActiveCell.Font

índice

Observaciones

Balance

El tipo de objeto que se devuelve depende de la selección actual (por ejemplo, si está seleccionada una celda, la propiedad devuelve un objeto Range). Si no hay nada seleccionado, la propiedad Selectiondevuelve Nothing. El uso de esta propiedad sin un calificador de objeto equivale a usar Application.Selection.

Propiedad Window.RangeSelection Devuelve un objeto Range que representa las celdas seleccionadas en la hoja de cálculo en la ventana especificada incluso si un objeto gráfico está activa o seleccionada en la hoja de cálculo. Solo lectura.

Sintaxis

expresión Variable que representa un objeto Window.

Ejemplo En este ejemplo se borra la selección de Sheet1 (siempre que la selección sea un rango de celdas). Worksheets("Sheet1").Activate Selection.Clear

En este ejemplo se muestra el tipo de objeto de Visual Basic de la selección. Worksheets("Sheet1").Activate MsgBox "The selection object type is " & TypeName(Selection)

expresión .RangeSelection

Observaciones Si un objeto gráfico está seleccionado en una hoja de cálculo, la propiedad Selection devuelve el objeto gráfico en lugar de un objeto Range, y la propiedad RangeSelection devolverá el rango de celdas que estaba seleccionado antes de seleccionar el objeto. Esta propiedad y la propiedad Selection devuelven valores idénticos cuando se selecciona un rango (no un objeto gráfico) en la hoja de cálculo. Si la hoja activa de la ventana especificada no es una hoja de cálculo, esta propiedad devolverá un error.

Ejemplo Este ejemplo muestra la dirección de las celdas seleccionadas en la hoja de cálculo de la ventana activa. MsgBox ActiveWindow.RangeSelection.Address

Propiedad Window.ActiveSheet Devuelve un objeto que representa la hoja activa (la hoja en primer plano) del libro activo o de la ventana o el libro especificado. Devuelve Nothing si no hay ninguna hoja activa.

Sintaxis expresión .ActiveSheet expresión Variable que representa un objeto Window.

Observaciones Si no se especifica un calificador de objeto, la propiedad devuelve la hoja activa del libro activo. Si un libro aparece en más de una ventana, es posible que la propiedad ActiveSheet tenga un valor diferente en cada ventana.

Ejemplo En este ejemplo se muestra el nombre de la hoja activa. MsgBox "The name of the active sheet is " & ActiveSheet.Name

Areas Objeto Colección de áreas, o bloques de celdas contiguas, de una selección.

Remarks There’s no singular Area object; individual members of the Areas collection are Range objects. The Areas collection contains one Range object for each discrete, contiguous range of cells within the selection. If the selection contains only one area, the Areas collection contains a single Range object that corresponds to that selection.

Example Use the Areas property to return the Areas collection. The following example clears the current selection if it contains more than one area. If Selection.Areas.Count <> 1 Then Selection.Clear

Use Areas(index), where index is the area index number, to return a single Range object from the collection. The index numbers correspond to the order in which the areas were selected. The following example clears the first area in the current selection if the selection contains more than one area. If Selection.Areas.Count <> 1 Then Selection.Areas(1).Clear End If

Some operations cannot be performed on more than one area in a selection at the same time; you must loop through the individual areas in the selection and perform the operations on each area separately. The following example performs the operation named "myOperation" on the selected range if the selection contains only one area; if the selection contains multiple areas, the example performs myOperation on each individual area in the selection. Set rangeToUse = Selection If rangeToUse.Areas.Count = 1 Then myOperation rangeToUse Else For Each singleArea in rangeToUse.Areas myOperation singleArea Next End If

Propiedades Nombre

Descripción

Application

Cuando se usa sin un calificador de objeto, esta propiedad devuelve un objeto Application que representa la aplicación de Microsoft Excel. Cuando se usa con un calificador de objeto, esta propiedad devuelve un objeto Application que representa al creador del objeto especificado (puede usar esta propiedad con un objeto de automatización OLE para devolver la aplicación de dicho objeto). Solo lectura.

Count

Devuelve un valor Long que representa el número de objetos de la colección.

Creator

Devuelve un entero de 32 bits que indica la aplicación en el que se creó el objeto. Readonly largo.

Item

Devuelve un solo objeto de una colección.

Parent

Devuelve el objeto primario especificado. Solo lectura.

del

objeto

Application.Rows y Application.Columns Returns a Range object that represents all the rows on the active worksheet. If the active document isn’t a worksheet, the Rows property fails. Read-only Range object. Returns a Range object that represents all the columns on the active worksheet. If the active document isn't a worksheet, the Columns property fails.

Syntax expression .Rows expression A variable that represents an Application object.

Remarks Using this property without using ActiveSheet.Rows.

an

object

qualifier

is

equivalent

to

When applied to a Range object that’s a multiple selection, this property returns rows from only the first area of the range. For example, if the Range object has two areas — A1:B2 and C3:D4 — Selection.Rows.Count returns 2, not 4. To use this property on a range that may contain a multiple selection, test Areas.Count to determine whether the range is a multiple selection. If it is, loop over each area in the range, as shown in the third example.

Example This example deletes row three on Sheet1. Worksheets("Sheet1").Rows(3).Delete

This example deletes rows in the current region on worksheet one where the value of cell one in the row is the same as the value in cell one in the previous row. For Each rw In Worksheets(1).Cells(1, 1).CurrentRegion.Rows this = rw.Cells(1, 1).Value If this = last Then rw.Delete last = this Next

This example displays the number of rows in the selection on Sheet1. If more than one area is selected, the example loops through each area. Worksheets("Sheet1").Activate areaCount = Selection.Areas.Count If areaCount <= 1 Then

How to: Refer to Rows and Columns Use the Rows property or the Columns property to work with entire rows or columns. These properties return a Range object that represents a range of cells. In the following example, Rows(1) returns row one on Sheet1. The Bold property of the Font object for the range is then set to True. Sub RowBold() Worksheets("Sheet1").Rows(1).Font.Bold = True End Sub

MsgBox "The selection contains " & _ Selection.Rows.Count & " rows." Else i = 1

The following table illustrates some row and column references using the Rows and Columns properties.

For Each a In Selection.Areas MsgBox "Area " & i & " of the selection contains " & _ a.Rows.Count & " rows."

Reference

Meaning

i = i + 1

Rows(1)

Row one

Rows

All the rows on the worksheet

Columns(1)

Column one

Syntax

Columns("A")

Column one

expression .Columns expression A variable that represents an Application object.

Columns

All the columns on the worksheet

Next a End If

Remarks Using this property without using ActiveSheet.Columns.

an

object

qualifier

is

equivalent

to

When applied to a Range object that's a multiple-area selection, this property returns columns from only the first area of the range. For example, if the Range object has two areas — A1:B2 and C3:D4 — Selection.Columns.Count returns 2, not 4. To use this property on a range that may contain a multiple-area selection, test Areas.Count to determine whether the range contains more than one area. If it does, loop over each area in the range.

To work with several rows or columns at the same time, create an object variable and use the Union method, combining multiple calls to the Rows or Columns property. The following example changes the format of rows one, three, and five on worksheet one in the active workbook to bold. Sub SeveralRows() Worksheets("Sheet1").Activate Dim myUnion As Range Set myUnion = Union(Rows(1), Rows(3), Rows(5)) myUnion.Font.Bold = True End Sub

This example deletes the empty rows from a selected range.

This example deletes the empty columns from a selected range.

Sub Delete_Empty_Rows() 'The range from which to delete the rows. Dim rnSelection As Range

Sub Delete_Empty_Columns() 'The range from which to delete the columns. Dim rnSelection As Range

'Row and count variables used in the deletion process. Dim lnLastRow As Long Dim lnRowCount As Long Dim lnDeletedRows As Long

'Column and count variables used in the deletion process. Dim lnLastColumn As Long Dim lnColumnCount As Long Dim lnDeletedColumns As Long

'Initialize the number of deleted rows. lnDeletedRows = 0

lnDeletedColumns = 0

'Confirm that a range is selected, contiguous. If TypeName(Selection) = "Range" Then If Selection.Areas.Count = 1 Then

and

that

the

range

is

'Initialize the range to what the user has selected, and initialize the count for the upcoming FOR loop. Set rnSelection = Application.Selection lnLastRow = rnSelection.Rows.Count 'Start at the bottom row and work up: if the row is empty then 'delete the row and increment the deleted row count. For lnRowCount = lnLastRow To 1 Step -1 If Application.CountA(rnSelection.Rows(lnRowCount)) = 0 Then rnSelection.Rows(lnRowCount).Delete lnDeletedRows = lnDeletedRows + 1 End If Next lnRowCount rnSelection.Resize(lnLastRow - lnDeletedRows).Select Else MsgBox "Please select only one area.", vbInformation End If Else MsgBox "Please select a range.", vbInformation End If 'Turn screen updating back on. Application.ScreenUpdating = True

'Confirm that a range is selected, contiguous. If TypeName(Selection) = "Range" Then If Selection.Areas.Count = 1 Then

and

that

the

range

is

'Initialize the range to what the user has selected, and initialize the count for the upcoming FOR loop. Set rnSelection = Application.Selection lnLastColumn = rnSelection.Columns.Count 'Start at the far-right column and work left: if the column is empty then 'delete the column and increment the deleted column count. For lnColumnCount = lnLastColumn To 1 Step -1 If Application.CountA(rnSelection.Columns(lnColumnCount)) = 0 Then rnSelection.Columns(lnColumnCount).Delete lnDeletedColumns = lnDeletedColumns + 1 End If Next lnColumnCount rnSelection.Resize(lnLastColumn lnDeletedColumns).Select Else MsgBox "Please select only one area.", vbInformation End If Else MsgBox "Please select a range.", vbInformation End If 'Turn screen updating back on. Application.ScreenUpdating = True

End Sub End Sub

-

Application.Cells

Syntax expression .Cells expression A variable that represents an Application object.

Returns a Range object that represents all the cells on the active worksheet. If the active document is not a worksheet, this property fails.

Remarks Because the Item property is the default property for the Range object, you can specify the row and column index immediately after the Cells keyword. For more information, see the Item property and the examples for this topic. Using this property without an object qualifier returns a Range object that represents all the cells on the active worksheet.

Example This example looks at data in each row and inserts a blank row each time the value in column A changes. Sub ChangeInsertRows() Application.ScreenUpdating = False Dim xRow As Long For xRow = Application.Cells(Rows.Count, 1).End(xlUp).Row To 3 Step -1 If Application.Cells(xRow, 1).Value <> Application.Cells(xRow - 1, 1).Value Then Rows(xRow).Resize(1).Insert Next xRow Application.ScreenUpdating = True End Sub

How to: Refer to All the Cells on the Worksheet Sub ClearSheet() Worksheets("Sheet1").Cells.ClearContents End Sub

Looping Through a Range of Cells One way to loop through a range is to use the For...Next loop with the Cells property. You can substitute the loop counter (or other variables or expressions) for the cell index numbers. In the following example, the variable counter is substituted for the row index. The procedure loops through the range C1:C20, setting to 0 (zero) any number whose absolute value is less than 0.01. Sub RoundToZero1() For Counter = 1 To 20 Set curCell = Worksheets("Sheet1").Cells(Counter, 3) If Abs(curCell.Value) < 0.01 Then curCell.Value = 0 Next Counter End Sub

Another easy way to loop through a range is to use a For Each...Next loop with the collection of cells specified in the Range property. Visual Basic automatically sets an object variable for the next cell each time the loop runs. The following procedure loops through the range A1:D10, setting to 0 (zero) any number whose absolute value is less than 0.01. Sub RoundToZero2() For Each c In Worksheets("Sheet1").Range("A1:D10").Cells If Abs(c.Value) < 0.01 Then c.Value = 0 Next End Sub

If you do not know the boundaries of the range you want to loop through, you can use the CurrentRegion property to return the range that surrounds the active cell. For example, the following procedure, when run from a worksheet, loops through the range that surrounds the active cell, setting to 0 (zero) any number whose absolute value is less than 0.01. Sub RoundToZero3() For Each c In ActiveCell.CurrentRegion.Cells If Abs(c.Value) < 0.01 Then c.Value = 0 Next End Sub

Comments Collection

Methods Name

A collection of cell comments.

Item

Description Returns a single object from a collection.

Properties Name

Description

Application


Count


Creator


Parent


Remarks Each comment is represented by a Comment object.

Example Use the Comments property to return the Comments collection. The following example hides all the comments on worksheet one. Set cmt = Worksheets(1).Comments For Each c In cmt c.Visible = False Next

Use the AddComment method to add a comment to a range. The following example adds a comment to cell E5 on worksheet one. With Worksheets(1).Range("e5").AddComment .Visible = False .Text "reviewed on " & Date End With

Use Comments(index), where index is the comment number, to return a single comment from the Comments collection. The following example hides comment two on worksheet one. Worksheets(1).Comments(2).Visible = False

Comment Object

Represents a cell comment.

Comment Object Remarks The Comment object is a member of the Comments collection.

Example Use the Comment property to return a Comment object. The following example changes the text in the comment in cell E5. Worksheets(1).Range("E5").Comment.Text "reviewed on " & Date

Use Comments(index), where index is the comment number, to return a single comment from the Comments collection. The following example hides comment two on worksheet one. Worksheets(1).Comments(2).Visible = False

Use the AddComment method to add a comment to a range. The following example adds a comment to cell E5 on worksheet one. With Worksheets(1).Range("e5").AddComment .Visible = False .Text "reviewed on " & Date End With

Methods

Properties

Name

Name

Description

Delete

Deletes the object.

Next

Returns a Comment object that represents the next comment.

Previous

Returns a Comment object that represents the previous comment.

Text

Sets comment text.

Description

Application


Author

Returns or sets the author of the comment. Read-only String.

Creator


Parent


Shape

Returns a Shape object that represents the shape attached to the specified comment.

Visible

Returns or sets a Boolean value that determines whether the object is visible. Read/write.

Manual de Referencia VBA Excel

Recommend Documents