List, endre eller slett eksterne formelreferanser (linker)

Ved hjelp av makroene nedenfor kan man lett finne og slette formler i celler som refererer til andre arbeidsbøker. Makroene finner ikke samtlige eksterne referanser da de kun ser etter linker i regnearkformlene.
Sub DeleteOrListLinks()
Dim i As Integer
    If ActiveWorkbook Is Nothing Then Exit Sub
    i = MsgBox("JA: Slett eksterne formel referanser" & Chr(13) & _
        "NEI: List eksterne formel referanser", _
        vbQuestion + vbYesNoCancel, _
        "Slett eller list eksterne formel referanser")
    Select Case i
        Case vbYes
            DeleteExternalFormulaReferences
        Case vbNo
            ListExternalFormulaReferences
    End Select
End Sub

Sub DeleteExternalFormulaReferences()
Dim ws As Worksheet, AWS As String, ConfirmReplace As Boolean
Dim i As Integer, OK As Boolean
    If ActiveWorkbook Is Nothing Then Exit Sub
    i = MsgBox("Vil du bekrefte hver enkelt endring av de eksterne formel referansene?", _ 
	vbQuestion + vbYesNoCancel, "Endre eksterne formel referanser")
    ConfirmReplace = False
    If i = vbCancel Then Exit Sub
    If i = vbYes Then ConfirmReplace = True
    AWS = ActiveSheet.Name
    Application.ScreenUpdating = False
    For Each ws In ActiveWorkbook.Worksheets
        OK = DeleteLinksInWS(ConfirmReplace, ws)
        If Not OK Then Exit For
    Next ws
    Set ws = Nothing
    Sheets(AWS).Select
    Application.ScreenUpdating = True
End Sub

Private Function DeleteLinksInWS(ConfirmReplace As Boolean, ws As Worksheet) As Boolean
Dim cl As Range, cFormula As String, i As Integer
    DeleteLinksInWS = True
    If ws Is Nothing Then Exit Function
    Application.StatusBar = "Endrer eksterne formel refereranser i " & ws.Name & "..."
    ws.Activate
    For Each cl In ws.UsedRange
        cFormula = cl.Formula
        If Len(cFormula) > 0 Then
            If Left$(cFormula, 1) = "=" Then
                If InStr(cFormula, "[") > 1 Then
                    If Not ConfirmReplace Then
                        cl.Formula = cl.Value
                    Else
                        Application.ScreenUpdating = True
                        cl.Select
                        i = MsgBox("Vil du erstatte denne formelen med verdien?", _
                            vbQuestion + vbYesNoCancel, _
                            "Erstatt ekstern referanse i " & cl.Address(False, False, xlA1) & _
                            " med cellens verdi?")
                        Application.ScreenUpdating = False
                        If i = vbCancel Then
                            DeleteLinksInWS = False
                            Exit Function
                        End If
                        If i = vbYes Then
                            On Error Resume Next ' in case the worksheet is protected
                            cl.Formula = cl.Value
                            On Error GoTo 0
                        End If
                    End If
                End If
            End If
        End If
    Next cl
    Set cl = Nothing
    Application.StatusBar = False
End Function

Sub ListExternalFormulaReferences()
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)
            SourceWB.Activate
            Set SourceWB = Nothing
        End If
        With TargetWS
            .Range("A1").Formula = "Nr"
            .Range("B1").Formula = "Celle"
            .Range("C1").Formula = "Formel"
            .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
        .Parent.Activate
        .Activate
        .Columns("A:C").AutoFit
        On Error Resume Next
        .Name = "Link liste"
        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 = "Finner eksterne formel referanser i " & _
        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
Dokumentet er sist oppdatert 2002-05-20 12:37:25 Utskriftsvennlig versjon