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: 80
 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: 19138
 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.


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