0

This is an MCVE of a point which cropped up in a comment to an answer here today. The answer was related to the task of transferring values from the 2nd dimension of a variant array into a vertical column of a worksheet. The comment suggested using Excel's WorhSheetFunction.Transpose to assign the variant's data into the destination cells. Fair enough.

The code below creates a one-dimensional array of values and then attampts to assign it to a column of cells in the worksheet. If you run it, you'll see that the result is

A1 = 1
A2 = 1
A3 = 1

This is the code

procedure AssignArray;
var
  Excel,
  WB,
  Values : OleVariant;
begin
  Excel := CreateOleObject('Excel.Application');
  Excel.Visible := True;
  WB := Excel.WorkBooks.Add;
  try
    Values := VarArrayCreate([0, 2], varVariant);
    Values[0] := 1;
    Values[1] := 2;
    Values[2] := 3;

    WB.ActiveSheet.Range('A1:A3') := Values;
    ShowMessage('Here');
  finally
    WB := UnAssigned;
    Excel := UnAssigned;
  end;
end;

Iow you get the first value of the array in all 3 rows. Googling this result finds Writing an array to a range. Only getting first value of array in which all three answers say to use Transpose. Fine, but my question is, how to avoid the triplicated first value problem in my case, where the source data is a single-dimensional array. Transposing a one-dimensional array doesn't make sense ...

MartynA
  • 30,454
  • 4
  • 32
  • 73
  • I have never worked with Excel's API before, but it seems like all ranges in Excel are two-dimensional, that is, matrices. So when you assign a one-dimensional array (`Values`) to a two-dimensional range (`Range['A1:A3']`), the one-dimensional array needs to be promoted to a two-dimensional matrix. This happens all the time in pure mathematics: vectors are implicitly treated as column matrices most of the time, and if you sometime need a row matrix, you transpose. See, for instance, [Wikipedia](https://en.wikipedia.org/wiki/Inner_product_space#Euclidean_vector_space). And it so happens that... – Andreas Rejbrand Mar 14 '21 at 19:35
  • ...Excel by default considers vectors to be rows instead of columns; hence, you need to transpose to get a column. Alternatively, you should be able to create an explicit two-dimensional array which is a column. To me this seems fairly natural. – Andreas Rejbrand Mar 14 '21 at 19:36
  • If you don't want to transpose (the extra cost of which I do not know), you can of course fill one value at a time using a loop, or maybe you can use some other property (not `Range`). – Andreas Rejbrand Mar 14 '21 at 19:39
  • @AndreasRejbrand: Thanks for the info. In fact, I've tried creating a 2D array, but my ham-fisted first attempt to put my data in the second column didn't get very far. Encouraged by your info, I'll have another go and edit it into my q, probably in the morning. – MartynA Mar 14 '21 at 19:39
  • @AndreasRejbrand: Ironically, it was an adverse comment on my filling in one value at a time which prompted my to try this wasy. – MartynA Mar 14 '21 at 19:41
  • for columns, create 2D arrays. Dimension 1 represents rows; dimension 2 represents columns. So your array for multiple columns would be `V(1 to numRows, 1 to numCols)` – Ron Rosenfeld Mar 14 '21 at 20:44
  • Sorry to see that you find my comment to [that answer](https://stackoverflow.com/a/66624934/11562188) adverse. It wasn't meant that way. I even emphasized my upvote to indicate that I liked the answer in the first place. I was just trying to share my view on how to improve performance, since performance was also an issue (mentioned in comments). As for your question, I'm afraid you will need 2D variant array `Values := VarArrayCreate([0, 2, 0, 0], varVariant);`. – Peter Wolf Mar 14 '21 at 21:19
  • @PeterWolf: Please, don't worry. I only meant "adverse" in the sense that it pointed out that the way I had coded it is sub-optimal, which is perfectly fair comment. And I'm glad that you did, because it provoked me into wondering how to load the data into a column wihout using `Transpose`. I will be tring out Ron Rosenfield's helpful suggestion ... – MartynA Mar 14 '21 at 21:58
  • @MartynA: For two dimensional arrays, see https://stackoverflow.com/a/16642049/62576. – Ken White Mar 14 '21 at 23:52

0 Answers0