How VBA works in Excel

This article is part of my series: Excel VBA & Macros – A Step by Step Complete Guide.

VBA is the most complex feature in Excel. If you have a background in the object-oriented programming language like C++, Java or any, you can grasp the VBA idea easily. Before going into details, here is a concise summary of how VBA actually works:

    • We write VBA code in a VBA module to perform our actions in Excel. We then execute this macro in any of various ways. VBA modules are stored in Excel workbook. You can store any number of VBA modules in a workbook. To view or edit a VBA module, you have to activate the VB Editor window. To change between Excel and the VB Editor window, press Alt+F11.
    • A VBA module is made up of procedures. A procedure is a piece of computer code that performs some actions. The following code is a simple Sub procedure and we have named it ShowSumOf. ShowSumOf Subprocedure adds 5 + 7 and displays the result in a pop-up dialog box:
      Sub ShowSumOf()
      Sum = 5 + 7
      MsgBox "The answer is " & Sum
      End Sub
How VBA works in Excel

The message box pop up when we run ShowSumOf macro

    • A VBA module may also have function procedures. A function procedure internally performs calculations and returns a single value. A function can be called from another VBA procedure. You can also use the function directly in a worksheet formula. Here’s an example of a function named AddTwoNumber. This function sums two values, which are supplied as arguments, and returns the result.
      Function AddTwoNumber(arg1, arg2)
      'Returns the sum of two numbers you supply as arguments
      AddTwoNumber = arg1 + arg2
      End Function
How VBA works in Excel

AddTwoNumber VBA function.

  • VBA works with objects. Excel provides more than 100 built-in objects that you can work with. Almost everything you create is the object. Examples of some objects are: whole Excel, a workbook, a worksheet, a cell range on a worksheet, a chart, and a rectangle shape.
  • Objects are arranged in a hierarchy. An object can contain one or more other objects. Excel itself is an object. Excel’s object name is Application. Excel holds other objects such as Workbook objects. The Workbook object holds objects like Worksheet objects and Chart objects. A Worksheet object can hold Range objects, PivotTable objects, and so on.
  • Same type objects form object collections. For example, all worksheets in a workbook make the Worksheets collection. In the same way, all the charts on a worksheet make ChartObjects collection. Collections are also objects.
  • We refer an object in our VBA code specifying its position in the object hierarchy. We separate objects with a period (.) separator.
    For example, we can refer to a workbook named Expenses.xlsx as
    This expression expresses that “Expenses.xlsx” workbook is one of the workbooks in the Workbooks collection. The Workbooks collection is in the Application object (it is, Excel). You can refer to Sheet1 in “Expenses.xlsx” workbook as follows:
    You can also refer to a specific cell (“A1”) of “Sheet1” as follows:
  • If you omit specific objects from the expression, Excel uses the active objects. If “Expenses.xlsx” is the active workbook, our preceding expression (Application.Workbooks(“Expenses.xlsx”).Worksheets(“Sheet1”).Range(“A1”)) can be simplified as follows:
    When “Sheet1” is the active sheet, it is possible to simplify the expression as follows:
  • Objects have properties. A property is basically a setting for an object. For example, a Range object has many properties: Value and Address are two of them. A Chart may have a title, type, and many other settings. These settings are Chart object’s properties but maybe in the different name: HasTitle and Type. We can use VBA to set object properties. We can also use VBA to change the values of properties.
  • We combine objects with properties separating them by a period (.). At first, we use the object, then we place any of the properties of that object. For example, we express the value of cell A1 on Sheet1 as follows: Worksheets(“Sheet1”).Range(“A1”).Value
  • We can assign values to variables. A variable is a VBA element that stores a value or text. Say, we set a variable Interest and we want to store the value of cell A1 in this variable. Assume the cell A1 is in “Sheet1” of “Expenses.xlsx” workbook. We can use this VBA statement to insert the A1’s value in Interest variable: Interest = Application.Workbooks(“Expenses.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Value
  • Objects have methods. Using a method you can perform some specific job with the object. Methods are of both types: built-in and customized. For example, Range object has a built-in method: ClearContents. This method clears the contents of the range.
  • We combine objects with methods separating them by a period (.). At first, we use the object, then we place any of the methods can be performed with that object. For example, to clear the contents of the range E11: F20, use the following VBA statement:
  • VBA also includes all the constructs of modern programming languages, including typed variables, arrays, looping, debugging aids, and so on.

We have described VBA in a nutshell. We shall enter into details step by step in next articles. We shall develop some real-time projects with VBA. This is a long journey to be expert in VBA. Keep in touch.

Happy Excelling 🙂

Read More…

Hello! Welcome to my Excel blog! I am conducting deep dives into the world of Excel. Please join with me and explore Excel deeply. Keep in mind this African proverb: "If you want to go fast, go alone, If you want to go far, go together." Let's together explore Excel deeply! ☕

1 Comment

      Leave a reply