One can implement conditional logic in VBA using an IF statement, multiple IF-Elseif statements or one can use the Select Case statement in order to implement conditional logic. In the case where one has multiple conditions, it is sometimes easier to use the Select Case Statement.
So, let’s get started with a simple example in order to demonstrate how to use the Select Case Statement in VBA.
Collagen is the most abundant structural protein and is found in the connective tissue of animal skins and bones. Collagen is utilized in the food industry, the cosmetic industry, the biomedical industry, and the pharmaceutical industry. It is extracted mainly from the skin of cows and pigs. However, due to concerns about diseases such as foot-and-mouth disease and bovine sponge encephalopathy, researchers have started to investigate alternative collagen sources such as fish.
In our example, a hypothetical researcher has set up an experimental design that looks at evaluating different types of fish and their yields of collagen upon extraction/isolation.
If the fish source yields between 0 g and 30 g of collagen per 100 g of source material used for extraction, then the source is deemed not a good alternative source of collagen, if the source yields between 31 g and 49 g of collagen per 100 g of the source material used for extraction, then the source is designated as a potential alternative perhaps, but a different method of extraction and isolation is needed. If the fish source yields between 50 g and 99 g, per 100 g of source material used for extraction, then the source is deemed as a feasible potential collagen alternative.
So basically the conditions are:
- If the collagen yield is between 0 and 30 g of collagen, then the source is deemed not a good alternative source.
- If the collagen yield is between 31 g and 49 g of collagen, then the source is deemed may be a potential alternative source but a different method of isolation/extraction is needed.
- If the collagen yield is between 50 g and 99 g of collagen, then the source material is deemed a feasible potential collagen alternative.
- Else there was an experimental error since that would mean one would have obtained a result of greater than 100 g for a yield which was 100 g or 100 g exactly which is not possible.
The example worksheet is shown below.
1) Press Alt-F11 on the keyboard in order to enter the Visual Basic Editor (VBE) Window.
2) Go to Insert>UserForm.
3) Using the Properties Window, change the name of the UserForm to frmcollagenAlternative, the BackColor to light yellow, the Caption to Alternative Collagen Source Experiment, the height to 400, and the width to 300. Set the ShowModal property to False. What this does (changing the ShowModal property to False) is to allow the user to click on the spreadsheet and even enter values in the spreadsheet while the form is running.
4) Using the Toolbox, add a label to the UserForm. Using the Properties Window, change the name of the label to lblOne, the BackColor to light gray, clear the Caption, set the height to 24 and the width to 282.
5) Now add a button below the label using the Toolbox. Using the Properties Window, change the name of the button to cmdSubmit and the Caption to Submit.
6) Right-click the command button, added and choose View Code.
7) Enter the following code:
Private Sub cmdSubmit_Click()
lblOne.Caption = ““
Dim yield As Integer
yield = Range(“B5”).Value
Select Case yield
Case 0 To 30
lblOne.Caption = “Not a Good Alternative Source of Collagen”
Case 31 To 49
lblOne.Caption = “Maybe a Potential Alternative Source of Collagen, but another extraction method is needed”
Case 50 To 99
lblOne.Caption = “A Feasible Potential Alternative Source of Collagen”
lblOne.Caption = “Experimental Error, redo experiments”
What this code does is evaluate the variable yield, which is given an integer data type. The actual yield value is obtained from user input in cell B5. The Select Case statement then evaluates all of the different conditions and based on the different conditions, the label’s caption changes according to the criteria mentioned and evaluated in the Select case structure.
8) Now return to the worksheet, and go to Developer>Controls>Insert and click on the drop-down arrow. Under ActiveX, choose Command Button.
9) Draw a command button on the worksheet.
10) With the button selected and Design Mode on the Developer Tab, selected. Choose Properties.
11) Using the Properties Window, change the name of the button to cmdshowcollagenForm and the Caption to Evaluate Collagen Yield.
12) Close the Properties Window and with Design Mode still selected, right-click the button and choose View Code as shown below.
13) Enter the following code for the button click event:
Private Sub cmdshowcollagenForm_Click()
14) Return back to the worksheet and with design mode deselected, click on the Evaluate Collagen Yield button in order to launch the UserForm we designed earlier.
15) Since we set the ShowModal Property to False, we can now engage with the worksheet while the form is running. So enter the value 15 in cell B5, in the worksheet, and press enter.
16) Now press the Submit button on the actual UserForm and the label caption: Not a Good Alternative Source of Collagen is delivered as shown.
17) Now go back to the worksheet and in cell B5, enter the value 95 and press enter as shown below.
18) Press the Submit button on the UserForm again and now A Feasible Potential Alternative Source of Collagen is shown on the label.
So now we see that the Select Case statement is working and all the conditions are being evaluated accurately for the conditions specified in the experimental design.
And there you have it.
Download Excel File
The Select Case statement is an alternative to the IF-Then-Else, IF-ElseIf statement when it comes to evaluating multiple conditions and returning different results. It can sometimes be much more efficient and simpler to use when one has multiple conditions.
Please feel free to comment and tell us if you prefer using Select Case or multiple IF statements in your VBA code.
- How to Use ActiveX Controls in Excel (Step by Step)
- How to Create a UserForm: an Overview
- How to Use Excel VBA User Defined Function in Formula
- 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)
- 6 Best Excel VBA Programming Books (For Beginners & Advanced Users)