Basic help with Arrays in VBA

[TheNoocH]

I'm trying to learn arrays in VBA and am testing some basic concepts...
i have the following code in which the TestArrayCol sub is working but the TestArrayRow is not....getting a subscipt out of range error on the msgbox myarrayrow(j) line...i'm sure i'm overlooking something simple...any help would be appreciated...

Sub TestArrayCol()
Dim myArrayCol As Variant, i As Integer
myArrayCol = WorksheetFunction.Transpose(Range("A1:A5"))
For i = LBound(myArrayCol) To UBound(myArrayCol)
MsgBox myArrayCol(i)
Next i
End Sub

Sub TestArrayRow()
Dim myArrayRow As Variant, j As Integer
myArrayRow = Range("B1:G1")
For j = LBound(myArrayRow) To UBound(myArrayRow)
MsgBox myArrayRow(j)
Next j
End Sub

 

[jindon]

in the lbound/ubound section if i did lbound(myarraycol1,2) to ubound(myarraycol1, 2)
it only returned the first element...

Of course, because your counter is always 1,
LBound(myarraycol1,2)=1 and UBound(myarraycol1,2)=1

the size of your array is
myarraycola(1 to 5, 1 to 1)

does it explain?

 

[jindon]

So the code should look like

Sub TestArrayCol1() 
Dim myArrayCol1 As Variant, i1 As Integer 
myArrayCol1 = Range("A1:A5") 
For i1 = LBound(myArrayCol1,1) To UBound(myArrayCol1,1) 
MsgBox myArrayCol1(i1, 1) 
Next i1 
End Sub

if you want to dispplay a,b,c,d,e

 

[TheNoocH]

Jindon,
that's the code i had...see my last post with code...

but the question i didn't understand was

in the lbound/ubound section if i did lbound(myarraycol1,2) to ubound(myarraycol1, 2)
it only returned the first element...
especially weird since in the msgbox line i needed it to look like a 2 dimensional array...ie MsgBox myArrayCol1(i1, 1) ..

 

[Greg Truby]

Nooch, run this with your view locals window open (or a watch on myArrayRow). Tile apps so you can see what's going on in the worksheet. Step through the code. You'll see how you can use the TRANPOSE to force into a single-dimensioned array. And you can see what happens when you need to feed a single dimensioned array back to cells.

<font face=Courier New><SPAN style="color:#00007F">Sub</SPAN> TestArrayRow()
    <SPAN style="color:#00007F">Dim</SPAN> myArrayRow <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Variant</SPAN>, j <SPAN style="color:#00007F">As</SPAN> <SPAN style="color:#00007F">Integer</SPAN>
    myArrayRow = WorksheetFunction.Transpose(Range("B1:G1"))
    myArrayRow = WorksheetFunction.Transpose(WorksheetFunction.Transpose(Range("B1:G1")))
    
    <SPAN style="color:#00007F">For</SPAN> j = <SPAN style="color:#00007F">LBound</SPAN>(myArrayRow) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(myArrayRow)
        myArrayRow(j) = j * 5
    <SPAN style="color:#00007F">Next</SPAN> j
    Range("b1:G1") = myArrayRow
    Range("B2:B7") = myArrayRow
    Range("B2:B7") = WorksheetFunction.Transpose(myArrayRow)
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN></FONT>
HTH

 

[jindon]

OK

What I was trying to say is
2nd dimention of your array is what you have in 1st dimention.

You have 1 to 5 in the 1st dimention and
1 to 1 in the 2nd dimention

so, you only have 1 element for each index of 1 st demention

say if you substitute "a1:c5" then you have
1 to 5 in 1st dimention and 1 to 3 in 2nd dimention

You know what I mean?

 

[TheNoocH]

Jindon/Greg....thanks...i THINK i got it now...i appreciate all your help...
i actually just used the knowledge you guys helped me acquire to do something fun in this post...

http://www.mrexcel.com/board2/viewtopic.php?p=780934#780934

Thanks again...i'm sure as i try to apply it more i'll have more questions...but i'll post specifics in new post as they arise...