Tabelas VBA e ListObjects

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

Tabelas VBA e ListObjects

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 realizar outras tarefas relacionadas à tabela.

Crie uma tabela com 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 Plan1.

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

123456 Sub CreateTableInExcel ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects.Add (xlSrcRange, Range ("$ A $ 1: $ B $ 8"),, xlYes) .Name = _"Tabela 1"End Sub

O resultado é:

Inserindo uma coluna no final da tabela com VBA

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

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

12345 Sub AddColumnToTheEndOfTheTable ()ActiveWorkbook.Sheets ("Folha1"). ListObjects ("Tabela1"). ListColumns.AddEnd Sub

O resultado é:

Inserindo uma linha na parte inferior da mesa com VBA

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

O código a seguir sempre adicionará uma linha ao final da sua tabela.

12345 Sub AddRowToTheBottomOfTheTable ()ActiveSheet.ListObjects ("Tabela1"). ListRows.AddEnd Sub

O resultado é:

Adicionando uma classificação simples com VBA

Você pode classificar uma tabela com VBA. Temos nossa tabela chamada Tabela1 mostrada abaixo e podemos usar o VBA para classificar a coluna de vendas da mais baixa para a mais alta.

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

12345678910111213141516171819 Sub SimpleSortOnTheTable ()Intervalo ("Tabela1 [[# Cabeçalhos], [Vendas]]"). SelecioneActiveWorkbook.Worksheets ("Folha1"). ListObjects ("Tabela1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Folha1"). ListObjects ("Tabela1"). Sort.SortFields.Add _Chave: = Range ("Table1 [[# All], [Sales]]"), SortOn: = xlSortOnValues, Order: = _xlAscending, DataOption: = xlSortNormalCom ActiveWorkbook.Worksheets ("Folha1"). ListObjects ("Tabela1"). Classificar.Header = xlSim.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplicarTerminar comEnd Sub

O resultado é:

Filtrar uma tabela com VBA

Você também pode filtrar uma tabela do Excel usando VBA. Temos nossa tabela chamada Tabela1 e gostaríamos de filtrar a tabela de forma que apenas vendas superiores a 1.500 sejam mostradas.

Podemos usar o método Autofilter, que possui 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.

123456 Sub SimpleFilter ()ActiveWorkbook.Sheets ("Planilha1"). ListObjects ("Tabela1"). Campo Range.AutoFilter: = 2, Criteria1: = _"> 1500", Operador: = xlAndEnd Sub

O resultado é:

Limpe o filtro com o método ShowAllData em VBA

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

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

123456789 Sub ClearingTheFilter ()Intervalo ("Tabela1 [[# Cabeçalhos], [Vendas]]"). SelecioneIf ActiveWorkbook.Worksheets ("Sheet1"). FilterMode = True ThenActiveSheet.ShowAllDataFim seEnd Sub

Limpar todos os filtros de uma tabela do Excel

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

123 Sub ClearAllTableFilters ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). AutoFilter.ShowAllDataEnd Sub

Excluindo uma linha com VBA

Você pode excluir uma linha no corpo de dados de sua tabela usando o método ListRows.Delete. Você deve especificar qual linha usando o número da linha. Temos a seguinte tabela chamada Tabela1.

Digamos que você queira excluir a segunda linha no corpo de dados de sua tabela, o código a seguir permitiria que você faça isso:

12345 Sub DeleteARow ()ActiveWorkbook.Worksheets ("Sheet1"). ListObjects ("Table1"). ListRows (2) .DeleteEnd Sub

O resultado é:

Excluindo uma coluna com 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:

12345 Sub DeleteAColumn ()ActiveWorkbook.Worksheets ("Folha1"). ListObjects ("Tabela1"). ListColumns (1) .DeleteEnd Sub

O resultado é:

Convertendo uma tabela de volta a um intervalo em VBA

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

12345 Sub ConvertingATableBackToANormalRange ()ActiveWorkbook.Sheets ("Sheet1"). ListObjects ("Table1"). UnlistEnd Sub

Adicionando colunas em faixas e formatação a todas as tabelas em uma planilha usando VBA

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

12345678910111213 Sub AddedBandedColumns ()Dim tbl As ListObjectDim sht como planilhaDefina sht = ThisWorkbook.ActiveSheetPara cada tbl em sht.ListObjectstbl.ShowTableStyleColumnStripes = Truetbl.DataBodyRange.Font.Bold = TruePróximo tblEnd Sub

O resultado é:

Criando uma tabela no Access em VBA usando DoCmd.RunSQL

Uma das principais maneiras de criar uma tabela no Access em VBA é usando 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 amostra e quando clicamos no botão, gostaríamos de criar uma tabela chamada ProductsTable com dois campos ou colunas, um seria o campo de chave primária chamado ProductsID e o outro seria um campo chamado Sales.

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

123456 Private Sub cmdCreateProductsTable_Click ()DoCmd.RunSQL "CREATE TABLE ProductsTable" _& "(ProductID INTEGER PRIMARY KEY, Sales Integer);"End Sub

O resultado é:

Filtrando uma tabela no acesso 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 ProductsTable.

Gostaríamos de pressionar este botão em nosso formulário e ver apenas as vendas superiores a 1.500.

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

1234567 Sub privado cmdFilter_Click ()DoCmd.OpenTable "ProductsTable"DoCmd.ApplyFilter, "[Vendas]> 1500"End Sub

O resultado é:

Você vai ajudar o desenvolvimento do site, compartilhando a página com seus amigos

wave wave wave wave wave