nesting a vba function

[lior03]

hello
i want to create a udf that will sum the number of all letters in a range
[VBA]
Function sumlen(r As Range) As Integer
sumlen = Application.WorksheetFunction.Sum(Len(r))
End Function

[/VBA]
what is wronge with my function?
thanks

[Charlize]

summing each cell in the range individually.[VBA]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 Function[/VBA]Charlize

[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.[vba]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 Function[/vba]Charlize

Why bother with the worksheet functions?

[vba]

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
[/vba]

[Charlize]

It was a very, very quick and dirty answer.

[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.
[VBA]
Function turnover(price As Long, quantity As Integer)
Application.Volatile
turnover = Application.WorksheetFunction.SumProduct(price, quantity)
End Function
[/VBA]
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]

[vba]

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