Excel 2007 and 2010 Crash with NamedRanges

Question

Copying a range (described below from one workbook to another will crash excel 2007 and excel 2010 every time.

Target Workbook

Must have at least 1 named range.  Create any blank workbook and add a single named range

 Source Workbook

Must have content in 2 cells.   In my example, the content looks like

=F18&" "&CustomFunction("Description", "Account="&F18, "Account")

=F19&" "&CustomFunction("Description", "Account="&F19, "Account")

This is stand-alone excel.  The CustomFunction resolves to #NAME? at this point since I don't have any of my addins loaded. 

 

Key Information

Open the Xml file with something that can read the Xml (I use 7xip).  The OpenXml view of worksheet is as follows:

<worksheet xmlns="http://schemas.openxmlformats.org/spreadsheetml/2006/main" xmlns:r="http://schemas.openxmlformats.org/officeDocument/2006/relationships">
	<dimension ref="C18:C19"/>
	<sheetViews>
		<sheetView tabSelected="1" workbookViewId="0">
			<selection activeCell="D18" sqref="D18"/>
		</sheetView>
	</sheetViews>
	<sheetFormatPr defaultRowHeight="15"/>
	<sheetData>
		<row r="18" spans="3:3">
			<c r="C18" t="e">
				<f t="shared" ref="C18:C19" ca="1" si="0">F18&amp;" "&amp;_xll.CustomFunction("Description", "Account="&amp;F18, "Account")</f>
				<v>#NAME?</v>
			</c>
		</row>
		<row r="19" spans="3:3">
			<c r="C19" t="e">
				<f t="shared" ca="1" si="0"/>
				<v>#NAME?</v>
			</c>
		</row>
	</sheetData>
	<pageMargins left="0.7" right="0.7" top="0.75" bottom="0.75" header="0.3" footer="0.3"/>
</worksheet>

 

To reiterate, there are several very important things that must be present and then this will crash every time.

1. Target workbook must have at least 1 named range

2. Source workbook must have 2 cells using the "shared" attribute

 

3. The thing being shared needs to contain a formula with a prefix.  If I edit the xml and remove the "_xll." it will work.  (still #NAME? but not crash).Additionally, if the prefix (_xll.) that is listed above is valid, as it is when my addin is loaded, it crashes.  That indicates it isn't about resolving that.  Valid or not, the mere presence is one of the key factors to this crash

I don't have control over any of these 3 things which is making this crash very difficult for my customer base.  It occurs infrequently, but when it does, it is very serious.  I can reproduce with Excel 2007 and Excel 2010 with all the latest patches.

Answer 1

Hi jds,

Thanks for your post.

I just wonder since you are trying to copy named range from one workbook to another by working with XML, why don't you use Open XML SDK? It is much safer than directly working with XML code and it's absolutely free.

You can download the SDK from here:

http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5124

And you can get started with it from here:

http://msdn.microsoft.com/en-us/library/bb456488.aspx

I hope this helps.

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Thank you for the response.  I'm afraid I wasn't very clear in my description of the use case and just gave too much of my diagnostics.  I only show the XML for analysis.  I don't actually work with it.  This can be replicated by hand using straight excel actions.I would attach the 2 sample files to make reproducing trivial, but there isn't an attach file that I've seen on these forums.

The use case would be

1. User opens File 1 (contains 2 formulas)
2. User opens File 2 (contains 1 NamedRange)  It can point anywhere and isn't really relevant.  For some reason there has to be at least 1 to crash, but it doesn't need to point to or reference anything else that is being used.
3. User manually selects the range on file 1 and presses copy
4. User switches to File 2 and presses paste
5. CRASH - Excel is done.

In my analysis, I have simplified these workbooks to their most basic form.  That most basic form will have as its OpenXml, the snippet I show above.  I didn't generate it, Excel did. 

If there is a place to upload or send files, I'll give you the 2 tiny xlsx files that will show this problem. 

I hope that helps.  Thanks again.

Answer 3

Hi,

I can't repro the issue as your description. You can upload these two workbooks into your skydrive, which requires you have a MSN account, and provide a link here to download them.

BTW, what is the CustomFunction in your formula? Is it a User defined function or a VBA function which is defined in a module?

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 4

I just uploaded the files.  Crash.zip has the source and target

https://skydrive.live.com/?cid=1D7D1063F5D42EEC&id=1D7D1063F5D42EEC%21124

1. Open Both
2. Select a single range including both formulas on the Source.xlsx
3. Copy
4. Switch to target
5. Paste

 

CustomFunction is made up.  Originally it was an XLL function that we had called GetData.   When the XLL was loaded, making it valid, it crashed.  I then unloaded our XLL and it still crashes.  I then changed the name of the function to something that is completely invalid and it still crashes.  It doesn't seem to matter what it is.  It does need the prefix to crash though.  FWIW.   The prefix is automatically being added. 

Originally, this is an XLL function using ExcelDna

 

Thanks again

 

Answer 5

Yeah, I can see the issue now when I copy the two cells which contains formulas into any other workbook, no matter if the target contains a named range, not matter the version of Excel.

But the issue doesn't occur when you copy the two cells one by one into an another workbook (Workbook NewBook)...

After the cells copied into NewBook (now the NewBook has the same value and formula with Source Book), I copy the two cells into another book, and this time the issue doesn't happen. It seems that the issue caused by your particular source workbook. Would you like to tell me how you generate the source workbook so that I can repro the issue.

I look forward to hearing of you.

---

Best Regards, Calvin Gao [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 6

Thanks Calvin,

Here is how to manually create that report. 

1. Create a new report
2. In D5 type =NoFunctionHere(D4,D3)
3. Select D5:H5 and fill right
4. Save it
5. Clear cell D5, G5,H5 (this is just to simplify it)
6. Save it
7. Close it
8. View it in 7-zip (C:\Temp\Crash\Book5.xlsx\xl\worksheets\sheet1.xml

You will see it looks just like the OpenXml in my sample, with 1 critical difference.   The _xll.  prefix is not on the function.  That makes sense as this isn't even a real function.  At this point this file will work fine.  The _xll. prefix (my understanding) is only put on when the function originates from an xll.  Once there, excel will leave it, regardless of if the xll is even loaded.  I have a couple xlls that both produce that prefix.  If you have an xll, you can use an xll function instead of this one and should be able to get in the same situation.  If you don't, just edit it in 7-zip by putting a _xll. in front of the function name and saving it.  You now have a workbook that will crash excel.  (if you haven't used 7-zip, it lets you select edit on the sheet and can update the xml and save it back and still have a working excel file)

A few things I have noted while trying to create this simple file manually.   The goal is to get to the point where you have "shared" for the 2 cells.   I found that if the function has a parameter which takes an array, excel doesn't seem to want to store it as shared.  I'm not sure of the nuances there.  If I could prevent Excel from "optimizing" the openxml with the shared context, the crash would go away.  That is effectively what happens when you select them separately, or modify one. 

-Javan

 

 

 

Answer 7

Any chance you've been able to look at this?  Do I need to go through MS Phone support? 

 

thanks

Answer 8

this is still an issue with latest patches.  Anyone else seen this?