Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Teams

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

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;
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;
   // Now write the VarArray back to the spreadsheet
   thisSheet.range[RangeStartAddr, RangeEndAddr].Value2 :=  myRange;
                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
                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
                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
                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
                Jul 23, 2018 at 15:55
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. 
  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');
    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;
                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?
– user1009073
                Jul 24, 2018 at 9:41
        
Thanks for contributing an answer to Stack Overflow!
Please be sure to answer the question. Provide details and share your research!
But avoid …
Asking for help, clarification, or responding to other answers.
Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.