Array Function won't fire

[Myles]

Can anyone tell me why the following function to test the uniqueness o
values in a (single and same) range fails to fire? It's based on th
Worksheet Array formula *=MAX(COUNTIF(rng,rng)) *returning 1 fo
"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

Myle

 

[Charles Williams]

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

 

[Ardus Petus]

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

 

[Guest]

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

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

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

 

[Dave Peterson]

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) & "))")

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

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

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