Excel VBA: Set Macro Security

Date:2020-10-30 Author:Sandra

Under normal circumstances, we will not open the VBA code of a workbook whose source is unknown. Because this is likely to cause computer poisoning. So by default, Excel will disable the running of macros. But we hope to be able to run the VBA code written by ourselves, or other VBA code we consider it safe. Excel provides a variety of security options for this, which can effectively prevent the code from running automatically and run our own code correctly.

Excel VBA: Set Macro Security

Macro Securiy

Excel provides 4 kinds of macro security options. On the Developer tab, just click the “Macro Security” button to open the Trust Center. The 4 options are as follows.

Excel VBA: Set Macro Security
  • Disable all macros without notification: VBA code in the open workbook cannot be run, and Excel will not prompt that the workbook contains code.
  • Disable all macros with notification: By default, VBA code cannot be run. However, when Excel opens a workbook containing VBA code, a security warning is displayed above the edit bar, and you can choose to enable or disable code execution. If you choose to enable, the next time you open the same workbook, there will be no warning.
  • Disable all macros except digitally signed macros: Macros will be disabled, but if there are macros, a security warning will be displayed. However, if a trusted publisher digitally signs the macro, and you already trust the publisher, you can run the macro. If you have not yet trusted the publisher, you will be notified to enable the signed macro and trust the publisher.
  • Enable all macros (not recommended; potentially dangerous code may be run): All macros can be run. This option is generally not recommended.

Macro Security Suggestion

In the actual Excel VBA development process, we not only want to prohibit code from unknown sources, but also hope that our own code does not prompt security warnings. So the author suggests that you check the second option.

Excel VBA: Set Macro Security

Set Trusted Locations             

After adding a folder to the trusted location of Excel, when the workbook containing VBA code under the folder is opened, no security warning will be prompted, and there is no need to manually open the code every time.

The picture below illustrates the setting method.

Excel VBA: Set Macro Security
Copyright Statement: Regarding all of the posts by this website, any copy or use shall get the written permission or authorization from Myofficetricks.

Leave a Reply

Your email address will not be published. Required fields are marked *