Excel VBA Array Function...What's wrong?

[Spatters71]

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

 

[Hong Quach]

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

 

[Jim Cone]

I cleaned up your function and also provided a shorter version...
'--

'Used to call the functions - "2" and "22"
Sub TestTheRangeFunctions()
Dim x As Variant
x = Range22Array(Selection)
MsgBox x(3, 2)

x = Range2Array(Selection)
MsgBox x(3, 2)
End Sub
'-----------

Function Range2Array(ByRef Source As Range) As Variant
Dim rowCount As Long
Dim colCount As Long
Dim Result() As Variant
Dim i As Long
Dim j As Long

If TypeName(Selection) <> "Range" Then Exit Function
rowCount = Source.Rows.Count
colCount = Source.Columns.Count
ReDim Result(1 To rowCount, 1 To colCount) As Variant

'Load result array
For i = 1 To rowCount
For j = 1 To colCount
Result(i, j) = Source(i, j).Value
Next j
Next i
'Return result array
Range2Array = Result
End Function
'--

'The shorter version...
Function Range22Array(ByRef Source As Range) As Variant
Dim Result As Variant
If TypeName(Selection) <> "Range" Then Exit Function
Result = Source.Value
Range22Array = Result
End Function
--
Jim Cone
Portland, Oregon USA




"Spatters71" <[email protected]>
wrote in message
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

 

[Dave Peterson]

I think that you missed the most important line in your code. You have to tell
it what the function returns:

Range2Array = Result
instead of:
Test = Result

Option Explicit
Function Range2Array(Source As Range) As Variant
Dim rowCount As Long
Dim colCount As Integer
Dim Result() As Variant
Dim i As Long
Dim j As Long

'single area check
Set Source = Source.Areas(1)

rowCount = Source.Rows.Count
colCount = Source.Columns.Count

' Ensure target is a range equal in size to source
If TypeOf Application.Caller Is Range Then
If Application.Caller.Columns.Count <> colCount _
And Application.Caller.Rows.Count <> rowCount Then
Range2Array = CVErr(xlErrRef)
Exit Function
End If
End If

' Load result array
ReDim Result(1 To rowCount, 1 To colCount)

'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
Range2Array = Result
End Function

I also declared your variables as long instead of integers and variants.

dim i,j as long
declares i as a variant and j as a long.

======
And if you really just wanted to pick up the values in a single area range, you
could use:

dim myArr as variant 'no ()'s here
myarr = source.value
or
myarr = source.areas(1).value

This results in a two dimensional array (x rows by y columns). It's even 2
dimensional if you passed it a single column (x rows by 1 column).

Kind of like:
dim myArr(1 to 99, 1 to 1)

 

[Spatters71]

Good answers, all. FYI the "Test =" line was just a symptom of the fact that
I renamed my Function from "Test" before I posted it.

Range.Value returns an array, that's handy

Anyway, thanks, I'll try these mods in the morning.