Replicate SUMPRODUCT in VBA to create UDF which is much shorter to simplify appearance of formulae

[Robnrolla]

Hello,

I would like to know how to create a UDF in VBA that replicates a SUMPRODUCT formula. Is this possible and how can I do it? I am new to VBA so not familiar enough with the code to create this myself.

For example - UDF - HO() = SUMPRODUCT((Item Index range=X)*(Date Index Range=Y)*(Numbers Range))

Thanks,

Rob

 

[James006]

Hello,

You should take a look at Evaluate ... in order to work ou the result of your Sumproduct formula ...

Hope this will help

 

[Robnrolla]

I don't suppose that you could be a bit more specific? thank you

 

[offthelip]

is somethign like this what you are looking for:

Function ho(r1 As Range, r2 As Range, r3 As Range)
 ho = Application.WorksheetFunction.SumProduct(r1, r2, r3)
End Function