Delphi - Using VarArrayCreate to loop through an Excel Range of data

Question

Delphi Tokyo - I am using Delphi to do some Excel spreadsheet pre-processing prior to a load operation. I am trying to read an Excel Range into a VarArray, loop through the array to do cleanup (in this case, it is a zip code column. If it is a 4 digit zipcode, prefix a '0'), and then write the VarArray back to the Excel range. Everything compiles, but I get an error message on the first access of the VarArray. The specific error is 'Variant or safe array index out of bounds'. My VarArray starts at row 2 of the excel range. Any idea why I am getting this error? I have tried to simplify the code as much as possible here....

function PROCESS_ZIP_CODE_5DIGIT_MIN(P1,P2 : String): integer;
var

MyColumnLetter : String;
thisSheet : _Worksheet;
i : Integer;
CellText : String;

 arrData: Variant;
 myRange : ExcelRange;
 RangeStartAddr, RangeEndAddr : String;
begin

  MyColumnLetter := 'H';
  thisSheet := oExcel.ActiveSheet as _Worksheet;

  {create variant array where we'll copy our data}
  arrData := VarArrayCreate([2, 500 ], varVariant);

  // Get the Range Address
  RangeStartAddr := MyColumnLetter + '2';
  RangeEndAddr :=   MyColumnLetter + IntToStr(500);

  // Now read the data into the VarArray
   myRange :=   thisSheet.range[RangeStartAddr, RangeEndAddr];
   arrData := myRange.Value2;

 // Now process the data itself
   for i  := 2 to 500 do
   begin

    CellText := arrData[i]; // ERROR ON THIS LINE
    if Length(CellText) = 4 then
    begin
      CellText:= '0' + CellText;
      arrData[i] := CellText;
    end;
   end;

   // Now write the VarArray back to the spreadsheet
   thisSheet.range[RangeStartAddr, RangeEndAddr].Value2 :=  myRange;
end;

After arrData := VarArrayCreate([2, 500 ], varVariant); you assign a value to arrData with arrData := myRange.Value2;, that assignment is overwriting you var array. Does this return an array of var, too? — nil
– nil, Commented Jul 23, 2018 at 15:06
@nil: and even if it returns a var array too, it still overwrites the array created with VarArrayCreate. I think the call to VarArrayCreate is not necessary. And the new array could have different bounds. These must be checked before entering the loop. — Rudy Velthuis
– Rudy Velthuis, Commented Jul 23, 2018 at 15:12
My code sample is based off of work here... scalabium.com/faq/dct0144.htm which copies a stringGrid to Excel. Obviously I am doing something wrong... :( — user1009073
– user1009073, Commented Jul 23, 2018 at 15:22
Your code sample is different. You use strings to access the cells (MyColumnLetter + '2'), while they use numbers: [wb.workSheets[1].Cells[1, 1], etc...]. Are you sure you receive anything? And you may need a two-dimensional array too, even if the second dimension is only 1 in size. Just try their exampe verbatim, and then modify it to your needs, constantly checking if it still works. If something goes wrong, you know what you did last and that must be the problem. — Rudy Velthuis
– Rudy Velthuis, Commented Jul 23, 2018 at 15:55

Ken White · Accepted Answer · 2018-07-23 17:37:04Z

2

I'm not going to try to sort out your code, because it has a bunch of errors in it.

Here's a working sample of code to retrieve a range of cells (in this case, H1 through the last populated cell in J) into a variant array and then put that array into a Delphi TStringGrid. While the code uses late binding instead of early binding, it pretty clearly demonstrates the proper use of VarArrayCreate when reading a range from Excel.

var
  Excel, Book, Sheet, Range1: OleVariant;
  i, j: Integer;
  Data: Variant;
const
  // Obtained at https://msdn.microsoft.com/en-us/library/office/ff820880.aspx
  xlDown = -4121;
begin
  Excel := CreateOleObject('Excel.Application');
  try
    Book := Excel.WorkBooks.Open('E:\TempFiles\Test.xlsx');
    Sheet := Book.Worksheets.Item['Sheet1'];

    // Get tne range we want to extract, in this case all rows of columns H-J.
    // .End(xlDown) finds the last used cell in the indicated column
    Range1 := Sheet.Range['H1', Sheet.Range['J1'].End[xlDown]];
    Data := Range1.Value;

    // Get the number of columns and rows from the array itself. The addition
    // of 1 is for the fixed row and column, and to synch up with the Data
    // array being 1 based instead of 0
    StringGrid1.ColCount := VarArrayHighBound(Data, 2) + 1;
    StringGrid1.RowCount := VarArrayHighBound(Data, 1) + 1;

    // StringGrid.Cells are accessed in Col, Row order, but the
    // array is returned in Row, Col layout. Note the swap in
    // i and j below in the subscripts to accomodate that fact.
    for i := 1 to StringGrid1.ColCount - 1 do
      for j := 1 to StringGrid1.RowCount - 1 do
        StringGrid1.Cells[i, j] := Data[j, i];

  finally
    // Clean up all references so Excel will close cleanly
    Range1 := null;
    Sheet := null;
    Book := null;
    Excel.Quit;
    Excel := null;
  end;

answered Jul 23, 2018 at 17:37

Ken White

126k15 gold badges237 silver badges476 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user1009073 Over a year ago

This gave me the info I needed. I really appreciate the in-depth comments. FYI - I could not get the Range1:= line to compile (Excel 2016) so I had to change that. If I change the Range so that it is from H1 to H50, I would have expected the 'Data' variant to be a single dimension array, yet it still is two dimensional (as per VarArrayDimCount(Data) . Can you explain WHY that is?

Collectives™ on Stack Overflow

Delphi - Using VarArrayCreate to loop through an Excel Range of data

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related