Excel crashes when re-activating Automatic Calculation after running VBA

[Cintler]

Hello,

I recently wrote a simple Macro to calculate something using a for-loop. The Macro works fine, except that Excel crashes every time at the end. After some researching I found that the probem lies with re-activating the Automatic Calculation (which I set on 'manual' at the beginning of the sub). When I leave it standing on manual there is no problem, untill I later on save the sheet or otherwise recalculate it. The strange thing however is that when I recalculate a part of the sheet with another sub with just the line 'Activeworkbook.Sheets(1).Range("1:20000").Calculate' there are no problems even though it covers all the cells with data I have in the worksheet. When I change that sub however to 'Activeworkbook.Sheets(1).Calculate' Excel yet again crashes.

My code is the following:

Sub code3()

Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False

Application.Calculation = xlCalculationManual
   
Dim shData As Worksheet
Dim n As Range
Dim m As Range

Dim StartTime As Double
Dim MinutesElapsed As String

 StartTime = Timer

Set shData = ActiveWorkbook.Worksheets(1)


Set n = shData.Range("X3")
Set m = shData.Range("X4")


Dim i As Integer, j As Integer

For i = 1 To n
    For j = 1 To m

    With shData
    
    Range("Y1:AC1").Value = Range(shData.Cells(6 + i, 3), shData.Cells(6 + i, 7)).Value
    Range("B3:F3").Value = Range(shData.Cells(6 + j, 3), shData.Cells(6 + j, 7)).Value

    Range("A1:AC4").Calculate
    Range(shData.Cells(5, 1), shData.Cells(6 + n, 21)).Calculate
    shData.Cells(6 + j, 26 + i).Value = Range("U5").Value
 
    End With
       
       Next j
    Next i
            

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.EnableEvents = True

ActiveSheet.DisplayPageBreaks = True


  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation

' Application.Calculation = xlCalculationAutomatic


End Sub

With, as you can see, the last rule disabled since that is the only way it won't immediately crash.

Some remarks:

- This macro is the simplified version of another macro which works perfectly (it also uses the 'Application.Calculation = xlCalculationAutomatic' line)
- On different computers this macro also fails, which leads me to believe the fault lies not within Excel or my OS but instead within my code
- The crashing goes as follows: The statusbar shows: "Calculating: (4 processors(0%))" and then Excel closes after showing the prompt 'Microsoft Excel has stopped working' etc.
- The Task Manager does not show another instance of Excel.exe being opened (I found this as a potential sympton on another thread)

I hope you can help me find out what the problem, thanks already!

Kind regards,

Cintler

 

[Cintler]

up

 

[Cintler]

up

 

[Cintler]

up

 

[NdNoviceHlp]

This doesn't look right...

With shData
     .Range("Y1:AC1").Value = .Range(.Cells(6 + i, 3), .Cells(6 + i, 7)).Value
    .Range("B3:F3").Value = .Range(.Cells(6 + j, 3), .Cells(6 + j, 7)).Value
    .Range("A1:AC4").Calculate
    .Range(.Cells(5, 1), .Cells(6 + n, 21)).Calculate
    .Cells(6 + j, 26 + i).Value = .Range("U5").Value
 End With

This likely won't help but gives U a bump. I'd suggest trialling not messing with the enable events and/or screen updating and see what happens. Dave

 

[Cintler]

Thanks for you suggestions. I tried it without enabling events/screenupdating and even the displaying, it all made no difference. And I do require the the manual calculation since it makes the code about 3/4 times faster, and even with that speed it can take over 10 hours.

 

[NdNoviceHlp]

10 hours???? Yuck! . So your code basically just moves some data from 2 range to 2 other ranges, does some calculations and then stores the results repetitively (loops). What are your I & J values? I'm not sure why U are messing with the page breaks... I've never seen anything about that changing the speed of code operation. U could trial turning off the auto document recovery feature which sometimes produces mysterious errors. Not sure if I have any other suggestions. Dave

 

[Fluff]

@Cintler
Do you have any conditional formatting, or named ranges?
Also if you go to A1 & then Ctrl End does that take you to end bottom right hand corner of your data?
Another thing, what are the values in X3 & X4?

 

[NdNoviceHlp]

This might be relevant...
http://www.vbaexpress.com/forum/sho...ing-Limitations-for-large-VBA-code&highlight=
Dave
edit: See Mike Erickson's comments

 

[Cintler]

Yes, that's right. It compares two dataranges with each other, calculates a certain value for this comparison and then stores this value. At the end I calculate the average for each row to see which has the lowest average and that's what I'm looking for. The I and J values differ, from 4 to 1200, 150 by 150 is regular but also 20 by 600. The 10 hours is an extreme, but it has occured. Most of the time the calculation time is under an hour.

The pagebreak, and other advice, I got from: http://datapigtechnologies.com/blog/index.php/ten-things-you-can-do-to-speed-up-your-excel-vba-code/

The auto document recovery also does not make any difference. Perhaps if you look at the previous version I mentioned, in which there are no problems but which to my view is only less efficient and uses three worksheets instead of 1. Can you see the major difference between these two versions with makes that the one version does not crash and the other does?

Sub previousversion()


Application.ScreenUpdating = False

Application.DisplayStatusBar = False

Application.EnableEvents = False

ActiveSheet.DisplayPageBreaks = False

Application.Calculation = xlCalculationManual
   
Dim shData1 As Worksheet
Dim shData2 As Worksheet
Dim shData3 As Worksheet
Dim n As Range

Dim StartTime As Double
Dim MinutesElapsed As String

'Remember time when macro starts
  StartTime = Timer


Set shData1 = ActiveWorkbook.Worksheets(5)
Set shData2 = ActiveWorkbook.Worksheets(3)
Set shData3 = ActiveWorkbook.Worksheets(2)

Set n = shData1.Range("C4")

Dim i As Integer, j As Integer

For i = 1 To n
    For j = 1 To n


    With shData1
    
    Range("L1:P1").Value = Range(shData1.Cells(1 + i, 5), shData1.Cells(1 + i, 9)).Value
    Range("L2:P2").Value = Range(shData1.Cells(1 + j, 5), shData1.Cells(1 + j, 9)).Value

    shData1.Range("L4:Q8").Calculate
    shData1.Range("K10:L10").Calculate

    End With
    
    
    shData2.Range("H11:I11").Value = shData1.Range("K10:L10").Value
    shData2.Range("B11:F11").Value = shData1.Range("L2:P2").Value

    shData2.Range("K2:P11").Calculate
    
    
    shData3.Range(shData3.Cells(3, 1), shData3.Cells(3 + n, 163)).Calculate
    
    shData1.Range("C10").Calculate
    
    shData1.Cells(1 + j, 20 + i).Value = shData1.Range("C10").Value


       Next j
    Next i
            
    shData1.Range(shData1.Cells(2, 1), shData1.Cells(1 + n, 1)).Copy
    shData1.Range("U1").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=True

Application.ScreenUpdating = True

Application.DisplayStatusBar = True

Application.EnableEvents = True

ActiveSheet.DisplayPageBreaks = True

Application.Calculation = xlCalculationAutomatic


    
    'Determine how many seconds code took to run
  MinutesElapsed = Format((Timer - StartTime) / 86400, "hh:mm:ss")

'Notify user in seconds
  MsgBox "This code ran successfully in " & MinutesElapsed & " minutes", vbInformation


End Sub

Regards,

Cintler