Tabelas do Excel com VBA

Visão Geral das Tabelas do Excel

O que é uma Tabela?

Uma Tabela é simplesmente um intervalo estruturado no qual você pode se referir a diferentes seções que são automaticamente mapeadas (como a linha de cabeçalho ou a coluna abaixo do cabeçalho "Valor"). Elas são tratadas como objetos no Excel e podem ser manipuladas via codigo através de um objeto do tipo ListObject. Dessa forma elas se tornam um recurso que simplifica a forma de ler e se referenciar a conteúdo de um intervalo, pois não apenas estruturam seus dados, mas também se expandem ou contraem à medida que estes dados crescem ou diminuem. 

Por ser um tratado como um objeto, existem facilidades das quais podemos usufruir aplicando programação em VBA.  Se você não sabe o que é VBA, leia um overview nesse artigo.

Manualmente no Excel, você pode criar rapidamente uma Tabela destacando um intervalo (com cabeçalhos apropriados) e usando o atalho de teclado Ctrl + t. Outra opção é navegar até a guia Inserir e selecionar o botão Tabela dentro do grupo Tabelas.

As Partes de uma Tabela

3 -Gerenciador de Nomes do Excel

Outra ótima maneira de encontrar uma tabela e o seu nome é acessando o Gerenciador de Nomes. Você pode acessar o gerenciador de nomes indo até a guia Fórmulas e clicando no botão Gerenciador de Nomes dentro do grupo Nomes Definidos.

Ao utilizar o menu de filtro no canto direito do Gerenciador de Nomes, você pode reduzir a lista de nomes para mostrar apenas as tabelas dentro da Pasta de Trabalho. O Gerenciador de Nomes mostrará exatamente onde as tabelas estão na planilha e também quais seus respectivos nomes.

4 - Código VBA 

Há situações em que você precisa descobrir se uma determinada célula está dentro de uma ListObject (Tabela). O código VBA a seguir, mostra como testar se a célula selecionada está dentro de uma Tabela do Excel. Se for o caso, mostra o seu nome.

Nota: No final do artigo há uma outra versão deste código

Selecionar áreas em tabelas

As nove macros a seguir são pequenos exemplos de como selecionar áreas específicas de uma tabela:

Selecionar uma Tabela inteira

Sub SelecionaTabelaInteira()

    'Seleciona uma tabela Inteira, incluindo cabeçalho e rodapé

    ActiveSheet.ListObjects("Tabela1").Range.Select

End Sub

Selecionar a Linha de cabeçalho

Sub SelecionaLinhaCabecalho()

    'Linha de Cabeçalho da Tabela

    ActiveSheet.ListObjects("Tabela1").HeaderRowRange.Select

End Sub

Selecionar uma a Área de dados (range de dados)

Sub SelecionaRangeDados()

    'Dados toda a área de dados da Tabela

    ActiveSheet.ListObjects("Tabela1").DataBodyRange.Select

End Sub

Selecionar uma coluna

Sub SelecionaColuna()

    'Seleciona uma coluna. Nesse exemplo a 3ª Coluna

    ActiveSheet.ListObjects("Tabela1").ListColumns(3).Range.Select

End Sub

Selecionar somente dados da coluna

Sub SelecionaDadosColuna()

    'Terceira Coluna (Apenas Dados)

    ActiveSheet.ListObjects("Tabela1").ListColumns(3).DataBodyRange.Select

End Sub

Selecionar uma linha

Sub SelecionaLinha()

    'Selecionar uma Linha. No exemplo, a 4ª linha de dados

    ActiveSheet.ListObjects("Tabela1").ListRows(4).Range.Select

End Sub

Selecionar a linha do cabeçalho

Sub SelecionarCabecalho()

    'Seleciona o cabeçalho. Nesse caso a 3º coluna

    ActiveSheet.ListObjects("Tabela1").HeaderRowRange(3).Select

End Sub

Selecionar uma uma única Célula

Sub SelecionarCelula()

    'Selecionar uma célula. Nesse exemplo a Linha 3, Coluna 2

    ActiveSheet.ListObjects("Tabela1").DataBodyRange(3, 2).Select

End Sub

Selecionar uma a linha do rodapé (Linha de totais)

Sub SelecionaRodape()

    'Seleciona a linha de rodapé, que contém os Totais

    ActiveSheet.ListObjects("Tabela1").TotalsRowRange.Select

End Sub

Inserir linhas e colunas em tabelas

As cinco macros a seguir mostram como inserir ou exibir itens de tabelas.

Iserir uma coluna

Sub InserirColuna()

    ' Insere uma nova coluna entre a coluna 2 e 3

    ActiveSheet.ListObjects("Tabela1").ListColumns.Add Position:=4

End Sub

Adicionar coluna à direita da tabela

Sub AdicionarColuna()

    ' Adiciona uma nova coluna no final da tabela, lado direito

    ActiveSheet.ListObjects("Tabela1").ListColumns.Add

End Sub

Inserir uma linha

Sub InserirLinha()

    ' Insere uma nova linha entre as linha 2 e 3

    ActiveSheet.ListObjects("Tabela1").ListRows.Add (3)

End Sub

Adicionar uma linha ao final da tabela

Sub AdicionarLinha()

    ' Adicionar uma linha ao final da tabela, antes da linha totais

    ActiveSheet.ListObjects("Tabela1").ListRows.Add AlwaysInsert:=True

End Sub

Exibir a linha de totais

Sub ShowTotais()

    ' Exibe a linha de totais

    ActiveSheet.ListObjects("Tabela1").ShowTotals = False

End Sub

Deletar linhas e colunas em tabelas

As cinco macros a seguir mostram como deletar ou ocultar itens de tabelas.

Remove uma coluna

Sub RemoverColuna()

    ' Remove a coluna na posição 3 da tabela

    ActiveSheet.ListObjects("Tabela1").ListColumns(3).Delete

End Sub

Remove a última coluna

Sub RemoverUltimaColuna()

    ' Remove a última coluna da tabela

    ActiveSheet.ListObjects("Tabela1").ListColumns(ActiveSheet.ListObjects("Tabela1").ListColumns.Count).Delete

End Sub

Remove uma linha

Sub RemoverLinha()

    ' Remove a linha na posição 5 da tabela

    ActiveSheet.ListObjects("Tabela1").ListRows(3).Delete

End Sub

Remove a última linha

Sub RemoverUltimaLinha()

    ' Remove a última linha da tabela

    ActiveSheet.ListObjects("Tabela1").ListRows(ActiveSheet.ListObjects("Tabela1").ListRows.Count).Delete

End Sub

Oculta a linha de totais

Sub OcultarTotais()

    ' Oculta a linha de totais

    ActiveSheet.ListObjects("Tabela1").ShowTotals = False

End Sub

Abordagem mais simples

Evoluindo um pouco mais na exibição dos códigos, apresento várias ações dentro de um único bloco de código, usando uma abordagem simplificada, que torna o código menor e mais legível.

Sub DeletaPartesTabela()

    Dim tbl As ListObject

    'Atenção: substituir "Tabela1" pelo nome correto da tabela

    Set tbl = ActiveSheet.ListObjects("Tabela1")

    'Remove 3ª coluna

    tbl.ListColumns(3).Delete

    'Remove 4a Linha do corpo (DataBody)

    '(considere o primeiro registro como primeira linha'

    tbl.ListRows(4).Delete

    'Remove da 3ª à 5ª linhas da tabela

    '(considere o cabeçalho como primeira linha)

    tbl.Range.Rows("3:5").Delete

    'Remove a linha de totais

    tbl.TotalsRowRange.Delete

End Sub

Deletar e limpar dados em uma tabela

O código à seguir exclui todas as linhas de dados de uma tabela, exceto a primeira, que é apenas apagada.

Sub LimpaTabela()

    Dim tbl As ListObject, ttLinhas As Long, ttColunas As Long

    Set tbl = ActiveSheet.ListObjects("Tabela1")

    ttLinhas = tbl.DataBodyRange.Rows.Count - 1 'variavel para deixar o código mais legível

    ttColunas = tbl.DataBodyRange.Columns.Count 'variavel para deixar o código mais legível

    'Deleta todas as linhas da tabela, exceto a 1º

    If tbl.DataBodyRange.Rows.Count > 1 Then

        tbl.DataBodyRange.Offset(1, 0).Resize(ttLinhas, ttColunas).Rows.Delete

    End If

    'Limpa os dados da primeira linha da tabela
    tbl.DataBodyRange.Rows(1).ClearContents

End Sub

Caso sua tabela possua fórmulas e você deseja mantê-las intactas, basta alterar a última linha o código anterior pela linha a seguir. Isso removerá apenas os valores não calculados existentes na linha que restou no Objeto Tabela:

 'Limpa os dados da primeira linha da tabela (retendo fórmulas)
  tbl.DataBodyRange.Rows(1).SpecialCells(xlCellTypeConstants).ClearContents

Loop - Como percorrer cada coluna ou linha da tabela

Avançando um pouco mais, mostro à seguir um código que executa repetições (loops). Neste exemplo utilizo laço de loop For... Next.

Como no exemplo anterior, incluí várias situações dentro de um mesmo código.

Sub PercorreTodaTabela()

    Dim tbl As ListObject

    Dim i As Long 'incremento de linha ou coluna em cada loop.

    Set tbl = ActiveSheet.ListObjects("Tabela1")

    'Percorre cada coluna da tabela

      For i = 1 To tbl.ListColumns.Count

        tbl.ListColumns(i).Range.ColumnWidth = 8

      Next i

    'Percorre todas as linhas da tabela

      For i = 1 To tbl.Range.Rows.Count

        tbl.Range.Rows(i).RowHeight = 20

      Next i

    'Percorre cada linha do corpo (DataBody) na tabela

      For i = 1 To tbl.ListRows.Count

        tbl.ListRows(i).Range.RowHeight = 15

      Next i

End Sub

Ordenar uma tabela por coluna

É possível ordenar os dados da tabela em ordem crescente ou decrescente. O código a seguir mostrará como classificar uma coluna em sua objeto tabela (ListObject).

Dica: Nesse caso é mais fácil usar o gravador de macros do Excel, como dito nos comentários do código.

Sub OrganizaTabByCol()

    'Propósito: Organizar uma tabela por colunas

    '           em ordem crescente ou decrescente.

    'Este exemplo é ilustrativo. É mais fácil gravar uma macro

    Dim tbl As ListObject

    Dim ordem As Integer, coluna As Integer

    'Define a ordem de classificação e a coluna

      ordem = xlAscending '(-> crescente xlDescending -> decrescente)

      coluna = 1

    'Armazene a tabela Excel desejada em uma variável

      Set tbl = ActiveSheet.ListObjects("Tabela1")

    'Limpa ordenações anteriores

      tbl.Sort.SortFields.Clear

    'Aplica a ordenação crescente na primeira coluna da tabela

    tbl.Sort.SortFields.Add2 _

        Key:=tbl.ListColumns(coluna).Range, _

        SortOn:=xlSortOnValues, _

        Order:=ordem, _

        DataOption:=xlSortNormal

    'Opções de ordenação (se quiser ordenar diferente do padrão)

    tbl.Sort.Header = xlYes

    tbl.Sort.MatchCase = False

    tbl.Sort.Orientation = xlTopToBottom

    tbl.Sort.SortMethod = xlPinYin

    'Aplicar a ordenação na tabela

      tbl.Sort.Apply

End Sub

Embora o código acima mostre todas diversas opções que você pode ajustar, na maioria das vezes você não precisará delas. Abaixo está o mesmo código simplificado sem mostrar valores que geralmente não são alterados.

