My function works but returns #VALUE [Excel 2003]

[Dynamo Nath]

I've been trying to write a function to simplify finding the number of unique items in a list. And it works too except for the fact that it won't put the answer in the cell! Clicking the fx button to access the function screen and entering the range to check shows the anticipated result at the bottom but pressing ok doesn't transfer the result to the cell.

Public Function myUniqueDataCount(data_range)

    Dim wibble As Range
    Dim myrange As String
    Dim myblanks As Integer
    Dim myanswer As Integer
    
    myblanks = Application.WorksheetFunction.CountBlank(data_range)
    
    data_range.Name = "data_range"
    
    If myblanks > 0 Then
        myUniqueDataCount = "Data contains empty fields"
        Exit Function
        Else:
        myanswer = Application.Evaluate("=SUM(IF(FREQUENCY((MATCH(data_range,data_range,0)), MATCH(data_range,data_range,0))>0,1))")
    End If
    
    myUniqueDataCount = myanswer 'this line might not be required but it was an attempt to resolve the problem.
    
End Function

I'm sure it's fairly straight forwards but I'm fresh out of ideas. Thanks in advance, Nathan.

 

[Ruddles]

You say the function "works but returns #VALUE". That suggests it's not working.

If you set a breakpoint at the top of the function code and step through it you'll see it fails at this line:-

data_range.Name = "data_range"

I don't think you can name a range like that. Don't you have to use Names.Add?

 

[RoryA]

You can name a range that way, but you can't name a range from a UDF. You could use:

Public Function myUniqueDataCount(data_range As Range)

    Dim wibble As Range
    Dim myrange As String
    Dim myblanks As Long
    Dim myanswer As Long
    Dim strAddy As String
    strAddy = data_range.Address
    
    myblanks = Application.WorksheetFunction.CountBlank(data_range)
    
    If myblanks > 0 Then
        myUniqueDataCount = "Data contains empty fields"
        Exit Function
        Else:
        myanswer = Application.Evaluate("=SUM(IF(FREQUENCY((MATCH(" & strAddy & "," & strAddy & ",0)), MATCH(" & strAddy & "," & strAddy & ",0))>0,1))")
    End If
    
    myUniqueDataCount = myanswer 'this line might not be required but it was an attempt to resolve the problem.
    
End Function

 

[Dynamo Nath]

Hi, thanks for the reply.

By stepping through the function wouldn't you be missing out the step where the argument is collected? Stepping through will mean that the data_range in brackets next to the function name will be empty.

Deleting the named range and by calling the function from a worksheet cell and stepping through I can't replicate your error. The answer appears in the function arguments dialogue box next to formula result but clicking ok doesn't return it

Naming the range in that fashion seems to work for me...

 

[Dynamo Nath]

Rory,

thanks for that I shall go try it and let you know. I'd come to the conclusion about the UDF and renaming it/storing it as a worksheet named range that the function would reference was my attempt at a solution. Maybe I should have called them different names to avoid confusion in my code; kinda makes sense to me.

 

[Dynamo Nath]

Rory,

Thanks very much that worked a treat. Reading the help file on "address" now sheds light on the phrase "..in the language of the macro" which I was struggling with before as was not sure what it meant but came up in the definition of something else when searching the help for clues.

 

[Dynamo Nath]

Sorry, one last question, why is it enclosed like this? & strAddy &

 

[RoryA]

Because you need to concatenate the value of strAddy into the formula string that is being evaluated.

 

[Dynamo Nath]

Doh! Of course! I mis-read why you put the " in to it. I'm so glad it's Friday!

Out of curiosity, do you know why in my original function, clicking in the formula bar on the cell with the function in and pressing F9 appears to return the result but takes out the function, ie just leaving the result.

many thanks for all your help.

 

[RoryA]

f9 evaluates the formula. It works that way with any function.