Automating defining names

  • Thread starter Thread starter Sid DeLuca
  • Start date Start date
S

Sid DeLuca

I am trying to streamline a certain process in our
software. In this process, we select client's data and
merge it with Excel ranges for formatting. The
destination workbook created ("on the fly") ends up with
thousands of rows of formulas and text. The final step is
to loop through each cell and define a particular name
when a certain text string is encountered, as dictated by
the cell contents.

The process runs fine for our clients with less than a
couple thousand records. However, for clients whose data
generates 15000+ rows and 20000+ defined names, the
process can take hours to complete. The Excel ranges can
be pasted in less than an hour. However, the remaining
time is simply defining the names in the worksheet (1).

Other information: Calculation is set to manual. Forcing
a periodic calculation does not seem to make a
difference. The names being defined are already
referenced in one or more cells (at this time, formulas go
from displaying #NAME to the value referenced).
ScreenUpdating is FALSE. The actual loop is done through
a variant type (array) of the Excel UsedRange range. All
functions are executed in the same process space as
Excel.

My question is: can someone explain to me why the act of
defining names takes so long? The code is:
MyWorksheet.Range(sAddress).Name = sProposedName

More questions: any suggestions of how to speed it up? It
seems to be related to how or the number of times the name
is referenced. Does it make a difference if the name is
re-used (new name definition replaces old)? Does this
have to do with Excel queuing up and flagging cells for
later calculation? Am I left with manipulating a HUGE XML-
SS file representation instead??

Thanks for your attention.

Sid DeLuca
 
Here is something you could try if you think calculation is part of the
problem:

in your code, do a replace (as in Edit=>Replace)

replace all equal signs with something unique like ZZZ=

run your code that creates the names

repeat the replace. but now replace ZZZ= with equal.

It might help.

Regards,
Tom Ogilvy
 
Back
Top