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.
Monthly Archives: May 2016
Range Names List
If you use range names then it is a good idea to include a list of all the range names in a file for documentation purposes.
There is a shortcut that does all the work for you.
Select the cell where you want to enter the list.
Press F3 and then click the Paste List button.
The list has two columns. The range name and it’s reference.
The list is not dynamic. If you add, delete or change the names you will need to do the paste again.
Today’s Date
The formula that will always display today’s date is
=TODAY()
The keyboard shortcut to enter today’s date in the active cell as an input is
Ctrl + ;
The VBA line of code to enter today’s date in cell A1 is
[A1] = Date
“Why,” said the Dodo, “the best way to explain it is to do it”
Lewis Carroll (1832-1898) from Alice in Wonderland (1865)
VBA – Easy Way to Handle TRUE/FALSE Tests
Using a boolean variable
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.
M is for (Data) Monkey (Holy Macro! Books, 2015)
Don’t be put off by the title, this book is full of gold. It is chock full of tips, tricks and traps.
Power Query allows you to automate data cleansing operations on all sorts of Excel data – both internal and external.
Power Query can replace copying and pasting. No need for formulas and macros to get the data right.
Well worth purchasing – it will save you hours of work.
The most precious things in speech are pauses.
Sir Ralph Richardson (1902 to 1983)
What we have to learn to do, we learn by doing.
Aristotle (384-322 BC)
Another Excel Date Issue Solved
Times Affecting Date Calculations
Excel has a few issues with dates. Mainly they relate to the dates that are imported into Excel. Different systems present different problems. This post solves an issue with dates that include times.
A teacher affects eternity; he can never tell where his influence stops.
Henry Adams (1938-1918)
Right Click – Filtering
One of my favorite and frequently use right click tips is the Filter option.
You can click a cell within a table that contains the entry you want to filter by, then click the Filter option and then Filter by Selected Cell’s Value.
Note: you can also filter by font and fill colour.