User Defined Function - Sumproduct

**PeterW** · 05-22-2008, 09:31 PM

Hi there

I have a very large formula that works. At the moment the formula exists in a cell on a worksheet and I have written some code to copy the formula to other cells on the worksheet and then after calculating the formula the code turns the resulting output into a value. I have had to do this as the formula is so resource intensive that if the formula lived in each of the cells the model would run too slowly.

I would now like to turn the formula into a UDF, so that I can protect it from the users, but I have no idea how to start... I'm not expecting a converted formula, just some hints on how to go about it ...

The formula is:

=IF(O$3="Opening Bal",O_Financials_Actual!O12,
IF(ROW()<ROW(modCashFlowStartRow),
(
SUMPRODUCT((I_Budget_Worksheet!$E$13:$E$288=$E12)*(I_Budget_Worksheet!$O$7:$BK$7=O$7)*(I_Budget_Worksheet!$O$13:$BK$288))-
SUMPRODUCT((I_Budget_Worksheet!$F$13:$F$288=$E12)*(I_Budget_Worksheet!$O$7:$BK$7=O$7)*(I_Budget_Worksheet!$O$13:$BK$288))
*
IF(O_Financials_Budget!$F12="Cr",-1,1))+
IF(O_Financials_Budget!$G12="***",O_Financials_Budget!N12,0),
SUMPRODUCT((I_Budget_Worksheet!$G$13:$G$288=$E12)*(I_Budget_Worksheet!$E$13:$E$288=Bank_Account_Identifier)*(I_Budget_Worksheet!$O$7:$BK$7=O$7)*(I_Budget_Worksheet!$O$13:$BK$288))-
SUMPRODUCT((I_Budget_Worksheet!$G$13:$G$288=$E12)*(I_Budget_Worksheet!$F$13:$F$288=Bank_Account_Identifier)*(I_Budget_Worksheet!$O$7:$BK$7=O$7)*(I_Budget_Worksheet!$O$13:$BK$288))
))

Thanks in advance
Peter

**Greg M** · 05-23-2008, 05:03 AM

Hi Peter,

Maybe I've misunderstood something, but if all you want to do is safeguard the formula from accidental or well-intentioned interference you can just protect the worksheet. Obviously those cells which are "legitimately" accessed by users or by your code should be unlocked before you do this.

Have I understood your requirement correctly?

Regards,

Greg M

**PeterW** · 05-23-2008, 05:45 AM

Hi Greg .. thanks for your response

I have incorrectly used the word 'protect'. I am aware of the 'protection' functionality in excel, but this doesnt stop users from reading the code. What I'm trying to do is 'hide' the formula in a UDF which can then be saved and protected from view in the VB code.

Cheers
Peter

**dominicb** · 05-23-2008, 05:53 AM

Good morning PeterW

If you use the protection idea that Greg M came up with, then select all your cells with the monster formula in, press Ctrl + 1, Protection, and cmake sure the box marked Hidden is ticked. Once protection is invoked, this will mean that your formula cannot be seen in the Formula bar, but the result can still be seen on screen.

HTH

DominicB