Adding many named ranges takes too much time RRS feed

  • Question

  • Hi together,

    I have a document-level Excel application where I generate a sheet copying blocks from a template sheet to a destination sheet.
    Within the block I work with named ranges which are block-specific. So after copying the block I have to adpat the names of the ranges to the specific names for that block.

    All works fine but the part of adding the new names takes too much time:
    Within the block I have about 300 names which have to be added. This takes more than 1 second, increasing time with every newly added block up to 5 seconds after 50 blocks.
    The number of blocks can be more than 100!

    What really costs time is this line:

    range.Name = "abc";

    It seems that there is an internal management for the ThisWorkbbok.Names container with preallocation and sorting and so on.

    My question is now :
    Is there a more efficient way to add new named ranges? Can I e.g. preallocate the memory for the ThisWorkbbok.Names container ?

    Thanks for any help

    Thursday, July 28, 2011 12:03 PM

All replies

  • Hi

    Try this approach

    Application.EnableEvents = False

    CODE ...


    Application.EnableEvents = True

    MVP Office Development, MCP, MCTS SharePoint 2010 Development
    Thursday, July 28, 2011 12:44 PM
  • Thanks for your quick answer.

    I tried it, but that doesn't change the behaviour.

    Thursday, July 28, 2011 1:19 PM
  • Hi!

    Refer here

    MVP Office Development, MCP, MCTS SharePoint 2010 Development
    Thursday, July 28, 2011 2:51 PM
  • Hi Kay,

    Thanks for your post.

    Have a try this property: Application.ScreenUpdating property 

    Turn off this property to speed up your code and set the property back to True to update the UI after your processor.

    I hope this helps.

    Best Regards, Calvin Gao [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, July 29, 2011 6:15 AM
  • Hello Calvin,

    Thanks for your answer. I have this performance problems with this property set to false.

    As well it's not a problem of getting the range or reading or writing data from/to a range.

    It really seems to be a problem of adding a named range to the list of named ranges. I did some investigations. Summarized I can say:

    Depending on how many named ranges already exist in the thisWorkbook.NamesContainer not every operation costs time.


    1000 named ranges in Container --> every 10th operation costs 16ms
    5000 named ranges in Container --> every 2nd operation costs 16ms

    increasing that every operation costs 16ms
    increasing that every operation costs 20ms
    increasing that every operation costs 30ms

    I hope this helps to understand my problem



    Friday, July 29, 2011 8:27 AM
  • Hello Calvin,

    meanwhile we talked to microsoft (even developers) and they confirmed the behaviour. The performance seems to be a little better using .net 4.x instead of .net 3.5 as I'm doing. But they do not have a solution either.

    So what we did is to reduce the number of named ranges as far as possible. This increased the perfomance in a way the customer is content with (I'm not).

    So we can close this thread.


    Kay Sommer

    Tuesday, November 8, 2011 2:23 PM