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

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Access Visual Basic Tool


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

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.

Saving Macros in Excel Permanently


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.

Record a Macro to Save Macros in Excel Permanently

  • 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.

Create your Own Macro to Save Macros in Excel Permanently

  • 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.

Saving Macro in Excel permanently

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

Accessing 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.

Write Desired Code in Visual Basic Editor. to Save Macro in Excel Permanently

  • 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.

Access the Available Macros

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

Choose the Desired Macro from the Available Macros

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

Running the Macro at Active Cell

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

Running Macro to All the Desired Cell

Read More: How to Edit Macros in Excel


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.

Access the Visual Basic Tool to Save Macros in Excel Permanently

  • 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.

Insert a New Module to Save Macros in Excel Permanently

  • 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.

Write Required Code in Module

  • Now, click on the File tab.

Access File Tab

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

Access the Save As Option

  • 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.

Save the Excel as .xlam Format

  • 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

Access Excel 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.

Insert Saved Add-ins

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

Install Required Add-in

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. 👇

Running the Code in All Cells

Read More: Excel VBA to Pause and Resume Macro


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.


Download Practice Workbook

You can download and practice from our workbook 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.


Related Articles

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjim Reza
Tanjim Reza

Md. Tanjim Reza Tanim, a BUET graduate in Naval Architecture & Marine Engineering, contributed over one and a half years to the ExcelDemy project. As an Excel & VBA Content Developer, he authored 100+ articles and, as Team Leader, reviewed 150+ articles. Tanim, leading research, ensures top-notch content on MS Excel features, formulas, solutions, tips, and tricks. His expertise spans Microsoft Office Suites, Automating Finance Templates, VBA, Python, and Developing Excel Applications, showcasing a multifaceted commitment to the... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo