Office 365 subscribers now have Excel’s new calculation engine available to them, meaning lots of users suddenly have access to dynamic arrays. This got me thinking about whether VBA user-defined functions (UDFs) can make use of the new functionality too. I’m not an expert in this; I’m just sharing as much as I’ve learned to Read More
Category: VBA
VBA Arrays
Before I start, let me share a little secret with you… I really dislike VBA arrays. There just seem to be too many oddities in how they work. Compared with other programming languages, VBA seems to make arrays overly complicated. If you feel the same, then you’re in the right place. This post contains a Read More
VBA to protect and unprotect Sheets
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, over types essential formulas and cell values. It’s even worse when that person happens to be ourselves; all it takes is one accidental keypress, and suddenly the entire worksheet is Read More
Auto refresh Power Query on parameter change
A few days ago, a reader asked a question about using parameters in Power Query; they wanted to auto-refresh Power Query whenever a parameter changes. “One question, how do you set the query to refresh automatically when a value changes.” I’ve not tried this before, but I can understand the benefits. It reduces the need Read More
Crack Excel passwords with VBA
There is nothing more frustrating than finding out a previous employee has used passwords to protect an Excel file, and it turns out nobody else knows the password. Or maybe it’s worse when it’s your file, your password and you’ve forgotten it. Either way… you’re stuffed! However, before you give in, let me share some Read More
Swap ranges, rows and columns in Excel
Copying, pasting and moving cells are simple everyday tasks for an Excel user. But what if something already occupies the area we want to paste to? We have to move things into temporary locations before putting them into their final destination. Wouldn’t it be better if there were a simple way to swap ranges, rows Read More
VBA Tables and ListObjects
Tables are one of the most powerful features of Excel. Controlling them using VBA provides a way to automate that power, which generates a double benefit 🙂 Excel likes to store data within tables. The basic structural rules, such as (a) headings must be unique (b) only one header row allowed, make tables compatible with Read More
How to add a prefix or suffix to every cell using VBA
We can often find spreadsheets with cells containing codes that have been shortened. So, rather than Proj003412, the cell value has been reduced to just 3412. It’s not a big problem to extend the code back to the full version; we could achieve that with a simple formula, then copy and paste special values. If Read More
Convert color codes (RGB, HSL, 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 Read More
Apportion a value across cells with VBA
Today, I want to share a really useful time-saving macro with you. It’s a macro to apportion a value across cells. Now that I’ve written it, I’m starting to gain the benefits, and hopefully, you can too. The filename for this post is 0005 Apportion value across cells.zip. When to use the macro? Let’s say you’ve Read More
