Solved: Fill an Array based on Range Values

[CreganTur]

This is an annoyingly simple concept...but so far I have had no luck implementing it.

I want to fill an array named arrType() with the values of the cells that make up Range("A40:A45").

Can someone point me in the right direction?

[Kenneth Hobs]

You can set the range equal to a variant. Depending on the range, you may need to use WorkSheetFunction.Transpose.

Here is something that I did to fill an array with constants though looping is a good way too.
[vba]Option Explicit

Sub Test1dArray()
Dim a(1 To 10) As Variant, b As Variant
Dim r As Integer

b = InitArray(a, "Hello World!")

For r = 1 To 10
Debug.Print "b(" & r & ")", b(r)
Next r
End Sub

Sub Test2dArray()
Dim a(1 To 10, 1 To 5) As Variant, b As Variant
Dim r As Integer, c As Integer

b = InitArray(a, "Hello World!")

For r = 1 To 10
For c = 1 To 5
Debug.Print "b(" & r & "," & c & ")", b(r, c)
Next c
Next r
End Sub


Function InitArray(ByRef MyArray As Variant, val As Variant) As Variant
Dim w As Worksheet
Dim r As Range

Set w = ThisWorkbook.Worksheets.Add()

On Error Resume Next
Range("A1").Resize(UBound(MyArray, 1), 1) = val
Range("A1").Resize(UBound(MyArray, 1), UBound(MyArray, 2)) = val
Set r = w.UsedRange


If Err = 9 Then
InitArray = WorksheetFunction.Transpose(r)
Else: InitArray = r
End If

Set r = Nothing
Application.DisplayAlerts = False
w.Delete
Application.DisplayAlerts = True
Set w = Nothing

End Function[/vba]

[Bob Phillips]

[vba]

Dim arrType As Variant

arrType = Range("A40:A45")
[/vba]

or, if you want a single dimension array

[vba]

Dim arrType As Variant

arrType = Application.Transpose(Range("A40:A45"))
[/vba]

[CreganTur]

I must be missing something, because this isn't working for me.

I'm unable to iterate through the array values. Here's what I've got so far:
[VBA]Sub ArrayTest()
Dim arrType() As Variant
Dim i As Integer

arrType = Application.Transpose(Range("A40:A45"))

i = 0
Do Until i > 5
Debug.Print arrType(i)
i = i + 1
Loop

End Sub[/VBA]

I keep getting Run-Time error 9 (subscript out of range)- The debugger highlights this line: 'Debug.Print arrType(i)'

[Bob Phillips]

[vba]

Sub ArrayTest()
Dim arrType() As Variant
Dim i As Integer

arrType = Application.Transpose(Range("A40:A45"))

i = LBound(arrType)
Do Until i > UBound(arrType)
Debug.Print arrType(i)
i = i + 1
Loop

End Sub
[/vba]

[CreganTur]

[vba]

Sub ArrayTest()
Dim arrType() As Variant
Dim i As Integer

arrType = Application.Transpose(Range("A40:A45"))

i = LBound(arrType)
Do Until i > UBound(arrType)
Debug.Print arrType(i)
i = i + 1
Loop

End Sub
[/vba]

That works perfectly... but I can't understand why. What is the difference between using the LBound and UBound functions, and me manually setting the value of 'i'?

[Bob Phillips]

Because the lower bound was 1 not 0. It is ALWAYS safer to use the bounds than to make assumptions about what the bounds are.

I posted about this the other day on another thread, but someone responded with how to avoid it, thereby obliterating the impact of my comment.

[CreganTur]

Because the lower bound was 1 not 0. It is ALWAYS safer to use the bounds than to make assumptions about what the bounds are.

Okay, that makes sense- I'm just surprised because I'm not using 'Base 1', so I did assume that the array would start with 0.

Thanks for the explination!

[Bob Phillips]

Even when setting Option Base, some arrays will always start at 0, some will always start at 1. I repeat, it is not safe to make assumption, it is ALWAYS safer to use the bounds.