V nižšie uvedených makrách môžete vyhľadávať a odstraňovať vzorce v bunkách, ktoré odkazujú na iné zošity.
Makrá nenachádzajú všetky externé odkazy, pretože sa pozerajú iba do vzorcov pracovného hárka.
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 alebo vypísať odkazy na externé vzorce ") Vyberte prípad i Prípad vbAno DeleteExternalFormulaReferences Prípad vbNo ListExternalFormulaReferences Koniec Vybrať Koniec Sub Sub Sub DeleteExternalFormulaReferences () Dim ws ako pracovný hárok, AWS ako reťazec, ConfirmReplace ako boolean Dim i ako celé číslo, OK ako boolean, ak aktívnyWork Sub i = MsgBox („Potvrdiť všetky nahradenia odkazov na externé vzorce hodnotami?“, _ VbQuestion + vbYesNoCancel, „Previesť odkazy na externé vzorce“) ConfirmReplace = False Ak i = vbCancel Potom ukončiť Sub Ak i = vbYes potom ConfirmReplace = True AWS = ActiveSheet.Name Application.ScreenUpdating = False for every ws in ActiveWorkbook.Worksheets OK = DeleteLinksInWS (ConfirmReplace, ws) If Not OK then Exit for Next ws Set ws = Nothing Sheets (A WS). Vyberte položku 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 = "Odstraňovanie odkazov na externé vzorce v" & _ 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 ("Nahradiť vzorec hodnotou?", _ vbQuestion + vbYesNoCancel, _ "Nahradiť odkaz na externý vzorec v" & _ cl.Address (False, False, xlA1) & _ "hodnotou bunky?") Application.ScreenUpdating = False If i = vbCancel Then DeleteLinksInWS = False Exit Function End If If i = vbYes Then On Error Resume Next 'in if the worksheet is protected cl.Formula = cl.Value On Error GoTo 0 End If E nd If End If End If End If Next cl Set cl = Nothing Application.StatusBar = False End Function Sub SubExternalFormulaReferences () Dim ws As Worksheet, TargetWS As Workheet, 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 'workbook is protected Set SourceWB = ActiveWorkbook Set TargetWS = Workbooks.Add.Worksheets (1) SourceWB.Activate Nastaviť SourceWB = nič sa nekončí, ak s TargetWS .Range ("A1"). Formula = "Sequence" .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 .Parent.Activate .Activate .Columns ("A: C"). Automatické prispôsobenie pri chybe Pokračovať ďalej .Name = "Zoznam odkazov" pri chybe GoTo 0 Koniec nastaveným cieľom TargetWS = Nič 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 odkazy na vzorce v "& _ ws.Name &" … "Pre každý cl In ws.UsedRange cFormula = cl.Formula If Len (cFormula)> 0 Then If Left $ (cFormula, 1) =" = "Then If InStr (cFormula , "[")> 1 Potom s TargetWS tRow = .Range ("A" & .Rows.Count) .End (xlUp) .Row + 1 .Range ("A" & tRow) .Formula = tRow - 1 .Range ("B" & tRow). Vzorec = ws.Meno & "!" & _ cl.Address (False, False, xlA1). Range ("C" & tRow) .Formula = "'" & cFormula Koniec s koncom, ak s koncom, ak s koncom, ak s nasledujúcim cl Set cl = s ničím Application.StatusBar = s falošným koncom