2017, this site’s first full year, is about to come to a close. In addition to wrapping up the Getting Started with VBA series early this fall, the Tips and Tricks with VBA Classes series is well under way and will conclude at the end of January. Below, you can see a list of this site’s most popular posts of 2017.
Top Posts of 2017
- Creating and Using Multi-Dimensional Arrays in VBA
- This is our most popular post, presenting key concepts and techniques for implementing multi-dimensional arrays in VBA. Multi-dimensional arrays are useful for storing large data sets. 2D arrays are the most common type, behaving as tables of data stored off of a spreadsheet, while 3D, 4D, and larger dimensioned arrays behave as complex data structures.
- Organize Code with User-Defined Types in VBA
- User-Defined Types are one of the lesser known (and under-appreciated) data types in VBA. In fact, they’re one of the last data-types I learned about. A User-Defined Type acts as a “light” version of a class, permitting the storage of different data types within a single identifier. Like classes, they can store multiple data points of varying data types. Unlike classes, they cannot store special features often found in class methods.
- Creating and Using Collections in VBA
- Collections are a great alternative to one-dimensional arrays. Their primary advantage relates to usability and their willingness to hold dynamic data sets that can increase and decrease in size. While one-dimensional arrays require sometimes cumbersome ReDim statements to modify size, collections use simple Add and Remove methods to manage new and old elements. One disadvantage: existing values cannot be modified — as an alternative, you must first remove an old value, then insert a new value.
- Leveraging Dictionaries in VBA
- Dictionaries are another data structure similar to one-dimensional arrays and collections. They hold a key advantage over a VBA collection: existing data can be modified within the dictionary. Dictionaries are more reliant on keys, requiring them for each element. In some situations, a dictionary may be a good alternative to a 2D array.
- Building a Custom Listbox in Excel with VBA
- This post was written back in this blog’s first month, October 2016 — it remains one of the top posts for 2017. The ActiveX listbox is—in theory—an excellent tool for presenting a selection menu or sub-data on a spreadsheet. However, the ActiveX listbox also comes pre-packaged with countless bugs that will appear at any moment. Specifically bugs related to sizing issues when hooking up to a project or changing monitor resolution. To get around this, I build custom listboxes with some VBA and a scrollbar control.
- Series Highlights and VBA Cheat Sheet
- This post is my personal favorite. I say that because I find myself going back to it more than anything on this site (I don’t have the entire VBA library memorized and I do use my own site as a reference). This post takes all concepts from the Getting Started with VBA series, and condenses them into small code snippets and one line explanations. Links are provided to each concept’s detailed post, letting you dive further into a topic.
- Bubble Sort for Arrays and Other VBA Data Structures
- One shortcoming in VBA—when compared to other modern programming languages—is the absence of any built-in sorting function for arrays and other data structures. There are a few scripting methods available to manually sort data, such as the insert sort, merge sort, and bubble sort. In VBA, the bubble sort technique generally works regardless of the data type. This post presents user-defined functions to sort one-dimensional arrays, 2D arrays, collections, dictionaries, and a collection class.
- Techniques to Organize a VBA Project
- Large VBA projects can become difficult to manage. The VBA editor leaves much to be desired in terms of organizing modules into folders to provide structured logic as you build your project. By using naming conventions to mimic a folder structure, you can begin to manage a complex project as if it were part of a more modern programming environment.
- Building a Custom Class: Part 1
- Classes are the most intimidating of all VBA data structures. Not only are they heavy in a conceptual base, but they require their own module where certain requirements are quite strict and unforgiving. Personally, I only learned how to work with classes in VBA after I learned about classes in C++. Knowledge learned about C++ transferred to VBA quite easily. While they may seem very advanced at first, I strongly suggest copying a class module from my example file into your existing project. By making slight modifications and testing different scenarios, you will begin to understand how classes function in the VBA environment.
Coming in 2018
I’m looking forward to posting new and exciting content in 2018. In late January, the Tips and Tricks with VBA Classes series—which dives into advanced topics and techniques for working with VBA classes—will wrap up. From there, I’ll move on to a few case studies on financial models, then begin a new series discussing the use of ActiveX controls in Excel and VBA. I’m also looking forward to sharing a large-scale VBA project I’ve been working on for over a year.
To all visitors who follow or subscribe to this blog, thank you for your continued support. For those of you who just stumbled upon this post, please check out some of our top posts listed above and follow/subscribe if you find the content interesting.
Wishing everyone a happy and healthy New Year.