VBA - Artigo 015 - Compreendendo o Sort

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

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.

POWER BI para não desenvolvedores! 

Aprenda a interpretar os painéis e relatórios do Power BI mesmo não sendo da área técnica de desenvolvimento.

Um curso do amigo Joviano Silveira que recomendo!

   

-------------------------------------------------------