Category Archives: Excel tips

Adding a table of contents to workbooks in Excel

I find myself often using a number of worksheets in a workbook and navigating through them is cumbersome. Googling for help, I found a a post on the office blogs where a simple macro would automagically create a table of contents (TOC) of all worksheets with hyperlink shortcuts.

Microsoft Excel - Foo.xlsx_2014-06-26_08-32-46I’ve slightly improved the version, the result:

  • the macro still creates a TOC 😉
  • you can add worksheets, run the macro again and it will preserve whatever you have in column B (e.g. a description)
  • you can quickly jump back to your TOC with CTRL-G gg. The macro adds a shortcut to the TOC sheet, by naming cell A1 “gg”.

Caveat: if you change the order of the tabs and run the macro again, you’ll have to change the order of column B manually.

No idea, how to use this code? Check out, how to add VBA code to your computer.

Sub swaCreateTOC() 

    Dim wbBook As Workbook
    Dim wsActive As Worksheet
    Dim wsSheet As Worksheet 

    Dim lnRow As Long
    Dim lnPages As Long
    Dim lnCount As Long 

    Dim DataRange As Variant
    Dim Irow As Long
    Dim Icol As Integer
    Dim MyVar As Double 

    Set wbBook = ActiveWorkbook 

    With Application
        .DisplayAlerts = False
        .ScreenUpdating = False
    End With 

    'If the TOC sheet already exist delete it and add a new

    On Error Resume Next
    With wbBook
        DataRange = .Worksheets("TOC").Range("B1:B10000").Value ' read all the values at once from the Excel grid, put into an array
        .Worksheets.Add Before:=.Worksheets(1)
    End With
    On Error GoTo 0 

    Set wsActive = wbBook.ActiveSheet
    With wsActive
        .Name = "TOC"
        With .Range("A1:B1")
            .Value = VBA.Array("Worksheet", "Content")
            .Font.Bold = True
        End With
    End With 

    If Not IsEmpty(DataRange) Then
        wsActive.Range("B1:B10000").Value = DataRange ' writes all the results back to the range at once
    End If 

    lnRow = 2
    lnCount = 1 

    'Iterate through the worksheets in the workbook and create
    'sheetnames, add hyperlink and count & write the running number
    'of pages to be printed for each sheet on the TOC sheet.
    For Each wsSheet In wbBook.Worksheets
        If wsSheet.Name <> wsActive.Name Then
            With wsActive
                .Hyperlinks.Add .Cells(lnRow, 1), "", _
                SubAddress:="'" & wsSheet.Name & "'!A1", _
                lnPages = wsSheet.PageSetup.Pages().Count
            End With
            lnRow = lnRow + 1
            lnCount = lnCount + 1
        End If
    Next wsSheet 

    ActiveWindow.DisplayGridlines = False
    ' now add the name "gg" to A1 of "TOC", so you can jump to it with CTRL-G gg
    wbBook.Names.Add "gg", RefersTo:=Sheets("TOC").Range("A1") 

    With Application
        .DisplayAlerts = True
        .ScreenUpdating = True
    End With 


End Sub

How to show trends in MS Project

In short, you can’t ;-). MS Project is good at showing all sorts of info on your project for any given point in time. It does not, however, contain historic data to calculate trends. But there is a workaround. In each reporting cycle (say every week), save your project plan to a new file. Then use Excel and a macro to read all past project plans and chart the trend. Use pivot tables to drill down to deliverable or sub-projects. Here is how to chart work compared to baseline work over time. Continue reading

Dump actual work from MS Project into Excel

As you may know, MS Project may occasionally behave divaesque. To make matters worse, older versions (like the one, I’m using) know only one (!) undo step. In order to check if some of my changes have screwed up actual work of the past, I dump all actual work from project start to project finish into an Excel file and analyze it with a pivot table. I can now easily compare actual work before and after I’ve made changes to the project. Continue reading

How to add VBA code to your computer

First of all, all VBA examples on this website use MS Excel 2003 2010 and/or MS Project 2003 2010. Most of the stuff may run with later versions, YMMV. Also, keep an eye on formulas in Excel: you may need to replace each colon with a comma (or tweak your regional settings in Windows).

Add code to MS Excel

  • I store all my macros in a central place (personal.xls), so that I can use them in all spreadsheets.
  • On my Win7 box, the file is located in %APPDATA%\Microsoft\Excel\XLSTART
  • Read, how to add macros to your personal.xls.
  • Hide personal.xls when you’re not working on your macros (via Window | Hide).

Add code to MS Project

  • I store all my macros in a central place (global.mpt), so that I can use them in all projects.
  • On my Win7 box, the file is located in %APPDATA%\Microsoft\MS Project\11\1033
  • You can move macros and other settings between different project files and global.mpt using MS Project’s organizer function, see Tools | Organizer.

Don’t forget to backup both files on a regular basis.