GO TO AND SELECT CASE IN VBA

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 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 Sub

EXPLANATION

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 Sub

EXPLANATION

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.