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 |
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 |
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.
| 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 |
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 |
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 |
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
