Excel VBA Macro Programming

This object communicates with a range of cells or individual cells and makes changes in them. Without this object, you cannot change multiple cells on a worksheet at one time.

Main Properties, Methods , and Collections

These are the main properties, methods, and collections you will use within the Range object.

Activate

This method activates a particular cell or range of cells to make them into the active cell or cells. For this to work, Sheet1 has to be the active worksheet:

Worksheets("sheet1").Range("a1").Activate

This only activates one cell, even if you enter a range such as a1.b10.

AddComment

This method allows you to add a comment into the cell defined by the range:

Worksheets("sheet1").Range("a1").AddComment ("MyComment")

If you try to add the comment to a cell that already has a comment, you will get an error message. If you enter a range of cells instead of a single cell reference, you will get an error. To edit the comment, you need to reference the comment within the Comments collection. See Chapter 22 to learn how this is done.

Address

This very important property gives you the address of a range ‚ for example, the range the user selected. You can use this property with the earlier examples.

MsgBox Worksheets("sheet2").Range("a3").Address

This will return $A$3.

BorderAround

This method draws a border around a group of cells or a single cell.

Worksheets("sheet2").Range("a3.b10").BorderAround (1)

This will put a single-line border around the range a3.b10 on sheet2 of the workbook. By changing the parameter number, you can draw other border types.

Calculate

This method calculates a particular range specified, assuming that autocalculation is not set to On for the workbook.

Worksheets("sheet2").Range("a3.d12").Calculate

You will see this being used as part of an application in Chapter 23.

Cells

This is a collection of cells within the range specified. You can find out how many cells are within the range, for example,

MsgBox Worksheets("sheet2").Range("a3.d12").Cells.Count

This will display 40.

CheckSpelling

This method checks the spelling in an individual range just as you can within a worksheet.

Worksheets("sheet2").Range("a3.d12").CheckSpelling

You can add optional parameters for Custom Dictionary , Ignore Uppercase , Always Suggest , and so on.

Clear

This method clears the contents of the range of cells. Bear in mind that it clears everything including comments and formats.

Worksheets("sheet2").Range("a3.d12").Clear

ClearComments

This method clears comments only from the range of cells specified.

Worksheets("sheet2").Range("a3.d12").ClearComments

ClearContents

This method clears the contents of a cell or range of cells only ‚ that is, the actual data that was typed in. It does not clear the format or the borders.

Worksheets("sheet2").Range("a3.d12").ClearContents

ClearFormats

This method clears the format of a range of cells.

Worksheets("sheet2").Range("a3.d12").ClearFormats

Column and Row

These properties return the first column number or the first column row within the range defined.

MsgBox Worksheets("sheet2").Range("b3.d12").Column

This will return 2 because B, which is the first column of the range, is column 2.

MsgBox Worksheets("sheet2").Range("b3.d12").Row

This returns 3 because the first reference of the range is B3, which is row 3.

Columns and Rows

These collections work in a similar way to the Column and Row properties just discussed, but return the actual number of columns and rows within the specified range. This is useful if you want to use a For..Next loop to work through each cell within the range:

MsgBox Worksheets("sheet2").Range("b3.d12").Columns.Count

This displays 3, which is the number of columns within the range.

MsgBox Worksheets("sheet2").Range("b3.d12").Rows.Count

This displays 10, which is the number of rows within the selected range.

ColumnWidth and RowWidth

These properties return or set the width of columns or the height of rows within the range specified:

Worksheets("sheet2").Range("b3.d12").ColumnWidth = 4 Worksheets("sheet2").Range("b3.d12").RowHeight = 10

Copy and PasteSpecial

These useful methods copy and paste a range of cells. You will see how they work in Chapter 21.

Worksheets("sheet2").Range("f19.g20").Copy Worksheets("sheet2").Range("h19").PasteSpecial

This takes the cells at range F19.G20 on Sheet2 and pastes them to range H19 on Sheet2.

PasteSpecial allows the use of optional parameters that define whether you want to paste only values or formats:

Worksheets("sheet2").Range("h19").PasteSpecial Type:=xlPasteValues

PrintOut and PrintPreview

When used with the Range object, these methods will allow print preview and printout of the specified range only.

Worksheets("sheet2").Range("f19.g20").PrintPreview Worksheets("sheet2").Range("f19.g20").PrintOut

Replace

A useful method that does exactly what it says and replaces a specified character found within the range with another one:

Worksheets("sheet2").Range("f19.g20").Replace "a", "b"

This replaces all the lowercase a 's with lowercase b 's.

Select

This important method allows you to select a range of cells in code:

Worksheets("sheet2").Range("f19.g20").Select

This selects the range F19 to G20 just as if you had dragged the cursor across the range yourself.

Text

This property returns the text in the cell of the range. The range can only be a single cell; otherwise , you will get an error. The name Text is not strictly true because it can also return a number as a text string. You cannot write back to this property in order to put a value into a cell ‚ you need to use the Value property to do this.

MsgBox Worksheets("sheet2").Range("f26").Text

Value

This property is similar to Text , but it is read/write, so you can write data into a spreadsheet with it:

MsgBox Worksheets("sheet2").Range("f26").Value Worksheets("sheet2").Range("f26").Value = 10

When reading you can only use a single cell value or you will get a Type Mismatch error, but when writing you can set a range of cells to one value:

Worksheets("sheet2").Range("f26.g40").Value = 10

The value of the Text property is also 10, but you cannot write to it.

WrapText

This property sets the WrapText flag for a range of cells. You can also read the value of the property. This sets whether the text will wrap within a cell or stay on one line and spill into the adjacent cells:

Worksheets("sheet2").Range("f26.g40").WrapText = True