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. |
Array Formula/UDF
On 3 Iun, 10:47, AA2e72E wrote:
Maybe you can use a parameter array : Function Add(ParamArray Target() As Variant) |
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. |
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. |
All times are GMT +1. The time now is 04:43 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com