Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array UDF
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 In article , "Dileepan" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Complex conditional summing - array COUNT works, array SUM gives#VALUE | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Prevent cell/array references from changing when altering/moving thecell/array | Excel Discussion (Misc queries) | |||
meaning of : IF(Switch; Average(array A, array B); array A) | Excel Worksheet Functions | |||
combining cells and array from different sheets into an array to pass to IRR() | Excel Discussion (Misc queries) |