array question

[Nick I]

I have an array that can go up to 25 columns and 9 rows in size. I am taking
the contents of this array and placing the data into an Excel sheet through
VB.

Sometimes, however, the array may be only 24 or 23 columns in size since
there is no data in the last few columns. When I run my loop that fills the
data, it kicks out with an error "Subscript out of range".

Instead of getting this error, I would just like the function to place a "0"
in the Excel sheet. Is there a way to tell Access that if I get the
"Subscript out of range" error, to have to go ahead an just put a 0 where I
want?

I tried asking if the array is equal to Null....but apparently subscript out
of range and NULL are not the same thing.

Here is a portion of the code below:

NumberForCell = 0
Down = 0
Across = 2


'Outer loop will be the across loop

For Across = 2 To 25

Down = 0
Letter = LetterReturn(Across + 1)

For Down = 0 To 9

NumberForCell = Down + 14

If IsNull(aryData(Across, Down)) Then
.Cells.Range(Letter & CStr(NumberForCell)) = 0
Else
.Cells.Range(Letter & CStr(NumberForCell)) =
aryData(Across, Down)

End If

Next Down

Next Across

Thanks for your help!

Nick

 

[Klatuu]

The subscript out of range is not cause by there being no data in an array
element. It is caused because the index you are using is greater than the
number of elements that exist. I don't believe the problem is in how you are
retreiving data from the array, but how you are building the array. My guess
would be that either you are diminsioning the array incorrectly or you are
using ReDim to dynamically create the array and, in either case, there are
not the number of elements you expect or you are not indexing correctly.

I suggest you review VBA help for Declaring Arrays to ensure you are using
them correctly.

Now, one other hint.

If IsNull(aryData(Across, Down)) Then
.Cells.Range(Letter & CStr(NumberForCell)) = 0
Else
.Cells.Range(Letter & CStr(NumberForCell)) = aryData(Across, Down)
End If

can be replaced with

Nz(.Cells.Range(Letter & CStr(NumberForCell)) = aryData(Across, Down), 0)

 

[Nick I]

I know why I am getting the error, I guess I need to figure out a way to
handle it.

Using ReDim does not help, because I still have to fill the Excel sheet with
0's in column 25, even though there may not be any column 25 in the query.
See what I'm saying?

If I ReDim the array correctly to 24 when there are only 24 columns, I won't
get an error, but I won't get the 0's I need in column 25.

 

[Klatuu]

I see what you are saying. Why would the Redim Preserve not work?
That way, regardless of the number of columns in the query, you could Redim
the array without lossing any data.

 

[Nick I]

How would I do that?

I put this in my code and I still got a subscript error:

Dim aryData

aryData = objRST.GetRows(10)
ReDim Preserve aryData(UBound(aryData) + 4)

Only this time I got it before the loop. (FYI - In the case I am testing,
the objRST.GetRows(10) function will correspond to an array with 10 rows and
22 columns. Sometimes this column value could be up to 26).

Regardless of the output of my query....23, 24, or 25 columns....I have to
update 25 columns in the Excel sheet I am populating. If there is no data in
the array, then I need to put 0's in those spots.

I just can't seem to figure out how to code that last "If".

Is there a way I could do it through Error Handling? I am not familiar with
much Error Handling.

Thanks for your help Klatuu.

Nick

 

[Klatuu]

Your Redim is specifying only the number of rows, not the number of columns.

ReDim Preserve aryData(UBound(aryData) + 4, 25)

 

[deltris]

If your array is built from a query in Access it sounds like its coming from
a crosstab. You can normally tell the query to produce a specified number
of columns using the Columns Heading property for the query (for example you
have a query that returns info under columns headed 1, 2, 3 .... 22 etc.
Sometimes more or less) if you always want to ensure you have columns 1 to
25 simply edit the query (right click in query design) and specify 1,2,3 ...
,23,24,25 in the column heading line. That way you guarantee the size of the
returned array is always 25 columns, regardless of wether it has info or not
(no info could be checked by len function to get around datatypes etc using a
variant variable).

Hope this helps


T Delaney, Ireland.