Making If-Then Statements in Excel VBA

If-then statements are used in programming to allow a computer to execute an action based on a criterion. When this is done well, a program can do almost anything the developer wants. This post will provide an example of how to use if-then statements in Excel VBA.

Our goal is to create a subroutine in Excel VBA that determines if a number is divisible by five. In order to do this we have to do the following

  1. Create a variable to store input from the user
  2. Ask the user for a number
  3. Test if this number is divisible by 5
  4. If the answer is yes do one thing
  5. If the answer is no do something else
  6. exit the subroutine.

Below is a visual of what is described above

The Code

Below is the code for completing this task. There is no other way but to share the whole thing and then go through screenshots of each step.

Below is an explanation

The subroutine is called “divideByFive.” After this, we create a variable called x as an integer.

After creating the x variable, Excel will ask the user for a number in the input box as shown below. For this example, we inputted the number 25.

Once you input a number and press Enter and Excel will use the mod function to determine if the number is divisible by five. If the mod is zero this means there is no remainder and this triggers true for the if-then statement. Since our number is divisible by five, you will see the following which is the first message box.

If you put any number that is not divisible by 5 you will see the following, which is the second message box.

For simplicity, we skipped many steps, for example, if you type in text or press the cancel button you will get an error message.

Conclusion

If then statements can be used in your programming to achieve specific actions in your code. The example provided here is one simple way of doing this in Excel VBA

Leave a Reply