Excel VBA Tutorial – The Best Way to Learn Programming in Excel

What is VBA?

VBA (Visual Basic for Applications) is a programming language that empowers you to automate almost every in Excel. With VBA, you can refer to the Excel Objects and use the properties, methods, and events associated with them. For example, you can create a pivot table, inserting a chart, and show a message box to the user using a macro.

what-is-vba

The crazy thing is:

For all the tasks which you perform manually in minutes, VBA can do it in seconds, with a single click, with the same accuracy. Even you can write VBA codes that can run automatically when you open a document, a workbook, or even on a specific time.

Let me show you a real-life example:

Every morning when I go to the office, the first thing I need to do is to create a pivot table for the month to date sales and present it to my boss. This includes the same steps, every day. But when I realized that I can use VBA to create a pivot table and insert it in a single click, it saved me 5 minutes every day.

Macro Codes To Create A Pivot Table

Note: VBA is one of the Advanced Excel Skills.

How VBA Works

VBA is an Object-Oriented Language and as an object-oriented language, in VBA, we structure our codes in a way where we are using objects and then defining their properties.

In simple words, first, we define the object and then the activity which we want to perform. There are objects, collections, methods, and properties which you can use in VBA to write your code.

how-vba-works

Don’t miss this: Let’s say you want to tell someone to open a box. The words you will use would be “Open the Box”. It’s plain English, Right? But when it comes to VBA and writing a macro this will be:

Box.Open

As you can see, the above code is started with the box which is our object here and then we have used the method “Open” for it. Let’s go a bit specific, let say if you want to open the box which is RED in color. And for this the code will be:

Boxes(“Red”).Open

In the above code, boxes are the collection and open is the method. If you have multiple boxes we are defining a specific box here. Here’s another way:

Box(“Red”).Unlock = True

In the above code, again boxes are the collection and Unlock is the property that is set to TRUE.

What is VBA used for in Excel?

In Excel, you can use VBA for different things. Here are few:

  • Enter Data: You can enter data in a cell, range of cells. You can also copy and paste data from one section to another.
  • Task Automation: You can automate tasks that want you to spend a lot of time. The best example I can give is using a macro to create a pivot table.
  • Create a Custom Excel Function: With VBA, you can also create a Custom User Defined Function and use it in the worksheet.
  • Create Add-Ins: In Excel, you can convert your VBA codes into add-ins and share them with others as well.
  • Integrate with other Microsoft Applications: You can also integrate Excel with other Microsoft applications. Like, you can enter data into a text file.

Write Your First Macro (VBA Program) in Excel

I have a strong belief that in the initial time when someone is starting programming in Excel, HE/SHE should write more and more codes from scratch. The more codes you write from scratch, the more you understand how VBA works.

But you need to start with writing simple codes instead of jumping into a complex one. That’s WHY I don’t want you to think about anything complex right now.

You can even write a macro code to create a pivot table, but right now, I don’t want you to think that far. Let’s think about an activity that you want to perform in your worksheet, and you can write a code for it.

  1. Go to the Developer Tab and open the Visual Basic Editor from the “Visual Basic” button.
    1-visual-basic-button
  2. After that, insert a new module from the “Project Window” (Right-click ➢ Insert ➢ Module).
    2-insert-a-new-module
  3. After that, come to the code window and create a macro with the name “Enter Done” (we are creating a SUB procedure), just like I have below.
    3-code-window
  4. From here, you need to write a code which we have just discussed above. Hold for second and think like this: You need to specify the cell where you want to insert the value and then the value which you wish to enter.
  5. Enter the cell reference, and for this, you need to use RANGE object and specify the cell address in it, like below:
    4-cell-reference-range-object
  6. After that, enter a dot, and the moment you add a dot, you’ll have a list of properties that you can define and activities that you can do with the range.
    5-enter-a-dot
  7. From here, you need to select the “Value” property and set the text which you want to insert in the cell “A1” and when to do it, your code with look something like below.
    6-select-value
  8. Finally, above the line of code, enter the text (‘this code enters the value “Done” in the cell A5). It’s a VBA Comment that you can insert to define the line of code that you have written.
    7-enter-the-text-above-line-code

The Best Way to VBA – Excel Programming

