Med makroene nedenfor kan du finne og slette formler i celler som refererer til andre arbeidsbøker.
Makroene finner ikke alle eksterne referanser siden de bare ser i regnearkformlene.
Sub DeleteOrListLinks () Dim i As Integer If ActiveWorkbook Is Nothing Then Exit Sub i = MsgBox ("YES: Delete external formula references" & Chr (13) & _ "NO: List external formula references", _ vbQuestion + vbYesNoCancel, "Delete eller liste eksterne formelreferanser ") Velg Case i Case vbJa DeleteExternalFormulaReferences Case vbNo ListExternalFormulaReferences End Velg 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 Sub i = MsgBox ("Bekreft alle erstatninger av eksterne formelreferanser med verdier?", _ VbQuestion + vbYesNoCancel, "Konverter eksterne formelreferanser") ConfirmReplace = False If i = vbCancel Avslutt deretter Sub hvis i = vbYes deretter ConfirmReplace = True AWS = ActiveSheet.Name Application.ScreenUpdating = Falsk for hver uke i ActiveWorkbook.Worksheets OK = DeleteLinksInWS (ConfirmReplace, ws) Hvis ikke OK Avslutt for neste ws Sett ws = ingenting ark (A WS) .Velg 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 And Exit Function Application. StatusBar = "Sletter eksterne formelreferanser 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 ("Erstatt formelen med verdien?", _ vbQuestion + vbYesNoCancel, _ "Erstatt ekstern formelreferanse i" & _ cl.Address (False, False, xlA1) & _ "med celleverdien?") Application.ScreenUpdating = False If i = vbCancel Then DeleteLinksInWS = False Exit Function Slutt hvis hvis i = vbJa, deretter på feil, fortsett neste 'hvis regnearket er beskyttet cl.Formula = cl.Value On Error GoTo 0 End If E nd If End If End If End If Next 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 Avslutt 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 Angi kildeWB = ingenting slutter hvis det er med TargetWS .Range ("A1"). Formel = "Sekvens" .Range ("B1"). Formel = "Cell" .Range ("C1"). Formel = "Formel" .Range ( "A1: C1")). Font.Bold = True End With For Every 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 På Feil Fortsett Neste .Name = "Linkliste" På Feil Gå til 0 Slutt med Angi TargetWS = Ingenting Application.ScreenUpdati ng = 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 formelhenvisninger i "& _ ws.Name &" … "For hver 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 if Next cl Set cl = Nothing Application.StatusBar = False End Sub