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

Jagged Arrays in VBA

Jagged arrays are one of the lesser known data structures in VBA. In some cases, it may be a viable an alternative to a traditional 2D multi-dimensional array. Today’s post will present three different approaches to implementing jagged arrays in your VBA project.

Google-Like Search in Excel

Today’s post introduces a Google-like search, with search-as-you-type suggestions. While it’s possible to run basic searches with VLOOKUP and other techniques, this VBA approach can provide immediate search suggestions or recommendations for the user.

Convert a Data Table from Cross-tab to Tabular Format

Cross-tab format is great for summarizing data, but not so great for working with it. Today’s post provides an overview of a VBA procedure that converts data from cross-tab to tabular format.

Case Study: VBA Implementation of a Bond Ladder

This is a short post showcasing some of the VBA procedures, classes, and coding techniques in last month’s bond ladder financial model. The model relies heavily on VBA, using it to manage displays, calculations, and data storage.

Financial Model: Bond Ladder

This post introduces a new financial model — an Excel/VBA implementation of a bond ladder portfolio and summary. Its general purpose is to calculate an average weighted yield for a ladder of bonds.

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 …