Select Menu



por Alessandro Trovato

» » » » » » » VBA - Artigo 015 - Compreendendo o Sort
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

Progredindo em VBA no Microsoft Excel

Compreendendo o Sort


O Sort, como se sabe, é o meio utilizado para ordenar colunas em uma planilha. Este é um comando que exige o conhecimento de detalhes, sendo por essa razão que muitos preferem gravar um macro ao invés de codificar, depois pegam o código gerado pelo gravador de macros e colocam no código no qual estejam trabalhando. Entretanto, há vezes que precisamos de ordenar um grupo de células que pode mudar em quantidade de linhas ou mesmo de colunas, que podem ser acrescentadas ao final. Desta forma, um código gerado pelo gravador de macros não servirá, pois ele mantém o intervalo de células fixo.

Segue um exemplo do código gerado desta forma, utilizando em uma planilha com cinco colunas (de A a E) e vinte registros (linhas 2 a 21, a linha 1 contém o cabeçalho):

    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add Key:=Range("A1"), _
        SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:=xlSortNormal
    With ActiveWorkbook.Worksheets("Planilha1").Sort
        .SetRange Range("A2:E21")
        .Header = xlNo
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Vamos entender o que ele faz nesse processo analisando o código. A primeira linha faz uma limpeza dos campos a serem ordenados (método SortFields.Clear). Isso é para evitar que defina uma nova área de ordenação sem que a anterior interfira no resultado. É importante que o método SortFields.Clear seja executado no início do bloco de ordenação, mais adiante você entenderá o motivo.

Logo abaixo, o método SortFields.Add é utilizado com alguns parâmetros. Vejamos a função de cada um desses:

Key (requerido): Especifica a chave da ordenação. O código foi gerado com Range("A1");

SortOn (opcional): Define a forma de ordenação. O valor padrão caso não seja especificado é xlSortOnValues, o mesmo gerado pelo gravador de macros. Outros valores possíveis são xlSortOnCellColor, xlSortOnFontColor e xlSortOnIcons, mas é preciso adicionar mais instruções ao código, que serão vistas mais adiante;

Order (opcional): É a forma de ordenação em si. O valor padrão é xlAscending (crescente) e a outra opção é xlDescending (decrescente);

