Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory leak in names collection?
Creating and deleting names repeatedly in Excel (2003, SP1) causes Excel
memory usage to grow. Furthermore, the more names have been created and deleted, the slower the below script becomes: Private Sub confuseExcel() Dim i As Long Dim myName As String For i = 0 To 5000 myName = "name" & CStr(cc) ActiveWorkbook.Names.Add Name:=myName, RefersToR1C1:="=Sheet1!R1C1" ActiveWorkbook.Names(myName).Delete Next i End Sub Creating and deleting the same name repeatedly behaves as expected, memory usage does not grow and the script runs at a constant speed. The real problem here is that I am developing a dll using C API, which relies on hidden dll names (calling xlsetName with Excel4) for identifying objects in memory. During a single Excel session, there may easily be 200000 names created and then deleted but so that only a few exists at the same time. Nevertheless, after creating and deleting maybe 10000 names Excel becomes very bloated and unusable slow. Is there a fix to this or a way around the problem? |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory leak in names collection?
The true usefulness of names is their permanence, and ability to move as ranges are moved around.
Why create a name if you are only going to delete it a short while later? Just set a range object = to the range of interest, and reference that instead: Dim myR As Range Set myR = Activeworkbook.Worklsheets("Sheet1").Range("A1") Then, instead of referencing Range("name1") just use myR HTH, Bernie MS Excel MVP "Karri" wrote in message ... Creating and deleting names repeatedly in Excel (2003, SP1) causes Excel memory usage to grow. Furthermore, the more names have been created and deleted, the slower the below script becomes: Private Sub confuseExcel() Dim i As Long Dim myName As String For i = 0 To 5000 myName = "name" & CStr(cc) ActiveWorkbook.Names.Add Name:=myName, RefersToR1C1:="=Sheet1!R1C1" ActiveWorkbook.Names(myName).Delete Next i End Sub Creating and deleting the same name repeatedly behaves as expected, memory usage does not grow and the script runs at a constant speed. The real problem here is that I am developing a dll using C API, which relies on hidden dll names (calling xlsetName with Excel4) for identifying objects in memory. During a single Excel session, there may easily be 200000 names created and then deleted but so that only a few exists at the same time. Nevertheless, after creating and deleting maybe 10000 names Excel becomes very bloated and unusable slow. Is there a fix to this or a way around the problem? |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory leak in names collection?
Karri,
You are not working with the workbook's names collection! but in the applications "hidden name space". The hidden namespace was intended to keep track of variables in macros while executing. and yes, there's memory leaks there, as you cannot delete the names, just invalidate them. You should be using with DEFINE.NAME not SET.NAME (see MacroFun.Hlp or xlMacro.chm for details) Sub confuseExcel() Dim i&, m& m = 5 'increase for testing... 'xl4 DEFINE.NAME (workbook names) For i = 1 To m ExecuteExcel4Macro Replace("DEFINE.NAME(""Name#"",""=R1C1"")", "#", i) ExecuteExcel4Macro Replace("DELETE.NAME(""Name#"")", "#", i) Next 'xl4 SET.NAME (application or macro names) For i = 1 To m ExecuteExcel4Macro Replace("SET.NAME(""hName#"",""=$A$1"")", "#", i) ExecuteExcel4Macro Replace("SET.NAME(""hName#"","""")", "#", i) Next End Sub HTH.... but fairly sure it does! -- keepITcool | www.XLsupport.com | keepITcool chello nl | amsterdam Karri wrote in Creating and deleting names repeatedly in Excel (2003, SP1) causes Excel memory usage to grow. Furthermore, the more names have been created and deleted, the slower the below script becomes: Private Sub confuseExcel() Dim i As Long Dim myName As String For i = 0 To 5000 myName = "name" & CStr(cc) ActiveWorkbook.Names.Add Name:=myName, RefersToR1C1:="=Sheet1!R1C1" ActiveWorkbook.Names(myName).Delete Next i End Sub Creating and deleting the same name repeatedly behaves as expected, memory usage does not grow and the script runs at a constant speed. The real problem here is that I am developing a dll using C API, which relies on hidden dll names (calling xlsetName with Excel4) for identifying objects in memory. During a single Excel session, there may easily be 200000 names created and then deleted but so that only a few exists at the same time. Nevertheless, after creating and deleting maybe 10000 names Excel becomes very bloated and unusable slow. Is there a fix to this or a way around the problem? |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Memory leak in names collection?
Thanks guys for your advice. I am associating the names with C++ objects in memory and then returning the name to the calling cell so that other functions can find the object in memory, too. The idea was to change the name every time the C++ object changes, that is, whenever the cell is recalculated. This way it is easy to follow what's happening in the spreadsheet. This works quite neatly until there are so many names that excel gets bloated. Well, I can always recycle the names and thereby avoid performance problems. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
XL 2007 - Out of Memory - memory leak/bug? | Excel Discussion (Misc queries) | |||
Track a Memory Leak | Excel Programming | |||
Memory Leak | Excel Programming | |||
Memory Leak | Excel Programming | |||
Memory Leak using ADO 2.6 | Excel Programming |