Excel Virtuoso

Excel Virtuoso

VBA Tutorials, Solutions, and Experiments
Blog
The Excel Virtuoso blog provides excellent resources, training, and information to help you become familiar with advanced features of Microsoft Excel.
Downloads
Find out how add-ins and templates can help you get the most out of Excel.
Series
Many posts in the blog are organized into series which cover broad topics to help build and improve your Excel skills. As series are produced, visit these pages as a launching point to get a...

Series

Many posts in the blog are organized into series which cover broad topics to help build and improve your Excel skills. As series are produced, visit these pages as a launching point to get a general overview and link directly to specific topics/examples.

Tips and Tricks with VBA Classes

Classes can significantly increase the functionality and efficiency of VBA procedures. By properly using classes in your VBA program, you can create application-like spreadsheet and business models to accomplish many tasks, such as advanced modeling, data management, and automation. Unfortunately, not much literature exists about classes outside of their basic setup and management. The Tips and Tricks with VBA Classes series uncovers some advanced features and techniques for using classes and custom objects in VBA.

Getting Started with VBA

Visual Basic for Applications, often abbreviated as VBA, is the programming language in Microsoft Excel. By using VBA, developers have endless possibilities when it comes to building robust spreadsheets. When used properly, VBA can create application-class spreadsheets that can automate and streamline any task.

Spreadsheet Design

The idea of spreadsheet design refers to the overall experience a user has interacting with a spreadsheet. Design may conjure up thoughts of visuals and aesthetics, but that’s only a minor component. It often dictates the way users interact with an application. In the early stages of spreadsheet application development specific questions should be asked to drive design decisions. Who will be the primary user? How will the user enter and manage data? How will errors be handled? How will the user navigate to various components of the application? These are just a few questions that can act as a basis for effective spreadsheet design.

Series Highlight: Getting Started with VBA

10 Non-VBA Concepts Important for VBA Developers
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...
Read More
Series Highlights and VBA Cheat Sheet
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...
Read More
Building a Custom Collection Class
Building a Custom Collection Class
A Collection class is a useful means to manage a large group of custom objects. The ability to store multiple classes permits the efficient operation...
Read More
Building a Custom Class: Part 2
Building a Custom Class: Part 2
Classes and objects open up a host of opportunities within Excel VBA. In addition to being a powerful feature in their own right, classes can...
Read More
Building a Custom Class: Part 1
Building a Custom Class: Part 1
If you've followed the series up to this point, you have previously worked with intrinsic (built-in) objects like Range, Shape, or Worksheet. In addition to...
Read More
Introduction to Custom VBA Classes and Objects
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...
Read More
Techniques to Organize a VBA Project
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...
Read More
Building User-Defined Functions in VBA
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...
Read More

Storing Arrays as Class Properties

There may be situations where a single class property is needed to store multiple values. This can support a list of data or provide the foundation for a loop. VBA classes provide the framework to store arrays as class properties. Overview This is the ninth post in the Tips and …

10 Assumptions to Avoid in VBA

Even experienced VBA developers struggle to foresee issues that may come up at a future point. Whether during development, or after a program is rolled out to users, bugs and glitches can lead to headaches and awkward explanations. To help avoid these issues and ensure our VBA projects run as …

Most Popular Posts of 2017

2017, this site’s first full year, is about to come to a close. In addition to wrapping up the Getting Started with VBA series early this fall, the Tips and Tricks with VBA Classes series is well under way and will conclude at the end of January. Below, you can …

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.

Storing Multiple Object Collections in a Collection Class

While VBA collection classes are best known for storing collections of single custom object types, they can also be built into much more complex data structures. This includes storing multiple object collections within a single collection class, permitting a node-like structure to store any number of related items.

Creating a Collection Class within a Collection Class

A common object structure in VBA is the collection class, where many custom objects are grouped together. VBA also permits additional layers above the collection class, resulting in a collection within a collection. This is a useful technique for organizing hierarchical data or grouping certain objects.

Bubble Sort for Arrays and Other VBA Data Structures

The bubble sort is a commonly used technique for sorting simple arrays and collections. It can also be used to sort through other common VBA data structures, such as two-dimensional arrays, collections of custom objects, and dictionaries.

Sorting Objects of a Collection Class

One feature noticeably absent from VBA is a built-in function to sort arrays and collections. The bubble sort is a well known technique for sorting groups of elements when no native function exists. With a few minor adjustments, a bubble sort can also be used to sort collections of custom …

Using Class Initialize in Collection Classes

Class Initialize is known as an intrinsic (native) procedure that triggers when a custom object is initialized. Did you know that this procedure will also work within collection classes? When used properly, class initialize can help set up collections by using default data or loading from saved data.