Delphi Excel Automation getting rid of .select for speed

[Smithy1001]

Hi,
I use delphi to manipulate excel and frequently format cells iteratively. I am told that using say range['A1'].select and then excel.selection.value:= etc is time consuming and the best way is to put just range['A1'].value:= as the select process takes time. So, I have the following code which I wish to rif of the .select

for i := 0 to NumCodesMPSDetail-1 do
begin
Num1 := (13*i)+10;
WS.range['B'+inttostr(Num1+1)+':M'+inttostr(Num1+1)].select;
Excel.Selection.FormatConditions.Delete;
Excel.Selection.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4));
Excel.Selection.FormatConditions[1].Interior.ColorIndex := 7;
Excel.Selection.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
Excel.Selection.FormatConditions[2].Interior.ColorIndex := 3;
end;

I have put
for i := 0 to NumCodesMPSDetail-1 do
begin
Num1 := (13*i)+10;
RangeString := 'B'+inttostr(Num1+1)+':M'+inttostr(Num1+1);
Ranger := ws.range[rangestring];
ranger.FormatConditions.Delete;
ranger.FormatConditions.Add(XLCellValue,XLLess,'=b$'+inttostr(Num1-4)); //note put a but gives b...!
ranger.FormatConditions[1].Interior.ColorIndex := 7;
ranger.FormatConditions.Add(XLCellValue,XLGreater,'=b$'+inttostr(Num1-3));
ranger.FormatConditions[2].Interior.ColorIndex := 3;
end;

where ranger is a variant. This works of sorts. Where I have put '=b$', my formatcondition actually comes out as '=a$' within the cells in excel. So I have 2 questions:
1. Why does the column reference b get transposed to a when I get rid of the select?
2. How can I use the with statement instead of having to write ranger. each time. ie. with ranger do begin formatconditions.delete; etc end;
Thanks

 

[DjangMan]

Everything looks fine without running the actual code.

Are you early or late binding? Have you tried selecting the whole range of cells and then apply the formatting to them so you're down to a few 'select' calls?

Have you turned off screen updates in Excel while you're processing the document?

 

[Smithy1001]

Hi,

I am late binding. I have not turned off screen updates but I will. I cannot select the whole range of cells as it's only every 13th row of 5000 rows that I want the 2 conditional formatting lines on, so it's row 10,23,36 etc (ie 10+x*i).
Also I cannot use the following scenario. I have even tried putting ws.pagesetup as a variant.
ws.pagesetup.centerheader := 'This works';
with ws.pagesetup do
begin
CenterHeader := 'This does not';
end;
I will put this as a separate thread.
Thanks