How to save VBA code in Excel 

By SpreadCheaters
Page last updated: 28/06/2023 | Next review date: 28/06/2025
close

Author bio


SpreadCheaters
View More north_east

VBA (Visual Basic for Applications) code should be saved whenever you have made changes or additions to the code that you want to preserve for future use. Saving your VBA code frequently helps protect your work and ensures that you have backup copies in case of unexpected events or computer crashes. Before executing any VBA code, it’s advisable to save it. This way, you can revert to a known working state if something goes wrong during the execution or testing. 

The VBA code given below automates the process of generating an employee table in Excel, updating payment statuses, and sending email notifications. It sets up the worksheet, populates the table using predefined employee details, and utilizes Microsoft Outlook to send emails when payments are released. The code sends personalized emails to employees, updates the payment status in the worksheet, and provides a notification to the user. By combining these functionalities, the code streamlines employee management, payment tracking, and communication, making it a valuable tool for enhancing efficiency in HR departments or similar scenarios. 

There are two simple methods to do it: 

Method 1 – Saving VBA code by exporting the Module 

Step 1 – Opening the Visual Basic Editor 

  • Open the Excel workbook in which you want to access the Visual Basic Editor. 
  • Press “Alt+F11” on your keyboard and it will open the Visual Basic Editor.  
  • Alternatively, you can go to the “Developer” tab (if it’s not visible, enable it from Excel options), and click on the “Visual Basic” button in the Code group. 
  • The Visual Basic Editor window will open, displaying the Project Explorer on the left side and the code editing area in the main window. 

Step 2 – Saving the VBA code as a .bas file 

  • In the Project Explorer window on the left side, locate the module or object that contains the VBA code you want to save as a .bas file. 
  • Right-click on the module or object and select “Export File” from the context menu. 
  • Choose a location on your computer where you want to save the .bas file. 
  • Enter a file name for the .bas file and make sure to include the “.bas” extension. 
  • Click “Save” to save the VBA code as a .bas file. 

Method 2 – Saving VBA code with Excel workbook 

Step 1 – Opening the File tab 

  • Open the Excel workbook that contains the VBA code you want to save. 
  • Click on the “File” tab in the Excel ribbon at the top left corner of the window. 

Step 2 – Saving the Excel file in .xlsm format 

  • After following the above step, select “Save As” or “Save As Another File Type” from the menu on the left-hand side. 
  • In the “Save As” dialog box, click on the “Browse” option.  
  • Then, navigate to the desired location on your computer where you want to save the Excel file containing the VBA code. 
  • Enter a file name for the workbook, and make sure to include the “.xlsm” extension at the end of the file name (e.g., VBAsaveworkbook.xlsm). 
  • Below the file name, there is a “Save as type” drop-down menu. Click on it and select “Excel Macro-Enabled Workbook (*.xlsm)” from the list of available file formats. 
  • Click the “Save” button to save the Excel file with the .xlsm extension and it would automatically save your VBA code within this file.