How to Save Macros in Excel Permanently (2 Suitable Ways)

Excel We sometimes need to perform the same thing or use the same formula in our Excel workbooks. In this regard, we can automate our work by enabling macros. By enabling macros, we will have to write a code once. And, the great thing is that we can use the code as many times as we want if we can save the macro in Excel forever. It would save our time and energy. In this article, I will show you 2 suitable ways to save macros in Excel permanently.

Table of Contents hide

Download Practice Workbook

What Is a Macro?

2 Suitable Methods to Save Macros in Excel Permanently

1. Save Macros Permanently as a Part of All Excel Workbooks Using XLSB Format

2. Save Macros Permanently as XLAM Format

Things to Remember

Conclusion

Download Practice Workbook

You can download and practice from our workbook here.

Save Macros Forever.xlsm

What Is a Macro?

Macro is a set of instructions written in VBA ( Visual Basic for Applications) code. You can fix a guideline for a definite performance or formula or calculation using macro. The advantage of a macro is you can perform a series of actions with a single click as many times as you want. You have to write the VBA code in the Visual Basic Editor. You can find it in the Developer tab >> Visual Basic section.

2 Suitable Methods to Save Macros in Excel Permanently

Suppose, we have an Excel file, where we have to put the copyright sign frequently. So, we can write a VBA code in the macro for copyright signs and use it frequently wherever we need it. If we want to use it in every workbook, we need to save it permanently. Follow any of the two ways described below to save macro permanently in Excel.

1. Save Macros Permanently as a Part of All Excel Workbooks Using XLSB Format

You can save a macro permanently in Excel using the .xlsb format. Follow the steps below to accomplish this. 👇

📌Steps:

First, go to the Developer tab. Subsequently, click on the Record Macro tool.

As a result, the Record Macro window will open. Now, write the macro name in the Macro name: text box. Subsequently, choose the Personal Macro Workbook option from the Store macro in: dropdown list. Finally, click on the OK button. Besides, you can also declare a shortcut for running the macro.

Now, close the workbook. A Microsoft Excel warning dialogue box will pop up. Subsequently, click on the Save button. Thus, you have saved your macro permanently.

Now, your personal macro file is ready, but there is no code inside it yet. For doing this, open the workbook again and go to the Developer tab. Subsequently, click on the Visual Basic tool.

Now, at the project window, you can see there is a project named PERSONAL.XLSB. Double-click on the project and choose Module 1 to write the code. Subsequently, write the code in the visual basic editor.

Subsequently, close and save the Excel file to save the code in the macro.
Now, open the workbook again and go to the Developer tab. Subsequently, click on the Macros tool.

As a result, The Macro window will appear now. Subsequently, choose the macro and click on the Run button.

As a result, the code will run and put the copyright sign in the active cell.

Finally, you can use this permanent macro in an Excel workbook and run it at any cell you want.

Similar Readings

2. Save Macros Permanently as XLAM Format

Besides, you can save your macros permanently in Excel using the .xlam format. Follow the steps below to do this. 👇

📌Steps:

First, go to the Developer tab. Next click on the Visual Basic tool.

As a result, the Microsoft Visual Basic for Applications window will open. Now, click on the Insert tab. Subsequently, choose the Module option from the dropdown list.

As a result, a new module named Module 1 under the Modules group is created. Click on Module 1 and subsequently, write the code in the VBA editor.

Now, click on the File tab.

Subsequently, click on the Save As option from the expanded File tab.

At this time, The Save As window will appear. Choose the .xlam format as the save as type from the Save as type: options. Next, click on the Save button.

Now, your macro is saved permanently as an add-in in Excel. Now, for checking, open any workbook and go to the File tab >> click on More… >> click on Options

The Excel Options window will open now. Choose the Add-ins from the tab list on the left side. Choose your saved add-in from the add-in list. Click on the Go button.

As a result, the Add-ins window will open. Choose your add-in and click on the OK button.

Now, finally, your saved add-in is installed in the current workbook and you can put the copyright sign using the code at any cell you want in any workbook just like the 6th,7th, and 8th steps of the first method. And, after running the code in several cells, the result will look like this. 👇

Things to Remember

To enable macro in an Excel file, it is a must to save the Excel file in .xlsm format. If you do not save Excel in this format, the macro will not work. And another thing is saving in this format will enable the macro in the current workbook only. If you want to save the macro permanently and use it in every workbook, you will have to follow any of the 2 ways described here.

Conclusion

So, in this article, I have shown you 3 suitable ways to save macros permanently in Excel in brief. Use any of these effective methods to accomplish your result in this regard. I hope you find this article helpful and informative. If you have any further queries or recommendations, please feel free to contact me. And, visit ExcelDemy for many more articles like this. Thank you!