Memory leak in names collection?

[Guest]

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?

 

[Bernie Deitrick]

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

 

[keepITcool]

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

 

[Guest]

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.