Working with Arrays

M

MJatAflac

Good morning,

I am trying to modify some existing code to suit my purpose. This is the
first time I've worked with an array so I'm experiencing some difficulty and
I was wondering if someone out there can answer a couple of questions for me.

I believe this code:

Dim MyArray(10, 10) As Integer

Would give me an array of integers 11 rows by eleven columns

But I have this code in my Proc and don't understand what it's doing:

varResults = rstPQA.GetRows(2 ^ 15)

I know it's taking the results of a query and loading them into an array but
what does the (2^15) do?

I have other questions but I think if I get the answer to this one it might
answer the others.

Thanks in advance for your help,

Michael Joyce
 
K

Klatuu

the ^ is an expotential operator. in means in this case raise the value of 2
to the 15th power which will return 32768
So, the GetRows method will return up to 32768 rows depending on whether it
hits EOF before that number of rows is returned.
 
T

Tim Ferguson

varResults = rstPQA.GetRows(2 ^ 15)

I know it's taking the results of a query and loading them into an
array but what does the (2^15) do?
Click to expand...

The help file she say:
GetRows Method


Retrieves multiple records of a Recordset object into an array.

Syntax

array = recordset.GetRows( Rows, Start, Fields )

Return Value

Returns a Variant whose value is a two-dimensional array.

Parameters

Rows Optional. A GetRowsOptionEnum value that indicates the number
of records to retrieve. The default is adGetRowsRest.
Click to expand...

She then goes on to say
GetRowsOptionEnum


Specifies how many records to retrieve from a Recordset.

Constant Value Description
adGetRowsRest -1 Retrieves the rest of the records
in the Recordset, from either the
current position or a bookmark
specified by the Start parameter
of the GetRows method.
Click to expand...

At a guess, 2^15 is just used as a large number (it's 2 to-the-power-of 15,
otherwise known as 32768) and will guarantee that GetRows will get all the rows up
to a maximum of 32K. This may be a limit to prevent the Variant Array overflowing
but I haven't looked this up specifically. The "official" way to get all the
(remaining) records would be to pass -1 or adGetRowsRest.

If you only have eleven rows in your Dimmed array, then you might be wise to set the
Rows parameter to 11 -- or at the very least check the UBound() value of the
varResults returned.

Hope that helps


Tim F
 
M

MJatAflac

Thanks, that helps a lot. Unfortunately knowing that didn't help me
understand the next part of my proc. What I am doing is trying to dump the
array into an excel sheet range. I'm getting the array fine and when I look
at it in the immediate window it looks the way I would expect but when I dump
it into the range using formulaArray I get a type mismatch error. I'm
thinking that the shape/size of my range isn't matching the shape/size of my
array. Any idea how I can test this?

The other thing that I noticed that I didn't understand is that the array
I'm building is filled with the results of an sql string that is just a dump
of everything in a table. The table has 29 rows but my array only has 28.

Thanks,

m
 
Top