Why does macro run slow

Problems like this can be baffling, and they often take some heavy-duty analysis in order to figure out. A good place to start is to add some "timer code" in your macro. Add a small routine that saves a time value and another routine that compares that saved value to the current time and displays the difference.

At the beginning of a section of code you want to analyze, you call the first routine which saves the start time and then at the end of the section of code you call the second routine. In that way, you can determine which portions of your code are taking the longest time to execute. These are the code sections you then focus on, so you can figure out what they are doing that is taking so long.

These turn off screen updating, which can slow down a running macro, and disable events. This last line is included so that changes done by the macro in your worksheet won't trigger Excel's recalculation routines. If your macro is making a lot of changes in the data in the worksheet, and a full recalculation is triggered after each change, then with such a large workbook, lots and lots of time can be spent just doing the recalc.

At the end of your macro, you reverse the effect of the two lines you added:. You may also want to turn off automatic calculation while your macro is running. Doing so makes sure that Excel doesn't try to calculate intermediate results while the macro is moving things around or otherwise working with data.

To turn off automatic calculation, use this line at the beginning of your macro:. It is a good idea to turn off automatic calculation in a macro only if your macro doesn't rely on calculated information in the worksheet.

If you do turn it off, you can later turn automatic calculation back on by placing the following line near the end of your macro:. One reader suggested giving focus to another application while the macro is running in the Excel worksheet. For instance, open up Notepad and make that window active. The macro should speed up considerably when Excel isn't active anymore.

Another option to try is to move the mouse pointer down to the taskbar area instead of having it on the active Excel worksheet. Note: If you would like to know how to use the macros described on this page or on any other page on the ExcelTips sites , I've prepared a special page that includes helpful information.

Click here to open that special page in a new browser tab. ExcelTips is your source for cost-effective Microsoft Excel training. This tip applies to Microsoft Excel , , , , , and Excel in Office With more than 50 non-fiction books and numerous magazine articles to his credit, Allen Wyatt is an internationally recognized author.

He is president of Sharon Parq Associates , a computer and publishing services company. Learn more about Allen If you use a macro to create and work with text files, you can find out the length of those files using a simple command.

You are not limited to starting the line numbering in a document with 1. The optimized code is more efficient and less prone to runtime errors. The commented lines show the Sheet and Table object references. The sheet reference is necessary only if you want to run the macro outside of the sheet Divisions, in this case. The Table references the Species column in a Table named Table3. Similar to selecting ranges and objects to perform an action in the sheet, an explicit reference to the sheet also slows down processing.

The solution is to use variables. For example, the following code references the same cell value six times:. At the very least, ReturnFeeSlow makes two explicit references to I4. It's not changing the value, it's using the value in a simple expression. In this case, it's more efficient to define a variable with the value in I4 and use the variable, as follows:.

Please forgive the obnoxiously contrived examples, but the concept is the point, not the code's purpose. Specifically, built-in updating features and explicit references to the sheet or a range will slow down your code.

Admittedly, with today's fast systems, simple macros won't always need optimization. However, if you're working with a complex custom application, these easy-to-implement changes should improve efficiency. Be your company's Microsoft insider by reading these Windows and Office tips, tricks, and cheat sheets. Delivered Mondays and Wednesdays. I answer readers' questions when I can, but there's no guarantee.

Don't send files unless requested; initial requests for help that arrive with attached files will be deleted unread. You can send screenshots of your data to help clarify your question. When contacting me, be as specific as possible.

For example, "Please troubleshoot my workbook and fix what's wrong" probably won't get a response, but "Can you tell me why this formula isn't returning the expected results? Please mention the app and version that you're using. I'm not reimbursed by TechRepublic for my time or expertise when helping readers, nor do I ask for a fee from readers I help. The recommended solution is shown in the following function AutoFillTable2 :.

Product Why think-cell? All features Continuous improvement Customer references Case study. Download Existing customer Free trial. Company About us News Contact. Resources Knowledge base KB Why is my Excel macro slow when think-cell is activated? Microsoft also recommends to avoid the.