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?