Classificando Dados no Excel VBA

Classificando Dados com o Excel VBA

O Excel tem uma excelente forma de ordenar um intervalo de dados tabulares utilizando o menu front end do Excel e, a dada altura, é provável que queira utilizar esta funcionalidade no seu código VBA. Felizmente, isso é muito fácil de fazer.

A caixa de diálogo do front-end pode ser encontrada clicando no ícone “Classificar” no grupo “Classificar e Filtrar” do separador “Dados” no menu do Excel. Primeiro, é necessário selecionar um intervalo de dados tabulares.

Também pode utilizar Alt-S-S para mostrar a caixa de diálogo para uma ordenação personalizada.

O método de classificação foi muito melhorado em versões posteriores do Excel. A ordenação costumava ser limitada a três níveis, mas agora é possível introduzir tantos níveis quantos os necessários, o que também se aplica ao VBA.

Você pode incorporar todas as funções de ordenação disponíveis na caixa de diálogo Classificar do Excel no seu código VBA. A função de Classificação no Excel é rápida, e mais rápida do que qualquer coisa que possa escrever em VBA, por isso tire partido da funcionalidade.

Observe que quando você faz uma classificação em VBA, os parâmetros de classificação permanecem os mesmos na caixa de diálogo de classificação front-end. Eles também são salvos quando a pasta de trabalho é salva.

Se um usuário selecionar o mesmo intervalo de dados tabulares e clicar no ícone Classificar, verá todos os parâmetros que foram introduzidos pelo seu código VBA. Se o usuário quiser fazer a sua própria ordenação, terá de eliminar primeiro todos os níveis de ordenação, o que será muito incômodo para ele.

Além disso, se não alterar os parâmetros no seu código e se basear em valores predefinidos, poderá verificar que o usuário fez alterações que se refletirão na sua classificação VBA e poderão dar resultados inesperados, que poderão ser muito difíceis de depurar.

Felizmente, existe um método Clear no VBA para redefinir todos os parâmetros de ordenação, de modo a que o usuário veja uma caixa de diálogo de ordenação limpa

Worksheets("Planilha1").Sort.SortFields.Clear

É uma boa prática limpar os parâmetros de ordenação no VBA antes e depois da classificação ter sido concluída.

Utilização Prática do Método Sort em VBA

Quando os dados tabulares são importados para o Excel, estão frequentemente numa ordem muito aleatória. Podem ser importados de um arquivo CSV (valores separados por vírgulas) ou podem vir de uma conexão a uma base de dados ou página Web. Não se pode confiar que estejam numa ordem definida de uma importação para outra.

Se apresentar estes dados a um usuário na sua planilha, o usuário pode ter dificuldade em ver e compreender uma enorme quantidade de dados que, em termos de ordem, estão desordenados. O usuário pode querer agrupar os dados ou cortar e colar certas seções dos mesmos noutra aplicação.

O usuário pode também querer ver, por exemplo, o empregado mais bem pago ou o empregado com mais tempo de serviço.

Utilizando o método Sort no VBA, é possível oferecer opções que permitam uma ordenação fácil para o usuário.

Dados de Amostra para Demonstrar a Classificação do Excel com VBA

Primeiro, precisamos que alguns dados de amostra sejam inseridos em uma planilha, para que o código possa demonstrar todos os recursos disponíveis no VBA.

Copie estes dados para uma planilha (chamada ‘Planilha1’) exatamente como mostrado.

Repare que foram utilizadas cores de fundo das células e cores de fonte de letra diferentes, uma vez que estas também podem ser utilizadas como parâmetros de ordenação. A ordenação utilizando as cores das células e dos tipos de letra será demonstrada mais à frente neste artigo. Note também que na célula E3, o nome do departamento está todo em minúsculas.

Não é necessário utilizar o interior da célula e as cores da fonte da letra se não pretender utilizar os exemplos de ordenação por célula e cor da fonte.

Gravação de uma Macro Para uma Classificação em VBA

O código VBA para classificação pode ser bastante complicado e, por vezes, pode ser uma boa ideia fazer a classificação no front end do Excel e gravar uma macro para mostrar como o código funciona.

