Introduction to VBA

VBA (Visual Basic for Applications) is a programming language that exists “under the hood” of MS Office applications. Therefore, it is a part of Excel and allows users to create new features, introduce additional functionality and automate work with spreadsheets. This is the first of the posts in which I will try to teach you the basics of VBA programming. Important advice: before you decide to write code learn Excel and try to find out if what you want to get is already there!

The program is a set of commands that are assigned to the computer and executed in the specified order. It often contains variables. It is best to describe them as memory placeholders which preserve values so that we can, in an easier way, carry out arithmetic operations with them. Since programs can be quite complex, it is often convenient to split them into smaller parts – subroutines, which we later call from the main program. Subroutines are often refered as functions and procedures.

VBA is an object-oriented programming language. It is a contemporary concept of programming that uses objects, and it is characterized by four features:

• Properties
• Methods
• Events
• Collections

Properties are variables within an object that describes it better. For example. If the object is a window, it can be variables that contain information about height, width, window position … Methods are pieces of code that are executed when the necessary conditions or when Events occur. Finally, Collections are sets of multiple objects of the same type, e.g. worksheets in Excel.

Every time you create macro Excel automatically in the background generates the VBA code. A macro can be an independent set of instructions, and it could be activated by certain events. For example: clicking the button, opening the drop-down list, positioning above the object … Macros could be edited when entering the VBA Editor, it is achieved by clicking on the Visual Basic icon in the Developer ribbon or, more convenient, by pressing the ALT + F11 key combination.

VBA Editor is a development environment that opens in a separate window and is used to add objects, manipulate them, and write code. When you run it on its left side, you will see a menu (Project Explorer) where, in the upper part, the tree groups of objects in Excel are displayed. The object at the highest level of the hierarchy is called VBAProject. Next, there are following object collections: Microsoft Excel Objects, Forms, Modules … When you click on an object, you can view and change its properties in the list below (Properties). The central part of the window is the area in which we enter the VBA programs (Code). You can temporarily hide some of these menus or re-display them by using the options in the View menu that is located under the title bar. There is also an Object Browser option that makes it easy to search for existing objects, their properties and methods.

By creating a macro you will design a subroutine that will appear in one of the modules (Module). You can add modules independently to write your own programs, and it is also possible to create module collections (Class Module). Sometimes you will want to create a forms (Forms) to further facilitate the entry of data into tables. You can add them, as well as modules, using the options that belong to the Insert menu.

When you write the program, you control it by using icons that resemble those that play multimedia content (Run, Break, Reset) and are located in the Run menu. If, when writing a program, it generates errors, their detection is easiest with the help of the options within the Debug menu …

Finally, we need to save the changes we make using the Save option in the File menu. It is important to note that worksheets containing macros have to be saved as XLSM files. Excel treats them as potentially dangerous, so before you start to work with macros check your security settings.

This was an (awkward) introduction to the VBA, and in the following posts you will be able to learn more about Excel objects and VBA programming.