Pass Named Range To Sumproduct Via Macro Code

    Howdy,


    I'm trying to make an Excel VBA function that generalizes the SUMPRODUCT worksheet function. This new function would take two named ranges and two conditions on them and then return the count of rows that meet those conditions


    For example, imagine that I have the following two-column worksheet:


    Team || Win/Loss
    ------------------------
    1 W
    2 L
    1 W
    3 W
    1 L


    I then name the first column's data as "TEAMNAME" and the second column's data as "OUTCOME".


    Using SUMPRODUCT directly, I could write


    =SUMPRODUCT((TEAMNAME="1")*(OUTCOME="W"))


    to see that team 1 won twice.


    However, I want to write a VBA function that will take these ranges and conditions and return this sumproduct value,e.g.


    =MYFUNC(TEAMNAME,"1",OUTCOME,"W")


    I want to generalize in this way because I have many different two-column pieces of data where I want to use the same centralized code (and in truth my real problem has much more data than this example). The problem is that I'm having trouble passing the named ranges "TEAMNAME" and "OUTCOME" to my VBA SUMPRODUCT function and getting it to do the counting. Any suggestions or examples that work?


    Many thanks, JP

    Using sumproduct as a worksheet function in VBA only seems to work in its simplest form (the example below works) but not when it gets more complicated ie teamname=1 * outcome=w


    answer = Application.WorksheetFunction.SumProduct(Range("teamname"), Range("outcome"))



    The following quick & dirty code works also:


    Sub test2()


    TeamVal = 1
    OutVal = "W"
    y = 0
    For x = 0 To Range("TeamName").Rows.Count - 1
    a = Cells(Range("teamname").Row + x, Range("teamname").Column).Value
    b = Cells(Range("outcome").Row + x, Range("outcome").Column).Value
    y = y + (a = TeamVal) * (b = OutVal)
    Next x


    MsgBox y
    End Sub

    Thanks Doug, but is there a way to pass "teamname" and "outcome" into the VBA function so that SUMPRODUCT can use it? I want the range to be something that is passed in to the function so that I can use the function for different sets of data.


    Thanks, JP

    I'm sure this isn't the prettiest way to do it but it works. Is this what you are looking for?


    Function Sumprodx(teamname As Range, TeamVal, outcome As Range, OutVal)
    Sumprodx = 0
    For x = 0 To teamname.Rows.Count - 1
    a = Cells(teamname.Row + x, teamname.Column).Value
    b = Cells(outcome.Row + x, outcome.Column).Value
    Sumprodx = Sumprodx + (a = TeamVal) * (b = OutVal)
    Next x
    End Function


    Sub Test3()
    z = Sumprodx(Range("whatever1"), 1, Range("whatever2"), "w")
    MsgBox z
    End Sub

    Hi guys,


    How about:

    Code
    Sub Test()
    MsgBox SPTest(Range("TEAMNAME"), 1, Range("OUTCOME"), "W")
End Sub


Function SPTest(rng1 As Range, i As Integer, _
    rng2 As Range, strText As String) As Integer
    SPTest = Evaluate("SumProduct(((" & rng1.Address & ")= " & i & ") * ((" _
        & rng2.Address & ")= """ & strText & """))")
End Function

    HTH

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login