VBA - Artigo 035 - Usando tabela no VBA

Postado Por: Unknown Data: 06:00 / comentario : 14

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