Macro de índice - dicas do Excel

Obrigado a Matt que enviou a pergunta sobre o Excel desta semana:

Tenho uma pasta de trabalho grande e crescente do Excel (muitas planilhas). Eu incluí números de página no rodapé durante a impressão, no entanto, está ficando cada vez mais difícil navegar quando estamos em uma reunião. Existe uma maneira de imprimir um índice baseado em nomes de planilhas do Excel para que eu e a equipe possamos ir rapidamente para a página #xx?

Esta é uma ótima idéia. A primeira sugestão simples é incluir o nome da folha nos rodapés de sua impressão. Quando você clica em "Rodapé personalizado" na caixa de diálogo Configurar página / Rodapé do cabeçalho, há 7 ícones. O ícone mais à direita parece um cartão de índice com três guias. Clicar na caixa Seção direita: e clicar nesse ícone fará com que o nome da folha seja impresso em cada folha. Isso por si só pode ajudar a navegar pelo relatório.

MrExcel gosta da ideia de ter uma macro para criar o índice analítico. O principal problema é que o Excel não calcula quantas páginas impressas estão em uma planilha até que você faça uma visualização da impressão. Assim, a macro permite que o usuário saiba que está prestes a ver uma Visualização da Impressão e pede para dispensá-la com um clique no botão Fechar.

A macro percorre cada planilha da pasta de trabalho. Em seu estado atual, ele coleta informações do nome de cada planilha. Também incluí duas outras linhas que estão comentadas. Se você preferir obter a descrição do cabeçalho esquerdo ou de um título na célula A1, existem linhas de amostra para fazer qualquer um desses também. Apenas descomente aquele que você deseja usar.

A macro calcula quantas páginas adicionando um ao número de quebras de página horizontais (HPageBreaks.count). Ele adiciona um ao número de quebras de página verticais (VPageBreaks.Count). Ele multiplica esses dois números para calcular o número de páginas na planilha. Se algum leitor fiel tiver uma maneira melhor de fazer isso, por favor, me avise. O método atual de contagem das quebras de página é terrivelmente lento. Não consegui encontrar uma propriedade que me dissesse quantas páginas impressas existem, mas você pensaria que o Excel incluiria uma.

O último truque foi entrar no intervalo de páginas. Se uma planilha estiver nas páginas "3 a 4", o Excel tratará isso como uma data e inserirá 4 de março. Ao definir o formato da célula para texto com o caractere "@", as páginas entram corretamente.

Aqui está a macro:

Sub CreateTableOfContents() ' Copyright 1999.com ' Determine if there is already a Table of Contents TOCFound = False For Each s In Worksheets If s.Name = "Table of Contents" Then TOCFound = True Exit For End If Next s If Not TOCFound Then Sheets.Add Before:=Worksheets(1) ActiveSheet.Name = "Table of Contents" End If ' Set up the table of contents page TOCRow = 7 PageCount = 0 Sheets("Table of Contents").Select Range("A2").Value = "Table of Contents" Range("A6").CurrentRegion.Clear Range("A6").Value = "Subject" Range("A6").ColumnWidth = 36 Range("B6").Value = "Page(s)" Range("B6").ColumnWidth = 12 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Worksheets.Select Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." Msgbox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information For Each s In Worksheets s.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("Table of Contents").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 Next s End Sub

Abaixo está uma macro equivalente, atualizada com várias novas técnicas de macro.

Sub CreateTableOfContents() ' Copyright 2002.com ' Determine if there is already a Table of Contents ' Assume it is there, and if it is not, it will raise an error ' if the Err system variable is> 0, you know the sheet is not there Dim WST As Worksheet On Error Resume Next Set WST = Worksheets("Table of Contents") If Not Err = 0 Then ' The Table of contents doesn't exist. Add it Set WST = Worksheets.Add(Before:=Worksheets(1)) WST.Name = "TOC" End If On Error GoTo 0 ' Set up the table of contents page WST.(A2) = "Table of Contents" With WST.(A6) .CurrentRegion.Clear .Value = "Subject" End With WST.(B6) = "Page(s)" WST.Range("A1:B1").ColumnWidth = Array(36, 12) TOCRow = 7 PageCount = 0 ' Do a print preview on all sheets so Excel calcs page breaks ' The user must manually close the PrintPreview window Msg = "Excel needs to do a print preview to calculate the number of pages. " Msg = Msg & "Please dismiss the print preview by clicking close." MsgBox Msg ActiveWindow.SelectedSheets.PrintPreview ' Loop through each sheet, collecting TOC information ' Loop through each sheet, collecting TOC information For Each S In Worksheets If S.Visible = -1 Then S.Select ' Use any one of the following 3 lines ThisName = ActiveSheet.Name 'ThisName = Range("A1").Value 'ThisName = ActiveSheet.PageSetup.LeftHeader HPages = ActiveSheet.HPageBreaks.Count + 1 VPages = ActiveSheet.VPageBreaks.Count + 1 ThisPages = HPages * VPages ' Enter info about this sheet on TOC Sheets("TOC").Select Range("A" & TOCRow).Value = ThisName Range("B" & TOCRow).NumberFormat = "@" If ThisPages = 1 Then Range("B" & TOCRow).Value = PageCount + 1 & " " Else Range("B" & TOCRow).Value = PageCount + 1 & " - " & PageCount + ThisPages End If PageCount = PageCount + ThisPages TOCRow = TOCRow + 1 End If Next S End Sub

Um breve resumo das novas técnicas de macro na macro mais recente:

  • Raramente é necessário selecionar uma folha
  • Em vez de percorrer cada planilha da pasta de trabalho à procura de uma planilha chamada Índice, a segunda macro simplesmente assume que ela está lá e verifica o status da variável Err. Se Err for diferente de 0, sabemos que a planilha não existe e precisa ser adicionada.
  • WST é uma variável de objeto e é definida como a planilha de índice analítico. Portanto, qualquer referência a Planilhas ("Índice"). pode ser substituído por WST.
  • A construção Células (linha, coluna) é mais eficiente do que o cálculo de Intervalo ("A" e TOCRow). Como Cells () espera parâmetros numéricos, Range ("A" & TOCRow) torna-se células (TOCRow, 1)
  • Os colchetes são usados ​​como uma forma abreviada de se referir a Faixa ("A1").

Artigos interessantes...