Wenn Sie viele Bedingungen überprüfen müssen oder eine Operation auf der Grundlage der Auswahl des Benutzers ausführen möchten, können Sie If Else If-Anweisungen verwenden. Aber sie sind problematisch, wenn es um mehrere Bedingungen geht. In diesem Fall ist die Methode Select Case * die beste Lösung in Excel VBA.

Was ist eine Select Case-Anweisung in VBA?

Die Select Case-Anweisungen in VBA sind nützlich, wenn Sie mehrere Bedingungen überprüfen müssen. Sie sind wie switch-Anweisungen in anderen Programmiersprachen. Es überprüft die angegebene Variable und führt den übereinstimmenden Fall aus.

Syntax von Select Case in Excel VBA

Select Case variable

case Condition1

result1

case Condition2

Condition2

case ConditionN

ErgebnisN

[Fall sonst

resultElse]

Select Case:Here Select Case* is a keyword to start the case check.

Variable: Variable is any variable that you want to check.

Case: from the next line, we start checking the conditions with variables.

You can have as many conditions as you want. We can check any condition with the keyword case. The condition can be a logical statement or a straight value of the Selected Variable. It will always result in true-false value. These all are valid statements:

Wählen Sie Fallregion

‚mit einem geraden Wert

Fall „Zentral“

Bereich („D1“). Wert = Region

‚mit einer is-Anweisung

Fall ist = „Zentral“

Bereich („D1“). Wert = Region

Ende Wählen Sie

With is you can put arithmetic logical operators to check if the Case is equal to(=), greater than(>), less than(<), etc.

You can use the comma (",") to do one operation on multiple conditions (like or operator).

Fall ist = „West“, „Nord“, „Süd“

Bereich („D1“). Wert = Region

You can also use To operator to check among large ranges.

Fall 1 bis 40

Note = „F“

Fall 41 bis 60

Note = „C“

Fall 61 bis 80

Note = „B“

Fall 81 bis 100

Note = „A“

The Case Else statement is optional. You can use it to do something by default if none of the case match. Like showing a message that the choice is invalid or something.

Fall sonst

MsgBox „Ungültige Option“

Now that we know about the basics of the Select Case, let's have an example.

===  Example: Create A Grading Function UsingVBA Select Case We need to create a function that checks the supplied value and returns the grades according to below rules:

*  If >41, Grade="F"

*  If between 41 and 60, Grade="C"

*  If between 61 and 80, Grade="B"

*  If between 81 and 100, Grade="A"

*  else #VALUE! error.

We will use the Select Case as a Switch statement of VBA here. The code for the custom function will be:

Funktion GRADES (num As Double)

Wählen Sie Fallnummer

Fall ist <41

GRADES = „F“

Fall 41 bis 60

GRADES = „C“

Fall 61 bis 80

GRADES = „B“

Fall 81 bis 100

GRADES = „A“

Fall sonst

GRADES = „#VALUE!“

Ende Wählen Sie

End Function

image:https://www.office-skill.site/images/wp-content-uploads-2020-01-Capture-43.jpg[image,width=658,height=282]

Now if you use this function on the sheet, it will return the grades easily.

image:https://www.office-skill.site/images/wp-content-uploads-2020-01-Capture-42.jpg[image,width=369,height=288]

So yeah guys, this is how the Select Case (switch) statement is used in Excel VBA to check multiple conditions. Instead of multiple If Else If statement we use the Select Case statement to switch results. If you have any doubts regarding this article or any other Excel/VBA related articles, ask in the comments section below.

Download the working file below:

image:https://www.office-skill.site/images/wp-content-uploads-2015-06-image-481.png[image 48,width=128,height=49]



===  Related Articles:

`link:/office-skill-de/using-loops-using-loops-in-vba-in-microsoft-excel[Using Loop in VBA in Microsoft Excel]`| The loops in VBA enable us to do a similar task over and over without repetition of code. There are 3 types of loops in Excel VBA.

`link:/office-skill-de/using-loops-for-loops-with-7-examples[7 Examples of For Loops in Microsoft Excel VBA]`| The 7 examples of for loop can make your automation life easy. Let's start with simple for loop.



===  Popular Articles:

`link:/office-skill-de/keyboard-formula-shortcuts-50-excel-shortcuts-to-increase-your-productivity[50 Excel Shortcuts to Increase Your Productivity]` | Get faster at your task. These 50 shortcuts will make you work even faster on Excel.

`link:/office-skill-de/formulas-and-functions-introduction-of-vlookup-function[The VLOOKUP Function in Excel]` | This is one of the most used and popular functions of excel that is used to lookup value from different ranges and sheets. `link:/office-skill-de/tips-countif-in-microsoft-excel[COUNTIF in Excel 2016]` | Count values with conditions using this amazing function. You don't need filter your data to count specific value.

Countif function is essential to prepare your dashboard.

`link:/office-skill-de/excel-formula-and-function-excel-sumif-function[How to Use SUMIF Function in Excel]` | This is another dashboard essential function. This helps you sum up values on specific conditions.