Ravindra BagaleCourses & study guides

12. Macros and VBA

12.1 Developer Tab, Macro Security and .xlsm

By Last updated:

Steps in Excel

  1. Show the Developer tab: File › Options › Customize Ribbon › in the right list tick Developer › OK. Groups: Code (Visual Basic, Macros, Record Macro, Use Relative References, Macro Security), Add-ins, Controls, XML.
  2. Macro security: Developer › Code › Macro Security (or File › Options › Trust Center › Trust Center Settings… › Macro Settings). Options:
    • Disable VBA macros without notification
    • Disable VBA macros with notification – recommended/default: a yellow bar offers Enable Content for files you trust
    • Disable VBA macros except digitally signed macros
    • Enable VBA macros – not recommended
  3. Trusted Locations (same dialog): add a folder such as D:\ExcelMacros\ – files there open with macros enabled.
  4. Save with macros: File › Save As › Save as type: Excel Macro-Enabled Workbook (*.xlsm). Saving as .xlsx deletes the code (Excel warns you).
  5. A downloaded .xlsm shows a red Security Risk bar: if you trust it, close it, right-click the file in File Explorer › Properties › tick Unblock › OK.
Format Macros? Use
.xlsx No Normal workbooks
.xlsm Yes Workbooks with macros
.xlsb Yes Binary – smaller/faster for very large files
.xlam Yes Add-ins (functions available in all workbooks)

Ravindra Bagale's Tip

Writing a macro and saving it as .xlsx is the most painful mistake many students make, because if you click "Yes" on Excel's warning, all the code is gone! Save the file as .xlsm from your very first macro. And don't set security to "Enable all macros" – Disable with notification is the safe option.

Practice task

Turn on the Developer tab, set a Trusted Location for your practice folder, and save a copy of Blinkit_Maharashtra as Blinkit_Macros.xlsm.