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

Automatic Initial Capitals in Tables

Have you ever started typing words in a table, only to find that Word automatically capitalizes the first word in each ...

Discover More

Selecting to the Next Punctuation Mark

Writing macros often involves selecting different parts of your document so that some sort of processing can be ...

Discover More

Using the Format Painter with Editing Restrictions in Place

Word allows you to apply protection to your documents that can affect which tools users can access. If you want to exempt ...

Discover More

Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!

More ExcelTips (ribbon)

Trouble Recording Paste Special Formula

Sometimes, when you upgrade to a new version of Excel, you could run into a problem recording macros that you had no ...

Discover More

Offering Options in a Macro

It is often helpful to get user input within a macro. Here's a quick way to present some options and get the user's response.

Discover More

Deleting Worksheet Code in a Macro

When creating an application in VBA for others to use, you might want a way for your VBA code to modify or delete other ...

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

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.