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.

 

[Dynamo Nath]

True, but I didn't think it would be able to do so given that it returns an error.

 

[Ruddles]

Sorry, I was dashing out to a meeting when I posted my first reply. I see things have moved on a bit since then.

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.

No. If you set a breakpoint at the first executable statement in the function code and invoke the function in the normal way, the argument will be passed to it and data_range will contain a value and execution will stop at the breakpoint waiting for you to hit F5 or F8.

@Rorya: d'oh! How many time have I told people that it's not possibe to modify a worksheet from a UDF, and I didn't spot it when it was happening right in front of my eyes!

 

[Dynamo Nath]

Ruddles,
Sorry, not sure that I'm following you. What was I modifying with my function? Is it because I was trying to update a named range?

 

[Ruddles]

Yes, I'm fairly sure that would have worked if it had been in a Sub.

 

[Dynamo Nath]

It did work when I tried it in a sub which is why I was so stumped as to why it wouldn't work in the function.

Thank you both for clearing this up for me. Nathan