Array UDF

  • Thread starter Thread starter Dileepan
  • Start date Start date
D

Dileepan

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
 
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
 
If you're creating a one dimensional array, you need to transpose it
if you're returning the values to a column. Here's one way:

Function rkArray(dataArray As Range, K As Integer) As Variant
Dim rk As Variant
Dim i As Long
ReDim rk(1 To K)
For i = 1 To UBound(rk)
rk(i) = dataArray.Cells(i).Value * i
Next i
With Application.Caller
If .Rows.Count = 1 Then
rkArray = rk
ElseIf .Columns.Count = 1 Then
rkArray = Application.Transpose(rk)
Else
rkArray = CVErr(xlErrNA)
End If
End With
End Function
 
Back
Top