Views

Histats

Vitrine

VBA Excel - Listando todas as fórmulas de uma planilha - List All Formulas in Workbook

Às vezes temos planilhas tão complexas, tão cheias de fórmulas que chegam a ficar confusas para uma manutenção.

Inline image 2

Quando precisamos revisar essas planilhas com as suas fórmulas, talvez trocando algumas referências, e/ou fazendo leves ajustes, isso pode tornar-se um verdadeiro caos. Ou pode ser que queiramos apenas imprimir as respectivas planilhas com as fórmulas.

Inline image 3

Sim, seria muito mais fácil se simplesmente pudéssemos enxergar o conteúdo das células com as fórmulas e não com os valores que estas refletem. Mas como fazer isso? 

Que tal usar a programação para termos mais detalhes sobre como os cálculos funcionam,  e criarmos uma lista de todas as fórmulas em cada planilha? No código a seguir, uma nova planilha é criada para cada planilha que contém fórmulas. A nova planilha será nomeado  com o prefixo "F_" seguida do nome original.

Inline image 4

Coloque este código num módulo regular:

Sub ListAllFormulas()
'print the formulas in the active workbook
Dim lRow As Long
Dim wb As Workbook
Dim ws As Worksheet
Dim wsNew As Worksheet
Dim c As Range
Dim rngF As Range
Dim strNew As String
Dim strSh As String
On Error Resume Next
Application.DisplayAlerts = False

Set wb = ActiveWorkbook
strSh = "F_"

For Each ws In wb.Worksheets
  lRow = 2
  
  If Left(ws.Name, Len(strSh)) <> strSh Then
    Set rngF = Nothing
    On Error Resume Next
    Set rngF = ws.Cells.SpecialCells(xlCellTypeFormulas, 23)
    If Not rngF Is Nothing Then
      strNew = Left(strSh & ws.Name, 30)
      Worksheets(strNew).Delete
      Set wsNew = Worksheets.Add
      With wsNew
        .Name = strNew
        .Columns("A:E").NumberFormat = "@" 'text format
        .Range(.Cells(1, 1), .Cells(1, 5)).Value _
            = Array("ID", "Sheet", "Cell", "Formula", "Formula R1C1")
        For Each c In rngF
          .Range(.Cells(lRow, 1), .Cells(lRow, 5)).Value _
            = Array(lRow - 1, ws.Name, c.Address(0, 0), _
              c.Formula, c.FormulaR1C1)
          lRow = lRow + 1
        Next c
        .Rows(1).Font.Bold = True
        .Columns("A:E").EntireColumn.AutoFit
      End With 'wsNew
      Set wsNew = Nothing
    End If
  
  End If
Next ws
  
Application.DisplayAlerts = True
End Sub

Neste código as planilha de fórmulas são apagadas antes de criarmos uma nova. No entanto, se desejar excluí-las sem criar um novo conjunto, pode executar o seguinte código:

Sub ClearFormulaSheets()
' Remove formula sheets

Dim wb As Workbook
Dim ws As Worksheet
Dim strSh As String

On Error Resume Next

Let Application.DisplayAlerts = False

Set wb = ActiveWorkbook
strSh = "F_"

Set wb = ActiveWorkbook
  For Each ws In wb.Worksheets
    If Left(ws.Name, Len(strSh)) = strSh Then
      ws.Delete
    End If
  Next ws
  
Application.DisplayAlerts = True
End Sub


ReferenceDebra Dalgleish

Tags: VBA, Excel, formula, workbook

LinkWithinBrazilVBAExcelSpecialist

Related Posts Plugin for WordPress, Blogger...