Excel Virtuoso

Excel Virtuoso

VBA Tutorials, Solutions, and Experiments
The Excel Virtuoso blog provides excellent resources, training, and information to help you become familiar with advanced features of Microsoft Excel.
Find out how add-ins and templates can help you get the most out of Excel.
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...


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
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 "10 Non-VBA Concepts 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...
Read More "Series Highlights and VBA Cheat Sheet"
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 Collection Class"
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 2"
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 "Building a Custom Class: Part 1"
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 "Introduction to Custom VBA Classes and Objects"
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 "Techniques to Organize a VBA Project"
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 "Building User-Defined Functions in VBA"

Protecting Layout and Formulas

While one of Excel offers users the ability to interact with and modify any area of a spreadsheet, there are times where you may not want users of your spreadsheet application to inadvertently change contents. The ability to protect certain parts of a spreadsheet allows for full control, while providing …

Create Dynamic Spreadsheets with Conditional Formatting

Conditional formatting is a built-in Excel feature that—with proper implementation—can provide very useful and responsive feedback to users. By providing dynamic visuals, users will be able to gain instant insights into data and dashboards.

10 Tips for Great Spreadsheet Aesthetics and Design

The aesthetic design of a spreadsheet, when done right, can add significant benefits in terms of usability and functionality.

Modifying Data in Spreadsheets with VBA

While it’s usually obvious to a user how to add data in a spreadsheet, modifying data is often less obvious and an afterthought for many developers. By providing a clean and inutitive interface to modify data, inputs can be validated and controlled, ensuring datasets remain clean.

Building a Custom Listbox in Excel with VBA

While the Listbox Form and ActiveX control can suit the needs of many, they lack many options that provide full interactivity and customization. A custom VBA solution will give you full control of how listboxes look, and how users interact with them.

Building a Custom Listbox in Excel with Formulas

As discussed in previous posts, separating users from raw datasets is a cornerstone of excellent spreadsheet design. Today’s post provides a non-VBA solution to displaying data to users through a dynamic custom listbox.

Data Validation with User Feedback in VBA

With VBA, it’s possible to develop user input forms with data validation features and responses similar to those found on professional websites and desktop applications. In doing so, you can present the user with an familiar interface and user experience.

Data Validation with Excel Formulas

Today’s post continues the discussion on data validation, introducing a new approach that utilized Excel formulas. This approach provides an additional level of flexibility without the need for complex macros or VBA.