HOW VISUAL BASIC FOR EXCEL WORKS?

Excel VBA

HOW VISUAL BASIC FOR EXCEL ACCESS TO THE SPREADSHEET?

Visual Basic for Applications (VBA) is an object-oriented programming language.

Every application built in VBA will use different individual objects for performing the actions we want to make in our spreadsheets. These objects will be, for instance, workbooks, worksheets, cells, charts, shapes, forms, buttons, …

In this way, the workbook over which we are making some changes will be an instance of the type of objects workbooks. The same for a worksheet and the rest of them.

Every VBA object will have different characteristics and will be able to perform some actions (or we will be able to program it to make some actions). These characteristics that define the object are called Properties and the actions that can perform Methods.

For instance, every instance of the object workbook will have the property name (different for every instance), or the number of worksheets that contains. And some of the methods we can perform over it is open, close, delete, …

HOW THE VBA OBJECTS ARE STRUCTURED?

The different Excel VBA objects are structured hierarchically.

The Application Object is the one that contains all the rest of them. So, for instance, we can have one or more workbooks below it.

Inside every workbook, we can have one or more worksheets. Inside every worksheet, we can have one or more ranges, one or more charts, … and so on.

These objects represent the corresponding parts of the Excel spreadsheet, and with VBA we can change their properties so they will display the new ones or we can use their methods to process them.

Workbooks, Worksheets and Ranges in the Excel Spreadsheet

For instance, if we change the property Name of a worksheet, we will see this change in the tab text.

For access to an object through code, we need to indicate the complete structure above it. Excel must know exactly which instance of the object we want to change.

Imagine we want to enter the text “Testing VBA” in the top-left cell (“A1”) of a workbook. We will need to enter a sentence code as the following one:

Application.Workbooks(“Book1.xlsx”).Worksheets(“Sheet1”).Range(“A1”).Value = “Testing VBA”

Objects structure splitted by dots in a VBA for Excel sentence

As we see, the dots of the sentence split the different object structure levels. Without indicating the workbook, worksheet, and range, the program will not be able to find the right cell where to enter the text. And they must follow this order, from top to bottom, as we could have different worksheets with the same name in different workbooks.

This structure can be omitted when we want to access objects of the currently active objects in the upper levels of the structure.

For instance, if currently, the workbook and worksheet over which we want to make this change are active, we could specify only the range object:

Range(“A1”).Value = “Testing VBA”

Or even if the cell where we want to enter it is also active, we can just enter:

Activecell.Value = “Testing VBA”

 WHAT ARE THE COLLECTIONS IN VBA?

Given the objects structure described in the previous points, at some level, there can be one or several objects of the same type. For instance, as we have seen, in every workbook, there can be one or several worksheets.

The group of these objects that belong to the same upper-level object is called a Collection in VBA. For instance, the group of worksheets that belong to the same workbook is a collection of worksheets.

This concept is important because whenever we have a collection, we need to specify which specific object of that collection we want to access. This can be done in two ways:

  1. Using an index (a sequential counter that starts with 1)
    • Worksheets(1).Value = "Testing VBA"
  2. Using its name in a quoted text
    • Worksheets("Sheet1").Value = "Testing VBA"

If we had another worksheet with the name “Sheet2” we could have access it with one of the following sentences:

    • Worksheets(2).Value = "Testing VBA"
    • Worksheets("Sheet2").Value = "Testing VBA"

Note: As you have seen, we have not included in this code examples the workbook, so in this case, the program will access to these worksheets of the active workbook. In case they don’t exist in it, an error will be raised when we run this code.

WHAT ARE THE PROPERTIES OF VBA OBJECTS?

The properties define different characteristics of each instance of an object at every given moment.

  • Although some of them are the same, they can be different depending on the type of object
    • For instance, the workbook object has the characteristic FileFormat that does not exist for the worksheet object.
  • They are different for each instance of each object
    • All worksheets have the characteristic Name, but the actual value for each instance of them will be different (“Sheet1”, “Sheet2”, “Test”, … or whatever name we want to give them)

There are many different objects and many different properties for each of them. But in practice, we won’t need to use all of them. So we don’t need to learn all of them, but simply need to know where to find the relevant information for using the right one when we want to write some specific code.

There are different ways of finding the property to be used:

  1. If we have set the “Auto List Members” check box in the VBA Editor settings (as we saw in this post):
    • Whenever we write an object in the code and enter the dot, we will see the list of properties and methods we can use for it, from where we can choose the one we want to use.
    • For Example, if we enter a workbook, we will see the following list:

List of possible properties and methods for an object in Visual Basic for Excel

  1. At any moment we can check the available properties, methods, and events from the Object Browser.
    • For this, we will press the key “F2”, choose the menu option “View -> Object Browser” or press the following icon in the toolbar:

Objects Browser in the tool bar for Visual Basic for Excel

  1. In case that even with this help, we don’t find the right property to use:
    • We can save a macro doing whatever we want the code to do and then check the generated code to know which properties were used
    • In the last case, we can always search online for whatever we want to do, and we might find some blog posts or forum messages with some hints we can use (although it does not sound very professional, in many cases, it is the fastest way of going forward).

As we have seen, when we write code in VBA we might need to access these properties to change them. For instance, the change in the value contained in a cell. But we might need to access them to only read whatever value they already have assigned. For instance, if we want to make a calculation using the value of a cell.

In fact, some the characteristics can only be read, and we will get an error if we enter a code that tries to change them.

WHAT ARE THE METHODS OF VBA OBJECTS?

Methods in VBA are the actions we can perform with an object or over an object.

For instance, the workbook has the method “Save” that we can use to save the file from the code. There are others specific of worksheets, such as “Calculate“, or specific of ranges, such as “Value“, …

As with properties, there are many methods, different depending on each type of object. And we can check them in the same way as we saw for the properties.