VBA Productivity Tip: Managing Ambiguous Names

Today’s VBA Productivity Tip provides an overview of a little-known—yet simple—technique for managing ambiguous names (duplication) issues in VBA subroutines and functions. By using the dot operator, subroutine and function calls can be targeted to specific modules. Overview Generally, it’s advisable to make sure all subroutines and functions (procedures) within …

Using Methods and Properties within a Collection Class

The VBA collection class provides a framework to support basic object structures. While its most common use a collection class to manage groups of custom objects, including new properties and methods permits extraction of key aggregate data points for the entire collection. Overview This is the eight post in the …

Forcing Class Properties to do More Work

Class properties are typically simple statements which serve the basic purpose of reading or writing data in custom objects. However, it is possible to add additional lines of code, giving properties features similar to standard subroutines or functions.

Introduction to Custom VBA Classes and Objects

The ability to build classes and custom objects is one of the most valuable, yet underappreciated, features in VBA. While complex and fully functional VBA programs can be created without the use of this feature, utilizing custom objects will permit a VBA programmer to significantly scale-up a project, make it …

Techniques to Organize a VBA Project

While most modern programming environments provide robust tools to organize complex projects and separate procedures, the VBA editor’s organization features are limited. While procedures can be separated into different modules, not many options exist in terms of organizing modules into subfolders.

Building User-Defined Functions in VBA

While subroutines can be called within other subroutines, their environments are generally segregated — a subroutine cannot directly provide any information to another subroutine. Using functions, however, permits the transfer of a value, array, or object from one procedure to another.

Getting the Most Out of Subroutines in VBA

Up until this point of the series, the Sub statement–short for subroutine–has been one of the most frequently used statements in tutorials and exercises. In their plain vanilla form, a subroutines serve one purpose — to contain procedural code. However, having a comprehensive understanding of how subroutines work and various …

VBA Productivity Tip: Intellisense

This post introduces our first VBA Productivity Tip, a summary of the VBA Editor’s intellisense feature. Intellisense can help reduce the occurrence of common syntactical and semantic errors by providing useful tooltips and lists to help you code more efficiently.