Array Function won't fire

Can anyone tell me why the following function to test the uniqueness of values in a (single and same) range fails to fire? It's based on the Worksheet Array formula =MAX(COUNTIF(rng,rng)) returning 1 for "unique", otherwise "not unique".

Function UniqueTest(rng1 As Range, rng2 As Range) As String

Application.Volatile

x = Application.Evaluate("Max(Countif(rng1,rng2))")
If x = 1 Then
UniqueTest = "Unique"
Else
UniqueTest = "Not Unique"
End If

End Function


and neither does ...

Function UniqueTest2(rng1 As Range, rng2 As Range) As String

Application.Volatile

x = [Max(Countif(rng1,rng2))]
If x = 1 Then
UniqueTest2 = "Unique"
Else
UniqueTest2 = "Not Unique"
End If

End Function

TIA

Myles

I can see several problems:

There is no connection between the rng1 and rng2 input parameters and the
string rng1,rng2 in evaluate which will be looking for excel defined named
ranges called rng1 and rng2

if you solve this problem you wont need the Application.volatile

Application.evaluate always assumes that that unqualified range references
refer to the active sheet, so your function would not work unless both rng1
and rng2 happen to be sitting on the active sheet.

You should add an on error handler to trap Evalute errors

see http://www.decisionmodels.com/calcsecretsh.htm for discussion of how to
use evaluate and an example UDF

regards
Charles
______________________
Decision Models
FastExcel 2.2 Beta now available
www.DecisionModels.com

"Myles" <[email protected]> wrote in
message news:[email protected]...
>
> Can anyone tell me why the following function to test the uniqueness of
> values in a (single and same) range fails to fire? It's based on the
> Worksheet Array formula *=MAX(COUNTIF(rng,rng)) *returning 1 for
> "unique", otherwise "not unique".
>
> Function UniqueTest(rng1 As Range, rng2 As Range) As String
>
> Application.Volatile
>
> x = Application.Evaluate("Max(Countif(rng1,rng2))")
> If x = 1 Then
> UniqueTest = "Unique"
> Else
> UniqueTest = "Not Unique"
> End If
>
> End Function
>
>
> and neither does ...
>
> Function UniqueTest2(rng1 As Range, rng2 As Range) As String
>
> Application.Volatile
>
> x = [Max(Countif(rng1,rng2))]
> If x = 1 Then
> UniqueTest2 = "Unique"
> Else
> UniqueTest2 = "Not Unique"
> End If
>
> End Function
>
> TIA
>
> Myles
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
> http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=522993
>

This should work:

Function UniqueTest(rng1 As Range, rng2 As Range) As String
Dim x As Long

Application.Volatile

x = Application.WorksheetFunction.CountIf(rng1, rng2)
If x = 1 Then
UniqueTest = "Unique"
Else
UniqueTest = "Not Unique"
End If

End Function

HTH
--
AP

"Myles" <[email protected]> a écrit dans le
message de news:[email protected]...
>
> Can anyone tell me why the following function to test the uniqueness of
> values in a (single and same) range fails to fire? It's based on the
> Worksheet Array formula *=MAX(COUNTIF(rng,rng)) *returning 1 for
> "unique", otherwise "not unique".
>
> Function UniqueTest(rng1 As Range, rng2 As Range) As String
>
> Application.Volatile
>
> x = Application.Evaluate("Max(Countif(rng1,rng2))")
> If x = 1 Then
> UniqueTest = "Unique"
> Else
> UniqueTest = "Not Unique"
> End If
>
> End Function
>
>
> and neither does ...
>
> Function UniqueTest2(rng1 As Range, rng2 As Range) As String
>
> Application.Volatile
>
> x = [Max(Countif(rng1,rng2))]
> If x = 1 Then
> UniqueTest2 = "Unique"
> Else
> UniqueTest2 = "Not Unique"
> End If
>
> End Function
>
> TIA
>
> Myles
>
>
> --
> Myles
> ------------------------------------------------------------------------
> Myles's Profile:
http://www.excelforum.com/member.php...o&userid=28746
> View this thread: http://www.excelforum.com/showthread...hreadid=522993
>

I don't think that will work if rng2 is a multicell range. It gives me a
type mismatch error.

