Names

[Microsoft]

Hi,

I'm populating a spreadsheet from some databases with quite a bit of
reference data and some formulas and data for analysis. For 80 price lines,
I get about 50 sheets of different reference data calculated from numerous
services and data sources. This results in about 30,000 named cells. The
names are the trouble. They have to be global to the workbook, so they are
all stored in the same "dictionary". The first 5-10,000 names are no
problem, but hereafter performance is O(n^2). Up to 30 pricelines, I can
populate the spreadsheet with one line per second, but for 80 lines it takes
about 5 minutes. I start by putting the cell names in a SafeArray and
populating the spreadsheet one sheet at a time. I can fill 18000 cells in
about 4 seconds. I then call CreateNames for each column. With 1000 lines
and 18 columns, I can create the first 1000 names in 30 ms, the second 1000
names in 60 ms, then 131 ms, 260 ms, 471 ms, 711 ms, .... and the last 1000
names in 4256 ms, or logarithmic longer times. I figured that the
"dictionary" object that holds names in excel is a hash map and that similar
names results in long lists to be traversed for existence, so I've tried to
prefix the names with a unique prefix to aid the hash function to evenly
distribute the map, put this only helped marginally. So, here is the
question :

Is it possible to replace the "dictionary" object that holds the names in
Excel with another COM object, so I could base it on a binary search tree,
or the like ? If not, is it possible to tune the "dictionary" object for
better performance with large volumns of data ?

I can seem to find anything in the COM interfaces or the worksheet
functions, nor the other registered office interfaces. I've also checked all
the DLLs, like mso, for usefull functions, but without luck. I've tried to
turn off all visual, events, history, and calculation effects. The solution
has to work for both Excel 2000 and XP, so I can't disable the error
checking for the 2000 version through the ErrorCheckingOptions interface (
it does give that much anyway in XP ). The Excel.exe process uses more than
99% of the time and my application uses less than 1%. Data is loaded in
SafeArrays and formatting is done on discontinuous ranges without any
problems. Please help as I'm running out of ideas.

Sincerely, Daniel von Tabouillot.

 

[Tom Ogilvy]

Can you change the internal workings of excel. No.

Is there any utility to having 30000 named cells - that would be hard to
fathom. Maybe you have some misperception about excel that leads you to
believe you have to name all the cells - on the other hand, maybe you have a
perfectly good reason for doing it.

 

[Microsoft]

Hi Tom,

There are about a million cells for 80 price lines, so only the ones
used in formulars are named. We generate the spreadsheet in our application
from server responses and then let the user manipulate the data before
importing a subset. We protect all that we can, but all cells that the user
can move around have to be named. It is just ashamed that I can't do
anything about the names because the rest ( loading and formatting data,
formula calculations, and the VBA macros for calculations ) completes in
acceptable times, but the part using names use more than 90% of the time and
is O(n^2).
Well, thanks for your input.

Sincerely, Daniel von Tabouillot.

 

[Tom Ogilvy]

If the names are the problem, I would test using just cell references -
excel does adjust these for some types of moving around, at least as
robustly as names.