Can anyone recommend a book, website, etc. that provides good, thorough
information on arrays and using them in VBA? I just spent a few days
painfully bludgeoning my way through some code where I learned that I can't
ReDim Preserve the first dimension of a two dimension array, that I had to
declare certain arrays as variant in order to get them to work (with
Transpose, I think), etc. I'd prefer to learn these lessons through other
means ;-)

I did some Google searches but all I could find were tutorials on what an
array is, how to declare them, ReDim/Preserve basics, etc. I am looking for
something more in depth.

Tom,

Thanks for the info. I wasn't expecting an entire book on arrays, though
with all the books out there you just never know.

I don't know that it was the Transpose that caused the issue (and from your
example I suspect it is not). The problem was that I didn't know what I
didn't know. I am using GetPrivateProfile to return some ini keys and
values, removed the NULL values, split the 1D array of key=value into a 2D
array of keys and values, transposed that array, and put it in a range (all
through passing array to multiple function/sub calls). I put it all
together using code I found from Google/Newsgroups with limited
understanding of what I was doing (I tried, but this is the first time I
have dealt with arrays in any real way). I tried so many things and all I
know is that the code would compile but not run unless some of the arrays
were declared as variants (and the only two error messages I got, over and
over and over, were subscript out of range and type mismatch). I probably
declared one array as variant in a function and things propagated from
there. I'd post it but its a lot of code from several modules...I'll go
back and look at some things and try to narrow down the problem area first.

I downloaded Alan Beban's Array Functions and will work through the code and
will continue to ask questions here as needed. This place is better than
any book but learning is a bit disorganized this way, at least for me.
Sorry for the long post and sob story...at least I feel better now ;-)


"Tom Ogilvy" wrote in message
...
Sub ABCD()
Dim v1(1 To 10, 1 To 2) As Long
For i = 1 To 10
For j = 1 To 2
v1(i, j) = i * j
Next
Next
Range("A1:J2") = Application.Transpose(v1)
Dim v2(1 To 10, 1 To 2) As String
For i = 1 To 10
For j = 1 To 2
v2(i, j) = Chr(i + j + 64)
Next
Next
Range("A3:J4") = Application.Transpose(v2)
End Sub

both work and neither involves a variant.

Perhaps you could be more specific on this restriction about Transpose.

You might find a couple of pages in someone's book on Arrays, but it isn't
a
topic large enough by itself. Information on Redim is right in the help
as
is most of what you need to know about arrays.

--
regards,
Tom Ogilvy


"M. Authement" wrote:

Can anyone recommend a book, website, etc. that provides good, thorough
information on arrays and using them in VBA? I just spent a few days
painfully bludgeoning my way through some code where I learned that I
can't
ReDim Preserve the first dimension of a two dimension array, that I had
to
declare certain arrays as variant in order to get them to work (with
Transpose, I think), etc. I'd prefer to learn these lessons through
other
means ;-)

I did some Google searches but all I could find were tutorials on what an
array is, how to declare them, ReDim/Preserve basics, etc. I am looking
for
something more in depth.