How do I prevent Excel VBA error: An unhandled win32 exception occurred in EXCEL.EXE [14756]

The client I'm working for recently upgraded all laptops to Microsoft Surface running windows 10 (version 10.0.16299) and Office 2016: Microsoft Excel 2016 MSO (16.0.9126.2295) 64-bit Microsoft Office 365 ProPlus

I'm working on an Excel VBA application that was working fine under the old laptops, but has now started crashing Excel with the following error: An unhandled win32 exception occurred in EXCEL.EXE [14756]

There are different dialog messages that appear as Excel crashes: Microsoft Excel has stopped working Windows Error Reporting has stopped working

Another forum post recommended checking the Windows Event Viewer to obtain log of Error crash report. This gave me the following details:

Faulting application name: EXCEL.EXE, version: 16.0.9126.2295, time stamp: 0x5bafc794 Faulting module name: MSCOMCTL.OCX, version: 0.0.0.0, time stamp: 0x5984a51c Exception code: 0xc0000005 Fault offset: 0x0000000000021f8f Faulting process ID: 0x42c8 Faulting application start time: 0x01d47124a3a41f4c Faulting application path: C:\Program Files\Microsoft Office\Root\Office16\EXCEL.EXE Faulting module path: C:\windows\system32\MSCOMCTL.OCX Report ID: 74d9c093-61f0-4616-b20b-dc7f2acda9a5 Faulting package full name: Faulting package-relative application ID:

I've been searching numerous forums for solutions using the information I've gathered and so far come up empty.

I've isolated the problem to a specific user action, clicking on a ListView control. This control was added to the application to replace the former Listbox control on a form in order to allow the font colour to be set to red where an item had been selected. Since moving to the Surface laptops, this ListView control appears to be causing a conflict which is causing Excel to crash. I can’t be any more sure than that. Disabling the ListView control and setting the application to use the ListBox control appears to have resolved the issue.

I hope this is helpful to others. If anyone can explain why Excel is actually crashing, I'd appreciate it as I only feel I have a workaround, rather than a fix.

I'm wondering if there is a conflict with 64 bit v 32 bit drivers/dll files. The VBA references being used are: VBA References Mark

Thanks to @jkpieterse and @Comintern for advice.

The reasons for the problem were identified as migrating to Excel 2016 64 bit while using 32 bit controls and VBA code.

In the end there were a number of changes we had to make to prevent Excel crashing.

1) Removing all code and object references to the ListView object.

2) Following guidelines in this Microsoft article for ensuring VBA compatibility with Office 2016 64 bit: https://docs.microsoft.com/en-us/office/client-developer/shared/compatibility-between-the-32-bit-and-64-bit-versions-of-office

3) We ultimately contacted Microsoft support who recommended that to avoid the issue where Excel crashes, that we install a new registry key in:

HKEY_CURRENT_USER\Software\Microsoft\Office\16.0\Excel\Options\

Name: ForceVBALoadFromSource Type: DWORD
Value = 1

(See Knowledge base article: https://support.microsoft.com/en-gb/help/4011597/december-5-2017-update-for-excel-2013-kb4011597) They explained that this is a fix Microsoft have rolled out to force Excel to compile VBA fully on 64 bit systems which prevents problems when 32 bit code partially compiles.

Obviously you should backup your registry and create a restore point before applying this fix (and check with your Tech department if you have one!)

Prior to the support call I'd narrowed down the point of the crash to when a particular form was called. Prior to this point the VBA code was running fine, an ADODB connection had been successfully made to a SQL Server database with data being returned. When the form was called the application crashed. If I loaded a different form, the application did not crash implying there were other controls or code causing a compile error or conflict.

Also, I identified that the application did not crash if the Visual Studio Editor window was already open. I was able to add a line of code to programmatically open the VBE window (plus some to resize it so it did not hide the form):

Application.VBE.MainWindow.Visible = True
With Application.VBE.MainWindow
    .Width = 500
    .Height = 500
End With

After adding this code, the application did not crash either.

The problem form was too complex to recreate without a significant cost in development. fortunately the Microsoft fix has resolved the problem.