Introduction
In our last lesson, we mastered Goal Seek and Solver, powerful tools for working backward to find the inputs needed to achieve specific financial outcomes. While incredibly useful, these tools still require manual setup each time you want to solve a problem. As you build more sophisticated solutions for clients, you'll want to automate repetitive processes entirely.
This lesson marks our entry into the world of automation with Macros and Visual Basic for Applications (VBA). Macros are recorded sequences of actions, and VBA is the programming language behind them that gives you ultimate control over Excel. Before we can start automating, we need to prepare our Excel environment. This foundational lesson will guide you through two essential one-time setup steps: enabling the hidden Developer tab, which is your command center for all things VBA, and understanding Excel's macro security settings to keep your work and your computer safe.
1. Unlocking Your Toolkit: Enabling the Developer Tab
By default, Excel hides the tools for creating macros to prevent new users from accidentally running powerful code. Our first step is to reveal this toolset by enabling the Developer tab on the ribbon. This tab consolidates all the key features you'll need, including the macro recorder and the Visual Basic Editor (VBE), where you will eventually write your own code.
The following video provides a quick demonstration of how to enable this tab.
Excel Macros & VBA - Tutorial for Beginners
This clip from the "Excel Macros & VBA - Tutorial for Beginners" by Kevin Stratvert shows you exactly where to find the setting to turn on the Developer tab.
Please watch from this section. The video demonstrates right-clicking the ribbon to access the customization options. Follow along to enable the tab in your own Excel application.
As you saw in the video, the process involves going into Excel's options and simply checking a box.
Once you click OK, you will see the "Developer" tab appear in your Excel ribbon. Take a moment to click on it and look at the groups of tools available. In the "Code" group on the far left, you will see buttons for "Visual Basic," "Macros," and "Record Macro." These will be our primary tools in the upcoming lessons.
2. Working Safely: Understanding Macro Security
Macros are powerful. They can automate formatting, calculations, and file operations, but because they are code, they can also be used for malicious purposes. If you open an Excel file from an untrusted source, a macro could potentially harm your computer. Because of this risk, Excel has a built-in security system to manage how macros are run.
As a future freelancer, understanding these settings is non-negotiable. You will be sending and receiving files from clients, and you must know how to do so safely.
The .xlsm File Type and the Security Warning
When you save a workbook that contains macros, you cannot save it as a standard .xlsx file. You must choose the "Excel Macro-Enabled Workbook" format, which has the .xlsm file extension. This special extension signals to Excel and to users that the file contains code.
The video below explains this process and shows the security warning you'll see when opening an .xlsm file for the first time.
Excel Macros & VBA - Tutorial for Beginners
This clip, also from Kevin Stratvert's tutorial, demonstrates saving a macro-enabled workbook and the security prompt that appears upon opening it.
Watch the segment from on saving and opening. Note the choice of the .xlsm file type and the yellow security bar that appears.
Configuring Security Settings in the Trust Center
Excel's default security setting is "Disable VBA macros with notification." This is what causes the yellow security bar to appear, giving you the choice to "Enable Content." This is generally the safest and most practical setting for everyday use. It forces you to make a conscious decision before running any code.
You can view and change these settings in the Trust Center. Let's review the available options.
This article from GeeksForGeeks clearly outlines the different macro security levels available in the Trust Center.
Please read the section under 2. Enabling Macros on Windows. Focus on the four options described in "Configuring Macro Settings" and the security advice at the end. This will give you a clear framework for the different levels of protection.
The four security levels give you a range of control:
- Disable all macros without notification: The most restrictive setting. Macros won't run, and you won't be told they exist.
- Disable all macros with notification: The default and recommended setting. You are notified and can choose to enable macros for files you trust.
- Disable all macros except digitally signed: An advanced option for corporate environments where macros are approved with a digital signature.
- Enable all macros (not recommended): The least secure option. All macros run automatically. You should avoid this setting, as it exposes you to significant risk from malicious files.
A Professional Workflow: Trusted Locations
Continuously clicking "Enable Content" for your own project files can become tedious. For files you create and trust completely, you can use a feature called Trusted Locations. By designating a specific folder on your computer as a trusted location, you tell Excel that any .xlsm file saved there should be opened with macros enabled automatically, bypassing the security warning.
This is an excellent practice for your own development work. You can create a dedicated folder for your Excel projects and add it to your trusted locations.
The following video provides a detailed walkthrough of macro security and demonstrates how to set up a trusted location.
Excel 2021 VBA Beginner Tutorial
This clip from the "Excel 2021 VBA Beginner Tutorial" by Learn Skills Daily offers a thorough explanation of security risks and the practical use of trusted locations.
Please watch the section from on security and trusted locations. Pay close attention to the reasoning behind macro security and follow the steps to navigate to the Trust Center and add a trusted location.
Conclusion
Congratulations on taking your first step into Excel automation! While this lesson didn't involve writing any code, you have now correctly configured your environment, which is a crucial prerequisite for all the macro and VBA work that will follow.
Key Takeaways:
- The Developer tab is your central hub for creating and managing macros and VBA code; it must be manually enabled from Excel's options.
- Workbooks containing macros must be saved in the Excel Macro-Enabled Workbook (
.xlsm) format. - Excel's macro security features are designed to protect you from potentially malicious code.
- The safest and most practical security setting is "Disable VBA macros with notification," which lets you enable content on a case-by-case basis.
- For your own projects, setting up a Trusted Location provides an efficient and secure workflow, automatically enabling macros for files stored in a specific folder you trust.
Now that your workshop is set up and the safety goggles are on, you are ready for the exciting part. In our next lesson, you will create your very first macro by recording a sequence of actions, running it to see it in action, and taking a first look at the VBA code it generates automatically.