Excel Funtion: VBA
Excel VBA: Learn the basics in a few minutes
What is VBA? VBA stands for Visual Basic for Applications. It is an implementation of Microsoft’s event-driven programming language Visual Basic which is now used with Microsoft Office applications such as MS Excel, MS Word and MS Access.
Introduction to this Excel VBA Tutorial
Why do we need to learn VBA? VBA is an incredible skill because it can help us automate our work, that is anything you do in Excel with a certain amount of repetition or frequency can be dealt with automatically by controlling Excel. What do I mean by controlling Excel? Well, quite simply, you can write some code (known as Visual Basic for Applications (or VBA) which sits ‘behind the scenes’ and can do all the tasks you normally would from copying/pasting, writing formulas, creating charts, saving files in a folder and pretty much everything else you can imagine! The potential to save time is incredible, I regularly create VBA scripts that save me up to an hour and my personal record is automating an entire monthly reporting process that lasted over 100 hours down to 5 minutes! What’s even more remarkable is that from my time saved, I was able to solve other problems for the companies I worked for and was promoted several times. My peers simply couldn’t compete with me because I was significantly more productive than them.
So today, I am going to teach you the basics of VBA and how it could be a great foundation to enhance your Excel skills to a higher level of expertise. After this tutorial, I hope you will be confident enough to start creating your own macros and continue practicing it. Soon, you’ll realize how amazing VBA can help you on your day to day work. And in no time, you can be the next Excel VBA Rockstar!
For our first lesson, we are going to make something automatically display in a cell in our Excel spreadsheet. By the way, there are two ways to refer to a cell in Excel, one is using the word Cells and the other one is by using the word Range. The latter I am going to discuss after this topic.
But before that, what we need to do first is to open the Visual Basic window. To do that, go to the Developer tab then click the Visual Basic button. Or, you can simply hold ALT key then press F11 key. (Alt+F11)
Whichever method you choose, a window should appear like the picture below. Double click an object on the left pane under Project – VBA Project. In this case, I clicked Sheet1 to show the coding area for Sheet1. By doing so, all written codes in this object will only affect Sheet1 worksheet. Similarly, if you add codes in object Sheet2 or Sheet3, the codes will only be run in its respective worksheet.
Now let’s start writing some codes. Again, we want some texts to automatically appear in cell B5 in Sheet1 so we will need to add the following code:
Cells(5,2) = “Hello”
Cells(5,2) also refers to cell B5, where 5 refers Row number, and 2 is the column number, which is B.
Hit the Run/Play button or press F5, and the word Hello should then appear in our Sheet1 cell B5 like this:
Note: A Sub (short for Subroutine) is a series of Visual Basic statements enclosed by the Sub and End Sub statements. A Sub procedure performs a task provided in a form of code.
As I have mentioned previously, we can also use the word Range when cell referencing. The good thing about using Range is it lets you reference either a single cell or multiple cells all at once. It is also easier to use because its format is the common way Excel cells are named like cell A1, A2 or cells A1:A10. But of course, Cells property has its advantages as well when it comes to coding loops.
Let’s use the Range object in VBA.
This is the code to add in the VBA editor:
Range(“A1”) = “Hello”
To display the text “Hello” in multiple cells or in a range of cells, we simply tweak the code like so:
Range(“A1:A10”) = “Hello”
What we did is instead of cell “A1” enclosed in our range, we replaced it with “A1:A10”, displaying the word Hello from cells A1 to A10.
VBA Copy and Paste
Everyone would agree when I say that Copy and Paste are the two most common Excel operations. You may have done a lot of copying and pasting manually. Now I will show you how quick and easy VBA can do it for you.
First, go back to the VBA window. Assuming we have some text written in cell A1 and we want to copy and paste it in cell A2, add this code:
The 2nd Range object is the destination range. This can also be a single or multiple range of cells.
This is how it would look like in the VBA editor and Excel sheet.
To paste to multiple cells, add this code:
Color Cells with VBA Color Index
VBA is so useful when we want to automate repetitive tasks. One example at work is, we often use cell highlighting or cell colors for quick overview of statuses etc.
There are two ways to set the color. One is with the use of ColorIndex, which has 56 colors, or Color which makes it possible to use any color at all.
When using the ColorIndex, you can refer to the image below for the color and its corresponding colorindex.
To set the color of our cell to one of these, write the code:
Range(“A1”).Interior.ColorIndex = 49
This is how it should look like in VBA Editor and Excel sheet:
Second option is to use the Color property. Under this, there are 2 inputs we can use:
- vbColor – This is the easiest way however; the colors are limited.
Range(“A1”).Interior.Color = vbYellow
Range(“A2”).Interior.Color = vbRed
Range(“A3”).Interior.Color = vbBlue
- RGB Colors – RGB which stands for Red Green Blue. To use RGB, enter a value between 0 to 255 for each color code.
Here is an example code:
Range(“A1”).Interior.Color = RGB(90, 70, 50)
Illustrated in VBA editor and Excel sheet, we’ll see:
VBA Control Fonts
We can also control the font’s attributes in VBA. The commonly used attributes are:
Bold, Color, ColorIndex, FontStyle, Italic, Name, Size, Underline, etc.
Here is the sample code for your reference:
Range(“A1”).Font.Bold = True
Range(“A2”).Font.Italic = True
Range(“A3”).Font.Underline = True
Range(“A5”).Font.Color = vbRed
Range(“A6”).Font.Name = “Arial Black”
Range(“A7”).Font.Size = 30
Let’s see our codes in action. By looking at the result of our codes, we can see that:
Text in A1 was set to Bold
A2 was set to Italic
A3 was underlined
A5 font color was set to red
A6 font name was set to Arial Black; and lastly,
A7 font size was set to 30
VBA Clearing Cells
There are different options to clear the cells in Excel. I will show you how to implement it using VBA.
The image below is the list of cell-clearing options. This suggestion box pops-up when you start typing the word ‘clear’, giving you options on what to clear in a cell.
Clear – Clears the entire specified range
ClearComments – Clears all cell comments from the specified range
ClearContents – Clears formulas and values from the range
ClearFormats – Clears the formatting from the specified range
ClearHyperlinks – Removes all hyperlinks from the specified range
ClearNotes – Clears notes and sound notes from the specified range
ClearOutline – Clears the outline for the specified range
To clear cell color, you can use this code as reference:
Cells.Interior.Color = xlColorIndexNone
VBA Columns Widths
Excel column widths can also be manipulated using VBA. This one is quite important especially in reports or data presentations when same and exact column widths are needed to display data properly.
To set the column width in VBA, use this code are reference:
Columns(“A:C”).ColumnWidth = 12
VBA Building Logic
VBA consists of several built-in operators and functions which can be used to build expressions and conditional logic. This includes mathematical, string, comparison and logical operators. So, when logical function and operators are combined into a series codes to evaluate a condition, it executes one set of code at a time, returns TRUE or FALSE as a result. Depending on the result, the following codes will continue to execute until it reaches the end of the conditional logic. Conditional statements are the If-Then with Else or ElseIf and the Select Case.
VBA IF Statement
VBA IF Statement is the most commonly used conditional statement. It allows you to evaluate for a condition then performs an action depending on the result.
IF THEN ELSE Statement simply means: “IF a condition is met, THEN what happens? ELSE, what happens when it is not met?”
Syntax # 1:
IF condition THEN code_if_true ELSE code_if_false
*Note that above statement is a simple one-line IF THEN ELSE statement where you don’t need to use the END IF statement. And that the ELSE part is optional.
Syntax # 2:
IF condition THEN
*Note that the second syntax is helpful if you have multiple lines of code to execute if the condition returns TRUE. Essentially, you need to use the END IF statement when using this syntax to avoid a compile error.
Examples of VBA IF THEN Statement
For instance, you want to check if a product’s quantity (cell B1) is under or above the minimum stock limit (10) and displays the result in cell C1.
Example#1 – One-line code:
If Range(“B1”) > 10 Then Range(“C1”) = “Above Minimum Limit” Else Range(“C1”) = “Below Minimum Limit”
Example#2 – Block of code:
If Range(“B1”) > 10 Then
Range(“C1”) = “Above Minimum Limit”
Range(“C1”) = “Below Minimum Limit”
Range(“C1”).Interior.Color = vbRed
Select Case Approach
Another logical function that you can use most specifically with three or more conditions is the Case Statement.
The Case statement performs the corresponding code for the first condition that is found to be TRUE.
Below is the syntax of Case statement:
Select Case test_expression
Once a condition is met, it will execute the corresponding code and will not evaluate the conditions any further.
Example code used in VBA:
Select Case Range(“A2”)
Case 90 To 100
Range(“B2”) = “A”
Case 70 To 90
Range(“B2”) = “B”
Case 60 To 70
Range(“B2”) = “C”
Case 50 To 60
Range(“B2”) = “D”
Range(“B2”) = “No Score Found”
The code returned “B” since score 75 falls under the case 70 to 90.
Second example, cell A2 is empty and as indicated in our Case Else, if no test_expression match any clause in all the other Case statements, then “No Score Found” will be the value in cell B2.
Start To Automate With Loops
Loops plays an essential role in controlling and handling code execution of repetitive tasks. Looping executes code repetitively until the condition is found TRUE. It is an effective way of optimizing your code in terms of speed and accuracy. There are multiple methods to loop, such as:
For – Next
For – Each – Next
Do While Loop
Do Until Loop
While – Wend
VBA For Loop
For loop is the most common loop in Excel VBA. This type of loop allows you to run the VBA code in a pre-determined number of times. Once all the statements have been executed, it goes back to the start of the structure, and that is why it is called a loop.
This is the syntax for For Loop:
For counter_variable = <start> to <end>
Sample code for your reference:
For iCtr = 1 to 10
Cells(iCtr, 1).value = “Hello”
When used in Excel VBA:
Looking at the image above, the statement between For and Next was executed 10 times. For iCtr = 1, enters the value “Hello” to Cells(iCtr, 1) which is cell A1. The code then reaches Next iCtr, and so it increments and goes back to the For statement. Now, our variable iCtr = 2, “Hello” is entered in the cell at the intersection of row 2 and column 1 (cell A2). The code executes until it reaches the end counter, which is 10, and that will be row 10, column 1 (cell A10).
Combine Logic And Loops To Automate
Let’s start combining logic and loops to automate our Excel task. Here is one good yet simple example you can start practicing on:
Dim iCtr As Integer
For iCtr = 1 To 20
If Cells(iCtr, 1) = “Apple” Then
Cells(iCtr, 1).Interior.Color = vbYellow
In this code, there is an IF statement between the For and Next statements. The IF statement evaluates the data and finds the value “Apple” in the list, and if found, colors the cell yellow. The counter 1 to 20 used as a variable for row number Cells(iCtr,1) indicates that it should loop from row 1 to 20 and then executes the statements that follow.
Here is how it should look like when added in VBA Editor.
Excel VBA: Learn the basics in a few minutes