Criação de um Relatório de nome com código VBA

Se você usar lotes de células nomeadas e faixas em sua pasta de trabalho, você pode se surpreender ao descobrir que o Excel não fornece nenhuma maneira para listar os detalhes de cada nome. Informações úteis aparece na caixa de diálogo Nome do Manager, mas não há nenhuma maneira de exibir essas informações de uma maneira que pode ser impresso.

O código VBA oferecido aqui gera um relatório útil que descreve os nomes definidos em qualquer livro.

Exemplo Nome do relatório

Aqui, você vê um exemplo de um relatório de Nome.

image0.jpg

O relatório, que é criado em uma nova planilha, inclui as seguintes informações para cada nome:

  • Nome: O nome.

  • Refere-se a: A definição para o nome. Geralmente, isto será uma célula ou gama, mas um nome pode também definir fórmula.

  • células: O número de células contidas no intervalo nomeado. Para fórmulas nomeados, este campo exibe # N / A.

  • Escopo: O escopo do nome - ou pasta de trabalho ou o nome da planilha específica em que o nome é válido.

  • Escondido: Verdadeiro se o nome está oculto. nomes ocultos são criados por alguns add-ins (como Solver), e não aparecem na caixa de diálogo Nome do Manager.

  • Erro: True se o nome contém uma referência errada.

  • Ligação: Um link que, quando clicado, ativa o intervalo nomeado. Somente nomes que se referem a células ou intervalos de incluir um link.

  • Comente: O comentário para o nome, se houver.

O código VBA

Para usar esse código, pressione Alt + F11 para ativar o Editor do Visual Basic. Em seguida, escolha Inserir - Módulo para inserir um novo módulo VBA. Copie o código e cole-o para o novo módulo.

Sub GenerateNameReport () 'Gera um relatório para todos os nomes na pasta de trabalho "(Não inclui nomes tabela) Dim n Como NameDim Row Como LongDim CellCount Como Sair Variant' se nenhum namesIf ActiveWorkbook.Names.Count = 0 ThenMsgBox Nº 147-A pasta de trabalho ativa não tem nomes definidos. Exit # 148-Exit SubEnd Se 'se pasta de trabalho é protectedIf ActiveWorkbook.ProtectStructure ThenMsgBox Nº 147-A nova folha não pode ser adicionado porque o livro está protegido nº 148-Exit SubEnd Se 'Inserir uma nova folha para a reportActiveWorkbook.Worksheets.AddActiveSheet.Move Depois:. = Sheets (ActiveWorkbook.Sheets.Count) ActiveWindow.DisplayGridlines = false 'Adicionar primeira linha de titleRange (# 147-A1: H1 # 148 -).. MergeWith Range (# 147-A1 # 148 -) Valor = # 147 Nome do relatório para: # 147- ActiveWorkbook.Name.Font.Size = 14.Font.Bold = True.HorizontalAlignment = xlCenterEnd Com 'Adicionar segunda linha de titleRange (# 147-A2: H2 # 148 -). MergeWith Range (# 147-A2 # 148-). valor = # 147-Generated # 147- Now.HorizontalAlignment = xlCenterEnd Com 'Adicionar o headersRange (# 147-A4: H4 # 148-) = Array (# 147-Name # 148-, # 147 RefersTo # 148-, nº 147-Cells # 148-, _ # 147-Scope # 148-, # 147 Escondido # 148-, # 147-Error # 148-, # 147-Link # 148-, # 147 Comment # 148-) 'loop através da Error Resume namesRow = 4On NextFor Cada n Em ActiveWorkbook.NamesRow = Row + 1'Column A: nameif n.Name Como # 147 - * * # 148- ThenCells (Row, 1) = Split (n.Name, ! # 147 - # 148 -) (1) 'Remover nameElseCells folha (linha, 1) = n.NameEnd If'Column B: RefersToCells (Row, 2) = # 147 - '# 147- n.RefersTo'Column C: Número de cellsCellCount = CVErr (xlErrNA) 'valor de retorno para o nome formulaCellCount = n.RefersToRange.CountLargeCells (Row, 3) = CellCount' Coluna D: ScopeIf n.Name Como # 147 - * * # 148- ThenCells (linha, 4) = Split (n.Name, # 147 - # 148 -) (0) 'nameCells folha extrato (Row, 4) = Replace (Cells (Row, 4), # 147 - '# 147-, # 147- # 147-) 'remover apostrophesElseCells (Row, 4) = # 147-livro # 148-End If'Column E: statusCells oculto (Row, 5) = Não n.Visible'Column F: Erroroneous nameCells (Row, 6) = n.RefersTo Como # 147 - * [#] REF * # 148-'Column G: HyperlinkIf Não Application.IsNA (Cells (Row, 3)) ThenActiveSheet.Hyperlinks.Add _Anchor: = Cells (Row, 7), _Address: = # 147 - # 147-, _SubAddress: = n.Name, _TextToDisplay: = n.NameEnd If'Column H: CommentCells (Row, 8) = n.CommentNext n 'convertê-la numa tableActiveSheet.ListObjects.Add _SourceType: = xlSrcRange, fonte_ : = Range (# 147-A4 # 148 -) CurrentRegion 'Ajustar as widthsColumns coluna (# 147-a: H # 148 -).. EntireColumn.AutoFitEnd Sub

Gerando um relatório

execute o GenerateNameReport procedimento, bem como o relatório é gerado em uma nova planilha na pasta de trabalho ativa. O código não tem que ser na pasta de trabalho que contém os nomes para o relatório.

Se você encontrar este código útil, você pode querer armazená-lo em sua pasta de trabalho macro pessoal, ou criar um add-in.

Semelhante

menu