An Excel Add-in is a mini-software application that provides Excel with enhanced functionality and automation features. Whilst they can be created by third-party suppliers, even Microsoft themselves develop add-ins to provide additional functionality. Add-ins can take a number of forms: COM Add-ins and Office Download Store Add-ins will have their own installation processes. Excel Add-ins, however, have … Read more
In VBA, there are several ways to run a macro from a macro, and even run a macro from another workbook. On forums, these methods are used interchangeably depending on the commenter’s personal preferences. This can lead to confusion for anybody trying to learn VBA. Depending on the method for running/calling the macro, the syntax is slightly … Read more
This post looks at how to Resize an Excel sheet. It’s a common requirement for users and is especially useful when creating dashboards for different-size monitors. Every office I have ever worked in has been an equipment battleground. It didn’t matter what the equipment was; it just had to be better than everybody else. A new keyboard, … Read more
VBA UserForms are a key tool for managing user interactions. When UserForms are well designed, they guide users through the options and settings without any help file or guidance. However, from my own UserForm development, I know one of the most overlooked aspects is how to close VBA UserForms. Is it best to hide or unload the … Read more
In 2020, I attended an online meetup where Danielle Stein Fairhurst shared a technique to automate Goal Seek in Excel with a VBA macro. I had never thought of this before; it had my mind buzzing with ideas of how I could use it. I have since implemented this technique on a few projects and … Read more
From stock parts to holiday villas, from employees to logos, there are plenty of reasons to insert and automatically change an image based on a cell. You may think of this as a lookup function that returns a picture, rather than a value. Ultimately, the purpose is to link an image or picture to a cell; … Read more
The space character for an Excel user can be a real problem. When exporting data from various IT systems, that export will often include extra spaces around values. Finding a way to remove spaces in Excel is a crucial skill for every user. We often can’t see the space characters, but Excel sees them. They … Read more
With macros, we can automate Excel and save time; big tasks or small tasks, it doesn’t matter. All that matters is that we’ve become more efficient. In this post, I share 30 of the most useful VBA codes for Excel that you can use today. If you’ve never used VBA before, that’s fine. Part 1 … Read more
I often find myself converting between different formats of color codes; especially from hex to RGB and back again. Rather than firing up Google and searching for color conversion tools online, I decided to write my own User Defined Functions in Excel, now I can calculate the result within a worksheet instead. The functions in this … Read more
When writing VBA macros, the concept of Private or Public is important. It defines how VBA code within one module can interact with VBA code in another module. This concept applies to both Private Subs and Private Functions. As a simple analogy – on social media, you can set parts of your profile so that everybody … Read more