The function provided below will not recalculate when inputs are
changed on another sheet. For example, if I change an input value on
Sheet 1 and Sheet2 contains the function, it will not recalculate.
This is unless I open Sheet2 and hit F9.

Is there anyway to force the function to calculate while I am changing
input values on Sheet1? The functions results are displayed at the top
of Sheet1.


1. Application.Volatile True
2. Application.Calculate
3. Application.Calculation = xlCalculationAutomatic

Thanks in advance.


Function YearlyAmortization(Current_Year As Double, Year_First As
Double, AmortizationFactors As Variant, _
IntanDrillCost As Variant, EOFL As Double, CounterMarker As Range) As
Variant
Dim Rng1 As Range
Dim Rng2 As Range
Dim Rng1Addr As String
Dim Rng2Addr As String
Dim YearDelta As Double
Dim LeftTextSegment1 As String
Dim RightTextSegment1 As String
Dim LeftTextSegment2 As String
Dim RightTextSegment2 As String

Set Rng1 = IntanDrillCost
Set Rng2 = AmortizationFactors
YearDelta = Current_Year - Year_First

'Test which year the calculations apply to (Year1, Year1+1, EOFL
calculations differ)
If Current_Year <= EOFL And Current_Year = Year_First Then
Rng1Addr = Rng1.Offset(-WorksheetFunction.Min(YearDelta, 5), 0)
_
.Resize(WorksheetFunction.Min(YearDelta + 1, 6), 1).Address
Rng2Addr = Rng2.Offset(-WorksheetFunction.Min(YearDelta, 5), 0)
_
.Resize(WorksheetFunction.Min(YearDelta + 1, 6), 1).Address
YearlyAmortization =
Application.WorksheetFunction.SumProduct(Range(Rng 1Addr),
Range(Rng2Addr))
ElseIf Current_Year < EOFL And Current_Year Year_First Then
Rng1Addr = Rng1.Offset(-WorksheetFunction.Min(YearDelta, 5), 0)
_
.Resize(WorksheetFunction.Min(YearDelta + 1, 6), 1).Address
Rng2Addr = Rng2.Offset(-WorksheetFunction.Min(YearDelta, 5), 0)
_
.Resize(WorksheetFunction.Min(YearDelta + 1, 6), 1).Address

Call TestText(Rng1Addr, LeftTextSegment1, RightTextSegment1)
Call TestText(Rng2Addr, LeftTextSegment2, RightTextSegment2)
YearlyAmortization =
Application.WorksheetFunction.SumProduct(Range(Lef tTextSegment1,
RightTextSegment1), Range(LeftTextSegment2, RightTextSegment2))
ElseIf Current_Year = EOFL Then
YearlyAmortization =
Application.WorksheetFunction.Sum(Range(CounterMar ker.Offset(-(YearDelta
- 1), -2).AddressLocal() & ":" & CounterMarker.Offset(0,
-2).AddressLocal())) _
-
Application.WorksheetFunction.Sum(Range(CounterMar ker.Offset(-(YearDelta
- 1), 0).AddressLocal() & ":" & CounterMarker.AddressLocal()))
End If
End Function