VBA Excel: How to Get Started and Make Your Work Easier

Important VBA Excel Terms

Before working with VBA, you must understand its key terms and concepts. So, here’s a list of some of the most used terms you'll encounter as you start automating tasks and building custom solutions in Excel.

• Modules are the containers for VBA code, where procedures and functions are stored.

• Objects are the building blocks of VBA. They represent elements like workbooks, worksheets, and cells.

• Procedures are the blocks of code that perform specific tasks, often categorized as sub-procedures or functions.

• Statements are the instructions within a procedure that tell Excel (or Word or Access) what actions to perform.

• Variables store data that can be used and manipulated within your code.

• Logical operators compare values and make decisions based on the results. They include operators like And, Or, and Not.

Getting Started with VBA in Excel

To begin using VBA, you'll need to access the VBA editor. This is where you'll write and edit your VBA code. Let's walk through how to get there:

Enabling the Developer tab 

The first step is to enable the Developer tab, which is often hidden on the Ribbon. To do so:

• Right-click anywhere on the Ribbon. 

Ribbon in Excel. Image by Author.

• Select the Customize the Ribbon... option. 

Customize the Ribbon to enable the Developer tab. Image by Author.

• A dialog box will appear. Check mark the Developer option and hit OK.

Selecting the Developer option. Image by Author.

You can now see a Developer tab at the top of the Ribbon.

The Developer tab is enabled on the Ribbon. Image by Author.

Opening the VBA editor 

Once the Developer tab is enabled, you can access the VBA editor. To do so, click the Developer tab and select Visual Basic from the options. You can also use the keyboard shortcut ALT + F11 to open the VBA editor directly.

VBA editor. Image by Author.

Navigating the VBA interface 

The VBA editor might initially seem intimidating, but it’s easy to use. Let’s understand its few key areas:

• Code Pane is where you write and edit your VBA code.
• Project Explorer shows a hierarchical view of the projects and modules in your workbook.
• Properties Window displays properties for selected objects to customize their settings.

VBA interface in Excel. Image by Author.

Writing VBA Code in Excel

Now that you're familiar with the VBA editor, it's time to start writing some code. VBA code consists of different parts, like sub procedures and functions. These are sets of instructions that tell Excel what to do. Don't worry if you've never programmed before - I'll take it step by step. 

Writing a VBA subroutine

Before writing a macro, you have to create a macro environment. To do so, follow these steps:

• Inside the VBA editor, select Insert > Module. A new module will be created with the default name, Module1. 

Select Module under the Insert tab. Image by Author.

• You can change the name of the module in the Properties pane.

Rename the VBA module. Image by Author.

Now you know how to set up the environment for creating a macro, let's create our first macro together to display a message. By default, the code window opens when the module is created. If it doesn't work in your case, right-click on the module you created and click View Code. The code window will appear where you can write your macros.

Here, I’m creating a macro to display the message Hello, World!. To do so, I write the following code in the code pane:

Sub ShowMessage()
    MsgBox "Hello, World!"
End Sub 

Here:

• Sub is the keyword for a subroutine, which is a block of code that performs a specific task. In VBA, a subroutine doesn't return a value.

