Using EVALUATE in UDF on an array formula giving error

powera86 · Sep 14th 2012

Hi everyone,

I am working on a UDF that will basically function as a "countifs" but only counting unique values. I think I am on the right track, however I can't get the "EVALUATE" function to operate properly on:

Code

Msgbox Evaluate("=SUM(IF(FREQUENCY(IF(" & OptArg1 & ",MATCH(" & RtrRng & "," & RtrRng & ",0)),ROW(INDIRECT(""1:""&ROWS(" & RtrRng & "))))>0,1))")

This is the full code:

Code

Function CountUniqueMultiple(SearchStr1 As Variant, SearchRng1 As Range, _
                                ReturnRng As Range, _
                                Optional SearchStr2 As Variant, Optional SearchRng2 As Range, _
                                Optional SearchStr3 As Variant, Optional SearchRng3 As Range, _
                                Optional SearchStr4 As Variant, Optional SearchRng4 As Range, _
                                Optional SearchStr5 As Variant, Optional SearchRng5 As Range, _
                                Optional SearchStr6 As Variant, Optional SearchRng6 As Range, _
                                Optional SearchStr7 As Variant, Optional SearchRng7 As Range, _
                                Optional SearchStr8 As Variant, Optional SearchRng8 As Range, _
                                Optional SearchStr9 As Variant, Optional SearchRng9 As Range, _
                                Optional SearchStr10 As Variant, Optional SearchRng10 As Range) As Variant
