VBA Window Tip
To open the VBA window press
Alt + F11
This shortcut actually toggles between the Excel window and the VBA window – but if the VBA window isn’t open, it will open it.
Monthly Archives: July 2016
Believe you can and you’re halfway there.
Theodore Roosevelt
Populating Blank Cells in Excel
A macro to make it easier
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.
Perfection is not attainable, but if we chase perfection we can catch excellence.
Vince Lombardi
Screen Clips
An easy way to create screen shots for manuals
Windows has a built-in screen clipping feature, but I paid for an app that makes the process a lot easier and is great for manuals and documentation.
Stop Text Being Input
If you want to stop text being entered in a range you can use a Custom Data Validation formula.
Select the range and press in sequence, not held down, Alt a v v. This opens the Data Validation dialog.
From the Allow drop down select Custom (last entry).
In the Formula box enter the following formula
=NOT(ISTEXT(A2))
Replace A2 with the top left cell in your range. Make sure there are no $ signs in the reference.
Click OK.
Nice to get emails like this
I wrote a macro to help someone fix their badly laid out data listing.
Macros rule!
If you think you are too small to make a difference, try sleeping with a mosquito.
Dalai Lama
Excel VBA Object Variables
Tips on using them
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.
Close Excel Quickly
In the more recent versions of Excel, when you click the X in the top right corner, you only close the current file.
In the older versions this would have closed Excel down, and you could then save each file in sequence.
To get the same effect in the new Excel, hold the Shift key down whilst you click the X with the mouse.
If you want to save all the open files click here to see a blog post with a macro to do just that.
Lose an hour in the morning, and you will spend all day looking for it.
Richard Whately
Richard Whately
When a Bar Chart is Better
Before and after charts
I was reading a magazine last week and a chart caught my eye. I thought I could improve it. I recreated it in Excel – its close to the original – see below – I didn’t quite match the column colour.