Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2007 using Ranges/Names?
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.Ex celLocale1033Proxy.Wrap(typeof(Excel.Application), this.Application); #endregion Office.CommandBars bars = (Office.CommandBars)this.Application.GetType().Inv okeMember("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_Clic kEventHandler(_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_Clic kEventHandler(_addinMenuItem2_Click); string str = "blah"; for (int i = 0; i < 300; i++) { _NameList.Add(str + i.ToString()); } } void _addinMenuItem_Click(Microsoft.Office.Core.Command BarButton 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.Comman dBarButton 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; } } |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2007 using Ranges/Names?
I forgot to mention that this behavior - slowed performance apparently
due to saving the workbook - doesn't happen in Excel 2003. |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Bug in Excel 2007 using Ranges/Names?
I forgot to mention that this behavior - slowed performance apparently
due to saving the workbook - doesn't happen in Excel 2003. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Excel 2007 Named Ranges | Excel Discussion (Misc queries) | |||
Is there a way to give range names (especially with relative reference) for series names and series values in Excel 2007 graphs? | Excel Programming | |||
dynamic ranges in excel 2007 | Charts and Charting in Excel | |||
union of named ranges based only on the names of those ranges | Excel Programming | |||
Excel macro to insert names for ranges, not always the same size.. | Excel Programming |