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: Blog
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
Top 10 with formulas in Excel
A top 10 list is a common form for displaying information, especially on dashboards and summarized reports. It is easy to create the list when working with sorted data, just cell link to the top 10 items in the list… easy! It’s also relatively simple when using Auto Filter, Tables and Pivot Tables, as it Read More
Power Query – Absolute and relative references
In Excel, we can easily create absolute and relative references. A simple $ symbol here and there is all it takes. But Power Query is less flexible, therefore getting values from other rows is not as simple. So, in this post, we cover how to get Power Query absolute and relative references. The filename for 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
Power Query – Percent of total or category
In this post, I want to cover how to calculate the percent of a total in Power Query. We’ll also take this one step further, to consider how to calculate the percent of a category. In standard Excel, these calculations are simple, as we are so used to the formulas to achieve this. But for 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
