Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
When you go to ToolsOptionsCalculations what do you have selected
under the first section? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
Sorry, I should have stated this in my original post.
On Tools-Options-Calculation Tab Calculations are set to Automatic. |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
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. |
#6
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
Right now, even F9 is not forcing the function to calculate when Sheet2
is activated. |
#7
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
What about when you do Calc Sheet from the Tools-Options-Calculation
Tab and you have Sheet 2 calculated, does that work? |
#8
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
No.
|
#9
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
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" wrote: Sorry, I should have stated this in my original post. On Tools-Options-Calculation Tab Calculations are set to Automatic. |
#10
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
Thanks for all of the suggestions. I truly appreciate your assistance.
Tonight I will try the latest recommendation and post the results. Cheers. |
#11
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
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 "VBA Dabbler" wrote: 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" wrote: Sorry, I should have stated this in my original post. On Tools-Options-Calculation Tab Calculations are set to Automatic. |
#12
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
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. |
#13
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
Please post your code if you want help with it.
"Floyd" wrote: 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. |
#14
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
VBA Dabbler,
The code is at the top of this post. |
#15
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
Oops! Thanks.
"Floyd" wrote: VBA Dabbler, The code is at the top of this post. |
#16
Posted to microsoft.public.excel.programming
|
|||
|
|||
Recalculating Functions
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. "Floyd" wrote: VBA Dabbler, The code is at the top of this post. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
problem recalculating | Excel Discussion (Misc queries) | |||
If Statement not recalculating | Excel Worksheet Functions | |||
Functions not "recalculating" when input changes ??? | Excel Worksheet Functions | |||
Functions not "recalculating" when input changes ??? | Excel Worksheet Functions | |||
Hold a value while recalculating another? | Excel Worksheet Functions |