What is VBA in Excel? Record a Simple Macro
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:
- Save each sheet in a workbook as a separate file.
- Send a sheet to a co-worker as an email attachment.
- Sort all sheets in a workbook.
- Remove all comments, hyperlinks, images, or personal information from a workbook.
- Replace all Excel functions with function results.
- Format all tables with proper colors, fonts, and other cosmetic features.
- Refresh all reports (or Pivot Tables) in a workbook.
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.
- Use a keyboard shortcut (Alt F11)
- Click a button on the Quick Access Toolbar
- Click a button on the Ribbon in the Developer Tab
- Click a button you add to the spreadsheet
- Click a shape or icon you add to the spreadsheet
- Click an image you add to the spreadsheet
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.
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.”
Top courses in Excel VBA
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.