Range.Calculate

[Greg Maxey]

Does anyone know how Range.Calculate manages to define the "range" of a
selection of cells to just those cells and exclude adjacent cells?

I created a simple 3 column x 2 row table and put the number 2 in each cell.
Then I selected the middle column and ran this code:

Sub Test()
Dim oCell As Word.Cell
Dim i As Single
MsgBox Selection.Range.Calculate
MsgBox Selection.Range.Cells.Count 'Returns 4 as unfortunately is expected.
For Each oCell In Selection.Range.Cells
On Error Resume Next
i = i + CSng(Left(oCell.Range.Text, Len(oCell.Range.Text) - 2))
Next oCell
MsgBox i 'Again returns 8 as unfortunately expected.
End Sub

It is the same range, but somehow Range.Calculate knows to process only the
values that are actually selected, and not the entire selection range. How
does it do this?

Thanks

 

[Greg Maxey]

I haven't answered my questions, but I have found away around the
unfortunate results using Selection.Range.Cells:

Sub Test()
Dim oCell As Word.Cell
Dim i As Single
MsgBox Selection.Range.Calculate
MsgBox Selection.Cells.Count 'Returns 2.
For Each oCell In Selection.Cells
On Error Resume Next
i = i + CSng(Left(oCell.Range.Text, Len(oCell.Range.Text) - 2))
Next oCell
MsgBox i 'Returns 4.
End Sub

 

[Tony Jollans]

Hi Greg,

In the general case of multi-cell (single area) selections there is a
difference between the Selection (just the selected cells) and the
Selection.Range (the cells starting with the first cell in the selection and
moving left-to-right, top-to-bottom through the table to the last cell in
the selection). As you have observed in your case Selection.Cells.Count is 2
(row 1 column2, and row2 column 2) and Selection.Range.Cells.Count is 4
(row1 column2, row 1 column 3, row 2 column 1, and row 2 column 2)

Calculate is a method of the Selection and, although it does work on
non-Selection Ranges, it works as though the Range supplied were a
Selection - that is (in your simple table) the Range from cell(1,2) to
cell(2,3) which contains 5 cells each with a value of 2, Calculates to 8
because cell(2,1), although in the range, is not included in the
calculation.

 

[Greg Maxey]

Thanks Tony.