Excel VBA

Introduction To VBA: Macros And Automation Part 1

Our First Macros

Using IF Statements

Very similarly to how the IF function works, we will see how an If statement works within VBA. This requires us to create one logical comparison, and have an idea of what we want to happen depending on if the comparison is TRUE or FALSE.

To keep within the same workbook we have already been using, the cell A3 will be used as our next input, and our fourth macro will be named ExLogical.

We will define an integer variable by the name of MyNumber, and we will assign it the value found in cell A3 of the active worksheet.

Here is where our If statement will begin. Before writing it we need to know what our criteria will be. Let’s say that users are going to be inputting a value between 1-10 into cell A3, and that anytime someone puts a number above a 5, they will be congratulated. Assuming someone puts the number 5 or lower, we want to advise them of their bad luck.

Now that we have described the interactions we want to have, we can begin our If statement by defining the initial comparison.

To do that, we enter the following line of code:

If MyNumber > 5 Then

This tells Excel we are starting an If statement, and what our comparison is. We use the Then keyword so that Excel understands that on the next line we will be telling it what to do if this statement is TRUE.

We will assign a generic congratulations message to our message box for users who meet the criteria. Something like:

MsgBox "Congratulations! You passed!"

Past this line of code, we either need to define what happens next if someone put a value above 5, or describe what should happen if they do not. To let Excel understand that we are moving on to the other side of our If statement, the next line of our code is simply:

Else

That keyword alone tells Excel that for any case where the value of our variable is not greater than 5, they need to perform the action we are about to describe. As we stated earlier, we want to let users know that they have unfortunately not passed, and have bad luck.

MsgBox "Oh no! Better lucky next time."

With both parts of our If statement defined, the only thing left to do is let Excel know that we are done with this statement, and to move back on to the rest of the code.

To do this, we end the statement with the following keywords:

End If

And our macro is finished!

The last step we have is to add a button for our fourth macro and enter something into cell A3.

Try having integers both above and below the threshold we set, but we careful to not enter any text values into cell A3! This macro doesn’t feature any type of error handling or rules to follow should a user enter an invalid response.

That is something which will be introduced in a later guide, but is an extremely important aspect of any programming language. Errors, whether intentional or otherwise, are common. Without telling the computer how to treat these mistakes, your code will not function when it encounters them. This can lead to many unfortunate outcomes, especially when you are working on larger projects.