Excel randomly stops responding after macro & data input

[renesis]

Hi guys,

first thanks to all of you who take part in this community, this is my first ever post but I've been lurking around and learning a lot from the content I see here.

Now on with the question,

I have created a workbook with Macros in it which used to run fine for the first 400 or so sheets. Now I'm at over 650 and Excel randomly stops responding upon data entry after running one macro which hasn't changed since the beginning.

It can go on for a few days without a single hiccup and then it can crash 5 times in a single day... I tried it on different PC's even very powerful ones and it still happens therefore I don't think it's a memory issue.

I also tried to export and reimport my code following this procedure https://exceleratorbi.com.au/excel-keeps-crashing-check-your-vba-code/
however it still crashes.

Here's the said macro :

Sub NouvellePolice()'
' NouvellePolice Macro
'
    Application.Calculation = xlCalculationManual
    Application.ScreenUpdating = False
    ActiveWorkbook.Unprotect Password:="TEST"
        Sheets("Base_Police").Copy After:=Sheets(Sheets.Count)
        
    ActiveWorkbook.Sheets("Base_Police (2)").Activate
    Sheets("Base_Police (2)").Range("K73").Value = Range("D1").Value
    Sheets("Base_Police (2)").Range("K74").Value = Range("D2").Value
    Sheets("Base_Police (2)").Range("K75").Value = Range("D6").Value
    Sheets("Base_Police (2)").Name = Sheets("DataPolices").Range("B1")
   
    ActiveWorkbook.Sheets("DataPolices").Activate
    Sheets("DataPolices").Range("B" & Rows.Count).End(xlUp).Offset(1).Value = Range("B1").Value
    Sheets("DataPolices").Range("A" & Rows.Count).End(xlUp).Offset(1).Value = Range("A1").Value
        
    Worksheets(Worksheets.Count).Visible = True
    Worksheets(Worksheets.Count).Select
           ActiveWorkbook.Protect Password:="TEST", Structure:=True
           Application.Calculation = xlCalculationAutomatic
           Application.ScreenUpdating = True
     End
      End Sub

Do you guys see anything wrong with it that could explain my random problem?

Thanks,

 

[Yongle]

The macro itself seems innocent enough

Consider

POSSIBLE WORKAROUND - RESET EXCEL
1. After running the macro, save & close the workbook BEFORE doing anything else. Close any other workbooks. Close Excel completely.
- if nothing else it will prove\disprove the connection between the macro and Excel failing to respond

DODGY USED RANGE ??
2. Perhaps there is a problem in one of the sheets making Excel think that the workbook is much bigger than it is
(eg a dot in cell XFD1048576 in the 420th sheet!)

Amend this macro and run it against the workbook - if the message box is empty then all is good
Perhaps the test should be 1000 cells in a worksheet - use your judgement

Sub UsedRangeCells()
    Dim ws As Worksheet, c As Long, msg As String
    For Each ws In ActiveWorkbook.Sheets
        c = ws.UsedRange.Cells.CountLarge
        If c > 100000 Then msg = msg & vbCr & ws.Name & vbTab & ws.UsedRange.Cells.CountLarge
    Next
    MsgBox msg
End Sub

CORRUPTION ??
3. Perhaps the workbook contains a minor instability
- any recent backup copy is in the same state
- it could have happened due to Excel version change
- it could have happened due to a power cut at the wrong time etc
- one day it may prevent the file opening etc

REDUCE THE SIZE OF THE WORKBOOK TO REDUCE RISK ??
4. Split the workbook into 2 (or 3)
(A) the sheets you regularly need access to
(B) those that are rarely referred to
- periodically update B and slim down A
- the problem occurs when inputting which is frustrating
- could input area be a separate tiny file which would both be quicker to work in and less prone to failing ??


You probably have a very practical reason to have over 650 sheets in one workbook, but the bigger the workbook, the bigger the strain on resources
- 650 sheets in one workbook + multi-tab browser open + Outlook open + etc, etc = "living life on the edge"

 

[renesis]

Hi Yongle,

thanks a lot for your time,

I did try some of your suggestions :


1. I used to have a save function at the end of that macro it was one of the first things I tried actually to see if the problem would go away and it didn't, I was only slower since the file is about 25mb saving it is kind of long. I'm pretty positive the macro is done running when the problem happens but it ONLY happens after running it, which is still weird. The fact that the problem only happens from time to time only makes it harder to diagnose..

2. I ran your test, most of the sheets are based on the same template (hence the macro) and they use about 10,000 cells, I have one large one which is a database sheet replicating data from other sheets with Indirect functions but it still has under 200,000 cells used.

3. That's what I thought but the fact that the problem still happens after restoring the VBA makes me wonder... I've been trying to fix this problem for quite some time so going back to the last functionning version isn't really an option

4. That would be nice however I really need a full view of everything for financial and practical reasons, maybe the would be a nicer way of doing it with split workbooks but not with my moderate Excel skills...