How to Use IF in VBA

Microsoft Excel IF statement is used in VBA Coding. It executes a set of defined code if the specified condition evaluates TRUE. IF statements are one of the foundational concepts in VBA programming and is one of the most frequently used statements. In this article we are going to discuss how to use IF statement in VBA programming.

SYNTAX

If Condition Then

   Result

End If

    • IFIF statements in VBA should always start with the code “IF”. This code Serves as opening line in IF statements.

    • Condition – a set of code that serves as condition which will be evaluated as either TRUE or FALSE.

    • Then – Code that should always be entered following the defined Condition.

    • Result – set of code that that will be executed if the condition evaluates TRUE.

    • End If – Code that closes the IF statements.

BASIC APPLICATION

Suppose you have a sales data that shows Branch Month of Sale, Sales Agent and Sales as shown in Figure A. Sales Data and you want to create a VBA Code into a Button that will clear the filter of the table if it is filtered.

Figure A. Sales Data

Instructions:

  1. To do this, go to Developer Tab. If Developer Tab is not shown in your Excel, check out the Microsoft guide in this link:

https://support.office.com/en-us/article/Show-the-Developer-tab-E1192344-5E56-4D45-931B-E5FD9BEA2D45

  1. Click Insert.

  2. Then click Button (Form Control).

  3. A VBA Command Button will appear which you can rename based on your preference. In this example, rename it to “Clear Filter”. To rename, right +  click on the button and then select Edit Text.

Figure B. Steps 1 - 4

  1. You will now start to write you VBA IF Statement. To do this, press ALT + F11 in your keyboard to activate Visual Basic Editor then select ThisWorkbook.

  2. Right + Click.

  3. Select Insert.

  4. Then select Module. This will create the module where you need to type your code.

3.png

Figure C. Steps 5 - 8

  • With this, type below code in your module where “RemoveFilter” is the name of the sub routine you will be creating. The name of sub routine can always be changed.

Sub RemoveFilter ()

End Sub

Figure D. Step 9

  • In between the lines Sub RemoveFilter () and End Sub code you have written, type:

If ActiveSheet.FilterMode Then

   ActiveSheet.ShowAllData

End If

Where:

ActiveSheet.FilterMode – Is the set of code as your condition

ActiveSheet.ShowAllData – Is the set of code as your result if the condition evaluates to TRUE

As a result, the overall sub routine code you will have is as follows:

Sub RemoveFilter ()

If ActiveSheet.FilterMode Then

   ActiveSheet.ShowAllData

End If

End Sub

Figure E. Step 10

  • After typing the code, you can now close the VBA Editor and go to your worksheet.

Now, Right + Click on the button you have created previously.

  • Select Assign Macro….

6.png

Figure F. Steps 11 - 12

  • A pop-up window that lists all macros you have created will appear. Select “RemoveFilter” – the subroutine/macro you have created.

  • Click OK.

Figure G. Steps 13 – 14

  • Your macro is now assigned to the button.  Now, try to filter the Branch in your table to show Batch 1 only.

8.png

Figure H. Step 15

  • Click anywhere within your table then click on your Macro Button. This will remove the filter and show all your data.

Figure I. Step 16

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit