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