Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating defining names
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Automating defining names
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 "Sid DeLuca" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
defining names in a macro | Excel Discussion (Misc queries) | |||
Help with defining names | Excel Worksheet Functions | |||
Defining Regions with Names | Excel Discussion (Misc queries) | |||
Defining Names in Excel | Excel Worksheet Functions | |||
Defining Names | Excel Programming |