Visual Basic for Applications, usually identified as VBA, is the programming language in Microsoft Excel. This post examines some of the basic concepts and components encountered as a VBA coder.
Although VBA contains most of the characteristics of a traditional programming language, it has some limitations. Most notably, VBA is hosted within Excel, and cannot run independent of an Excel file. Also, while VBA holds many features of an object-oriented programming language (OOP), it lacks true OOP properties such as inheritance and polymorphism. Some experienced VBA developers have created work-arounds for this, but given that, it is not considered a true OOP. However, VBA does allow for the creation of classes and objects.
VBA is not to be confused with the popular programming language Visual Basic (VB). While they are very similar in both semantics and syntax, VB works within Microsoft’s .net framework and can run independently within Windows.
Key Components of a VBA Script
A sub, also known as a sub procedure or subroutine, is a set of commands meant to perform one or more tasks. Subs are the foundation of VBA scripts, and can hold many variables, functions, and control flow statements. This blog typically refers to a sub as a subroutine.
A variable is a value within a VBA script which can be declared as a type (for example, integer or text string), initiated/defined (for example, assigned the value 100), and changed within a script. Variables provide an essential tool in running complex calculations and algorithms.
Arrays are variables that contain more than one value. For example, the array “colors()” may contain a list of 3 strings, such as “red”, “yellow” or “blue”. Arrays can be displayed in their entirety through loops, or individual values by providing a specific integer expression.
A function is a set of commands that run to return a calculated or tested value. Functions are extremely useful for completing repetitive calculations in VBA. VBA also comes pre-loaded with many built in functions. These functions are not to be confused with user-defined functions, which are user created functions for use directly on a spreadsheet, or Excel’s own spreadsheet functions, which are built-in functions for use directly on a spreadsheet.
A class is a template of a custom made variable type. For example, a Range is a (built-in) variable type that has many attributes and methods which can control how VBA interacts with a spreadsheet. By creating a class, a programmer can create a custom variable that behaves in a certain way. Classes are an advanced topic which will not be discussed in this series, but at a later time.
An object is an instance of a custom template class, or an instance of a built-in object. To view a list of built-in objects, press the F2 key while in the module, or select View > Object Browser. The following posts will work extensively with the Range object.
A conditional statement, sometimes referred to as an IF-THEN-ELSE statement, is a statement that tests if certain conditions are met. This includes tests to see if values match or if values exist. Conditional statements allow developers to control whether certain scripts are run based on a TRUE/FALSE outcome. Conditional statements will be discussed later in this series.
A loop is a control-flow statement which repeats if certain conditions are met. Loops are great for populating spreadsheets, placing data, or finding values. Three types of loops exist to test conditions in different ways – these will be discussed later in the series.
Components of a VBA Project
The workbook object relates to the general windows functions associated with an Excel workbook. This includes opening, closing, and saving a workbook; resizing a workbook window, or various click actions. With this, developers can initiate scripts based on these events. For example, a workbook can be forced to save when a user closes it.
The worksheet objects relate to activities within a specific worksheet. Every sheet in the workbook will have its own object (presented in the Project panel). The worksheet object can trigger events when a specific cell is clicked, or value is changed. The Worksheet_SelectionChange event was used in a previous excersize for worksheet navigation.
A module is a container for most VBA scripts which do not belong to a Workbook or Worksheet object. There is no limit to the number of modules your project can have, and they are an effective means of storing code. Modules can be renamed.
A Class module provide a container for custom class templates. These modules are meant to hold class constructors, methods, and properties. Classes will not be discussed in this series, but will be discussed in later posts.
UserForms contain the custom interface dialog boxes (forms) that developers can create to collect user inputs. Userforms will not be discussed in this series, but will be discussed in later posts.
As a VBA developer, it’s important to leave proper notes in your script to explain what is going on. This will help both when you refer back to your code, or if any other members of your project team need to refer to the code. These are known as comments, and can be left anywhere in your code by starting with the asterisk “ ‘ “ Charater.
' This is a comment on its own line
MsgBox "Hello World!" ' This is a comment sharing a line with code
It’s important to learn proper coding techniques to make scripts clear and concise. Unnecessarily complicated code is known as spaghetti code. Spaghetti code tends to be common with new programmers, as they may not be aware of proper techniques or shortcuts to write clear and efficient code. Always reread your code and question your own approach.
VBA and Named Ranges
It’s important that you avoid referring to spreadsheet cells by their address. It’s much more effective if you refer to a cell’s named range. This will protect your script if you make any modifications to the spreadsheet, and can also add clarity to the script with well-thought-of range names.
Macros vs. Scripts
You may frequently hear your colleagues refer to VBA scripts as macros. Generally speaking, a macro is a set of automated instructions applied to a user interface. While there is no fully agreed upon consensus in the VBA community as to what defines a macro vs. a script, this blog’s position is that VBA can go a step beyond automation and can process complex algorithms. In most situations, this blog will refer to VBA code as VBA scripts.