VBA code crashes Excel for only one out of 3 Computers Using it

[CRIMEDOG]

This is the offending VBA:

ActiveSheet.Sort.SortFields.Clear
    ActiveSheet.Sort.SortFields.Add Key:=Range("B7:B" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    ActiveSheet.Sort.SortFields.Add Key:=Range("C7:C" & LastRow), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Equipment").Sort
        .SetRange Range("A6:AM" & LastRow)
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

the Sortfields.Clear works - then excel crashes and restarts
It works on 2 Computers but not a 3rd
All are Windows 10 v17.09 Excel 2016 64 Bits

Does anyone know of known issues? We are going to re-install MS Office and run all available updates.

[Zer0Cool]

I vaguely recall 2016 VBA introducing an issue with filters/sorts where clearing used to work but now you essentially need to show all then clear or it throws errors. I am very foggy on the details and it may not apply in this case. Ill see if I can dig up details.

EDIT: Also, if you could post any error messages or any meaningful entries from event viewer related to the crash it would help.

EDIT2: Does the computer crashing have any addins installed (active or not) that the others do not, if so try disabling it (or more extremely uninstalling the addin to see if it resolves the issue)

[Zer0Cool]

Ok so it turns out the 2016 issue I was thinking of isnt likely your issue. It had to do with deleting custom sort lists before clearing the sort (which used to work but now makes Excel crash when you try and save the file later)

[CRIMEDOG]

No Add ins
Error message: "Microsoft excel has stopped working, a problem caused Microsoft to stop working..."

[Zer0Cool]

Please check event viewer in Windows and see if you can find a log of the crash, it should provide more details about why it is crashing.

You also say they are both XL2016, but are they the exact same version (File | Account | About Excel)? Are some Office 365 subscriptions and others not?

Have you tried making a simplified file (starting from a new file) with a little bit of data and the same routine you posted to see if the code works in other files? If it does then its something specific to that file causing an issue on the machines environment, otherwise its unlikely to be the contents of the file (the data, layout, objects, etc) but instead the code and/or environment.

[CRIMEDOG]

Log.txt:

Faulting application name: EXCEL.EXE, version: 16.0.8431.2270, time stamp: 0x5b1a032a
Faulting module name: VBE7.DLL, version: 0.0.0.0, time stamp: 0x59bae6d1
Exception code: 0xc0000005
Fault offset: 0x000000000014c8a1
Faulting process id: 0x1604
Faulting application start time: 0x01d419e811e7cd7f
Faulting application path: C:\Program Files\Microsoft Office\root\Office16\EXCEL.EXE
Faulting module path: C:\Program Files\Common Files\Microsoft Shared\VBA\VBA7.1\VBE7.DLL
Report Id: 1e74b6d6-5e2c-405d-a60d-76ed90e43ddd
Faulting package full name:
Faulting package-relative application ID:

[CRIMEDOG]

Update:

Range("A7:AV" & LastRow).Select
    Selection.Sort Key1:=Range("J7"), Order1:=xlAscending, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom

    Selection.Sort Key1:=Range("B7"), Order1:=xlAscending, Key2:=Range("Z7"), Order2:=xlAscending, Key3:=Range("A7"), Order3:=xlAscending, OrderCustom:=1, MatchCase:= _
        False, Orientation:=xlTopToBottom

The computer was always crashing at the sort - so I found another sort method. Now it does not crash. The PROBLEM:
1. The sort can only use 3 Keys - so I sort it, then re-sort
2. The computer that was crashing won't sort through VBA


Is there some setting that keeps it from doing?

Still unresolved

[Zer0Cool]

On the computer that crashes under the sort, does it work manually sorting it to the exact same parameters? If not the sort itself is an issue on that machine, not the code. If it works we know the sort should work but the implementation in VBA is causing some problem.

Also see post #3 now, as your new code snippet uses a custom sort. You need to delete the custom sort prior to clearing the sort or it will cause a crash. May not be your problem, but now there is a chance it is with the introduction of using a custom sort.

The log, while good of you to post, doesnt really tell us much other than its VBA/VBE crashing, which we already know is the origin. That doesnt however mean its the cause unless we test and ensure this works manually with the same exact parameters. That means ensuring LastRow is what you expect it to be and any other variables, custom sort, range, etc.

Also, check the full version of Excel on each machine, not just 2016 but File | Account | About Excel -> 16.0.4639.1000 x64 for example. If the one having the issue is lower/higher it could be a problem caused by an update (higher) or lack of a patch (lower).

Compare the settings under options on a machine working and the one not working. Things like DDE, graphics acceleration, # of CPU's to use, etc. Try changing the printer to a default like the XPS or a PDF printer (changes how Excel renders the workbook on screen). Double check and ensure that no addin is enabled, neither COM nor Excel addins (there are built in ones that may be loaded but not visible). If any addin is enable toggle it off, restart Excel and retest, as addins, even when loaded but not visible can interfere with normal operations.

If I was you, I would start with checking if it manually works to do the sort. If not I'd ignore VBA for now and start checking things like the settings above, seeing if a newly created dummy file can do the same type of sort, checking version, etc maybe even a repair of Office on that machine.

If it does work manually and not via VBA, its likely a combination of something on that machine (printer driver, setting, version, 3rd party software) that conflicts with the specific code used. In this case try being as precise/explicit in the language used in your code as you can be (IE: Workbook.Worksheet.Range.Sort vs Range vs Selection, vs Active...). If not using it, set Option Explicit at the top of your module and declare all variables. Add debug.print to your code for key variables and step through the code ensuring all variables, object properties of significance, etc are as you would expect. Ensure that if you use custom sort you also delete the custom sort prior to clearing the sort (and do clear it when done). Beyond that, try the code with a sample data set in a new file and see if the problem persists (determine if the file is an issue, or that machine or the combination of both).

Hope this helps

[CRIMEDOG]

Turns out the user had two instances of Excel - and the default program was the one crashing... got it fixed