Índice
Com as macros abaixo, você pode localizar e excluir fórmulas em células que se referem a outras pastas de trabalho.
As macros não encontram todas as referências externas, uma vez que só procuram nas fórmulas da planilha.
Sub DeleteOrListLinks () Dim i As Integer Se ActiveWorkbook não for nada, então saia Sub i = MsgBox ("YES: Exclua referências de fórmulas externas" & Chr (13) & _ "NO: Listar referências de fórmulas externas", _ vbQuestion + vbYesNoCancel, "Delete ou listar referências de fórmulas externas ") Selecione Caso i Caso vb Sim DeleteExternalFormulaReferences Caso vbNo ListExternalFormulaReferences End Selecione End Sub Sub DeleteExternalFormulaReferences () Dim ws As Planilha, AWS As String, ConfirmReplace As Booleano Dim i As Integer, OK Sair Como Booleano If ActiveWorkbook Is Nothing Then Sub i = MsgBox ("Confirmar todas as substituições de referências de fórmulas externas com valores?", _ VbQuestion + vbYesNoCancel, "Converter referências de fórmulas externas") 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) Se não estiver OK Então saia para o próximo ws Set ws = Nothing Sheets (A WS) .Select Application.ScreenUpdating = True End Sub Função privada DeleteLinksInWS (ConfirmReplace As Boolean, _ ws As Worksheet) As Boolean Dim cl As Range, cFormula As String, i As Integer DeleteLinksInWS = True Se ws Is Nothing Então Saia do aplicativo de função. StatusBar = "Excluindo referências de fórmulas externas em" & _ 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 ("Substituir a fórmula pelo valor?", _ vbQuestion + vbYesNoCancel, _ "Substitua a referência de fórmula externa em" & _ cl.Address (False, False, xlA1) & _ "pelo valor da célula?") Application.ScreenUpdating = False If i = vbCancel Then DeleteLinksInWS = False Função de Saída End If If i = vbYes Then On Error Resume Next 'caso a planilha esteja protegida cl.Formula = cl.Value On Error GoTo 0 End If E nd If End If End If End If Next cl Definir cl = Nothing Application.StatusBar = False End Function Sub ListExternalFormulaReferences () Dim ws As Worksheet, TargetWS As Worksheet, SourceWB As Workbook Se ActiveWorkbook for Nothing Then Exit Sub Application.ScreenUpdating = False With ActiveWorkbook com erro Resume Next Set TargetWS = .Worksheets.Add (Before: =. Worksheets (1)) If TargetWS Is Nothing Then 'a pasta de trabalho está protegida Set SourceWB = ActiveWorkbook Set TargetWS = Workbooks.Add.Worksheets (1) SourceWB.Activate Definir SourceWB = Nothing End If With 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 Se 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"). AutoAjuste no erro Resume Next .Name = "Lista de links" no erro GoTo 0 End With Set TargetWS = Nothing 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 Se ws Is Nothing Then Sair Sub Se TargetWS Is Nothing Then Sair Sub Application.StatusBar = "Finding external referências de fórmula em "& _ ws.Name &"… "Para Cada cl Em ws.UsedRange cFormula = cl.Formula If Len (cFormula)> 0 Then If Left $ (cFormula, 1) =" = "Then If InStr (cFormula , "[")> 1 Então com 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
