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

| Blog Office VBA | Blog Excel | Blog Access | Blog Infochart | 

Você tem direito as informações da sua empresa

MIS - Usufruindo a excelência na análise das suas Informações

Dados ou Informações?

Dashboards - 8 Importantes características desejáveis

Key Performance Indicators - 7 Características desejáveis

Tópicos relacionados:

Business English - Termos em inglês do mundo dos negócios - Speaking the lingo

Acrônimos - Technology, Electronics and Games Acronyms

13 Dicas para se Falar em Público

Os 10 mandamentos do gerenciamento de projetos

KPI - Key Performance Indicator

Acesse: http://goo.gl/0v6AO   

À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

End Sub

Reference: Debra Dalgleish

Tags: VBA, Excel, formula, workbook