Essential VBA coding every Excel user should know

Excel users don’t program, and programmers don’t care about Excel so the potential of VBA remains a wasteland between these two worlds for a lot of users.

By the end of this article you’ll be able to write your own simple programs and customized Excel functions regardless of your current level.

Photo by Oleg Magni from Pexels

I’am writing this with a really straight forward approach, because although there are a lot of great VBA blogs out there, they fail at breaking the barrier for most non-programers to start with it.

My goal is to help as much people as possible improving their work with some VBA skills.

If you already know what VBA is, and you have some basic programming skills I suggest you skip this introduction and go straight to the “Ok, let’s get serious” title below. If this is not your case, then let me explain what this is about.

VBA stands for Visual Basic for Applications. It’s a programming language included in the MS Office package.

VBA is perfect to perform repetitive tasks which doesn’t add value. For example, the classical update, copy, paste, repeate type of workbook. Analizing the reports adds value, but building them is a waste of time, son instead of doing it yourself let the computer work for you (I talked about this on How to become a better controller).

With a handful of VBA programs you can save huge amounts of time.

Non the less, this VBA scripts are contained in the Excel files, so another person without any technical knowledge can benefit on the code you write.

You can use it also to perform difficult tasks which would be very tedious, or simply impossible to do with Excel’s built-in functions alone. Like performing an action depending on the background color of a cell for an instance.

But before we begin, there is some general misunderstanding about what is a Macro and what is a VBA program, so let’s keep it clear before we begin.

Are VBA and Macros the same?

Macros are peaces of VBA code generated automatically via Excel. It is really easy to create a macro, you just need to click on Developer/Record Macro and do whatever you want it to repeat later.

When using macros you don’t need to see the code, much less editing it, but at the end this is exactly what Excel is doing. In the GIF below you can see how the code is generated while recording a macro:

Excel writing VBA while recording a macro

Even for experienced VBA users it’s useful to record a macro every now and then because it gives you a hint of how to write an specific code without spending an afternoon searching on google. The downside is that this macro’s code usually isn’t the best option.

For example, in the GIF above Excel writes 7 lines to change the color of a cell but, a single line with the code ActiveCell.Interior.Color=65535 would be more than enought.

Forget about macros for now and let’s focus on pure VBA programming.

Part 1: Writing a program

Photo by Annie Spratt on Unsplash

As I said before my goal is for you to be able to write simple vba programs, so forgive me if I go straight to the point and skip some technical details.

Also, if you have problems following any step just leave a comment, I’ll be really happy to help you.

First of all, we are going to create a module, you can imagine it as a notebook where you write VBA code. On any Excel worksheet, press Alt+F11 or go to Developer/Visual Basic, this will open the VBA editor window.

At the left you should see a frame named Project -VBA project containing a treelike structure with the name of the open workbooks on your computer, their sheets, and some other stuff which we’ll talk about later. If you can’t see this go to View/Project Explorer.

Now right-click on any sheet and press Insert/Module (see GIF below)

Hello World

If you have ever programmed a Hello World function then you might want to skip this part, but if not this is a fun way to write your first program.

Copy this in your module, or better type it yourself, and press F5 or Run/Run Sub.

Sub HelloWorld()
MsgBox ("Hello World")
End Sub

You should see something like this:

Tada!!! Congratulations for your first working program. Let’s take a look at it step by step. First the sub block, this is telling your Excel Here you have a VBA program called ‘HelloWorld’. The End Sub points the last line of the program.
You will write the commands between these two lines

Sub HelloWorld()
End Sub

Next we summon the msgbox() function. This function can do a lot more than this, but sometimes it’s ok to keep it simple.

Note that we must write the text quoted, if it’s not quoted the editor will handle it like a variable and pop an error.

Msgbox("Hello World")

Coding Hint 1: you can leave comments within your code with an apostrophe. In your VBA editor you will see them green colored.

'This the classical Hello World program
Sub HelloWorld() 'Here starts the program
MsgBox (message) 'This fuction displais the message
End Sub 'Here is the final line

Codign Hint 2: You can also use tabs, spaces and empty lines to make it more readable, they don’t affect the program either.

'This is the classical hello world program

Sub HelloWorld()
    MsgBox (message)
    
End Sub

Ok, now let’s get serious

How about counting the number of colored cells in a range for example? As the exercise above, this has a pedagogical purpose, but try to think about the kind of tasks you could apply it for.

First of all, I have a sheet that looks like this:

