What Is a Program in VBA?

Reading time: 2 minutes

A program is a list of instructions that the computer executes one after the other, from the first line to the last one. In VBA, these instructions are written in a procedure.

Before writing your first macro, you need to understand two things: how a line of code is read, and what makes VBA different from other programming languages.

1. The left part receives, the right part calculates

All programs in the world use this convention!

Store_The_Result = Instruction_Calculation

The sign = doesn’t mean “equal”, as in mathematics. It means “receives”. VBA reads the line in two steps:

  • First, it calculates the right part: the instruction.
  • Then, it stores the result in the left part.

This is an international convention. It is the same in all programming languages: Python, JavaScript, C and many others.

2. VBA: Visual Basic for Applications

Visual Basic is the name of the language. The A means Applications. In other words, when you create a program with Excel, you will use specific instructions that belong to Excel only.

For instance you have the keywords

  • Cells to use a cell in your code
  • Range to use a range of cells, or only one
  • Worksheets or Sheets to indicate which worksheet is used
  • Workbooks

These words only exist in Excel VBA. In Word VBA, you will find other specific words, like Document or Paragraph.

3. A first program in Excel

Let’s start with a simple example

  1. We write the value 10 in cell A1
  2. Then, in B1, we reuse the content of A1 to multiply A1’s value by 2.

In this program, we use the Range instruction to indicate that we work with Excel cells.

Sub FirstCalculation()
 Range("A1") = 10
 Range("B1") = Range("A1") * 2
End Sub

And here is exactly the same program, written with Cells:

Sub FirstCalculation()
 Cells(1, 1) = 10
 Cells(1, 2) = Cells(1, 1) * 2
End Sub
VBA program FirstCalculation in Module_First and its result in the worksheet: 10 in A1, 20 in B1

⚠️ Be careful with the order. In Range(“B1”), the column comes first (B), then the row (1). In Cells(1, 2), it is the opposite: the row comes first (1), then the column (2).

4. The limit of this program

This program works, but it is not the best way to write code. The cell references are written “in hard”: the program will always work on A1 and B1, and only on these two cells.

If your number moves to another cell, you have to change the code. If you have 100 numbers to double, you have to write 100 lines.

This is where variables are useful. They make your code flexible: the same lines of code work, no matter where your data is or how many you have. This is the subject of the next article: What Is a Variable in VBA?.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *

What Is a Program in VBA?

Reading time: 2 minutes

A program is a list of instructions that the computer executes one after the other, from the first line to the last one. In VBA, these instructions are written in a procedure.

Before writing your first macro, you need to understand two things: how a line of code is read, and what makes VBA different from other programming languages.

1. The left part receives, the right part calculates

All programs in the world use this convention!

Store_The_Result = Instruction_Calculation

The sign = doesn’t mean “equal”, as in mathematics. It means “receives”. VBA reads the line in two steps:

  • First, it calculates the right part: the instruction.
  • Then, it stores the result in the left part.

This is an international convention. It is the same in all programming languages: Python, JavaScript, C and many others.

2. VBA: Visual Basic for Applications

Visual Basic is the name of the language. The A means Applications. In other words, when you create a program with Excel, you will use specific instructions that belong to Excel only.

For instance you have the keywords

  • Cells to use a cell in your code
  • Range to use a range of cells, or only one
  • Worksheets or Sheets to indicate which worksheet is used
  • Workbooks

These words only exist in Excel VBA. In Word VBA, you will find other specific words, like Document or Paragraph.

3. A first program in Excel

Let’s start with a simple example

  1. We write the value 10 in cell A1
  2. Then, in B1, we reuse the content of A1 to multiply A1’s value by 2.

In this program, we use the Range instruction to indicate that we work with Excel cells.

Sub FirstCalculation()
 Range("A1") = 10
 Range("B1") = Range("A1") * 2
End Sub

And here is exactly the same program, written with Cells:

Sub FirstCalculation()
 Cells(1, 1) = 10
 Cells(1, 2) = Cells(1, 1) * 2
End Sub
VBA program FirstCalculation in Module_First and its result in the worksheet: 10 in A1, 20 in B1

⚠️ Be careful with the order. In Range(“B1”), the column comes first (B), then the row (1). In Cells(1, 2), it is the opposite: the row comes first (1), then the column (2).

4. The limit of this program

This program works, but it is not the best way to write code. The cell references are written “in hard”: the program will always work on A1 and B1, and only on these two cells.

If your number moves to another cell, you have to change the code. If you have 100 numbers to double, you have to write 100 lines.

This is where variables are useful. They make your code flexible: the same lines of code work, no matter where your data is or how many you have. This is the subject of the next article: What Is a Variable in VBA?.

Practice with exercises

Related articles

Leave a Reply

Your email address will not be published. Required fields are marked *