Iterating through an Excel Range with COM automation

[stephen cacclin]

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

 

[Bob Phillips]

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)

 

[Tushar Mehta]

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