VBA Tutorial 2: the Basics

Topics covered in this Tutorial:

Sub Routines
Variables
Values from Cells, Writing to Cells
String Concatenation
If Conditions
For Loops

Sub Routines

Any code begins with Sub and ends with End Sub. All your code will be within these two lines. It’s as simple as saying Hi (Sub) and Bye (End Sub).

The name of the program below is Test and will always be accompanied by parentheses. The parentheses form a passage to obtain values from outside and pass it into the program Test. (Beyond Requirement, JFYI)

Variables

Variables store values. Some of the common variables are Integers, Strings, Double, Float, and Boolean. For this series, we will only use Integers and Strings. Integers store whole numbers, while Strings hold text (A-Z, a-z, 0-9)

Remember we opened the immediate window in Tutorial 1? We use Debug.Print to print a value on the immediate window. The words Hello World are in inverted commas because they are text/string. If run without inverted commas, it will recognize Hello World and two variables named Hello & World.

Debug.print "Hello World"

To introduce a variable to the computer, we use Dim. Dim declares and allocates storage space for a variable.

Dim variableName as variableType

Values from Cells, Writing to Cells

Add words “Hello” and “World” to cells A1 & A2 respectively

Tutorial2 01 Setup.gif

Extracting Values from Cells

Debug.Print Cells(Row, Column).Value

To obtain the value for cell A1, row = 1 & column = 1.
To obtain the value for cell A2, row = 2 & column = 1.

Go to VBA editor and type the following code on Sheet1.

Writing Values to Cells

Let’s now write “Hello” and “World” to cells B1 & B2.

Cells(Row, Column).Value = "Hello"

String Concatenation

Let’s now combine both values in A1 and A2 together and write it to cell A3.

To join two strings together, simply use the ampersand and add a space between the two strings ” “.

Debug.Print str1 & " " & str2

If Conditions

In simple English, If a condition is matched then do something, Else do something else. An If condition will always end with an End If.

Let’s do a simple exercise.

Define strName as a string variable
Set strName as your name
If strName = “” then, print “Welcome Back ” + strName
Else print “Hello ” + strName

The output for the above example would be “Hello Alex”. This is because strName = “Alex” is not equal to “John”

For Loops

For Loop is a commonly used loop in VBA. Loops are used when you want to repeat a certain code for repeatedly.

for i = 1 to 10
    <statement>
next i

This loop will print integers 1 to 10 in the immediate window. You can refer to the other types of loops here.

Next: Tutorial 3 The Intermediate