Excel includes VBA which is known for its powerful programming language that is used to develop all sorts of macros.  As you are in the process of developing macros, before that you need to make the whole of it change to your macros. It can cause some tooting problems in your worksheets.

But as the changes move forward like adding and removing code then automatically the actual file used to store the macros that is the workbook will get a bit fragmented.

It may seem that internally all the macros are stored in blocks but sometimes the blocks can become “non-contiguous” over time. There are times when the fragmentation can turn worse and the macros either fail or the workbook can become unusable. The one and only solution to this is to do your macro development in a different workbook. So when you transfer the macro to its final home, it will be transferred as a contiguous block, and not as fragmented.

If you want to make sure that all the macro fragmentation is completely removed from an existing workbook then you need to do is transfer your VBA modules to text files. For this generate a brand new workbook, and then introduce the modules into it.

The following are the steps to create a macro by recording a macro.

Step 1:

First you need to set the security to medium or low. The steps are

On the Tools menu, click on Options and then Click on the Security tab.

Now, under Macro Security, click on Macro Security and then Click on the Security Level tab and then select the security level you want to use.

Step 2:

On the Tools menu, point your cursor to Macro, and then click Record New Macro.

Step 3:

In the Macro name box, you need to enter a name for the macro.


Step A

The first character of the macro name needs to be a letter. The Other characters can be letters, numbers, or underscore characters. Spaces are not permissible in a macro name and as a word separator you can use underscore character.

Step B

Does not use a macro name that is also a cell reference or else you can get an error message that the macro name is not valid.

Step 4:

If you want to run the macro by pressing a keyboard shortcut key, enter a letter in the Shortcut key box for example You can use CTRL+ letter (for lowercase letters) or CTRL+SHIFT+ letter (for uppercase letters), where letter is any letter key on the keyboard. The shortcut key letter you use cannot be a number or special character such as @ or #or anything as such.

Step 5:

In the Store macro in box, click on the location where you want to store the macro. If you want a macro to be available whenever you use Excel, then select Personal Macro Workbook.

Step 6:

If you want to include a description of the macro, type it in the Description box and write the description and Click OK.

Step 7:

If you want the macro to run relative to the position of the active cell, record it using relative cell references.

On the Stop Recording toolbar, you will have to click on the Relative Reference. Excel will keep on continuing it to record macros with relative references until you quit excel or until you click Relative Reference again, so that it is not selected.


Carry out the actions that you want to record. To stop recording, On the Stop Recording toolbar, click Stop Recording.