Array UDF
Your code is only going to work for an array function entered into a single row of cells. Also, if you are using the default zero
based arrays, rk will contain K+1 elements.
A more general treatment of rows and columns is shown below where Application.Caller is used to discover the range into which the
array function is entered and the array is filled with numbers. It works with single row, single column arrays as well as arrays
with multiple rows and columns.
Function rkArray() As Variant
Dim rk()
Dim rArray As Range
Dim NumRows As Long
Dim NumColumns As Long
Dim i As Long
Dim j As Long
Set rArray = Application.Caller
NumRows = rArray.Rows.Count
NumColumns = rArray.Columns.Count
ReDim rk(1 To NumRows, 1 To NumColumns)
For i = 1 To NumRows
For j = 1 To NumColumns
rk(i, j) = i + j
Next j
Next i
rkArray = rk
End Function
--
John Green - Excel MVP
Sydney
Australia
"Dileepan" wrote in message ...
Hi,
I have an array UDF that displays value from the first
cell into all the cells in the array range. With MsgBox
I know the values are getting computed correctly. Please
let me know what I am doing wrong.
The relevant portion of the UDF is given below.
======================================
Function rkArray(dataArray As Range, K As Integer) As
Variant
.... statements .....
ReDim rk(K)
.... statements computing rk array
rkArray = rk
End Function
======================================
-- Dileepan
|