Excel VBA: First steps

With Visual Basic Applications in Excel, you can automate workflows in Excel. This saves you time and ensures that your data is automatically kept up to date.

What is VBA in Excel?

VBA is short for “Visual Basic for Applications”, a scripting language that is available to Microsoft Office users in applications such as Excel. VBA was developed in the 1990s to standardize the various macro languages of individual applications. VBA in Excel can be used to automate regular workflows and generate practical tools for project management or arithmetic.

Like any other Office application, Excel consists of various objects, such as worksheets, tables, and cells. The methods and properties of these objects can be controlled manually or, if needed, with VBA programming. You can generate automated actions using the VBA editor, which is already integrated into the application. The individual code modules are already defined so your task is simply to put the individual modules together to create a personalized solution. Such solutions, also known as Excel macros, make it easier to organize, format or import data, among other things.

How to open VBA in Excel

Before you can start programming with VBA, you first need to open Excel VBA. You can use the key combination [Alt] + [F11] to open VBA.

Alternatively, you can also use the Excel menu bar. Click on Visual Basic in the developer tab to open Excel VBA. If the developer tab isn’t displayed, you can add tabs to your menu bar via File > Options > Customize Ribbon. Under Main Tabs, put a check next to Developer.

Excel VBA tutorial

Before creating a program in Excel with Visual Basic, you first need a basic structure, which you’ll then fill with code blocks.

Step 1: When the VBA editor opens, click on Insert > Module to insert a new module.

Step 2: A pop-up window will open. Enter Option Explicit and then Option Base 1. This will allow you to work with Subs and Functions. Subs are programs made of only commands. There are no return values. Return values are used when entering functions which is why they’re suitable for mathematical calculations. The program’s responses are displayed here in the form of dialog windows.

How to add variables

In Excel Visual Basic, variables are used in the same manner as mathematic equations. They’re not just placeholders for values, but also placeholders for character strings or objects. The use of variables is a good idea if certain elements are activated more than once. In VBA code, you need to declare a variable as a type of data. Use the following DIM command to do so: Dim [Variable] As [Data type].

For example, Dim datStart As Date tells VBA that a variable with the name datStart of data type Date should be created.

Depending on the content of your variable, you’ll need to choose the correct data type in Excel VBA. The most-used data types and declaration symbols are:

• Variant: Can contain arbitrary data, for example, numerical values, character strings, time and date values. A variant is automatically shown if the type of data is not explicitly declared. Symbol: not defined.
• Integer: Used for whole numbers of values between -32,768 to 32,767. Symbol: %()
• Long: Used for whole numbers between values of -2,147,483.648 to 2,147,483.647. Symbol: (&)
• Double: Encompasses all floating-point numbers from plus/minus 1.79 10^308. Symbol: (#)
• Boolean: These are true or false variables displayed as “True” or “False” or #TRUE# or #FALSE#
• String: Strings are character arrangements of variable or fixed length. Symbol: ($)
• Date: Used for date and time designations. Times from 00:00:00 (midnight) to 23:59:59.9999999 can be entered. The date format is MM/DD/YYYY or YYYY-MM-DD.

Most common VBA commands

Do…Loop command

Do
Commands
Loop [While/Until] Expression

The Do loop allows you to define commands that are repeated until the loop matches a predefined condition. Using while repeats the loop until the condition becomes False. If you use Until, the loop is repeated until True.

For…Next command

For Variable = [StartValue] To [EndValue] Step [Value]
Command
Next Variable

The For loop repeats commands as specified. The loop starts as soon as the specified variable is reached and increases by the value specified in Step until the final value.

If…Then…Else command

If [Expression1] Then
Command1
ElseIf [Expression2] Then
Command2
Else
Command3
End If

The If command executes a series of statements based on the validity of an expression. In the code above, if Expression1 is true, Command1 is automatically executed. If it is not true, it checks whether Expression2 is correct. If this is the case, Command2 is executed. If both expressions are not correct, this triggers Command3.

VBA programming example

The best way to learn Excel VBA is to practice using examples and exercises. The following shows a simple If…Then…Else command:

The following example uses an Excel macro to show whether candidate 142 passed a test or not. To pass, they would need to have scored at least 60 points.

The VBA code above: If Range(“b2”).Value >= 60 Then Range(“c2”).Value = “Yes” stipulates the condition that needs to answer the value in cell C2 with Yes. When you hit the play button and Run in the next window, VBA programming begins and cell C2 is auto-filled:

The example above is simple so that beginners can get a basic idea of Visual Basic in Excel. You can find many other examples online that you can use for practice.