Populate a series of values

A

Adrian T

Hi:

I am trying to calculate a Net Present Value of a series
of annual rates (30 years). After I get the NPV result, I
want to populate the next consecutive cells with the
annual rates for over 30 years (when the optional blnOut
is true).

Here's my code:
-----------------------------------------------------
Public Function NPV_LossAdjustmentExp(Optional blnOut As
Boolean) As Double
Dim arrLossAdjustmentExp() As Double
Dim i As Integer
ReDim arrLossAdjustmentExp(1 To 30)

For i = 1 To 30
arrLossAdjustmentExp(i) = Losses.LossAdjustmentExp(i)
Next i
NPV_LossAdjustmentExp = NPV(Assumptions.getNPVRate,
arrLossAdjustmentExp())

If blnOut Then
ActiveCell.Next.Activate
For i = 1 To 30
ActiveCell.Value = arrLossAdjustmentExp(i)
ActiveCell.Next.Activate
Next i
End If

End Function
----------------------------------------------------

The NPV formula works just fine. But the populate command
doesn't work because the cell pointer is still in the same
position. In another word, 'ActiveCell.Next.Activate'
command can't be executed.


Is there a way to execute the NPV calculation, get out
from the cell, move to the next cell, populate it, move to
the next cell, populate it, and so on.

I am not sure if I can do it without completely finishing
the function. So, any other logic is welcome.


Thank you,

Adrian T
 
R

Robin Hammond

Adrian,

As a general principal, functions can only alter the contents of the cell
from which they were called. You therefore can't do this within the
function.

It might be possible to use Application.Caller and
Application.Caller.Parent, respectively the cell and its parent sheet, the
array you have created in the function, and a call to a different sub
passing these as parameters to populate your other values to the cell on the
right and beyond, but I haven't tested it, and it is a dangerous thing to do
because the other cells may not be empty.

Alternatively, you could use an array formula with a UDF in it, and the
function can return an array of values. This is probably far more robust.

Use something like this to see if it is an array formula rather than using
your blnOut flag.

Function Test() As Variant
Dim vArray
Dim nCounter as Integer
nItems = Application.Caller.Cells.Count
if nItems = 1 then
Test = 1
Else
Redim vArray(0 to nItems - 1)
For nCounter = 0 to nItems - 1
vArray(nCounter) = nCounter
Next nCounter
Test = vArray
End If
End Function


Robin Hammond
www.enhanceddatasystems.com
 
Top