DataOption (opcional): Refere-se a como os dados serão tratados. O valor padrão é xlSortNormal, que ordena separadamente células formatadas como texto e número, independente do conteúdo. O outro valor possível é xlSortTextAsNumbers, que verifica se há números formatados como texto (por exemplo '35) , ajustando a ordenação neste caso;

- CustomOrder (opcional): Esta propriedade não apareceu no código gerado porque é usada em listas personalizadas. Serve para ordenar listas que não sejam em ordem alfabéticas, como nome de meses. Os valores devem ser passados como string e separados por vírgulas, como por exemplo CustomOrder:= "jan,fev,mar,abr,mai,jun,jul,ago,set,out,nov,dez".

A melhor forma de aprender é testando o código na prática, portanto uma planilha para testar o código fornecido aqui é importante. Crie uma planilha com valores aleatórios para poder testar o código original fornecido pelo gerador de macros. Coloque na quinta coluna valores de meses com três letras, para fazer o teste com o CustomOrder.

É possível criar várias chaves de ordenação acrescentando mais métodos SortFields.Add após o primeiro no código. O limite do Excel é de 64 chaves, o que é mais do que suficiente para praticamente toda necessidade, salvo casos extremamente raros. Vamos remover os parâmetros não utilizados e acrescentar mais uma chave na ordenação no nosso exemplo:

    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add Key:=Range("A1")
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add Key:=Range("B1")

Executando o código vemos que a coluna A foi ordenada e depois a B. Isso só será visualizado se houver valores iguais na primeira coluna. Se sua planilha não tiver valores iguais na primeira coluna, altere alguns valores para poder ver o resultado.

Cada linha de chave deve ter seus próprios parâmetros, sendo que os parâmetros para uma chave não interferem nos da outra chave. Acrescente o parâmetro Option:=xlDescending na linha referente à coluna B e teste o resultado. Você verá que a segunda coluna foi ordenada em ordem decrescente.

A quinta coluna foi preenchida com meses abreviados (com três caracteres). Vamos alterar a primeira chave para "E1", sem acrescentar nenhum parâmetro. Ao executar a rotina, a coluna foi ordenada da seguinte forma: Abr, Ago, Dez, Fev, Jan, Jun, Jul, Mai, Mar, Nov, Out, Set, ou seja, ordem alfabética, que é a ordem padrão. Acrescente o parâmetro CustomOrder:= "jan,fev,mar,abr,mai,jun,jul,ago,set,out,nov,dez" e teste o resultado. Agora fica na ordem desejada. Perceba que você também pode utilizar este parâmetro para meses por extenso, dias da semana por extenso ou abreviados e quaisquer outras listas personalizadas. Se houver valores que não estejam na lista, eles irão aparecer no final em ordem alfabética.

Agora vejamos as propriedades que foram agrupadas no bloco With (não sei porque os métodos SortFields.Add e SortFields.Clear ficaram de fora, mas poderiam ficar dentro do bloco With para economizar espaço):

- SetRange (requerido): Define o intervalo que será utilizado para ordenação, podendo incluir ou não o cabeçalho (próximo item). Pode usar um intervalo nomeado ou uma variável que contenha um intervalo;

- Header (opcional): Indica se o intervalo definido para ordenar tem ou não cabeçalho. O valor padrão é xlNo, que significa que não há cabeçalho e que todo o intervalo deve ser ordenado. O valor xlYes informa que a primeira linha é cabeçalho e esta se mantém no topo do intervalo. Há também o valor xlGuess, onde o Excel irá adivinhar se há ou não um cabeçalho, mas não recomendo seu uso;

- MatchCase (opcional): Propriedade booleana, quando True ordena levando em conta caixa alto ou baixa (maiúsculas e minúsculas), quando False (valor padrão) não faz distinção;

- Orientation (opcional): O valor que veio no gravador de macros é xlTopToBottom porque está ordenando pelas colunas. Para ordenar por linhas o valor é xlLeftToRight;

- SortMethod (opcional): Há dois valores possíveis: xlPinYin e xlStroke, que são usados para caracteres do leste asiático. Em caracteres latinos ambos valores chegam no mesmo resultado.

Por fim, temos o método Apply. Como o próprio nome sugere, aplica todos os parâmetros e propriedades definidos anteriormente, executando a ordenação em si. Se não houver este método no código nada será ordenado. Também não pode ser colocado no começo ou no meio, pois neste caso não irá levar em conta as definições que forem colocadas posteriormente.

Vimos que há vários parâmetros e propriedades que são opcionais, ou seja, nem precisa codificar. Vejamos como fica o código sem esses opcionais e definindo variáveis referentes à planilha e ao intervalo:

    Dim Planilha As Worksheet
    Dim Intervalo As Range
   
    Set Planilha = ActiveWorkbook.Worksheets("Planilha1")
    Set Intervalo = Range("A2:E21")
   
    With Planilha.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1")
        .SetRange Intervalo
        .Apply
    End With

O código ficou muito mais enxuto e muito mais fácil de ser compreendido sem aquele monte de opcionais. Os parâmetros podem ser acrescentados conforme a necessidade (ordem decrescente, texto como número, distinção de caixa alta/baixa etc).

Agora suponha que tenha uma rotina na planilha que precise ordenar de uma forma para realizar um processamento e depois ordene de novo para o formato anterior. Aqui vai ficar evidente a necessidade do SortFields.Clear:

    Dim Planilha As Worksheet
    Dim Intervalo As Range
   
    Set Planilha = ActiveWorkbook.Worksheets("Planilha1")
    Set Intervalo = Range("A2:E21")
   
' Ordena pela coluna A
    With Planilha.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("A1")
        .SetRange Intervalo
        .Apply
    End With

' ... 
' processamento após o primeiro Sort
' ... 

' Ordena pela coluna E
    With Planilha.Sort
        .SortFields.Clear
        .SortFields.Add Key:=Range("E1"), _
            CustomOrder:="jan,fev,mar,abr,mai,jun,jul,ago,set,out,nov,dez"
        .SetRange Intervalo
        .Apply
    End With

Coloque um ponto de interrupção no segundo With, para que possa acompanhar a execução de cada Sort separadamente. Veja ordenar primeiro pela coluna A e depois pela E. Em seguida, comente o segundo SortFields.Clear e execute de novo. Você verá que o segundo Sort aparentemente não fez nada. Por que isso aconteceu? Lembre-se que o SortFields.Add acrescenta uma chave na ordenação e que no primeiro Sort há uma chave existente para a coluna A. O segundo SortFields.Add adicionou a chave para a coluna E à ordenação já existente, portanto a prioridade na ordenação é a coluna A, não a E. É por isso que o segundo não funcionou como esperado e para evitar erros deste tipo que sempre é bom usar o SortFields.Clear como primeira linha de um bloco de ordenação.

Para ordenar usando as cores das células ou do texto da célula é um pouco diferente. Vejamos um código de exemplo gerado pelo gravador de macros:

    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add(Range("A1:A21"), _
        xlSortOnFontColor, xlAscending, , xlSortNormal).SortOnValue.Color = _
        RGB(255, 0, 0)
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add Key:=Range( _
        "A1:A21"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Planilha1").Sort
        .SetRange Range("A1:A21")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Esse código ordena primeiro as células que tenham texto em vermelho para depois ordenar em ordem crescente. Perceba que após o Add aparece as propriedades entre parênteses e depois um novo método, SortOnValue.Color. Mais uma vez, ele coloca parâmetros à toa no código. Na ordenação por cores, basta estabelecer o intervalo (Range) e o tipo de ordem (xlSortOnFontColor). O parâmetro xlAscending é o padrão, portanto pode ser removido sem problemas, a menos que queira colocar o vermelho no fim da tabela (neste caso deverá ser usado xlDescending). Já o parâmetro xlSortNormal é completamente inútil, suas opções não se aplicam a cores. Se for preciso acrescentar mais cores na ordenação, basta acrescentar linhas adicionais, uma para cada cor.

Perceba que há um segundo comando SortFields.Add que deixará as células em ordem crescente. O seu funcionamento é exatamente como foi descrito no começo do artigo. Da forma como está o código, as células com texto em vermelho ficarão no topo e depois serão ordenadas em ordem crescente de valores. Depois seguirão o restante das células ordenadas por valor, independentemente da cor. Se colocar essa linha antes da ordenação por cores, as células serão ordenadas primeiramente por valor, para então as células com texto vermelho ficarem acima das outras cores. A ordem das instruções importa muito no resultado da ordenação.

Para ordenar pela cor das células, basta trocar o parâmetro do tipo de ordenação de xlSortOnFontColor para xlSortOnCellColor. O funcionamento é exatamente o mesmo, só muda o foco da cor do texto para a cor do fundo.

Para ordenar células com ícones é um pouco diferente. Acrescentei o primeiro conjunto de ícones (via Formatação Condicional e Conjuntos de Ícones), depois usei o gravador de macros e obtive este código:

    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Clear
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add(Range("A2:A21"), _
        xlSortOnIcon, xlAscending, , xlSortNormal).SetIcon Icon:=ActiveWorkbook. _
        IconSets(1).Item(1)
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add(Range("A2:A21"), _
        xlSortOnIcon, xlAscending, , xlSortNormal).SetIcon Icon:=ActiveWorkbook. _
        IconSets(1).Item(2)
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add(Range("A2:A21"), _
        xlSortOnIcon, xlAscending, , xlSortNormal).SetIcon Icon:=ActiveWorkbook. _
        IconSets(1).Item(3)
    ActiveWorkbook.Worksheets("Planilha1").Sort.SortFields.Add Key:=Range( _
        "A2:A21"), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
        xlSortNormal
    With ActiveWorkbook.Worksheets("Planilha1").Sort
        .SetRange Range("A1:A21")
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

Os parâmetros do SortFields.Add também são apresentados entre parênteses, mas aqui usa um outro método: SetIcon. Este método usa o parâmetro Icon para especificar qual é o ícone a ser usado na ordenação. O resultado deste código é colocar as setas vermelhas no topo, seguidas pelas amarelas e por último pelas verdes, para depois ordenar em ordem crescente.

Perceba que o resultado deste código é exatamente o mesmo que se fizesse uma ordenação por ordem crescente, pois os ícones são colocados de acordo com os valores das células. Portanto, a ordenação por ícones só precisa ser usada quando for necessário destacar algum grupo específico. Além disso, o conjunto de ícones usados no exemplo foi o primeiro e isso se reflete no código em IconSets(1). Para valores de outros conjuntos de ícones, recomendo que use o gravador de macros para obter os parâmetros para o conjunto que será usado.

Espero que com este artigo você consiga entender o funcionamento do Sort e fazer ordenações mais adequadas às suas necessidades, sem a poluição de código que o gerador de macros produz. Para quaisquer dúvidas, comentários e sugestões, use a seção de comentários.

Pedro Martins



Formado em Tecnologia em Eletrônica Digital, já trabalhou como artefinalista, eletrotécnico, programador de CLP (para máquinas industriais) e analista de sistemas em sistema bancário, programando em COBOL.
Mexe com computadores e programação desde a segunda metade dos anos 1980, quando teve um MSX e aprendeu a programar em BASIC. É a favor da disseminação do conhecimento.



Autor Pedro Martins

Esta é uma breve descrição no bloco de autor sobre o autor. Você edita-lo, no html
«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

5 comentários

  1. Olá Pedro, como vai? No início desta matéria você diz que uma das desvantagens de gravar uma macro é que ela mantém fixo o intervalo a ser ordenado. Ainda sou iniciante no assunto e preciso de um esclarecimento, no exemplo que você deu essa questão não foi tratada, correto? O intervalo continuou sendo A2:E21. Se num outro momento meu intervalo de dados for de A2:E73 por exemplo, como faço para o código entender isso de forma automática?
    Obrigado.
    José Roberto

    ResponderExcluir
    Respostas
    1. Olá, José Roberto. Sim, é verdade que não coloquei a melhor solução para o caso.

      Existem duas formas adequadas de ter um intervalo dinâmico. A primeira é usar o gerenciador de nomes e definindo um intervalo flexível com a função DESLOC, como no exemplo abaixo:

      =DESLOC(NomeDaPlanilha!$A$1;0;0;CONT.VALORES(NomeDaPlanilha!$A:$A);5)

      Essa fórmula cria um intervalo a partir da célula A1 até a quinta coluna (último parâmetro é 5) e vai até a última linha encontrada na coluna A (através da função CONT.VALORES).

      A partir daí você pode usar o intervalo no código com o nome que você usou, como por exemplo Range("Teste").

      Outra forma é usar o método End do Range e do Cells para obter a última linha do intervalo desejado. Segue um exemplo:

      Set Intervalo = Range(Cells(1, 5), Cells(1, 1).End(xlDown))

      Esse código irá definir a variável Intervalo como o intervalo entre a célula E1, através do Cells(1, 5), até o último valor encontrado na coluna A, através do Cells(1, 1).End(xlDown).

      Não sei se você viu o canal do YouTube do Alessandro Trovato, em algumas aulas ele usa esses métodos para ter intervalos dinâmicos. Segue o link, as aulas serão de grande ajuda:
      https://www.youtube.com/playlist?list=PL7iAT8C5wumpfsfVzZRRVfpTS17d6sykT

      Para mais informações sobre os objetos Range e Cells, sugiro uma lida no meu quarto artigo:
      http://www.sigaonerd.com/2016/03/vba-artigo-004-o-objeto-range-e-o.html

      Espero que ajude a esclarecer sua dúvida. Abraço!

      Excluir
    2. Excelente explicação Pedro como sempre!
      José Roberto, acompanhe os artigos do Pedro no blog do SigaONerd. Há explicações que normalmente não encontramos na literatura nacional. Ele fala com grande propriedade sobre os temas.
      Quanto as faixas dinâmicas elas podem ser definidas conforme a necessidade e o projeto. Sempre que o usuário tiver que trabalhar com faixas dinâmicas eu as utilizo, se não é o caso e as listas são para uso de forma constante e sem alterações, não me preocupo em dinâmiza-las. Ambos os recursos são ótimos e dão liberdade a você como desenvolvedor pois diminuem sensivelmente o suporte posterior ao desenvolvimento!
      Grato por acompanhar os trabalhos!

      Excluir
  2. Ao usar Header como xlNo, realmente o cabeçalho irá para a última linha.
    Fechei o arquivo e não salvei.
    Usei novamente o código sem a opção Header, e o Excel novamente executou como se eu tivesse feito a escolha sem cabeçalho.
    Portanto, acho importante manter a opção Header.
    Acredito que o mesmo deva acontecer com as outras opções dentro do segundo bloco Whit, e acho importante então manter estas opções.
    Quanto ao primeiro bloco Whit, classifiquei por ordem Descendente.
    Depois apliquei novamente o código sem a opção de ordem, e o Excel entendeu como ordem Ascendente.
    Me parece que algumas opções mantém a última escolha, como acontece com o método FIND.

    ResponderExcluir
    Respostas
    1. Olá, Paulo. Nos exemplos usei o intervalo "A2:E21", ou seja, sem o cabeçalho, por isso que não usei a propriedade Header.
      Se seu intervalo contiver o cabeçalho, é essencial colocar essa propriedade com o valor xlYes. O xlGuess tenta "adivinhar", mas normalmente não dá certo. Inclusive tem a explicação dos parâmetros no texto, lá na explicação da propriedade Header.

      Excluir