Writing a custom function

  • Thread starter Thread starter sue
  • Start date Start date
S

sue

Hi all,

I am attempting to write (and I aim to be successful at it) a custom Excel function using VBA. The
function will encapsulate the calculations and error checking necessary to produce the PayBack
period in years for a capital investment. I have been browsing through numerous sources, including
the fine book "Excel 2003 Formulas", but can seem to get it!

What I am having problems with is extracting the values from a range passed as one of the arguments.
The signature is shown below:

Public Function Return_PayBack(CumFlow As Range, NetFlow As Range, period As Integer) As Single

I would like to be able to call the function like:
Return_PayBack(C15:G15,C16:G16, 7)

The last algorithm I tried was:

intCount = 0
For Each f In CumFlow
CFlow(intCount) = f.Value
intCount = intCount + 1
Next f

Return_PayBack = CFlow(0) ' return this value to debug!

The code to produce the PayBack period would be a snap...if I could get the values from the spreadsheet.

Any ideas?

Thanks so much,

Sue
 
Hi Sue!

I just wrote two similar functions recently, having gotten some help from
this newsgroup. The functions are Simple Payback, =SPAYBACK(range) and
Discounted Payback, =DPAYBACK(discount rate, range). Here's the code. Please
revert with any improvements!


Function SPayback(InputRange As Range) As Double
Dim rng As Range, Counter As Integer
Dim Total As Double, TotalPlus As Double, TotalMinus As Double
Counter = -1
If InputRange(1) >= 0 Then GoTo ErrorTrap 'No initial outlay
For Each rng In InputRange
Total = Total + rng.Value
If Total >= 0 Then
TotalPlus = Total
TotalMinus = Total - rng.Value
SPayback = Counter - TotalMinus / (TotalPlus - TotalMinus): Exit
Function
End If
Counter = Counter + 1
Next
'Error Payback period exceeds investment horizon (selected range)
ErrorTrap: SPayback = CVErr(xlErrNA)
End Function


Function DPayback(Disc As Double, InputRange As Range) As Double
Dim rng As Range, Counter As Integer
Dim Total As Double, TotalPlus As Double, TotalMinus As Double
Counter = -1
If InputRange(1) >= 0 Then GoTo ErrorTrap 'No initial outlay
For Each rng In InputRange
Total = Total + rng.Value / (1 + Disc) ^ (Counter + 1)
If Total > 0 Then
TotalPlus = Total
TotalMinus = Total - rng.Value / (1 + Disc) ^ (Counter + 1)
DPayback = Counter - TotalMinus / (TotalPlus - TotalMinus): Exit
Function
End If
Counter = Counter + 1
Next
'Error Payback period exceeds investment horizon (selected range)
ErrorTrap: DPayback = CVErr(xlErrNA)
End Function



sue said:
Hi all,

I am attempting to write (and I aim to be successful at it) a custom Excel function using VBA. The
function will encapsulate the calculations and error checking necessary to produce the PayBack
period in years for a capital investment. I have been browsing through numerous sources, including
the fine book "Excel 2003 Formulas", but can seem to get it!

What I am having problems with is extracting the values from a range
Click to expand...
passed as one of the arguments.
The signature is shown below:

Public Function Return_PayBack(CumFlow As Range, NetFlow As Range, period As Integer) As Single

I would like to be able to call the function like:
Return_PayBack(C15:G15,C16:G16, 7)

The last algorithm I tried was:

intCount = 0
For Each f In CumFlow
CFlow(intCount) = f.Value
intCount = intCount + 1
Next f

Return_PayBack = CFlow(0) ' return this value to debug!

The code to produce the PayBack period would be a snap...if I could get
Click to expand...
the values from the spreadsheet.
 
sue said:
I am attempting to write (and I aim to be successful at it) a custom
Excel function using VBA. The function will encapsulate the calculations
and error checking necessary to produce the PayBack period in years for a
capital investment. I have been browsing through numerous sources,
including the fine book "Excel 2003 Formulas", but can seem to get it!

What I am having problems with is extracting the values from a range
passed as one of the arguments.

Public Function Return_PayBack(CumFlow As Range, NetFlow As Range, _
period As Integer) As Single
Click to expand...

First, unless you have a *very* compelling reason for using Single, *never*
use Single. Use Double instead. There are some old-timers who would argue
this point, but they consistently fail to realize that damn near all
floating point arithmetic now takes place in hardware at double or extended
precision. The only conceivable advantage to using Single is that such
variables may take up half the storage of Double variables. For a function
return type, the potential 4 byte savings is nugatory.
I would like to be able to call the function like:
Return_PayBack(C15:G15,C16:G16, 7)

The last algorithm I tried was:

intCount = 0
For Each f In CumFlow
CFlow(intCount) = f.Value
intCount = intCount + 1
Next f
Click to expand...

Don't. You can assign the values in a single-area range as an array of
variant type entries to a variable of variant type. For example,

Dim CFlow As Variant
CFlow = CumFlow.Value

This will be 1-based rather than 0-based, but that's easy enough to deal
with compared to the simplicity (and speed) of this approach.
 
Back
Top