Board index » delphi » Help:Variant Array of Variant confusion

Help:Variant Array of Variant confusion

I seem to be missing some fundamental piece of information about
variants.  I have a subroutine which returns a variant array of
variant from Excel from a Range.  Since I don't know beforehand the
size of the range or the varType of each item in the array, I tried
using the following code;

  Var
    dd, lb, hb, ii : Integer;
    vDataIn : Variant;

  ...

  vDataIn := xlGetVariant(1,VDataSize,Row);
  for dd := 1 to VarArrayDimCount(VDataIn) do begin
    lb := VarArrayLowBound(VDataIn,dd);
    hb := VarArrayHighBound(VDataIn,dd);
    for ii := lb to hb do
      if lb = hb
        then memo1.Lines.Add(YN(VarIsArray(VDataIn[dd])))
        else memo1.Lines.Add(YN(VarIsArray(VDataIn[dd][ii])));
  end;

YN is just a function that takes a boolean and returns a string.  I
originally tried using VarToStr in place of YN(VarIsArray(...)), but I
change it in case it was a conversion problem.

The error message I get (whether or not lb = hb) is "Index out of
range"

How can this be, if I know the upper and lower bounds of the array?

Can someone point out what I'm doing wrong?

Thanks,

Paul

 

Re:Help:Variant Array of Variant confusion


<<Paul Johnson:
I seem to be missing some fundamental piece of information
about variants.  

Quote

Dimensions aren't columns. A range in a single worksheet is
always 2-dimensional, unless it is a single cell. This
example doubles every value in a worksheet range (Area):

      ArrV := Area.Value;
      if VarArrayDimCount(ArrV) = 1 then
        { Area is a single cell }
        ArrV := ArrV * 2
      else
      begin
        for Row := 1 to VarArrayHighBound(ArrV, 1) do
          for Col := 1 to VarArrayHighBound(ArrV, 2) do
            ArrV[Row, Col] :=  ArrV[Row, Col] * 2;
        Area.Offset[8, 0].Value := ArrV;
      end;

--
Deborah Pate (TeamB)
http://delphi-jedi.org
Sorry, no email please.

Re:Help:Variant Array of Variant confusion


<<Deborah Pate (TeamB):
  if VarArrayDimCount(ArrV) = 1 then

Quote

Oops, sorry that should have been

  if VarArrayDimCount(ArrV) = 0 then
because in those circumstances ArrV is not an array at all.

--
Deborah Pate (TeamB)
http://delphi-jedi.org
Sorry, no email please.

Re:Help:Variant Array of Variant confusion


Are you sure about single cell in case of reading? I know for sure that
writing of a 2d array consisting of one element to an Excel range works - I
use it all the time in my code.

Re:Help:Variant Array of Variant confusion


Are you sure about single cell in case of reading? I know for certain that
writing of a 2d array consisting of one element to an Excel range works - I
use it all the time in my code.

Re:Help:Variant Array of Variant confusion


<<Igor Ivanov:
Are you sure about single cell in case of reading? I know
for sure that writing of a 2d array consisting of one
element to an Excel range works - I use it all the time in
my code.

Quote

You can assign a variant array to an Excel range of a
different size. If the Excel range is smaller, only the
values that fit in will show; if it's larger, you'll see
lots of #N/A signs filling the rest. But when you read an
Excel range into a variant, the variant is sized according
to the Excel range - and if that is only one cell, the
variant won't be an array at all.

--
Deborah Pate (TeamB)
http://delphi-jedi.org
Sorry, no email please.

Re:Help:Variant Array of Variant confusion


Deborah,

Thanks so much for this clarification.  I have Delphi 5 Pro and Office
Partner from TurboPower, and in all that documentation I could not
find this information.

For future reference, where should I go to find this and related
information?

Thanks again,

Paul
On Wed, 29 Nov 2000 19:50:06 GMT, "Deborah Pate (TeamB)"

Quote
<d.p...@cableinet.co.not-this-bit.uk> wrote:
><<Paul Johnson:
>I seem to be missing some fundamental piece of information
>about variants.  

>Dimensions aren't columns. A range in a single worksheet is
>always 2-dimensional, unless it is a single cell. This
>example doubles every value in a worksheet range (Area):

>      ArrV := Area.Value;
>      if VarArrayDimCount(ArrV) = 1 then
>        { Area is a single cell }
>        ArrV := ArrV * 2
>      else
>      begin
>        for Row := 1 to VarArrayHighBound(ArrV, 1) do
>          for Col := 1 to VarArrayHighBound(ArrV, 2) do
>            ArrV[Row, Col] :=  ArrV[Row, Col] * 2;
>        Area.Offset[8, 0].Value := ArrV;
>      end;

Re:Help:Variant Array of Variant confusion


<<Paul Johnson:
For future reference, where should I go to find this and
related information?

Quote

I don't know a better place than here, really - the Excel
VBA help doesn't mention it anywhere that I've seen.

--
Deborah Pate (TeamB)
http://delphi-jedi.org
Sorry, no email please.

Other Threads