2

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

Watch on YouTube and give it a thumbs up.YouTube Thumbs Up LikeYouTube Subscribe Logo Excel Campus

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.

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

Please share
Jon Acampora
 

Welcome to Excel Campus! I am excited you are here. My name is Jon and my goal is to help you learn Excel to save time with your job and advance in your career. I've been an avid Excel user and VBA developer for 10+ years. I am also a Microsoft MVP. I try to learn something new everyday, and want to share this knowledge with you to help you improve your skills. When I'm not looking at spreadsheets, I get outdoors and surf. :) more about me

Click Here to Leave a Comment Below 2 comments
Shyam - November 5, 2018

Hi,

I just want to know how we may use macro for doing V look up in Excel.

Thanks,
Shyam

Reply
ryan - April 23, 2018

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.

Regards,

Ryan

Reply

Leave a Reply: