When you record a macro that refers to a particular cell or range on a particular sheet in Excel the range reference is hard coded into VBA (macro) code. Unfortunately this means if rows or columns are inserted or deleted in the reference range the code is not updated. There is an easy way to get around this.
In a recent webinar I was asked about the “Too many different cell formats” error. This tends to be an error in Excel 2010 and earlier versions. In many cases this error is caused by having too many custom Styles.
Let’s say you are creating a new table in a new sheet using a macro and you need to create the headings in row 1. There is a reasonably easy way to do it.
It is easy to create a recorded macro. It is not so easy to create a flexible and re-usable recorded macro. Click the materials Button below to download the pdf manual and example file.
Learn the techniques that can allow you to record effective macros that can handle different ranges and changes to sheet names.
Macros can speed up your work and reduce the time taken for tedious tasks, as well as adding functionality to Excel.
This is the first in a series of webinars dedicated to macros. Future paid sessions this month will expand on the techniques taught in this session.
Excel will automatically decrease the print zoom % to fit to one page, but it won’t increase the zoom % to fit to one page. E.g. if you want to print on A3 instead of A4. I had a request to do this, so I wrote a macro to do 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.
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 have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)
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.