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]

@Fluff

I do have some conditional formatting, though not within the range that is manually calculated in the code, I do not have named ranges.
A1 & then Ctrl End does indeed take me to the bottom right hand corner of my data.

The values in X3 and X4 are integer values differing between 4 and 1200, these I manually set before running the code.

 

[Cintler]

@NdNoviceHlp

My n and m value (in X3 and X4) are almost never over 1000, certainly not 1000000, and I am now trialing with 10x10 and the problem still exists. I even tried to remove the n and m in the code and instead changed it to manually with:

For i = 1 To 10
    For j = 1 To 10

This also made no difference

 

[Fluff]

Try creating a copy of the workbook, delete all the conditional formatting & see if that makes a difference

 

[Cintler]

@Fluff

Thanks for you suggestion, I tried it but it did not make a difference unfortunately.

 

[Fluff]

Assuming that you can recalculate the sheet, prior to running the macro, without any problems. Then unfortunately, I've run out of ideas, as there's nothing in the code itself, that rings alarm bells.

 

[Cintler]

It seems that for now I have found a solution, I divided the manual calculation in the code up into smaller pieces:

.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("N3:V3").Calculate
    Range(.Cells(7, 15), .Cells(6 + n, 15)).Calculate
    Range(.Cells(7, 16), .Cells(6 + n, 16)).Calculate
    Range(.Cells(7, 17), .Cells(6 + n, 17)).Calculate
    Range(.Cells(7, 18), .Cells(6 + n, 18)).Calculate
    Range(.Cells(7, 19), .Cells(6 + n, 19)).Calculate
    Range(.Cells(7, 20), .Cells(6 + n, 20)).Calculate
    Range(.Cells(7, 21), .Cells(6 + n, 21)).Calculate
    .Range("U5").Calculate
    
    .Cells(6 + j, 26 + i).Value = .Range("U5").Value

That seems to hold for now, although I am not completely sure why it was a problem in the first place.
If it still proves to be unstable in a later instance I will respond on this thread again, for now I assume it is solved.

Thanks for thinking with me on this!