Usando tabela no VBA
Intervalos nomeados são uma grande ajuda no Excel, pois
permitem acesso às suas células mesmo que elas sejam movidas para outra posição
da planilha. Se o intervalo for dinâmico melhor ainda, pois permite adição e
exclusão de linhas e/ou colunas, mantendo a referência corretamente ao
intervalo de células com dados.
O recurso de tabela do Excel contém diversos recursos
adicionais em relação aos intervalos nomeados dinâmicos, possibilitando
diferentes abordagens ao lidar com os dados. Por exemplo, as formatações e as fórmulas
são copiadas automaticamente para novas linhas. Esse recurso foi adicionado no
Excel 2003 e melhorado nas versões posteriores, entretanto algumas propriedades
sofreram mudanças. O conteúdo aqui deve funcionar bem no Excel 2016 (que eu
uso) e no 2013, mas nas versões anteriores talvez não funcione e precise de
adaptações.
Provavelmente você já deve ter usado o recurso de tabela
no Excel. Se ainda não fez, selecione um intervalo, vá para o menu Inserir e clique no botão Tabela. Uma janela abrirá perguntando
basicamente duas coisas: o intervalo dos dados e se há cabeçalho ou não. Se
você selecionou a tabela inteira não precisa editar o intervalo. Se seu intervalo
tem cabeçalho, deixe a opção marcada. Após clicar em Ok, você perceberá que o
menu ativo passa a ser Design e que
traz algumas opções para edição da tabela. O mais importante no momento é o
nome, pois o nome padrão Tabela1
certamente não é o melhor para representar seus dados. Mais à direita, há
opções de estilo, como linha de totais, que adiciona uma linha de totais
automaticamente à sua tabela.
Para criar uma tabela no VBA, é preciso usar o ListObjects, que, como o próprio nome
indica, é uma coleção. Há outros
objetos do Excel que podem ser criados com ele, mas vamos focar em tabelas (os
outros ficam para artigos futuros). O ListObjects
exige que uma planilha-mãe seja especificada, ou seja, não dá para usá-lo sem
especificar nenhuma planilha. A forma mais simples é usar ActiveSheet.ListObjects, mas você pode usar o nome de código de sua
planilha. O método para criar uma tabela é Add
e ele possui os seguintes parâmetros:
- SourceType: Tipo
de fonte de dados, usa um valor da enumeração XlListObjectSourceType. Usaremos o valor xlSrcRange, que indica um intervalo de células;
- Source: Fonte
dos dados. Quando usamos xlSrcRange devemos
especificar o intervalo que será transformado em tabela;
- LinkSource: Vínculo
da fonte de dados. É utilizado quando usamos fontes de dados externa, o que não
irei abordar neste artigo. Podemos omitir este parâmetro;
- XlListObjectHasHeaders:
Se há cabeçalho no objeto. Usa a enumeração XlYesNoGuess,
onde xlYes significa sim, xlNo é não e xlGuess é adivinhar. Como nossas tabelas têm cabeçalhos, deixamos xlYes;
- Destination:
Destino dos dados, deve ser usado quando a fonte de dados é externa. Podemos
omitir este parâmetro;
- TableStyleName:
Nome do estilo de tabela. O Excel traz
alguns estilos de tabela de tonalidades clara, média e escura. Os nomes são
"TableStyleLight#" (sendo #
de 1 a 21), "TableStyleMedium#"
(# de 1 a 28) e "TableStyleDark#"
(# de 1 a 11). Se você tiver seus próprios estilos, pode usar o nome aqui.
Vejamos um exemplo de intervalo para transformar em tabela,
o código e o resultado:
ActiveSheet.ListObjects.Add(xlSrcRange,
Range("A1:B10"), , _
xlYes, , "TableStyleMedium5").Name = "TbFrutas"
Com o objeto criado, podemos referenciá-lo da seguinte
forma: ActiveSheet.ListObjects("TbFrutas").
Entretanto, não é prático ter de digitar tudo isso toda vez que for preciso
trabalhar com alguma propriedade ou método da tabela. É melhor criar uma
variável do tipo objeto e associá-la à tabela:
Dim TbFrutas As Object
Set TbFrutas = ActiveSheet.ListObjects("TbFrutas")
Desta forma fica muito mais prático lidar com a tabela.
Se quiser, por exemplo, alterar o estilo da tabela podemos escrever da seguinte
forma:
TbFrutas.TableStyle = "TableStyleMedium2"
ListObjects
possui diversos métodos e propriedades. Primeiramente vamos ver alguns
objetos-propriedades de referência a setores da tabela:
- Range: Se
refere a toda a tabela, incluindo a linha de cabeçalho e a de totais, se
houver;
- HeaderRowRange:
Se refere à linha de cabeçalho da tabela;
- DataBodyRange:
Se refere ao corpo da tabela, ou seja, excluindo a linha de cabeçalho e a de
totais;
- TotalsRowRange:
Se refere à linha de totais da tabela;
- ListColumns:
Se refere a uma coluna da tabela;
- ListRows: Se
refere a uma linha da tabela.
Essas propriedades são provavelmente as que você mais
usará quando estiver trabalhando com tabelas. Cada uma delas tem propriedades e
métodos próprios, como veremos a seguir.
ListColumns e ListRows podem ser usados para inserir e
remover colunas e linhas, respectivamente. É importante notar que qualquer
linha que for adicionada ou excluída será no intervalo DataBodyRange. Não faz sentido acrescentar e excluir a linha de
cabeçalho ou a de totais, elas são exibidas ou ocultas de outra maneira.
ListColumn.Add
adiciona uma coluna à direita da tabela. Se usar algum número como parâmetro,
irá adicionar uma coluna naquela posição. Por exemplo, ListColumns.Add(2) (ou ListColumns.Add
Position:=2) irá adicionar uma coluna na segunda posição e empurrar as
outras para a direita. Para excluir essa coluna adicionada, o comando é ListColumns(2).Delete. Atente para o
fato que a posição do índice em adicionar e remover são diferentes.
Da mesma forma, ListRows.Add
adiciona uma linha no final da tabela. Se houver uma linha de totais, ela será
empurrada para baixo. Se for colocada uma posição específica, a linha
adicionada será acrescentada naquela posição e as outras serão empurradas para
baixo. Por exemplo, ListRows.Add(2)
irá adicionar uma linha na segunda posição em DataBodyRange, empurrando a existe e as anteriores para baixo. ListRows(3).Delete irá apagar a terceira
linha em DataBodyRange, deslocando todas
as posteriores para cima. O Excel irá acusar um erro se tentar excluir uma linha ou coluna que não existe.
A propriedade que serve para exibir ou ocultar a linha
de totais é ShowTotals, que usa valores booleanos (verdadeiro ou falso). É
fácil deduzir que usar ShowTotals = True
exibe a linha de totais e ShowTotals =
False oculta. Quaisquer conteúdos em células abaixo da tabela são empurrados
para baixo ao exibir a linha de totais ou puxados para cima quando na
ocultação. Para a linha de cabeçalho, a propriedade é ShowHeaders, cujo funcionamento é idêntico, exceto que as linhas
não se deslocam quando oculta ou exibe o cabeçalho. Na ocultação a linha é
substituída por outra em branco.
É possível combinar ListColumns
e ListRows com as outras
propriedades. Por exemplo, ListColumn(2).DataBodyRange
se refere à segunda coluna da área de dados (sem cabeçalho e totais). Também é
possível usar o nome da coluna em ListColumns,
como ListColumns("Preço").DataBodyRange
para se referir ao mesmo intervalo anterior.
Vamos acrescentar uma coluna nova Quantidade na tabela TbFrutas
do exemplo acima. Você pode escrever o código desta maneira:
TbFrutas.ListColumns.Add
A coluna foi criada, mas o nome não está lá. É preciso
adicioná-lo de alguma forma. Sabemos que foi adicionada na terceira posição e
poderíamos colocar o nome da seguinte forma:
TbFrutas.ListColumns(3).Name = "Quantidade"
Porém, nem sempre conheceremos a posição da coluna que
foi recém-adicionada ao final da tabela. É possível conhecer a quantidade desta
forma:
TbFrutas.ListColumns.Count
Podemos então combinar as duas informações em uma só
linha:
TbFrutas.ListColumns(TbFrutas.ListColumns.Count).Name =
"Quantidade"
Seria muito melhor adicionar uma coluna com seu nome ao
mesmo tempo, não? Felizmente é possível combinar o método Add com a propriedade Name.
Vamos criar outra coluna para demonstrar isso:
TbFrutas.ListColumns.Add.Name = "Preço Total"
A coluna foi criada com seu nome na linha de cabeçalho.
Esta forma é muito mais prática. Agora vamos preencher o conteúdo das colunas.
Vamos colocar 2 na quantidade em
todas as linhas de uma vez só:
TbFrutas.ListColumns("Quantidade").DataBodyRange
= 2
Você perceberá que ao invés do número 2 o Excel colocou R$ 2,00. Isso se deve porque ele copiou as propriedades da coluna
anterior na nova coluna. Para converter o formato da coluna para "geral",
use o seguinte comando:
TbFrutas.ListColumns("Quantidade").DataBodyRange.NumberFormat
= "General"
Agora o Excel exibe o número 2 corretamente como uma quantidade, não mais como moeda. Como podemos imaginar, a coluna
Preço Total já está em formato de
moeda, então não precisamos nos preocupar com o formato dessa coluna. Lembre-se
que um recurso bem interessante da tabela é poder utilizar o nome da coluna na
fórmula e podemos fazer isso também na programação:
TbFrutas.ListColumns("Preço
Total").DataBodyRange = "=[Preço]*[Quantidade]"
A fórmula foi passada para todas as linhas da coluna de
uma vez. Imagine que você tenha um arquivo que precisa importar rotineiramente,
transformar em tabela e adicionar colunas com cálculos entre os valores
recebidos. Com o que foi visto até aqui já é possível fazer essa automatização
de tarefa, economizando um tempo precioso.
Para adicionar uma linha nova ao final da tabela podemos usar a mesma abordagem anterior. Entretanto, para conseguir obter a
referência correta da linha dá mais trabalho. ListRows.Range.Count dá a quantidade de linhas da tabela,
inclusive a linha de cabeçalho e totais, enquanto ListRows.DataBodyRange dá somente as linhas do corpo da tabela. Portanto,
se a tabela tiver linha de totais, a linha nova seria a quantidade de linhas
fornecida por ListRows.Range.Count
menos um (a linha de totais) ou a quantidade de ListRows.DataBodyRange mais um (a linha de cabeçalho). Isto
adiciona uma certa complexidade ao código, ainda mais se sua tabela tiver
momentos em que a linha de totais fica visível ou não.
Podemos usar uma outra abordagem para conseguir o número
da nova linha, usando uma variável tipo Range
para receber a linha nova:
Dim NovaLinha As Range
Set NovaLinha = TbFrutas.ListRows.Add.Range
Desta forma, temos um objeto NovaLinha que se refere à linha nova adicionada. Para adicionar
conteúdo a essa linha, podemos usar a seguinte forma:
NovaLinha.Cells(1,1).Value = "Goiaba"
Entretanto, esta forma não permite utilizar os nomes de
coluna, perdendo assim o benefício das colunas nomeadas. Sabemos que o objeto NovaLinha se refere a essa linha
específica e que um tipo Range contém
a propriedade Row, que se refere ao
número da linha. Sabemos que o objeto TbFrutas
se refere à tabela, que pode usar as propriedades ListColumns e assim usar a referência da linha. Podemos escrever
então a seguinte maneira:
TbFrutas.ListColumns("Frutas").Range.Rows(NovaLinha.Row)
= "Goiaba"
Note que foi usada a propriedade Range (a tabela inteira) e não DataBodyRange
(o corpo de dados da tabela). Caso utilizássemos a segunda o texto seria
escrito na linha de totais, caso haja. Se não houver uma linha de totais, uma
nova linha seria adicionada com aquele conteúdo. Podemos então concluir como
escrever o conteúdo das outras colunas:
TbFrutas.ListColumns("Preço").Range.Rows(NovaLinha.Row)
= 5.7
TbFrutas.ListColumns("Quantidade").Range.Rows(NovaLinha.Row)
= 4
Como a fórmula é copiada automaticamente, não há
necessidade de preencher o conteúdo para a coluna Preço Total. Esta maneira de usar preserva a vantagem de usar o
nome das colunas como referência, portanto podemos criar uma nova coluna em
qualquer ponto da tabela que não irá interferir no código existente.
Como vimos acima, podemos exibir ou ocultar a linha de
totais com a propriedade ShowTotals.
Mas quando exibimos, a única coisa que aparece é a palavra Total na primeira coluna. Para essa linha fazer sentido é preciso
que tenha conteúdo. Para isso usamos a propriedade TotalsCalculation de ListColumns.
Essa propriedade pode receber um valor da enumeração xlTotalsCalculation, que pode ter os seguintes valores:
- xlTotalsCalculationNone:
Nenhum cálculo;
- xlTotalsCalculationSum:
Soma;
- xlTotalsCalculationAverage:
Média;
- xlTotalsCalculationCount:
Quantidade de células com valor preenchido;
- xlTotalsCalculationCountNums:
Quantidade de células com valores numéricos;
- xlTotalsCalculationMin:
Mínimo;
- xlTotalsCalculationMax:
Máximo;
- xlTotalsCalculationStdDev:
Desvio padrão;
- xlTotalsCalculationVar:
Variância;
- xlTotalsCalculationCustom:
Cálculo personalizado.
Para o nosso exemplo, precisamos da soma das colunas Quantidade e Preço Total. Fazemos da seguinte forma:
TbFrutas.ListColumns("Quantidade").TotalsCalculation
= xlTotalsCalculationSum
TbFrutas.ListColumns("Preço
Total").TotalsCalculation = xlTotalsCalculationSum
A tabela também possui as propriedades Find e Sort, que funcionam de maneira similar
ao que foi visto em artigos anteriores. Visite-os para aprender ou relembrar
como usar esses métodos.
Para finalizar, é preciso lembrar que a tabela é um
intervalo nomeado, portanto podemos Range("TbFrutas")
para editar as propriedades das células do intervalo, como por exemplo a tipagem
(fonte de texto). É preciso lembrar que as propriedades de Range e de ListObject são
diferentes e cada necessidade demanda o objeto adequado.
Para dúvidas sobre o artigo, comentários ou sugestões, utilize os comentários abaixo. Até o próximo artigo!
Pedro Martins
Pós-graduando em Business Intelligence e Big Data pela Faculdade Impacta de Tecnologia. Formado em Tecnologia em Eletrônica Digital com Ênfase em Microprocessadores
Muito Obrigado pelo artigo Pedro! Utilizo uma codificação para importar um arquivo Excel. Agora vou refazer essa codificação utilizando Tabelas pra praticar e melhorar meu código. Abraço e até o próximo artigo.
ResponderExcluirEspero que esse artigo ajude bastante. Acredito que a parte de cálculos será de grande valia.
ExcluirAjudou muito, Obrigado.
ExcluirPedro
ResponderExcluirMuito Bom esse artigo, o melhor que já li sobre o assunto.
Grande Abraço
Obrigado pelo elogio, é isso que motiva a continuar produzindo material :)
ResponderExcluirA forma como você explica o código, fica muito fácil o entendimento.Parabéns!
ResponderExcluirExcelente Artigo
ResponderExcluirEstou dando meus primeiros passos em VBA e artigos assim são excelentes pra mim. Ainda tenho algumas duvidas, mas devo ir melhorando com a pratica. Vlw!!!
ResponderExcluirOlá, bom dia. Primeiramente, parabéns pelo material. Muito claro e prático.
ResponderExcluirNão estou conseguindo pegar o maior número de uma coluna em tabelas, e gostaria de ajuda. Estou testando dois códigos, ambos sem sucesso:
Private Sub UserForm_Initialize()
Dim GRUPOALUNOS As Object
Set GRUPOALUNOS = ActiveSheet.ListObjects("GRUPOALUNOS")
Dim contador As Integer
contador = GRUPOALUNOS.ListColumns("CODGRUPO"). _
TotalsCalculation = xlTotalsCalculationMax
contador = contador + 1
Dim CONT As Integer
CONT = Application.WorksheetFunction.Max(GRUPOALUNOS.ListColumns("CODGRUPO").DataBodyRange.Select)
CONT = CONT + 1
contador = contador + 1
Range("A10") = contador
Range("A11") = CONT
End SUB
O resultado da variável "Contador" é 0 e da variável "Cont" é 1. Porém, existem muitos números nesta coluna, e o maior número é o número 200. Entretanto, não estou conseguindo trazer este número utilizando estes códigos.
Alguém poderia me ajudar?
Este comentário foi removido pelo autor.
ResponderExcluirParabéns , excelente trabalho, tiro muitas dúvidas por aqui. abraço. Roberto Caeiro
ResponderExcluirOlá Pedro, tudo bem?!
ResponderExcluirDepois que li seu artigo, passei a sempre usar tabelas nas minhas planilhas pois fica muito mais facil usar os nomes das tabelas e colunas do que os endereços das células no vba. Porém ainda não consegui desenvolver uma forma de selecionar uma determinada linha da tabela, é possível?
Para exemplificar o caso, tenho uma tabela TB_PROTOCOLO onde a primeira coluna (PROTOCOLO) são os números de protocolo. Tenho um trecho de código onde encontro um protocolo especifico nessa coluna e copio a linha inteira da planilha, mas quero copiar somente a linha da tabela, é possível fazer sem referenciar as células? Veja o trecho de código:
Dim PROT, ENDERECO As String
PROT = "2019-0002"
ENDERECO = Range("TB_PROT[PROTOCOLO]").Find(PROT).Address
Range(ENDERECO).Select
ActiveCell.EntireRow.Copy
Dá pra usar o recurso TB_PROTOCOLO[@] no vba?
Depois de algumas tentativas aqui, consegui uma forma de fazer, veja:
ExcluirDim PROT As String
Dim TBROW, TBCOL1, TBCOLS As Integer
PROT = "2019-0002"
TBCOL1 = Range("TB_PROT[PROTOCOLO]").Column
TBCOLS = Range("TB_PROT").Columns.Count
TBROW = Range("TB_PROT[PROTOCOLO]").Find(PROT).Row
Range(Cells(TBROW, TBCOL1), Cells(TBROW, TBCOLS)).Copy
Não sei se é a melhor forma de fazer (processamento, memória etc.) mas deu certo.