Please note javascript is required for full website functionality.

Locating Links #3

Identifying Links

Sometimes deliberate and sometimes inadvertent, external links often work their way into our Excel workbooks.  To understand what they are, where they are and why they exist, we need to first know which external sources are referenced, because we can’t find what we are looking for if we don’t know what we are looking for.  I feel a U2 song coming on.  That’s right, I can’t live with or without my links – of which there may be more than One [please stop – Ed.].  

Many years ago, I wrote an article that explained how a macro could be employed to list all external links in an Excel workbook:

Sub ListExternalLinks()

Dim ws As Worksheet, TargetWS As Worksheet, SourceWB As Workbook

    If ActiveWorkbook Is Nothing Then Exit Sub

    Application.ScreenUpdating = False

    With ActiveWorkbook

        On Error Resume Next

        Set TargetWS = .Worksheets.Add(Before:=.Worksheets(1))

        If TargetWS Is Nothing Then ' the workbook is protected

            Set SourceWB = ActiveWorkbook

            Set TargetWS = Workbooks.Add.Worksheets(1)


            Set SourceWB = Nothing

        End If

        With TargetWS

            .Range("A1").Formula = "Link No."

            .Range("B1").Formula = "Cell"

            .Range("C1").Formula = "Formula"

            .Range("A1:C1").Font.Bold = True

        End With

        For Each ws In .Worksheets

            If Not ws Is TargetWS Then

                ListLinksInWS ws, TargetWS

            End If

        Next ws

        Set ws = Nothing

    End With

    With TargetWS




        On Error Resume Next

        .Name = "Link List"

        On Error GoTo 0

    End With

    Set TargetWS = Nothing

    Application.ScreenUpdating = True

End Sub

Private Sub ListLinksInWS(ws As Worksheet, TargetWS As Worksheet)

Dim cl As Range, cFormula As String, tRow As Long

    If ws Is Nothing Then Exit Sub

    If TargetWS Is Nothing Then Exit Sub

    Application.StatusBar = "Finding external formula references in " & _

        ws.Name & "..."

    For Each cl In ws.UsedRange

        cFormula = cl.Formula

        If Len(cFormula) > 0 Then

            If Left$(cFormula, 1) = "=" Then

                If InStr(cFormula, "[") > 1 Then

                    With TargetWS

                        tRow = .Range("A" & .Rows.Count).End(xlUp).Row + 1

                        .Range("A" & tRow).Formula = tRow - 1

                        .Range("B" & tRow).Formula = ws.Name & "!" & _

                            cl.Address(False, False, xlA1)

                        .Range("C" & tRow).Formula = "'" & cFormula

                    End With

                End If

            End If

        End If

    Next cl

    Set cl = Nothing

    Application.StatusBar = False

End Sub

If you are terrified of VBA and hate programming, don’t worry – feel free to ignore the above: I have no plans to explain it.  I reproduce it for effect only. 

Whilst working on a client project recently, I came across this wonderful trick that combines old, “outdated” Excel with one of the very latest features in Excel.  It is so simple and just blows the above solution out of the water.  I would love to take the credit for this, but I cannot: long-time Excel Most Valuable Professional (MVP) Bob Umlas, please take a bow, because the crux of this trick belongs to you.

Before Visual Basic for Applications (VBA) came to Excel, coding was undertaken using what were known as Excel 4.0 (“xl4”) macro functions.  These old xl4 macro functions are still doing the rounds because Microsoft cannot get rid of anything, because the software giant knows some spreadsheets still in use were developed probably before the wheel was.

I have written about EVALUATE before, which is a very useful function (it essentially converts text strings into formulae that may be, er, evaluated).  For example, consider the following complex spreadsheet:



would be EVALUATE(1+2) which is 3.  That’s all good, except it doesn’t work unless you use it via a range name definition. 

You won’t find it in Excel Help (“That function isn’t valid.”), but as I say, it is recognised as long as you use it inside an Excel range name.  And its sister function, LINKS, which recognises external links in an Excel workbook, behaves very similarly.

The process for identifying and listing your links in an Excel workbook is very easy.

First, let’s define a range name.  I can do this using ‘Defined Name’ in the Formulas tab of the Ribbon:

This calls the ‘New Name’ dialog:

Here, I have created a new range name, called listlinks, which refers to the formula


If I were to type this formula straight into a cell, I would get the following (aforementioned) error:

but that’s sort of untrue indirectly.  If instead I were to type in =listlinks (i.e. my freshly minted range name), I wouldn’t get an error if the model contains external links and you have an Office 365 version of Excel:

However, if the model doesn’t contain links, I would receive a prima facie error:

In the first instance, Office 365 has spilled the references, i.e. it has listed the references in adjacent cells along the same row.  Good old Excel: it does like to default on the incorrect choice.  To counter this great default “feature”, if I were to use TRANSPOSE, suddenly things become much more readable:

Voila!  All your links presented dynamically.

This is just so much simpler than convoluted VBA code or using third party software.  All you need is the ability to spill your results, i.e. your version of Excel supports dynamic arrays (presently, this means using an Office 365 version of Excel).

That’s all there is to it.

Word to the Wise

Excel 4.0 functions stored in defined names may only be saved in macro-enabled workbooks (.xlsm or .xlsb).  If using this feature in conjunction with dynamic arrays, the file will have to be generated using Excel 365 too, so do be aware of these limitations when incorporating this functionality into existing workbooks.