Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Develop Macros in Their Own Workbook.
Excel includes VBA as a powerful programming language that you can use to develop all sorts of macros. It is not unusual, as you are developing macros, to go through many iterations and make wholesale changes to your macros. You may want to keep in mind, however, that doing so can cause problems in your workbooks.
As you make changes to macros, adding and removing code, the actual file used to store the macros (the workbook) can get quite fragmented. It seems that internally the macros are stored in blocks and, much like a disk drive, the blocks can become "non-contiguous" over time. (This happens only through editing, not through use of the macros themselves.) Some readers have reported that there are times the fragmentation can get so bad that the macros may fail or the workbook become unusable.
The solution to this potential problem is to do your macro development in a different workbook than the one that will eventually hold the macros. Thus, when the macro is transferred to its final home, it will be transferred as a contiguous block, rather than being fragmented.
If you want to make sure that the macro fragmentation is completely removed from a current workbook, all you need to do is export your VBA modules to text files, create a brand new workbook, and import the modules into it.
Note:
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (10351) applies to Microsoft Excel 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. You can find a version of this tip for the older menu interface of Excel here: Develop Macros in Their Own Workbook.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
If your macro closes workbooks, you'll want to make sure that it will save any changes you made to the workbook. Here's ...
Discover MoreWhen creating a workbook to be used by others, you may want any worksheets they add to the workbook to contain some ...
Discover MoreYour macros can easily open and manipulate other Excel workbooks. If a workbook you are trying to use is already in use ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-03-18 23:11:47
sarma
This tip brought lot of peace of mind. One of my workbooks developed "unusable" issue & I was agonizing, even suspecting some problem with my computer.
Now, I know what I should do to set it right.
Thanks.
2015-02-16 11:12:09
Huh, this has never happened to me, fortunately. It sounds like a real pain.
@John (completing Ward's answer)
PivotTable cache can also do that. Or having too many formats.
2015-02-16 04:48:37
Ward N
Unexplained growth in filesize : check the "last used cell" issue.
Resetting this has been a filesize reducer lots of the time for me.
2015-02-16 03:25:16
John Baldock
I have a similar situation to Sheldon, but on an even bigger scale. I have discovered that the main issue has been unexplained growth in filesize - I overcome this by copying and overwriting Module and Form code. I too would like to know if this technique will have the effect of 'cleansing' my code. Thanks.
2015-02-15 17:44:05
Jeff C
Is there a way to detect this fragmentation or is it being suggested that the code be imported into a new workbook only when issues start to occur?
2015-02-14 09:14:33
Roger Plant
Would simply COPYING the macro(s) from one module to another module and then deleting the original module suffice to avoid fragmentation?
2015-02-14 07:24:07
sheldon hopkins
I have a complex Excel spreadsheet (13 worksheets,26 userforms, 18 modules, 504 active controls, 5000+ lines of code) which has undergone modification over 6 years. This tip (export/import modules to defragment) could be quite usable for me.
Question:
Would Exporting a module, deleting the module, then importing the module have the same effect on a potentially fragmented module???
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2020 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, 2016, 2019, and Excel in Office 365. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments