VBA – Tabelas e ListObjects

Este tutorial demonstrará como trabalhar com Tables e ListObjects no VBA.

Tabelas e ListObjects do VBA

As tabelas são um dos recursos mais úteis e poderosos do Excel. Neste tutorial, veremos como usar o VBA para criar uma tabela, adicionar uma classificação simples a uma tabela, filtrar uma tabela e executar outras tarefas relacionadas a tabelas.

Criar uma Tabela com o VBA

O método ListObjects.Add pode adicionar uma tabela a uma planilha, com base em um intervalo dessa planilha. Temos o intervalo mostrado em ($A$1:$B$8) em uma planilha chamada Planilha1.

O código a seguir adicionará uma tabela chamada Tabela1 à sua planilha, com base no intervalo ($A$1:$B$8), usando o Table Style padrão:

Sub CriarTabelaEmExcel()

ActiveWorkbook.Sheets("Planilha1").ListObjects.Add(xlSrcRange, Range("$A$1:$B$8"), , xlYes).Name = _
"Tabela1"

End Sub

O resultado é:

Inserção de uma Coluna no Final da Tabela com VBA

Você pode usar o método ListColumns.Add para adicionar uma coluna ao final da tabela. Temos uma tabela chamada Tabela1 mostrada abaixo.

Você pode adicionar uma coluna à sua tabela usando o código a seguir, que sempre adicionará uma coluna ao final da tabela:

Sub AdicionarColunaAoFinalDaTabela()

ActiveWorkbook.Sheets("Planilha1").ListObjects("Tabela1").ListColumns.Add

End Sub

O resultado é:

Inserção de uma Linha na Parte Inferior da Tabela com o VBA

Você pode usar o método ListRows.Add para adicionar uma linha à parte inferior da tabela. Temos uma tabela chamada Tabela1 mostrada abaixo.

O código a seguir sempre adicionará uma linha à parte inferior da tabela.

Sub AdicionarLinhaParteInferiorTabela()

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

End Sub

O resultado é:

Adição de uma Classificação Simples com o VBA

Você pode classificar uma tabela com o VBA. Temos uma tabela chamada Tabela1 mostrada abaixo e podemos usar o VBA para classificar a coluna Vendas da menor para a maior.

O código a seguir classificará a coluna Sales em ordem crescente.

Sub ClassificacaoSimpleDaTabela()

Range("Tabela1[[#Headers],[Vendas]]").Select
ActiveWorkbook.Worksheets("Planilha1").ListObjects("Tabela1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Planilha1").ListObjects("Tabela1").Sort.SortFields.Add _
Key:=Range("Tabela1[[#All],[Vendas]]"), SortOn:=xlSortOnValues, Order:= _
xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Planilha1").ListObjects("Tabela1").Sort

.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply

End With

End Sub

O resultado é:

Filtrar uma Tabela com VBA

Você também pode filtrar uma tabela do Excel usando o VBA. Temos uma tabela chamada Tabela1 e gostaríamos de filtrá-la para que apenas as vendas superiores a 1.500 sejam exibidas.

Podemos usar o método Autofilter, que tem cinco parâmetros opcionais. Como gostaríamos de filtrar a coluna Vendas, que é a segunda coluna, definimos o campo como 2 e usamos o parâmetro do operador xlAnd, que é usado para datas e números.

Sub FiltroSimples()

 ActiveWorkbook.Sheets("Planilha1").ListObjects("Tabela1").Range.AutoFilter Field:=2, Criteria1:= _
 ">1500", Operator:=xlAnd

End Sub

O resultado é:

Limpar o Filtro com o Método ShowAllData no VBA

Você pode acessar o método ShowAllData da classe Worksheet para limpar o filtro. Se quiser limpar o(s) filtro(s) de uma tabela, primeiro será necessário selecionar uma célula na tabela, o que pode ser feito no VBA.

O método ShowAllData gerará um erro se não for usada a lógica condicional para verificar se foi aplicado um filtro na planilha. O código a seguir mostra como fazer isso:

Sub LimpandoFiltro()

Range("Tabela1[[#Headers],[Vendas]]").Select
If ActiveWorkbook.Worksheets("Planilha1").FilterMode = True Then
ActiveSheet.ShowAllData

End If

End Sub

Limpar Todos os Filtros de uma Tabela do Excel

Você pode acessar o método ShowAllData da classe ListObject sem precisar selecionar uma célula na tabela primeiro. O código a seguir mostra como fazer isso:

Sub LimpandoTodosFiltros()
ActiveWorkbook.Worksheets("Planilha1").ListObjects("Tabela1").AutoFilter.ShowAllData
End Sub

Exclusão de uma Linha com o VBA

Você pode excluir uma linha do conjunto de dados da tabela usando o método ListRows.Delete. É necessário especificar a linha usando o número da linha. Temos a seguinte tabela chamada Tabela1.

Digamos que você queira excluir a segunda linha da tabela, o código a seguir permitirá que você faça isso:

Sub ApagarUmaLinha()

ActiveWorkbook.Worksheets("Planilha1").ListObjects("Tabela1").ListRows(2).Delete

End Sub

O resultado é:

Exclusão de uma Coluna com o VBA

Você pode excluir uma coluna de sua tabela usando o método ListColumns.Delete. Temos a seguinte tabela, chamada Tabela1, mostrada abaixo:

Para excluir a primeira coluna, você usaria o seguinte código:

Sub ExcluirUmaColuna()

ActiveWorkbook.Worksheets("Planilha1").ListObjects("Tabela1").ListColumns(1).Delete

End Sub

O resultado é:

Conversão de uma Tabela de Volta em um Intervalo no VBA

Você pode converter uma tabela de volta em um intervalo normal usando o VBA. O código a seguir mostra como converter uma tabela chamada Tabela1 de volta em um intervalo:

Sub ConverterTabelaParaIntervalo()

ActiveWorkbook.Sheets("Planilha1").ListObjects("Tabela1").Unlist

End Sub

Adição de Colunas Zebradas e Formatação a Todas as Tabelas em uma Planilha usando o VBA

Você pode acessar todas as tabelas de sua planilha usando a coleção ListObjects. Na planilha abaixo, temos duas tabelas e gostaríamos de adicionar uma coluna zebrada a ambas as tabelas de uma só vez e alterar a fonte da seção de dados de ambas as tabelas para negrito, usando o VBA.

Sub AdicionandoColunaZebrada()

Dim tbl As ListObject
Dim sht As Worksheet

Set sht = ThisWorkbook.ActiveSheet

For Each tbl In sht.ListObjects
tbl.ShowTableStyleColumnStripes = True
tbl.DataBodyRange.Font.Bold = True
Next tbl

End Sub

O resultado é:

Criação de uma Tabela no Access em VBA usando DoCmd.RunSQL

Uma das principais maneiras de criar uma tabela no Access em VBA é usar o método DoCmd.RunSQL para executar uma consulta de ação com uma instrução SQL.

Temos um botão em nosso formulário de exemplo e, quando clicamos no botão, gostaríamos de criar uma tabela chamada TabelaDeProdutos com dois campos ou colunas, um seria o campo de chave primária chamado IDProduto e o outro seria um campo chamado Vendas.

Para criar essa tabela, usaríamos o seguinte código:

Private Sub cmdCriarTabelaProdutos_Click()

DoCmd.RunSQL "CREATE TABLE TabelaDeProdutos " _
& "(IDProduto INTEGER PRIMARY KEY, Vendas Integer);"

End Sub

O resultado é:

Filtragem de uma Tabela no Access Usando VBA

Você também pode filtrar uma tabela no Access usando o método DoCmd.ApplyFilter. Temos nossa tabela simples mostrada abaixo no Access, chamada TabelaDeProdutos.

Gostaríamos de pressionar este botão em nosso formulário e ver apenas as vendas maiores que 1500.

Para isso, usaríamos o seguinte código para fazer isso:

Private Sub cmdFiltro_Click()

DoCmd.OpenTable "TabelaDeProdutos"

DoCmd.ApplyFilter , "[Vendas]>1500"

End Sub

O resultado é: