Visual Basic for Applications in short VBA is the programming language related to Microsoft Excel. Sometimes this feature remains turned off for a workbook. In order to have the macro Excel workbook, there are some easy ways that we can follow. I am going to explain how you can enable an Excel workbook with macros in five simple and easy ways. I hope it will be helpful for Excel users.
Below, I have attached an overview image of the Excel macro enabled workbook.
Download Practice Workbook
Introduction of Excel Macro
A Macro is a set of instructions that can be used to automate a task or series of tasks. Macro Programming in Visual Basic for Applications is used in Microsoft Office applications (VBA). You can automate numerous operations, including document formatting, computations, and the insertion of text or images, using macros. Other programs, including Microsoft Word or PowerPoint, can also be controlled by macros.
Excel Macro Enabled Workbook Extension
In terms of working with Excel, we use the file extension .xlsx. If you save the file with this extension, the file won’t support Macro which is a programming language. Macro-enabled workbooks have the file extension .xlsm. You need to change your file extension from .xlsx to .xlsm if you want to enable macro in a file.
Excel Macro Enabled Workbook: 5 Simple Ways to Open
Now, I will explain how to enable the macro. Also, how to open an enabled macro workbook. The following methods will vary with different versions of Excel. So, use the exact one for your Excel version.
1. Changing File Extension to Enable Macro
We often save our Excel files in .xlsx mode. If the file is saved in this extension, Macro won’t work. So, changing the extension to .xlsm is a possible way to enable Macro in Excel workbook. In order to change the extension, you need to follow the following procedure.
- First, go to the File tab >> select the Save As option >> choose your file location >> pick Excel Macro-Enabled Workbook (*.xlsm) from the Save As Type option.
This will enable Macro in the Excel Workbook. Any Macros that were previously set, will work just fine. Furthermore, we can even write additional VBA code in the module.
2. Changing File Property to Enable Macro in Excel Workbook
In the recent security update process, a shared Excel file with Macro needs some property modification to enable Macro. This might be a reason that disables the Macro in Excel workbook.
- Right-Click on the mouse putting the cursor on the Excel file >> go to the Properties option.
- From the General tab, check the Unblock box >> press on Apply >> click on OK to finish the process.
This might be another way to enable the Macro in Excel workbook. This will work for the recent version of Excel. Still, you get a security warning from Excel about Macros have been disabled.
- Then, besides that warning, click on Enable Content. After clicking on Enable Content, the Macros will work fine.
3. Enable Macros in Trusted Locations – Permanently
When you trust the source Excel file, and there will be no harm with that file, then you can permanently enable the Macros files.
Basically, I’m going to create a folder where Excel files with Macros will be auto enabled.
A dialog box of Excel Options will appear.
- Next, click on Trust Center >> go to Trust Center Settings…
Another dialog box named Trust Center will come forward.
- Now, click on Trusted Locations >> then select Add new location.
As a result, you will get another dialog box named Microsoft Office Trusted Location.
- Choose your preferable folder >> check Subfolders of this location are also trusted >> press OK.
- After that, press OK on the Trust Center dialog box.
- Then, click OK on the Excel Options dialog box.
Now, move any Excel file with Macros to that location and get that workbook enabled.
4. Using Security Warning Bar to Enable Macros for Excel 2010, 2013, and 2016
In the case of Excel 2010,2013, and 2016 versions, you can use this method.
- After opening an Excel file with Macro >> you will get a security warning from Excel.
The warning will say that Macros have been disabled.
- Besides that warning, you will be able to click on Enable Content.
- After clicking on Enable Content, the Macros will work fine.
5. Enabling Macros from Trust Center- Permanently
We can try checking the Enable VBA Macros option from the Trust Center to enable Macros in Excel Workbook. This is another very simple way to do so. Here, I need to open the Trust Center dialog box. Actually, I explained how to open the Trust Center dialog box in method 3.
- So, click on the File tab first >> select Options.
- From Excel Options >> click on Trust Center >> go to Trust Center Settings…
The dialog box of Trust Center will come forward.
- After that, click on Macro Settings >> from the available option >> check Enable VBA macros (not recommended; potentially dangerous code can run) box >> click OK.
Then you will get the Excel Options box again.
- Click OK on that to finish the process.
I hope this will enable macro in the Excel workbook.
Note: This will work for Excel 2010-2016 version.
How to Disable Macros in Excel
You can enable an Excel file with Macro at any time with these methods explained earlier. Now, if you need to disable the Macros file then you can do that too.
For Microsoft Excel 365, the built-in process is keeping the Macro file disabled. But for other versions, you may need to manually disable the Macros file.
- To do so, click on the File tab first >> select Options.
- Then, from the Excel Options dialog box >> click on Trust Center >> go to Trust Center Settings…
So, the dialog box named Trust Center will appear.
- Now, click on Macro Settings >> from the available option >> check Disable VBA macros with notification box >> click OK.
So, this is how you can disable all the upcoming macro files.
How to Get Hidden Macros in Excel
Sometimes you don’t get the Macros. But you can find them easily. Below, I will show the steps.
- From the Developer tab >> go to Visual Basic.
As a result, a window named Microsoft Visual Basic for Applications will appear.
- Now, in that window, from Top Ribbon >> go to View tab >> select Project Explorer.
Then, you will get all the Macros on the left side of that window.
Frequently Asked Questions
1. What is the difference between an Excel workbook and Excel macro-enabled workbook?
Answer: An Excel workbook cannot contain any macro. The file extension is .xlsx. Basically, this is the basic type of Excel file that may have formulas, functions, and formatting.
But an Excel macro-enabled workbook can contain macros and the file extension is .xlsm. You can automate many tasks using the VBA code of this Excel macro-enabled workbook.
2. Is it safe to enable macros in Excel?
Answer: No, all macros are not safe. If you enable a macro from an untrusted source then that code can damage your files. So, only enable these macros which are from trusted sources.
In the above section of this article, I have tried to explain the Excel macro enabled workbook in five simple ways. It would be a matter of great pleasure for me if this article could help any Excel user even a little. For any further queries, comment below. You can visit our site Exceldemy, for more articles about using Excel.