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
Category: VBA
How to resize Excel sheet – 4 ways: manual & automatic
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
How to close VBA UserForm: Hide vs Unload
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
How to automate Goal Seek in Excel with VBA Macro (2 ways)
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
How to change images based on cell values (3 ways)
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
How to remove spaces in Excel (7 simple ways)
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
Useful VBA codes for Excel (30 example macros + Free ebook)
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
How to convert color codes (RGB, HSL, HSV, Hex, Long, CMYK)
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
Private vs Public Subs, Variables & Functions in VBA
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
VBA protect and unprotect Sheets (25+ examples)
Protecting and unprotecting sheets is a common action for an Excel user. There is nothing worse than when somebody, who doesn’t know what they’re doing, overtypes essential formulas and cell values. It’s even worse when that person happens to be us; all it takes is one accidental keypress, and suddenly the entire worksheet is filled with Read More