About Ranges

A range in MS-Word is an object that represents a continuous block of text. Because Ranges are objects we have to use the set command to create a new one or to assign a range to a variable. .
dim x,y as Range

Set x=new Range

Set y=Selection

Ranges have two key properties: .start and .end. These are numbers which represent character offsets from the beginning of the file. The Selection (or Application.Selection) is a range variable that represents the currently selected block of text.

Selection.start=5

Selection.end=8

If you do the above assignment in the Immediate window you will hilite the text from the 5th character to just before the 8th.

Word uses the following collections that refer to ranges

Collection / Description
Documents(n) / A collection of all the open documents. Word has a special variable ActiveDocument which represents the document you are currently working with. Each element of Documents is of dataype Document. All documents have a .range property: .start is always 0 and .end is the number of characters in the text.
debug.print Documents(1).range.end
Paragraphs(n) / A collection representing all the paragraphs in a document. Each element of Paragraphs is of datatype Paragraph. Like documents, paragraphs have a .range indicating the character offset whether they begin and end:
debug.print Documents(2).Paragraphs(3).range.start
debug.print Documents(2).Paragraphs(3).range.text
Sentences(n) / A collection representing all the sentences in a document. Each element of Sentences is of datatypeRange(there is no datatype sentence)
debug.print ActiveDocument.sentences(1).text
Words(n) / A collection representing all the words in a document. Each element of Words is of datatypeRange
Debug.print ActiveDocument.print.sentences(1).text
Characters(n) / A collection representing all the single characters in the text. Each element of Characters is also a Range, where the value of .end is always greater than .end

Ranges have interesting properties. The .text property represents the letters contained in the range. When you print out a range its this value that gets printed:

Debug.print selection.text ‘prints out the selected text

Debug.print selection ‘prints out the same thing

The .font property describes the font properties of a selection
selection.font.size=20

ActiveDocument.Paragraphs(3).range.font.color=vbRed

ActiveDocument.Words(17).font.bold=true
You can’t change the start and end of a Document, Paragraph, Sentence or Word (other than by editing the document), but you can change these value in a variable or in the special value Selection.

Dim x as range

X=ActiveDocument.Words(20)

x.end=x.end+20

x.font.italic=true

set ActiveDocument.selection=x

An interesting feature of Ranges is that all ranges have Collections of Paragraphs, Sentences, Words and Characters. Therefore, to determine the number of words, sentences and in the 3rd paragraph
dim x as Range

Set x=ActiveDocument.Paragraphs(3)
debug.print x.Sentences.count, x.Words.count
The text has a very good section on extending and collapsing the Selection range and on cutting and pasting text.

Ranges in Excel

The idea of a Range in Excel is very similar to that of a range in Word. There is a SelectionRange (ActiveWorkbook.Selection). The major difference in Excel is that the smallest unit that a range can refer to is a Cell in a spreadsheet and that Ranges refer to a rectangular block of cells. In Word the smallest unit is a character. Like Word, Excel has a Selection object which has a datatypeRange.

Instead of Documents, Paragraphs, Sentences, Words and Characters, Excel uses: Workbooks, WorkSheets, Rows, Columns and Cells. A Workbook is really the Excel data file and the default template has 3 blank worksheets. The ActiveWorkbook is Excel’s equivalent of an ActiveDocument. The front Worksheet is referenced by the built in variable ActiveSheet (not ActiveWorkSheet – there is no such built in object).

Ranges have the properties .row and .column which refer to the position of the top left cell of a range. All ranges have a Rows and Columns collection – myRange.Rows.count and myRange.Columns.count will tell you how many rows and columns you have in the range.

The .UsedRange property of a worksheet refers to the block of cells starting from first occupied cell at the top left of the worksheet to the last occupied cell at the bottom right. farth. It doesn’t matter if other cells in this range are occupied or not. It can be used to limit operations such as search and setting properties to only those cells that might have values.

Excel has at least 3 ways to refer to a cell or a range of cells

  1. Excel spreadsheets display columns as letters and rows as numbers. We call this A1 notation. Using square brackets we can refer to a single cell or a block of cells
  2. [A1] refers to a single cell
  3. [A1:C3] refers to a block of 9 cells
  4. [A1..C3] is an alternate notation. There is not difference in result
  5. To the left of the formula bar there is a name box which usually shows the location of the currently selected cell or range of cells. By typing a name in this box we can give a name to the current selection, ie: Contacts or NetworkData We can then refer to the current selection by name
  6. [Contacts]
  7. ActiveWorkbook.Names(“Contacts”)
  8. Individual cells can be addressed using the Cells collection which can be thought of as a 2 dimensionals array where the 1st subscript represents rows and the 2nd represents columns. We can also refer to whole rows and whole columns.
  9. ActiveSheet.Cells(3,5)=10. [C3..F5].Cells(1,1) refers to cell C3 – the first cell in the Range.
  10. ActiveSheet.Rows(1) refers to the 2nd row
    [C3..F3].Rows(3).value=”Hi’ sets the 3rd row of the range (C5,D5,E5)
  11. ActiveSheet.Columns(“C”) and ActiveSheet.Columns(3) refer to the 3rd column of the worksheet.
  12. ActiveSheet.Rows(5).Columns(2) is the same as [B2] or Cells(5,2) or ActiveSheet.Columns(2).Rows(5)

The .value property of a Cell or a Range of Cells can be set with assigning a value to a range. [b3:d7].value=7 is the same as [b3:d]=7.

A problem in Excel is that Intellisense does not work with named ranges such as [Contacts], [A1:B7], Rows(3) etc. If you need Intellisense to tell you the properties or methods of a range, one trick is to assign the range to a declared variable of type Range. Declared variables always display Intellisense prompts.
dim x as Range

Set x=[B10:C12]

x. (Intellisense displays after you type the dot)

Looping through a Range

The for/each construct is good to loop through all the cells in a range. The order you go through the cells is called column major order – you go through each column and then on to the next row

Dim cell as Range ‘A single cell is a range

For each cell in ActiveSheet.UsedRange

Cell.Font.Size=Cell.Font.Size+2 ‘Make each cell bigger

If cell.value >=0 then
cell.Font.Color=vbBlack

Else

Cell.Font.Color=vbRed

End if

Debug.print cell.Address,cell.value
next cell
We can also control the order of the loop by using individual cells
dim myRange as Range

Set myRange=[D4:M10]

For row=1 to myRange.Rows.count
for column=1 to myRange.Columns.count
debug.print row,column, myRange.Cells(row,Column)

Next column

Next row

If you wanted to handle the items by row instead of column

dim myRange as Range

set myRange=Application.Selection

For column=1 to myRange.Columns.count
for row=1 to myRange.Rows.count
debug.print row,column, myRange.Cells(row,Column)

Next column

Next row

In summary, a cell is the basic building block of Excel spreadsheets and characters are the basic building blocks of Word documents. Ranges are collections of cells or characters. Learning to use the Range datatype allows us to write code to manipulate the contents of these applications.