+ Reply to Thread
Results 1 to 7 of 7

VBA to Control Formula Calculations

  1. #1
    Forum Contributor
    Join Date
    07-02-2008
    Location
    Utah
    Posts
    171

    VBA to Control Formula Calculations

    Hi,

    I have a really large file (~160MB) that takes forever when I calculate the formulas. Currently, I have the Excel Options set to manual calculation. I have been using CTRL+ALT+F9, SHIFT+F9, and F9 to calculate but it can still take a while.

    I need some VBA that gives me greater flexibility so I can calculate specific cells faster. For example, if I could calculate just a range of cells or just calculate cells without dependencies, etc. Any help you can provide would be much appreciated!

    Thanks!!
    John
    Last edited by John Bates; 06-07-2010 at 02:47 PM. Reason: Solved

  2. #2
    Forum Expert royUK's Avatar
    Join Date
    11-18-2003
    Location
    Derbyshire,UK
    MS-Off Ver
    Xp; 2007; 2010
    Posts
    26,200

    Re: VBA to Control Formula Calculations

    You calculate all or none I'm afraid
    Hope that helps.

    RoyUK
    --------
    For Excel Tips & Solutions, free examples and tutorials why not check out my web site

    Free DataBaseForm example

  3. #3
    Forum Contributor
    Join Date
    07-02-2008
    Location
    Utah
    Posts
    171

    Re: VBA to Control Formula Calculations

    So there isn't a way to use some custom-written VBA code to calculate? For example, using Range.Calculate function or something like this?

    Thanks,
    John

  4. #4
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: VBA to Control Formula Calculations

    If you run on Manual Calc (or remove sheets from the Calc stack) you can calculate specific ranges but obviously this is far from ideal.

    For info. on calculation see: http://www.decisionmodels.com/calcsecretsg.htm and other related pages.

    I would say however that your problems sound more fundamental - no file should take "forever" to calculate - the same site referenced above has lots of info. on optimisation techniques - the most general rules of thumb are:

    a) avoid using Arrays (incl. SUMPRODUCT) en masse
    b) avoid Volatility in large models (esp. in conjunction with point a)
    c) try to sort data wherever viable for large range lookups so as to facilitate use of binary search (much more efficient given only a small % of values will be processed)

    etc etc...

  5. #5
    Forum Contributor
    Join Date
    07-02-2008
    Location
    Utah
    Posts
    171

    Re: VBA to Control Formula Calculations

    Ok, thanks. I do have arrays and SUMPRODUCTS. Not to mention the ~250,000 rows by 90 columns of data that the calculations are based on.

    Thanks!
    John

  6. #6
    Forum Guru DonkeyOte's Avatar
    Join Date
    10-22-2008
    Location
    Northumberland, UK
    MS-Off Ver
    O365
    Posts
    21,531

    Re: VBA to Control Formula Calculations

    Well I'd say there's one huge bottleneck...

    SUMPRODUCTs and Arrays are very expensive in terms of performance given their iterative nature - esp. used over large ranges given they (unlike most "normal" formula) are not restricted against the usedrange intersect of the specified ranges.

    You will often find that using 1000's of helpers (eg concatenation etc) to negate the need for Arrays / Sumproduct (replaced with SUMIF, COUNTIF etc and wildcard) will greatly improve performance.

    edit: demo link of concept: http://www.youtube.com/watch?v=ry3vSqN1TQ0

    Less in XL is most definitely not more - elegance and efficiency do not always go together hand in hand (unfortunately).

    Without knowing specifics its hard to offer anything more substantial I'm afraid.
    Last edited by DonkeyOte; 06-07-2010 at 02:29 PM.

  7. #7
    Forum Contributor
    Join Date
    07-02-2008
    Location
    Utah
    Posts
    171

    Re: VBA to Control Formula Calculations

    I agree...Because of the complexity of the formulas needed and the time it took to build I used formulas which I was more familiar with (not necessarily optimal for efficiency). I will take your suggestion as I improve the 2nd version of the file/tool so it is more efficient. thanks again for all your help!

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

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