Infelizmente, a função de gravação pode gerar uma grande quantidade de código porque define praticamente todos os parâmetros disponíveis, embora os valores padrão para muitos parâmetros sejam aceitáveis para a operação de classificação.

No entanto, dá-lhe uma ideia muito boa do que está envolvido na escrita de código de ordenação VBA, e uma vantagem é que o código gravado funcionará sempre. O seu próprio código pode necessitar de testes e depuração para funcionar corretamente.

Lembre-se de que, para uma operação feita em VBA, não há recurso de desfazer, portanto, é uma boa ideia fazer uma cópia dos dados tabulares em outra planilha antes de começar a escrever seu código de classificação.

Como exemplo, se fizer uma ordenação simples nos dados de amostra acima, ordenando por Empregado, a gravação geraria o seguinte código:

Sub Macro1()

Range("A1:E6").Select

ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Planilha1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .MatchCase = False
    .Orientation = xlTopToBottom
    .SortMethod = xlPinYin
    .Apply
End With

End Sub

Este é um grande pedaço de código, e muito dele é desnecessário devido à utilização de parâmetros predefinidos. No entanto, se estiver sob pressão de tempo para concluir um projeto e precisar de algum código que funcione rapidamente, pode facilmente colar isto no seu próprio código VBA.

No entanto, se quiser tornar o seu código compreensível e mais elegante, existem outras opções disponíveis.

Código VBA para Classificação de Nível Único

Se pretender ordenar os mesmos dados com base apenas no Empregado como anteriormente, o código é muito simples:

Sub ClassificacaoNivelUnico()

