Bug in Excel 2007 using Ranges/Names?

[Cullen Morris]

First, I ask forgiveness for the lack of brevity. A little
background... I'm writing an Excel Add-In using Visual Studio 2008.
Under certain circumstances I'm encountering very poor performance
when accessing either the Workbook.Names collection, or specifically
the named Range object, or both.

Right now all my addin does is create two menu items, each with a
click handler implemented. The first menu item, when clicked, creates
a range for each of the first 300 cells in the first column. The
range is created, the value of the cell is set to a string ("blahx"
where "x" is an integer 0-299), and a Name is added to the
workbook.Names collection using the range just created.

The second menu item's click handler uses the ArrayList of strings
("blah0-299") used to create the original ranges to find each range in
the active worksheet, clear the range, and call Names.Item.Delete to
delete the name associated with that range. It then re-creates each
range and Name in the next column over. That's it.

So, in a new blank workbook, if you click the first menu item, then
the second menu item, you will see the first column fill up very
quickly, then get cleared very quickly followed by the second column
filling with strings. All very quickly.

However, if you click the first menu item to fill the first column,
then save the workbook to disk, then click the second menu item, you
will see the cells in the first column start to be cleared VERY SLOWLY
(maybe one or two cells per second). It takes at least 5 minutes to
complete clearing the first column and populating the second column.
Why does saving the workbook kill the performance of Excel? The
memory usage, as shown in the taskmanager, shoots way up at the same
time.

Below is all of the code for the addin. I encourage anyone with Excel
2007 and Visual Studio 2008 to create a blank Excel Add-in project and
paste this code in and try this experiment.

private Office.CommandBar MainMenuBar;
private Office.CommandBarControl _addinMenu;
private Office.CommandBarButton _addinMenuItem;
private Office.CommandBarButton _addinMenuItem2;
private ArrayList _NameList = new ArrayList();

private void ThisAddIn_Startup(object sender, System.EventArgs
e)
{
#region VSTO generated code

this.Application =
(Excel.Application)Microsoft.Office.Tools.Excel.ExcelLocale1033Proxy.Wrap(typeof(Excel.Application),
this.Application);

#endregion

Office.CommandBars bars =
(Office.CommandBars)this.Application.GetType().InvokeMember("CommandBars",
BindingFlags.GetProperty, null, this.Application, null);
this.MainMenuBar = bars["Worksheet Menu Bar"];
_addinMenu =
(Office.CommandBarControl)MainMenuBar.Controls.Add(Office.MsoControlType.msoControlPopup,
Type.Missing,
Type.Missing, Type.Missing, true);
_addinMenu.Caption = "HELP!";
_addinMenuItem = (Office.CommandBarButton)
((Office.CommandBarPopup)_addinMenu).Controls.

Add(Office.MsoControlType.msoControlButton,
Type.Missing, "Refresh", Type.Missing,
false);
_addinMenuItem.Caption = "First Add";
_addinMenuItem.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(_addinMenuItem_Click);


_addinMenuItem2 = (Office.CommandBarButton)
((Office.CommandBarPopup)_addinMenu).Controls.

Add(Office.MsoControlType.msoControlButton,
Type.Missing, "Refresh", Type.Missing,
false);
_addinMenuItem2.Caption = "Remove Then Add Again";
_addinMenuItem2.Click += new
Microsoft.Office.Core._CommandBarButtonEvents_ClickEventHandler(_addinMenuItem2_Click);

string str = "blah";
for (int i = 0; i < 300; i++)
{
_NameList.Add(str + i.ToString());
}
}

void
_addinMenuItem_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref
bool CancelDefault)
{
Excel.Worksheet asheet = null;
Excel.Workbook workbook =
(Excel.Workbook)this.Application.ActiveWorkbook;
asheet = (Excel.Worksheet)workbook.ActiveSheet;

int currRow = 1, currColumn = 1;
foreach (string name in _NameList)
{
Excel.Range rng = (Excel.Range)asheet.Cells[currRow,
currColumn];
rng.set_Value(Type.Missing, name);
try
{
workbook.Names.Add(name, rng, true, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
catch (System.Exception ex)
{
MessageBox.Show("Name.Add throws...");
}
currRow += 1;
}
}

void
_addinMenuItem2_Click(Microsoft.Office.Core.CommandBarButton Ctrl, ref
bool CancelDefault)
{
Excel.Worksheet asheet = null;
Excel.Workbook workbook =
(Excel.Workbook)this.Application.ActiveWorkbook;
asheet = (Excel.Worksheet)workbook.ActiveSheet;

foreach (string name in _NameList)
{
Excel.Range rng = asheet.get_Range(name,
Type.Missing);
rng.Clear();
workbook.Names.Item(name, Type.Missing,
Type.Missing).Delete();
}

int currRow = 1, currColumn = 2;
foreach (string name in _NameList)
{
Excel.Range rng1 = (Excel.Range)asheet.Cells[currRow,
currColumn];
rng1.set_Value(Type.Missing, name);
try
{
workbook.Names.Add(name, rng1, true, Type.Missing,
Type.Missing, Type.Missing,
Type.Missing, Type.Missing, Type.Missing,
Type.Missing, Type.Missing);
}
catch (System.Exception ex)
{
MessageBox.Show("Name.Add throws...");
}
currRow += 1;
}
}

 

[Cullen Morris]

I forgot to mention that this behavior - slowed performance apparently
due to saving the workbook - doesn't happen in Excel 2003.

 

[Cullen Morris]

I forgot to mention that this behavior - slowed performance apparently
due to saving the workbook - doesn't happen in Excel 2003.