nesting a vba function

[lior03]

hello
i want to create a udf that will sum the number of all letters in a range

Function sumlen(r As Range) As Integer
sumlen = Application.WorksheetFunction.Sum(Len(r))
End Function


what is wronge with my function?
thanks

[Charlize]

summing each cell in the range individually.Function sumlen(r As Range) As Integer
Dim cell As Range
For Each cell In r
sumlen = sumlen + Application.WorksheetFunction.Sum(Len(cell))
Next cell
End FunctionCharlize

[Norie]

Why not just use an array formula?

=SUM(LEN(A1:A10))

Entered with CTRL+SHIFT+ENTER.

[Bob Phillips]

=SUMPRODUCT(--(LEN(A1:A10)))

[Bob Phillips]

summing each cell in the range individually.Function sumlen(r As Range) As Integer
Dim cell As Range
For Each cell In r
sumlen = sumlen + Application.WorksheetFunction.Sum(Len(cell))
Next cell
End FunctionCharlize

Why bother with the worksheet functions?



Function sumlen(r As Range) As Integer
Dim cell As Range
For Each cell In r
sumlen = sumlen + Len(cell.Value)
Next cell
End Function

[Charlize]

It was a very, very quick and dirty answer.:type

[lior03]

hello
i have a table with two columns .price & quantity of various goods.i want to get the overall value of the goods supplied.

Function turnover(price As Long, quantity As Integer)
Application.Volatile
turnover = Application.WorksheetFunction.SumProduct(price, quantity)
End Function

why can't i use the sum product in a udf? .it work pefectly as a formula.
thanks

[Bob Phillips]

Because it isn't exposed to the WorksheetFunction method.

[Norie]

The Sumproduct function expects ranges as arguments, not just numbers.

There is one problem with the function in VBA, you can't reallyh use coercion.

eg using the double -- won't work

[Bob Phillips]

MsgBox Activesheet.Evaluate(&quot;SUMPRODUCT(--(A1:A10>1),--(A1:A10<10))&quot;)