VBA basics
20. Conditional branch (If)

Excel macro basics and applications, introduction to Excel VBA
last updated:08-25-2019

20. Conditional branch (If)

Some of this topic was processed by machine translation. Original


マクロ VBA Ifステートメント

You may want to process only under certain conditions, or you may want to change the processing contents depending on the conditions.
The If statement is used when you want to branch processing according to a condition.


In many cases of automation with VBA macros,
It is to repeat a certain number of times while changing the process according to the conditions.
The most important statement in the program is the If statement.

If statement syntax

If conditional expression Then true processing [Else false processing]
Or
If conditional expression Then
  True processing ... Processing when the conditional expression is satisfied
[Else
  [Fake processing] ... Processing when the conditional expression is not satisfied
End If

[ ] Is optional.

True means that if the conditional expression is satisfied,
False means that the conditional expression is not met

Determine the condition written in the conditional expression,
True processing is executed if the condition is met (matched), and false processing is executed otherwise (the conditional expression is not satisfied).

Multiple processes (statements, etc.) can be written for true and false processes.

Conditional expression
The condition judged by If
Specifies a formula or string expression that evaluates to true or false.

A formula is a combination of a numeric value and a comparison / logical operator,
A string expression is a combination of a string and a comparison / logical operator.

Comparison operator


Comparison operator Meaning
= Equal
<> Not equal
> Greater than
>= More
< Less than
<= Below

Logical operators
Logical operator Meaning
And AND
Or OR
Xor exclusive OR
Eqv logical equivalence
Imp logic inclusion
Not logical negation

Logical operators other than And, Or, and Not will not need to be used for the time being.
Especially when you share Macro VBA with others, you should avoid using things that are difficult to understand just because you know.

If example sentences

Example sentence 1.

If Cells(1, 1) >= 100 Then Cells(1, 2) = "○"


When A1 cell is 100 or more, "B" is displayed in B1 cell

Example sentence 2.


If Cells(1, 1) > 100 Then
  Cells(1, 2) = "○"
End If


Example sentence 1. Is the same.

Use this unless you have particular circumstances (for example, if you want to improve the readability of the program by writing it on one line).

Example sentence 3.

If Cells(1, 1) > 100 Then
  Cells(1, 2) = "○"
Else
  Cells(1, 2) = "×"
End If


When A1 cell is 100 or more, "B" is displayed in B1 cell
If the A1 cell is less than 100, “B” is displayed in the B1 cell.

Example sentence 4.

If Cells(1, 1) > 100 Then Cells(1, 2) = "○" Else Cells(1, 2) = "×"


This is an example sentence 3. But it ’s better not to use this,
The program becomes difficult to see.

If nesting

More complicated conditional branching is possible by nesting If statements (nesting, if within If).

If Cells(1, 1) >= 100 Then
  If Cells(1, 1) >= 200 Then
    Cells(1, 2) = "A"  '>= 200
  Else
    Cells(1, 2) = "B"  '>=100 AND < 200
  End If
Else
  If Cells(1, 1) >= 50 Then
    Cells(1, 2) = "C"  '< 100 AND >= 50
  Else
    Cells(1, 2) = "D"  '< 50
  End If
End If


A1 cell
> = 200, "A" in cell B1
> = 100 AND <200, "B" in cell B1
When <100 AND> = 50, "C" in cell B1
If <50, "D" in cell B1

There is no limit to the level of nesting, but it should be roughly three levels.
If it exceeds that, try devising conditional expressions.

In most cases, it can be aggregated into simpler conditional expressions.

If reference page in the site

Finally

It can be said that the level of VBA skill can be judged by whether or not the If statement can be written quickly.
Even if you understand the grammar of IF, you can't write easily.

Please learn carefully here.
After that, always write If if you are writing Macro VBA.



Articles with the same theme "VBA basics"

17. Iterative processing (Do Loop)
18. Get last row (End, Rows.Count)
19. General practice question 1
20. Conditional branch (If)
21. Conditional branch (ElseIf)
22. Conditional branch (Select Case)
23. Message box (MsgBox function)
24. Input box (InputBox function)
25. About named arguments
26. General practice question 2
27. Select book / sheet (Select, Activate)



  • >
  • >
  • 20. Conditional branch (If)

  • If you find this site useful, please share and bookmark.


    I'm going to pay close attention to the description,
    If you have any mistakes or suggestions,「Contact Us」I hope you will let me know.
    Use the posted VBA code at your own risk.
    We are not liable for any damages such as data corruption.




    If you find this site useful, please share and bookmark.
    To the bottom of the text