Excel Virtuoso

Excel Virtuoso

Build Powerful Business Applications in Excel
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

Overview

Spreadsheets have not fundamentally changed over the past 30 years. While many professionals and casual users choose to avoid advanced features, the general functionality is very simple and familiar. Access to spreadsheets is always available, whether through Microsoft Excel, Google Spreadsheets, OpenOffice Calc, etc. Many spreadsheet suites are even accessible on smart phones. Given their ease of access and consistent functionality, information provided on this site will be relevant for many years to come.

The goal of this site is to provide comprehensive case studies to present useful Excel solutions. While the focus is primarily on advanced VBA features, my tutorial posts will help users understand VBA’s complexity. To start, view the following links:

Downloads

Download and read about our Excel templates, add-ins, and models.

Blog

View our tutorials and exercises for many topics in Excel and VBA.

Series

Many blog posts are organized into a series format. Each series contains multiple posts discussing related features and concepts around important Excel and VBA topics.

If you find the content of this site useful, please consider following us on social media.

LinkedIn
Twitter
Facebook
Google
Tumblr

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 of VBA projects, while keeping code clean and readable.

Financial Model: Coffee Brewing Methods

In recognition of National Coffee Day, I’ve put together a simple financial model to compare the cost of coffee brewing methods. While most holiday backers seem to be trying to get you to spend more on coffee, I’m here to help you spend less! By inputting basic details on your …

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 utilize every VBA coding technique, and permit small project teams to work on large-scale VBA projects with ease. By learning and fully understanding VBA classes …

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 these intrinsic objects, VBA offers the ability to build custom objects through the creation of a class.

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 …

VBA Productivity Tip: Naming Modules

This VBA Productivity Tip introduces a very simple feature that can be used to organize modules in complex projects — naming individual modules. By providing modules with specific and informative names, developers can easily group and compartmentalize VBA subroutines.