Now I’m not an Excel genius or anything so if I’m doing this wrong, let me know. Anyway, I went in TFS2010 to play around with the TFS2010 Agile Iteration Backlog workbook (Iteration Backlog.xlsm) and I ran in to some problems with Excel macro execution. The Iteration Backlog workbook helps you to plan out the work in your Agile Iterations (or Scrum Sprints) using historical data from TFS about previous sprints and items in your product backlog. In order to do it’s magic, the worksheet needs to be able to execute its macros.
To open the iteration backlog workbook, open up Visual Studio 2010, go to Team Explorer, and find your Team Project. From the Team Project, drill in to Documents –> Shared Documents –> Iteration 1. Now you should see Iteration Backlog.xlsm.
If you double-click the document, you’ll see the following File Download warning.
Hit OK.
Excel should pop up and you should see a Server Workbook warning and a Security Warning.
According to the current version of the documentation, you should be able to click the Options… button on the Security Warning to enable the Macros. But instead -- at least on my machine – Excel goes in to a “hourglass” waiting mode and then I get a TF208085 error from TFS saying that “Office Excel has disabled macros in the workbook. To recover the macros, save and re-open the workbook”.
Hmm. Well, I don’t really want to do that. Click OK on the Excel message box and then POOF! the Security Warning’s Option button disappears.
At this point, you may be wondering “well, are the macros really disabled?” Yes. Totally disabled. In case you’re wondering, click on the Capacity worksheet tab at the bottom of the Excel window.
You should now be on the Capacity worksheet and you should see a bunch of Macro warnings.
(Doh!)
Here’s how to enable the macros.
Click on the big button in the upper left corner of Excel.
This should bring up a menu like the next picture.
Click on Excel Options.
You should now be on the Excel Options dialog.
Select the Popular tab page.
Check **Show Developer tab in the Ribbon.
**Click the OK button.
The Developer tab should now be visible in Excel.
Click the Developer tab and locate the Code section of the tab.
Click Macro Security to bring up the Trust Center dialog.
Select Enable all macros (not recommended; potentially dangerous code can run). (NOTE: this is the part where I’m wondering if there’s a better way to make this work. That warning on the radio button option is sounds pretty dire. If you know of a better way, please let me know.)
Click OK.
Now that you’ve changed the Macro Settings, you need to re-open the Excel workbook.
Close Excel and re-open Iteration Backlog.xlsm from Team Explorer.
Now the macros should be working.
-Ben
