Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, and 2013. 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.

Develop Macros in Their Own Workbook

by Allen Wyatt
(last updated February 14, 2015)

7

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:

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 (10351) applies to Microsoft Excel 2007, 2010, and 2013. You can find a version of this tip for the older menu interface of Excel here: Develop Macros in Their Own Workbook.

Author Bio

Allen Wyatt

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. ...

MORE FROM ALLEN

Jumping to the End without Repaginating

When you open a document and try to jump to the end of it, Word may go through the sometimes long process of ...

Discover More

Automatically Opening a Document at a Specific Zoom Setting

Do you prefer to have your documents open at a specific zoom magnification? You can get whatever magnification you desire ...

Discover More

Every Second Tuesday

Need a way to enter dates from every second Tuesday (or some other regular interval)? Excel makes it easy, providing ...

Discover More

Comprehensive VBA Guide Visual Basic for Applications (VBA) is the language used for writing macros in all Office programs. This complete guide shows both professionals and novices how to master VBA in order to customize the entire Office suite for their needs. Check out Mastering VBA for Office 2010 today!

More ExcelTips (ribbon)

Maximum Length Limit for a Macro

Make your macros too long, and Excel may just refuse to run them at all. This tip explains what the limit is for macros, ...

Discover More

Macro, while Running, Stops Excel from Responding

When running a macro, have you ever seen Excel appear to stop responding? This can be frustrating, but there are a couple ...

Discover More

Showing RGB Colors in a Cell

Excel allows you to specify the RGB (red, green, and blue) value for any color used in a cell. Here's a quick way to see ...

Discover More
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

View most recent newsletter.

Comments

If you would like to add an image to your comment (not an avatar, but an image to help in making the point of your comment), include the characters [{fig}] in your comment text. You’ll be prompted to upload your image when you submit the comment. Maximum image size is 6Mpixels. Images larger than 600px wide or 1000px tall will be reduced. Up to three images may be included in a comment. All images are subject to review. Commenting privileges may be curtailed if inappropriate images are posted.

What is 6 - 3?

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

balthamossa2b

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???


This Site

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.

Newest Tips
Subscribe

FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."

(Your e-mail address is not shared with anyone, ever.)

View the most recent newsletter.