Case of Excel Conditional Formatting Hang

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

image

 

The progress bar opening document stuck for over 60 seconds at the same position…

image

From Process Explorer we notice for the duration of hang, Excel is consuming high CPU.

image

 

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.

image

 

image

Running under Visual Studio Profiler…

image

From the per-module view, we can see almost all time is spent in one function in MSO.dll

image

Using Rohitab Monitor we add external DLL MSO.dll and monitor Excel launching document

image

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.

image

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:

image

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/ )

image

 

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:

image

However there were two rows in the worksheet that had 250 conditional formatting rules applied to an individual cell – 500 total.

image

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.

About chentiangemalc

specializes in end-user computing technologies. disclaimer 1) use at your own risk. test any solution in your environment. if you do not understand the impact/consequences of what you're doing please stop, and ask advice from somebody who does. 2) views are my own at the time of posting and do not necessarily represent my current view or the view of my employer and family members/relatives. 3) over the years Microsoft/Citrix/VMWare have given me a few free shirts, pens, paper notebooks/etc. despite these gifts i will try to remain unbiased.
This entry was posted in API Monitor, Debugging, Office and tagged . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s