Recalculating Functions

[Floyd]

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(Rng1Addr),
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(LeftTextSegment1,
RightTextSegment1), Range(LeftTextSegment2, RightTextSegment2))
ElseIf Current_Year = EOFL Then
YearlyAmortization =
Application.WorksheetFunction.Sum(Range(CounterMarker.Offset(-(YearDelta
- 1), -2).AddressLocal() & ":" & CounterMarker.Offset(0,
-2).AddressLocal())) _
-
Application.WorksheetFunction.Sum(Range(CounterMarker.Offset(-(YearDelta
- 1), 0).AddressLocal() & ":" & CounterMarker.AddressLocal()))
End If
End Function

 

[Chip]

When you go to Tools>Options>Calculations what do you have selected
under the first section?

 

[Floyd]

Sorry, I should have stated this in my original post.

On Tools-Options-Calculation Tab

Calculations are set to Automatic.

 

[Chip]

I think you can just use the call "Calculate"

I got it from the macro recorder and it works for me....i.e.

Sub calc2()
Calculate
End Sub


The Calculate line will force calculation

 

[Floyd]

Chip,

I tried that and it too is a no go.

I was wondering whether it would be necessary to place code in Sheet1
to tell Sheet2's function to calculate. As long as Sheet2 is not
activated, it does not calculate.

Thanks for your time and assistance.

Cheers.

 

[Floyd]

Right now, even F9 is not forcing the function to calculate when Sheet2
is activated.

 

[Chip]

What about when you do Calc Sheet from the Tools-Options-Calculation
Tab and you have Sheet 2 calculated, does that work?

 

[Floyd]

No.

 

[Guest]

Try the following:

Application.CalculateFull

It forces recalculation of all open workbooks.

If you want more control of the scope of the calculation, then use:

expression.Calculate

Regards,
VBA Dabbler

 

[Floyd]

Thanks for all of the suggestions. I truly appreciate your assistance.

Tonight I will try the latest recommendation and post the results.

Cheers.

 

[Guest]

Try placing the following code in each Sheet object in your workbook
VBAProject.

Private Sub Worksheet_Change(ByVal Target As Range)
ActiveSheet.Calculate
End Sub

 

[Floyd]

I tried several variations of all that has been suggested here. Then I
noticed that it is predominately the first function call that reports
#VALUE!. That is until you click on the cell and force it to
calculate. All other calls to this function calculate without any
difficulties.

Therefore, it may be a logic error. To use OFFSET I have CounterMarker
referenced to a blank cell in the first use of the function. If I
place an arbitrary value into the blank cell, it will calculate as if I
had forced it to do so. Then when all of the functions calculate a
#VALUE! reappears in the first instance.

I had thought that I could pass a blank cell into the function without
any problems. I even tried to have CounterMarker as Optional, but it
too failed.

Any ideas as to why the first occurence fails to calculate?

Thank you.

 

[Guest]

Please post your code if you want help with it.

 

[Floyd]

VBA Dabbler,

The code is at the top of this post.

 

[Guest]

Oops! Thanks.

 

[Guest]

What are the lines of code for your 'TestText' sub?

Also, could you please explain your various function parameters and what
should example values be? I am dangerously familiar with amortization,
DCFROR, NPV, etc., but not: 'AmortizationFactors', 'IntanDrillCost', 'EOFL',
and 'CounterMarker'.

Also, information about the data you are passing the function would be of
assistance.