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 é:
