How to Use the Boolean Data Type in VBA
Contents
Introduction to Flag Options in Programming
In any programming language, there’s a need to set a variable to options like on/off, true/false, yes/no. This variable is then later used in the code to check or understand something else.
For example, let us imagine that we have a list of “wonders of the world” and their respective “locations” as a table in an Excel sheet.
S.no | Wonder | Location |
1 | Taj Mahal | India.- Agra |
2 | Chichen Itza | Mexico.- Yucatán |
3 | Christ the Redeemer | Brazil.- Rio de Janeiro |
4 | Colosseum | Italy.- Rome |
5 | Great Wall of China | China |
6 | Machu Picchu | Cuzco Region |
7 | Petra | Jordan.-Ma’an Governorate |
8 | Great Pyramid of Giza | Egypt |
We want to find the location of any specific wonder, i.e. Taj Mahal. Now we can loop through the wonders column of the table and once we find it, we can print (or) display the location against it.
But there is no necessity for that loop to continue with further iterations. To fulfill this, we can use a flag variable of Boolean data type that will store only “true” or “false” values. So, once the matching value is found, the flag should be set to “true” using a conditional statement like “if” and the loop can be exited/broken. This flag value can be validated using another conditional statement to see if the matching value was found in the table/it was not found in spite of searching until the end.
That’s only one example to explain the use of the Boolean data type. There are many other uses of this data type that make your programming life easier. The Boolean data type does not store the true and false values in the form of strings. The only two values allowed are true and false. But they are considered like on/off (or) yes/no. like a kind of switch.
Declare a Variable of Boolean Data Type
Syntax:
Dim <variable name>
as Boolean
Example:
Dim found_me
as Boolean
Boolean in Conditional Statements:
The output of a conditional check is always a Boolean value (true/false). Based on this output value, the control determines whether to execute the block of code under it.
Examples with Code
Purchase at a Snack Bar
Here is an example where a customer wants to buy an ice cream. Let us find out if it is available. The program tried to find it in the menu. If available, the price is displayed. If not, a message is displayed.
Sub purchase() ' Declare all required variables Dim ask_for, temp, price As String Dim available As Boolean ' receive an input from the customer ask_for = InputBox("What would you like to buy? ") 'set an initial value for the flag variable available = False ' Check if the item is available For i = 2 To 13 'use a temp variable to store the cell value temp = Sheets("Snackbar").Cells(i, 1).Value 'compare value with what the customer asked for If temp = ask_for Then 'display it's price price = Sheets("Snackbar").Cells(i, 2).Value Debug.Print "The cost of " &amp; ask_for &amp; " is " &amp; price 'change value of flag variable and exit the loop as further iterations are not required available = True Exit For End If Next i 'now display a msg if it was not found If available = False Then ' means still - the same initialized value? Debug.Print " Sorry , the item '" &amp; ask_for &amp; "' is not available. Please try something else." End If End Sub
Entry to a Carnival
In this program, a VBA message box is used with “Yes” and “No” buttons to receive an input from the user. Based on the input, the person is either permitted or restricted from entering the carnival. This example is used to indicate that benefit of having yes/no buttons that are equal to a Boolean value. The two words vbYes and vbNo are predefined vba keywords with respective values. Here we can consider vbYes value as True value of the Boolean data type and vbNo value as False value of the Boolean data type.
Sub carnival_entry() ' declare variables Dim have_ticket ' msgbox to receive only a boolean input have_ticket = MsgBox("Do you have a ticket with you?", vbYesNo) If have_ticket = vbYes Then Debug.Print "Your entry to the carnival is permitted!" Else ' if have_ticket is false Debug.Print "Your entry to the carnival is restricted!" End If End Sub
Here is the program with the output on the side if we click on the “Yes” button.
The message box with “Yes” and “No” buttons.
Calculation of Simple Interest Based on Conditions
This is a program that receives three inputs from a bank customer.
- Age
- Amount to be invested
- Duration of investment in terms of years
Based on the age entered, the rate of interest to calculate simple interest is decided. Then, using the formula (pnr/100), the simple interest and maturity amount (interest + principal amount) is calculated.
Sub simple_interest_calculation() ' declare all required variables Dim Prin, no_of_years, cut_age, roi, simple_interest, mat_amt ' Receive necessary inputs from the end user Prin = InputBox("Enter the Principle amount") no_of_years = InputBox("Enter the number of years") cut_age = InputBox("Enter the cut_age of the customer") ' Set rate of interest depending on the cut_age of the customer ( varies for senior citizens ) If cut_age > 59 Then ' senior citizens roi = 10 Else ' non- senior citizens roi = 8 End If ' Calculate the simple interest and maturity amount simple_interest = (Prin * no_of_years * roi) / 100 mat_amt = simple_interest + Prin ' Display the calculated output MsgBox "The interest amount is " &amp; simple_interest &amp; vbCrLf &amp; "The maturity amount is " &amp; mat_amt End Sub
Conclusion
Boolean statements are very important to write programs because they decide how the program control flows based on decisions (“if” statements). There can be more than one condition in a conditional statement which can be combined with and/or operators.
Here is a simple table that shows how a combination of Boolean statements works with various operators:
Result of one condition / bool value | Operator | Result of other condition / bool value | Final result / bool value |
TRUE | And | TRUE | TRUE |
FALSE | And | FALSE | FALSE |
TRUE | or | TRUE | TRUE |
FALSE | or | FALSE | FALSE |
TRUE | and | FALSE | FALSE |
TRUE | or | FALSE | TRUE |