Privacy settings allow you to control who sees the Power Query data. There seems to be a bug that remembers your response to a dialog and this ignores any changes to the Privacy settings. Find out the VBA line of code that can fix it.
In a Linkedin Excel Group recently there was a discussion about whether or not you should use the Dim statement to declare your variables. The argument was that you don’t have to and someone had managed to successfully create some code without declaring variables.
Unfortunately lots of people use the Merge & Center format in their spreadsheets. When working with other people’s files that contain Merged cells I will often remove the Merged cells format and apply Center Across Selection which is the preferred format to use. The macro below will convert Merged cells to Center Across Selection.
I was working on a project for a client and receiving multiple files. Some of the sheets had hidden rows or columns. I realised there is no easy way to find out if a sheet has hidden rows or columns, so I wrote a macro.
When you copy a sheet that contains range names you usually end up making a duplicate of those names at the Worksheet level. I have written a macro that removes all duplicated sheet-based range names in a file.
I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.
You can right click a sheet tab and select Hide, but it is just as easy to Unhide the sheet. What if you want to make it harder to unhide the sheet?
If you inherit a file or you haven’t used a file for while, it can be useful to do an inventory of all the PivotTables. A macro can do all the work for you.
I have mentioned before that blank cells in your data can affect Pivot Table defaults in Excel. They can also reduce the effectiveness of some keyboard and mouse shortcuts. The macro below populates blank cells in the selected range with zeros.
Object variables are the variable types that have their names written in black (right hand side) when you define them – see example image below for Range, Worksheet and Workbook. These are the most common objects used. There are two important things to know about using object variables.
It is common to create headings for data sheets in Excel VBA. There is an array technique that can make this a simple process.
Excel has a BeforePrint event which enables you run VBA code before a document is printed. This event can also be triggered by Print Preview. But not all Print Previews are the same.
Variables can speed up your code and make maintenance a lot easier. You should always declare or Dim (technical term) your variables, here’s why.
Let’s say you have VBA code that handles a budget and a forecast. There is a cell B2 on the Input sheet that contains the word Budget or Forecast. Based on that cell the macro with do different things. You may need to test for Budget/Forecast a few times within the code. There is an easy and flexible way to handle this.
You can create a macro to open a CSV file. One problem you may face is that dates are treated as US dates. A simple change can fix this.
Sometimes when Excel imports email addresses they are not recognised as emails and are not hyperlinks. They are two ways to fix this.
These days running a macro off a control button seems to be old school and many people have started running macros off graphics.
Sometimes when you hide or unhide, rows or columns, you can get an error message saying that Excel can’t move objects off the sheet. The solution is in the macro below.
When you become more advanced with macros and VBA programming (Visual Basic for Applications), you realise that you can create re-useable macros.
In Excel if you need to hide data or workings sheets you can hide multiple sheets in one action. Unfortunately Excel won’t let you unhide multiple sheets in one step (even in the latest version). To get around this limitation you can use a macro that unhides all the sheets.