No Out put from UDF

[ashish128]

Hi,
I am not a developer and have learnt VBS just from groups likes this
one.

I wrote a UDF (actually it was of some other person explaining
something else but I modified it to my use)

The problem is this that this function is not returning anything.

Logic Behind: this function takes a range as first parameter and all
other parameters are optional

This function is intended to return either CSV string or count of
unique values from a range.

if you provide second parameter as "c" then it will calculate the
count else CSV string and third parameter is to replace comma with any
othe delimiter.

Following is the code

Public Function nodup(ByRef rRng As Excel.Range, Optional ByVal
str_data_or_count As String = "", Optional ByVal str_Delim As String =
"") As String
Dim No_Duplicates As New Collection
Dim int_count As Integer
Dim rCell As Range

On Error Resume Next
For Each rCell In rRng
If rCell.RowHeight <> 0 Then
If IsEmpty(rCell) Then
'do nothing
Else
No_Duplicates.Add Cell.Value, CStr(Cell.Value)
End If
End If
Next rCell
On Error GoTo 0
For i = 1 To No_Duplicates.Count - 1
nodup = nodup & str_Delim & No_Duplicates(i)
Next i
nodup = Mid(nodup, Len(str_Delim) + 1)
If str_target = "c" Then
nodup = CStr(No_Duplicates.Count)
End If

End Function


Please advice and also tell me how to debug this function (F8 is not
working on it)

Thanks & Regards

Ashish Sharma

 

[Andy Pope]

Hi,

This line with the For Each loop references a object that does not exist and
is not being caught due to your error trapping.

No_Duplicates.Add Cell.Value, CStr(Cell.Value)

Change to,

No_Duplicates.Add rCell.Value, CStr(rCell.Value)

You may find it useful to add Option Explicit to the top of your code
module. This will at least force you to declare all your variables. And
would have caught this particular problem when you compiled the code.

Cheers
Andy

 

[Ivyleaf]

Hi,

Andy hit the nail right on the head with the Cell -> rCell.

In addition, your variable for selecting the 'count' option is defined
in the function as "str_data_or_count", yet you refer to it later by
checking if "str_target = "c"". As a result, the count function won't
work for you. I would also suggest moving this test above the part
where you build the CSV string, otherwise you are wasting time in the
function by building a string that isn't used.

One other piece of advice would be that if you define your function as
a variant, then the count function could return the CSV string, or the
count as a number... I would assume that having the count as a number
would be far more useful if you are using that value anywhere else on
the sheet.

Cheers,
Ivan.

 

[ashish128]

Hi,

This line with the For Each loop references a object that does not exist and
is not being caught due to your error trapping.

No_Duplicates.Add Cell.Value, CStr(Cell.Value)

Change to,

No_Duplicates.Add rCell.Value, CStr(rCell.Value)

You may find it useful to add Option Explicit to the top of your code
module. This will at least force you to declare all your variables. And
would have caught this particular problem when you compiled the code.

Cheers
Andy
--


















- Show quoted text -

Thanks friend,

I know my errors now and I really thank you for pointing them out

With Regards

Ashish Sharma

 

[ashish128]

Hi,

Andy hit the nail right on the head with the Cell -> rCell.

In addition, your variable for selecting the 'count' option is defined
in the function as "str_data_or_count", yet you refer to it later by
checking if "str_target = "c"". As a result, the count function won't
work for you. I would also suggest moving this test above the part
where you build the CSV string, otherwise you are wasting time in the
function by building a string that isn't used.

One other piece of advice would be that if you define your function as
a variant, then the count function could return the CSV string, or the
count as a number... I would assume that having the count as a number
would be far more useful if you are using that value anywhere else on
the sheet.

Cheers,
Ivan.

Thanks Ivan,

I have corrected the mistakes as told by you and will take care of it
going forward.

Regards

Ashish Sharma