Customized Sumproduct Function in Excel VBA

[velu130486]

Dear Seniors,

I am new user to VBA. I had created a Array sum product formula with multiple conditions. Now I would like to get help from this forum to create the same formula in Excel VBA. Presently I am using named ranges in my formula. But I would like to use the formula by selecting the dynamic ranges.

Here is the formula I am using to calculate my result based on the Date in C1, DEPT Value in D1

{=SUMPRODUCT((DEPT=D1)*IF(((CPYFORIFD>0)*(CPYFORIFD<=C$1)),1 ,IF(((CPYFORIFA>0)*(CPYFORIFA<=C$1)),0.8,IF(((CPYF ORIFR>0)*(CPYFORIFR<=C$1)),0.6,0)))*(CPYWF))}

where us CPYFORIFD, CPYFORIFA, CPYFORIFR are Named ranges which contains Dates & CPYWF is a Number. I am browsing the internet for last 2 weeks but I could not crack the code. Please help me to sort out this issue and this will help me a lot.

My Inputs will be CPYFORIFD, CPYFORIFA, CPYFORIFR (Dates), CPYWF (Double) and DEPT (String)




Thanks and Regards
R. Vadivelan

 

[velu130486]

Hi Andrew,

This formula is working. However in this function only 2 parameters are defined. I would like to have other parameters also. (i.e) CPYFORIFD, CPYFORIFA, CPYFORIFR & CPYWF. If we defined that too then it will be more useful for me.

And my formula is a array formula, however here I have not seen any array in the code. Please correct me if I am wrong. Is it possible to make the DEPT parameter as optional.

Please let me know for any clarifications required.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

The Evaluate function will resolve any valid formula. There is no way or need to specify that its argument is an array formula.

Now that you have seen it in action you should be able to modify the function for additional arguments.

 

[velu130486]

Hi Andrew,

Thanks for your help. I am already working on the formula and I will come back to you for any help.

could you please assist me how to define a optional parameter.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

Look here:

Optional Arguments To Procedures

 

[velu130486]

Hi Andrew,

Thanks for your information. With help of your code I had customized my formula, but I am facing a issue.

Function Plan_Progress(CutoffDate As Range, PlanIFR As Range, PlanIFA As Range, PlanIFD As Range, WF As Range)

    With Application.Caller.Parent
        Plan_Progress = .Evaluate("=SUMPRODUCT(IF(((" & PlanIFD.Address & ">0)*(" & PlanIFD.Address & "<=" & CutoffDate.Address & ")),1,IF(((" & PlanIFA.Address & ">0)*(" & PlanIFA.Address & "<=" & CutoffDate.Address & ")),0.8,IF(((" & PlanIFR.Address & ">0)*(" & PlanIFR.Address & "<=" & CutoffDate.Address & ")),0.6,0)))*(" & WF.Address & "))")
    End With
End Function

When I use this UDF in the same sheet it works fine, however When I use this UDF in another sheet it does not works. I had checked the parameters and it is referred correctly but still I am getting error.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

How are you calling the function?

 

[velu130486]

Hi Andrew,

I am calling that function similar to excel formulas.
i.e =Plan_Progress

It is perfectly working in sheet with Data's, whereus it is not working if I use the formula in another sheet.

I have the data's in Sheet 1 and the formula is giving me the correct results, Where us it is not working in sheet 2.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

Please give an example of a formula that uses the Plan_Progress function.

 

[velu130486]

Hi Andrew,

In REV_0 Sheet, Which Contains the Dates

I put the Formula in BL7 Cell

=Plan_Progress(BL6,REV_0!$AG$8:$AG$4175,REV_0!$AH$8:$AH$4175,REV_0!$AI$8:$AI$4175,REV_0!$BD$8:$BD$4175)

This gives me the Correct Result.

The same formula I had copied to the different sheet named SCurve, but it does not works. The following is the Formula

=Plan_Progress(C1,REV_0!$AG$8:$AG$4175,REV_0!$AH$8:$AH$4175,REV_0!$AI$8:$AI$4175,REV_0!$BD$8:$BD$4175)

The Only difference is the Cut Off Date Location

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

Try:

Function Plan_Progress(CutoffDate As Range, PlanIFR As Range, PlanIFA As Range, PlanIFD As Range, WF As Range)
    Dim Cutoff As String, IFR As String, IFA As String, IFD As String, WFAddr As String
    Cutoff = "'" & CutoffDate.Parent.Name & "'!" & CutoffDate.Address(False, False)
    IFR = "'" & PlanIFR.Parent.Name & "'!" & PlanIFR.Address(False, False)
    IFA = "'" & PlanIFA.Parent.Name & "'!" & PlanIFA.Address(False, False)
    IFD = "'" & PlanIFD.Parent.Name & "'!" & PlanIFD.Address(False, False)
    WFAddr = "'" & WF.Parent.Name & "'!" & WF.Address(False, False)
    Plan_Progress = Application.Evaluate("=SUMPRODUCT(IF(((" & IFD & ">0)*(" & IFD & "<=" & Cutoff & ")),1,IF(((" & IFA & ">0)*(" & IFA & "<=" & Cutoff & ")),0.8,IF(((" & IFR & ">0)*(" & IFR & "<=" & Cutoff & ")),0.6,0)))*(" & WFAddr & "))")
End Function