Annoying Problem With VBA Editor Crashing Excel

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I am working on an Excel VBA project and have recently started encountering a very annoying problem.
When I am testing my project, if I run into an error, or I stop to step through and make changes based on the results encountered while stepping through, and attempt to save these changes or corrections, Excel crashes. No error messages, it just folds up and restarts the project as if nothing happened. It's the original file, not a backed up one that is started, and the changes made are not reflected. (Assuming the save was unsuccessful).

If I don't execute my code in my project, and just go straight to making known changes or corrections and save, I'm fine. It's only after my code has been executed.

I know it's a crap shoot, but is there any way I can determine what is causing this crash?
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,263
Office Version
  1. 2010
Platform
  1. Windows
Hoping also you are not under 365 Excel version …​
 

Excel Facts

Copy PDF to Excel
Select data in PDF. Paste to Microsoft Word. Copy from Word and paste to Excel.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,263
Office Version
  1. 2010
Platform
  1. Windows
So if you can try the same workbook on another Excel version but classic (2010 to 2019)​
and also on another computer with Excel 365 version, hoping you are not in the Beta loop …​
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey thanks Marc for your support. I don't have access to any of the legacy versions of Excel unfortunately, and I'll have to wait until tomorrow to try on another 365 computer. The project works flawlessly, I just havce to remember to make and save any code changes before I use the form. Where I fear problems is whether the code to save my workbook at different points will crash the project. That would not be cool. I haven't got to that point in my testing yet. I was hoping there was some sort of crash log or something ... not that I would be able to work with that info.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,263
Office Version
  1. 2010
Platform
  1. Windows

ADVERTISEMENT

Excel whatever the version does not like big forms with many objects …​
Sometimes just exporting the UserForm, deleting it, saving the workbook, importing back the UserForm and saving the workbook may solve the issue.​
Wait also for ideas from others …​
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
So I have been working through my code, placing breaks frequently through it to try and determine at what point I lose the ability to save.

I made it to this procedure...

Rich (BB code):
Sub iupdate_srvcpda()
    Application.ScreenUpdating = False

    With ws_master
        mbevents = False
        .Unprotect
        srvcs_no = Application.WorksheetFunction.CountA(ws_thold.Range("AK1:AK8"))
        scol = 13 '14, 15, 16
        srccol = 1 '2-8
        srvc_drow = Application.WorksheetFunction.Match("ADD", .Columns(1)) + 1
Stop 'try save      SAVE OK
        For L1 = 1 To srvcs_no
            If srvc_drow > 32 Then 'add row
                srvc_drow = srvc_drow + 1
                MsgBox "Not enough room. Row added at " & svc_lrow + 1, , "UNTESTED"
                Stop
                .Range("A" & srvc_drow & ":R" & srvc_drow).Insert Shift:=xlDown
            End If
            With .Range("H" & srvc_drow & ":Q" & srvc_drow)
                .Cells.Value = ""
                .Cells.Interior.Color = RGB(166, 166, 166)
                .Cells.locked = True
            End With
            If L1 = 5 Then
                scol = scol - 4
            End If
            Set rng_cpy = ws_master.Range("A" & srow & ":G" & srow)
            rng_cpy.Copy ws_master.Range("A" & srvc_drow)
            With .Cells(srvc_drow, scol)
                .Value = ws_thold.Cells(srccol, 39)
                .Interior.ColorIndex = 0
            End With
            If ws_thold.Cells(srccol, 36) = "RLN" Then
                d1 = "Reline"
            Else
                d1 = "Change"
            End If
            dmsg = d1 & " " & ws_thold.Cells(srccol, 37) & "-" & ws_thold.Cells(srccol, 38)
            With .Cells(srvc_drow, 2)
                .Font.Size = 6
                .Font.Color = vbBlack
                .Font.Bold = True
                .Value = dmsg
                .HorizontalAlignment = xlCenter
            End With
            With .Cells(srvc_drow, 18)
                .Value = ws_thold.Cells(srccol, 43)
                .Font.Size = 6
                .Font.Color = RGB(229, 242, 251)
            End With
Stop 'try save      SAVE OK
            .Rows(srvc_drow).AutoFit
            .Rows(srvc_drow).Cells.locked = True
            .Range(.Cells(srvc_drow, 1), .Cells(srvc_drow, 17)).VerticalAlignment = xlCenter
            scol = scol + 1
            srccol = srccol + 1
            srvc_drow = srvc_drow + 1
        Next L1
Stop 'try save      SAVE OK
        'MsgBox "Sort PDA service here > times in column r"
        sort_pda
        .Protect
        mbevents = True
    End With
    Application.ScreenUpdating = True
Stop 'try save      SAVE FAILS
End Sub

I suspect that something is going amuck in the procedure 'sort_pda'.

Code:
Sub sort_pda()
        With ws_master
             rng_svctop = Application.WorksheetFunction.Match("ADD", .Columns(1)) + 1
             rng_svcbot = Application.WorksheetFunction.Match("Facility Maintenance Activities", .Columns(1)) - 3
             Set rng_pdaservices = .Range("A" & rng_svctop & ":R" & rng_svcbot)
             With rng_pdaservices
                 ws_master.Unprotect
                 .Sort key1:=.Range("R" & rng_svctop), order1:=xlAscending, Header:=xlNo
             End With
         End With
    'MsgBox "PDA Sservices range sorted."
End Sub

Is anyone able to spot anything unusual with this code that looks suspicious?

There are numeric values in colunm R for the the range defined by ("R" & rng_svctop & "R" rng_svcbot). See the purple code in the procedure 'iupdate_srvcpda' above.
The only thing I might question is whether or not I need to define the start and end of the sort range in the underlined code?
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
991
Office Version
  1. 365
  2. 2010
Platform
  1. Windows

ADVERTISEMENT

FWIW, I recently had a report of one of my O365 workbooks crashing on code execution. The user sent me the file and sure enough it did crash hard. It turned out to be a corrupted user form. I only found it because after trying everything else to track down the problem and hitting a brick wall each time, I started replacing the forms one by one from a 'clean' copy of the same workbook.
 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,263
Office Version
  1. 2010
Platform
  1. Windows
Somewhat not the issue here but as a trick :​
⚠️ Never use WorksheetFunction except if you want to crash your VBA procedure when an Excel function returns an error !​
✅ Just keep Application like for example Application.Match …​
Use a Variant variable to store the worksheet function result and check it via VBA functions IsError or IsNumeric …​
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
991
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Somewhat not the issue here but as a trick :​
⚠️ Never use WorksheetFunction except if you want to crash your VBA procedure when an Excel function returns an error !​
✅ Just keep Application like for example Application.Match …​
Use a Variant variable to store the worksheet function result and check it via VBA functions IsError or IsNumeric …​

There is no reason to avoid WorksheetFunction . The application object and the worksheetfunction function assert errors differently and require a different error handling approaches , but you can add error handling to WorksheetFunction so that it is just as robust. Chip Pearson has a good write up on this:

 

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,263
Office Version
  1. 2010
Platform
  1. Windows

As Application alone so without WorksheetFunction avoids any error trapping via On Error statement …​
 

Forum statistics

Threads
1,141,707
Messages
5,707,988
Members
421,539
Latest member
zuniBM

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
Top