Hi Spatters71,

I tested out your code and the only thing I change is the 2nd line to the
last "Test = Result" to "Range2Array = Result"

The way I test your function is to start a new Excel file VB Editor
Insert New Module Paste your code into it Change "Test" to "Range2Array"
Return to Excel Enter arbitrary data into range A1:A25 hight light
range C1:C25 type in "=Range2Array(A1:A25)" without quote Hold Ctrl +
Shift and press Enter Data in range C1:C25 now has the same content as
range A1:A25.

I also use the step through and confirmed that After "Result(i, j) =
Source(i, j).Value" Result is not Empty.

Remember the way to get the result value from the function is through the
name of the function itself.

Hong Quach

"Spatters71" wrote:

I am just trying to create an array function that returns an array containing
the values in a source Range. It shouldn't be that difficult, right? My
source is below. I am running into the problem on this line: "Result(i, j) =
Source(i, j).Value". The Result element is Empty after the assignment, even
though Source(i,j).Value is not Empty. Help!? If you know why this assignment
isn't working or if you know a better way to do this, please let me know.

Function Range2Array(Source As Range) As Variant
Dim rowCount, colCount As Integer
rowCount = Source.Rows.count
colCount = Source.Columns.count

' Ensure target is a range equal in size to source
If Not (TypeOf Application.Caller Is Range _
And Application.Caller.Columns.count = colCount _
And Application.Caller.Rows.count = rowCount _
) Then
Exit Function
End If


' Load result array
Dim Result() As Variant
ReDim Result(1 To rowCount, 1 To colCount) As Variant
Dim i, j As Integer

Result(1, 1) = "Test"

For i = 1 To rowCount
For j = 1 To colCount
' At this point Source(i,j).Value is not Empty
Result(i, j) = Source(i, j).Value
' At this point the value of Result is Empty.
Next j
Next i

' Return result array
Test = Result
End Function