Here I have listed some of the most amazing tutorials (not in any particular sequence) that can help you learn VBA in NO TIME.

  1. Excel Programming Language – The Ultimate Guide to Macros in Excel
  2. Top 100 Useful Excel Macro [VBA] Codes Examples
  3. Excel VBA Font (Color, Size, Type, and Bold)
  4. How to a Line Break in a VBA Code (Single Line into Several Lines)
  5. How to Activate a Sheet using VBA
  6. How to ACTIVATE a Workbook using VBA in Excel
  7. How to Add a Comment in a VBA Code (Macro)
  8. How to Add a New Line (Carriage Return) in a String in VBA
  9. How to AutoFit (Rows, Column, or the Entire Worksheet) using VBA
  10. How to Check IF a Sheet Exists using VBA in Excel
  11. How to CHECK IF a Workbook is OPEN using VBA
  12. How to CLEAR an Entire Sheet using VBA in Excel
  13. How to Close a Workbook using VBA in Excel
  14. How to Combine Workbooks using VBA in Excel
  15. How to Copy and Move a Sheet in Excel using VBA
  16. How to COUNT Sheets using VBA in Excel
  17. How to Count Unique Values in Excel [Formulas + VBA]
  18. How to Create a New Workbook using VBA in Excel
  19. How to Create a User Defined Function [UDF] in Excel using VBA
  20. How to Create Excel PERSONAL.XLSB MACRO Workbook for VBA Codes
  21. How to DELETE a File using VBA in Excel
  22. How to DELETE a SHEET using VBA in Excel
  23. How to Extract Hyperlink Address (URL) in Excel Using VBA UDF
  24. How to Hide/Unhide a Sheet using VBA in Excel
  25. How to Highlight Duplicate Values in Excel using VBA Codes
  26. How to Loop Through All the Sheets using VBA in Excel
  27. How to Merge and Unmerge Cells in Excel using a VBA Code
  28. How to Open a Workbook using VBA in Excel
  29. How to PROTECT and UNPROTECT a Sheet using VBA in Excel
  30. How to Protect\Unprotect a Workbook using VBA in Excel
  31. How to Record a Macro in Excel
  32. How to RENAME a Sheet using VBA in Excel
  33. How to RENAME a Workbook using VBA in Excel
  34. How to Run a Macro in Excel (Run a VBA Code)
  35. How to Save an Excel Workbook using VBA
  36. How to Search on Google using a VBA Code
  37. How to SELECT ALL the Cells in a Worksheet using a VBA Code
  38. How to SET (Get and Change) Cell Value using a VBA Code
  39. How to use ACTIVE CELL in VBA in Excel
  40. How to use Option Explicit Statement in VBA
  41. How to use ThisWorkbook in VBA in Excel
  42. How to use UsedRange Property in VBA in Excel
  43. How to use VBA to Generate Random Number in Excel
  44. How to use With Statement in VBA (With-End With)
  45. How to VBA ON-OFF Button in Excel
  46. How to Write a VBA Code to Create a New Sheet in Excel (Macro)
  47. VBA ClearContents (from a Cell, Range, or Entire Worksheet)
  48. VBA Data Types – Variables and Constants
  49. VBA Do While Loop – The Ultimate Guide with Examples
  50. VBA FOR LOOP (For Next, For Each) – The Guide + Examples
  51. VBA Functions LIST (Category Wise)
  52. VBA IF Statement (IF Then Else) to Write Conditions
  53. VBA Immediate Window (Debug.Print)
  54. VBA LOOPS (Beginner to Advanced) – A Guide
  55. VBA MSGBOX – A Complete Guide to Message Box Function + Examples
  56. VBA Range (Working with Ranges and Cells) – The Guide
  57. VBA SELECT CASE Statement (Test Multiple Conditions)
  58. VBA Select Range – 10 Things you Need to Know
  59. VBA Workbook – A Guide to Work with Workbooks in VBA
  60. VBA Worksheet Function (Use Excel Functions in a Macro)
  61. VBA Worksheets – Understanding VBA Worksheet Object
  62. VBA Range (Working with Ranges and Cells) – The Guide
  63. VBA Wrap Text (Cell, Range, and Entire Worksheet)
  64. A Step By Step Guide to Create a Pivot Table in Excel using VBA
  65. How to Sort a Range using VBA in Excel
  66. How to Create a Named Range using VBA (Static + Dynamic) in Excel
  67. How to Merge and Unmerge Cells in Excel using a VBA Code
  68. How to Check IF a Cell is Empty using VBA in Excel
  69. How to use OFFSET Property with the Range Object or a Cell in VBA
  70. How to Copy a Cell\Range to Another Sheet using VBA
  71. How to use Range/Cell as a Variable in VBA in Excel
  72. How to Find Last Rows, Column, and Cell using VBA in Excel
  73. How to use Active Cell in VBA in Excel
  74. How to use Special Cell Method in VBA in Excel
  75. How to Apply Borders on a Cell using VBA in Excel
  76. How to Refer to the UsedRange using VBA in Excel
  77. How to Change Row Height/Column Width using VBA in Excel
  78. How to Insert a Row using VBA in Excel
  79. How to Insert a Column using VBA in Excel