VBA Basics


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 Microsoft Office such as MS-Excel, MS-Word and MS-Access every day, mastering the VBA language will help you.

It allows 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.

What are the basics of VBA ?

This tutorial teach you enough understanding on VBA to have a higher level of expertise.

You will learn to create a macro, manipulating Excel objects, use conditions and loops, interact with a user, understanding the principles of debugging.

“Technology is nothing. What’s important is that you have a faith in people, that they’re basically good and smart, and if you give them tools, they’ll do wonderful things with them. “

Steve Jobs
Porquerolles Island in Hyères, France – Photo taken by me

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. You can also 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 :


click on File in the Ribbon –> click on Options at the bottom on the tab –> select Customize Ribbon –> tick the box Developer.

2. Module, Procedure & Class Module

Your project is divided into Folders in the VBA Project Explorer.

The Microsoft Excel Object folder contains sheets and a ThisWorkbook file which is your workbook object.

You can write codes in these objects.

The Modules folder contains all Modules of your project.

I recommend you to write your code in 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.

VBA is an object-oriented programming language. 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 experiences.

For more details on class objects, you can consult the following article.

You can create other folders of Class Module, right-click in the Project Explorer -> Insert -> Class Module.

To remove, insert, export a module, right-click in the Project Explorer -> Insert or Import or Export.

Within each module, write your procedures. Each procedure is surrounded by the following words Sub and End Sub.

Sub NameOfYourProcedure ()
   '...
   'Your Code
   '...
End Sub

3. My First Code

Let’s create your first macro!

Open VBA Editor –> insert a module –> rename your module –> write a procedure using Sub. 

In this example, write the basic test code of any programmer, the classic : Hello World.

You can write a value in the range “A1” of your sheet and 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 you have written our first code, run it. There are several ways to run code.

  • In the IDE you can click on the Run button, to launch the procedure.
  • In the editor 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 to launch code is clicking on a button 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, a useful method available in the Excel tool is the Record Macro. This functionality records every task you perform manually 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. When 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 to click a second time on the button Stop Recording.

Open the VB Editor after recording your tasks 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 as workbooks, sheets, ranges, cells. These objects are easily accessible through code and have many methods and properties.

The code below manipulates these Excel objects. The procedure creates a new workbook, renames the sheet, writes a unique value in a range.

Sub Excel_Object_Manipulation()

    '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

For more details around the handling of these Excel objects with many properties, consult the articles Range & Cells, Sheet, Workbook, Row and Column.

7. Define a variable

In a programming language, a variable is storage space for a result. The variables are diverse and take different forms.

In VBA, variables are declared at the beginning of your code using the Dim As. The most used types are String, Integer, Double, Long, Boolean, Sheet, Workbook, Object, Collection. When the type of your variable is not known in advance, you can declare it as a Variant. You can 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 :

=Equal to
<>Different from
<Less than
>More than
<= 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.

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!

Advertisement