4

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

0m3r
  • 11,189
  • 14
  • 28
  • 60
Frozbie
  • 51
  • 1
  • 7
  • 1
    I expect you used a 32 bit version of the listview control and you state that the users have 64 bit Office. That is the root cause I expect. – jkpieterse Nov 02 '18 at 12:38
  • I seem to be missing the question here. Is it "why does 64 bit Excel crash when you use a deprecated 32 bit control from 20 years ago"? If that's the case, the answer is in your error report - 0xc0000005 is an access violation. – Comintern Nov 02 '18 at 13:31
  • @jkpieterse Thank you for that, I'll look into whether there is a 64bit version available. – Frozbie Nov 03 '18 at 11:29
  • @Comintern You appear to have more knowledge of error codes than I do. I see possibly six error codes in the windows log that I posted and freely admit I don't which of them are relevant or helpful in terms of identifying the problem and solution. I hadn't realised that MSCOMCTL.OCX is a file referred to in the VBA references which helps confirm which driver/reference file is the issue. – Frozbie Nov 03 '18 at 11:33

2 Answers2

1

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.

Frozbie
  • 51
  • 1
  • 7
0

I recently had a similar issue of crashing when a sub-routine is called from an add-in. The code worked fine when manually debugged. We are using Excel 2016, 32 bit version. I figured out the issue was related to an uncompiled code in the add-in. After recompiling all the code using Debug -> Compile VBAProj from VBA editor, the error disappeared.

Sarav
  • 1