As I did before, I’ll show you the program finished, so that you can just copy&paste and see it working on your computer. Then I’ll explain it line by line.

Here it goes:

Sub Colored()
Dim R As Range
Dim Count As Integer
Set R = Range("A1:H15")
Count = 0
For Each cell In R
     If Not cell.Interior.ColorIndex = xlNone Then Count = Count + 1
    
Next
MsgBox (Count)
End Sub

Once you run it you should get a message box like this:

It will work for any color and any number of colored cells within the range A1:H15, but don’t believe me, try coloring different cells with different colors and see it for yourself.

How does it work? Let’s go step by step:

We declared two variables, a Range and an Integer. You can imagine a variable like a container for an specific type of data. For instance, we created an integer, which is just a whole number, and a Range. Ranges are really interesting in VBA for Excel. Once you assign a range of cells to a range type variable you gain access to those cells properties, like their value, their background color, format, etc. We’ll get to that later.

By the way, it’s a healthy habit to declare the needed variables at the beginning.

Dim R As Range
Dim Count As Integer

Next step, assign values.

Set R = Range("A1:H15")
Count = 0

Notice that in order to fill a Range you need to use the Set function while with simpler variables like integers it’s not necessary.

Acting on each cell of a given range

To perform this we’ll use a for statement. In case you’re not familiar with programing, a for function runs through a set of elements, performing an action in each one of them.

In its simplest expression, it just repeats something over.
For example, if you try this:

Sub Count1to10()
For i=1 to 10
    msgbox(i)
Next
End Sub

Something like this will happen:

Got it? Now look at the code we used before:

For Each cell In R
      'Do whatever
    
Next

In this snippet, instead of going from 1 to 10 we do something more useful in Excel, going through each cell of a given range and performing an action in each one of them.

Now let’s take a look at what’s inside the for statement.

If function in VBA

As an Excel user, you may be familiar with the IF function. In case you’re not, it works this way:

The condition is a boolean, this means that its value can only be True or False.

The equivalent to this Excel function in VBA would be:

If (2+2=3) then
    msgbox("Yep")
else
    msgbox("Nope")
End If

In other words:

If [BOOLEAN CONDITION] then
    [DO THIS IF TRUE]
else
    [DO THIS IF FALSE]
End If

In our example, we are only interested in the true part. If the cell is colored then we count it, if not we don’t care. When you’re only interested in the true part you can write it as a single line

If [CONDITION] then [DO THIS]

Going back to our code:

If (Not cell.Interior.ColorIndex = xlNone) Then (Count = Count + 1)

The condition is (Not cell.Interior.ColorIndex = xlNone), in natural language it would be something like (this is not an colorless cell). So if the cell has any color the program will adds 1 to the variable Count. Finally, when the for statement has already run over all cells in range R it added as many +1 as colored cells. That’s how we count them.

Cells properties in VBA

I don’t want to get technical, but you should know that VBA is an object oriented program. VBA objects have properties and methods. A property is something like color, size, column, etc. A method is an operation like copy, save, etc. Each type of object is called a Class, and each Class has different properties and methods. For instance, it makes sense for a Workbook class object to have a save method, but it doesn’t for a cell class object as there is no way to save a single cell.

When you type a dot next to a variable the VBA Editor will show you all the available properties and methods for its class (see GIF below).

In the color counting example we used the property .Interior.ColorIndex. An easy way to get the name of a given property is to change it while recording a macro.

Cell Class has dozens of properties. Some of the most useful and easy to understand are those:

• .Row: row number
• .Column: column number. I insist, not letter, but number. Column A is 1, B is 2 and so on.
• .Value: its value, being the output of a formula or just a raw value (usually a number, a string or a boolean).

And there is also a property called .Offset( ) which is particularly useful.

This one has two arguments .Offset(rows,columns). For example, Range(“A1”).offset(1,2) is C2, as it’s one row below and two columns to the right. With this property you can act over a cell depending of its position relative to an other cell which you have declared in your code.

Look at this example. Take a moment to read and understand the code and then pay attention to what is actually doing.

Example of offset and value properties

See how you can combine different properties like Offset and Value?

By the way, to run a program line by line as I did above just press F8.

Stop condition

While running a for statement, sometimes you don’t need to go through all the cells. For instance, imagine that we want to know if there is any colored cell. As you don’t care about how many are in there you can stop when you find the first one. To do this use the Exit For statement. See example below:

