Visual Basic for Applications

Introduction

VBA stands for Visual Basic for Applications. It is a programming language developed by Microsoft that is integrated into various Microsoft Office applications, such as Excel, Word, PowerPoint, and Access. VBA allows users to automate tasks, create custom functions, and enhance the functionality of Office applications by writing scripts or macros.

VBA code is written within the Office application environment and can access the application's object model, allowing for direct manipulation of elements such as worksheets, cells, documents, slides, and more. This makes VBA a versatile tool for a wide range of tasks, from simple data manipulation to complex automation workflows.

Set up

To be able to code within Excel in VBA, you need to have the Developer tab.

If you don't have it:

• Go to the tab File;
• Click on Options;
• Click on Customize Ribbon;
• On the right, in the Main Tabs, select Developer;
• Click on OK.

Now you can go to the tab Developer:

• Click on Visual Basic to open the editor;
• Click on Insert and then select Module;
• There you can write your VBA code.

First example

• Write the following code in an empty VBA module:

📋 Interpreter

Sub example()
    Sheets("Intro VBA").Cells(11,2) = "Hello World"
End Sub

• Click the Run Sub button
• Check the result in the Intro VBA sheet.

Modify a cell

To modify a cell in a specific sheet:

📋 Interpreter

Sheets("worksheetName").Cells(row,colum) = "content"

Your content can be a known value or it can be a reference to a different cell.

Example

📋 Interpreter

Sub example2()
    Sheets("Intro VBA").Cells(12,2) = Sheets("Participants").Cells(36,1)
    Sheets("Intro VBA").Cells(12,3) = Sheets("Participants").Cells(36,2)
End Sub

Conditional

The syntax for the conditional in VBA is the following:

📋 Interpreter

If condition Then
    processing
ElseIf condition Then
    processing
Else
    processing
End If

Let's do a concrete example:

Example

📋 Interpreter

Sub example3()
    If Sheets("Intro VBA").Cells(11, 2) = "Hello World" Then
        Sheets("Intro VBA").Cells(13,2) = "There is Hello World two rows above me"
    Else
        Sheets("Intro VBA").Cells(13,3) = "Hello world is not there..."

    End If
End Sub

Help for the project

To check if a cell is not empty, you can write:

📋 Interpreter

If Sheets("Participants").Cells(2,9) <> ""

Loop

The syntax for the loop in VBA is the following:

📋 Interpreter

Do until condition
    processing
Loop

Let's do a concrete example:

Example

📋 Interpreter

Sub example4()
    rowP = 2
    rowI = 14
    Do Until Sheets("Participants").Cells(rowP, 1) = ""
        Sheets("Intro VBA").Cells(rowI, 1) = Sheets("Participants").Cells(rowP,1)
        rowP = rowP + 1
        rowI = rowI + 1
    Loop
End Sub

Sorting Data

When sorting using VBA, you need to use the Range.Sort method.

The Range would be the data that you’re trying to sort. For example, if you’re sorting the data in A1:A10, then Range would be Range(“A1:A10”).

With the sort method, you need to provide some additional information through parameters. Below are the key parameters you need to know:

• Key – here you need to specify the column that you want to sort. For example, if you want to sort column A, you need to use key:=Range(“A1”)
• Order – here you specify whether you want the sorting in an ascending order or the descending order. For example, if you want the sorting in ascending order, you will use Order:=xlAscending
• Header – here you specify whether your data set has headers or not. If it has headers, the sorting starts from the second row of the data set, else it starts from the first row. To specify that your data has headers, you will use Header:=xlYes

1. Single Column Without Header

📋 Interpreter

Range("A1:A12").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlNo

2. Single Column With Header

📋 Interpreter

Range("DataRange").Sort Key1:=Range("C1"), Order1:=xlDescending

3. Sorting Multiple Columns With Headers

📋 Interpreter

Range("A14:B498").Sort Key1:=Range("A14"), Header:=x1Yes

Clearing Data

You can clear data in VBA using the ClearContents method. You only have to specify the range that you want to clear.

For example:

📋 Interpreter

Sheets("Attendance").Range("A10:E1000").ClearContents