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]

Thanks Andrew,

Now it works fine and I am getting the results.

Now I am trying to customize the Formula with Optional Parameters and I will come back to you for any clarifications.

I was looking for this formula for last 1 month in Internet but I got it solved from you in last 3 days.

Thanks Once again
Regards
R. Vadivelan

 

[Andrew Poulsom]

There is a limit to the length of a formula that Evaluate can resolve. What is the purpose of your SUMPRODUCT formula exactly? It may be possible to shorten it.

 

[velu130486]

Hi Andrew,

Ok Noted. May I know what is the limit Evaluate can resolve? I am using SUMPRODUCT since we are multiplying the 4 arrays with some condition.

Previously I was calculating the Progress Line by Line and then We will calculate the Overall Progress with help of SUMPRODUCT.

Now my objective is to eliminate the Line by Line progress calculation that's why I am using SUMPRODUCT to calculate the Overall Progress based on 1 date.

If required I can send my progress calculation file.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

The limit is 255 characters.

You can put your workbook on a share like Box.com and post the URL.

 

[velu130486]

Hi Andrew,

I had uploaded my file in Google drive and you can download the same from the following link

https://drive.google.com/file/d/0B-gqVByC6OGQWWZHOEpCUUlUV2s/edit?usp=sharing

In the File as I said, previously we are are calculating the Progress deliverable wise in Column Y & Z, Then the overall Progress is calculated in Cell Y4 & Z4 using Sumproduct.

Now using this UDF I can able to calculate the Overall progress directly as I had done in Sheet Curve Row 31 & 32.

Is it possible to Calculate the deliverable wise progress in UDF First and Use the Sumproduct to calculate the Overall progress (i.e) Doing the Progress calculation by 2 Parts. If Possible could you please advice me.

Also I would like to understand whether it will cause any problem if we store more datas in name manager. In order to avoid mistakes I always store the formulas in Name manager and use the name in calculation but found that excel takes quite time while opening and saving data.

Please let me know for any further clarifications required.

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

I don't think using a UDF will speed things up for you. After all, it's doing the same thing as your formulas.

You have a lot of names with invalid references in that workbook.

 

[velu130486]

Hi Andrew,

Actually I had deleted some unwanted rows, that's why its showing invalid references.

How about doing the progress calculation in UDF in 2 parts, is it possible?

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

You should be able to convert those formulas to a UDF. They are pretty similar to your original formula.

 

[velu130486]

Hi Andrew,

As discussed earlier, I am trying to split the formula into 2 steps, But I am not getting the results. Could you please advice me what I had done wrong in the Following Code

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

I am not getting any error message while calling this function, but still my function returns value error

Thanks in Advance for your help
Regards
R. Vadivelan

 

[Andrew Poulsom]

Why have you declared Progress as Range? What do you expect it to contain? If you add this line before the last line does return what you expect?

MsgBox "=SUMPRODUCT(" & Progress & ")*(" & WFAddr & "))"