Excel VBA for Beginners

Joseph Basic, Excel, VBA 3 Comments

If you’ve done a lot of work in Excel, you’ve probably heard about Macros or VBA. Excel VBA can help you do your work much, much faster. It can also help you expand the true power of Excel in ways you never thought possible. Ranging from processing data in a worksheet all the way to scraping web pages, VBA is quite the beast. In this post, I’d like to discuss Excel VBA for beginners. We’ll cover:

  • What is VBA?
  • When should you use VBA?
  • Getting started with the Visual Basic Editor
  • Your first *useful* program
  • More to come

What is VBA and why should I care?

VBA stands for Visual Basic for Applications. It is an implementation of Microsoft’s Visual Basic 6 programming language specific to Office applications in order to give you access to each application’s features like cells, charts, and more. Each Office application has its own VBA Object Model (an Object Model is sort of like a map of the features you have access to with VBA). So while the VBA concepts are the same for each Office application, there is still a learning curve for each one.

Ok, enough with the boring stuff. Why should you care about Excel VBA?

Simple: it makes your life easier.

Excel VBA - Success

(You laugh – this really happened to me)

You can write programs that will do tasks for you lightning fast – leaving you more time to do other things that are more important. This is especially useful for when you have to do a repetitive, tedious task to perform. For example, you could use Excel VBA for:

  • Copying and modifying data
  • Apply formatting automatically
  • Finding unique values
  • Data checking before Saving
  • Go beyond Sheet Protection
  • Create forms to control data entry
  • And so much more (seriously, way more)

When Should You Use VBA?

When you have a logical, repeatable process that you do over and over, then it’s time to consider automating it. However, if you’re just doing a one-off thing that will take you 10 minutes to do and will probably never do it again, it will probably not be worth your time making a macro to get the job done. Just rough through it. Trust me on this.

Excel VBA for Beginners – Start with the Visual Basic Editor

The first place to get started would be the Visual Basic Editor. Press ALT+F11. You will have a new window pop up.

Excel VBA - VBE

This is called the Visual Basic Editor and this is where you’ll be doing all your magic. Consider this your workbench, or your garage, or your desk – whatever place you get your work done.

I’m sure you’re itching to get some coding done, and we will! But it’s important to understand where the tools are so you understand your surroundings. So, let’s quickly breakdown these components:

Excel VBA Project Window

Excel VBA - VBE Project Window

Located at the top left of the VBE window, the Excel VBA Project window will show you all the places where your VBA code can potentially live. Think of it as a quick reference like Windows folders in Explorer. In this section, you can find Worksheet code, Workbook code, Userform code, regular Modules, and Class Modules. Each one of these could be considered a Project Object. All of these Project Objects can hold code, but each one has it’s own special uses (which I’ll cover in a later post).

Properties Window

Excel VBA - VBE Properties Window

Just below the Project Window is the Properties Window. This window holds the properties of each Project Object that is currently selected. I haven’t found many times where I needed to use this, but feel free to poke around to get familiar with changing those properties.

Toolbar

Excel VBA - VBE Toolbar

Just your standard toolbar. We’ll dig more into this as we move along with future posts.

Okay. Enough of that. Let’s get to the real meat.

Writing Your First *Useful* Program

Many “intro to programming in X language” articles usually start with a “Hello World” program. What that basically means is that the article teaches you how to display “Hello World” to the user. But these kinds of intros leave a lot to be desired. So, let’s forget “Hello, world!” and get some REAL work done.

Before Beginning

Danger
Please realize that you cannot Undo any changes that Excel VBA code will make to your spreadsheet.

Sorry, but there’s no Control+Z’ing your way out of a VBA mistake! That said, please Save before moving executing any Excel VBA code in case something goes wrong. I personally suggest that you use a test workbook when learning VBA.

For our first program, let’s delete entire rows of data if column A has a specific value in it. For example, say we have this data:

Excel VBA - Sample Data

We want to delete any row with “Delete” in it and leave the other rows alone. So, we have 4 “Keep” cells to leave alone and to delete 3 “Delete” cells. If you were to do this, how would you begin?

Your first inclination might be to loop through the cells, search for “Delete” and, if found, delete the entire row. Yes? Cool, let’s try that.

Go to the VBE window and create a new module by right-clicking on the workbook name in the Project Window, hover over Insert, and click on Module, like so:


Excel VBA - Insert Module

Add this code to the module (we’ll go over what this means in a minute):

Option Explicit

Public Sub deleteText()
    Dim r As Range

    For Each r In Selection
        If (r.Value = "Delete") Then
            r.EntireRow.Delete
        End If
    Next r
End Sub

In order to run this code:

  1. Go back to the workbook and select the cells A1:A8 (this is important to remember)
  2. Bring up the Macro Window by pressing ALT+F8
  3. Select “deleteText”

