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.noWonderLocation
1Taj MahalIndia.- Agra
2Chichen ItzaMexico.- Yucatán
3Christ the RedeemerBrazil.- Rio de Janeiro
4ColosseumItaly.- Rome
5Great Wall of ChinaChina
6Machu PicchuCuzco Region
7PetraJordan.-Ma’an Governorate
8Great Pyramid of GizaEgypt

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;amp; ask_for &amp;amp; " is " &amp;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;amp; ask_for &amp;amp; "' is not available. Please try something else."
End If

End Sub
Example in EXCEL of a Boolean value.
VBA boolean value code.

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.

VBA boolean data type program with the output on the side if we click on the “Yes” button.

The message box with “Yes” and “No” buttons.

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.

  1. Age
  2. Amount to be invested
  3. 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 &gt; 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;amp; simple_interest &amp;amp; vbCrLf &amp;amp; "The maturity amount is " &amp;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 valueOperatorResult of other condition / bool valueFinal result / bool value
TRUEAnd TRUETRUE
FALSEAnd FALSEFALSE
TRUEorTRUETRUE
FALSEorFALSEFALSE
TRUEand FALSEFALSE
TRUEorFALSETRUE

Leave a Reply

Your email address will not be published. Required fields are marked *