+ Reply to Thread
Results 1 to 6 of 6

Enable AutoCalculations at the end of a macro

  1. #1
    Registered User
    Join Date
    08-27-2014
    Location
    Florida, U.S.
    MS-Off Ver
    2010
    Posts
    20

    Enable AutoCalculations at the end of a macro

    Hello,

    I have a macro that disables autocalc at the beginning to speed up the find/replace process for large quantities of data. The code works flawlessly but I cannot get autcalc to turn back on. I have it in the code and have stepped through the code with F8. It appears to be working but at the end when I run any mass calculations (e.g, dragging a calculation down, my autocalc is still set to off). Can you tell me what I am missing?

    Please Login or Register  to view this content.

  2. #2
    Forum Guru
    Join Date
    03-28-2012
    Location
    TBA
    MS-Off Ver
    Office 365
    Posts
    12,454

    Re: Enable AutoCalculations at the end of a macro

    Please Login or Register  to view this content.

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

    Re: Enable AutoCalculations at the end of a macro

    Other things to note:

    1.) Remove or comment out the On Error line to see if your code throws an error. It's not good practice to just ignore code errors unless that's your explicit intent.

    2.) In this case, the code doesn't go any faster because of
    Please Login or Register  to view this content.
    You are only using one Replace function then turning those Application settings back on. There's no advantage to turn them off and only do one thing. The advantage is to turn them off when doing a loop or using multiple functions; especially when repeatedly using ones that are slow.

    3.) You use variable names like Find and Replace. Those happen to be identical names of functions as well. It's leagle to use those variable names, but its's considered unnecessarily confusing and bad practice.
    Last edited by AlphaFrog; 08-24-2016 at 03:34 PM.
    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.

  4. #4
    Registered User
    Join Date
    08-27-2014
    Location
    Florida, U.S.
    MS-Off Ver
    2010
    Posts
    20

    Re: Enable AutoCalculations at the end of a macro

    Quote Originally Posted by AlphaFrog View Post
    Other things to note:

    1.) Remove or comment out the On Error line to see if your code throws an error. It's not good practice to just ignore code errors unless that's your explicit intent.

    2.) In this case, the code doesn't go any faster because of
    Please Login or Register  to view this content.
    You are only using one Replace function then turning those Application settings back on. There's no advantage to turn them off and only do one thing. The advantage is to turn them off when doing a loop or using multiple functions; especially when repeatedly using ones that are slow.

    3.) You use variable names like Find and Replace. Those happen to be identical names of functions as well. It's leagle to use those variable names, but its's considered unnecessarily confusing and bad practice.
    How would I be able to go through the entire process and then turn on auto calculations within the same code?

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

    Re: Enable AutoCalculations at the end of a macro

    Please Login or Register  to view this content.

  6. #6
    Registered User
    Join Date
    08-27-2014
    Location
    Florida, U.S.
    MS-Off Ver
    2010
    Posts
    20

    Re: Enable AutoCalculations at the end of a macro

    Thank you Alpha Frog for teaching me. I appreciate you giving me some of your time and solutions.

+ 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] How to save macro enable file without selecting macro enable option?
    By Imran Magsi in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 03-01-2016, 02:37 AM
  2. Replies: 1
    Last Post: 01-29-2015, 12:36 PM
  3. macro to open excel file with auto macro enable
    By jamewoong in forum Excel Programming / VBA / Macros
    Replies: 8
    Last Post: 04-14-2014, 10:44 AM
  4. Replies: 0
    Last Post: 08-06-2013, 08:21 AM
  5. VBA Help - turning autocalculations off
    By nickm84 in forum Excel Programming / VBA / Macros
    Replies: 2
    Last Post: 05-27-2013, 04:23 PM
  6. Big challenge: protect multiple sheets AND enable outlining AND enable insert comment
    By visschercaravelle in forum Excel Programming / VBA / Macros
    Replies: 6
    Last Post: 10-23-2010, 11:46 AM
  7. VBA select default Don't Enable / Enable macros Setting
    By BEEJAY in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 06-29-2006, 03:50 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