How to increase Named Range performance using VBA? RRS feed

  • Question

  • Hi,

    I have 500 formulas in excel sheet, I replaced all cell reference in all formula with named range to increase quick readability/understand the purpose of formula. Now all formula taking more time to calculate and return result in sheet after replacing all cell reference with named range in formulas. Most of the formula having cell reference with other sheet not in the same sheet.

    Also workbook file size is increased and it taking more time to load after creating new named range and replacing cell reference with named range

    Now how i can increase the performance of named range. Please help me.

    Refer below example for better clarity


    • Edited by Balaramji Saturday, June 4, 2016 11:49 AM
    Saturday, June 4, 2016 11:45 AM

All replies

  • Hi,

    the effects you describe are easy to explain:

    1. Because names in formulas have to be looked up during the execution of a formula, you get a performance panelty, especially if you have defined many names.
    2. The table of names is part of the workbook. So you get some small size penalty.

    But the workbook is easier to understand and maintain.

    One chance to improve performace again is to use array formulas, if you have iterating formulas.

    Some versions of EXCEL used to retain replaced/changed code after editing. Maybe this has hit you too.Try to copy the worksheets to an empty workbook. This should eliminate such artefacts.

    Hope this helps.

    Regards Jörg

    Monday, June 6, 2016 4:21 PM