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

Suppressing Headers or Footers

Don't want a header or footer to appear on just a portion of your document? It's easy to do when you understand that ...

Discover More

Converting Strings to Numbers

When creating macros, you often need to convert a text string that contains numbers into actual numeric values. You do ...

Discover More

Counting Words

Do you need to know how many words are in a range of cells? Excel provides no intrinsic way to count the words, but you ...

Discover More

Solve Real Business Problems Master business modeling and analysis techniques with Excel and transform data into bottom-line results. This hands-on, scenario-focused guide shows you how to use the latest Excel tools to integrate data from multiple tables. Check out Microsoft Excel 2013 Data Analysis and Business Modeling today!

More ExcelTips (ribbon)

Displaying the Selected Cell's Address

Need to know the address of the cell that is currently selected? The function and macro highlighted in this tip will come ...

Discover More

Finding Positions of Formatted Characters in a Cell

With a little bit of work, Excel allows you to format individual characters of the text you place in a cell. If you want ...

Discover More

Macros Run Fine Individually, but Not Collectively

Developing macros can be rewarding, but it can also be challenging. Getting individual macros to run properly is hard ...

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 two more than 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.