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.

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

Changing to UK English

Understanding how Word uses the Language settings.

Discover More

Creating Labels

Using Word to create and print labels is a snap. All you need to do is provide the text you want on the labels, pick a type ...

Discover More

Dissecting a String

Want to pull a string apart in a macro? It's easy using the string functions introduced in this tip.

Discover More

Professional Development Guidance! Four world-class developers offer start-to-finish guidance for building powerful, robust, and secure applications with Excel. The authors show how to consistently make the right design decisions and make the most of Excel's powerful features. Check out Professional Excel Development today!

More ExcelTips (ribbon)

Making Common Functions Available to Others

When you use macros to create functions, you might want to share those functions with others—particularly if they ...

Discover More

Automating Copying Macros

You can manually copy macros from one workbook to another, but what if you want to automate the copying process? Here's some ...

Discover More

Bypassing the BeforeClose Event

Hold down the Shift key as you open a workbook, and Excel bypasses any "startup macros" that may be in the workbook. If you ...

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 3 + 8?

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.