Sub OrganizaTabByCol_Simples()

    'Propósito: Organizar uma tabela por colunas

    '           em ordem crescente ou decrescente.

    Dim tbl As ListObject

    Dim ordem As Integer, coluna As Integer

    'Define a ordem de classificação e a coluna

    ordem = xlDescending  '(ou xlAscending)

    coluna = 1

    'Armazene a tabela Excel desejada em uma variável

    Set tbl = ActiveSheet.ListObjects("Tabela1")

    'Limpa ordenações anteriores

    tbl.Sort.SortFields.Clear

    'Configura a ordenação crescente na coluna

    'de acordo cm a variável "coluna" na tabela

      tbl.Sort.SortFields.Add2 _

          Key:=tbl.ListColumns(coluna).Range, _

          Order:=ordem

    'Aplica a ordenação na tabela

    tbl.Sort.Apply

End Sub

Pesquisar valores em uma tabela

É possível procurar valores em uma tabela. Existem muitos cenários de pesquisa diferentes. Segue um exemplo didático, para encontrar uma string* de Código na primeira coluna de uma tabela específica e retorna o número da linha na tabela correspondente desse código. 

Espero que este exemplo  sirva para inspirar seu próprio código.

*String é o mesmo que texto. Neste caso, um número foi formatado como texto

Sub PesquisarTabela()

    Dim tbl As ListObject

    Dim celula As Range

    Dim pesquisarPor As String

    Dim linha As Integer

    linha = 0 'inicia a linha como vazia

    'Valor a ser pesquisado

    pesquisarPor = "780"

    'Guarda o objeto tabela em uma variável

    Set tbl = ActiveSheet.ListObjects("Tabela1")

    'Tenta pesquisar na primeira coluna

    On Error Resume Next

    Set celula = tbl.DataBodyRange.Columns(1).Find(pesquisarPor, LookAt:=xlWhole)

    On Error GoTo 0

    'Retorna a linha na tabela se localizado...

    If Not celula Is Nothing Then

        linha = tbl.ListRows(celula.Row - tbl.HeaderRowRange.Row).Index

        'seleciona a linha encontrada.

        tbl.ListRows(linha).Range.Select

        'feedback ao usuário

        MsgBox "Encontrado na tabela na linha: " & linha

    Else

        'feedback ao usuário

        MsgBox "O cógigo '" & pesquisarPor & "' não foi encontrado"

    End If

End Sub

Descobrir se uma tabela está selecionada e qual seu nome

Até quando esse artigo foi escrito, o Excel não disponibiliza uma  proprietade ou método que permita facilmente ver qual é a tabela ativa. Não temos uma forma direta para manipular uma tabela selecionada pelo usuário. Felizmente é possível contornar isso com o exemplo à seguir:

Sub GetTabelaAtiva()

    Dim celulaSelecionada As Range

    Dim nomeTabela As String

    Dim tabelaAtiva As ListObject

    Set celulaSelecionada = ActiveCell

    'Descobre se a célula selecionada está dentro de uma tabela

      On Error GoTo NenhumaTabelaEncontrada

        nomeTabela = celulaSelecionada.ListObject.Name

        Set tabelaAtiva = ActiveSheet.ListObjects(nomeTabela)

        MsgBox "A célula selecionada está em uma tabela chamada '" & nomeTabela & "'"

      On Error GoTo 0

    'Ação de exemplo ma variável da sua tabela (adicionar uma linha ao final)

      tabelaAtiva.ListRows.Add AlwaysInsert:=True

    Exit Sub

    'Tratamento de erro

NenhumaTabelaEncontrada:

    MsgBox "Nenhuma tabela selecionada no momento!", vbCritical

End Sub

Faça um download do arquivo

Este arquivo está no OneDrive. Para baixar, siga estes 3 passos:

1 -Clique no Link para o arquivo

2 - No menu superior à esquerda, clique em "Arquivo"

3 - Clique em 'Salvar Como' -> Baixar uma Cópia

Conclusão

Nesse artigo mostrei diversas formas para manipular tabelas usando VBA. Procurei ser o mais direto e prático possível. Agradeço por você ter lido e aguardo seu feedbabk atraves do meu e-mail: eduardo@sobrinho.com, ou diretamente neste post do linkedin onde fiz: 

Grande abraço e até o próximo.

Eduardo Sobrinho.