I am trying to write an excel function in VBA that can has a 9 numbe
array as a variable and returns a 9 number array in the column t
variable array's right. I have been unable to find a way to write thi
function properly. Any help (even just webpages with descriptions) i
greatly appreciated. Thank

--
Message posted from http://www.ExcelForum.com

neebington,

See the sample code below, which would be used in the worksheet by first
selecting cells in a row or column, let's say B1:B9, and then entering

=ReturnArray2(A1:A9)

(Where A1:A9 is your 9 number array in a column) and using Ctrl-Shift-Enter
to enter it as an array formula. The sample code simply doubles the input
values - you never said what transformation you wanted, so....

HTH,
Bernie
MS Excel MVP

Function ReturnArray2(InRange As Range) As Variant
'Choose adjacent cells and enter =ReturnArray2(InputRangeAddress)
'with Ctrl-Shift-Enter to get the array of doubled values returned
Dim ReturnVals() As Variant
Dim i As Integer

ReDim ReturnVals(1 To InRange.Cells.Count)
For i = 1 To InRange.Cells.Count
ReturnVals(i) = InRange(i).Value * 2
Next i

If Application.Caller.Rows.Count = 1 Then
ReturnArray2 = ReturnVals
Else
ReturnArray2 = Application.Transpose(ReturnVals)
End If
End Function


"neebington " wrote in message
...
I am trying to write an excel function in VBA that can has a 9 number
array as a variable and returns a 9 number array in the column to
variable array's right. I have been unable to find a way to write this
function properly. Any help (even just webpages with descriptions) is
greatly appreciated. Thanks


---
Message posted from http://www.ExcelForum.com/

You can't specify where a function returns its values - that depends on
where its called from.

As an example, if you had values in A1:A9, you could select B1:B9 and
array enter

=MyFunc(A1:A9)

Public Function MyFunc(rRange As Range) As Variant
Dim vTemp As Variant
Dim i As Long
Dim j As Long
With Application.Caller
If .Rows.Count = rRange.Rows.Count And _
.Columns.Count = rRange.Columns.Count Then
'Do something
ReDim vTemp(1 To rRange.Rows.Count, _
1 To rRange.Columns.Count)
For i = 1 To UBound(vTemp, 1)
For j = 1 To UBound(vTemp, 2)
vTemp(i, j) = rRange(i, j) * 2
Next j
Next i
MyFunc = vTemp
Else
MyFunc = CVErr(xlErrValue)
End If
End With
End Function


In article ,
neebington wrote:

I am trying to write an excel function in VBA that can has a 9 number
array as a variable and returns a 9 number array in the column to
variable array's right. I have been unable to find a way to write this
function properly. Any help (even just webpages with descriptions) is
greatly appreciated. Thanks