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?
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I can't say for certain I quite understand this last round of advice. I removed the worksheetfunction. Put in some guesswork for error reporting (no errors) and added some colour to help ensure that the sort range was indeed the area I wanted sorted within the worksheet. It is. No resolution.

VBA 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
        
        rng_svctop = Application.Match("ADD", .Columns(1)) + 1
        If IsError(rng_svctop) = True Then MsgBox CLng(Split(CStr(rng_svctop), " ")(1))
            
        rng_svcbot = Application.Match("Facility Maintenance Activities", .Columns(1)) - 3
        If IsError(rng_svcbot) = True Then MsgBox CLng(Split(CStr(rng_svcbot), " ")(1))
        
        Set rng_pdaservices = .Range("A" & rng_svctop & ":R" & rng_svcbot)
        With rng_pdaservices
            .Cells.Interior.ColorIndex = 36
            ws_master.Unprotect
            .Sort key1:=.Range("R" & rng_svctop), order1:=xlAscending, Header:=xlNo
        End With
    End With
    'MsgBox "PDA Sservices range sorted."
End Sub
 

Excel Facts

Ambidextrous Undo
Undo last command with Ctrl+Z or Alt+Backspace. If you use the Undo icon in the QAT, open the drop-down arrow to undo up to 100 steps.

Marc L

Well-known Member
Joined
Apr 5, 2021
Messages
1,723
Office Version
  1. 2010
Platform
  1. Windows
Is your Excel a 365 Beta version ?​
Another point : for an exact match you need to add this red part :​
Rich (BB code):
Application.Match("ADD", .Columns(1), 0)
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hey Marc,
No ... not Beta ... not that I think anyway. Paying pretty good money for 365 sub, so hoping I'm getting tried, tested and true.
Thanks for the added advise about the extra parameter. Sadly this made no difference.
Whatever the issue, it must be very unique.

Perhaps there's another way to do what I want to do? Sort a dynamic range within my worksheet.
 
Last edited:

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
I even tried unloading the form before doing the sort. Prior to sort, could save and no crash. Close userform, perform sort, save and crash.
 

Marc L

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

ADVERTISEMENT


If you could link your workbook with a files host website like Dropbox for example​
in order different helpers can check it whatever their Excel version …​
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
If you could link your workbook with a files host website like Dropbox for example​
in order different helpers can check it whatever their Excel version …​
Hi Marc, I do appreciate your continue interest in cracking this case. I would jump at the opportunity to share my project, but it's not one easily shared. It relies on several network accessed support files which would not only fire off errors in attempts to execute, but eliminating their need would not allow the application to function. It would take far too much effort to modify to adjust to work without the external files and to censor of any sensitive personal data.

I'm guessing since more haven't chimed in that there isn't any obvious reasons why this wouldn't work. I might share this with another forum to see if I can tap into their skilled population even to see if there is another approach to this simple sort concept.
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows

ADVERTISEMENT

I will add, the behaviour was consistent on another computer, the only difference being Windows did actually try to go the process of trying to identify the cause before restarting, unlike originally where it just restarted Excel. The process of Windows trying to identify the problem went on forever, and pressing cancel to bypass that sent Windows into an unending process of restarting Excel. I had to CTRL-ALT-DEL to close the unresponsive Excel program.
 

rlv01

Well-known Member
Joined
May 16, 2017
Messages
1,139
Office Version
  1. 365
  2. 2010
Platform
  1. Windows
Assuming runtime error trappingit is something you want to avoid.
Hi Marc, I do appreciate your continue interest in cracking this case. I would jump at the opportunity to share my project, but it's not one easily shared. It relies on several network accessed support files which would not only fire off errors in attempts to execute, but eliminating their need would not allow the application to function. It would take far too much effort to modify to adjust to work without the external files and to censor of any sensitive personal data.

I'm guessing since more haven't chimed in that there isn't any obvious reasons why this wouldn't work. I might share this with another forum to see if I can tap into their skilled population even to see if there is another approach to this simple sort concept.
You should be able to pare down your workbook by deleting userforms, code modules and functions to a much simpler version that still exhibits the save problem and post that. Remember that the goal here is to preserve the problem so that it can be demonstrated to others, not to have the workbook 'do' anything. And if, in the course of sytematically deleting things, you discover that the problem suddenly goes away, then that would be a HUGE clue. :)
 

Ark68

Well-known Member
Joined
Mar 23, 2004
Messages
3,976
Office Version
  1. 365
  2. 2016
Platform
  1. Windows
Hi rlv ... this is a pretty big project. I'm not sure how I could do this. I'm afraid that the problem may be in the creation of the worksheet and the data put on it that is contributing to the problem. Data the user enters into the open userform is transfered to the worksheet and the sort is based on that data put into the prepared worksheet. So either there is something wrong with the worksheet causing the problem, or some negative reaction associated to the form being open. If I stop the program execution at the point where the data is transfered over, the userform closes, I can perform the sort of that same data without having the save crash.

Perhaps I'm not fully understanding how to adapt my application to your suggestion becuase it is a pretty complex project. I do appreciate your input though, thank you.
 

RoryA

MrExcel MVP, Moderator
Joined
May 2, 2008
Messages
37,746
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2010
Platform
  1. Windows
  2. MacOS
Does it make any difference if you use the worksheet.sort object rather than the range.sort method, or at least clear any existing sortfields set on the sheet sort before you do your range sort?

Also, are any of the controls on your userform linked directly to ranges on worksheets using rowsource or controlsource?
 

Forum statistics

Threads
1,147,482
Messages
5,741,409
Members
423,657
Latest member
Medrok2021

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