This simple procedure loads the values in the range A1:A5 into an array. The Debug.Print statement prints the contents of the array in the Immedicate Window of Visual Basic Editor.

Sub LoadArrayFromRange()

    Dim arrRange()
    Dim Rng As Range

    x = 0

    For Each Rng In Range("A1:A5")
        ReDim Preserve arrRange(x)
        arrRange(x) = Rng.Value
        x = x + 1
    Next Rng

    For Each Item In arrRange
        Debug.Print Item
    Next

End Sub

I'm not sure where I found this so unfortunately I can't credit the original author.

The cell-by-cell approach can be much slower than the direct array approach.

Function LoadArray() As Variant
LoadArray = ActiveSheet.Range("A1:A5").Value
End Sub

Sub DumpArray(vArray As Variant)
ActiveSheet.Range("A1") _
.Resize(UBound(vArray, 1) + 1 - LBound(vArray, 1), _
UBound(vArray, 1) + 1 - LBound(vArray, 1) _
.Value = vArray
End Sub

P.S. Note that if you include the default member of Range, this line:

For Each Rng In Range("A1:A5")

becomes

For Each Rng In Range("A1:A5").Cells

In general I prefer including these defaults, because they help remind me what I’m doing.

This includes using this line

Dim arrRange As Variant

instead of

Dim arrRange

and this line

ActiveSheet.Range("A1:A5")

instead of

Range("A1:A5")

This last makes it easier to generalize code to the case in which the active sheet is not necessarily the sheet being manipulated. When I see ActiveSheet, I immediately think “Sheet, okay, what sheet is this?”, but if I only see Range, I don’t make the connection as reliably.

Thanks again Jon.  Your example of a direct array approach is much more efficient than looping through a cell range.  Using the defaults as you have described does document the code in a much more logical manner.

The most important role that documentation plays for me is reminding me when I review a bit of code, what I was thinking months before when I wrote the code. If I use all keywords, even the defaults, then I understand what I wrote more quickly.

I read a study one time that said most of a programmer’s time is spent understanding code, meaning reading and interpreting code, written by themselves or by others. Whatever you can do to shorten this step will give you more time to do productive work.