Leila Gharani

Imagine you could teleport from New York City to Sydney, Australia, in five seconds. Would you bother taking the traditional 22-hour flight? Of course you wouldn’t.

Imagine you could create a full Excel report with tables and charts in five seconds with one mouse click. Would you want to know how? Or would you rather craft the report by hand and use 22-hours of your life in the process?

If you answered “yes” to the five-second option, then VBA and Macros in Excel are just what you are looking for.

Think of a situation where you find yourself in Excel performing the same set of steps, over and over. This is a prime candidate for using a macro.

Imagine performing the following processes in Microsoft Excel with a single click:

Unlock Excel VBA and Excel Macros

Last Updated December 2020

Bestseller
  • 172 lectures
  • All Levels
4.7 (24,538)

Automate Complex Tasks with Microsoft Excel VBA & Excel Macros (Real-World Projects included). Achieve More. Save time. | By Leila Gharani

Explore Course

Imagine a process you wish could be more efficient. It probably can be with the use of VBA language and macro code.

VBA – Excel’s teleporter

So what is VBA in Excel? Visual Basic for Applications (or VBA) is a computer programming language subset of Visual Basic. Think of it as a “lite-version” of Visual Basic, or VB.

People use VB to create self-contained programs that run directly under the host operating system. You can use VBA to create processes and routines that run within one of the Office applications, like Excel.

VBA exists to automate tasks and processes that are normally performed long-form by a human being. These automations are VBA macros.

By implementing a macro, you can reduce the effort and time used to create an Excel report. This could be by 80%, 90%, or in some cases 99.9% if we count pressing a button as expended effort.

Creating a VBA macro

You can create Macros using two key methods: the Macro Recorder and hand-crafted VBA code. Often, you use both methods in the same project.

You can use the Macro Recorder to create a general framework for the major tasks. You can use hand-crafted code to include features that the Macro Recorder can’t make, like user input and process loops.

The Macro Recorder is an easy way for both beginners and VBA veterans to create macros. It’s not the most efficient way to write VBA code, though. It gets the job done, but not always in the most elegant way.

If you want to get the full power of macros, you need to enter into the world of VBA code. This can take a bit of time and practice, but it is an investment that leads to huge rewards.

Don’t let the above example intimidate you. The macro recorder can write most of the code for you.

Running a macro

There are several options available when running macros.

Each of these methods has its pros and cons. It’s best to learn as many of these as possible to use the best option in any given situation.

Relative and absolute macros

Excel cell references can behave in either relative or absolute mode. In other words, references that change when you repeat formulas versus references that remain fixed. VBA code can also be relatively or absolutely.

This is useful when you have two processes. One process needs to happen in the same place in a spreadsheet. The other process needs to happen in potentially different locations each time you run it.

Imagine a form heading that needs to be in the same cells at the top of a spreadsheet. You also have a legal disclaimer that has to be two rows below the last used row. You would create the heading as an absolute macro, and create the legal disclaimer as a relative macro.

Getting user input

VBA can get input from the user and include that input in the code during execution.

Instead of writing a macro that always creates a sales report for last year’s sales, you can prompt the user for a date range. By getting input, you can report on 10 days or 10 years of sales using the same macro. You don’t have to create a separate macro for every possible period you may wish to report against.

Imagine making a dropdown list that allows the user to select a department, a sales representative, or a product by which to report. Again, creating a report based on different criteria each time you make the report gives a lot of flexibility to a macro.

Fewer macros mean less production time, as well as ease of maintenance. They also mean fewer break-fixes.

These solicitations can be dialog boxes or user forms. Where an input box will collect information one question at a time…

…a user form can accept more than one piece of information and process all pieces against varying logic.

We can use a user form to collect information that we will store in an underlying table. You can use these tables to feed a variety of different reports.

Local versus global macros

A traditional Excel file stores your Local macros. They travel with the file if you send it via email attachments, cloud-based sharing, or old-school sneaker-nets.

These are ideal if you want to automate user processes for people who may not be as adept at Excel. Let people click a button and have the macro guide them through a process or perform the entire set of steps quickly and accurately.

