Bottom line: Learn 5 ways to use VBA macros to automate Excel tasks you do frequently at work.
Skill level: Beginner
Download the Excel File
Download the Excel file to follow along with the video.
5 Uses For Macros And VBA.xlsm (1.2 MB)
What Can I Use Macros For in My Job?
This is a question I hear often. You might have heard you need to learn macros, but you're not exactly sure how they will help you. In the video above I explain 5 different ways to use macros to automate tasks in Excel.
We can use VBA macros to automate everything from simple tasks to complex reporting processes with multiple files. Here are the 5 topics covered in the video, along with the links mentioned to other articles and resources.
1. Data Related Tasks
Data related tasks include everyday Excel tasks to cleanup and format data. Examples covered in the video include.
- Remove Duplicates – Create a List of Unique Values
- Select or Delete Blank Rows
- Create formulas with macros – Percentage Change Formula
- Find the Last Used cell, row, or column in a sheet
- Apply formatting to raw data exports – Format Copier
2. Workbook Tasks
We can also automate tasks across worksheets and workbooks. This includes things like listing all the sheets, creating a table of contents, hiding specific sheets, opening and closing workbooks, and more. Here are some examples covered in the video.
3. Pivot Table Tasks
Pivot tables are an amazing tool in Excel. They can also be time consuming to create, format, update, and maintain. Fortunately, we can automate just about every property and action with pivot tables using VBA macros. Here are a few examples from the video.
- Show Details Macro
- Automatic Default Number Formatting in Excel Pivot Tables
- Expand and Collapse Entire Pivot Table Fields
- How to Change Date Formatting for Grouped Pivot Table Fields
- The PivotPal Add-in – The entire add-in was built with VBA, and automates many repetitive pivot table tasks.
4. Userforms & Add-ins
Userforms are windows that open over Excel with interactive controls. They make our spreadsheets easier for other users to use. The userforms can be created and modified in the VB Editor.
Add-ins are installed on the users computer and usually contain custom ribbon tabs with buttons. This allows other users to run our macros on any open workbook. Checkout the Excel Campus Add-ins page for examples.
5. Process Automation
We can also automate more complex processes with macros & VBA. This includes creating systems to create, update, or modify several Excel files. I created many systems like this for forecasting and budgeting processes. Here is a link to an overview video of an application I call the File Manager. The File Manager allows you to run multiple macros on sets of files.
I explain how to create and use the File Manager step-by-step in module 11 of The VBA Pro Course.
Free Webinar on Macros & VBA
If you are interested in learning more about macros, I'm currently running my free webinar called “The 7 Steps to Getting Started with Macros & VBA”. It's running all this week, and it's absolutely free to register.
During the webinar I explain why you might want to learn VBA, and a lot of the basic coding concepts that will help you get started. I jump into Excel and the VB Editor and walk through how to write and run our first macro. Even if you have been using VBA for awhile, I'm sure you will learn some new tips.
What Else Can We Use Macros For?
We can use macros for just about every task we do in Excel. This video doesn't even scratch the surface of what's possible, but hopefully it gets you thinking about some repetitive Excel tasks that you might want to automate.
Please leave a comment below with any questions or suggestions. Thank you! 🙂