Dim OptArg1 As Variant
Dim OptArg2 As Variant
Dim OptArg3 As Variant
Dim OptArg4 As Variant
Dim OptArg5 As Variant
Dim OptArg6 As Variant
Dim OptArg7 As Variant
Dim OptArg8 As Variant
Dim OptArg9 As Variant
Dim OptArg10 As Variant
Dim OptDelim1 As Variant
Dim OptDelim2 As Variant
Dim OptDelim3 As Variant
Dim OptDelim4 As Variant
Dim OptDelim5 As Variant
Dim OptDelim6 As Variant
Dim OptDelim7 As Variant
Dim OptDelim8 As Variant
Dim OptDelim9 As Variant
Dim OptDelim10 As Variant
Dim Rng1 As String
Dim Rng2 As String
Dim Rng3 As String
Dim Rng4 As String
Dim Rng5 As String
Dim Rng6 As String
Dim Rng7 As String
Dim Rng8 As String
Dim Rng9 As String
Dim Rng10 As String
Dim RtrRng As String
Rng1 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng2 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng3 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng4 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng5 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng6 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng7 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng8 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng9 = SearchRng1.Address(ReferenceStyle:=xlA1)
Rng10 = SearchRng1.Address(ReferenceStyle:=xlA1)
RtrRng = ReturnRng.Address(ReferenceStyle:=xlA1)
If IsMissing(SearchStr1) = True Then
    OptArg1 = ""
    OptDelim1 = ""
        ElseIf IsMissing(SearchStr1) = False Then
            OptArg1 = "(" & Rng1 & "=""" & SearchStr1 & """)"
            OptDelim1 = "*"
End If
If IsMissing(SearchStr2) = True Then
    OptArg2 = ""
    OptDelim2 = ""
        ElseIf IsMissing(SearchStr2) = False Then
            OptArg2 = "(" & Rng2 & "=" & SearchStr2 & ")"
            OptDelim2 = "*"
End If
If IsMissing(SearchStr3) = True Then
    OptArg3 = ""
    OptDelim3 = ""
        ElseIf IsMissing(SearchStr3) = False Then
            OptArg3 = "(" & Rng3 & "=" & SearchStr3 & ")"
            OptDelim3 = "*"
End If
If IsMissing(SearchStr4) = True Then
    OptArg4 = ""
    OptDelim4 = ""
        ElseIf IsMissing(SearchStr4) = False Then
            OptArg4 = "(" & Rng4 & "=" & SearchStr4 & ")"
            OptDelim4 = "*"
End If
If IsMissing(SearchStr5) = True Then
    OptArg5 = ""
    OptDelim5 = ""
        ElseIf IsMissing(SearchStr5) = False Then
            OptArg5 = "(" & Rng5 & "=" & SearchStr5 & ")"
            OptDelim5 = "*"
End If
If IsMissing(SearchStr6) = True Then
    OptArg6 = ""
    OptDelim6 = ""
        ElseIf IsMissing(SearchStr6) = False Then
            OptArg6 = "(" & Rng6 & "=" & SearchStr6 & ")"
            OptDelim6 = "*"
End If
If IsMissing(SearchStr7) = True Then
    OptArg7 = ""
    OptDelim7 = ""
        ElseIf IsMissing(SearchStr7) = False Then
            OptArg7 = "(" & Rng7 & "=" & SearchStr7 & ")"
            OptDelim7 = "*"
End If
If IsMissing(SearchStr8) = True Then
    OptArg8 = ""
    OptDelim8 = ""
        ElseIf IsMissing(SearchStr8) = False Then
            OptArg8 = "(" & Rng8 & "=" & SearchStr8 & ")"
            OptDelim8 = "*"
End If
If IsMissing(SearchStr9) = True Then
    OptArg9 = ""
    OptDelim9 = ""
        ElseIf IsMissing(SearchStr9) = False Then
            OptArg9 = "(" & Rng9 & "=" & SearchStr9 & ")"
            OptDelim9 = "*"
End If
If IsMissing(SearchStr10) = True Then
    OptArg10 = ""
    OptDelim10 = ""
        ElseIf IsMissing(SearchStr10) = False Then
            OptArg10 = "(" & Rng10 & "=" & SearchStr10 & ")"
            OptDelim10 = "*"
End If
MsgBox Evaluate("=SUM(IF(FREQUENCY(IF(" & OptArg1 & ",MATCH(" & RtrRng & "," & RtrRng & ",0)),ROW(INDIRECT(""1:""&ROWS(" & RtrRng & "))))>0,1))")


CountUniqueMultiple = "=SUM(IF(FREQUENCY(IF(" & OptArg1 & ",MATCH(" & RtrRng & "," & RtrRng & ",0)),ROW(INDIRECT(""1:""&ROWS(" & RtrRng & "))))>0,1))"


End Function

Display More

Any thoughts???

Thanks

powera86 · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

I should mention that I am in now way set on using the above method. All I need is a UDF to return the count of unique values in "ReturnRng" based on multiple (also optional) criteria and criteria ranges to a limit of 10.

I just haven't been able to find anything so far that can do what I need, which is what started this whole problem in the first place.

**norie** · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Why do you need the Evaluate and message box?

powera86 · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Hi Norie,

...I don't need the message box, it is simply there for now while I try and figure out why the evalute portion isn't returning a valid result.

I need the evaluate portion because based on the criteria, search ranges, and result range that is passed in from the udf by the user, the result of my UDF will be a count of unique values in the return range based on the criteria that the user chooses...and to return the count of unique values based on multiple criteria, I currently only know how to do with an array formula which is why I am trying to use "evaluate" to capture that number.

**norie** · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Sorry you've lost me.

If you are going to return the result from the Evaluate why are you showing, or at least trying to, it in a message box and not assigning it to CountUniqueMultiple?

powera86 · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Hi Norie,

No worries, I've lost myself by now on a Friday lol.

You're right, there is no need for the msgbox and the end result would be for me to assign the Evaluate result to CountUniqueMultiple, however this becomes sort of irrelevant because even when I assign the Evaluate result to CountUniqueMultiple I just get a #REF error returned anyways.

Meaning...even if i do this:

Code

CountUniqueMultiple = Evaluate("=SUM(IF(FREQUENCY(IF(" & OptArg1 & ",MATCH(" & RtrRng & "," & RtrRng & ",0)),ROW(INDIRECT(""1:""&ROWS(" & RtrRng & "))))>0,1))")

...it still isn't working

The bottom line is the Evaluate portion of the code isn't working, so that's what I am looking for help with

**norie** · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Have you checked the formula?

powera86 · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Yes definitely checked the formula. Which is part of the reason why I was playing around with the msgbox. If I set countuniquemultiple to:

Code

"=SUM(IF(FREQUENCY(IF(" & OptArg1 & ",MATCH(" & RtrRng & "," & RtrRng & ",0)),ROW(INDIRECT(""1:""&ROWS(" & RtrRng & "))))>0,1))"

So basically, the UDF spits out the string that I would be asking EVALUATE to handle.

If I then take the string returned from the UDF and paste values, then edit the formula and confirm with CTRL+SHIFT+ENTER it works and calculates correctly.

**norie** · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Can you attach a workbook with sample data?

powera86 · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

No problem.

See attached.

forum.ozgrid.com/index.php?attachment/48466/

**norie** · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Sorry but I can't see what the problem is.

The Evaluate is returning an Error 2015, whatever that means, but I don't know why.

By the way, why do you have SearchRng1 repeated 10 times when setting Rng1 to Rng10?

powera86 · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Hi Norie,

That's an error. The searchrng variable should be incrementing up with the rng variable. But because I'm only testing at 1 criteria. Right now didn't catch it. Thanks!

Back to the issue though, thanks for trying! Hopefully someone else can shed some light if they've run in to this before. From what I can see it should be evaluating fine.

**norie** · Sep 14th 2012

Re: Using EVALUATE in UDF on an array formula giving error

What happens if you do the Evaluate in a Sub rather than a Function?

powera86 · Sep 16th 2012

Re: Using EVALUATE in UDF on an array formula giving error

I'm not too sure if putting it in a sub would change anything but will give it a shot Monday morning when I'm back on my system.

Definitely less than ideal though. Hoping someone out there might have a work around or more elegant solution of producing what I'm looking for.

powera86 · Sep 17th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Unfortunately putting this into a Sub routine had no effect, still erroring out.

powera86 · Sep 17th 2012

Re: Using EVALUATE in UDF on an array formula giving error

After trouble shooting this for a bit, it seems that this may be an issue with using INDIRECT within an EVALUATE in VBA. Still haven't resolved, but thought I'd keep the thread updated incase anyone is working on this / following along.

powera86 · Sep 17th 2012

Re: Using EVALUATE in UDF on an array formula giving error

So, to close the loop it does appear that the issue was using INDIRECT within an EVALUATE within a UDF. I cannot say why this doesn't work, but it doesn't. I have managed to work around this, and now have a functioning UDF. In case anyone is looking for something like this (I haven't seen anything else online through my searches to resolve this issue) the code is available below. This is a UDF that will return the count of unique values in a range given a variable number of criteria, and criteria ranges. There may be a more elegant way to accomplish this, but this is the best I have come up with or seen so far:

Definitely open to suggestions and improvements

Code

Option Explicit
Function CountUniqueMultiple(SearchStr1 As Variant, SearchRng1 As Range, _
                                ReturnRng As Range, _
                                Optional SearchStr2 As Variant, Optional SearchRng2 As Range, _
                                Optional SearchStr3 As Variant, Optional SearchRng3 As Range, _
                                Optional SearchStr4 As Variant, Optional SearchRng4 As Range, _
                                Optional SearchStr5 As Variant, Optional SearchRng5 As Range, _
                                Optional SearchStr6 As Variant, Optional SearchRng6 As Range, _
                                Optional SearchStr7 As Variant, Optional SearchRng7 As Range, _
                                Optional SearchStr8 As Variant, Optional SearchRng8 As Range, _
                                Optional SearchStr9 As Variant, Optional SearchRng9 As Range, _
                                Optional SearchStr10 As Variant, Optional SearchRng10 As Range) As Variant
'Created by Adam Power 09/17/2012
'Purpose of this function is to allow the user to return a count of the unique values of a range, given an optional 10 criteria and criteria ranges
Dim OptArg1 As Variant
Dim OptArg2 As Variant
Dim OptArg3 As Variant
Dim OptArg4 As Variant
Dim OptArg5 As Variant
Dim OptArg6 As Variant
Dim OptArg7 As Variant
Dim OptArg8 As Variant
Dim OptArg9 As Variant
Dim OptArg10 As Variant
Dim OptDelim1 As Variant
Dim OptDelim2 As Variant
Dim OptDelim3 As Variant
Dim OptDelim4 As Variant
Dim OptDelim5 As Variant
Dim OptDelim6 As Variant
Dim OptDelim7 As Variant
Dim OptDelim8 As Variant
Dim OptDelim9 As Variant
Dim OptDelim10 As Variant
Dim Rng1 As String
Dim Rng2 As String
Dim Rng3 As String
Dim Rng4 As String
Dim Rng5 As String
Dim Rng6 As String
Dim Rng7 As String
Dim Rng8 As String
Dim Rng9 As String
Dim Rng10 As String
Dim RtrRng As String
If IsMissing(SearchStr1) = False Then
Rng1 = SearchRng1.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr1) = True Then
        Rng1 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
If IsMissing(SearchStr2) = False Then
Rng2 = SearchRng2.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr2) = True Then
        Rng2 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
If IsMissing(SearchStr3) = False Then
Rng3 = SearchRng3.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr3) = True Then
        Rng3 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
If IsMissing(SearchStr4) = False Then
Rng4 = SearchRng4.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr4) = True Then
        Rng4 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
If IsMissing(SearchStr5) = False Then
Rng5 = SearchRng5.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr5) = True Then
        Rng5 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
If IsMissing(SearchStr6) = False Then
Rng6 = SearchRng6.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr6) = True Then
        Rng6 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
If IsMissing(SearchStr7) = False Then
Rng7 = SearchRng7.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr7) = True Then
        Rng7 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
If IsMissing(SearchStr8) = False Then
Rng8 = SearchRng8.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr8) = True Then
        Rng8 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
If IsMissing(SearchStr9) = False Then
Rng9 = SearchRng9.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr9) = True Then
        Rng9 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
If IsMissing(SearchStr10) = False Then
Rng10 = SearchRng10.Address(ReferenceStyle:=xlA1)
    ElseIf IsMissing(SearchStr10) = True Then
        Rng10 = SearchRng1.Address(ReferenceStyle:=xlA1)
End If
RtrRng = ReturnRng.Address(ReferenceStyle:=xlA1)
Dim firstrow As Variant
Dim lastrow As Variant
firstrow = SearchRng1.Cells(1, 1).Row
lastrow = SearchRng1(SearchRng1.Count).Row
If IsMissing(SearchStr1) = True Then
    OptArg1 = ""
    OptDelim1 = ""
        ElseIf IsMissing(SearchStr1) = False Then
            OptArg1 = "(" & Rng1 & "=""" & SearchStr1 & """)"
            OptDelim1 = ""
End If
If IsMissing(SearchStr2) = True Then
    OptArg2 = ""
    OptDelim2 = ""
        ElseIf IsMissing(SearchStr2) = False Then
            OptArg2 = "(" & Rng2 & "=""" & SearchStr2 & """)"
            OptDelim2 = "*"
End If
If IsMissing(SearchStr3) = True Then
    OptArg3 = ""
    OptDelim3 = ""
        ElseIf IsMissing(SearchStr3) = False Then
            OptArg3 = "(" & Rng3 & "=""" & SearchStr3 & """)"
            OptDelim3 = "*"
End If
If IsMissing(SearchStr4) = True Then
    OptArg4 = ""
    OptDelim4 = ""
        ElseIf IsMissing(SearchStr4) = False Then
            OptArg4 = "(" & Rng4 & "=""" & SearchStr4 & """)"
            OptDelim4 = "*"
End If
If IsMissing(SearchStr5) = True Then
    OptArg5 = ""
    OptDelim5 = ""
        ElseIf IsMissing(SearchStr5) = False Then
            OptArg5 = "(" & Rng5 & "=""" & SearchStr5 & """)"
            OptDelim5 = "*"
End If
If IsMissing(SearchStr6) = True Then
    OptArg6 = ""
    OptDelim6 = ""
        ElseIf IsMissing(SearchStr6) = False Then
            OptArg6 = "(" & Rng6 & "=""" & SearchStr6 & """)"
            OptDelim6 = "*"
End If
If IsMissing(SearchStr7) = True Then
    OptArg7 = ""
    OptDelim7 = ""
        ElseIf IsMissing(SearchStr7) = False Then
            OptArg7 = "(" & Rng7 & "=""" & SearchStr7 & """)"
            OptDelim7 = "*"
End If
If IsMissing(SearchStr8) = True Then
    OptArg8 = ""
    OptDelim8 = ""
        ElseIf IsMissing(SearchStr8) = False Then
            OptArg8 = "(" & Rng8 & "=""" & SearchStr8 & """)"
            OptDelim8 = "*"
End If
If IsMissing(SearchStr9) = True Then
    OptArg9 = ""
    OptDelim9 = ""
        ElseIf IsMissing(SearchStr9) = False Then
            OptArg9 = "(" & Rng9 & "=""" & SearchStr9 & """)"
            OptDelim9 = "*"
End If
If IsMissing(SearchStr10) = True Then
    OptArg10 = ""
    OptDelim10 = ""
        ElseIf IsMissing(SearchStr10) = False Then
            OptArg10 = "(" & Rng10 & "=""" & SearchStr10 & """)"
            OptDelim10 = "*"
End If
CountUniqueMultiple = Evaluate("=SUM(IF(FREQUENCY(IF(" & OptArg1 & OptDelim2 & OptArg2 & OptDelim3 & OptArg3 & OptDelim4 & OptArg4 & OptDelim5 _
& OptArg5 & OptDelim6 & OptArg6 & OptDelim7 & OptArg7 & OptDelim8 & OptArg8 & OptDelim9 & OptArg9 & OptDelim10 & OptArg10 & ",MATCH(" & RtrRng _
& "," & RtrRng & ",0)),ROW($" & firstrow & ":$" & lastrow & "))>0,1))")
End Function

Display More

powera86 · Sep 17th 2012

Re: Using EVALUATE in UDF on an array formula giving error

And....first issue has come up. If you are referencing ranges outside of the workbook where the UDF is being used, the formula won't calculate unless I go to the external workbook, activate a cell (F2) and then hit enter.

Not sure why this is coming up, but it seems to only be an issue with external workbook references.

Edit: Further testing has revealed that this issue comes up even if you are referencing ranges that are in the same workbook as the UDF, but on different worksheets.

**rory** · Sep 17th 2012

Re: Using EVALUATE in UDF on an array formula giving error

Due to the limitations of evaluate, you might use something like this

Code

Function CountUniqueMultiple(ReturnRng As Range, ParamArray SearchTerms()) As Variant     'Created by Rory 09/17/2012
     'Purpose of this function is to allow the user to return a count of the unique values of a range
     ' based on multiple criteria
    Dim n As Long
    Dim x As Long
    Dim oDic As Object
    Dim bMatch As Boolean
    Set oDic = CreateObject("Scripting.Dictionary")
    For x = 1 To ReturnRng.Columns(1).Cells.Count
        bMatch = True
        For n = LBound(SearchTerms) To UBound(SearchTerms) Step 2
            If StrComp(SearchTerms(n + 1).Cells(x, 1).Value, SearchTerms(n), vbTextCompare) <> 0 Then
                bMatch = False
                Exit For
            End If
        Next n
        If Not oDic.Exists(Lcase$(ReturnRng.Cells(x, 1).Value)) And bMatch Then oDic.Add LCase$(ReturnRng.Cells(x, 1).Value), 1
    Next x
    CountUniqueMultiple = oDic.Count
    Exit Function
        
oops:
    CountUniqueMultiple = CVErr(xlErrValue)
End Function

Display More

which allows you to pass one or more criteria pairs (value and range in that order). As written it will only work with columnar ranges but I leave that as an exercise for you.
Haven't done a lot of testing either!

powera86 · Sep 17th 2012

Re: Using EVALUATE in UDF on an array formula giving error

As always, you are the best!

Seems to be working quite nicely, and early testing looks promising! I'll send you a paypal transfer later today when I get home, contribute to the coffee fund

If you get a chance at some point to make it a little more robust to enable row ranges, I'll throw in a little extra :).

Either way, thank you so much for your help!

Using EVALUATE in UDF on an array formula giving error

Participate now!

Share