Izjava o primeru Excel VBA
Izjava primera VBA je ena od logičnih funkcij. Case Statement testira več logičnih testov in rezultat dobi na dva načina, tj. Če je rezultat ali logični test TRUE en niz rezultatov in če je rezultat ali logični test FALSE, potem drugi niz rezultatov.
Logični testi se običajno izvajajo z uporabo formul IF, najsi gre za formulo delovnega lista ali v kodiranju VBA; na obeh platformah nam ta funkcija pomaga pri številnih zapletenih izračunih. Mnogi od nas se ne zavedajo, da imamo v izjavi VBA alternativo izjavi IF, tj. „Izjava o primeru“. V tem članku so vse podrobnosti o tej logični izjavi.
Sintaksa
Spodaj je sintaksa stavka "Select Case".
Izberite primer »Vrednost za preizkus« Primer je »Logični test« Rezultat, če je primer 1 TRUE Primer je »Logični test« Rezultat, če je primer 2 TRUE Primer je »Logični test« Rezultat, če je primer 3 TRUE Primer Else Če noben od rezultati so TRUE End Select
To je skoraj podobno sintaksi stavka IF, vendar namesto uporabe ELSEIF uporabljamo primer 1, primer 2, primer 3 itd.
Primeri izjave o primeru VBA
Primer # 1
V celico A1 sem vnesel številko kot 240.
Zdaj bomo to številko preizkusili, ali je ta večja od 200 ali ne, z uporabo stavka SELECT CASE .
1. korak: Odprite izjavo Select Case zdaj.
Koda:
Sub Select_Case_Example1 () Select Case End Sub
2. korak: Ko se odpre "Select Case", moramo navesti vrednost, ki jo preizkušamo. V tem primeru preizkušamo vrednosti celice A1.
Koda:
Sub Select_Case_Example1 () Izberite obseg primerov ("A1"). Vrednost Konec Sub
3. korak: Ko je vrednost, ki jo je treba preizkusiti, dana zdaj, moramo v Excelu uporabiti logične teste z uporabo besede » Primer je «.
Koda:
Sub Select_Case_Example1 () Izberite obseg primerov ("A1"). Vrednost primera je> 200 End Sub
4. korak: V naslednji vrstici moramo vnesti vrednost »rezultat«, če je uporabljeni logični test TRUE. V polju za sporočilo potrebujemo rezultat kot »Število je> 200«.
Koda:
Sub Select_Case_Example1 () Izberite obseg primerov ("A1"). Vrednost primera je> 200 MsgBox "Število je> 200" End Sub
5. korak: V tem primeru potrebujemo le dva rezultata, zato ne bom uporabil več izjav "Primer je". Nato bom z besedo »Case Else« zaprl izjavo VBA »Select Case«.
Koda:
Sub Select_Case_Example1 () Izberite obseg primerov ("A1"). Vrednost Case je> 200 MsgBox "Število je> 200" Case Else MsgBox "Število je <200" End Sub
6. korak: Ko dobimo vse primere, moramo izjavo select case zapreti z besedo "End Select".
Koda:
Sub Select_Case_Example1() Select Case Range("A1").Value Case Is> 200 MsgBox "Number is>200" Case Else MsgBox "Number is <200" End Select End Sub
Step 7: Now run the code and see what the result we get in the VBA message box is.
The result we got is “Number is>200” because in cell A1 value is 240, which is>200.
Example #2
Now we will see some practical real-time examples of testing scores. Look at the below VBA code.
Code:
Sub Select_Case_Example2() Dim ScoreCard As Integer ScoreCard = Application.InputBox("Score should be b/w 0 to 100", "What is the score you want to test") Select Case ScoreCard Case Is>= 85 MsgBox "Distinction" Case Is>= 60 MsgBox "First Class" Case Is>= 50 MsgBox "Second Class" Case Is>= 35 MsgBox "Pass" Case Else MsgBox "Fail" End Select End Sub
Let me explain the code line by line to understand better.
First, I have declared the variable as Integer, and for this variable, I have assigned the InputBox in VBA, where a user has to enter the score between 0 and 100.
When you run the code, you will see the input box like the below, and in this input box, you need to enter the score.
Now whatever we enter in the input box will be stored to the variable “ScoreCard.”
In the next line, I have applied a select case statement to test this score.
First, it will test the ScoreCard>=85 or not. If this is TRUE, then we will get the value in the message box as “Distinction.”
Select Case ScoreCard Case Is>= 85 MsgBox "Distinction"
Similarly, in the following lines, I have applied the second test as ScoreCard>=60. If this is TRUE, then it will show the result as “First.”
Case Is>= 60 MsgBox "First Class"
Like this, I have applied other tests as well, and in the end, I have used the “Case Else” statement. If all the applied logical tests are FALSE, then we will get the result as “Fail.”
Case Else MsgBox "Fail"
Now I have supplied 68 as the score, and we should get the result as “First Class” in the message box.
Example #3 - Using the “To” keyword
In the above example, we have used student scores to arrive at the result. The same test can be conducted by using the “To” word to determine the lower limit and upper limit of the logical test.
Code:
Sub Select_Case_Example3() Dim ScoreCard As Integer ScoreCard = Application.InputBox("Score should be b/w 0 to 100", "What is the score you want to test") Select Case ScoreCard Case 85 To 100 MsgBox "Distinction" Case 60 To 84 MsgBox "First Class" Case 50 To 59 MsgBox "Second Class" Case 35 To 49 MsgBox "Pass" Case Else MsgBox "Fail" End Select End Sub
I have used the same code as above, but the only yellow-colored area I have changed here. Based on the number we type in the input box accordingly, we will get the result.
Things to Remember
- Select Case je alternativa stavku IF.
- Select Case je na voljo samo z VBA.
- V prvi vrstici »Select Case« moramo navesti le vrednost, ki jo je treba preizkusiti. Nato moramo v vrstici »Primer« uporabiti logični test. To je za razliko od našega stanja IF.