• ShowMessage is the name of the subroutine. You can name it whatever you like, as long as it follows VBA naming conventions (e.g., no spaces, can't start with a number).

• () are used here to define the subroutine's parameters. Since no parameters are needed for this task, the parentheses are empty.

• MsgBox is a built-in VBA function that displays a message box on the screen. 

• End Sub statement indicates the end of the subroutine. Everything between Sub and End Sub is the code that will run when the subroutine is called.

• Text strings in VBA are enclosed in double quotes. Here, we want the string Hello, World! to appear inside the message box.

Writing a macro. Image by Author.

• Now, it's time to run the code. Use the shortcut key F5. If you want to do it manually, go to the Developer tab and select Macros. Hit Run. 

Select the Macros option under the Developer tab. Image by author.

You can now see the message box appear on your Excel sheet.

Message box. Image by Author.

Understanding objects in VBA 

In VBA, objects allow you to control different elements within Excel and automate tasks for more efficient workflows. There are three key objects: Workbook, Worksheet, and Range. Let’s understand each with a practical example.

The Workbook object is any Excel file that’s currently open. It allows us to perform actions such as adding new sheets or saving the existing sheets within the workbook. In this example, I want to add a sheet to the currently opened sheet and then save it. At first, I had Sheet1. To add another sheet, I write the following code:

Sub AddSheetAndSaveWorkbook()
    ' Adds a new worksheet to the active workbook
   ActiveWorkbook.Sheets.Add
    
    ' Saves the workbook
    ActiveWorkbook.Save
End Sub

This code works exactly the same as the previous code, but I’ve added comments here. Comments start with ' and they don't affect your code. We only add them for our convenience to make the code clearer. 

You can see another sheet, Sheet2 is created now.

Add and save a new sheet in the existing sheet using VBA. Image by Author.

The Worksheet object represents the currently active sheet in Excel. With this, you can modify or manipulate the active sheet. For example, I want to change the name of the active sheet. To do so, I enter the following code:

Sub RenameActiveSheet()
    ' Renames the active sheet to "Sales Report".
    ActiveSheet.Name = "Sales Report"
End Sub

Changing the name of the worksheet using VBA. Image by Author.

Now, Sheet1 has been renamed to Sales Report.

Renaming the Excel worksheet using VBA. Image by Author.

The Range object refers to a specific group of cells or a single cell that we can handle as needed.  In the following example, I select a range of cells from F3 to I3 and change the background color. To do so, I write the following code:

Sub FormatRange()
    ' Selects the range from A1 to C6
    Range("F3:I3"). Select
    
    ' Changes the background color of the selected range to Green
    Selection.Interior.Color = RGB(101, 255, 143)
End Sub

Selecting range and changing the color using VBA. Image by Author.

You can see the difference in results. 

Changing the color using VBA in Excel. Image by Author.

Creating variables in VBA

Like other programming languages, VBA variables store two main data types, numbers and text. To use them in VBA, you must first declare the variable using the keyword according to your preferences, followed by the variable name and data type.

Keyword variableName As DataType

Here: 

• Keyword can be Dim, Static, Public, and Private.

• variableName refers to the name chosen for this variable (no spaces).

• As is used to declare the variable type.

• DataType refers to the variable type, such as Integer.

When working with variables, you also have to abide by some rules:

• The length must be less than 255 characters.
• No spaces are allowed between characters.
• The name cannot start with a number.
• Don’t use periods in the name.

Numeric data type

Numerical data types in VBA are used to store numeric values.

Non-numeric data type

Non-numerical data types in VBA store values that aren't numbers.

Automating Tasks with VBA Excel Macros

Macros are essentially a series of instructions created in VBA to perform repetitive tasks. Using a macro, you can record a series of actions, like formatting cells, copying data, or performing calculations. After saving the macro, we can re-apply these actions with a single click. This saves time, especially when working with large datasets or tasks.

For example, if you often format your reports the same way, you can record a macro that applies all the necessary formatting steps instead of doing it manually each time. Later, you can run this macro to format new data. We showed a couple of basic examples, but imagine doing more small actions with just one click. Even if you don’t have coding experience, you can still automate routine tasks to streamline workflow and reduce the chances of errors that can occur when performing repetitive actions manually.

Recording an Excel macro 

Let’s see how you can record a macro to make some formatting changes:

• Go to the Developer tab > Record Macro button. 
• A dialog box will appear. Give your macro a name like I did FormatCells.
• Assign a keyboard shortcut if desired. I assigned it Ctrl+S.
• Click OK to start recording.

Now that the macro starts recording, perform the actions you want to automate. For this example, I am recording some formatting changes to a simple table. Once you’ve completed it, go back to the Developer tab and click the Stop Recording button. Below, you can see the macro in action.

Recording a macro using VBA in Excel. Gif by Author.

Now, if you have another dataset in another sheet and want the same formatting in that dataset, too, instead of formatting the whole thing again, press the shortcut key you created (in my case, it’s Ctrl+S). Otherwise, go to sheet2 > Developer tab > Macros > Run to do it manually.

Using the Excel macro. Gif by Author.

Editing Excel macros 

After recording a macro, you can even tweak or customize its actions. Here’s how: 

• Go to the Developer tab and click on Visual Basics. 
• VBA editor will appear. Now, in the Project Explorer, search for the workbook where your macro is stored. 
• Expand the Modules folder, then double-click the module that contains your macro. The code window will display the VBA code of your recorded macro.

Code of recorded macro in Excel. Image by Author.

Each line of code shows the action you performed during the recording. From here, you can modify the recorded code to customize the macro per your choice. In my case, I set the Selection.Font.Bold to False to remove the bold formatting. Once the edits are done, save the changes by clicking the save button or press Ctrl+S and Run the code to apply changes.

Customizing the Excel VBA macro. Image by Author.

After running your macro, you can see the results — the previously bold columns are no longer bold. 

Using a macro in Excel. Image by Author

Writing custom Excel macros 

There may be some advanced tasks where you can’t record a macro. In such cases, you have to write a macro from scratch. For example, I want to copy the data from one worksheet to another. Here’s how I write a custom macro:

• Open the VBA editor manually in the first sheet or press Alt+F11.
• Insert a new module.
• Write the following code in the code pane.

Sub CopyData()
Sheets("Sheet1").Range("B1:E21").Copy Destination:=Sheets("Sheet2").Range("B1")
End Sub

• Save the macro and hit Run.

Now, you can see that the data from Sheet1 has been copied to Sheet2.

Writing a custom macro using VBA. Gif by Author.

Final Thoughts 

From automating repetitive tasks to creating complex macros, VBA opens up a world of possibilities within Excel. As you become more comfortable with the basics, practice writing code and gradually explore the more advanced features of VBA. Remember, the key to mastering VBA—or any programming language—is consistent practice and a willingness to experiment. To further enhance your skills, consider exploring these additional resources. The Data Analysis in Excel and Learn Excel courses are excellent for learning more about Excel's features. 

If you want to expand your coding knowledge beyond VBA, the How to Become a Programmer in 2024 guide offers a roadmap. Do check it out if you are interested in the field. Additionally, the Financial Modeling in Excel course is perfect for those interested in creating detailed financial models.