Classificando dados no Excel VBA
O Excel tem um meio excelente de classificar uma variedade de dados tabulares usando a faixa de opções no front end do Excel e, em algum momento, você provavelmente desejará usar essa funcionalidade em seu código VBA. Felizmente, isso é muito fácil de fazer.
A caixa de diálogo front-end é encontrada clicando no ícone ‘Classificar’ no grupo ‘Classificar e Filtrar’ da guia ‘Dados’ na faixa do Excel. Você precisa selecionar um intervalo de dados tabulares primeiro.
Você também pode usar Alt-A-S-S para mostrar a caixa de diálogo para uma classificação personalizada.
O método de classificação foi bastante aprimorado nas versões posteriores do Excel. A classificação costumava ser restrita a três níveis, mas agora você pode inserir quantos níveis desejar, e isso também se aplica ao VBA.
Você pode incorporar todas as funções de classificação oferecidas na caixa de diálogo Classificar do Excel em seu código VBA. A função de classificação no Excel é rápida e mais rápida do que qualquer coisa que você possa escrever em VBA, portanto, tire proveito da funcionalidade.
Observe que quando você faz uma classificação no VBA, os parâmetros de classificação permanecem os mesmos na caixa de diálogo de classificação do 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, ele verá todos os seus parâmetros inseridos por seu código VBA. Se eles quiserem fazer um tipo de design próprio, eles terão que excluir todos os seus níveis de classificação primeiro, o que será muito chato para eles.
Além disso, se você não alterar os parâmetros em seu código e confiar nos valores padrão, poderá descobrir que o usuário fez alterações que refletirão em sua classificação VBA e podem gerar resultados inesperados, que podem ser muito difíceis de depurar .
Felizmente, existe um método Clear no VBA para redefinir todos os parâmetros de classificação para que o usuário veja uma caixa de diálogo de classificação limpa
| 1 | Planilhas ("Planilha1"). Sort.SortFields.Clear |
É uma boa prática limpar os parâmetros de classificação no VBA antes e depois que a classificação for concluída.
Uso prático do método de classificação em VBA
Quando os dados tabulares são importados para o Excel, geralmente estão em uma ordem muito aleatória. Ele pode ser importado de um arquivo CSV (valores separados por vírgula) ou pode vir de um link para um banco de dados ou página da web. Você não pode confiar que ele esteja em uma ordem definida de uma importação para outra.
Se você estiver apresentando esses dados a um usuário em sua planilha, o usuário pode achar difícil olhar e entender uma grande quantidade de dados que, em termos de ordem, estão por toda parte. Eles podem querer agrupar os dados ou recortar e colar certas seções deles em outro aplicativo.
Eles também podem querer ver, por exemplo, o funcionário mais bem pago ou o funcionário com o serviço mais longo.
Usando o método Sort no VBA, você pode oferecer opções que permitem fácil classificação para o usuário.
Dados de amostra para demonstrar 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 esses dados em uma planilha (chamada 'Planilha1') exatamente como mostrado.
Observe que diferentes cores de fundo de célula e cores de fonte foram usadas, pois também podem ser usadas como parâmetros de classificação. A classificação usando cores de células e fontes será demonstrada posteriormente neste artigo. Observe também que na célula E3, o nome do departamento está todo em minúsculas.
Você não precisa do interior da célula e das cores da fonte se não quiser usar os exemplos de classificação por célula e cor da fonte.
Gravando uma macro para uma classificação VBA
O código VBA para classificação pode ser bastante complicado e, às 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 sua operação de classificação.
No entanto, dá uma boa ideia do que está envolvido na escrita do código de classificação VBA, e uma vantagem é que o código gravado sempre funcionará para você. Seu próprio código pode precisar 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.
Por exemplo, se você fez uma classificação simples nos dados de amostra acima, classificando por Funcionário, a gravação geraria o seguinte código:
| 123456789101112131415161718 | Sub Macro1 ()Intervalo ("A1: E6"). SelecioneActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnValues, Order: = xlAscending, DataOption: = xlSortNormalCom ActiveWorkbook.Worksheets ("Planilha1"). Classificar.SetRange Range ("A1: E6").Header = xlSim.MatchCase = False.Orientation = xlTopToBottom.SortMethod = xlPinYin.AplicarTerminar comEnd Sub |
Este é um grande pedaço de código, e muito dele é desnecessário por causa dos parâmetros padrão que estão sendo usados. No entanto, se você estiver sob pressão de tempo para concluir um projeto e precisar de algum código que funcione rapidamente, poderá colá-lo facilmente em seu próprio código VBA.
No entanto, se você quiser tornar seu código mais compreensível e elegante, existem outras opções disponíveis.
Código VBA para fazer uma classificação de nível único
Se você deseja classificar o código de amostra com base em Funcionário apenas como antes, ao gravar uma macro, o código é muito simples:
| 1234567 | Sub SingleLevelSort ()Planilhas ("Planilha1"). Sort.SortFields.ClearIntervalo ("A1: E6"). Chave de classificação1: = Intervalo ("A1"), Cabeçalho: = xlSimEnd Sub |
Isso é muito mais fácil de entender do que o código gravado porque aceita os padrões, por exemplo, classificação crescente, portanto, não há necessidade de definir os parâmetros para os valores padrão. Isso pressupõe que você tenha usado uma declaração 'Clear' de antemão.
O método 'Clear' é usado inicialmente para garantir que cada parâmetro de classificação para essa planilha seja definido de volta para os valores padrão. Um usuário pode ter definido previamente os parâmetros com valores diferentes ou uma classificação anterior no VBA pode tê-los alterado. É importante começar de uma posição padrão ao classificar, caso contrário, você pode facilmente acabar com resultados incorretos.
O método Clear não redefine o parâmetro Header e é aconselhável incluí-lo em 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 nos dados de amostra e sua planilha ficará assim:
Código VBA para fazer uma classificação multinível
Você pode adicionar quantos níveis de classificação forem necessários em seu código. Suponha que você deseje classificar primeiro por departamento e depois por data de início, mas em ordem crescente para o departamento e em ordem decrescente para a data de início:
| 12345678 | Sub MultiLevelSort ()Planilhas ("Planilha1"). Sort.SortFields.ClearIntervalo ("A1: E6"). Chave de classificação1: = Intervalo ("E1"), Chave2: = Intervalo ("C1"), Cabeçalho: = xlSim, _Ordem1: = xlAscendente, Ordem2: = xlDescendenteEnd Sub |
Observe que agora existem duas chaves na instrução de classificação (Chave1 e Chave2). Key1 (coluna E do departamento) é classificado primeiro de tudo e, em seguida, Key2 (coluna C da data de início) é classificado com base na primeira classificação.
Existem também dois parâmetros de pedido. O Pedido1 é associado à Chave1 (Departamento) e o Pedido2 é associado à Chave2 (Data de Início). É importante garantir que as chaves e os pedidos sejam mantidos em sintonia.
Execute este código nos dados de amostra e sua planilha ficará assim:
A coluna Departamento (E) está em ordem crescente e a coluna Data de início (C) está em ordem decrescente.
O efeito desse tipo é mais perceptí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 mostradas em ordem decrescente.
Se o intervalo de dados tabulares puder ser de qualquer comprimento, você poderá usar o objeto UsedRange para definir o intervalo de classificação. Isso só funcionará se houver apenas os dados tabulares na planilha, pois qualquer valor fora dos dados fornecerá resultados incorretos para o número de linhas e colunas.
| 1234567 | Sub MultiLevelSort ()Planilhas ("Planilha1"). Sort.SortFields.ClearPlanilhas ("Folha1"). UsedRange.Sort Key1: = Range ("E1"), Key2: = Range ("C1"), Header: = xlYes, _Ordem1: = xlAscendente, Ordem2: = xlDescendenteEnd Sub |
Isso evita o problema se você usar o método ‘End (xlDown)’ para definir o intervalo de classificação. Se houver uma célula em branco no meio dos dados, qualquer coisa após a célula em branco não será incluída, enquanto UsedRange desce para a última célula ativa na planilha.
Classificando pela Cor da Célula
Desde o Excel 2007, a classificação pela cor de fundo de uma célula agora é possível, o que fornece enorme flexibilidade ao projetar seu código de classificação em VBA.
| 123456789101112 | Sub SingleLevelSortByCellColor ()Planilhas ("Planilha1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add2 Key: = Range ("A2: A6"), _SortOn: = xlSortOnCellColor, Order: = xlAscending, DataOption: = xlSortNormalCom ActiveWorkbook.Worksheets ("Planilha1"). Classificar.SetRange Range ("A1: E6").AplicarTerminar comEnd Sub |
Este código classificará o intervalo de dados de amostra (A2: A6) com base na cor de fundo da célula. Observe que agora existe um parâmetro adicional chamado ‘SortOn’, que tem o valor ‘xlSortOnCellColor’.
Observe que o parâmetro ‘SortOn’ só pode ser usado por um objeto de planilha e não por um objeto de intervalo.
Por causa disso, o código é mais complicado do que para uma classificação usando valores de células.
Este código usa um valor-chave para a classificação que cobre todo o intervalo de dados, mas você pode especificar colunas individuais como a chave para a classificação da cor de fundo e usar vários níveis, conforme mostrado anteriormente.
Depois de executar este código, sua planilha ficará assim:
Classificando por cor da fonte
A função de classificação no Excel VBA oferece ainda mais flexibilidade, pois você pode classificar por cores de fonte:
| 1234567891011121314 | Sub SingleLevelSortByFontColor ()Planilhas ("Planilha1"). Sort.SortFields.ClearActiveWorkbook.Worksheets ("Sheet1"). Sort.SortFields.Add (Range ("A2: A6"), _xlSortOnFontColor, xlAscending, xlSortNormal) .SortOnValue.Color = RGB (0, 0, 0)Com ActiveWorkbook.Worksheets ("Planilha1"). Classificar.SetRange Range ("A1: E6").Header = xlSim.Orientation = xlTopToBottom.AplicarTerminar comEnd Sub |
O código para classificar pela cor da fonte é muito mais complicado do que para a cor de fundo da célula. O parâmetro ‘SortOn’ agora contém o valor de ‘xlSortOnFontColor’.
Observe que você deve especificar a orientação como ‘xlTopToBottom’ e deve especificar uma cor para classificar. Isso é especificado em termos RGB (vermelho, verde, preto) com valores variando de 0 a 255.
Depois de executar esse código nos dados de amostra, sua planilha ficará assim:
Classificar usando cores em VBA é muito mais complicado do que uma classificação de vários níveis, mas se o seu código de classificação não funcionar (o que pode acontecer se um parâmetro estiver faltando ou se você não tiver inserido o código corretamente), você sempre pode voltar a gravar uma macro e integração do código gravado em seu VBA.
Usando outros parâmetros na classificação VBA
Existem vários parâmetros opcionais que você pode usar em seu código VBA para personalizar sua classificação.
SortOn
SortOn escolhe se a classificação usará valores de célula, cores de fundo de célula ou cores de fonte de célula. A configuração padrão é Valores de Célula.
| 1 | SortOn = xlSortOnValues |
Pedido
Ordem escolhe se a classificação será feita em ordem crescente ou decrescente. O padrão é Crescente.
| 1 | Pedido = xlAscending |
DataOption
DataOption escolhe como o texto e os números são classificados. O parâmetro xlSortNormal classifica dados numéricos e de texto separadamente. O parâmetro xlSortTextAsNumbers trata o texto como dados numéricos para a classificação. O padrão é xlSortNormal.
| 1 | DataOption = xlSortNormal |
Cabeçalho
Cabeçalho escolhe se o intervalo de dados tabulares tem uma linha de cabeçalho ou não. Se houver uma linha de cabeçalho, você não deseja que ela seja incluída na classificação.
Os valores dos parâmetros são xlYes, xlNo e xlYesNoGuess. xlYesNoGuess deixa para o Excel determinar se há uma linha de cabeçalho, o que poderia facilmente levar a resultados inconsistentes. O uso deste valor não é recomendado.
O valor padrão é XNo (nenhuma linha de cabeçalho nos dados). Com dados importados, geralmente há uma linha de cabeçalho, portanto, certifique-se de definir esse parâmetro como xlYes.
| 1 | Cabeçalho = xl Sim |
Caso de compatibilidade
Este parâmetro determina se a classificação diferencia maiúsculas de minúsculas ou não. Os valores das opções são True ou False. Se o valor for False, os valores em minúsculas serão considerados iguais aos valores em maiúsculas. Se o valor for True, a classificação mostrará a diferença entre os valores em maiúsculas e minúsculas dentro da classificação. O valor padrão é falso.
| 1 | MatchCase = False |
Orientação
Este parâmetro determina se a classificação ocorrerá para baixo nas linhas ou em todas as colunas. O valor padrão é xlTopToBottom (classificar pelas linhas). Você pode usar xlLeftToRight se desejar classificar horizontalmente. Valores como xlRows e xlColumns não funcionam para este parâmetro.
| 1 | Orientação = xlTopToBottom |
SortMethod
Este parâmetro é usado apenas para classificar os idiomas chineses. Ele possui dois valores, xlPinYin e xlStroke. xlPinYin é o valor padrão.
xlPinYin classifica usando a ordem de classificação chinesa fonética para caracteres. xlStroke classifica pela quantidade de traços em cada caractere.
Se você gravar uma macro de classificação, esse parâmetro sempre será incluído no código e você pode muito bem ter se perguntado o que significava. No entanto, a menos que você esteja lidando com dados em chinês, eles são de pouca utilidade.
| 1 | SortMethod = xlPinYin |
Usando um evento de clique duplo para classificar dados tabulares
Em toda a funcionalidade que a Microsoft incluiu nos métodos de classificação para VBA, ela não incluiu um meio simples de clicar duas vezes no cabeçalho de uma coluna e classificar todos os dados tabulares com base nessa coluna específica.
Este é um recurso realmente útil e fácil de escrever o código para fazê-lo.
| 12345678910111213141516171819202122232425262728293031323334 | Sub-planilha privada_BeforeDoubleClick (ByVal Target As Range, Cancel As Boolean)'Supõ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 linha máximas de _'os dados tabularesDim 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 subIf Target.Row 1 Then Exit Sub'Capture o máximo de linhas no intervalo de dados tabulares usando o objeto ‘UsedRange’RCol = ActiveSheet.UsedRange.Columns.Count'Capture o máximo de colunas no intervalo de dados tabular usando o objeto' UsedRange 'RRow = ActiveSheet.UsedRange.Rows.Count'Verifique se o usuário não clicou duas vezes em uma coluna fora do intervalo de dados tabularIf Target.Column> RCol Then Exit Sub'Capture a coluna que o usuário clicou duas vezesCol = Target.Column'Limpe os parâmetros de classificação anterioresActiveSheet.Sort.SortFields.Clear'Classifica 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çãoActiveSheet.Range (Cells (1, 1), Cells (RCol, RRow)). Sort Key1: = Cells (1, Col), Header: = xlSim'Selecione a célula A1 - isso é para garantir que o usuário não seja deixado no modo de edição após a classificação ser _'completadoActiveSheet.Range ("A1"). SelecioneEnd Sub |
Este código precisa ser colocado no evento de clique duplo na planilha que contém os dados tabulares. Você faz isso clicando no nome da planilha na janela Project Explorer (canto superior esquerdo da tela do VBE) e, em seguida, selecionando ‘Planilha’ no primeiro menu suspenso na janela de código. Selecione ‘BeforeDoubleClick’ no segundo menu suspenso e, em seguida, você poderá inserir seu código.
Observe que nenhum nome, intervalo ou referência de célula está codificado neste código, exceto para mover o cursor para a célula A1 no final do código. O código é projetado para obter todas as informações necessárias das coordenadas da célula nas quais o usuário clicou duas vezes e do tamanho do intervalo de dados tabular.
Não importa o tamanho do intervalo de dados tabulares. O código ainda coletará todas as informações necessárias e pode ser usado em dados mantidos em qualquer lugar de sua pasta de trabalho, sem a necessidade de codificar os valores.
A única suposição feita é que há 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 dentro do código.
Qualquer usuário ficará impressionado com esta nova funcionalidade de classificação!
Estendendo a função de classificação usando VBA
A Microsoft permitiu uma enorme flexibilidade na classificação usando uma ampla gama de parâmetros. No entanto, no VBA, você pode levar isso mais longe.
Suponha que você queira classificar quaisquer valores com uma fonte em negrito no topo de seus dados. Não há como fazer isso no Excel, mas você pode escrever o código VBA para fazer isso:
| 123456789101112131415161718192021222324252627282930313233343536373839404142 | Sub SortByBold ()'Crie variáveis para conter o número de linhas e colunas para os dados tabularesDim RRow As Long, RCol As Long, N As Long'Desligue a atualização de tela para que o usuário não possa ver o que está acontecendo - eles podem ver _'valores sendo alterados e me pergunto por queApplication.ScreenUpdating = False'Capture o número de colunas no intervalo de dados tabularRCol = ActiveSheet.UsedRange.Columns.Count'Capture o número de linhas dentro do intervalo de dados tabularRRow = ActiveSheet.UsedRange.Rows.Count'Iterar por todas as linhas no intervalo de dados tabular, ignorando a linha do cabeçalhoPara N = 2 para RRow'Se uma célula tiver uma fonte em negrito, coloque um valor 0 inicial contra o valor da célulaSe ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = "0" & ActiveSheet.Cells (N, 1) .ValueFim sePróximo N'Limpe todos os parâmetros de classificação anterioresActiveSheet.Sort.SortFields.Clear'Classifique o intervalo de dados tabular. Todos os valores com valor 0 à esquerda irão para o topoActiveSheet.Range (Cells (1, 1), Cells (RCol, RRow)). Sort Key1: = Cells (1, 1), Header: = xlSim'Iterar por todas as linhas no intervalo de dados tabular, ignorando a linha do cabeçalhoPara N = 2 para RRow'Se uma célula tiver uma fonte em negrito, remova o valor 0 inicial do valor da célula para _'restaurar os valores originaisSe ActiveSheet.Cells (N, 1) .Font.Bold = True ThenActiveSheet.Cells (N, 1) .Value = Mid (ActiveSheet.Cells (N, 1) .Value, 2)Fim sePróximo N'Ative a atualização da tela novamenteApplication.ScreenUpdating = TrueEnd Sub |
O código calcula o tamanho do intervalo de dados tabulares usando o objeto ‘UsedRange’ e, em seguida, itera por todas as linhas dentro dele. Quando uma fonte em negrito é encontrada, um zero à esquerda é colocado na frente do valor da célula.
Uma espécie então ocorre. Como a classificação está em ordem crescente, qualquer coisa com um zero na frente irá para o topo da lista.
O código então itera por todas as linhas e remove os zeros à esquerda, restaurando os dados aos seus valores originais.
Este código classifica usando fontes em negrito como critério, mas você pode facilmente usar outras características da célula da mesma maneira, por exemplo, fonte itálica, tamanho do texto em pontos, fonte de sublinhado, nome da fonte, etc.
