Excel VBA and Variables
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.
(more…)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…
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
VBA – Easy Way to Handle TRUE/FALSE Tests
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. (more…)
Another Excel Date Issue Solved
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.
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.