User Defined Function

[RK]

I'm trying to do a User Defined Function (UDF) that calculates the payback
period for an investment. But before that, I can't even get right a simple
UDF that returns the sum of a selected range (similar to Excel's SUM
function). What's wrong with the following code?



Function Grandtotal(ParamArray inputrange() As Variant) As Double
Dim cell As Variant, Total As Double
Total = 0
For Each cell In inputrange
Total = Total + cell.Value
Next
Grandtotal = Total
End Function


I keep getting a #VALUE! result.

Thanks.

 

[Frank Kabel]

Hi
try
Function Grandtotal(ParamArray inputrange() As Variant) As Double
Dim cell As Variant, Total As Double
Dim i
Total = 0
For i = 0 To UBound(inputrange)
For Each cell In inputrange(i)
Total = Total + cell.Value
Next
Next
Grandtotal = Total
End Function

 

[Rob van Gelder]

Here's an example:

Function GrandTotal(InputRange As Range) As Double
Dim rng As Range, dblTotal As Double

dblTotal = 0
For Each rng In InputRange
dblTotal = dblTotal + rng.Value
Next
GrandTotal = dblTotal
End Function

 

[RK]

Thanks. It certainly works, but why is there a for-next statement nested
within another for-next loop? Can I do away with one of them?

Thanks.

 

[RK]

Thank you. I was of the impression that the ParamArray keyword was required
if you wanted to code a UDF with a range as an argument. I was wrong!

 

[RK]

Hi! I've managed to shorten it a little by omiting the dblTotal variable:-

Function GrandTotal(InputRange As Range) As Double
Dim rng As Range
GrandTotal = 0
For Each rng In InputRange
GrandTotal = GrandTotal + rng.Value
Next
End Function

Will GrandTotal be reset to zero each time this subroutine runs? The
function seems to work even with the "GrandTotal = 0" statement removed.

Thanks again!

 

[Frank Kabel]

Hi
depend:
If you want to accept multiple ranges (as in SUM) you need to for-next
loops (one for each range). If you can deal with only one range take
Rob's solution.

 

[Frank Kabel]

Hi
yes it will return to zero each time. Though I would leave that
statement

 

[Rob van Gelder]

Most implementations I've seen only set the return value once. I don't think
there is any good reason to avoid your approach though (except maybe it's a
pain to search replace if you rename the procedure)
The initial value of a double variable will be 0.

Good programmers have learned not to make assumptions.
It generally makes programmers nervous to rely on some external mechanism to
initialise variables for them, even if it is the compiler.
This is why you see = 0 in a lot of code. This nervous attitude is some of
the reason toward why VB programmers don't rely on object default
properties. eg. They use MsgBox Range("A1").Value instead of MsgBox
Range("A1")