Thanks Thanks:  0
Likes Likes:  0
Page 1 of 2 12 LastLast
Results 1 to 10 of 12

Thread: xlcalculationAutomatic not working

  1. #1
    Board Regular
    Join Date
    Oct 2010
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default xlcalculationAutomatic not working

    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

  2. #2
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: xlcalculationAutomatic not working

    Better post your function.
    Microsoft MVP - Excel

  3. #3
    Board Regular
    Join Date
    Oct 2010
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: xlcalculationAutomatic not working

    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

  4. #4
    MrExcel MVP
    Moderator
    Andrew Poulsom's Avatar
    Join Date
    Jul 2002
    Posts
    73,092
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    1 Thread(s)

    Default Re: xlcalculationAutomatic not working

    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.
    Microsoft MVP - Excel

  5. #5
    Board Regular
    Join Date
    Oct 2010
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: xlcalculationAutomatic not working

    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.

  6. #6
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,336
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: xlcalculationAutomatic not working

    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.

  7. #7
    Board Regular
    Join Date
    Oct 2010
    Posts
    118
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: xlcalculationAutomatic not working

    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

  8. #8
    New Member
    Join Date
    Jan 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: xlcalculationAutomatic not working

    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.

  9. #9
    MrExcel MVP
    Moderator
    RoryA's Avatar
    Join Date
    May 2008
    Location
    UK
    Posts
    32,336
    Post Thanks / Like
    Mentioned
    25 Post(s)
    Tagged
    3 Thread(s)

    Default Re: xlcalculationAutomatic not working

    Hi, and welcome to the forum.

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

  10. #10
    New Member
    Join Date
    Jan 2015
    Posts
    3
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: xlcalculationAutomatic not working

    Quote Originally Posted by RoryA View Post
    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

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •