+ Reply to Thread
Results 1 to 5 of 5

Question about Drawbacks Using CalculationManual in Excel VBA

  1. #1
    Registered User
    Join Date
    11-05-2016
    Location
    U.S.
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    6

    Question about Drawbacks Using CalculationManual in Excel VBA

    Hello,

    I just used CalculationManual in some code I modified from the internet, like below:

    Please Login or Register  to view this content.
    I ran across 2 articles that said that there was a drawback to using CalculationManual and I don't understand what they are saying on a couple of points and was hoping someone could explain:

    1. They said 'This is a band aid approach (using Manual Calculation) rather than addressing the underlying cause' - I guess he is referring to efficient spreadsheet design, like faster formulas, etc.?

    2. They said, 'If you run any Excel Workbook in manual calculation it's only a matter of time before non-calculated data is read off!' - What does that mean, 'read off'?

    Full quote:
    If you have Excel VBA macro code that runs slow, the chances are it's caused by Excel having to recalculate at each line of code. Deleting rows is one of many things that can become painfully slow. This can be overcome very easily by switching Excel into manual calculation before your code runs. Just be aware that if your code bugs out, and you have no error trap, the Workbook will be left in manual calculation and NO properly designed spreadsheet should ever be used with calculation in manual. If you ever get advice to switch Excel into manual calculation to prevent slow saving, closing and data entry...run very fast! This is a band aid approach rather than addressing the underlying cause. If you run any Excel Workbook in manual calculation it's only a matter of time before non-calculated data is read off! See Efficient Spreadsheet Design .
    3. Should there be any concern in putting Excel in manual calculation at the beginning of the code as the quote says below b/c you're going to turn it back on for the other worksheets, too, at the end of the code, correct?


    There is an important caveat to remember in relation to using this macro. You can only set the calculation mode for the application as a whole. Thus, with automatic recalculation turned off, no other worksheets will be automatically recalculated, either.
    Thank you in advance for your help.

    Sources:
    http://www.ozgrid.com/VBA/calc-stop.htm
    https://excel.tips.net/T001988_Forci..._Workbook.html

  2. #2
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Question about Drawbacks Using CalculationManual in Excel VBA

    1.) The author is referring to efficient spreadsheet design as you surmised.

    2.) Don't know what "read off" means in this context.

    3.) The concern is...
    Just be aware that if your code bugs out, and you have no error trap, the Workbook will be left in manual calculation and NO properly designed spreadsheet should ever be used with calculation in manual.
    Your code doesn't appear to have an error trap.
    Surround your VBA code with CODE tags e.g.;
    [CODE]your VBA code here[/CODE]
    The # button in the forum editor will apply CODE tags around your selected text.

  3. #3
    Registered User
    Join Date
    11-05-2016
    Location
    U.S.
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    6

    Re: Question about Drawbacks Using CalculationManual in Excel VBA

    Thank you, AlphaFrog. Re: #3, I was really asking about the quote below it. I just didn't see why he was concerned about calculations being turn off for the Excel application as a whole, thus affecting other worksheets, b/c the code will take care of that at the end where one sets the calculation back to automatic.

    I just wonder what you or someone else thinks about that. I'm just learning Excel VBA; that is why I'm asking. Thank you!

  4. #4
    Forum Guru
    Join Date
    07-25-2011
    Location
    Florida
    MS-Off Ver
    Excel 2003
    Posts
    9,643

    Re: Question about Drawbacks Using CalculationManual in Excel VBA

    I don't have a problem with a macro temporarily suspending calculations, other than #3.

  5. #5
    Registered User
    Join Date
    11-05-2016
    Location
    U.S.
    MS-Off Ver
    Office 365 Excel 2016
    Posts
    6

    Re: Question about Drawbacks Using CalculationManual in Excel VBA

    AlphaFrog, thank you. By #3 I mean the quote below, by the way. OK, thank you for your help.

    There is an important caveat to remember in relation to using this macro. You can only set the calculation mode for the application as a whole. Thus, with automatic recalculation turned off, no other worksheets will be automatically recalculated, either.

+ 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] Mathematical question or excel question? Multiple several numbers in a row.
    By douglascaixeta in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-22-2015, 05:48 PM
  2. Issue with CalculationManual on worksheet
    By scottc_00 in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 02-18-2015, 10:36 AM
  3. Are there drawbacks to using the "End" statement?
    By dmasters4919 in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 01-16-2015, 12:44 PM
  4. database question, excel data sorting question
    By weblisterltd.com in forum Excel General
    Replies: 1
    Last Post: 12-17-2014, 01:35 PM
  5. Any drawbacks to disabling the Live Preview feature??
    By RickyJames in forum Excel General
    Replies: 0
    Last Post: 05-07-2014, 10:35 PM
  6. Excel 2.007 drawbacks vs. Excel 2.003
    By rolito in forum Excel General
    Replies: 1
    Last Post: 06-14-2007, 10:00 AM
  7. Replies: 3
    Last Post: 01-23-2006, 02:25 PM

Tags for this Thread

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