Array Formula/UDF

  • Thread starter Thread starter AA2e72E
  • Start date Start date
A

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.
 
Maybe you can use a parameter array :

Function Add(ParamArray Target() As Variant)
 
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
 
Perfect.

Thanks Charles.

Charles Williams said:
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
Click to expand...
 
Back
Top