Board index » delphi » Excel VBA code that does not work under OLE automation

Excel VBA code that does not work under OLE automation

The following VBA code executes properly as a macro (fills 10 rows with
1,2..10):

Public Sub mysub()
Dim arr(1 To 10, 1 To 10) As Variant
  For i = 1 To 10
    For J = 1 To 10
      arr(i, J) = i
    Next J
  Next i
  ActiveSheet.Range(ActiveSheet.Cells(1, 1), ActiveSheet.Cells(10, 10))
= arr
End Sub

Yet what I would think is the corresponding Delphi code using variants
does not work. Instead, a "Member not Found" error is issued.  Here is
the Delphi code:

procedure TForm1.Button1Click(Sender: TObject);
var
  XL:OLEVariant;
  Arr:Variant;
  I,J:Integer;
begin
  XL := getactiveoleobject('EXCEL.APPLICATION');
  XL.Visible := true;
  Arr := VarArraycreate([1,10,1,10],varvariant);
  For I := 1 to 10 do
    For J:= 1 to 10 do Arr[i,j] := I;

XL.Activesheet.range(xl.activesheet.cells(1,1),xl.activesheet.cells(10,10))
:= arr;
end;

Why doesn't the Delphi code work????

 

Re:Excel VBA code that does not work under OLE automation


Hi Tom!!

Try changing your code to this:

 ActiveSheet.Range[ActiveSheet.Cells(1, 1), ActiveSheet.Cells(10, 10)]

Use brackets instead of parenthesis in a range definition, I'm not sure,
but it is possible that Cells shoud also be referenced using brackets.
Delphi views VB indexed properties as array properties.

Re:Excel VBA code that does not work under OLE automation


Quote
>  ActiveSheet.Range[ActiveSheet.Cells(1, 1), ActiveSheet.Cells(10, 10)]

> Use brackets instead of parenthesis in a range definition, I'm not sure,
> but it is possible that Cells shoud also be referenced using brackets.
> Delphi views VB indexed properties as array properties.

 Thanks for the suggestion but "member not found" is also generated when
square brackets are used.

Re:Excel VBA code that does not work under OLE automation


Hi!  this should work (I've tested it)

ActiveSheet.Range[ActiveSheet.Cells[1, 1].Value,
ActiveSheet.Cells[10,10].Value].Value = ....

Tom Tatham <ttat...@usuhs.mil> wrote in article
<36111383.5432E...@usuhs.mil>...

Quote
> >  ActiveSheet.Range[ActiveSheet.Cells(1, 1), ActiveSheet.Cells(10, 10)]

> > Use brackets instead of parenthesis in a range definition, I'm not
sure,
> > but it is possible that Cells shoud also be referenced using brackets.
> > Delphi views VB indexed properties as array properties.

>  Thanks for the suggestion but "member not found" is also generated when
> square brackets are used.

Other Threads