Excel range iterate

Add-in Express™ Support Service
That's what is more important than anything else

Excel range iterate
 
gui




Posts: 14
 Joined: 2016-05-11
Hello,

I'm writing an Excel add-in with your great product. But I'm faced with a problem.

I selected several cells in a workbook, and I retrieve the range with:

function TAddInModule.GetSelectedRange: IxlRange;
var
Intf: IDispatch;
begin
Intf := ExcelApp.Selection[adxLCID];
Intf.QueryInterface(IxlRange, Result);
end;

Now, I need to iterate on each cell to retrieve its address.

In basic, I would use:
Dim rng As Range: Set rng = Application.Range("B2:C3")
Dim cel As Range
For Each cel In rng.Cells
Debug.Print cel.Address
Next cel


But in Delphi I can't reproduce the same.
I tried: 
Range := GetSelectedRange;
 for j := 1 to Range.Cells.Count do
  memo1.lines.Add(Range.Cells.Item[j, EmptyParam].Address[false, true, xlA1, false, EmptyParam] );
 exit;

But it always returns the same cell reference.

Any idea?

Thanks ;)
Posted 20 Mar, 2018 10:34:32 Top
Andrei Smolin


Add-in Express team


Posts: 18948
 Joined: 2006-05-11
Hello, 

function TAddInModule.GetSelectedRange(): Excel2000.ExcelRange;
var Intf: IDispatch;
begin
  Intf :=  ExcelApp.Selection[adxLCID];
  Intf.QueryInterface(Excel2000.DIID_ExcelRange, Result);
end;


procedure TAddInModule.adxRibbonTab1Controls0Controls1Click(
  Sender: TObject; const RibbonControl: IRibbonControl);
var
  rng: Excel2000.ExcelRange;
  j: integer;
begin
 rng := GetSelectedRange;
 for j := 1 to rng.Cells.Count do begin
  ShowMessage(rng.Cells.Item[j, EmptyParam].Address[false, true, xlA1, false, EmptyParam]);
 end;
end;



Andrei Smolin
Add-in Express Team Leader
Posted 21 Mar, 2018 03:38:18 Top
gui




Posts: 14
 Joined: 2016-05-11
Thank you for your answer! I also found the problem: range has to be in the same area.
Posted 21 Mar, 2018 18:11:03 Top
Andrei Smolin


Add-in Express team


Posts: 18948
 Joined: 2006-05-11
Hello,

gui writes:
I also found the problem: range has to be in the same area.


Sorry? Are you saying that the code above doesn't work in some scenario?


Andrei Smolin
Add-in Express Team Leader
Posted 22 Mar, 2018 01:12:13 Top
gui




Posts: 14
 Joined: 2016-05-11
Hello, Andrei

It does for cells in the same "area" but if you select cells in different areas of a worksheet, it won't work completely.
I found the solution in your Excel Toys for VCL sample for dealing with Areas. So your code above combined with the code below works in all cases. 

   for j := 1 to IRange.Areas.Count do
        for i := 1 to IRange.Areas.Item[j].Count do
          IRange.Columns.Item[i, EmptyParam].Merge(false)


Thank you!
Posted 22 Mar, 2018 15:19:19 Top
Andrei Smolin


Add-in Express team


Posts: 18948
 Joined: 2006-05-11
Thank YOU!


Andrei Smolin
Add-in Express Team Leader
Posted 23 Mar, 2018 00:46:21 Top