Ok, so now we can store and edit data, what’s next? Well, just being able to modify data isn’t very useful. The real power of VBA comes in with control statements.
Control Statements:
A control statement is a piece of code that allows a program to make a decision based on a condition. Now, there really are only two conditions in programming — true and false. The key to creating working programs is being able to encode more complex logic statements into small true/false statement chains.
- If:
- The If statement evaluates two given parameters and may be accompanied by Else/ElseIF, but must be accompanied by Then/GoTo and End If.
- For:
- The For is an iterating function that must be accompanied by Next. When using this statement, a numerical iterator must be declared.
- For Each:
- The For Each is an iterating function that must be accompanied by Next. Like the For statement, an iterator must be declared; however, the iterator for this statement is going to be the same type as whatever you’re trying to iterate through whereas the For statement uses a numerical iterator.
- While:
- The While statement is a function that repeats while a condition is true and must be accompanied by Wend.
- Do:
- The Do statement is a function that repeats while a condition is true and must be accompanied by Loop. Do is actually a While, but the code inside it is executed once before the conditional is evaluated.
Operators
There are six mathematical operators and four logical operators that the If, Do, and While statements use to evaluate their parameters:
Mathematical:
- greater than (“>”)
- less than (“<“)
- equal to (“=”)
- greater than or equal to (“>=”)
- less than or equal to (“<=”)
- no equal to (“<>”)
Logical:
- and (“and”)
- or (“or”)
- exclusive or (“xor”)
- not (“not”)
Logical Explanation
We’ve all seen the mathematical operators, but the logical operators are unique to programming and electronics. The logical operators are the keys to creating the true/false statement chains I was talking about earlier.
- And:
- evaluates two parameters and returns true if both of the parameters are true or false.
- Or:
- evaluates two parameters and returns true if either or both of the parameters are true.
- Xor:
- evaluates two parameters and returns true if only one of the parameters is true or false.
- Not:
- Returns the opposite value. I.E. not of true is false and vice versa.
Code
Enough theory and explanations, let’s look at some code! I created a little table of employees and relevant information for this demonstration.
Okay, so let’s see what we can do to the data with these control statements.
Pro tip: an excellent exercise in programming is reading code and trying to imagine what it does before running it. This helps with the translation of thoughts to code.
So what does this do? Which “path” or “decision” does the program make?
Is it what you were expecting? Probably not. Why didn’t it just show 3? I mean, Cells(3, 1) is 3! It’s not. Cells(3, 1) is 2 because of the labels. Cells(4, 1) is actually 3. Let’s correct the logical error and run it again
Voila, รง’est parfait! Let’s add more!
Now we’re making use of the for loop to look through all the employees. But, the message box popping up every time is annoying. Let’s modify it a little more.
There, much better! But what if we don’t know how many employees we have? Then we should use a while loop. Take note: a while loop should be used in this instance compared to a do because there could be a case where there are no employees in the database and we wouldn’t want the code to be executed if that’s the case!
Notice how the use of the variable i — our iterator — is different. In the for statement, we set the value of i in the statement itself. With while, we set the value before we wrote the statement. On top of that, we need to manually increment the iterator with i = i + 1 otherwise the evaluation would be “While(Cells(1,1) <> “”) forever”! If you do end up getting stuck in the “infinite loop”, there’s a hotkey for a kill-switch which is control + pause/break.
Another protip: cells that have no value in the actually have an empty string —
This post is getting a little long so let’s move on to the logical operators. The two conditional statements will get you through 80% of your problems.
This is the same thing except I changed the if statement — “If Cells(i, 1) = 3 And i = 3 Then”. Now, if you run this nothing happens! Well,
Now the message box pops up twice — which I changed to show data from the table in JSON format. JSON is a DIF or Data Interchange Format that allows data to be passed from program to program in a lightweight, text-base form. It’s the most popular way to transfer information over the internet.
Why doesn’t anything pop up here? Because xor REQUIRES that one OR the other be true, but never BOTH. If I were to change this back to 3 it would have the same effect as the regular or.
Now, this one shows the data in JSON format whenever the id is not 3. When it is 3, the ElseIf branch is executed.
That’s it for the basics of control statements. And I know, it’s A LOT. But, once mastered, there’s virtually nothing you can’t do now! From here on, it’s mostly how to organize code so that’s more understandable, reusable, and efficient. You can officially say you know how to program now!
Last protip: when writing an if statement with multiple branches, make sure you organize the conditionals so that the highest priority ones are at the top. You can have conditions overlap and cause them to take the wrong path.
Thank you for taking the time to read my tutorials! There will be more to come.