Range.Calculate

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Greg Maxey - 01 Jan 2008 14:58 GMT

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

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

Reply to this Message

Greg Maxey - 01 Jan 2008 18:51 GMT

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

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Does anyone know how Range.Calculate manages to define the "range" of
> a selection of cells to just those cells and exclude adjacent cells?
[quoted text clipped - 19 lines]
>
> Thanks

Reply to this Message

Tony Jollans - 01 Jan 2008 19:52 GMT

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.

Signature

Enjoy,
Tony

>I haven't answered my questions, but I have found away around the
>unfortunate results using Selection.Range.Cells:
[quoted text clipped - 34 lines]
>>
>> Thanks

Reply to this Message

Greg Maxey - 02 Jan 2008 00:01 GMT

Thanks Tony.

Signature

Greg Maxey/Word MVP
See:
http://gregmaxey.mvps.org/word_tips.htm
For some helpful tips using Word.

> Hi Greg,
>
[quoted text clipped - 56 lines]
>>>
>>> Thanks

Reply to this Message