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 …

10 Non-VBA Concepts Important for VBA Developers

As a VBA developer, it’s important not to lose sight of the broader Excel application environment. VBA will always be part of Excel — an application containing its own interface, features, and functionality. This post will provide an overview of 10 important non-VBA concepts and features important for VBA developers …

Series Highlights and VBA Cheat Sheet

As we approach the close of the Getting Started with VBA series, it’s important to take a broad look at all topics discussed. This post will act as a reference for general syntax, concepts, and other topics.

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.

Calling Subroutines with Worksheet and Workbook Events

Form and ActiveX Controls provide a user-friendly way to initiate Excel macros, but it may not always be clear to the user that a button needs to be clicked. If you find yourself including notes such as “please click button here”, you may want to consider Worksheet and Workbook events …

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 …

Organize Code with User-Defined Types in VBA

VBA data structures such as arrays, collections, and enumerations can help organize large and complex sets of data. When drilling down to specifics within data, it can be somewhat difficult to work with these structures. VBA offers another data structure, User-Defined Types, which provide a more intuitive and user friendly …