VBA: questions about working with arrays

[BIGTONE559]

Greetings,

I'm trying to learn how to work with arrays. in this sample that i wrote i keep receiving an error at the "Msgbox MyArray(i)" line. the goal is to have the msgbox cycle through all of the values in the array. the error is "Subscript out of Range"

Sub Sample()
Dim i As Integer


Dim MyArray() As Variant
MyArray = Range("MyDates")
For i = 1 To UBound(MyArray)
MsgBox MyArray(i)
Next i

End Sub

All help is appreciated.

 

[JLGWhiz]

Greetings,

I'm trying to learn how to work with arrays. in this sample that i wrote i keep receiving an error at the "Msgbox MyArray(i)" line. the goal is to have the msgbox cycle through all of the values in the array. the error is "Subscript out of Range"

Sub Sample()
Dim i As Integer
 
 
Dim MyArray() As Variant
MyArray = Range("MyDates")
For i = 1 To UBound(MyArray)
MsgBox MyArray(i)
Next i
 
End Sub

All help is appreciated.

I got this modified version to work:

Code:
Sub Sample2()
Dim i As Integer
Dim MyArray() As Variant
MyArray = Application.Transpose(Range("myDates"))
For i = 1 To UBound(MyArray)
MsgBox CDate(MyArray(i))
Next i
End Sub
Code:

 

[JoeMo]

What you have defined is NOT a 1-D array, but a 2-D array, so you need to tell Excel which dimension of the array you want returned in the msgbox. Like this (assumes MyDates is an N row x 1 Column array):

Sub Sample()
Dim i As Integer
Dim MyArray() As Variant
MyArray = Range("MyDates") 'this is a 2-D array NOT 1-D
For i = 1 To UBound(MyArray, 1)
    MsgBox MyArray(i, 1)
Next i

End Sub

Suppose your range "MyDates" is A1:A4
then MyArray(1,1)= the value in A1; MyArray(2,1) = the value in A2 and so on. MyArray is a 4 rows X 1 column array.

 

[JLGWhiz]

Also, if you want to make it a string value:

Code:
Sub Sample3()
Dim i As Integer
Dim MyArray() As Variant
MyArray = Application.Transpose(Range("myDates"))
For i = 1 To UBound(MyArray)
MsgBox Format(MyArray(i), "mm/dd/yyyy")
Next i
End Sub
Code:

 

[BIGTONE559]

I got this modified version to work:

Code:
Sub Sample2()
Dim i As Integer
Dim MyArray() As Variant
MyArray = Application.Transpose(Range("myDates"))
For i = 1 To UBound(MyArray)
MsgBox CDate(MyArray(i))
Next i
End Sub
Code:

Worked Perfectly... I noticed you used application.transpose to change a range to array values. will i have to use this method to change range values in the future?

 

[BIGTONE559]

What you have defined is NOT a 1-D array, but a 2-D array, so you need to tell Excel which dimension of the array you want returned in the msgbox. Like this (assumes MyDates is an N row x 1 Column array):

Sub Sample()
Dim i As Integer
Dim MyArray() As Variant
MyArray = Range("MyDates") 'this is a 2-D array NOT 1-D
For i = 1 To UBound(MyArray, 1)
    MsgBox MyArray(i, 1)
Next i

End Sub

Suppose your range "MyDates" is A1:A4
then MyArray(1,1)= the value in A1; MyArray(2,1) = the value in A2 and so on. MyArray is a 4 rows X 1 column array.

JoeMO I just seen your response and i have some questions. What do you mean by a 2-D array (i'm assumming dimensions????). does it change if the list was in rows as oppose to columns e.g. data in A1:D1. If i wanted value C1 MyArray(3,1)?

How would you loop through multiple arrays?

e.g. two sets of different arrays MyDates and My Colors (somewhat different from the original question)

 

[BIGTONE559]

What you have defined is NOT a 1-D array, but a 2-D array, so you need to tell Excel which dimension of the array you want returned in the msgbox. Like this (assumes MyDates is an N row x 1 Column array):

Sub Sample()
Dim i As Integer
Dim MyArray() As Variant
MyArray = Range("MyDates") 'this is a 2-D array NOT 1-D
For i = 1 To UBound(MyArray, 1)
    MsgBox MyArray(i, 1)
Next i

End Sub

Suppose your range "MyDates" is A1:A4
then MyArray(1,1)= the value in A1; MyArray(2,1) = the value in A2 and so on. MyArray is a 4 rows X 1 column array.

JoeMO I just seen your response and i have some questions. What do you mean by a 2-D array (i'm assumming dimensions????). does it change if the list was in rows as oppose to columns e.g. data in A1:D1. If i wanted value C1 MyArray(3,1)?

How would you loop through multiple arrays?

e.g. two sets of different arrays MyDates and My Colors (somewhat different from the original question)

"For Each value in MyArray

For Each Element in MyArray1 ' pertains to MyColors "

 

[JoeMo]

A1:D1 would be a 1 row X 4 column array so value in C1 would be
MyArray(1,3) and to read out all values via your msgbox you would use:

For i = 1 to Ubound(MyArray,2)

where Ubound(MyArray, 2) is 4 in this example and Ubound(MyArray,1) is 1.

To loop through array of this type (where the array is dim'd as a variant and then set = the values in a contiguous range):

for i = Lbound(MyArray,1) to Ubound(MyArray,1)
msgbox MyArray(i,1)
next i
will produce the values in the first column and ith row.

To go across a row
For i = Lbound(MyArray,2) to Ubound(MyArray,2)
msgbox MyArray(1,i)next i
next i

To loop through all values (read out row by row):
For i = Lbound(MyArray,1) to Ubound(MyArray,1)
For j = Lbound(MyArray,2) to Ubound(MyArray,2)
msgbox MyArray(i,j)
Next j
Next i


BTW for this type of array, the Lbound is always 1.

 

[BIGTONE559]

A1:D1 would be a 1 row X 4 column array so value in C1 would be
MyArray(1,3) and to read out all values via your msgbox you would use:

For i = 1 to Ubound(MyArray,2)

where Ubound(MyArray, 2) is 4 in this example and Ubound(MyArray,1) is 1.

To loop through array of this type (where the array is dim'd as a variant and then set = the values in a contiguous range):

for i = Lbound(MyArray,1) to Ubound(MyArray,1)
msgbox MyArray(i,1)
next i
will produce the values in the first column and ith row.

To go across a row
For i = Lbound(MyArray,2) to Ubound(MyArray,2)
msgbox MyArray(1,i)next i
next i

To loop through all values (read out row by row):
For i = Lbound(MyArray,1) to Ubound(MyArray,1)
For j = Lbound(MyArray,2) to Ubound(MyArray,2)
msgbox MyArray(i,j)
Next j
Next i


BTW for this type of array, the Lbound is always 1.

JoeMO there are times when things happen in life and that little light bulb above your head goes off. You sir. . . have just help that take place for me. I appreciate your responses and i will take this info and do some great things.

Thanks Again!!!!!!!!

 

[GTO]

Hi Big,

Not sure if this will add anything, but I think a good way to see what is happening is to use the Locals window and step through the code. For instance, with some values like:

Excel 2000
	A	B
1	2.3	1.15
2	4.6	2.3
3	6.9	3.45
4	9.2	4.6
5	11.5	5.75
6	13.8	6.9
7	16.1	8.05
8	18.4	9.2
9	20.7	10.35
10	23	11.5
Sheet1

Display the Locals window, ditch the parenthesis for the moment and step thru:

Sub example()
Dim MyArray
    
    MyArray = Sheet1.Range("A1:A10").Value
    MyArray = Sheet1.Range("A1:B1").Value
    MyArray = Sheet1.Range("A1:B10").Value
    MyArray = Sheet1.Range("A1").Value
    
    MyArray = Application.Transpose(Sheet1.Range("A1:A10").Value)
    
    MyArray = Application.Transpose(Sheet1.Range("A1:B10").Value)
End Sub