Where Are Macros Stored In Excel? How To Find Them -

If you are relatively new to recording macros in Excel you may be wondering where they are actually stored within Excel and how to retrieve them. Additionally, you may have also had the experience of recording a macro and then finding the macro has disappeared and you may be wondering why. This article will give you all the answers on that sort of thing.

When you record a macro in Excel it is stored in the file that you created the macro in. The code which is created when you record a macro is stored in an area called the VBA editor which can be accessed either by the shortcut key ALT + F8 or through the developer tab which usually is not displayed on the ribbon unless you customize it.

The ribbon can be customized by selecting File on the excel’s ribbon (top left hand corner of the screen). Then select options which is in the bottom left hand corner of the screen. Select customize ribbon and click the checkbox for the developer tab.

Hitting the ALT + F8 hotkey or selecting the macros on the developers tab will produce a list of the macros that have been recorded for that particular file. To access the code that has been produced during the recording of a macro you need to select the name of the macro created and click the step into button which will take you into the VBA editor itself.

When you go into the Visual Basic editor you will see a series of modules. The number that appears will be dependent on how frequently you have recorded macros. If you are recording macros on the same day a new module will be created automatically that contains all the macros recorder on that particular day. If macros are recorded on different days new modules will be created for each day. An example of what the VBA editor looks like is shown below.

Why Has My Macro Disappeared?

As mentioned at the start of the article some people that are in experience with using recorded macros may have the situation where they create a macro in a particular file and then save file and I open it sometime later only to find that the macro is missing.

The reason that this can occur is due to the file type that the document is saved as. The default setting for saving an Excel spreadsheet has the file ending in the file extension .XLSX. This particular file extension does not support the creation of macros in the file.

If you select .XLSX as the file extension when it has recorded macros in it Excel will give you a warning that any VB code in the document will be lost if saved as that file format. At this point if you ignore that warning and click save you will lose the macro that has been recorded.

To overcome this problem the file type that needs to be selected is Excel Macro-Enabled Workbook which is shown in the screenshot the below.

Why Do The Macros In An Excel File Not Work?

The other common issue that people have with recorded macros is when they get a file sent from someone else with macros which don’t work. The main reason that this happens is that the default Excel is to disable macros for files coming from an unknown author.

This is done because some macros can be dangerous because they are capable of doing all sorts of things such as deleting files or sending emails on your behalf. To enable the macros in these circumstances you need to select the developer tab and then the macro security. Once this is done the next step is to select enable VBA macros.

However, it is important to note that in many cases even when you check the enable macros button the fire will not immediately work. To overcome this problem close and then reopen the file after doing the change. This will normally rectify the problem.

Can You Transfer Recorded Macros To Another Workbook?

It is possible to take recorded macros from one workbook and use them in another workbook. The easiest way to do this is to copy the code for the macro from one worksheet and paste it into another. This will mean that the macro will appear in the list of macros for that workbook and can be triggered in exactly the same way as a macro created within the book itself.

As mentioned earlier in the article the code can be accessed by going into the VBA editor and into the module where the code is contained. If you have many macros in the file and you are not sure which code is for which the easiest way to identify is to look at the first line of code in each program.

The first line of the code will be Sub and then the name of the macro. An example of this is shown in the image below where the first line of a macro is highlighted in yellow. To successfully copy that macro into another worksheet you need to copy all of the code down until the last line of code which will be End Sub.

In the example below you will note that there is more than one macro in the module. When this occurs there will be a line separating each macro which makes it easy to identify where it starts and where it ends.

This code can then be pasted into the new file, however it’s important to note that if there are no other macros in the file it may not show any modules being present in the VBA editor. To insert one click the insert button and then select module. Once you have created the new area for the code then it can be posted in turn the file ready to use.