UDF not returning anything

[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

 

[Niek Otten]

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 Tools>Options>Require variable declaration

Debug>Compile VBAproject


--
Kind regards,

Niek Otten
Microsoft MVP - Excel

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

 

[ashish128]

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 Tools>Options>Require variable declaration

Debug>Compile VBAproject

--
Kind regards,

Niek Otten
Microsoft MVP - Excel


| 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