INTRODUCTION-GOTO
We discussed the IF THEN ELSE construct which helps us to take a decision. Similarly there are few more methods to control the execution as we want.
For example , select case , in which we can deal with the coding on the basis of case to case as per the input.
And goto statement which lets us take the control anywhere in the program at once.
Let us check the GOTO Statement first.
Goto statement changes the flow of the program as we want. We just need to give a LABEL to a particular part of the program. This can be called anytime or can be put in the conditions.
GOTO statement can’t take us out of the procedure. Both label and goto should be in the same procedure.
SYNTAX OF GOTO STATEMENT
SYNTAX is a particular way of writing the programs.
Here is the syntax of GOTO STATEMENT
GOTO LABEL
LABEL:
GOTO can be used as an action anywhere in the program for example after an if condition.
LABEL is written in the same way as NAMEOFTHELABLE:
Goto LABEL is used to throw control to the LABEL at once.
Goto should not be used a lot and mainly should be used for error handling only.
Let us understand this with the use of an example.
EXAMPLE -GOTO STATEMENT
The example finds out the loan eligibility and tells about the interest rate applicable.
The plan is to check the non eligibility before any execution so that resources are not wasted if the person is not eligible for the loan. If a person is eligible the control would move through the IF statements and decide the applicable interest rate.
CODE
'GYANKOSH.NET Check if its weekend today or not
'SIMPLE IF
Sub IfExample2()
Dim income As Double
income = InputBox("Enter your income")
Dim interest As Integer
If income <= 100000 Then
GoTo NOTELIGIBLE
ElseIf income > 100000 And income <= 1000000 Then
interest = 5
ElseIf income > 1000000 And income <= 2000000 Then
interest = 10
ElseIf income > 2000000 Then
interest = 15
End If
MsgBox ("You are eligible for the loan and the applicable interest will be " & interest & "%")
Exit Sub
NOTELIGIBLE:
MsgBox ("You are not eligible for loan")
End SubEXPLANATION
The code starts with the declaration of income variable which is a double due to the range used.
The input is taken from the user and put in the income.
The income is checked and if its less than equal to 100000, the goto statement is used to directly send the control to the end of the program and message is shown “You are not eligible for loan”.
If the guy is eligible, the income is checked for the interest rates according to slab and message is displayed for the interest rate.Look at the picture below for the running of the program.
RUNNING THE CODE AND OUTPUT
We run the program.The program asks for the income.
The income is through the code and the output is shown as per programming.
At first income is put as 50000 , so the Excel says that you are not eligible for the loan
After that 500000 and 2500000 are put and applicable interest rates are shown .
SELECT CASE IN VBA
INTRODUCTION
Select Case in VBA is again a standard kind of switch case found in different programming languages
“SELECT CASE FACILITATES US TO DEFINE DIFFERENT CASES AND DIRECTLY SWITCH TO THEM FOR FURTHER PROCESSING.”
All the different cases are defined with different kind of processing. The case is selected as per the condition.
The expression is evaluated and the case is referred for processing.
Let us check out the syntax of SELECT CASE in VBA.
SYNTAX OF SELECT CASE
Select Case EXPRESSION_TO_BE_TESTED
[ Case expressionlist-n [ statements-n ]] ‘ CASES ONE BY ONE AND STATEMENTS
[ Case Else [ elsestatements ]] CASE FOR WHICH NO CRITERIA IS GIVEN
End Select
EXPLANATION-SYNTAX
Syntax of SELECT CASE is given above.
The expression starts with SELECT CASE after which expression is given (which is to be evaluated).
After this is a list of different cases with conditional expressions followed by statements for that particular block.
At the end is the Case else which contains the OTHER OPTIONS which are not enlisted.Ended with END SELECT.
EXAMPLE -SELECT CASE STATEMENT
The example here finds out the eligibility for the loan and the applicable interest rate.
The different slabs are
income <100000 NOT ELIGIBLE
>100000 TO <1000000 5%
>1000000 TO 2000000 10%
>2000000 15%
Let us try to program this.
CODE
'GYANKOSH.NET SELECT CASE
'CHECKING OUT THE LOAN ELIGIBILITY AND SHOWING THE APPLICABLE INTEREST RATE
Sub SelectCaseExample()
Dim income As Variant
income = InputBox("Enter your income") ‘input for the income
Dim interest As Integer
Select Case income ‘ select case
Case ""
Exit Sub
Case Is <= 100000 ‘ different cases
GoTo NOTELIGIBLE
Case 100000 To 1000000
interest = 5
Case 1000000 To 2000000
interest = 10
Case Else
interest = 15
End Select
MsgBox ("You are elgible for the loan and the applicable interest will be " & interest & "%")
Exit Sub
NOTELIGIBLE:
MsgBox ("You are not elgible for loan")
End SubEXPLANATION
The code starts with the input of income.Input is take through a INPUT BOX.
The income level switches the cases.
Different cases are with the expressions.
After the expressions INTEREST RATE is set.
All other cases are put in CASE ELSE.
GoTo statement is also used .
The CASE SELECT is a good substitute for IF ELSE.
Let us run the code.
RUNNING THE CODE AND OUTPUT
We run the program.
The program asks for the income.
The income is through the code and the output is shown as per programming.
At first income is put as 5000 , so the Excel says that you are not eligible for the loan After that 900000 is put and interest is shown as 5% Finally 2100000 are put and applicable interest rates are shown as 10 and 15 % respectively.
