✔ Programação GLOBAL®: VBA Excel - Listando todas as fórmulas de uma planilha

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

| Blog Office VBA | Blog Excel | Blog Access |

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

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.

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.

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