IF & AND Functions in Excel VBA to Test Multiple Conditions

Get FREE Advanced Excel Exercises with Solutions!

We have already seen how to use the IF & AND worksheet Functions to test multiple conditions, in a previous tutorial. We are now going to see how to use the IF statement in conjunction with the AND Function in VBA, in order to test multiple conditions.

So, let’s get started with the same example, we used previously, in order to demonstrate how to use conditional logic in VBA.

Introduction

Patients suffering from HIV/AIDS frequently face opportunistic infections, as their CD4 count decreases. Common opportunistic infections that target the immune systems of HIV/AIDS patients, include Tuberculosis, Kaposi’s sarcoma (a type of cancer that occurs in the cells that line the lymph or blood vessels), and cardiovascular disease.

Tuberculosis has been a disease that has affected mankind throughout the ages. In previous eras, Tuberculosis was known by various names such as consumption, phthisis pulmonalis, and Pott’s disease. The bacteria responsible for causing Tuberculosis, Mycobacterium tuberculosis has even been discovered in ancient Egyptian mummies spinal cords, upon analysis. Poets and authors such as John Keats and Emily Bronte were thought to have suffered from Tuberculosis.

People who have HIV/AIDS are at greater risk of picking up Tuberculosis. In 2014, there were a recorded 9.6 million new cases of Tuberculosis. 1.2 million of those cases are people living with HIV/AIDS.  When a person has both HIV/AIDS and Tuberculosis, it has been found that the Tuberculosis speeds up the rate of HIV/AIDS progression and HIV/AIDS speeds up the rate of Tuberculosis progression.

Kaposi’s sarcoma is a type of cancer that is responsible for abnormal lesions in the skin, caused by the tumor’s blood vessels leaking into the skin. The incidence of Kaposi’s sarcoma was not that high. It was during the 1980’s that doctors began to notice an increase in the rate of patients diagnosed with Kaposi’s sarcoma, and eventually, the link between HIV/AIDS and Kaposi’s sarcoma was discovered.

At a hypothetical hospital, a medical technician is responsible for registering each patient that comes into the hospital and directing them to the departments they need, in the order that they need it.

In the case of patients with HIV/AIDS, the technician first has to send the patient to Ward One to collect Antiretrovirals for HIV/AIDS treatment and then to the department dealing with the opportunistic infection.

So, for example, a patient with HIV/AIDS and the opportunistic disease Tuberculosis would first be sent to Ward One, to collect ARVS (Antiretrovirals) and then to the respiratory department of the hospital, to receive treatment and palliative care for Tuberculosis.

A patient with HIV/AIDS and the opportunistic disease Kaposi’s sarcoma would first be sent to Ward One, to collect ARVS (Antiretrovirals) and then to the oncology department of the hospital, to receive treatment for Kaposi’s sarcoma.

A patient with HIV/AIDS and cardiovascular disease would first be sent to the Ward One, to collect ARVS (Antiretrovirals) and then to the cardiology department of the hospital, to receive treatment for cardiovascular disease. If the patient does not have AIDS, but one of the other opportunistic infections only or another disease, they bypass Ward One and go directly to the department they need.

Read More: Excel If Function with Multiple Conditions (Nested IF)

The source workbook is shown below.

Test Multiple Conditions in Excel VBA

1) So, the first thing we are going to do is create a UserForm.

2) Press Alt-F11, to enter the Visual Basic Editor (VBE).

3) Once in the VBE, go to Insert>UserForm as shown below.

Excel VBA

4) If the Properties Window is not visible, go to View>Properties Window or press F4 on the keyboard.

5) Using the Properties Window, change the name of the UserForm to frmpatientRegister, the BackColor to light blue, the BorderColor to orange, the BorderStyle to 1-fmBorderStyleSingle, the Caption to Patient Register, the height to 400, and the width to 270.

6) Using the Toolbox, add a label. Using the Properties Window change the name of the label to lblconditionOne, change the BackColor to light orange, the font to Calibri, the size to 14, and the Caption to Name of Condition: as shown below.

7) Using the Toolbox, add a combo box to the UserForm.

8) Using the Properties Window, change the name of the combo box to cmbconditionOne.

9) Add another label using the Toolbox. Using the Properties Window, change the name of the label to lblhivAids, change the BackColor to light orange, the font to Calibri, the size to 14, the Caption to Does the Patient have HIV/AIDS? and change the width to 204 as shown below.

10) Add another combo box, below the second label. Using the Properties Window change the name of this combo box to cmbconditionhivAids and set the width to 90.

11) Add a textbox below the second combo box, using the Properties Window, change its name to txtResultant, set the height to 35, and set the width to 234, change the Multiline value to True as shown below. By changing the Multiline value to true, we can accommodate more text since the text will continue in the lines needed.

12) Using the Toolbox, put a button underneath the textbox, and using the Properties Window, change the name of the button to cmdSubmit, the background color to light pink, and the caption to Submit as shown below.

13) Right-click the actual UserForm and select View Code.

