Build an interactive worksheet in Excel 2011

Microsoft Office brings support for Visual Basic for Applications (VBA) macros back to the Mac. But if you haven’t used VBA before, you might be wondering what you can actually use it for and how difficult it is to use. I’ve got a sample project that will provide some answers to both questions.

The project is a time-tracking spreadsheet that lets you quickly log the start- and finish-times for a task. It then calculates the total time spent on the task and applies your hourly rate to figure out the bill. It’s a good demonstration of how easy it is to embed interactive and automated elements using VBA.

To start, you’ll want to set up a standard worksheet laid out roughly as shown in the screenshot below. You can use any layout that works for your business as long as it includes columns for start time (column B), end time (column C), total minutes (column F) and billing (column G). In this example, column A is formatted to show just the month and year. Columns B and C show the date and time, including AM and PM. Column F, which calculates the total minutes spent on a task, displays just hours and minutes.

Creating a time-stamp button

The first step is to create a button that will insert the current date and time in a selected cell. To do so, open a new worksheet in Excel 2011 and enable the Developer tab in the Ribbon: In preferences (Excel -> Preferences), open the Ribbon pane and select Developer at the bottom of the Tab list.

Now go back to your worksheet and open the Ribbon’s Developer tab. You should see four buttons on the left: Editor, Macros, Record and Relative Reference. Turn on Relative Reference (that means the recorded macro will work with any cell in your worksheet, as opposed to the cell that was selected when you recorded it). Now select a cell somewhere in the Start Time column in your worksheet and then click on the red Record button. Give your macro a name (InsertTime), a description, and a shortcut key (I used Option-Command-T), then click OK to start recording. Type this formula in the cell: =now(). Copy the cell, choose Edit -> Paste Special, then select Values. Click the black Record button to stop the recording.

To examine the VBA code you recorded, click on Macros on the ribbon’s Developer tab, select your macro in the dialog box and click on Edit. The code (with automatically generated comments removed) should look like this:

Sub InsertTime()    ActiveCell.FormulaR1C1 = "=NOW()"    Selection.Copy    Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,_        SkipBlanks:=False, Transpose:=FalseEnd Sub

The now() function inserts the current time and date into a cell. Unfortunately, it also updates itself whenever the worksheet is recalculated; you don’t want it to do that. That’s why you add the copy/paste values step: That overwrites the now() formula with its current results. Test your macro to make sure it’s working correctly—that it inserts the current time as a value in whatever cell you select.

You could use the shortcut key to trigger this macro. But it’s more intuitive (especially if you’re building this sheet for others to use) to trigger it with a button. On the Developer tab, under Form Controls, click on Button and then draw a button on your worksheet. In the Assign Macro dialog box, choose the InsertTime macro. Right-click on the button to reposition, resize or edit it.

To use the time-stamp button to record when you started on or finished a project, just select the appropriate cell in the Start or End column and click on the button.

Calculating with time

Now that you can record the times you worked on a project, the next step is to add formulas that will calculate the total time you spent and then apply that time to your hourly rate.

The first one is simple: In my example, the formula in column F subtracts the Start Time (column B) from the End Time (column C): =C6-B6.

The formula for calculating the billing is a little trickier. You can’t just multiply your hourly rate by the total time in column F. That’s because Excel expresses that time as a simple number. (Without the time/date formatting, Excel expresses one hour as 0.042106481.) You can’t calculate your bill with that number. Instead, you have to translate the time value into hours and minutes and then apply the hourly rate to each portion.

The formula that does that for row 6 is =(HOUR(F6)*hourly_rate)+(MINUTE(F6)*(hourly_rate/60)). The name hourly_rate refers to cell B3 in my sample sheet. To name a cell, you select it, then enter the name in the Name box (the entry area at the far left of the formula bar); the name should have no spaces in it. The billing formula then extracts the number of hours from the total time and multiplies that by the hourly rate, then extracts the number of minutes and multiplies that by the hourly rate divided by 60.

Customizing it

You can tweak this sheet as necessary to suit your needs. For example, if you manage multiple employees or consultants, you could create a separate worksheet (tab) for each one. If you’re a freelancer, you could create a worksheet for each client. If you need to track other data such as job numbers or purchase orders, it’s easy enough to add columns for them. If you bill by the month, you add up the month’s total (as I have in column H) and reformat it (perhaps by changing the border) when it’s invoiced or paid.