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:
- Autoexpansão dos dados
- Formatação padronizada
- Cópia de fórmulas automática
- 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.
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.
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.
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.
A parte selecionada abaixo é uma linha da tabela.
Para referenciar uma linha de uma tabela Excel com VBA você pode utilizar a propriedade ListRows.
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.
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.
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
