Performance Considerations in Excel

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

Performance Considerations in Excel
 
George Spears




Posts: 76
 Joined: 2010-05-06
Hello,

I have a plugin written for excel. One routine in the plugin goes through each row in a column and left pads the text with a certain number of '0'. I have a similar routine from Ablebits. (Which I think is written using Add-In Express). It is much faster than my code. I know there is something else I should be doing, but I don;t know what... Here is a code snippet. What am I overlooking? 

for i := StartingRow to BoundRec.LastRow do
  begin
    // Make sure that there is NOT a filter applied.
    if IsRowVisible(i) then
    begin
      CellAddr := ColumnToUpdate + IntToStr(i);
      MyCellText := trim(aws.Range[CellAddr, EmptyParam].Value);
      if Length(MyCellText) > 0 then
      begin
        // There is something here...
        // If we want the text left aligned
        if CB_Align.Text = 'Left' then
         PaddedText := PadC(MyCellText, StrToInt(PadLength.Text), PadCharacter, True)
         else
          PaddedText := PadC(MyCellText, StrToInt(PadLength.Text), PadCharacter, False);
       
         // aws = Active worksheet.
         aws.Range[CellAddr, EmptyParam].Value := PaddedText;

        end;

      end;
    end;


There are 150,000 rows to process. I do notice that the rows appear to be DISPLAYED as soon as they are updated. By this, I mean that when I start the routine, I can see the first 30 or so rows, and the appropriate column values have changed, even though the routine is still working. Could my issue be that I need some type of "disable screen updating" function?

Thanks
George S
Posted 08 Feb, 2018 09:50:39 Top
Andrei Smolin


Add-in Express team


Posts: 18421
 Joined: 2006-05-11
Hello George,

Instead of editing each cell, you need to construct a Range object containing many cells, retrieve an array containing their values by reading Range.Value, modify the values, and put them back by writing the array to Range.Value.

Regards from Belarus (GMT+3),

Andrei Smolin
Add-in Express Team Leader
Posted 09 Feb, 2018 05:41:21 Top