Excel 2016 for Mac often crashes reading ranges containing #N/A or #DIV/0!

[DentonGentry]

OS (e.g. Windows 10 or macOS Sierra)

MacOS 10.14.2

Versions of xlwings, Excel and Python (e.g. 0.11.8, Office 365, Python 3.7)

Excel 2016 for Mac 16.16.6

Describe your issue (incl. Traceback!)

Excel frequently crashes when reading a range with errored cells containing #N/A or #DIV/0! For example, executing the Python code:

    sheet = workbook.sheets['Sheetname']
    sheet.range('B26:L73').raw_value

causes Excel to crash if cell C26 contains #DIV/0. Not a Python traceback, Excel itself crashes.

Reading different ranges doesn't always trigger the crash, for example with the same spreadsheet reading B26:C73 and then reading D26:L73 both succeed without crashing Excel.

The Excel crash information points to freeing memory in code involving AppleEvents, possibly heap corruption or other memory-related bugs. This is clearly Excel's problem not xlwings, I'm mostly reporting it here for future reference for others who run into the same issue.

# 1 0x00007fff30c0f7b5 in _implArrayRelease(__CFAllocator const*, void const*) + 0x00000004 (AE + 0x00000000000147b5) 0x0000000000000000 # 2 0x00007fff2f9a4088 in _CFArraySetValueAtIndex + 0x00000170 (CoreFoundation + 0x0000000000067088) 0x0000000000000000 # 3 0x00007fff30c0f5d7 in AEListImpl::putElement(long, unsigned int, AEImpl const*) + 0x0000005F (AE + 0x00000000000145d7) 0x00007ffee97a0880 # 4 0x00007fff30c13693 in _AEPutDesc + 0x000000AF (AE + 0x0000000000018693) 0x00007ffee97a08b0

Include a minimal code sample to reproduce the issue (and attach a sample workbook if required!)

The attached Excel file contains a #DIV/0! error dividing 1/0 in cell B1.

Executing the following will cause Excel to crash:

    import xlwings
    workbook = xlwings.Book('/Users/dgentry/Documents/divzero.xlsx')
    sheet = workbook.sheets['Sheet1']
    sheet.range('A1:C10').raw_value

Note that reading A1:B10 does not crash Excel, reading A1:C10 does.

divzero.xlsx

[DentonGentry]

I'm working around the problem for my case by adding VBA code in the Excel file which I can call from xlwings before trying to read a range:

Sub clearErrorCells(sheetname As String, range As String)
    On Error Resume Next
    Sheets(sheetname).range(range).SpecialCells(xlCellTypeFormulas, 16).ClearContents
    On Error GoTo 0
End Sub

(and rename the file to extension xlsm to allow macros to run)

The Python code then adds a call to invoke the VBA code:

    clearErrorsMacro = workbook.macro('clearErrorCells')
    clearErrorsMacro('Sheet1', 'A1:C10')
    sheet.range('A1:C10').raw_value

and Excel does not crash, presumably because there is no longer a DIV/0 error present.

For my purposes, I would turn all of those errors into N/A data anyway, so having the Excel VBA code pre-process them out of existence is acceptable.

[fzumstein]

i didn't look into your sample yet, but is there a reason why you use raw_value instead of value?

[DentonGentry]

Cargo cult programming, mostly.

[fzumstein]

thanks for reporting. It seems indeed to be an issue between AppleScript (what xlwings uses under the hood) and (probably) the Mojave release. I get the same crash when executing the following directly in the ScriptEditor:

tell application "Microsoft Excel"
	get value of range "A1:C10"
end tell

gives me

error "Microsoft Excel got an error: Connection is invalid." number -609

However, reading in just "B1" will give the correct answer (missing value), while reading in B1:B10 will just ignore that value: {{2.0}, {3.0}, {4.0}, {5.0}, {6.0}, {7.0}, {8.0}, {9.0}, {10.0}}

I'll see if I can find out more.

[fzumstein]

I just got confirmation from Microsoft that this is a bug - will let you know once I know more.

[fzumstein]

This seems to be fixed with recent versions of Excel.

[fzumstein]

Actually, this still seems to be an issue but depends on where the error cell appears. For me, it currently only breaks with a cell error anywhere between A1 and A4.