Table of Contents
A case statement in VBA is a conditional statement used to execute specific code based on the evaluation of a given expression. It follows the syntax “Select Case [expression] Case [value]: [code to execute] Case [value]: [code to execute] … End Select”. This allows for more efficient and organized coding compared to using multiple nested if statements. An example of a case statement in VBA could be as follows:
Select Case userResponse
Case “Yes”
MsgBox “Thank you for choosing yes.”
Case “No”
MsgBox “Thank you for choosing no.”
Case Else
MsgBox “Invalid response, please try again.”
End Select
This code will display a message box based on the user’s input, either “Thank you for choosing yes.” or “Thank you for choosing no.” If the input does not match any of the specified cases, an error message will be displayed.
Write a Case Statement in VBA (With Example)
A case statement is a type of statement that goes through conditions and returns a value when the first condition is met.
You can use the following basic syntax to write a case statement in VBA:
Sub CaseStatement() Dim i As Integer For i = 2 To 9 Select Case Range("B" & i).Value Case Is >= 30 result = "Great" Case Is >= 20 result = "Good" Case Is >= 15 result = "OK" Case Else result = "Bad" End Select Range("C" & i).Value = result Next i End Sub
This particular example looks at each cell in the range B2:B9 and returns the following values in the range C2:C9:
- “Great” if the value in column B is greater than or equal to 30.
- Else, “Good” if the value in column B is greater than or equal to 20.
- Else, “OK” if the value in column B is greater than or equal to 15.
- Else, “Bad” if none of the previous conditions are met.
The following example shows how to use this syntax in practice.
Example: Writing a Case Statement in VBA
Suppose we have the following dataset in Excel that shows the number of points scored by various basketball players:
Suppose we would like to write a case statement to assign a value of Great, Good, OK or Bad to each player based on their number of points scored.
We can create the following macro to do so:
Sub CaseStatement() Dim i As Integer For i = 2 To 9 Select Case Range("B" & i).Value Case Is >= 30 result = "Great" Case Is >= 20 result = "Good" Case Is >= 15 result = "OK" Case Else result = "Bad" End Select Range("C" & i).Value = result Next i End Sub
When we run this macro, we receive the following output:
Column C returns a value of Great, Good, OK or Bad based on the corresponding value in column B.
Cite this article
stats writer (2024). How do you write a case statement in VBA, and can you provide an example?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-do-you-write-a-case-statement-in-vba-and-can-you-provide-an-example/
stats writer. "How do you write a case statement in VBA, and can you provide an example?." PSYCHOLOGICAL SCALES, 24 Jun. 2024, https://scales.arabpsychology.com/stats/how-do-you-write-a-case-statement-in-vba-and-can-you-provide-an-example/.
stats writer. "How do you write a case statement in VBA, and can you provide an example?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-do-you-write-a-case-statement-in-vba-and-can-you-provide-an-example/.
stats writer (2024) 'How do you write a case statement in VBA, and can you provide an example?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-do-you-write-a-case-statement-in-vba-and-can-you-provide-an-example/.
[1] stats writer, "How do you write a case statement in VBA, and can you provide an example?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How do you write a case statement in VBA, and can you provide an example?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.