Insert variant array to excel range

[Davidous]

Hello,

I'm using Lazarus 2.2.4. x32 bit currently.
Before Lazarus 2.2.0. I used variant arrays to export big ranges of data from excel, and after the neccessary calculations I imported this variant array back to excel with a formula like this:

Excel.Activesheet.range[Excel.Activesheet.Cells[row,column],Excel.Activesheet.Cells[row,column]]:=Variant array;

Before Lazarus 2.2.0. this formula worked, but now I get an error even with 2.2.4.

Any ideas why this can be?

Thanks in advance!

[Thaddy]

What error? That would be very helpful!

[Davidous]

I have the error message in my language, the translation would be "Member not found". Hope it helps. I can also make a small demo program later this afternoon.

[rvk]

I have the error message in my language, the translation would be "Member not found". Hope it helps.

No, not very much.
You can switch your Lazarus to English (much easier to communicate).
Otherwise copy the EXACT error in your language (EXACT error is very important).
You can right click the error and choose Copy > Copy selected messages to clipboard.

Anyway... the error suggests either .ActiveSheet isn't a member of Excel or .Range or .Cells are not member of .ActiveSheet.

Also... show more code. How did you obtain Excel and .ActiveSheet?
Did you check if they got a valid value?

[TRon]

I can also make a small demo program later this afternoon.

You should. And when doing so make sure that every call made to comm has the result returned and checked against.

