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

 

[mortgageman]

This may help. When I use MSgBox to find out the value of your upper bound, it seems that it is 1 (and not 5). I don't know why, but you can run this macro and see for yourself.

Edit: I meant 6 and not 5 (which is what I assumed you were thinking)

Gene Klein


Sub TestArrayRow()
Dim myArrayRow As Variant, j As Integer
Dim mylbound As Integer
Dim myubound As Integer

myArrayRow = Range("B1:G1")
mylbound = LBound(myArrayRow)
MsgBox "mylbound is " & mylbound
myubound = UBound(myArrayRow)
MsgBox "myubound is " & myubound
For j = LBound(myArrayRow) To UBound(myArrayRow)
MsgBox myArrayRow(j)
Next j
End Sub

 

[Richie(UK)]

Hi,

One tip - when working with Row variables you should use a Long data type rather than an Integer data type because the latter may not always be sufficient. See if changing j to a Long helps.

 

[TheNoocH]

Gene,
in my original code i stepped through and noticed that too...not sure why either...


Richie, changing it to long still did not fix it...


any other ideas?

 

[Greg Truby]

Nooch,

Use your view locals window to study the structure of your arrays. In your second case your array is double dimensioned.

<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 = Range("B1:G1")
    <SPAN style="color:#00007F">For</SPAN> j = <SPAN style="color:#00007F">LBound</SPAN>(myArrayRow, 2) <SPAN style="color:#00007F">To</SPAN> <SPAN style="color:#00007F">UBound</SPAN>(myArrayRow, 2)
        MsgBox myArrayRow(1, j)
    <SPAN style="color:#00007F">Next</SPAN> j
<SPAN style="color:#00007F">End</SPAN> <SPAN style="color:#00007F">Sub</SPAN>
</FONT>

 

[TheNoocH]

Greg that absolutely did the trick...
now i'm confused though...
i thought columns were two dimensional ...thus the TRANSPOSE....

PS...just for fun...how would i change hte testarrayCol one not to use transpose then? or is that the way i should do it?

Thanks again Greg...

 

[mdavide]

to declare array:
if you know the size use

dim test_array as ... where n =integrer
dim test_array(n,m) as ... where n =integrer and m =integrer

in order to refer to it you must use the index from 0 to n-1,so
test_array(0) is the first element and test_array(n-1) is the last one

also you can write in the module this line ption base 1
then you refer to it using the index from 1 to n

also you can declare the array:
dim test_array(a to b) where a<b
then you refer to it using the index from a to b

if you want to use a variable array you must define
dim test_array()
and then
redim test_array or
redim preserve test_array

ciao

DM

 

[jindon]

TheNoocH,

When you substitute the range value, the array will be multi-dimentional, no exception even in one row or one column.
And the data type must be variant.

If you have
A1=a, B1="B"
A2="c", B2="d"
then a=Range("a1:a2").Value means
a(1,1)="a",a(2,1)="c"

When a=Range("a1:b1").Value means
a(1,1)="a", a(1,2)="b"

as you know, lower bound begin from 1, not 0 for this type of substitution.

Whereas the array created by the functions like
Filter, Array, Split..
lower bound always begin from 0, even if you state Option Base 1, NO EXCEPTION.

hope this helps

 

[TheNoocH]

mdavide and jindon...thanks for the clarifications...

for fun i redid the column sub and got rid of the TRANSPOSE part...
this is what i came up with...

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

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...

any thoughts?

 

[TheNoocH]

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

Jindon,
when i run that it only returns the first element...
eg...i have a, b, c, d, e in A1:A5
it only returns a?