Topics covered in this Tutorial:
- Sub Routines
- Values from Cells, Writing to Cells
- String Concatenation
- If Conditions
- For Loops
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 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"
|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
|'Dim variableName as variableType|
|Dim int1 as Integer|
|Dim str1 as String|
|int1 = 1|
|str1 = "Hello World"|
Values from Cells, Writing to Cells
Add words “Hello” and “World” to cells A1 & A2 respectively
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.
|Debug.Print Cells(1, 1).Value|
|Debug.Print Cells(2, 1).Value|
Writing Values to Cells
Let’s now write “Hello” and “World” to cells B1 & B2.
Cells(Row, Column).Value = "Hello"
|Cells(1, 2).Value = "Hello"|
|Cells(2, 2).Value = "World"|
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
|Cells(3,1).Value = Cells(1, 1).Value & " " & Cells(2, 1).Value|
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.
|If <condition> then|
|Elseif <condition> then|
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
|Dim strName As String|
|strName = "Alex"|
|If strName = "John" Then|
|Debug.Print "Welcome Back" + strName|
|Debug.Print "Hello " + strName|
The output for the above example would be “Hello Alex”. This is because strName = “Alex” is not equal to “John”
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
|For i = 1 To 10|
This loop will print integers 1 to 10 in the immediate window. You can refer to the other types of loops here.