+ Reply to Thread
Results 1 to 10 of 10

Accelerate workbook performance without rebuilding indirect formulas

  1. #1
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Accelerate workbook performance without rebuilding indirect formulas

    Hi,

    i am using many indirect references for listobjects within formulas.
    I think this is causing recalculation of whole workbook all the time (when one formula is changed).

    I have to rebuild macro for downloading tables from database (not column but rows approach to eliminate all indirect references) and rewrite all formulas
    (about 1500 formulas). But this will take a little time.

    Maybe there is an option to have a fix not requiring changing whole workbook model?
    Like calculation (second option):

    Screenshot_10.png

    Or turn on calculation only when sheets is activated.
    Or other workarounds for this?

    Creating workbook model i didn't think that it will be so enormous that it will cause worst overall performence (all the time calculating in the background).

    Do you have any solutions for this?
    Please help,
    Jacek

  2. #2
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Accelerate workbook performance without rebuilding indirect formulas

    For complex workbooks with a lot of overhead calculations, it is better to use manual mode with some user-initiated re-calc method (like a macro or event).
    Bernie Deitrick
    Excel MVP 2000-2010

  3. #3
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Accelerate workbook performance without rebuilding indirect formulas

    Hi Bernie Deitrick,

    thank you and sorry for my late answer.

    it is better to use manual mode with some user-initiated re-calc method (like a macro or event).
    can you explain in details?

    Manual and for example if user will input something in cell C3 macro event should run calculation for formula where vlookup is?
    Or what?

    Best,
    Jacek

  4. #4
    Forum Expert
    Join Date
    02-11-2014
    Location
    New York
    MS-Off Ver
    Excel 365 (Windows)
    Posts
    5,912

    Re: Accelerate workbook performance without rebuilding indirect formulas

    For example, if there is one worksheet that needs to be calculated when you change cell C3 - or, say, any cell in C2:C20 - of that sheet:

    1) Copy this code.
    2) Right-Click the sheet tab of interest.
    3) Select "View Code"
    4) Paste the code into the window that appears.
    5) Save the file as a macro-enabled .xlsm file.
    6) Make changes as needed

    Please Login or Register  to view this content.

  5. #5
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Accelerate workbook performance without rebuilding indirect formulas

    thank you Bernie,

    interesting approach.
    I love VBA (it is my work!) but i am avoiding if i can use native Excel formulas.
    If you have vlookup function and referencing to table without indirect only one cell will be calculated - with this formula. There is no need to write code.
    But if you have multiple indirects your workbook can be slow.

    What is other Masters opinion about this topic?

    Best,
    Jacek
    Last edited by jaryszek; 04-18-2019 at 11:50 AM.

  6. #6
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Accelerate workbook performance without rebuilding indirect formulas

    Bump,

    Jacek

  7. #7
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Accelerate workbook performance without rebuilding indirect formulas

    Did anybody had similar issue ?

    Jacek

  8. #8
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Accelerate workbook performance without rebuilding indirect formulas


  9. #9
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2019
    Posts
    17,428

    Re: Accelerate workbook performance without rebuilding indirect formulas

    If the worksheet were put in manual mode then the user could recalculate all open documents by pressing the F9 key or just the current worksheet by pressing the Shift and F9 keys.
    Documentation (near bottom of article): https://support.microsoft.com/en-us/...of-calculation
    Let us know if you have any questions.
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  10. #10
    Forum Contributor
    Join Date
    01-16-2014
    Location
    Poland
    MS-Off Ver
    Excel 2016-365
    Posts
    2,940

    Re: Accelerate workbook performance without rebuilding indirect formulas

    Hi,

    F9 key or just the current worksheet by pressing the Shift and F9 keys.
    this would be very inconvenient for my user.

    I have all indirect formulas within NAmed Ranges and working for code to replace "indirect" into table name directly.

    It can work.
    After few testes performance is better but still in the background i have some calculations - i have to check what is causing it.

    Maybe aggregate formulas like this:

    Screenshot_13.png

    A lot of them in my workbook.

    Jacek

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Rebuilding a file (formulas linking to the old file)
    By Jeffy in forum Excel Formulas & Functions
    Replies: 1
    Last Post: 01-22-2016, 01:50 AM
  2. Replies: 1
    Last Post: 01-21-2016, 09:27 PM
  3. [SOLVED] VBA Formula SUMIF with INDIRECT performance issue
    By ffasan in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 07-02-2014, 08:33 AM
  4. [SOLVED] Too many INDIRECT formulas? (slow workbook)
    By Yoshi64 in forum Excel Formulas & Functions
    Replies: 12
    Last Post: 01-09-2014, 11:25 AM
  5. Worksheets, Indirect References and Performance Issues
    By Rikkdh in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 06-28-2013, 09:36 AM
  6. Increase performance speed by removing INDIRECT functions
    By pepperjoe in forum Excel General
    Replies: 1
    Last Post: 05-19-2011, 10:22 AM
  7. accelerate the macro
    By afgi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-01-2010, 09:44 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1