Ifs<\/strong><\/a> with a short single\nline code to execute.<\/p>\n\n\n\nMultiple Ifs<\/h2>\n\n\n\n You can use multiple Ifs<\/strong> if you want to execute multiple parts of code, and each If<\/strong> has to be closed with End If<\/strong>. Here\u2019s what I mean.<\/p>\n\n\n\nSub MultipleIfs()\n Dim age As Integer\n age = 77\n \n If age >= 18 Then\n MsgBox \"This person is an adult\"\n End If\n If age >= 65 Then\n MsgBox \"This person is a retiree\"\n End If\nEnd Sub<\/code><\/pre>\n\n\n\nIn this case, both conditions are met, therefore Excel will display both messages. If you have multiple Ifs<\/strong>, it\u2019s a good idea to use the SELECT .. CASE<\/strong> statement.<\/p>\n\n\n\nIf .. Then .. Else<\/h2>\n\n\n\n The If<\/strong> statement\nallows us to check a single condition. If the condition is met (returns TRUE<\/strong>), the particular code is executed.<\/p>\n\n\n\nBut what if the statement returns FALSE<\/strong> and, in this case, you want to execute a different part of a code?<\/p>\n\n\n\nIn this case, you can use ELSE<\/strong> statement. This part of code will execute if the condition is not met. Let\u2019s take a look.<\/p>\n\n\n\nSub IfElse()\n Dim age As Integer\n age = 16\n \n If age >= 18 Then\n MsgBox \"This person is an adult\"\n Else\n MsgBox \"This person is not an adult\"\n End If\nEnd Sub<\/code><\/pre>\n\n\n\nExcel checks whether the condition is met. In our case, 16 >= 18 returns FALSE<\/strong>, therefore program will execute code that is inside the Else<\/strong> statement.<\/p>\n\n\n\n <\/figure>\n\n\n\nIf .. Then .. ElseIf<\/h2>\n\n\n\n So far you\u2019ve learned about If<\/strong> and Else<\/strong>. In this\npart, you are going to learn about ElseIf<\/strong>,\nwhich allows creating more complicated conditions. <\/p>\n\n\n\nIt works in the following way. First Excel checks the If<\/strong> condition. If the condition is not\nmet (and only then) it checks the ElseIf\n<\/strong>condition. If the condition is still not met, it returns the Else<\/strong> statement. You can use multiple ElseIf<\/strong> statements, but only a single If<\/strong> or Else<\/strong> in a single if statement.<\/p>\n\n\n\nLet\u2019s find out using the following example.<\/p>\n\n\n\n
Sub IfElseIfElse()\n Dim age As Integer\n age = 11\n \n If age < 18 Then\n MsgBox \"This person is underage\"\n ElseIf age >= 65 Then\n MsgBox \"This person is a retiree\"\n Else\n MsgBox \"This person is an employee\"\n End If\nEnd Sub<\/code><\/pre>\n\n\n\nThis code checks whether a person is underage. If the condition is met it returns the message. If the condition is not met if checks the next condition \u2013 if a person is over 65. If the condition returns TRUE<\/strong> it returns the next message. If both conditions are not met, it returns the last message inside Else<\/strong>.<\/p>\n\n\n\nIF with logical operators<\/h2>\n\n\n\nNOT<\/h3>\n\n\n\n In our example, we used the following condition:<\/p>\n\n\n\n
age >= 18<\/strong><\/p>\n\n\n\nIf you want to select people who are younger than 18, you can use this statement: age < 18<\/strong>, or you can use negation.<\/p>\n\n\n\nWith negation, our example looks like this.<\/p>\n\n\n\n
Sub SingleIfNot()\n Dim age As Integer\n age = 18\n \n If Not age >= 18 Then MsgBox \"This person is not an adult\"\nEnd Sub<\/code><\/pre>\n\n\n\nIn the above example, no code will be executed because it makes our condition negative.<\/p>\n\n\n\n
Sub SingleIfNot2()\n Dim age As Integer\n age = 16\n \n If Not age >= 18 Then MsgBox \"This person is not an adult\"\nEnd Sub<\/code><\/pre>\n\n\n\nAfter you changed our variable to be smaller than 18, the message is going to be displayed.<\/p>\n\n\n\n <\/figure>\n\n\n\nOR and AND<\/h3>\n\n\n\n Besides NOT<\/strong>, you can also use AND<\/strong>\nand OR<\/strong>.<\/p>\n\n\n\nLet\u2019s modify our example.<\/p>\n\n\n\n
First, we are going to use AND<\/strong>.<\/p>\n\n\n\nSub IfAnd()\n Dim age As Integer\n age = 56\n \n If age >= 18 And age <= 65 Then\n MsgBox \"This person is an adult and employee\"\n End If\nEnd Sub<\/code><\/pre>\n\n\n\nIn this example, both conditions\nhave to return TRUE<\/strong> in order to\ndisplay the message. In other words, the condition is met if the age<\/strong> variable is between 18 and 65.<\/p>\n\n\n\nNesting IF and ELSEIF (intermediate)<\/h2>\n\n\n\n You can find that one If<\/strong> the statement is nested inside\nanother If<\/strong> statement. It\u2019s not only\npossible, but you can find that this is a common practice.<\/p>\n\n\n\nI\u2019ll illustrate it in the following example.<\/p>\n\n\n\n
Sub Fruits()\n Dim discount As Single\n Dim amount As Integer\n Dim fruit As String\n \n fruit = \"apple\"\n amount = 120\n \n If fruit = \"banana\" Then\n If amount > 100 Then\n discount = 0.1\n ElseIf amount > 10 Then\n discount = 0.05\n End If\n MsgBox \"You bought \" + CStr(amount) + \" banana(s) with \" + CStr(discount * 100) + \"% discount\"\n ElseIf fruit = \"orange\" Then\n If amount > 100 Then\n discount = 0.2\n ElseIf amount > 10 Then\n discount = 0.1\n End If\n MsgBox \"You bought \" + CStr(amount) + \" orange(s) with \" + CStr(discount * 100) + \"% discount\"\n ElseIf fruit = \"apple\" Then\n If amount > 100 Then\n discount = 0.4\n ElseIf amount > 10 Then\n discount = 0.2\n End If\n MsgBox \"You bought \" + CStr(amount) + \" apple(s) with \" + CStr(discount * 100) + \"% discount\"\n Else\n MsgBox \"You didn't buy anything\"\n End If\nEnd Sub<\/code><\/pre>\n\n\n\nIn the first If<\/strong>\nstatement this code checks what kind of fruit you are buying. If there are\nother If statements inside, it checks how many fruits you are buying and gives you\na discount.<\/p>\n\n\n\nThe IIf function<\/h2>\n\n\n\n There is no typo. There is a function in VBA, called IIf<\/strong>. This function is not as popular as\nthe If<\/strong> statement, but it exists, so\nI decided that it should be mentioned.<\/p>\n\n\n\nIt\u2019s similar to ternary operators from other programming\nlanguages.<\/p>\n\n\n\n
With this function, you can make your code even shorter than\nwith single-line If<\/strong> statements.<\/p>\n\n\n\nThis is our If<\/strong> .. Else<\/strong> example.<\/p>\n\n\n\nSub IfElse()\n Dim age As Integer\n age = 16\n \n If age >= 18 Then\n MsgBox \"This person is an adult\"\n Else\n MsgBox \"This person is a youngster\"\n End If\nEnd Sub<\/code><\/pre>\n\n\n\nLet\u2019s modify it in order to use the IIf<\/strong> function.<\/p>\n\n\n\nSub IfElse()\n Dim age As Integer\n age = 16\n \n MsgBox IIf(age >= 18, \"This person is an adult\", \"This person is a youngster\")\nEnd Sub<\/code><\/pre>\n\n\n\nIt\u2019s going to return this message.<\/p>\n\n\n\n <\/figure>\n","protected":false},"excerpt":{"rendered":"Sometimes you don\u2019t want every part of your code to be executed as you run your macro. In almost every computer language. In…<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[19],"tags":[],"yoast_head":"\n
VBA If Then Else (complete guide)<\/title>\n \n \n \n \n \n \n \n \n \n \n \n \n \n \n\t \n\t \n\t \n