In computer science, VBA language is called a scripting language. It automates many tasks on Microsoft Office software. If you work with the applications of the Office suite such as MS-Excel, MS-Word and MS-Access every day, mastering the VBA language can only help you. It will allow you to optimize many processes and save time.
This article is for the beginners to introduce you to the fundamental concepts of VBA programming in Excel through 12 key steps. This tutorial will teach you enough understanding on VBA from where you can take yourself to a higher level of expertise. You will be able to create a macro manipulating Excel object, use conditions and loops, interact with a user, understanding the principles of debugging.
1. VBA IDE
The Integrated Development Environment (IDE) of VBA is included in the Excel Application. To access the IDE click on tab “Developer” of your Ribbon and select “Visual Basic” (to go faster use the keyboard shortcut Alt + F11 for Windows to open it or Fn + Alt + F11 for Mac OS users).
To turn on the “Developer” tab, execute the following steps (for Windows) :
click on File in the Ribbon –> click on Options at the bottom on the tab –> select Customize Ribbon –> tick the box Developer.
Now, you have access to the IDE :
2. Module, Procedure & Class Module
Your project is divided into folders in the VBA Project Explorer.
We have the Microsoft Excel Object folder which contains the different sheets of the workbook and a file which is the workbook object called ThisWorkbook. It is possible to write code in these objects.
We have the Modules folder which contains all Modules that you will create in your project. I recommend you to write your code in several modules and rename your Module with the most explicit names possible. A good practice is to be as explicit as possible in all the names you assign in the project. It is important to divide your code into several modules according to each use.
To create new modules: right-click in the Project Explorer -> Insert -> Module.
To rename a module click on it, then in the “Properties” window change the name of your Module.
We can create other folders like Class Module: right-click in the Project Explorer -> Insert -> Class Module. VBA is an object-oriented programming language because it allows you to create your custom objects thanks to class modules. The creation and manipulation of class objects are advanced concepts of the language and are suitable for users with some experience. For more details on class objects, you can consult the following article.
To remove, insert, export a module: right-click in the Project Explorer -> Insert or Import or Export.
Within each module, we write what we call procedures or subroutines. Each procedure is surrounded by the following words ‘Sub’ and ‘End Sub’:
Sub NameOfYourProcedure () '... 'Your Code '... End Sub
3. My First Code
That’s it, let’s create our first macro!
To do it nothing more simple: open VBA Editor –> insert a module –> rename your module –> write a procedure using Sub. In this example, we will write the basic test code of any programmer, the classic “: Hello World !“. We write a value in the range “A1” of the sheet and we display the value with a dialog box for the user :
Sub DisplayHelloWorld() Range("A1").Value2 = "Hello World !" 'write on a cell MsgBox Range("A1").Value2 'display a dialog box with the value of the range A1 End Sub
You can also launch a procedure by calling it in another procedure :
Sub MainRun() DisplayHelloWorld ' launch the procedure "HelloWorld" if in the same module 'AnotherModule.HelloWorld launch the procedure "HelloWorld" if in another module End Sub
4. Launch a Macro
Now that we have written our first code we will see how to run it. There are several ways to run code.
- In the IDE you can click on the “Run” button, it will launch the procedure in which your cursor is located:
- In the same way as before you can use the keyboard shortcut F5 in the IDE
- If you are on the Excel workbook interface: click on the Developer tab –> click on Macro –> select your Module.NameOfYourProcedure –> Run
- One of the most interesting methods is to launch code by clicking on a button that you have created and designed. You can attach the procedure launched to shapes or icon objects in Excel. You can designate these objects through the interface: create a shapes object or insert an icon -> right-click on it -> select assign macro your ‘Module.Procedure’. You can now launch your VBA code by clicking on the object in your sheet.
You now have your first code procedure running by clicking on your design button.
Et voilà !
5. Record a Macro
Now that you know how to write and run a macro, we will see a useful method available in the Excel tool: “Record Macro“. This functionality records every task you perform with Excel. In the “Developer” tab, next to the access to the IDE, you have a button named “Record Macro”.
When you click on it, a window will go out and you can give your macro a name. The moment you click on “OK“, Excel will record all your actions in a procedure. Each manual action will be transcribed in code. When you no longer wish to record, all you have to do is click a second time on the button which will now be called “Stop Recording“.
Open the Visual Basic Editor after recording the task to see how it can be programmed. All manual actions are recorded.
As you will see in the article on the good practices to gain efficiency on the language, it is not recommended to use the Select and Activate methods. The macro recorder will not give you the best results in terms of performance but can be very useful in finding the syntax of certain properties and methods.
6. Interactions with Excel Object : Workbook, Sheet, Range, Cells
An essential point of programming in VBA is to be able to interact with the various Excel objects. The main ones are workbooks, sheets, ranges, cells. These objects are easily accessible through code and have many methods and properties.
The code below manipulates the Excel objects. The procedure creates a new workbook, renames the sheet, writes a unique value in a range, and then writes a value in a cell.
Sub ExcelObjectManipulation() 'Declaration Dim WbNew As Workbook Dim ShAnalysis As Worksheet 'Create a new workbook Workbooks.Add Set WbNew = ActiveWorkbook 'Rename the sheet1 Set ShAnalysis = WbNew.ActiveSheet ShAnalysis.Name = "Analysis" 'Rename the tab 'Write the same value in multiple cells ShAnalysis.Range("A1:F10").Value2 = "Test" 'Write in the Cell C3 Cells(3, 3).Value2 = "Hello World" End Sub
7. Define a variable
In a programming language, a variable is storage space for a result. The variables are diverse and can take different forms. Each variable has a type that defines it. The variables are declared at the start of the code using the “Dim As“. The most used types are String, Integer, Double, Long, Boolean, Sheet, Workbook, Object, Collection… When the type of the variable is not known in advance, it is possible to declare it as a Variant. It is possible to declare the variables of the same types on the same line.
The example below retrieves the values entered in the Excel sheet and calculates the Body Mass Index (BMI) of a person stored in the BMI variable, then displays it on the screen using a dialog box.
Declare variables is not mandatory but strongly recommended. It makes the code clearer and more explicit.
It is possible to force variable declarations by placing “Option Explicit” at the top of the module. In that case, if a variable declaration is missing the macro will not be executed. An error will appear.
It is also possible to create your variables. For that, I invite you to consult the following type article.
8. Conditions (If, Select)
Conditions are very useful in programming, and we will be used to perform actions according to precise criteria.
Below the using of an “If” condition in VBA :
If ... Then 'if the condition is true then 'Instructions if true Else '... if the condition is false then 'Instructions if false End If
To compare numeric values the following arithmetic operator can be used :
|<=||Less than or equal to|
|>=||More than or equal to|
The comparison with logical operators can also be used : AND, OR, NOT, MOD.
For multiple conditions we also use the operator “ElseIf” :
If ... Then ' 'if the condition 1 is true then 'Instructions 1 ElseIf ... Then 'ElseIf the condition2 is true then 'Instructions 2 Else ... 'else if the condition 1 and 2 are false 'Instructions 3 End If
Only if you have one code line after “Then” and no “Else”, it is allowed to place a code line directly after Then without “End If” :
If ... Then 'Instructions
An alternative to “If” statements containing a lot of “ElseIf” exists, and that called Select. Below an example of using a Select condition:
9. Loops (For, For Each, While, Until)
In programming, loops allow instructions to be repeated a defined number of times and therefore save time and efficiency. It is one of the most powerful programming techniques. We mainly use “For” and “While” loops. You can use a loop in a loop.
Below is the use of the classic “For” loop which automatically increments the counter “i” on each loop:
Sub For_Example() Dim i As Integer For i = 1 To 8 Cells(i,1).Value2 = i Next i End Sub
A “For Each” loop is used to iterate through each item in a set of items. For example: iterate through each cell in a range of cells, iterate through each sheet in a workbook, iterate through each key in a dictionary, iterate through each item in a collection, iterate through each element of an array…
Sub ForEachSheet_Example() 'Loop on each sheet in the Workbook 'Declaration Dim Sh As Worksheet For Each Sh In Worksheets MsgBox Sh.Name 'Display the name of each sheet Next Sh End Sub
A “While” loop allows you to continue a series of instructions as long as the previously defined condition is not verified. A “While” loop is written as follows :
Sub DoWhile_Example() 'Declaration Dim Num As Integer 'Initialisation Num = 1 'If Num <=5 the loop is repeated Do While Num <= 5 Cells(Num, 1).Value2 = Num 'write in the specific cell the Num value Cells(row, column) Num = Num + 1 'incrementation of Num until 5 Loop End Sub
Rather than repeating the loop while the condition is verified, it is possible to exit the loop when the condition is verified by using “Until”:
Sub DoUntil_Example() Do Until ... 'Instructions Loop End Sub
Sometimes, it can be useful to save time to force the exit of a loop earlier. In that case, we use the following commands: “Exit For“, “Exit Sub“, “Exit Do“, “Exit Function“.
Sub ExitFor_Example() 'Declaration Dim i As Integer 'Loop on 50 by step 1 For i = 1 To 50 'Condition if the cells is equal to "OK" we exit the loop before the end If Cells(i, 1).Value2 = "OK" Then MsgBox "The cell " & i & " is equal to OK !" Exit For 'Exit the loop End If Next i End Sub
10. User Interactions through Dialog Boxes (MsgBox, InputBox)
Dialog boxes are objects used to interact with users. The codes above have already shown you the use of the MsgBox dialog box. This dialog box is also configurable because it is about a function that can take several parameters in arguments.
MsgBox [TEXT], [BUTTONS], [Tittle]
Text: text of the dialog box
Buttons: choice of buttons (Yes, No, Ok, Cancel…)
Title: title of the dialog box
The second argument “Buttons” of MsgBox can take different values: “vbOKOnly”, “vbOKCancel“, “vbAbortRetryIgnore“, “vbYesNoCancel“, “vbYesNo“, “vbRetryCancel“, “vbCritical“…
Depending on the user’s response, the following values may be returned by a MsgBox : “vbOK“, “vbCancel“, “vbAbort“, “vbRetry“, “vbIgnore“, “vbYes“, “vbNo”.
Similar to the MsgBox function, the InputBox function asks the user to enter a value in a dialog box and outputs it as a variable that can be used in our code:
Sub InputBox_Example() Dim Height As Variant Height = InputBox("Height ?", "Height") MsgBox Height, vbOKOnly, "Your Height" End Sub
11. Functions & Procedure with arguments
A function is different from a procedure in that it returns a value while a “Sub” cannot. The arguments of a function are written in parentheses.
Function square(nb As Double) 'Square function which return the value of square square = nb ^ 2 End Function Sub Square_Example() 'Declaration Dim result As Double result = square(42.33) MsgBox result 'Display result (the square of 42.33) End Sub
A procedure taking arguments as input is used to transmit and modify the value of these variables. It is possible to add optional arguments by specifying them in the procedure. If not specified, the arguments are passed by default : by reference (ByRef). In programming, passing by reference means that if the variable is modified in the sub–procedure, it will also be modified in the calling procedure. Unlike passing by value (ByVal) which means that if the variable is modified in the sub–procedure it will not be modified in the calling procedure.
12. Debugger and Add Watch
At this point, you are now able to write and run code in VBA according to your needs. An important aspect of programming concerns debugging. Indeed, you will quickly see that after having written many lines of code, the execution will not give what you expected. It will therefore be necessary to debug your code.
The VBA editor will help you. It is possible to put what are called breakpoints. The code will run to the breakpoint you specified and pause at the selected location. You will therefore have the hand to observe the execution of the lines before your stop.
Then, you will be in Debug mode. During this mode, you can pass your cursor over a variable to see its value.
You can also select a variable: right-click -> Add Watch. You will then have access to the Add Watch table which gives you more details on the selected variable.
You can see its type, its value, the methods and properties associated. All of these elements can help you to find the error hiding behind your code.
That’s it! Now, you know all the basics of the VBA language, of course, it’s not over … The language is vast. There are a lot of objects that could be very useful to you. In particular dictionaries, class modules, communication with other tools of the Microsoft Office suite such as Outlook, PowerPoint, communication with other languages like Python, and many other tips. The articles on this blog will help you go beyond the basics and make you an experienced developer!
And don’t forget that most of the learning, of course, is in doing!