Med makron nedan kan du hitta och ta bort formler i celler som refererar till andra arbetsböcker.
Makronen hittar inte alla externa referenser eftersom de bara ser i kalkylbladets formler.
Sub DeleteOrListLinks () Dim i som heltal Om ActiveWorkbook inte är något Avsluta Sub i = MsgBox ("JA: Radera externa formelreferenser" & Chr (13) & _ "NEJ: Lista externa formelreferenser", _ vbQuestion + vbYesNoCancel, "Radera eller lista externa formelreferenser ") Välj 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 Exit Sub i = MsgBox ("Bekräfta alla ersättningar av externa formelreferenser med värden?", _ VbQuestion + vbYesNoCancel, "Konvertera externa formelreferenser") ConfirmReplace = False If i = vbCancel Avsluta sedan Sub om i = vbYes Sedan ConfirmReplace = True AWS = ActiveSheet.Name Application.ScreenUpdating = Falskt för varje gång i ActiveWorkbook.Worksheets OK = DeleteLinksInWS (ConfirmReplace, ws) Om inte OK Avsluta för nästa ws Ställ in ws = Inget blad (A WS) .Välj 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 = "Radera externa formelreferenser i" & _ ws.Name & "…" ws.Activate For Each cl In ws.UsedRange cFormula = cl.Formula If Len (cFormula)> 0 Then If Left $ (cFormula, 1) = "=" Då If InStr (cFormula, "[")> 1 Then If Not ConfirmReplace Then cl.Formula = cl.Value Else Application.ScreenUpdating = True cl.Select i = MsgBox ("Ersätt formeln med värdet?", _ vbQuestion + vbYesNoCancel, _ "Ersätt extern formelreferens i" & _ cl.Address (False, False, xlA1) & _ "med cellvärdet?") Application.ScreenUpdating = False If i = vbCancel Then DeleteLinksInWS = False Exit Function Avsluta om om i = vbJa Då vid fel Återuppta nästa 'om arbetsbladet är skyddat cl.Formula = cl.Value On Error GoTo 0 End If E nd Om Slut Om Slut Om Slut Om Nästa cl Ange cl = Inget Application.StatusBar = Falsk Slutfunktion 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 Ange SourceWB = Ingenting slutar om med TargetWS .Range ("A1"). Formel = "Sekvens" .Range ("B1"). Formel = "Cell" .Range ("C1"). Formel = "Formel" .Range ( "A1: C1"). Font.Bold = True End With för varje ws i. 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 Vid fel Återuppta nästa .Name = "Länklista" Vid fel Gå till 0 Avsluta med Set 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 formelreferenser i "& _ ws.Name &" … "För varje kl In ws.UsedRange cFormula = cl.Formula If Len (cFormula)> 0 Then If Left $ (cFormula, 1) =" = "Then If InStr (cFormula , "[")> 1 Sedan med TargetWS tRow = .Range ("A" & .Rows.Count) .End (xlUp) .Rad + 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