Editor’s Note: To read more about spreadsheets, share a tip with other readers, or suggest a topic for Bill Jelen to cover in an upcoming column, click here to visit CFO.com’s Spreadsheet Tips Page.
Reader David H. wins an autographed copy of VBA & Macros for Microsoft Excel from CFO and MrExcel.com for his question: “I think it’s pretty simple, but I am just not sure what the ‘trick’ is to make my spreadsheet work in the following way: I have a column which has each day of the month in it (e.g., 1, 2, 3, 4, etc.). When I open Excel, I’d like the spreadsheet to go to the cell that has today’s day of the month. For example, if today is Oct. 1, 2010, I’d like Excel to go to the cell that has ‘1’ in it, which in this case represents the first of the month. I use the spreadsheet to list projects and other tasks that are due on each day of the month, categorized by day (e.g., 1st day).”
This trick uses a very small macro known as the Workbook_Open macro. This tiny macro runs every time the workbook opens, and makes use of a system variable called Date. Note that Date will return a value such as 4/20/2011. To find the date of the month, you would combine the Excel DAY function with the Date variable to return a number such as 20: Day(Date).
Use a File Type That Allows Macros
Macros will work with files saved as .XLS, .XLSB, or .XLSM. They will not work with files saved as .XLSX. Unfortunately, the default file type in Excel 2007 and Excel 2010 is often .XLSX. Use the Save As command to save your file as a different file type if it is currently stored as .XLSX.
Allow Macros on Your Computer
In Excel, press Alt+T followed by the letters M and S. You will get to the macro security settings. In Excel 2003, avoid “Very High” and “High.” In Excel 2007 and newer, avoid “Disable All Macros Without Notification.”
The Workbook_Open Macro Is Entered in a Special Code Window
Unlike general macros that you record, the Workbook_Open macro needs to be typed or pasted into the code pane that is attached to ThisWorkbook. Here is how to arrive at that code pane:
1. From Excel, press Alt+F11 to open the VBA Editor. (Some newer keyboards require you to press the F-Lock key before using the function keys.)
2. In the VBA Editor, press Ctrl+R to display the Project – VBA Project pane. You will see a list of all open workbooks.
3. Find your workbook in the list. Click the + sign to the left of your workbook to expand the list of workbook objects.
4. If necessary, click the + sign to the left of the Microsoft Excel Objects folder. You should now see a list of worksheets in the workbook. At the bottom of the list is an entry called ThisWorkbook.
5. Double-click the ThisWorkbook entry. You will now have an empty code pane on the right with two dropdowns at the top.
6. From the left dropdown above the code pane, choose Workbook (it is the only item in the dropdown).
Excel will automatically assume that you want to create a Workbook_Open macro and will type the start of the macro in the code pane.
Plan Your Macro
We do not know the exact structure of David’s worksheet. I can guess that there are a few columns of identifying data on the left: perhaps three rows of titles at the top, a blank row, headings in row 5, and data in row 6. Say that cell D5 contains the heading 1, E5 contains 2, F5 contains 3, and so on, out to AH5, which contains 31. In this case, I would want to select row 6 of the correct column. The correct column would be three plus the day of the current month.
Using Cells() Instead of Range()
If you’ve experimented with the macro recorder, you might have seen a recorded macro with a line such as this:
Range(“W6”).Select
The macro recorder prefers to use the Range keyword when referring to cells. However, this is very inefficient. Think about how you would convert the 20 from the date into a cell address. The code becomes very complex, particularly when you need to select column AA through AH (see Figure 1).
Fig. 1
Luckily, Excel VBA offers an easier way to refer to a cell instead of using Range; that is, use the Cells keyword. This keyword is followed by a parenthesis, the row number, a comma, the column number, and then a closing parenthesis. To select cell W6 using Cells, you would type:
Cells(6,23).Select. It is a little backward that Cells asks for the row number first and then the column number. This is just something that you have to get used to.
In the macro, you want to select row 6 and then column 3+current date. The beauty of using the Cells keyword is that you can put a variable or a calculation in for the row and/or column number (see Figure 2).
Fig. 2
This allows the Workbook_Open macro to be very short:
Private Sub Workbook_Open()
Cells(6, 3 + Day(Date)).Select
End Sub
Figure 3 (below) shows the complete macro on the ThisWorkbook code pane.
Fig. 3
If you open the workbook on April 20, the Date variable will automatically return 4/20/2011. The Day(Date) will return 20 since it is the 20th of the month. The Cells command will take you to row 6 and column 3+20 or the 23rd column, which is column W.
Taking the Macro Farther
David’s original question made it sound as if he only has a single worksheet in his workbook. What if he had 12 worksheets, named Jan through Dec? In VBA, the FORMAT function is very similar to the TEXT function in Excel. You can convert the current date to a three-character month abbreviation with
FORMAT(Date, “MMM”)
If you wanted the macro to select the correct worksheet first, change the macro to this:
Private Sub Workbook_Open()
Worksheets(Format(Date, “MMM”)).Select
Cells(6, 3 + Day(Date)).Select
End Sub
If your worksheet has the month names spelled out like January, February, and so on, add one extra M in the Format command:
Private Sub Workbook_Open()
Worksheets(Format(Date, “MMMM”)).Select
Cells(6, 3 + Day(Date)).Select
End Sub
If your worksheet names are in the format of Apr 2011, then use this format command:
Worksheets(Format(Date, “MMM YYYY”)).Select
CFO contributing editor Bill Jelen is the author of 32 books about Excel, including three editions of VBA & Macros for Microsoft Excel. For more information on Excel macros, check out his May 19 Webcast on CFO.com. As always, you can win a copy of one of Bill’s books if your question is selected as a topic for his column. Post your question to the community content block on the right.