Med makroerne herunder kan du finde og slette formler i celler, der refererer til andre projektmapper.
Makroerne finder ikke alle eksterne referencer, da de kun ser i regnearksformlerne.
Sub DeleteOrListLinks () Dim i som heltal Hvis ActiveWorkbook ikke er noget, så afslut Sub i = MsgBox ("JA: Slet eksterne formelhenvisninger" & Chr (13) & _ "NEJ: Angiv eksterne formelhenvisninger", _ vbQuestion + vbYesNoCancel, "Delete eller angiv eksterne formelreferencer ") Vælg Case i Case vbJa 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 Sub i = MsgBox ("Bekræft alle udskiftninger af eksterne formelhenvisninger med værdier?", _ VbQuestion + vbYesNoCancel, "Konverter eksterne formelhenvisninger") ConfirmReplace = False If i = vbCancel Afslut derefter Sub Hvis i = vbYes Herefter ConfirmReplace = True AWS = ActiveSheet.Name Application.ScreenUpdating = Falsk for hver gang i ActiveWorkbook.Worksheets OK = DeleteLinksInWS (ConfirmReplace, ws) Hvis ikke OK Afslut derefter til næste ws Indstil ws = Intet ark (A WS) .Vælg 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 Exit Function Application. StatusBar = "Sletning af eksterne formelreferencer i" & _ ws.Name & "…" ws.Activate For Every cl In ws.UsedRange cFormula = cl.Formula If Len (cFormula)> 0 Then If Left $ (cFormula, 1) = "=" Så hvis InStr (cFormula, "[")> 1 Then If Not ConfirmReplace Then cl.Formula = cl.Value Else Application.ScreenUpdating = True cl.Select i = MsgBox ("Erstat formlen med værdien?", _ vbQuestion + vbYesNoCancel, _ "Erstat ekstern formelreference i" & _ cl.Address (False, False, xlA1) & _ "med celleværdien?") Application.ScreenUpdating = False If i = vbCancel Then DeleteLinksInWS = False Exit Function Slut hvis Hvis i = vbJa Så ved fejl Genoptag næste 'hvis regnearket er beskyttet cl.Formula = cl.Værdi ved fejl Gå til 0 Slut hvis E nd 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 på fejl Genoptag Næste Angiv TargetWS = .Worksheets.Add (før: =. Worksheets (1)) Hvis TargetWS ikke er noget så er 'projektmappen beskyttet Set SourceWB = ActiveWorkbook Set TargetWS = Workbooks.Add.Worksheets (1) SourceWB.Activate Indstil SourceWB = Intet slutter hvis 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 ved fejl Genoptag næste .Name = "Linkliste" Ved fejl GoTo 0 Slut med Set TargetWS = Intet program.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 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 Herefter med TargetWS tRow = .Range ("A" & .Rows.Count) .End (xlUp) .Række + 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