Office Object Model: Excel

A range consists of a number of cells that need not necessarily be adjacent.

Core properties of ranges include

value
formula
format

A Range object can be obtained by using one of the two properties application.cells or application.range.

The method application.inputBox allows a user to select a range with the mouse and pass the selected range to a VBA program.

Each of the four edges (borders) of a range is represented by a Border object.

A range's borderAround method draws a border around its area.

An individual cell is referred to by a range.

Methods and properties

`activate()`	Activates a single cell. Use `select` in order to select multiple cells.
`addComment()`	Adds a `comment` object to the range. Compare with the `clearComments()` method and the `comment` property.
`addCommentThreaded()`	Apparently, threaded comments are more modern than comments that are added with `addComment()`
`addIndent`
`address`	returns a textual representation of the range (such as `$E$4:$G$8`)
`addressLocal`
`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.
`allocateChanges()`	Used in edited cells of an OLAP data source to execute an `UPDATE CUBE` statement. Compare with `discardChanges()`
`allowEdit`	Specifies if the range can be edited on a protected worksheet. Compare with `locked`
`application`
`applyNames()`
`applyOutlineStyles()`
`areas`
`autoComplete()`
`autoFill()`	aka flash fill?
`autoFilter()`	TODO: Compare with the `autoFilter` object
`autoFit()`	Compare with `shrinkToFit`
`autoOutline()`
`borderAround()`	Draws a border around (but not inside) the range:
`borders`
`calculate()`	Recalculates the formulas of the range. (There is also `application.calculate()` and `worksheet.calculate()`). Unlike `calculateRowMajorOrder`, `calculate()` takes into account dependencies among cells being calculated.
`calculateRowMajorOrder()`	Calculates the cells in the range left to right, top to bottom, without taking into account dependencies.
`cells`	The collection of the cells in the range.
`characters`	Used to format individual characters of text
`checkSpelling()`	See also `worksheet.checkSpelling`
`clear()`
`clearComments()`	Compare with `addComment()`
`clearContents()`	Clears formulas and data. Compare with `delete()`
`clearFormats()`
`clearHyperlinks()`
`clearNotes()`
`clearOutline()`
`column`, `row`	The column/row number of the range's first area.
`columnDifferences()`, `rowDifferences()`
`columns`, `rows`	A `range` object which can be indexed to get the range of a given column/range of a range: `rng.columns(4)`. The number of columns/rows of a range can be determined with `rng.columns.count` and `rng.rows.count`. Compare with `entireColumn`, `entireRow`.
`columnWidth`, `rowHeight`
`comment`
`commentThreaded`
`consolidate()`	consolidates data from multiple ranges of multiple worksheets into a single worksheet
`convertToLinkedDataType()`	Tries to convert the cells to a linked data type (such as Stocks or Geography)
`copy()`	Copy a range to another range or into the clipboard.
`copyFromRecordset()`	Copies an ADO recordset or a DAO recordset to a range.
`copyPicture()`
`count`
`countLarge`
`createNames()`	automatically creates named ranges from a region's header and row names.
`creator`
`currentArray`	Compare with `hasArray`
`currentRegion`	Returns a (rectangular) range so that adjacent cells's values are blank.
`cut()`	Cuts a range and pastes it to another range or into the clipboard.
`dataSeries()`
`dataTypeToText()`	Converts linked data type to text
`delete()`	Deletes the range and shifts the neighboring content either from the right or from the bottom into the original space the range occupied. Compare with the various `clear…()` methods
`dependents`
`dialogBox()`
`directDependents`	Compare with `showDependents()`
`directPrecedents`	Compare with `showPrecedents()`
`dirty()`	mark the region to be recalculated.
`discardChanges()`	Used in edited cells of an OLAP data source to execute an `ROLLBACK TRANSACTION` statement. Compare with `allocateChanges()`
`displayFormat`
`editionOptions()`
`end`	navigates to the «next» cell in a given direction
`entireColumn`, `entireRow`	These properties return a range that extends the range on which the property is used to the entire column or row. Compare with `columns`, `rows`
`errors`	An `Errors` object. This property can only be retrieved on a single-cell range.
`exportAsFixedFormat()`	Export the data as PDF or XPS. Compare with `printOut()`
`fillDown()`
`fillLeft()`
`fillRight()`
`fillUp()`
`find()`	finds values in a range
`findNext()`	Continues finding values
`findPrevious()`
`flashFill()`
`font`	The range's `font` object which specifies the attributes of the text displayed in the range's cells.
`formatConditions`	returns a collection of `formatCondition` objects with which it is possible to conditionally format certain characteristics of the range (such as background color etc.)
`formula`
`formulaArray`
`formulaHidden`	Compare (but don't confuse) with `hidden`
`formulaLocal`
`formulaR1C1`
`formulaR1C1Local`
`functionWizard()`	Opens the function wizard.
`group()`	Used in Pivot Tables for grouping operations
`hasArray`	Compare with `currentArray`
`hasFormula`
`hasRichDataType`
`height`
`hidden`	Compare (but don't confuse) with `formulaHidden`
`horizontalAlignment`	`xlHAlignLeft`, `xlHAlignRight`, `xlHAlignCenter`, `xlHAlignJustify`, `xlHAlignCenterAcrossSelection`, `xlHAlignDistributed`, `xlHAlignFill` or `xlHAlignGeneral`. Compare with the `font` property.
`hyperlinks`
`ID`
`indentLevel`
`insert()`
`insertIndent()`
`interior`	the range's `interior` object which allows, for example, to set the background color of the range.
`item`
`justify()`
`left`
`linkedDataTypeState`	returns the state of a linked data type (one of `xlLinkedDataTypeStateNone`, `xlLinkedDataTypeStateValidLinkedData`, `xlLinkedDataTypeStateDisambiguationNeeded`, `xlLinkedDataTypeStateBrokenLinkedData`, `xlLinkedDataTypeStateFetchingData`)
`listHeaderRows`	Returns the number of header rows (as a `long`) of the range that this property is applied on.
`listNames()`	This method is useful to quickly get a list of (non-hidden) named ranges: It puts their names in a column and their addresses into the neighboring column. See also the `Name` object
`listObject`
`locationInTable`
`locked`	Compare with `allowEdit`
`MDX`
`merge()`	Creates a merged cell from the specified range. Compare with `unmerge`
`mergeArea`	evaluates to the `range` object that was merged a cell (if the cell is merged, otherwise, returns just the cell)
`mergeCells`	evaluates to `true` if the specified range contains merged cells.
`name`	If the range is named, the `name` property returns a `name` object. Thus, in order to obtain the name of a range as string, the `name` property of the `name` object must be used (i. e. `rng.name.name`).
`navigateArrow()`	Compare with `showDependents()` and `showPrecedents()`
`next`
`noteText()`
`numberFormat`	specifies how the data of a range's underlying cells' data is formatted.
`numberFormatLocal`
`offset`	The location and size of a range can be changed with `.offset(…)` and `.resize(…)`.(See moving ranges around)
`orientation`
`outlineLevel`
`pageBreak`
`parent`
`parse()`	Split the data of the range into multiple «columns». Compare with `textToColumns()`
`pasteSpecial()`
`phonetic`
`phonetics`
`pivotCell`
`pivotField`
`pivotItem`
`pivotTable`
`precedents`
`prefixCharacter`
`previous`
`printOut()`	Compare with `exportAsFixedFormat()`
`printPreview()`
`queryTable`
`range`
`readingOrder`
`removeDuplicates()`	remove duplicate values.
`removeSubtotal()`
`replace()`
`resize`	The location and size of a range can be changed with `.offset(…)` and `.resize(…)` (See moving ranges around)
`run()`	Runs the macro on a macro sheet (not applicable on an «ordinary» worksheet). See also `application.run`
`select()`	Compare with `activate()`
`serverActions`
`setCellDataTypeFromCell()`	Creates another instance of a linked data type that exists in another cell.
`setPhonetic()`	Creates `phonetic` objects
`show()`	Scrolls the active Window so that the (single) cell of the range is visible.
`showCard()`	Used in conjunction with linked data type.
`showDependents()`	Compare with `showPrecedents()`, `directDependents` and `navigateArrow()`
`showDetail`
`showErrors()`	Draw arrows to the source of errors.
`showPrecedents()`	Compare with `showDependents()`, `directPrecedents` and `navigateArrow()`
`shrinkToFit`	A boolean (technically: a variant) that controls if the size of text in cells is shrunk so that fits the cell's available space. Compare with `autoFit`
`sort()`
`sortSpecial()`
`soundNote`
`sparklineGroups`
`speak()`
`specialCells()`	obtain a `range` object that matches cells with a specific data type and/or cell type.
`style`
`subscribeTo()`
`subtotal()`
`summary`
`table()`
`text`
`textToColumns()`	Use the values in a single column to fill multiple columns (for example by splitting them on a character, i. e. CSV - or using fixed widths). Corresponds to Data -> Data Tools -> Text to Columns. Compare with `parse()`
`top`
`ungroup()`
`unMerge()`	Compare with `merge()`
`useStandardHeight`
`useStandardWidth`
`validation`	A `validation` object that allows to limit the possible values that can be entered in a range.
`value`
`value2`
`verticalAlignment`
`width`
`worksheet`	The `worksheet` object in which the range is found.
`wrapText`	Compare to Popup Format Cells -> Alignment -> Text Control -> Wrap text
`xPath`

default member

Without arguments, the range's default member returns the value property.

With arguments, the default member returns the result of item(…), called with the same arguments.

Thus,

rngDest = rngSrc is equivalent to rngDest.value = rngSrc.value,
rng(x) is equivalent to rng.item(x) and
rng(x,y) is equivalent to rng.item(x,y).

Item(…)

The item property is called by the default member if invoked with arguments.

Item() method comes in two forms:

item(arg)
item(arg_1, arg_2)

Referring to other workbooks and/or worksheets

Get the value in the 4th row, 2nd column of the current worksheet:

val = range("b4")

In order to refer to the worksheet named worksheetName, the name is separated from the address with an exclamation mark (!):

val = range("worksheetName!b3")

The name of a workbook is put into square brackets:

val = range("[workbookName.xlsx]worksheetName!a3")

In order to refer to a workbook with full path, single quotes are needed:

val = range("'P:\ath\to\other\[workbookName.xlsx]worksheetName'!a3")

Such single quotes are also advisable when referring to a workbook or worksheet with special characters:

='[foo-bar.xlsm]config'!$D$18

Selecting multiple cells

The following example creates a Range object and uses it to modify some properties in all cells from B3 to E9 (such as assigning a formula or modifying the font):

option explicit

public sub main() ' {

  '
  ' Create range object for 3rd row, 2nd column
  ' to 9th row, 5th column:
  '
    dim rng as range
    set rng = range(    _
       cells(3, 2),     _
       cells(9, 5)      _
    )

  '
  ' Use range object to modify some properties
  ' of all cells that belong to the range:
  '
    rng.formula      = "=rand()"
    rng.numberFormat = "0.000"

    with rng.font
        .name = "Lucida Console"
        .size =  10
    end with

    rng.columns.autoFit

    activeWorkbook.saved = true

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Range/b3-e9.bas

Setting values within the range

A range's value property can be assigned a (one dimension) value in which case all cells of that range will have that value.

If an array is assigned, the values of the array are used to fill the columns of the range.

option explicit

sub main()

  ' Define a range ...
  '
    dim rng as range
    set rng =  range("b2:e4")

  ' and set each cell's value within the
  ' range to the same value:
  '
    rng.value = "*"

  ' Define another range...
  '
    set rng = range("b6:d6")


  ' and set the values of each column in
  ' the range:
  '
    rng.value = array("foo", "bar", "baz")

end sub

Github repository about-MS-Office-object-model, path: /Excel/Range/value.bas

Compare with writing a 2D array to a worksheet and Using WorksheetFunction.transpose to write an array vertically to a worksheet.

Iterating over the cells in a range

The for each statement allows to iterate over the cells in a range:

option explicit

sub main()

  fillData

  dim c as range

  for each c in range("a1:b5")
      debug.print "Value of " & c.address & " = " & c.value
  next c

end sub

sub fillData()

    cells(1, 1) = 17
    cells(2, 1) =  9
    cells(3, 1) = 48
    cells(4, 1) =  6
    cells(5, 1) = 33

    cells(1, 2) = 21
    cells(2, 2) = 14
    cells(3, 2) =  9
    cells(4, 2) = 27
    cells(5, 2) = 39

end sub

Github repository about-MS-Office-object-model, path: /Excel/Range/for-each.bas

Naming a range

A range can be given a hopefully meaningful name by assigning a (string-) value to its .name property.

option explicit

public sub main() ' {

    range("b3:d6").name = "randomNumbers"
    range("e2:f8").name = "foos"

    range("randomNumbers").formula = "=rand()"
    range("randomNumbers").interior.color = rgb(255, 140,  30)

    range("foos").value   = "foo"
    range("foos").interior.color = rgb(145, 175, 255)

    activeWorkbook.saved = true

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Range/name.bas

Such a named range is stored in a name object which is stored in a workbook's names collection. Thus, a named range can alternatively also be created with names.add ….

Names that are defined with range(…).name = "…" are global to a workbook. It is possible, however, to define names for ranges that are local to a worksheet with

activeWorksheet.names.add name := "invoiceNumber", refersToR1C1 := "Sheet1!R5C5"

The name of a range must

start with a letter or underscore
not include a space and certain disallowed characters (such as hyphen, dollar symbol, hash sign etc.)
not conflict with an existing name in the workbook

With Power Query M formula, it is possible to access the values in a named range with Excel.CurrentWorkbook.

In the GUI, a range or cell can be named using the name box.

With Formula-M, the data of a (named) range can be queried with the Excel.CurrentWorkbook() function.

Named ranges of a range's header on row names can be created with range.createNames.

A list of all named ranges can be pasted onto a worksheet with the range.listNames() method.

numberFormat

numberFormat specifies how the data of a range's underlying cells' data is formatted.

Formatting, such as what applied with .numberFormat, can be removed with rng.clearFormats.

Selecting multiple columns

Range(…) can be passed two columns(n) in which case it selects the range between those two columns.

In the following example, the columns 4 through 9 are selected:

option explicit

sub main() ' {

    dim colStart as long
    dim colEnd   as long

    colStart = 4
    colEnd   = 9

    range(columns(colStart), columns(colEnd)).select

    with selection
        .columnWidth    = application.centimetersToPoints(0.1)
        .interior.color = rgb(250, 140, 30)
    end with

end sub ' }

Github repository about-MS-Office-object-model, path: /Excel/Range/select-multiple-columns.bas

autofit

The columns property (defined in application) returns a range object on which autofit can be called. This will set the width of the columns such that the entire text in the columns is visble.

public sub main()

  for c = 1 to 20

    cells(1, c).value = string(c, "*")

    columns(c).autoFit

  next c

  activeWorkbook.saved = true

end sub

Github repository about-MS-Office-object-model, path: /Excel/Range/autofit.bas

In order to set the width of a column to an arbitrary value, columnWidth can be used:

columns(7).columnWidth = 3

autofit might be used in conjunction with usedRange or columns and entireColumn

activeSheet.usedRange.entireColumn.autofit
activeSheet.usedRange.columns.autofit

autofit does not change the width of columns that contain no data.

Create a picture

range(…).copyPicture creates a picture of the given range and copies it into the clipboard. Excel: Save a range as image[This example then pastes the clipboard into a chart and saves it to a file.

clearContents

clearContents deletes the content of a range.

It might be used in conjunction with worksheet.usedRange.

In order to not only remove the data but also the underlying formatting of the cells, clearFormats must be used

noteText()

.noteText(txt) seems to be a shorthand to insert a comment.

cells(4,5).noteText("some comment")

Properties related to the size of a range

A range's .width and .height properties return the width and height of the range in points. These properties are read only.

.rowHeight corresponds to the height, measured in points, of the first row of the range that it is applied on.

.columnWidth corresponds to the width, measured in characters of the default font, of the first(?) column of the range that it is applied on.

.rowHeight and columnWidth are read/write properties.

Office Object Model: Excel - Range