CODEWITHSUNDEEP
vbaexcelexcel-formula
Anu
Anu

Reputation: 1129

Why SumProduct cannot use in Excel VBA

Scenario

I am trying to use COUNTIF in my VBA code to count total how many similar string found in a range. But realised COUNTIF is not case sensitive. Eg: I want to count how many Apple in a range. But sometimes if in that range got apple, it is counting that also. Code as below. Consider Range(poRange) something like C1:C100 and Trim(mainpage.po.value) as Apple

iVal = Application.WorksheetFunction.CountIf(Range(poRange), Trim(mainPage.po.Value))

Thus I decided to use another method which is 

iVal = Application.WorksheetFunction.SumProduct(--(EXACT(Range(poRange), Trim(mainPage.po.Value))))

But this time I couldn't run my VBA due to Compile time error 'Sub or Function not defined'

Could someone help me please?

Upvotes: 2

Views: 671

Answers (2)

Anu
Anu

Reputation: 1129

I used one of the above solutions and it solved the problem. Entire code as below

Public c As Integer

Sub findVal()
Call SumProductExact(Sheet1.Range("A1:A10"), "Apple")
MsgBox c ' You can use this c value for other calculations

End Sub

Public Function SumProductExact(rng As Range, testItem As String) As Long
    Dim evalExpr As String

    On Error GoTo EH
    evalExpr = "=SUMPRODUCT(--(EXACT(" & rng.Address & ", """ & testItem & """)))"
    SumProductExact = Evaluate(evalExpr)
    c = SumProductExact
    Exit Function

EH:
    SumProductExact = -1
End Function

Take note that for the above code, it is using static range which is A1:A10 and a static name Apple. You may change it to a dynamic range and name according to your needs.

Upvotes: 0

Ambie
Ambie

Reputation: 4977

The issue is with the EXACT function, which WorksheetFunction does not expose.

For unexposed Excel functions, a common workaround is to convert the expression to a string and call the Evaluate function. A rudimentary routine would look like this:

Public Function SumProductExact(rng As Range, testItem As String) As Long
    Dim evalExpr As String

    On Error GoTo EH
    evalExpr = "=SUMPRODUCT(--(EXACT(" & rng.Address & ", """ & testItem & """)))"
    SumProductExact = Evaluate(evalExpr)
    Exit Function

EH:
    SumProductExact = -1
End Function

and would be called like so:

Debug.Print SumProductExact(Sheet1.Range("A1:A10"), "Apple")

But it hardly seems worth going to those lengths for a relatively trivial function, so you could just write your own CountIf function for an exact match. You might want to expand this to cater for other type comparisons, like dates, but fundamentally it would look like this:

Public Function CountIfExact(rng As Range, testItem As Variant) As Long
    Dim v As Variant
    Dim c As Long

    On Error GoTo EH
    For Each v In rng.Value2
        If v = testItem Then c = c + 1
    Next

    CountIfExact = c
    Exit Function

EH:
    CountIfExact = -1
End Function

which would be called as follows:

Debug.Print CountIfExact(Sheet1.Range("A1:A10"), "Apple")

Upvotes: 3

Related Questions