Jumping to the end of IFs-statements when condition met at first IF

Question

Following macro get input (Ticker) and translate it into Countries Name. After getting input it go through all If conditions. how could I stop macro from going through all Ifs when condition met at first If?

For example: Macro get input "BU" after first If variable UT becomes "BULGARIA" NOW I need to stop here and jump at the end of last If. how it is done? Is there any other way to tackle this kind of situation?

ST:
T = InputBox("Country Ticker?", "Finding country name using a Ticker", "Write Country Name to Transfer Data")

ut = UCase(T)
'Dim C As Integer
C = Len(T)

If T = "Write Country Name to Transfer Data" Then
MsgBox "No Country Name Input"
GoTo ST

ElseIf ut = "" Then
Exit Sub

ElseIf C < 2 Then
MsgBox "Wrong Country Name :) Please Try Again!"
GoTo ST
End If

If ut = "BU" Then ut = "BULGARIA"
If ut = "AR" Then ut = "ARGENTINA"
If ut = "CI" Then ut = "CHILE"
If ut = "CB" Then ut = "COLOMBIA"
If ut = "CZ" Then ut = "CROATIA"
If ut = "CP" Then ut = "CZECH REPUBLIC"
If ut = "ET" Then ut = "ESTONIA"
If ut = "HB" Then ut = "HUNGARY"
If ut = "IQ" Then ut = "IRAQ"
If ut = "KN" Then ut = "KENYA"
If ut = "LR" Then ut = "LATVIA"
If ut = "LH" Then ut = "LITHUANIA"
If ut = "MM" Then ut = "MEXICO"
If ut = "NL" Then ut = "NIGERIA"
If ut = "PW" Then ut = "POLAND"
If ut = "RO" Then ut = "ROMANIA"
If ut = "RM" Then ut = "RUSSIA"
If ut = "RU" Then ut = "RUSSIA"
If ut = "RX" Then ut = "RUSSIA"
If ut = "SJ" Then ut = "SOUTH AFRICA"
If ut = "TI" Then ut = "TURKEY"
If ut = "UG" Then ut = "UGANDA"
If ut = "UZ" Then ut = "UKRAINE"
If ut = "ZH" Then ut = "ZIMBABWE"
If ut = "AB" Then ut = "SAUDI ARABIA"
If ut = "EY" Then ut = "EGYPT"
If ut = "OM" Then ut = "OMAN"
If ut = "QD" Then ut = "QATAR"
If ut = "UH" Then ut = "UNITED ARAB EMIRATES"
If ut = "DU" Then ut = "UNITED ARAB EMIRATES"
If ut = "KK" Then ut = "KUWAIT"
If ut = "BI" Then ut = "BAHRAIN"
If ut = "JR" Then ut = "JORDAN"
If ut = "MC" Then ut = "MOROCCO"
If ut = "TZ" Then ut = "TANZANIA"
If ut = "RW" Then ut = "RWANDA"
If ut = "CD" Then ut = "COTE D IVOIRE"
If ut = "ZL" Then ut = "Zambia"
If ut = "SG" Then ut = "Serbia"
If ut = "NW" Then ut = "NAMIBIA"
If ut = "GN" Then ut = "GHANA"
If ut = "TP" Then ut = "TRINIDAD & TOBAGO"
If ut = "GA" Then ut = "GREECE"
If ut = "PS" Then ut = "PALESTINE"
If ut = "NO" Then ut = "NOTABLE RESEARCH"
C = Len(ut)
If C < 3 Then Exit Sub

No! I get it . `elseif` do the work :D I am looking for alternate ways now I found `case statement` can do the same. — VBAbyMBA, Jan 19 '19 at 06:34
I believe vba also supports the collection object. Then it could become `countries.Item(“EY”)` — MDK, Jan 19 '19 at 06:41

QHarr · Accepted Answer · 2019-01-19T07:29:37.357

3

Use Select Case

Select Case ut
    Case "DU","UH"
        ut = "UNITED ARAB EMIRATES"
    Case "BU"
        ut = "BULGARIA"
    Case "AR"
        ut = "ARGENTINA"
        '...
End Select

You might also consider using a dictionary where the abbr e.g. BU is the key and the replacement is the value.

Example with dictionary:

Dim dict As Object, ut As String
Set dict = CreateObject("Scripting.Dictionary")
dict.Add "BU", "Bulagaria"
dict.Add "AR", "Argentina"
'etc

'Example
ut = "AR"
If dict.Exists(ut) Then ut = dict(ut)

edited Jan 19 '19 at 07:29

answered Jan 19 '19 at 06:30

QHarr

