Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula/UDF
If I have a user defined function, say, Add and I want to enter an array
formula such as =Add(A1:A3,B1:B3) in cells C1:C3, how should I code the function Add? Everything I've tried returns a #VALUE error. Thanks. |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula/UDF
On 3 Iun, 10:47, AA2e72E wrote:
Maybe you can use a parameter array : Function Add(ParamArray Target() As Variant) |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula/UDF
The trick is to return an array of values: something like this
(but you would need to add error checking etc etc) Public Function ADD(Range1 as range,Range2 as range) as variant dim vOut() as variant dim v1 as variant dim v2 as variant dim j as long redim vOut(1 to application.caller.rows.count,1 to 1) v1=range1.Value2 v2=Range2.value2 for j=1 to ubound(vOut) vOut(j,1)=v1(j,1)+v2(j,1) next j ADD=vOut End Function Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "AA2e72E" wrote in message ... If I have a user defined function, say, Add and I want to enter an array formula such as =Add(A1:A3,B1:B3) in cells C1:C3, how should I code the function Add? Everything I've tried returns a #VALUE error. Thanks. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Array Formula/UDF
Perfect.
Thanks Charles. "Charles Williams" wrote: The trick is to return an array of values: something like this (but you would need to add error checking etc etc) Public Function ADD(Range1 as range,Range2 as range) as variant dim vOut() as variant dim v1 as variant dim v2 as variant dim j as long redim vOut(1 to application.caller.rows.count,1 to 1) v1=range1.Value2 v2=Range2.value2 for j=1 to ubound(vOut) vOut(j,1)=v1(j,1)+v2(j,1) next j ADD=vOut End Function Charles ___________________________________ The Excel Calculation Site http://www.decisionmodels.com "AA2e72E" wrote in message ... If I have a user defined function, say, Add and I want to enter an array formula such as =Add(A1:A3,B1:B3) in cells C1:C3, how should I code the function Add? Everything I've tried returns a #VALUE error. Thanks. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Array formula SUMIF with 2D sum_range array | Excel Worksheet Functions | |||
Array formula: how to join 2 ranges together to form one array? | Excel Worksheet Functions | |||
Find specific value in array of array formula | Excel Worksheet Functions | |||
copy one array formula to an array range | Excel Programming | |||
Tricky array formula issue - Using array formula on one cell, then autofilling down a range | Excel Programming |