Assign Range passed to fuction to an array

[Michael]

I am looking for an example of how to pass a function a range and
assign it to an array so I can access the values with an index number.
For some reason I cant get this to work.

 

[Michael]

I am looking for an example of how to pass a function a range and
assign it to an array so I can access the values with an index number.
For some reason I cant get this to work.

Forgot to indclude example

Function testfunc2(rng As Range)
Dim r As Variant
Dim arr As Variant
Dim x As Integer

'arr = rng

'assign range values to array
For Each r In rng
arr(x) = r.Value
x = x + 1
Next

'list array values
For x = LBound(arr) To UBound(arr)
Debug.Print arr(x).Value
Next x

testfunc2 = "test"
End Function

 

[Mike H]

Michael,

This may do what you want. take this simple function below. Called with a
range object you can refer to each cell of the range using .item.

I've included 2 message boxes in the function to demonstrate the method but
in practice these would be replaced with your code.

=myvalue(A1:A10)

Public Function MyValue(rng As Range) As Double
Dim x As Long
With rng
For x = 1 To .Count
MsgBox .Item(x).Address
MsgBox .Item(x).Value
Next x
End With
End Function


Mike

 

[Per Jessen]

Hi

Try this:

MyArray = Array(Range("A1:A10").Value)

Regards,
Per

 

[Mike H]

Michael,

Your function simplified

=testfunc2(A1:A10)

Function testfunc2(rng As Range) As Double
Dim r As Variant
Dim arr As Variant
Dim x As Integer

For x = 1 To rng.Count
Debug.Print rng.Item(x).Value
Debug.Print rng.Item(x).Address
Next x

End Function


Mike