Prb: Writing Array functions in VBA
 

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

Prb: Writing Array functions in VBA
 

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

Writing Array functions in VBA
 

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/