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.
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?
ResponderExcluirObrigado.
José Roberto
Olá, José Roberto. Sim, é verdade que não coloquei a melhor solução para o caso.
ExcluirExistem 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!
Excelente explicação Pedro como sempre!
ExcluirJosé 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!
Ao usar Header como xlNo, realmente o cabeçalho irá para a última linha.
ResponderExcluirFechei 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.
Olá, Paulo. Nos exemplos usei o intervalo "A2:E21", ou seja, sem o cabeçalho, por isso que não usei a propriedade Header.
ExcluirSe 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.
Excelente material. Preciso e muito bem detalhado. Parabéns!!
ResponderExcluirPedro, legal demais o texto. Ótima explicação e um aprofundamento muito bom no tema. Consegui resolver o meu problema graças ao seu texto, muito obrigado por disponibilizar o conteúdo!
ResponderExcluirÓtimo material, bom detalhamento e exemplos claros. Obrigado !
ResponderExcluir