Member not found simply means that comm is unable to find the property/method in the comm object. Where exactly depends on how far you are able to get. In worse case scenario it might even be that the Excel object is empty (we can't tell from your one-liner).

edit: Ah, crossed post with rvk, see his remarks.

[Davidous]

Dear TRon, rvk and Thaddy,

thank you for your suggestions. I made my demo program and compiled it with laz 1.8.4. and 2.2.4. In the program I create a variant array of 2*2 and fill it with "1" numbers. After that I (would like to) add this variant array to an excel range.
The program compiled with 1.8.4. works as it should, but the one compiled with 2.2.4. gives an error message.

You can download the exe-s from here:

https://drive.google.com/file/d/1ypN8w70jXuLaPPbu15kZ_iKgWeFj2baj/view?usp=share_link

The code is the following:

unit Unit1;

{$mode objfpc}{$H+}

interface

uses
  Classes, SysUtils, Forms, Controls, Graphics, Dialogs, StdCtrls, ComObj,
  Variants;

type

  { TForm1 }

  TForm1 = class(TForm)
    Button1: TButton;
    procedure Button1Click(Sender: TObject);
  private

  public

  end;

var
  Form1: TForm1;
  E,arr: Variant;

implementation

{$R *.lfm}

{ TForm1 }

procedure TForm1.Button1Click(Sender: TObject);
begin
  E:=CreateOleObject('Excel.Application');
  E.WorkBooks.Add;
  E.Visible:=True;
  arr:=VarArrayCreate([0,1,0,1], VarVariant);
  arr[0,0]:=1;
  arr[1,0]:=1;
  arr[0,1]:=1;
  arr[1,1]:=1;
  E.ActiveSheet.Range(E.ActiveSheet.Cells[1,1],E.ActiveSheet.Cells[2,2]):=arr;
  E:=Unassigned;
end;

end.

[Nicole]

If any possible avoid variant. I work a lot with figures. They internally are tricky. The bits differ, how they store figures. So I shred the bytes into bits in some cases. What do I say? I? I got a lot of help for this trick by a genius.

Yes, I use some variant types as well. Sometimes it must be.
Under the line, I prefer to use most common types, if ever possible: integer, string, double,...

And I had an Excel-interface as well. Did it with VBA and OLE and..........  :'( :'(

The day I gave it up, was a good day.
Why? It was not stable because of the most dangerous element for any software: the user (who may this have been,  ).

[rvk]

I made my demo program and compiled it with laz 1.8.4. and 2.2.4.

First of all, you made a fine test program. It crashes here too (although I can't see why on first glance).

Although you normally need to put code between code-tags in your posts (you can use the #-button in the editor for that.

*) I would make E an OleVariant. Not sure if it really matters but I think better.

*) The following line doesn't return an error

Code: Pascal  [Select][+][-]

1. var Range: OleVariant;
2. //...
3.   Range := E.ActiveSheet.Range(E.ActiveSheet.Cells[1, 1], E.ActiveSheet.Cells[2, 2]); // This is Ok
4.   Range := 'aaaaaaa'; // this fills the array with aaaa's
5.   // Range.Value := arr; // we don't do this yet

So E, ActiveSheet and Range all work correctly.

*) So the real error is in this line.

Code: Pascal  [Select][+][-]

1.   Range.Value := arr; // copy data from allocated variant array

I'm not sure why.

I thought Excel array's should be 1-based so you should do:

Code: Pascal  [Select][+][-]

1.   Arr := VarArrayCreate([1, 2, 1, 2], VarVariant);
2.   Arr[1, 1] := 1;
3.   Arr[2, 1] := 1;
4.   Arr[1, 2] := 1;
5.   Arr[2, 2] := 1;

But it doesn't fix your crash (at least not here).

I've got this now:

Code: Pascal  [Select][+][-]

1. procedure TForm1.Button1Click(Sender: TObject);
2. var
3.   E, Range: olevariant;
4.   Arr: variant;
5. begin
6.   E := CreateOleObject('Excel.Application');
7.   E.WorkBooks.Add;
8.   E.Visible := True;
9.   Arr := VarArrayCreate([1, 2, 1, 2], VarVariant);
10.   Arr[1, 1] := 1;
11.   Arr[2, 1] := 1;
12.   Arr[1, 2] := 1;
13.   Arr[2, 2] := 1;
14.  
15.   Range := E.ActiveSheet.Range(E.ActiveSheet.Cells[1, 1], E.ActiveSheet.Cells[2, 2]); // this is Ok
16.   Range.Value := 'aaaaaaa'; // Works ok
17.   ShowMessage('1');
18.   Range.Value := Arr; // Crashes
19.  
20.   E := Unassigned;
21. end;

[Davidous]

Thank you for trying! I couldn't figure it out either so far.
About the excel arrays... If you read a certain range from excel into an array then the arrays rows and columns will start with 1, but if you just create an array, then you can start with 0 and read it into excel. Or you could, because it's not working now

[Davidous]

I made one more exploration.
I tried to add a certain item of my variant arrai to an excel cell like this:

E.ActiveSheet.Cells[1, 1]:=arr[1,1];

The result was the same error when I tried to add the whole variant array to an excel range. BUT!

E.ActiveSheet.Cells[1, 1]:=WideString(arr[1,1]); -> this works!!!

I wonder if there is a way to convert the whole variant array to an array of widestrings? Maybe then it could be added to excel too, like it was in the above example.

[Davidous]

Hello!

I found the solution!

Range.Value := arr; -> gives an error message
Range.Value := OleVariant(arr); -> works!!! 

[marcov]

Have people ever tried to debug issues like this? Are they compiler or runtime related?

[TRon]

Have people ever tried to debug issues like this? Are they compiler or runtime related?

afaik it was a design decision introduced with FPC 3.0.0.

Our variant was then rendered incompatible with OleVariant (at least the array).

I seem to remember it was mentioned somewhere but was unable to find it in the user changes wiki page. So far I was unable to find the related forum post that mentioned it as well. Ergo the reasoning behind it eludes me atm.

[jamie]

As far as know, OleVariant has always been the requirement, even with Delphi as long as I remember!

 Which is why I keep seeing this problem and I keep saying to use OLEVariant !

[marcov]

Have people ever tried to debug issues like this? Are they compiler or runtime related?

afaik it was a design decision introduced with FPC 3.0.0.

Our variant was then rendered incompatible with OleVariant (at least the array).

It also happens when all variables are olevariant, so apparently someting in the runtime or compiler still returns variant intermediate results.