Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
UDF not returning anything
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
UDF not returning anything
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 message ups.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 | |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
UDF not returning anything
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Returning an age | Excel Discussion (Misc queries) | |||
Returning #N/A | Excel Discussion (Misc queries) | |||
UDF returning #VALUE! why? | Excel Discussion (Misc queries) | |||
Returning a 0 Value | Excel Worksheet Functions | |||
Returning more than one value | Excel Discussion (Misc queries) |