14) Change the event from Click to Initialize for the UserForm, using the drop-down list on the left.

15) Add the following code to the Initialize event:

Private Sub UserForm_Initialize()

 

cmbconditionOne.Clear

With cmbconditionOne

.AddItem “Tuberculosis”

.AddItem “Kaposi’s sarcoma”

.AddItem “Cardiovascular disease”

.AddItem “Other condition”

End With

 

cmbconditionhivAids.Clear

With cmbconditionhivAids

.AddItem “Yes”

.AddItem “No”

End With

End Sub

This code adds the items to the two combo boxes, as soon as the UserForm is initialized.

16) Click on the name of the UserForm, in the Project-VBAProject section in order to go back to the UserForm.

17) Double-click the Submit button on the UserForm and for the button click event, enter the following code:

Private Sub cmdSubmit_Click()

If cmbconditionOne.Value = “Tuberculosis” And cmbconditionhivAids.Value = “Yes” Then

txtResultant = “Go to Ward One to collect ARVs and then to the respiratory department”

 

ElseIf cmbconditionOne.Value = “Kaposi’s sarcoma” And cmbconditionhivAids.Value = “Yes” Then

txtResultant = “Go to Ward One to collect ARVs and then to the oncology department”

 

ElseIf cmbconditionOne.Value = “Cardiovascular disease” And cmbconditionhivAids.Value = “Yes” Then

txtResultant = “Go to Ward One to collect ARVs and then to the cardiovascular department”

 

ElseIf cmbconditionOne.Value = “Other condition” And cmbconditionhivAids.Value = “Yes” Then

txtResultant = “Go to Ward One to collect ARVs and then to the respective department needed”

 

ElseIf cmbconditionOne.Value = “Tuberculosis” And cmbconditionhivAids.Value = “No” Then

txtResultant = “Bypass Ward One, and go directly to the respiratory department”

 

ElseIf cmbconditionOne.Value = “Kaposi’s sarcoma” And cmbconditionhivAids.Value = “No” Then

txtResultant = “Bypass Ward One, and go directly to the oncology department”

 

ElseIf cmbconditionOne.Value = “Cardiovascular disease” And cmbconditionhivAids.Value = “No” Then

txtResultant = “Bypass Ward One, and go directly to the cardiovascular department”

 

ElseIf cmbconditionOne.Value = “Other condition” And cmbconditionhivAids.Value = “No” Then

txtResultant = “Bypass Ward One, and go directly to the respective department needed”

 

End If

End Sub

What this code does, is use an IF-Then statement in conjunction with ElseIf, and an AND function in order to evaluate multiple conditions and combinations. The first section:

If cmbconditionOne.Value = “Tuberculosis” And cmbconditionhivAids.Value = “Yes” Then

txtResultant = “Go to Ward One to collect ARVs and then to the respiratory department”

Translates to if the combo box called cmbconditionOne’s value is Tuberculosis and the combo box cmbconditionhivAids’s value is Yes, then once the button is clicked, the textbox called txtResultant should show Go to Ward One to collect ARVs and then to the respiratory department. Each of the ElseIf statements evaluates a different combination for the first combo box in conjunction with the second combo box’s value. In this way, we are testing multiple conditions and combinations.

Read More: Best 12 MS Excel Books for Beginners & Intermediate Users

18) Now click on ThisWorkbook in the Project –VBAProject section as shown below.

19) Select Workbook from the drop-down list on the right.

20) Add the following code for the Workbook open event.

Private Sub Workbook_Open()

 frmpatientRegister.Show

 End Sub

This code ensures that when the Workbook is opened, the UserForm is launched.

21) Save the macro-enabled Workbook and close the workbook.

22) Now open the workbook, you just saved and the form should launch immediately.

23) Now let’s test the UserForm, we created. Select the name of the condition, in this case, we’ll choose cardiovascular disease, and select whether the patient has HIV/AIDS or not.

24) Click on the Submit button to see the appropriate result delivered.

And there you have it, you can now select all the other combinations and have the results delivered.

Download Zone

Testing-Multiple-Conditions-in-VBA

Conclusion

One can test conditional logic in a worksheet using the worksheet functions, IF, AND, and OR. The IF statement and AND Function utilized in VBA provide a way to test multiple combinations while utilizing conditional logic. One can also use the IF statement in combination with the OR Function.

Please feel free to tell us if you use the IF statement and AND Function in your VBA code often.

Read More…

How to Use the For Each Next Loop in Excel VBA

How to Use the Do While Loop in Excel VBA

Changing Case using Worksheet Functions and Excel VBA

For Next Loop in VBA Excel (How to Step and Exit Loop)

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.

Taryn Nefdt
Taryn Nefdt

Taryn is a Microsoft Certified Professional, who has used Office Applications such as Excel and Access extensively, in her interdisciplinary academic career and work experience. She has a background in biochemistry, Geographical Information Systems (GIS), and biofuels. She enjoys showcasing the functionality of Excel in various disciplines. She has over ten years of experience using Excel and Access to create advanced integrated solutions.

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo