Guia VBA para Tabelas no Excel

Guia VBA para Tabelas no Excel

Este é um guia para selecionar, incluir, deletar ou alterar dados de tabelas no Excel com o VBA.

O uso de tabelas no Excel é não só uma prática comum, como também recomendada.

O uso do recurso tabela no Excel tem vários benefícios, entre eles:

  1. Autoexpansão dos dados
  2. Formatação padronizada
  3. Cópia de fórmulas automática
  4. Nomeação automática…

São muitos os benefícios, como disse, então é comum que precisemos utilizar o VBA para automatizar o trabalho com estas tabelas no Excel.

Para isso criei este Guia aonde você irá encontrar diversos códigos que lhe auxiliarão a manipular tabelas com VBA no Excel.

Partes da Tabela VBA Excel

Em vermelho abaixo temos uma tabela inteira. Quando nos referimos à tabela como um todo temos que usar a propriedade Range do VBA Excel.

Guia Excel Tabelas VBA 1 - range

O intervalo abaixo se refere apenas ao conteúdo da tabela. Para acessar este conteúdo da tabela por VBA demos utilizar a propriedade DataBodyRange.

Guia Excel Tabelas VBA 1 - data body range

O intervalo em vermelho abaixo se refere ao cabeçalho da tabela.

Este cabeçalho da tabela pode ser referenciado via VBA com propriedade HeaderRowRange no Excel.

Guia Excel Tabelas VBA 1 - header row range

O intervalo em vermelho da tabela abaixo é uma coluna completa.

Para referenciar esta coluna da tabela com VBA você pode utilizar a propriedade ListColumns no Excel.

Guia Excel Tabelas VBA 1 - list columns

A parte selecionada abaixo é uma linha da tabela.

Para referenciar uma linha de uma tabela Excel com VBA você pode utilizar a propriedade ListRows.

Guia Excel Tabelas VBA 1 - list rows

A última linha pode conter totais na tabela.

Para acessar esta linha de total da tabela com VBA no Excel você pode usar a propriedade TotalsRowRange da Table.

Guia Excel Tabelas VBA 1 - total row range

Nome da Tabela antes de iniciar no VBA

Para realizar o acesso à tabela uma forma é fazer pelo seu nome.

Para pegar o nome da tabela para utilizar no VBA basta clicar sobre a tabela em questão e na guia Design da Tabela verifique o texto que está em Nome da Tabela.

Guia Excel Tabelas VBA - Nome da tabela

Este nome será utilizado no código VBA para realizarmos acesso a tabela e também ações.

Como selecionar áreas de Tabelas com VBA no Excel

Abaixo uma lista de como acessar as diferentes partes de uma tabela utilizando VBA no Excel.

Public Sub lsSelecionaritens()
    'Selecionar a tabela inteira
    ActiveSheet.ListObjects("tFaturamento").Range.Select
    
    'Selecionar o cabeçalho da tabela
    ActiveSheet.ListObjects("tFaturamento").HeaderRowRange.Select
    
    'Selecionar todos os dados da tabela
    ActiveSheet.ListObjects("tFaturamento").DataBodyRange.Select
    
    'Selecionar a segunda coluna inteira da tabela
    ActiveSheet.ListObjects("tFaturamento").ListColumns(2).Range.Select
    
    'Selecionar somente o conteúdo da segunda coluna da tabela
    ActiveSheet.ListObjects("tFaturamento").ListColumns(2).DataBodyRange.Select
    
    'Selecionar a segunda linha inteira da tabela
    ActiveSheet.ListObjects("tFaturamento").ListRows(2).Range.Select
    
    'Selecionar o segundo cabeçalho da tabela
    ActiveSheet.ListObjects("tFaturamento").HeaderRowRange(2).Select
    
    'Selecionar a segunda linha da quarta coluna da tabela
    ActiveSheet.ListObjects("tFaturamento").DataBodyRange(2, 4).Select
    
    'Selecionar a linha de totais da tabela
    ActiveSheet.ListObjects("tFaturamento").TotalsRowRange.Select
End Sub

No procedimento acima pronto para testes basta trocar o nome da tabela tFaturamento pelo nome da tabela que tiver no seu código e já pode utilizar conforme a necessidade.

Inserir linhas e colunas em uma tabelas VBA

Como já vimos acima a seleção de dados sempre passa pelos mesmos códigos ActiveSheet.ListObjects(“tFaturamento”), substituindo claro o ActiveSheet pelo nome da planilha e o nome da tabela em tFaturamento pelo nome da sua tabela.

Para inserir colunas, linhas e linhas de total em tabelas com VBA no Excel você tem abaixo um exemplo de vários códigos:

Public Sub lsInserirItens()
    'Inserir uma nova coluna na posição 3
    ActiveSheet.ListObjects("tFaturamento").ListColumns.Add Position:=3
    
    'Inserir uma coluna ao final da tabela
    ActiveSheet.ListObjects("tFaturamento").ListColumns.Add
    
    'Inserir uma linha após a linha 5
    ActiveSheet.ListObjects("tFaturamento").ListRows.Add (3)
    
    'Inserir uma linha no final da tabela.
    ActiveSheet.ListObjects("tFaturamento").ListRows.Add AlwaysInsert:=True
    
    'Inserir uma linha de total
    ActiveSheet.ListObjects("tFaturamento").ShowTotals = True
End Sub

Deletar dados de tabelas com VBA Excel

Para deletar linhas, colunas  ou total de tabelas Excel com VBA o processo é muito parecido com o anterior para inclusão de dados.

Veja um exemplo de código para deletar os dados de tabelas com VBA de várias formas.

Public Sub lsLimparTabela()

    'Deletar a coluna 2
    ActiveSheet.ListObjects("tFaturamento").ListColumns(2).Delete

    'Deletar a linha 3
    ActiveSheet.ListObjects("tFaturamento").ListRows(3).Delete

    'Apagar as linhas 2 e 4, remover
    ActiveSheet.ListObjects("tFaturamento").Range.Rows("2:4").Delete
    
    'Remover a linha total
    ActiveSheet.ListObjects("tFaturamento").TotalsRowRange.Delete

End Sub

Como pode perceber a questão de inserir ou deletar muda pouco, praticamente a ação que será realizada para o objeto, trocando então de Add para Delete para deletar os intervalos desejados das tabelas do Excel com VBA.

Limpar todo o conteúdo de uma tabela VBA Excel

Para limpar todo o conteúdo de uma tabela com VBA no Excel você pode usar o seguinte código.

Public Sub lsLimparTodaTabela()
    'Código para limpar todo o conteúdo da tabela
    ActiveSheet.ListObjects("tFaturamento4").DataBodyRange.Delete
End Sub

Como pode perceber é passado o nome da tabela e a região que desejamos limpar e passamos o Delete.

Diferente de apagar uma parte, ele apaga todos os registros da tabela deixando apenas uma linha em branco após o cabeçalho e a linha de total se houver.

Usando a tabela como uma variável de objeto no VBA

Você também pode usar a tabela  como um objeto em uma variável de objeto no VBA.

Isso reduz o código fonte e o torna mais organizado no caso de modificações.

Para isso utilize o seguinte código por exemplo:

Public Sub lsTabelaObjeto()
    Dim lTbFaturamento As ListObject
    
    Set lTbFaturamento = ActiveSheet.ListObjects("tFaturamento")

    'Selecionar a tabela inteira
    lTbFaturamento.Range.Select
    
    'Selecionar o cabeçalho da tabela
    lTbFaturamento.HeaderRowRange.Select
    
    'Selecionar todos os dados da tabela
    lTbFaturamento.DataBodyRange.Select
    
    'Selecionar a segunda coluna inteira da tabela
    lTbFaturamento.ListColumns(2).Range.Select
    
    'Selecionar somente o conteúdo da segunda coluna da tabela
    lTbFaturamento.ListColumns(2).DataBodyRange.Select
    
    'Selecionar a segunda linha inteira da tabela
    lTbFaturamento.ListRows(2).Range.Select
    
    'Selecionar o segundo cabeçalho da tabela
    lTbFaturamento.HeaderRowRange(2).Select
    
    'Selecionar a segunda linha da quarta coluna da tabela
    lTbFaturamento.DataBodyRange(2, 4).Select
    
    'Selecionar a linha de totais da tabela
    lTbFaturamento.TotalsRowRange.Select
End Sub

Como pode perceber o código encurta bastante e fica muito mais fácil de você trabalhar.

Como realizar um loop por uma coluna de Tabela com VBA Excel

Para realizar o loop por uma coluna de uma tabela com VBA há várias formas.

Este é um exemplo prático de como você pode realizar isto com os conhecimentos que adquiriu ao longo deste artigo.

Public Sub lsLoopColuna()
    Dim lTbFaturamento As ListObject
    
    Set lTbFaturamento = ActiveSheet.ListObjects("tFaturamento")

    'Loop por todas as linhas da coluna 2 da tabela com VBA
    For i = 1 To lTbFaturamento.ListColumns(2).DataBodyRange.Rows.Count
        lTbFaturamento.DataBodyRange(i, 2).Select
    Next i
End Sub

Como pode perceber o loop é realizado por todas as linhas somente dos dados da tabela, excetuando então os dados do cabeçalho e também do total da tabela.

No loop são passados os dados das posições de linha e coluna respectivamente no DataBodyRange e selecionado o seu conteúdo, mas poderia ser alterado ou realizado qualquer outra atualização ou trabalho com estes dados.

Download da planilha de exemplo

Pode realizar o download da planilha pronta com os exemplos deste artigo á partir do link abaixo.

Basta preencher com seu nome e e-mail para baixar gratuitamente e fazer parte também da nossa newsletter gratuita. Iremos lhe enviar novidades do site periodicamente e promoções.

Baixe a planilha