Global macros are specific to your machine and act more like a built-in program feature. These macros are available no matter what file you are using.

Accessing the VBA code

You can view VBA code in the Visual Basic Editor. You can launch this editor with one of three methods:

·       Press the ALT F11 keyboard combination

·       Right-click any sheet tab and select “View Code

·       From the tabs/ribbons, select Developer Tab -> Code (group) -> Visual Basic

The VB Editor shows you several things. You can see a list of all open workbooks, all module sheets (these typically hold the VBA code), and a window to view the VBA code for a selected module.

Creating a simple macro

Let’s see how powerful a macro can be for saving time, as well as how easy they are to create. We will create a very simple macro that almost everyone can use.

Suppose we must type and customize a heading at the beginning of a spreadsheet.

This is something that we do several times per day. It is always the same in terms of content and appearance. We can record the process of creating the heading so it can be a single‑click, repeatable event.

1.       Open a blank workbook.

2.       Select View (tab) -> Macros (lower-part of button) -> Record Macro.

3.       Enter the macro name. The name is the only necessary part. You can use the image below as an example of other input that you can fill in.

4.       Click OK to start the recorder.

Select cell A1 of the current blank sheet. Create a heading of your choosing. This can include information like company name, address, telephone number, website address, and more.

Customize the fonts, size, colors, and alignments. You can do anything you normally do to the heading.

Once you have created and customized the heading, stop the recording process. Do this by selecting View (tab) -> Macros (lower-part of the button) -> Stop Recording.

Testing the macro

Now let’s test the macro’s performance and accuracy. Create a new blank sheet, then run the macro by selecting View (tab) -> Macros (upper-part of the button).

From the Macros dialog box, select the macro with the name you picked and click RUN.

The results should be an exact repeat of the steps you performed during the recording process.

Viewing the code

To view the recorded steps as VBA code, select View (tab) -> Macros (lower-part of button) -> View Macros.

Select the macro from the list and click EDIT.

The code will open in the Visual Basic Editor and appear like the following:

Sub Insert_Heading()
' This macro will insert the standard
' corporate heading starting in cell A1
' of the current worksheet.
    Range("A1").Select
    ActiveCell.FormulaR1C1 = "ACME Widget Corporation"
    Range("A3").Select
    ActiveCell.FormulaR1C1 = "Pazmangasse 24"
    Range("A4").Select
    ActiveCell.FormulaR1C1 = "Vienna, Austria"
    Range("A5").Select
    ActiveCell.FormulaR1C1 = "A-1020 Vienna"
    Range("A6").Select
    ActiveCell.FormulaR1C1 = "AUSTRIA"
    Range("A1").Select
    
    With Selection.Font
        .Name = "Century Gothic"
        .Size = 24
        .ThemeColor = xlThemeColorAccent6
        .TintAndShade = -0.249977111117893
    End With
    
    Range("A3:A6").Select  
    With Selection.Font
        .Name = "Lato Heavy"
        .Size = 18
        .ThemeColor = xlThemeColorAccent2
        .TintAndShade = -0.499984740745262
    End With   
    Range("A8").Select   
End Sub

Remember, you can perform running the macro in more user-friendly ways. This can be something like clicking a button on Quick Access Toolbar.

Key takeaway

This is a very simple example to get you started with Excel macros. If there is one thing macros are good for, it’s automating repetitive, predictable tasks. No matter how difficult the task is. As long as there is a logical approach to organizing and analyzing the data, you can use a macro to automate it.

The next time you are performing the same task the same way, think to yourself, “I should turn this into a macro.”

Page Last Updated: November 2020

Top courses in Excel VBA

More Excel VBA Courses

Excel VBA students also learn

Empower your team. Lead the industry.

Get a subscription to a library of online courses and digital learning tools for your organization with Udemy for Business.

Request a demo

Courses by Leila Gharani

Visually Effective Excel Dashboards
Leila Gharani
4.7 (10,003)
Bestseller
Unlock Excel VBA and Excel Macros
Leila Gharani
4.7 (24,538)
Bestseller
Google Sheets - The Comprehensive Masterclass
Leila Gharani
4.8 (485)
Bestseller

Courses by Leila Gharani