Fedmahn Kassad
New Member
- Joined
- Sep 8, 2015
- Messages
- 8
Hi everyone,
I have some VBA code that I wrote in order to calculate what is effectively a payback period. I have a threshold ("Reserve"), and use the series of cash flows ("NWO") to figure out how many periods it takes to breach that threshold.
Iterating this formula through thousands of cells in many different sheets causes 2 issues:
1. If I have iterative calculation turned on, the formula won't stop calculating (though I can click stuff as it calculates; it just never stops recalculating)
2. Excel eventually, but inevitably, crashes (always on something like a worksheet rename or entering any formula)
Does anyone have insight as to what may cause this behavior? The second is critical as it prevents the wider use of this code.
Thanks
Reserve is always 1 cell, NWO is always a 20x1 array, both are formula-driven numbers in the millions (ie, 1,250,000). The function gives the CORRECT result, it's the side effects I would like a second opinion on.
I have some VBA code that I wrote in order to calculate what is effectively a payback period. I have a threshold ("Reserve"), and use the series of cash flows ("NWO") to figure out how many periods it takes to breach that threshold.
Iterating this formula through thousands of cells in many different sheets causes 2 issues:
1. If I have iterative calculation turned on, the formula won't stop calculating (though I can click stuff as it calculates; it just never stops recalculating)
2. Excel eventually, but inevitably, crashes (always on something like a worksheet rename or entering any formula)
Does anyone have insight as to what may cause this behavior? The second is critical as it prevents the wider use of this code.
Thanks
Code:
Function FWC(Reserve As Double, NWO As Range) As Double
Application.ScreenUpdating = False: Application.Calculation = xlCalculationManual
Dim n As Integer
Dim p As Double
p = WorksheetFunction.Sum(NWO.Columns(1)): n = 1
On Error GoTo FuncFail
Do While p < Reserve
n = n + 1: p = p + WorksheetFunction.Sum(NWO.Columns(n))
Loop
FWC = (n) - ((p - Reserve) / NWO.Columns(n))
Application.ScreenUpdating = True: Application.Calculation = xlCalculationAutomatic
Exit Function
FuncFail: FWC = CVErr(xlErrNA)
End FunctionReserve is always 1 cell, NWO is always a 20x1 array, both are formula-driven numbers in the millions (ie, 1,250,000). The function gives the CORRECT result, it's the side effects I would like a second opinion on.
