On Nov 6, 2:48 pm, "Niek Otten" wrote:
Hi Ashish,

If you compile your UDF you'll find that you use serveral undeclared variables
Always use Option Explicit and make that happen automatically with ToolsOptionsRequire variable declaration

DebugCompile VBAproject

--
Kind regards,

Niek Otten
Microsoft MVP - Excel

"ashish128" wrote in oglegroups.com...

| 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
|

Thank you Niek,

I will take care of it and many thanks to you for pointing this out.

Regards

Ashish Sharma