5 Uses for VBA Macros in Excel with Your Job

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.

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.

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.

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.

Tab Hound Quickly Search and Navigate to any sheet in the workbook

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.

File Manager Automate Processes on Excel 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.

Macros and VBA Webinar Banner - Join Me - 550x200

Click here to learn more and register for the webinar

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! 🙂


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

  • Hi Jon,

    I want to know how to properly do a SUMPRODUCT function in VBA.

    I know of 2 methods, the Evaluate function and the other is using .Formula which returns the the formula of SUMPRODUCT in the target range.

    Is there any other way i.e., simpler and easier to do?

    Thank you.



  • Hi Jon,

    I was wondering if you have videos to your seminar because I missed it? I always loved Excel and now I want to learn to code the VBA so I can make my sheets better for work.

    Thanks Al,
    P.S. I have some knowledge in Programming

  • I have a doubt!
    You can create macro for Excel that protects a range (5 columns) conditioned to multiple users.
    User A has filled certain columns (A, B, C, D, E) –
    The macro authorizes its completion because user “A” is allowed!
    Already the user “X” can not make his name is not on the list of authorizations and is not allowed to change the cells of this range (columns).

  • Dear Jon,
    Unfortunately, I couldn’t participate to your webinar about Marroc in July/Agust…
    Is there a way to have access to some kind of replay of those sessions?
    Thanks in advance.

  • Hello
    I Have a question. Is it possible to use VBA to pay bills? I mean have a code to ba able to read bill ID and payment ID and pay the bill for factories? As you know factories have alot of bills for electricity or others.

  • Hi, I am a beginner to excel n vba. I want to learn perfectly.how to understand n remember vba code? Give me a suggestion Please…
    Also having excel functions also y do we need vba?

  • Hello,

    I just would like to thank you for sharing this. Sometimes we forgot the work of people who gave some very insightful advices for free. I know this is a big work.

    Thanks for that.

  • Hi, I love the table of contents with thumbnail view, I was wondering if you can design this so the thumbnail views sit under predefined (a fixed number and fixed names) sections (like chapters of a book) and have a button at the end of each section on the contents which allows another sheet to be added automatically under that section that prompts the user to elect a copy of a pre existing worksheet so the formatting is maintained throughout the workbook. We would also like our branding theme throughout (white labelled).
    Additionally, have a table of a section jump to (hyperlink buttons) at the top of the contents page to navigate between its sections.
    Each Worksheet would also need a button to take the user back to the contents.
    My view is we would start off with one template worksheet for each section and a button to add another.
    The user could then build out the Workbook from there.
    We would also like to have the flexibility to shrink the the thumbnails so the contents is easier to view.
    Is this possible? How would one have this created?

    The final item in the Wishlist would be if you could create an external Weblink not only to the Workbook (which would be saved in Office 365) but to a place within the workbook so weblinks could be given to others which would take them straight to the Worksheet relevant to them. However I’m told this is not possible!

Generic filters
Exact matches only

Excel Shortcuts List

keyboard shortcuts list banner

Learn over 270 Excel keyboard & mouse shortcuts for Windows & Mac.

Excel Shortcuts List

Join Our Weekly Newsletter

The Excel Pro Tips Newsletter is packed with tips & techniques to help you master Excel.

Join Our Free Newsletter