Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Out put from UDF
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.programming
|
|||
|
|||
No Out put from UDF
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 -- Andy Pope, Microsoft MVP - Excel http://www.andypope.info "ashish128" wrote in message oups.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.programming
|
|||
|
|||
No Out put from UDF
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. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Out put from UDF
On Nov 6, 2:16 pm, "Andy Pope" wrote:
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 -- Andy Pope, Microsoft MVP - Excelhttp://www.andypope.info"ashish128" wrote in message oups.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- Hide quoted text - - Show quoted text - Thanks friend, I know my errors now and I really thank you for pointing them out With Regards Ashish Sharma |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
No Out put from UDF
On Nov 6, 3:10 pm, Ivyleaf wrote:
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|