V
vba.NetPlease
Hi all,
I know im no expert with vb(a) but i consider myself mildly competant and i
have come across something i have never seen before.
I have an excel spreadsheet that pulls data from an SQL database and i use
the 'Getrows' function to push this data into an array, then i step through
the array etc... For those wanting to know why i use this function its a
module i built for another sheet that uses realtime minute by minute data
over a period of 1 month. It uses base / standby / excess / total / and avg
figures per min - thats 44700 * 5 loops. Getrows gives acceptable performance.
Anyways - The error i get in my other spreadsheet is subscript out of range.
Of course i know why this is occuring.
The sheet pulls gas trades into aRS1 for a particular day. It then loops
through aRS1 and aggregates these trades into aaRS1 (agregated array #1).
However in this particular case the first aRS1 is empty and therefore does
not redim the second nor enter any values into aaRS1.
A watch on aaRS1 throughout all modules and procedures displays "Empty".
However the test i use to skip:
If Not IsEmpty(aaRS1) Then
Will continue. So ?IsEmpty(aaRS1) into the intemediate window displays the
value "FALSE" meaning the array is not empty. The watch window at this time
displays:
Watch : : aaRS1 : <Out of context> : Variant/Empty : <insert any module /
procedure name here>
Now the watch is defined as all modules all procedures and stays the same no
matter where in the code your at. Interestingly aRS1 displays this:
Watch : : aRS1 : Empty : Variant/Empty :
From my perspective nothing is wrong with this code. I beleive this is a bug
or limitation with vba.
How the first array can be empty, which is the array that populates the
second, and the second is not until you try to use it is beyond my
understanding.
To step over this error i had to err handle.
If aRS1 contains values its fine, if thats blank it fails.
I know im no expert with vb(a) but i consider myself mildly competant and i
have come across something i have never seen before.
I have an excel spreadsheet that pulls data from an SQL database and i use
the 'Getrows' function to push this data into an array, then i step through
the array etc... For those wanting to know why i use this function its a
module i built for another sheet that uses realtime minute by minute data
over a period of 1 month. It uses base / standby / excess / total / and avg
figures per min - thats 44700 * 5 loops. Getrows gives acceptable performance.
Anyways - The error i get in my other spreadsheet is subscript out of range.
Of course i know why this is occuring.
The sheet pulls gas trades into aRS1 for a particular day. It then loops
through aRS1 and aggregates these trades into aaRS1 (agregated array #1).
However in this particular case the first aRS1 is empty and therefore does
not redim the second nor enter any values into aaRS1.
A watch on aaRS1 throughout all modules and procedures displays "Empty".
However the test i use to skip:
If Not IsEmpty(aaRS1) Then
Will continue. So ?IsEmpty(aaRS1) into the intemediate window displays the
value "FALSE" meaning the array is not empty. The watch window at this time
displays:
Watch : : aaRS1 : <Out of context> : Variant/Empty : <insert any module /
procedure name here>
Now the watch is defined as all modules all procedures and stays the same no
matter where in the code your at. Interestingly aRS1 displays this:
Watch : : aRS1 : Empty : Variant/Empty :
From my perspective nothing is wrong with this code. I beleive this is a bug
or limitation with vba.
How the first array can be empty, which is the array that populates the
second, and the second is not until you try to use it is beyond my
understanding.
To step over this error i had to err handle.
If aRS1 contains values its fine, if thats blank it fails.