How to Use the Select Case Structure in Excel VBA

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.

Read More: How to create an Excel VBA UserForm

Introduction

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.

Read More: If-Then construct, For-Next loops, With-End With construct, Select Case construct in Excel

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 maybe 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.

Select Case VBA Excel - Image 1

1) Press Alt-F11 on the keyboard in order to enter the Visual Basic Editor (VBE) Window.

2) Go to Insert>UserForm.

Select Case VBA Excel - Image 2

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 allow the user to click on the spreadsheet and even enter values in the spreadsheet while the form is running.

Select Case VBA Excel - Image 3

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.

Select Case VBA Excel - Image 4

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.

Select Case VBA Excel - Image 6

6) Right-click the command button, added and choose View Code.

Select Case VBA Excel - Image 7

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”

 

Case Else

lblOne.Caption = “Experimental Error, redo experiments”

 

End Select

End Sub

What this code does is evaluate the variable yield, that 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.

Read More: How to Create a Body Mass Index (BMI) Calculator in Excel Using VBA

8) Now return to the worksheet, and go to Developer>Controls>Insert and click on the drop-down arrow. Under ActiveX, choose Command Button.

Select Case VBA Excel - Image 8

9) Draw a command button on the worksheet.

Select Case VBA Excel - Image 9

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.

Select Case VBA Excel - Image 1012) Close the Properties Window and with Design Mode still selected, right-click the button and choose View Code as shown below.

Select Case VBA Excel - Image 11

13) Enter the following code for the button click event:

 

Private Sub cmdshowcollagenForm_Click()

 

frmcollagenAlternative.Show

 

 End Sub

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.

Select Case VBA Excel - Image 12

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.

Select Case VBA Excel - Image 13

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.

Select Case VBA Excel - Image 14

17) Now go back to the worksheet and in cell B5, enter the value 95 and press enter as shown below.

Select Case VBA Excel - Image 15

18) Press the Submit button on the UserForm again and now A Feasible Potential Alternative Source of Collagen is shown on the label.

Select Case VBA Excel - Image 16

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

SelectCastStatementInExcelVBA

Conclusion

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.

Useful Link

Collagen: What is it and what are its uses

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.

In her spare time when she’s not exploring Excel or Access, she is into graphic design, amateur photography and caring for her two pets, Pretzel and Snoopy.

You may also like...

Leave a Reply

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

Solve the Math * Time limit is exhausted. Please reload CAPTCHA.