Excel VBA - Run Macro

Click Run. Let’s take a look at the data.

Excel VBA - Delete text ran for first time

…huh. There’s still one cell that says Delete.

C’mon man. How is this a “*useful*” program? It didn’t even work right!

Alright, alright. Calm down. This kind of thing is something you’ll always run into when coding. No matter how good you get, there will always be something that snags you. I figured it was a good idea to get you used to this type of thing early on. All this means is that we have a bug in the code. So let’s “debug” it.

Debugging Code

There are a lot of debugging features to cover, but this post has already gone on and on pretty long and I want to try to hold your attention for just a liiiittle bit longer. So let’s cover the code step by step to see if we can uncover what went wrong.

The first line of code is just a variable declaration, so there’s nothing wrong with that. The only other part of this code is the For loop, so let’s see what’s going on there.

For Each r In Selection

This line tells VBA to process all the cells in your selection one-by-one. In order to do anything with the cell being processed for each time the loop runs its course, we tell VBA to assign each cell to the variable r. This is the way we access each cell’s information as we loop through all the cells in the selection.

If (r.Value = "Delete") Then

The text inside the cell is what we’re trying to check – and this is how we check it. r.Value will return whatever text is within the cell that we’re investigating. If the cell’s value is "Delete" then we will move on to the next statement, which is:

r.EntireRow.Delete

So, if the cell’s text reads “Delete” then we want to delete the entire row that that cell is in. Simple, right?

Well, not exactly.

When we tell VBA “hey, loop through these cells, please,” it will take that range of cells, and store it internally so it can loop through it. The way Excel does this is by using a counter to keep track of where it is within the range. So, let’s look at the original data again:

Excel VBA - Sample Data

The first row has “Data” in the cell, so we skip this one. The second row has “Delete” in it, so we delete it, along with the entire row. This affects the entire range that we’re working with. So, before we deleted the row, VBA was at row 2:

Excel VBA - VBA before deleting row

Then we delete the row, and VBA is still at row “2” (which was row 3, but is now row 2).

Excel VBA - VBA after deleting row

What’s next in the code?

End If

This is a way to “close off” the If statement. So only the items inside the If statement will be executed if the If statement evaluates to True. What’s next?

Next r

This tells Excel to move to the next row down, which is row 3. So, technically, it skipped the “Keep” that was in row 3, but is now in row 2.

Confusing? Sometimes that’s just how code is 🙂

Think you know where the bug is? If you realized that the second “Delete” cell was skipped because we modified the range while VBA was looping through it, you guessed right.

Ok, so how do we fix this?

Let’s pick something easy. Let’s find the cells that have “Delete” in them, and instead of deleting the entire row, let’s just make the text blank. We’ll change r.EntireRow.Delete to r.ClearContents.

But that only makes the cells blank, and you want to remove the entire row for all of those. So we’ll add another line of code:

Selection.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete

After all the “Delete” cells have had their text cleared, we do a search on the original selection for blank cells, and delete the entire row for only those cells. Here’s our new code:

Option Explicit

Public Sub deleteText()
    Dim r As Range

    For Each r In Selection
        If (r.Value = "Delete") Then
            r.ClearContents
        End If
    Next r

    Selection.Cells.SpecialCells(xlCellTypeBlanks).EntireRow.Delete
End Sub

Try running that code again. Notice anything different?

Excel VBA - Delete Cells After Code Fix

Awesome. Now we have working code 🙂

Where to Go From Here

We’ve covered so much and I don’t want to leave you hanging because we really didn’t show more ways to write Excel VBA code. So let me share a good trick with you to quickly learn some VBA code. Open the VBE (Alt+F11) and record a Macro in Excel. When you start recording the macro, a new Module is created within that workbook (found in the VBE Project window). Double-click on that new Module and place the VBE window in a location that you can see both the VBE widow and Excel. As you do things to Excel, it will start writing the code for you! Check that out and observe the behavior. It’s a great way to get started. Good luck and drop me a line if you have any questions.

 

Are you ready to master Excel Dashboards?

Learn Excel Dashboard Course Power BI Webinar

Still not convinced? Check out my review of the course!

  • Bill Reader

    I’ve worked with Joseph’s VBA code in the past. This guy is brilliant. Listen to what he says and you’ll be amazed at what you can do with VBA code. Thanks for everything you do, Joseph!

  • jacktx42

    I did some Excel VBA (actually, it was Outlook VBA) to combine multiple linked PDFs and rename according to a project number in the subject. While it didn’t get me a promotion, it did save someone’s job. The volume of what needed to be done saved an estimated 9 hours of work every two weeks. I was proud of that.

    • Joseph

      That’s awesome! Congrats on saving your colleague’s job!