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)

Sub Test()
<statement>
End Sub()

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"
Sub Test()
Debug.Print "Hello World"
End Sub

view raw
Tut2-DebugPrint.xlsm
hosted with ❤ by GitHub

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

Dim variableName as variableType
Sub Test()
'Dim variableName as variableType
Dim int1 as Integer
Dim str1 as String
int1 = 1
str1 = "Hello World"
Debug.print int1
Debug.print str1
End Sub

view raw
Tut2-Variables.xlsm
hosted with ❤ by GitHub

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.

Sub ExtractValues()
Debug.Print Cells(1, 1).Value
Debug.Print Cells(2, 1).Value
End Sub

Writing Values to Cells

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

Cells(Row, Column).Value = "Hello"
Sub WriteValues()
Cells(1, 2).Value = "Hello"
Cells(2, 2).Value = "World"
End Sub

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
Sub StringConcat()
Cells(3,1).Value = Cells(1, 1).Value & " " & Cells(2, 1).Value
End Sub

view raw
gistfile1.txt
hosted with ❤ by GitHub

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.

Sub IfConditions()
If <condition> then
<statement>
Elseif <condition> then
<statement>
Else
<statement>
End If
End Sub

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
Sub IfName()
Dim strName As String
strName = "Alex"
If strName = "John" Then
Debug.Print "Welcome Back" + strName
Else
Debug.Print "Hello " + strName
End If
End Sub

view raw
Tut2-IfName.xlsm
hosted with ❤ by GitHub

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
Sub ForLoop()
For i = 1 To 10
debug.print i
Next i
End Sub

view raw
Tut2-ForLoops.xlsm
hosted with ❤ by GitHub

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

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s