Vba From Worksheet Functions

Zygomatic · Apr 3rd 2007

I'm trying to create a UDF of:
=SUM(IF(FREQUENCY(IF(LEN(F3:F29)>0,MATCH(F3:F29,F3:F29,0),""),IF(LEN(F3:F29)>0,MATCH(F3:F29,F3:F29,0),""))>0,1))

Where F3:F29 is a given range into VBA Code such as Function CountUniques(Rng as Range). I'm pretty much self-taught (ha...) in VBA, so I tried a bunch of stuff, but maybe I'm just retarded.

Wigi · Apr 3rd 2007

Re: Vba From Worksheet Functions

First, tell us what the formula and the code should achieve, given that there is a range.

**Kenneth Hobson** · Apr 3rd 2007

Re: Vba From Worksheet Functions

When using worksheetfunctions, be sure that they work before using them in udf's. Yours does not work as your function name implies. I use q="""" to concatenate a quote in str for Evaluate. Evaluate(), requires a string.

For unique counts, I have used:

Code

Function UniqueCount(uniqueRange As Range) As Long
  Dim coll As New Collection
  Dim cell As Range
  
  On Error Resume Next
  For Each cell In uniqueRange
    coll.Add cell.Value, CStr(cell.Value)
  Next cell
  On Error GoTo 0
  
  UniqueCount = coll.Count
End Function

Display More

Zygomatic · Apr 3rd 2007

Re: Vba From Worksheet Functions

I just want to put in one range (instead of the same one 6 times) to the UDF and have it run the equivalent of the worksheet functions I posted, but everything I've tried so far has just given me #VALUE! errors. I know If and Len won't work since they are in VBA, but I did get If to work like it does on a sheet using evaluate brackets

Zygomatic · Apr 3rd 2007

Re: Vba From Worksheet Functions

Thanks Ken, that works perfectly, I just made one alteration to ignore blank cells. Couldn't ask for simpler code either

Vba From Worksheet Functions

Participate now!

Share