xlcalculationAutomatic not working

switters_aka

Board Regular
Joined
Oct 26, 2010
Messages
118
Hello all, I am running this code to refresh a sheet which has a vba function to calculate values in a cell. For some reason, the function is constantly producing #value errors but on refresh, the function works. The problem is, the refresh sub() is doesn't always work. If I modify it in some way, even if it means removing a blank line, then it works. It's one of those intermittent problems which does your head in. Any ideas where I might be going wrong?

Code:
Public Sub refresh()


   Application.Calculation = xlCalculationManual

  Application.Calculate

  Application.Calculation = xlCalculationAutomatic

Application.ScreenUpdating = True
End Sub
 

Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
Some of the variables are in German but that doesn't matter.
Code:
Function C_Nr_Inst(start, von, bis As Long, x As Range) As String
Dim i As Integer
Dim Sum As Double
Dim Sum2 As Double
  
  Sum2 = 0
  For i = start To x.Row
    Sum2 = Sum2 + Sheets("ROI").Cells(i, 2).Value
  Next i
  i = von
  Sum = 0
  Do While (i < bis) And (Sum2 > Sum + ActiveSheet.Cells(i, 5).Value)
    Sum = Sum + ActiveSheet.Cells(i, 5).Value
    i = i + 1
  Loop
  
  C_Nr_Inst = Sheets("ROI").Cells(i, x.Column).Value
End Function
 
Upvote 0
Your function is only returning Sheets("ROI").Cells(i, x.Column).Value. I don't see the purpose of the other lines in it, other than incrementing i.

Also it's not a good idea to use ActiveSheet in a function. You won't get the results you expect if the right sheet isn't active when it's evaluated.
 
Upvote 0
thanks for your suggestion Andrew. It's certainly an odd problem. I changed the formula to have 'absolute' sheet references using application.worksheets() and it seems to have made the function more stable and responsive to the refresh function which, by the way is activated by a refresh button on the sheet where the formula reside (hence it's always the active sheet). But, like I said, it seems to have made everything somewhat more stable however......when I open a back up version of the same file, it knocks out the function on the previous file. Never come across something like that before when another open workbooks affects the functionality of a workbook. I'll struggle on but thanks again for your help.
 
Upvote 0
You would be much better off passing all the values you need to the function as arguments. If you do not, you need to add:
Code:
Application.Volatile True
as the first line of your function so that it will recalculate properly.
 
Last edited:
Upvote 0
Sorry for the late response Andrew. I'm not sure what you mean by passing the values as arguments but I did insert the below code as you suggested and it seems to be holding. I'll do some research on the below line of code. Thanks again.

Code:
Application.Volatile True</pre>
 
Upvote 0
I haven't see this question answered yet and I'm having the same problem. I'm running an application that I run once a month and I have not made any changes to the code in over a year, but today when I execute this line of code, Application.Calculation = xlCalculationAutomatic, my VBA program just stops. There's no error message, no break, no crash; the program just stops, as if it encountered an "End" statement.
 
Upvote 0
Hi, and welcome to the forum.

Does the code otherwise work as normal? Do you have any conditional formatting that uses UDFs?
 
Upvote 0
Hi, and welcome to the forum.

Does the code otherwise work as normal? Do you have any conditional formatting that uses UDFs?

There's only one line of code in my test.

Sub Test()
Excel.Application.Calculation = xlCalculationAutomatic
End Sub

When I single step through the subroutine, it never reaches the End Sub.

No conditional formatting or UDFs
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,952
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top