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
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
When most people use PivotTables, they copy the source data into a worksheet, then carry out lots of VLOOKUPs to get the categorization columns into the data set. After that, the data is ready, we can create a PivotTable, and the analysis can start. But we don’t need to do all those VLOOKUPs anymore. Instead, we can … Read more
Since Excel 2010, it has been possible to save Excel as PDF. The PDF format was then and continues to be, one of the most common file formats for distributing documents. The code examples below provide the VBA macros to automate the creation of PDFs from Excel using the ExportAsFixedFormat method. This means you do not … Read more
We have all experienced it, and often at the worst possible moment. For whatever reason, Excel’s formulas aren’t calculating correctly. The good news is that it is usually something simple. Once we know the most likely causes, it is easier to troubleshoot the problem. So, in this post, we look at the most likely reasons … Read more
Top 10 lists are a common form for displaying information, especially on dashboards and summarized reports. It is easy to create a top 10 in Excel when working with sorted data; just cell link to the top 10 items in the list… easy! It’s also relatively simple when using AutoFilter, Tables, and PivotTables, as it … Read more
This post looks at how to loop through all items in a Data Validation list with VBA. This was inspired by a question from a reader. He wanted to save a PDF for each item in a Data Validation list. However, once we master the looping process, we are not restricted to PDFs; we can … Read more
If you have ever looked up a value from another worksheet, I’m sure you will have used the VLOOKUP function. There are other lookup functions available, but by far the most popular is still VLOOKUP. My guess is when you create this formula, you enter False or 0 as the last argument. But what does the range_lookup … Read more
In this post, we look at using Excel to reference another workbook without opening it. While this may seem like a great idea, we need to be very careful. When using this technique, a lot can go wrong without us realizing it. Let’s explore this area. Please read to the end and ensure you know … Read more
In this post, we explore the topic of Power Query custom functions; What are they? When should we use them? And how to create them? At the time of writing, over 700 standard M functions are already available in Power Query. So why do we need custom functions at all? Custom functions use the existing M function … Read more