PDA

View Full Version : nesting a vba function



lior03
04-06-2007, 07:16 AM
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
04-06-2007, 07:32 AM
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
04-06-2007, 08:17 AM
Why not just use an array formula?

=SUM(LEN(A1:A10))

Entered with CTRL+SHIFT+ENTER.

Bob Phillips
04-06-2007, 08:35 AM
=SUMPRODUCT(--(LEN(A1:A10)))

Bob Phillips
04-06-2007, 08:37 AM
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
04-06-2007, 12:15 PM
It was a very, very quick and dirty answer.:type

lior03
04-14-2007, 05:01 AM
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
04-14-2007, 05:36 AM
Because it isn't exposed to the WorksheetFunction method.

Norie
04-14-2007, 08:42 AM
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
04-14-2007, 02:58 PM
MsgBox Activesheet.Evaluate(&quot;SUMPRODUCT(--(A1:A10>1),--(A1:A10<10))&quot;)