Worksheets("Planilha1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("A1"), Header:=xlYes

End Sub

Este código é muito mais fácil de compreender do que o código gravado porque aceita as predefinições, por exemplo, ordenação ascendente, pois não é necessário definir os parâmetros para valores predefinidos. Isto pressupõe que tenha utilizado previamente uma instrução ‘Clear’.

O método “Clear” é utilizado inicialmente para garantir que todos os parâmetros de classificação dessa planilha são redefinidos para os valores padrão. Um usuário pode ter definido previamente os parâmetros para valores diferentes, ou uma ordenação anterior em VBA pode tê-los alterado. É importante começar por uma posição predefinida ao ordenar, caso contrário, poderá facilmente obter resultados incorretos.

O método Clear não redefine o parâmetro Cabeçalho, e é aconselhável incluir isso no seu código, caso contrário o Excel pode tentar adivinhar se uma linha de cabeçalho está presente ou não.

Execute este código em relação aos dados de amostra e a sua planilha terá o seguinte aspecto:

Código VBA para Classificação Multinível

Você pode adicionar tantos níveis de ordenação quantos os necessários no seu código. Suponha que pretende ordenar primeiro por departamento e depois por data de admissão, mas por ordem ascendente para o departamento e descendente para a data de início:

Sub ClassificacaoMultinivel()

Worksheets("Planilha1").Sort.SortFields.Clear

Range("A1:E6").Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

End Sub

Note que agora existem duas chaves na instrução de ordenação (Key1 e Key2). A Key1 (coluna E do departamento) é ordenada em primeiro lugar e, em seguida, a Key2 (coluna C da data de admissão) é ordenada com base na primeira ordenação.

Existem também dois parâmetros de ordem. Order1 associa-se a Key1 (Departamento) e Order2 associa-se a Key2 (Data de Admissão). É importante assegurar que as chaves e as ordens são mantidas em sintonia umas com as outras.

Execute este código com base nos dados de amostra e a sua planilha terá o seguinte aspecto:

A coluna Departamento (E) está em ordem crescente e a coluna Data de Admissão (C) está em ordem decrescente.

O efeito desta ordenação é mais visível quando se olha para Jane Halfacre (linha 3) e John Sutherland (linha 4). Ambos estão em Finanças, mas Jane Halfacre começou antes de John Sutherland e as datas são apresentadas por ordem descendente.

Se o intervalo de dados tabulares puder ser de qualquer comprimento, pode-se utilizar o objeto UsedRange para definir o intervalo de ordenação. Isto só funcionará se existirem apenas os dados tabulares na planilha, uma vez que quaisquer valores fora dos dados darão resultados incorretos para o número de linhas e colunas.

Sub ClassificacaoMultinivel()

Worksheets("Planilha1").Sort.SortFields.Clear
Worksheets("Planilha1").UsedRange.Sort Key1:=Range("E1"), Key2:=Range("C1"), Header:=xlYes, _
    Order1:=xlAscending, Order2:=xlDescending

End Sub

Isto evita o problema se utilizar o método “End(xlDown)” para definir o intervalo de ordenação. Se houver uma célula em branco no meio dos dados, tudo o que estiver depois da célula em branco não será incluído, enquanto que UsedRange vai até à última célula ativa na planilha.

Ordenar por Cor da Célula

Desde o Excel 2007, a classificação pela cor de fundo de uma célula agora é possível, o que proporciona uma enorme flexibilidade ao projetar seu código de classificação no VBA.

Sub ClassificacaoNivelUnicoCorCelula()

Worksheets("Planilha1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add2 Key:=Range("A2:A6"), _
    SortOn:=xlSortOnCellColor, Order:=xlAscending, DataOption:=xlSortNormal

With ActiveWorkbook.Worksheets("Planilha1").Sort
    .SetRange Range("A2:E6")
    .Apply
End With

End Sub

Este código ordenará o intervalo de dados de exemplo (A2:A6) com base na cor de fundo da célula. Observe que agora há um parâmetro adicional chamado ‘SortOn’ que tem o valor de ‘xlSortOnCellColor’.

Note-se que o parâmetro “SortOn” só pode ser utilizado por um objeto de planilha e não por um objeto de intervalo.

Por este motivo, o código é mais complicado do que para uma ordenação que utiliza valores de células.

Este código utiliza um valor-chave para a ordenação que abrange todo o intervalo de dados, mas pode especificar colunas individuais como chave para a ordenação da cor de fundo e utilizar vários níveis, como mostrado anteriormente.

Depois de executar este código, a planilha passa a ter o seguinte aspecto:

 

Classificando por Cor de Fonte

A função de ordenação no Excel VBA oferece ainda mais flexibilidade na medida em que pode ordenar por cores da fonte:

Sub ClassificacaoNivelUnicoCorFonte()

Worksheets("Planilha1").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add(Range("A2:A6"), _
    xlSortOnFontColor, xlAscending, xlSortNormal).SortOnValue.Color = RGB(0, 0, 0)

With ActiveWorkbook.Worksheets("Planilha1").Sort
    .SetRange Range("A1:E6")
    .Header = xlYes
    .Orientation = xlTopToBottom
    .Apply
End With

End Sub

O código para ordenar por cor de fonte é muito mais complicado do que para a cor de fundo da célula. O parâmetro ‘SortOn’ tem agora o valor de ‘xlSortOnFontColor’.

Note que tem de especificar a orientação como ‘xlTopToBottom’ e tem de especificar uma cor para ordenar. Esta é especificada em termos RGB (Red, Green, Black) com valores que vão de 0 a 255.

Depois de executar este código em relação aos dados de amostra, a sua planilha terá o seguinte aspecto:

A ordenação por cores em VBA é muito mais complicada do que uma ordenação multinível, mas se o seu código de ordenação não funcionar (o que pode acontecer se faltar um parâmetro ou se não tiver introduzido o código corretamente), pode sempre recorrer à gravação de uma macro e integrar o código gravado no seu VBA.

Utilização de Outros Parâmetros na Classificação VBA

Há uma série de parâmetros opcionais que podem ser utilizados no código VBA para personalizar a ordenação.

SortOn

SortOn escolhe se a ordenação utilizará valores de célula, cores de fundo de célula ou cores de fonte de célula. A configuração padrão é Valores da célula.

SortOn = xlSortOnValues

Order

A opção Order escolhe se a ordenação será efetuada por ordem ascendente ou descendente. A predefinição é Ascendente.

Order = xlAscending

DataOption

DataOption escolhe a forma como o texto e os números são ordenados. O parâmetro xlSortNormal ordena os dados numéricos e de texto separadamente. O parâmetro xlSortTextAsNumbers trata o texto como dados numéricos para a ordenação. A predefinição é xlSortNormal.

DataOption = xlSortNormal

Header

Header seleciona se o intervalo de dados tabulares tem uma linha de cabeçalho ou não. Se houver uma linha de cabeçalho, ela não deve ser incluída na ordenação.

Os valores dos parâmetros são xlYes, xlNo e xlYesNoGuess. xlYesNoGuess deixa ao Excel a tarefa de determinar se existe uma linha de cabeçalho, o que pode facilmente levar a resultados inconsistentes. O uso desse valor não é recomendado.

O valor padrão é XNo (nenhuma linha de cabeçalho dentro dos dados). Com dados importados, geralmente há uma linha de cabeçalho, portanto, certifique-se de definir esse parâmetro como xlSim.

Header = xlYes

MatchCase

Este parâmetro determina se a ordenação é ou não sensível a maiúsculas e minúsculas. Os valores de opção são True (Verdadeiro) ou False (Falso). Se o valor for False, então os valores em minúsculas são considerados iguais aos valores em maiúsculas. Se o valor for True, a ordenação mostrará a diferença entre os valores em maiúsculas e minúsculas dentro da ordenação. O valor predefinido é Falso.

MatchCase = False

Orientation

Este parâmetro determina se a ordenação será efetuada para baixo, através das linhas, ou através de todas as colunas. O valor predefinido é xlTopToBottom (ordenar através das linhas). Pode utilizar xlLeftToRight se pretender ordenar horizontalmente. Valores como xlRows e xlColumns não funcionam para este parâmetro.

Orientation = xlTopToBottom

SortMethod

Este parâmetro só é utilizado para ordenar as línguas chinesas. Tem dois valores, xlPinYin e xlStroke. xlPinYin é o valor predefinido.

xlPinYin ordena utilizando a ordem de classificação fonética chinesa para caracteres. xlStroke ordena pela quantidade de traços em cada carácter.

Se gravar uma macro de ordenação, este parâmetro será sempre incluído no código, e você pode muito bem ter-se perguntado o que significava. No entanto, a menos que esteja a lidando com dados em chinês, é de pouca utilidade.

SortMethod = xlPinYin

Utilização de um Evento de Duplo Clique para Ordenar Dados Tabulares

Em todas as funcionalidades que a Microsoft incluiu nos métodos de ordenação para VBA, não incluiu um meio simples de fazer duplo clique num cabeçalho de coluna e ordenar todos os dados tabulares com base nessa coluna específica.

Esta é uma funcionalidade realmente útil e é fácil escrever o código para o fazer.

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
'Pressupõe-se que os dados começam na célula A1

'Crie três variáveis para capturar a coluna de destino selecionada e a coluna _
' e a linha máximas dos dados tabulares.
Dim Col As Integer, RCol As Long, RRow As Long

'Verifique se o usuário clicou duas vezes na linha do cabeçalho - linha 1; caso contrário, saia da sub
If Target.Row <> 1 Then Exit Sub

'Capture the maximum rows in the tabular data range using the ‘UsedRange’ object
RCol = ActiveSheet.UsedRange.Columns.Count

'Capture o máximo de linhas no intervalo de dados tabulares usando o objeto "UsedRange"
RRow = ActiveSheet.UsedRange.Rows.Count

'Verificar se o usuário não clicou duas vezes em uma coluna fora do intervalo de dados
If Target.Column > RCol Then Exit Sub

'Capturar a coluna em que o usuário clicou duas vezes
Col = Target.Column

'Limpar os parâmetros de classificação anteriores
ActiveSheet.Sort.SortFields.Clear

'Classifique o intervalo tabular conforme definido pelo máximo de linhas e colunas do objeto "UsedRange".
'Classifique os dados tabulares usando a coluna clicada duas vezes pelo usuário como a chave de classificação
ActiveSheet.Range(Cells(1, 1), Cells(RRow, RCol)).Sort Key1:=Cells(1, Col), Header:=xlYes

'Selecione a célula A1 - isso serve para garantir que o usuário não fique no modo de edição _
'depois que a classificação for concluída
ActiveSheet.Range("A1").Select

End Sub

Este código tem de ser colocado no evento de duplo clique na planilha que contém os dados tabulares. Para tal, clique no nome da planilha na janela Projeto – VBAProject (canto superior esquerdo da tela do VBE) e, em seguida, selecione ‘Worksheet’ no primeiro menu pendente da janela de código. Selecione ‘BeforeDoubleClick’ no segundo menu pendente e pode então introduzir o seu código.

Note que não há nomes, intervalos ou referências de células codificadas neste código, exceto para mover o cursor para a célula A1 no final do código. O código foi concebido para obter todas as informações necessárias a partir das coordenadas da célula em que o usuário fez duplo clique e do tamanho do intervalo de dados tabulares.

Não importa o tamanho do intervalo de dados tabulares. O código continuará a obter todas as informações necessárias e pode ser utilizado em dados existentes em qualquer local do seu arquivo sem ter de introduzir valores por código.

O única premissa assumida é que existe uma linha de cabeçalho nos dados tabulares e que o intervalo de dados começa na célula A1, mas a posição inicial do intervalo de dados pode ser facilmente alterada no código.

Qualquer usuário ficará devidamente impressionado com esta nova funcionalidade de ordenação!

Extensão da Função de Classificação Utilizando VBA

A Microsoft permitiu uma enorme flexibilidade na classificação utilizando uma vasta gama de parâmetros. No entanto, no VBA, é possível ir mais longe.

Suponha que pretende ordenar quaisquer valores com um tipo de letra negrito no topo dos seus dados. Não há forma de o fazer no Excel, mas pode escrever o código VBA para o fazer:

Sub OrdenarPorNegrito()

'Criar variáveis para gravar o número de linhas e colunas dos dados tabulares
Dim RRow As Long, RCol As Long, N As Long

'Desativar a atualização da tela para que o usuário não possa ver o que está acontecendo _
'ele pode ver os valores sendo alterados e se perguntar por quê
Application.ScreenUpdating = False

'Capturar o número de colunas no intervalo de dados tabulares
RCol = ActiveSheet.UsedRange.Columns.Count

'Capturar o número de linhas dentro do intervalo de dados tabulares
RRow = ActiveSheet.UsedRange.Rows.Count

'Iterar por todas as linhas no intervalo de dados tabulares, ignorando a linha do cabeçalho
For N = 2 To RRow
    'Se uma célula tiver uma fonte em negrito, coloque um valor 0 à esquerda em relação ao valor da célula
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = "0" & ActiveSheet.Cells(N, 1).Value
    End If
Next N

'Limpar todos os parâmetros de classificação anteriores
ActiveSheet.Sort.SortFields.Clear

'Classificar o intervalo de dados tabulares. Todos os valores com um valor 0 à esquerda serão movidos para o topo
ActiveSheet.Range(Cells(1, 1), Cells(RRow, RCol)).Sort Key1:=Cells(1, 1), Header:=xlYes

'Iterar por todas as linhas no intervalo de dados tabulares, ignorando a linha do cabeçalho
For N = 2 To RRow
    'Se uma célula tiver uma fonte em negrito, remova o valor 0 inicial do valor da célula _
    'para restaurar os valores originais
    If ActiveSheet.Cells(N, 1).Font.Bold = True Then
        ActiveSheet.Cells(N, 1).Value = Mid(ActiveSheet.Cells(N, 1).Value, 2)
    End If
Next N

'Ativar novamente a atualização da tela
Application.ScreenUpdating = True

End Sub

O código calcula o tamanho do intervalo de dados tabulares utilizando o objeto ‘UsedRange’ e, em seguida, itera através de todas as linhas dentro do mesmo. Quando é encontrado um tipo de letra negrito, é colocado um zero à frente do valor da célula.

Em seguida, é efetuada uma ordenação. Como a ordenação é feita por ordem ascendente, tudo o que tiver um zero à frente irá para o topo da lista.

Em seguida, o código itera através de todas as linhas e remove os zeros à esquerda, restaurando os dados para os seus valores originais.

Este código ordena utilizando fontes a negrito como critério, mas pode facilmente utilizar outras características da célula da mesma forma, por exemplo, fonte itálica, tamanho do texto, fonte sublinhada, nome da fonte, etc.