Array Formula/UDF

[AA2e72E]

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.

 

[ytayta555]

Maybe you can use a parameter array :

Function Add(ParamArray Target() As Variant)

 

[Charles Williams]

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]

Perfect.

Thanks Charles.

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