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.

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

9 comments

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

  • 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 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.

  • 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.
    Pierre

  • I have a doubt!
    You can create macro for Excel that protects a range (5 columns) conditioned to multiple users.
    Example:
    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).

  • 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

  • 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

Search
Generic filters
Exact matches only
Filter by Custom Post Type

JOIN US & LEARN EXCEL

Learn 10 great Excel techniques that will wow your boss and make your co-workers say, "how did you do that??"
Plus weekly updates to help you learn Excel.

Download the eBook

About Me

Jon Acampora Profile

Hello and welcome! My name is Jon Acampora and I'm here to help you learn Excel.

This blog is updated frequently with Excel and VBA tutorials & tools to help improve your Excel skills and save time with your everyday tasks. Subscribe above to stay updated. More about me...

MVP_Horizontal_BlueOnly