Annoying Problem With VBA Editor Crashing Excel

[Ark68]

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]

​

Hoping also you are not under 365 Excel version …​

 

[Ark68]

Yes 365 ...

 

[Marc L]

​

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]

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]

​

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]

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...

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'.

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]

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]

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]

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:

Calling Worksheet Functions In VBA

www.cpearson.com

 

[Marc L]

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