Iterating through an Excel Range with COM automation
 

aloha!

I'm trapping the WorksheetChange and WorksheetSelectionChange events with
some C++ COM code and would like to properly iterate through the cells in
the range object. Single cell or single block ranges are no problems, but I
can't seem to detect/process cases where the range is discontinuous (as one
encounters when ctrl-clicking various cells).

In VBA, one can obviously use something like "for each cellObj in rangeObj".
With COM, however, one is limited to using an object's properties/method;
the desired results aren't readily available through the Cells or Index
properties.

On the surface it seems simple enough: check the "Count" property, then use
Index(n) to access the individual cells. But a funny thing happens: Index(2)
and onwards return cells that are not necessarily selected (ie. in the
range). For example: if you select six cells in a diagonal
(A2,B3,C4,D5,E6,F7), the results of Index(n) would be: (A2,A3,A4,A5,A6,A7).
Ditto, I believe for Cells(n). And so this is a row offset parameter to the
first cell of the range only and not at all useful.

Am I missing something or has anyone experienced this?

Best regards,

Stephen

Iterating through an Excel Range with COM automation
 

I can't talk C++, but I fail to see why you can't use For Each

This is valifd VBA code from Automation

For Each cell In xlApp.Activeworkbook.Range("A1:A10")

where cell is declared as Excel.Range.

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"stephen cacclin" wrote in message
...
aloha!

I'm trapping the WorksheetChange and WorksheetSelectionChange events with
some C++ COM code and would like to properly iterate through the cells in
the range object. Single cell or single block ranges are no problems, but
I
can't seem to detect/process cases where the range is discontinuous (as
one
encounters when ctrl-clicking various cells).

In VBA, one can obviously use something like "for each cellObj in
rangeObj".
With COM, however, one is limited to using an object's properties/method;
the desired results aren't readily available through the Cells or Index
properties.

On the surface it seems simple enough: check the "Count" property, then
use
Index(n) to access the individual cells. But a funny thing happens:
Index(2)
and onwards return cells that are not necessarily selected (ie. in the
range). For example: if you select six cells in a diagonal
(A2,B3,C4,D5,E6,F7), the results of Index(n) would be:
(A2,A3,A4,A5,A6,A7).
Ditto, I believe for Cells(n). And so this is a row offset parameter to
the
first cell of the range only and not at all useful.

Am I missing something or has anyone experienced this?

Best regards,

Stephen

Iterating through an Excel Range with COM automation
 

I'm surprised that C++ doesn't support the equivalent of For Each...

But, in any case, use the Areas property to detect non-contiguous
selections. Use the C++ equivalent of

Sub testIt()
Dim AreaIdx As Long, CellIdx As Long, _
Target As Range
Set Target = Selection
Debug.Print Target.Areas.Count
For AreaIdx = 1 To Target.Areas.Count
Debug.Print Target.Areas(AreaIdx).Cells.Count
For CellIdx = 1 To Target.Areas(AreaIdx).Cells.Count
Debug.Print Target.Areas(AreaIdx).Cells(CellIdx).Address
Next CellIdx
Next AreaIdx
End Sub

The result is:

6
1
$A$1
1
$B$2
1
$C$3
1
$D$4
1
$E$5
1
$F$6

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...
aloha!

I'm trapping the WorksheetChange and WorksheetSelectionChange events with
some C++ COM code and would like to properly iterate through the cells in
the range object. Single cell or single block ranges are no problems, but I
can't seem to detect/process cases where the range is discontinuous (as one
encounters when ctrl-clicking various cells).

In VBA, one can obviously use something like "for each cellObj in rangeObj".
With COM, however, one is limited to using an object's properties/method;
the desired results aren't readily available through the Cells or Index
properties.

On the surface it seems simple enough: check the "Count" property, then use
Index(n) to access the individual cells. But a funny thing happens: Index(2)
and onwards return cells that are not necessarily selected (ie. in the
range). For example: if you select six cells in a diagonal
(A2,B3,C4,D5,E6,F7), the results of Index(n) would be: (A2,A3,A4,A5,A6,A7).
Ditto, I believe for Cells(n). And so this is a row offset parameter to the
first cell of the range only and not at all useful.

Am I missing something or has anyone experienced this?

Best regards,

Stephen