83,427
12
54
101

1

See my comment above. Look at the collection object – MDK Jan 19 '19 at 06:42
For me the logical structure would be a dictionary as you are essentially wanting to do a lookup. Key value pairs (dictionary) are optimized for this as well as providing a method for testing for existence which then allows you to dictate an action if not found. – QHarr Jan 19 '19 at 06:44
1

Plus 1 for the dictionary example. Provides runtime flexibility I.e keys can be added at runtime – MDK Jan 19 '19 at 06:45
HOW can I merge multiple cases resulting same output? In my question "UNITED ARAB EMIRATES" is result of two cases "DU" or "UH". Can we do that? – VBAbyMBA Jan 19 '19 at 07:25
1

you extend that case line with "," next value e.g. Case "DU","UH" – QHarr Jan 19 '19 at 07:28
With a dictionary you would have two keys "DU" and "UH" both with same value associated with key. – QHarr Jan 19 '19 at 07:30

score 1 · Answer 2 · answered Jan 19 '19 at 06:28

1

After the first If, all the others should be ElseIf. The ‘Else’ part of that causes it to be skipped if a prior If test was true.

answered Jan 19 '19 at 06:28

Bob Jacobsen

1,150
6
9

score 1 · Answer 3 · answered Jan 20 '19 at 01:40

This question made me consider which solution would perform faster between Select Case and ElseIf (I previously did a comparison of Long vs Integer). I ran some tests of a bunch of Select Case code and compared to similar ElseIf code.

Predictably there wasn't much of a difference, but Select statements always ran slightly slower than ElseIf. I did a few iterations of about 6 billion case checks and the difference seemed consistent.

I doubt this delta of a few seconds on such a massive volume is ever worth deviating from whatever approach you're most comfortable with. However here are my results and the code I used if anyone is curious:

'Module variables
Dim beginTIme As Double, i As Long, r As Long
Const MaxValue As Long = 999999999

Sub goSelect()
    beginTIme = Now

    For i = 0 To MaxValue
        r = r Mod 12

        Select Case r
            Case 0
            Case 1
            Case 2
            Case 3
            Case 4
            Case 5
            Case 6
            Case 7
            Case 8
            Case 9
            Case 0
            Case 11
        End Select

    Next i

    With Cells(Rows.Count, 1).End(xlUp)
        .Offset(1, 0).Value = Round((Now - beginTIme) * 3600 * 24, 0) & " seconds using select."
        .Offset(1, 1).Value = MaxValue
    End With

End Sub


Sub go4If()
    beginTIme = Now

    For i = 0 To MaxValue
        r = r Mod 12

        If r = 0 Then
            ElseIf r = 1 Then
            ElseIf r = 2 Then
            ElseIf r = 3 Then
            ElseIf r = 4 Then
            ElseIf r = 5 Then
            ElseIf r = 6 Then
            ElseIf r = 7 Then
            ElseIf r = 8 Then
            ElseIf r = 9 Then
            ElseIf r = 10 Then
            ElseIf r = 11 Then
        End If

    Next i

    With Cells(Rows.Count, 3).End(xlUp)
        .Offset(1, 0).Value = Round((Now - beginTIme) * 3600 * 24, 0) & " seconds using elseif."
        .Offset(1, 1).Value = MaxValue
    End With

End Sub
'Yeah this is what I'm doing on Saturday night....

You had done great comparison and quit right I used `IF ELSEIF` and its faster than `Select Case`. — VBAbyMBA, Jan 20 '19 at 09:54

score 0 · Answer 4 · answered Jan 19 '19 at 06:30

0

You could use a Case statement:

Select Case ut
Case "BU"
    ut = "BULGARIA"
Case "AR"
    ut = "ARGENTINA"
...
End Select

You could also use if else:

If ut = "BU" Then
    ut = "BULGARIA"
ElseIf ut = "AR" Then
    ut = "ARGENTINA"
ElseIf
    ...
End If

answered Jan 19 '19 at 06:30

Tim Biegeleisen

502,043
27
286
360

score 0 · Answer 5 · answered Jan 19 '19 at 06:41

You either can use the:

If ut = "BU" Then
   ut = "Bulgaria"
Elseif ut ="ZL" Then
   ut = "Zambia"
...
End If

But with so much conditions I would recommed the Select statement. This way you need to write the varible just once, and you have a Case Else when no condition is met:

Select Case ut
   Case "ZL"
       ut = "Zambia"
   Case "BU"
        ...
   ....
   Case Else
       Msgbox "Country not found"
End Select

Jumping to the end of IFs-statements when condition met at first IF

5 Answers5