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.
Gostaria de saber como consigo despejar uma matriz inteira em um DataBodyRange??
ResponderExcluirEste comentário foi removido pelo autor.
ResponderExcluirExcelente artigo @Pedro !! Porém na parte de adição de linhas, usar Range para utilizar a propriedade Row confere uma certa complexidade na leitura do código.
ResponderExcluir