Debug Print Dynamic Array.

[farmerscott]

Hi Everybody,

I am trying to get a list of the array elements in the intermediate window.

The code is erroring (code 9) on the debug.Print line.

The elements are being loaded into the array as I am getting the correct value for the variable x.

Sub Data()

Dim lr As Long
Dim x As Long
Dim i As Long
Dim a As Variant
Dim arr() As Variant

Dim b As Long
ScreenUpdating = False

Worksheets("Sheet1").Select

lr = Cells(Rows.Count, "A").End(xlUp).Row

ReDim Preserve arr(1 To lr)
arr = Range("A1:A" & lr)
x = UBound(arr, 1) - LBound(arr, 1) + 1



For i = 0 To UBound(arr)
   
Debug.Print arr(i) [COLOR=#ff0000]-erroring.[/COLOR]
Next i

End Sub

thanks for the help.

FarmerScottt

 

[Jerry Sullivan]

Hi FarmerScott,

The error is because arr is an array with 2 dimensions and your debug.print statement doesn't include the second dimension index. Also, the lower bounds of an array assigned by passing range values will always be 1 instead of 0.

There are some other parts of the code that are incorrect or unnecessary. Here is a streamlined version.

Sub Data()

 Dim lr As Long
 Dim i As Long
 Dim arr() As Variant

 With Worksheets("Sheet1")
   lr = .Cells(.Rows.Count, "A").End(xlUp).Row
   arr = .Range("A1:A" & lr).Value
 End With

 For i = LBound(arr, 1) To UBound(arr, 1)
   Debug.Print arr(i, 1)
 Next i
End Sub

 

[farmerscott]

Hi Jerry,

I appreciate your work on my code.

I know I had some redundant lines of code in there (as I was using then as a cross check- variable x).

If I may ask a few questions from your comments-

1. can I use the same Dim statement to only declare a 1 dimension array?

2. why don't I have to ReDim the array once I have loaded the data into the array?

3. have you got a good website for learning the basics on arrays?

4. does this code work (note the different debug print statement for the array) because it was declared as a 1 dimension array at the beginning?

Sub test_Macro_Array()

Dim sAnimal(1 To 3) As String
sAnimal(1) = "Cat"
sAnimal(2) = "Dog"
sAnimal(3) = "Mouse"
For i = 1 To 3
  Debug.Print sAnimal(i)
Next i
End Sub

Source:https://www.mrexcel.com/forum/excel-questions/654641-vba-loop-through-array-strings.html, post#4

5.

Also, the lower bounds of an array assigned by passing range values will always be 1 instead of 0.

Certainly did not know this... any reason why not?

Again appreciate your work.

FarmerScott

 

[Jerry Sullivan]

1. can I use the same Dim statement to only declare a 1 dimension array?

There are two kinds of arrays: Static Arrays, and Dynamic Arrays

Static Array are sized in the Dim statement. Your question #4 shows an example of sizing a 1-D Static Array in the Dim statement:

Dim sAnimal(1 To 3) As String

Dynamic Arrays are not sized in the Dim statement. In this example, the size and number of dimensions is not set.

Dim sAnimal() As String

A Dynamic Array can be sized later using one of several methods including a using a ReDim statement or passing the values from a Range object.

2. why don't I have to ReDim the array once I have loaded the data into the array?

The process of passing the values from a Range object to a Dynamic Array variable automatically ReDim's the Dynamic Array to have 2 dimensions with the same size as the group of passed values.

3. have you got a good website for learning the basics on arrays?

snb's website is very good for learning the basics:
http://www.snb-vba.eu/VBA_Arrays_en.html

Chip Pearson's array function library is good for intermediate or advanced coders:
http://www.cpearson.com/excel/vbaarrays.htm

4. does this code work (note the different debug print statement for the array) because it was declared as a 1 dimension array at the beginning?

Sub test_Macro_Array()

Dim sAnimal(1 To 3) As String
sAnimal(1) = "Cat"
sAnimal(2) = "Dog"
sAnimal(3) = "Mouse"
For i = 1 To 3
  Debug.Print sAnimal(i)
Next i
End Sub

Source:https://www.mrexcel.com/forum/excel-questions/654641-vba-loop-through-array-strings.html, post#4

Yes. It works because it is Dimensioned as a 1-D Static Array.

5. Certainly did not know this... any reason why not?

The designers of VBA for Excel chose to use LBounds of 1 as a convention for Dynamic Arrays ReDim'd and sized by passing 2-D values from a Range object. I presume that was to correspond to the number of Rows and Columns, but I'm not sure that's the reason.

 

[farmerscott]

Thanks Jerry.

You explained it well.

I will take it all on board.

cheers,

FarmerScott