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.
The formula that will always display today’s date is
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
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.
To apply the Japanese Yen format can take quite a few mouse clicks.
The macro that does it, on the other hand, is quite simple. Select the range, then run the macro.
Sub JapaneseYen() Selection.NumberFormat = "[$¥-411]#,##0.00" End Sub
If you are unsure how to use macros, see the link below.
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.
When you are typing code in the VBA code window you can press
Ctrl + Space Bar
to have Excel finish the word for you. Eg type
And then press Ctrl + Space Bar to have Excel finish the word Application.
When using copy and paste in a macro it is a good idea to clear the clipboard at the end of the macro. If you don’t, the user could use paste to paste the last thing you had copied in the macro.
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.
Here’s the problem. You have a number of sheets that are named after Department codes. Those sheets contain the details for each department. You have reports throughout the model that refer to these department codes. You want to be able to select a cell that contains a department code and click a button that will take you to that department’s sheet.
The Quick Access Toolbar (QAT) can really speed up your work in Excel. You can even attach macros to the QAT.
If want to see a list of the functions available in the VBA language just type
in the code window
You don’t need to include vba. when using the function in code.
If you see a function with a $ in its name it means it returns a string (text) eg UCase$
(Thanks to John Walkenbach for this tip.)
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.
If you need to limit where a user can scroll to in a sheet you can change a setting in the VBA screen to restrict access to a specific range.
When you first start out using VBA and writing VBA (macro) code it is useful that the VBA Editor helps you quickly identify when you have made a mistake. If a line of code has an error it will display a dialog box that explains the error and turns the line of code red.
Hyperlinks are a great tool as they allow you to speed up and simplify navigation within a file. Sometimes hyperlinks can be frustrating. See how to remove some of those frustrations below.
There are two commands you can insert at the top of your code to speed up your macros.
When you are using sheet passwords in Excel it can be handy to have a macro to unprotect all the sheets in one step. This can make maintenance easier.