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.

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 …

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 …

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 will act as a reference for general syntax, concepts, and other topics.

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.

Creating and Using Enumerations in VBA

Enumerations are data structures useful for grouping a elements which can be (or need to be) be associated with numeric values. Examples of useful enumerations are products with product IDs, or working with date values, such as months 1 through 12. Many developers also use enumerations to manage events, such …

Creating and Using Collections in VBA

Similar to arrays, Collections are data structures which can store many values. While each have their own benefits, Collections do not need to be manually resized and are very useful when working with dynamic lists and other data.

Nested Loops – Looping Within Loops

While stand-alone loops will accomplish many tasks that require basic iteration, loops can be nested within other loops. In Excel, nested loops provide robust functionality to complete a number of tasks, such as filling out spreadsheet data, creating multidimensional arrays, or formatting cells.