Previously we saw network traffic causing a hang https://chentiangemalc.wordpress.com/2014/08/21/case-of-the-excel-hang-on-worksheet-open/
This time no network traffic was occurring when launching a customer’s payroll Excel Application (XLS file). The application hang occurred in Excel 2010 and 2013, but not in Excel 2003. As before Office File Validation had been confirmed off, and issue occurred with no add-ons…
During the hang Process Monitor (http://live.sysinternals.com/ProcMon.exe ) detected no registry/file/network activity from Excel.exe
The progress bar opening document stuck for over 60 seconds at the same position…
From Process Explorer we notice for the duration of hang, Excel is consuming high CPU.
Using XPerf + Windows Performance Analyzer with the –stackWalk option we can see this see on a graph. We can also on see in the “Images – Transient Lifetime By Process, Image” that several modules loaded at the same time the high CPU started. In some cases we might get a clue just from this, but not today.
Running under Visual Studio Profiler…
From the per-module view, we can see almost all time is spent in one function in MSO.dll
Using Rohitab Monitor we add external DLL MSO.dll and monitor Excel launching document
Almost all the time is spent in function @8879 which is called non-stop during the hang, followed up by _MsoFreePv@4. This lined up with what I had captured in WinDbg when attaching to Excel. With Microsoft’s internal symbols we can probably easily figure out what is causing this hang from this information, as we can get the actual API name at least.
We could spend some time reversing the function and trying to figure out how it’s being used but instead went back to a more basic troubleshooting method:
1) Saved document as XLSM – issue doesn’t occur, document opens instantly
2) Saved XLSM to XLS again – issue is back
3) Saved XLSM to XLSX (No Macros – there is an open document macro) – issue still occurs
4) Deleted 1st workheet – issue doesn’t occur
OK so we have found the culprit is the 1st worksheet. In fact when we are saving the document in XLS format we are given a clue to this problem.
We see two warnings about conditional formatting:
So in the worksheet I clear all conditional formatting (This required unprotecting the sheet, which required a password, which happened to be the department’s name, and was throughout the macros as .Unprotect <password>) Keep in mind password protected macros are not protected! ( https://chentiangemalc.wordpress.com/2012/01/17/powershell-script-to-remove-office-macro-protection/ )
Sure enough this fixed the issue, document loaded instantly. But what conditional formatting actually caused the issue…
Looking at the rows most rows had a set of rules like this:
However there were two rows in the worksheet that had 250 conditional formatting rules applied to an individual cell – 500 total.
Recreating these two rows, fixed the performance issue.
This issue didn’t occur in Excel 2003 due to the issue being cause by Excel 2007 and later functionality that would be ignored in Excel 2003. The XLS vs XLSM are significantly different file formats – XLS being a binary OLE Object Document, XLSX/XLSM being essentially a ZIP file of XML files, so the processing of these files will be different.