this worked for me: (demo'd from the immediate window)

set rng1 = Range("A1:A10")
set rng2 = Range("B1:B10")

? application.Evaluate("Sum(countif(" & rng1.Address & "," & _
rng2.address & "))")
2

this also appears to work
? application.Sumproduct(application.Countif(rng1,rng2))
4

although I would consider this a special case. Using worksheetfunction as a
qualifier of Countif in this instance causes it to fail, however.

--
Regards,
Tom Ogilvy





"Ardus Petus" wrote:

> This should work:
>
> Function UniqueTest(rng1 As Range, rng2 As Range) As String
> Dim x As Long
>
> Application.Volatile
>
> x = Application.WorksheetFunction.CountIf(rng1, rng2)
> If x = 1 Then
> UniqueTest = "Unique"
> Else
> UniqueTest = "Not Unique"
> End If
>
> End Function
>
> HTH
> --
> AP
>
> "Myles" <[email protected]> a écrit dans le
> message de news:[email protected]...
> >
> > Can anyone tell me why the following function to test the uniqueness of
> > values in a (single and same) range fails to fire? It's based on the
> > Worksheet Array formula *=MAX(COUNTIF(rng,rng)) *returning 1 for
> > "unique", otherwise "not unique".
> >
> > Function UniqueTest(rng1 As Range, rng2 As Range) As String
> >
> > Application.Volatile
> >
> > x = Application.Evaluate("Max(Countif(rng1,rng2))")
> > If x = 1 Then
> > UniqueTest = "Unique"
> > Else
> > UniqueTest = "Not Unique"
> > End If
> >
> > End Function
> >
> >
> > and neither does ...
> >
> > Function UniqueTest2(rng1 As Range, rng2 As Range) As String
> >
> > Application.Volatile
> >
> > x = [Max(Countif(rng1,rng2))]
> > If x = 1 Then
> > UniqueTest2 = "Unique"
> > Else
> > UniqueTest2 = "Not Unique"
> > End If
> >
> > End Function
> >
> > TIA
> >
> > Myles
> >
> >
> > --
> > Myles
> > ------------------------------------------------------------------------
> > Myles's Profile:
> http://www.excelforum.com/member.php...o&userid=28746
> > View this thread: http://www.excelforum.com/showthread...hreadid=522993
> >
>
>
>

And just to add to Tom's comments.

I'd use .address(external:=true) in his expression (both spots).

Then I wouldn't have to worry about what sheet was active or where the ranges
were.

? application.Evaluate("Sum(countif(" & rng1.Address(external:=true) & "," & _
rng2.address(external:=true) & "))")

Tom Ogilvy wrote:
>
> I don't think that will work if rng2 is a multicell range. It gives me a
> type mismatch error.
>
> this worked for me: (demo'd from the immediate window)
>
> set rng1 = Range("A1:A10")
> set rng2 = Range("B1:B10")
>
> ? application.Evaluate("Sum(countif(" & rng1.Address & "," & _
> rng2.address & "))")
> 2
>
> this also appears to work
> ? application.Sumproduct(application.Countif(rng1,rng2))
> 4
>
> although I would consider this a special case. Using worksheetfunction as a
> qualifier of Countif in this instance causes it to fail, however.
>
> --
> Regards,
> Tom Ogilvy
>
> "Ardus Petus" wrote:
>
> > This should work:
> >
> > Function UniqueTest(rng1 As Range, rng2 As Range) As String
> > Dim x As Long
> >
> > Application.Volatile
> >
> > x = Application.WorksheetFunction.CountIf(rng1, rng2)
> > If x = 1 Then
> > UniqueTest = "Unique"
> > Else
> > UniqueTest = "Not Unique"
> > End If
> >
> > End Function
> >
> > HTH
> > --
> > AP
> >
> > "Myles" <[email protected]> a écrit dans le
> > message de news:[email protected]...
> > >
> > > Can anyone tell me why the following function to test the uniqueness of
> > > values in a (single and same) range fails to fire? It's based on the
> > > Worksheet Array formula *=MAX(COUNTIF(rng,rng)) *returning 1 for
> > > "unique", otherwise "not unique".
> > >
> > > Function UniqueTest(rng1 As Range, rng2 As Range) As String
> > >
> > > Application.Volatile
> > >
> > > x = Application.Evaluate("Max(Countif(rng1,rng2))")
> > > If x = 1 Then
> > > UniqueTest = "Unique"
> > > Else
> > > UniqueTest = "Not Unique"
> > > End If
> > >
> > > End Function
> > >
> > >
> > > and neither does ...
> > >
> > > Function UniqueTest2(rng1 As Range, rng2 As Range) As String
> > >
> > > Application.Volatile
> > >
> > > x = [Max(Countif(rng1,rng2))]
> > > If x = 1 Then
> > > UniqueTest2 = "Unique"
> > > Else
> > > UniqueTest2 = "Not Unique"
> > > End If
> > >
> > > End Function
> > >
> > > TIA
> > >
> > > Myles
> > >
> > >
> > > --
> > > Myles
> > > ------------------------------------------------------------------------
> > > Myles's Profile:
> > http://www.excelforum.com/member.php...o&userid=28746
> > > View this thread: http://www.excelforum.com/showthread...hreadid=522993
> > >
> >
> >
> >

--

Dave Peterson