Sub Colored_variant()
Dim R As Range
Set R = Range("A1:H15")
For Each Cell In R
    cell.select '<--- This will place the cursor over the cell which it's checking
    If Not Cell.Interior.ColorIndex = xlNone Then
        MsgBox ("one colored cell found")
        Exit For '<--- Skips the remaining cells
    End If
Next
End Sub

You can copy this on you VBA editor and run it step by step just pressing F8 repeatedly. Put the windows in a way that you can see the worksheet, I entered the snippet cell.select so you can see which cell it’s checking at a time.

Part 2: Different ways to run a program

Pressing play (F5) in the VBA Editor is the simplest way to execute a program, but you need to have at least some knowledge of VBA to do it.
What if your program is for someone else who doesn’t know anything about VBA? Or what if you want it to work under certain circumstances? like when you open a certain file, for example.

Running a program on demand

The easiest way to do this is to assign the program you have just created to a shape. This is really easy, just right click on any shape and choose Assign Macro. After this you’ll run the program just by clicking (left click) on this shape.

A rather more professional way to do this is with Active X controls. There are different types of Active X controls, but in this article we’ll see only buttons.
Go to Developer/Insert/Active X controls to add a new button (see GIF)

By right clicking and choosing View Code you can write exactly what the button is supposed to do. Note that this code is created in the Sheet code, not in a module.

You can also summon a sub from another sheet or module with the function call.

Private Sub CommandButton1_Click()
Call HelloWorld()
End Sub

There are some other ways to run a VBA program on demand, I’ll write more about this topic in the future, but for now understanding this two ways should be more than enough.

Running by events

In this context, an event is an action. In the previous example, you saw a Button_Click event. In this case the event is clicking that particular button, not very original. But there are a lot of different events you can use. Let’s see a few examples:

1. By activating a certain sheet

To program an event on a given sheet just open its code with a double click and choose between the available events in the choose boxes above. You can also write the code straight away if you know how does it goes.

This would be an example of what you can do.

Private Sub Worksheet_Activate()
MsgBox ("Welcome to sheet number 1")
End Sub

It will do something like this:

Remember that you can also call some function from another module with the Call Statement.

2. By opening a Workbook

Same as with sheets, there are some events regarding whole workbooks. To program those events double click on ThisWorkbook in the project explorer menu to open its code.

Imagine you have a lot of pivot tables in the same document, and you have to make sure you don’t forget to update them. Take it easy, let the computer work for you. You can automate it with just 3 lines of code:

Private Sub Workbook_Open()
ThisWorkbook.RefreshAll
End Sub

Play with the chooseboxes to find more events, the more you play the more you learn.

Part 3: Customized functions

Photo by Gijs Coolen on Unsplash

Would you like to create your own functions, not VBA functions, but functions you can use in a spreadsheet like =SUM() or =VLOOKUP(), then I have good news for you, this is way easier than it looks like.

For example, did you ever have a sheet with a lot of ratios like A/B, sometimes it’s really annoying when B equals 0 and it start giving #DIV0 errors.
I know, you can just do something like:

=IF(B5=0;0;B4/B5)

But it would be easier if you just have a formula that does exactly that without having to type it each time, wouldn’t it.

Create a new module and try this one:

Function Div(A,B) as do
If B=0 then 
    Div=0
Else
    Div=A/B
End If
End Function

That’s it! You don’t need to do anything else the new function is available. See how it works:

The difference between functions and sub, like the MutiplyByTwo we did before, is that a sub will be calculate only when the program runs, but a function written in a cell will update like any other Excel Built-in function as SUM( ) or COUNT( ).

Bonus: What to do next?

Photo by Danielle MacInnes on Unsplash

Those are the foundations to start doing some worthy VBA programs, but it’s just scratching the surface.

You can do far more than this, almost everything is possible (in a way or another) so just try to imagine things beyond what a spreadsheet should be capable of.

I know it’s a bit cliché but… Think Outside of the Box.

Some advice:

• MS Office on-line documentation is quite good to start with.
• There are dozens of blogs covering different topics, so if you’re not doing something really weird, chances are if you google it you will find at least two or three bloggers covering the topic.
• If you’re indeed doing something really weird… First of all. Congratulations! you’re already outside of the box! But if you need some help with it you can go to www.stackoverflow.com. Or if you’re spanish-speaker www.todoexpertos.com is a great alternative (you’ll find me in both sites).

And of course, if I can help you with any program, or if you will like me to cover any particular topic regarding VBA, Excel or Access just ask for it in the coments or send me a private message on linkedIn if you prefer.

I’ll be really happy to help!!

I hope you find this article useful.
Thank you for reading and have a nice day!