 Search:

# How can I convert a Range to an Array?

Asked By: Carolina    Date: Dec 10    Category: MS Office    Views: 1687

I am writting a function which will take range as an argument and
will store the value in the range in an array for further
manupulation.
A part of the code is below:

Function elastrate(Elast As Double, Elasticity As Range, Rate As
Range) As Double
Dim duration(1 To 6) As Double

duration(1 to 6) = Elasticity

Dim TRate(1 To 6) As Double ' the second way I was trying to convert
range to an array
For i = 1 To 6
TRate(i) = Rate(i)
Next i

Share:

The following function  will take a range  argument and store  the values in its
cells in an array. Please note that if you declare the array  within the
function, it ceases to exist when the function ends.

Option Explicit

Dim Trate() As Double, MaxIdx As Long

Sub AAAAA()
Dim n As Long
If Rng2Array(Selection) Then
For n& = 1 To MaxIdx&
MsgBox Trate(n&)
Next n&
End If
End Sub

Function Rng2Array(Rng As Range) As Boolean
Dim c As Range
On Error GoTo R2Aerr
ReDim Trate(Rng.Cells.Count)
MaxIdx& = 0
For Each c In Rng
MaxIdx& = MaxIdx& + 1
Trate(MaxIdx&) = c.Value
Next c
Rng2Array = True
Exit Function
R2Aerr:
Rng2Array = False
End Function

Also... If you declare the array  as an argument  of the sub then it
will alter the array that is passed, effectively "returning" the
altered array as a function  would.

Of course you are right, and many programmers prefer to avoid using
global/public variables. Passing the array  as an argument, the code  could be
rewritten:

Option Explicit

Sub AAAAA()
Dim n As Long, Trate() As Double
If Rng2Array(Selection, Trate()) Then
For n& = 1 To Selection.Cells.Count
MsgBox Trate(n&)
Next n&
End If
End Sub

Function Rng2Array(Rng As Range, InArray() As Double) As Boolean
Dim c As Range, x As Long
On Error GoTo R2Aerr
ReDim InArray(Rng.Cells.Count)
x& = 0
For Each c In Rng
x& = x& + 1
InArray(x&) = c.Value
Next c
Rng2Array = True
Exit Function
R2Aerr:
Rng2Array = False
End Function

Didn't find what you were looking for? Find more on How can I convert a Range to an Array? Or get search suggestion and latest updates.