Blog

The Excel Virtuoso blog provides excellent resources, training, and information to help you become familiar with advanced features of Microsoft Excel.

The general focus is on VBA, but some content related to the regular Excel interface is discussed. If you are familiar with Excel’s core functionality and looking to expand your expertise, this blog will provide a strong foundation to become a reliable Excel/VBA developer.

Use the categories and tags below to view all posts. Also make sure to check out our blogging series which provide comprehensive overviews of important concepts in Excel and VBA. If you have never previously programmed or used VBA, a good starting point is our Getting Started with VBA series.

Filter by Category

 

Filter by Tag

Case Study: Custom VBA Tooltips

This post provides a case study of an advanced implementation of custom VBA tooltips. While Excel offers a built-in tooltips feature for data validation, its limited to displaying simple text messages. By implementing custom tooltips through VBA, you can provide users with dynamic validation hints and infographics.

Easy VBA Tooltips

One of the most difficult parts of spreadsheet design relates to making complex spreadsheets accessible to all users. In business settings, a select group may be trained on how to use a spreadsheet model, but staff turnover may result in training gaps. This is where instructions embedded within a spreadsheet—such …

The Boss Macro

We’ll make this an April Fools’ Day tradition. Last year, I created The Garbage Financial Statement — a prank financial statement that would flip data whenever entered into a cell, and subsequently move the data to an adjacent cell whenever the user attempted to delete it. I wouldn’t recommend sending …

The On Error Statement: To Use or Not To Use

Managing coding errors can be a time consuming and complex task in VBA. Unlike many modern programming languages, VBA lacks native functions that help avoid common errors, sometimes requiring the use of the On Error statement. While the On Error statement can seemingly make a procedure work, it does not …

Series Overview: Tips and Tricks with VBA Classes

Post ContentsOverviewUseful Concepts Custom Keys Saving (Persisting) Object Data Class Initialized in a Collection Class Sorting Collection Class Objects Complex Class StructuresCollection Class within a Collection Class Multiple Object Collections within a Collection Class Advanced Concepts with Class PropertiesAdvanced Use of Class Properties Advanced Use of Properties within a Collection …

VBA Productivity Tip: Managing Ambiguous Names

Post ContentsOverviewManaging AmbiguityPreventing the Ambiguity ErrorCommon SituationsNote of CautionRelevant Tutorials and Posts 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 …

VBA Interfaces: The Implements Statement

Post ContentsOverviewExample FileConceptVBA PolymorphismBuilding an InterfaceInterface ModuleClass ModulesStandard Module ImplementationFull ModulesInterface ModuleClass ModulesStandard ModuleOther Notes The VBA Implements statement permits the use of interfaces, a mechanism to build clarity and consistency between multiple classes. As VBA projects grow, interfaces can be a central component of how a developer interacts with …

VBA Inheritance: Custom Objects as Class Properties

Inheritance is a concept that permits the transfer of data from one class to another class. While common in many programming languages, it is not native to VBA. In the absence of true inheritance, effective coding can help mimic its behavior.

Storing Objects as Class Properties

In most situations, Excel objects, such as shapes and buttons, perform simple functions and stand on their own. However, when stored within a class, the opportunity exists to supplement the functionality of custom objects.

Storing Arrays as Class Properties

Post ContentsOverviewExample FileStoring Arrays as Class PropertiesDirectly Assign Array to a PropertyBuild Array within a Property Using a StringBuild an Array Incrementally within a PropertyOutputFull Class ModuleOther Notes There may be situations where a single class property is needed to store multiple values. This can support a list of data …