Follow 3stcom on Twitter
Visitas: 640.314

Uso de VBA no Excel - Exemplo 01

Neste exemplo é utilizada uma planilha excel com 5 colunas, sendo ID, Produto, Fornecedor, Quantidade e Valor e algumas linhas. O objetivo é criar Macros VBA que permitam classificar as colunas A ou B ou C. Após criação dessas colunas e digitação de alguns dados, os seguintes passos devem ser seguidos:

  • Abrir menu Ferramentas/Macros/Gravar nova macro ...
  • Preencher no campo Nome da macro o nome que será utilizado, sortGenerico.
  • Pressionar o botão OK para iniciar o processo de gravação.

                    http://www.3st.com.br

  • O Campo Tecla de atalho é onde definimos uma tecla, que em conjunto com a Ctrl, execute a query. Neste exemplo não vamos usar esse recurso.
  • Uma pequena barra de ferramentas terá sido, agora, adicionada ao conjunto de barras de ferramentas do excel.

                               

  • Posicionar o cursor no campo ID.
  • O próximo passo é fazer a classificação, utilizando o processo normal de classificação do excel.
  • Dados/Classificar - uma nova janela será aberta. Escolher a coluna Produto como objeto da classificação e Crescente como ordem de classificação.

                               

  • Pressionar o botão OK para efetivar a classificação.
  • Agora é preciso encerrar o processo de gravação da macro. Pressione o botão quadrado azul na barra de gravação de macro.

                              

  • O processo de gravação da macro está concluído.

Agora é preciso prover um meio de executar a macro, visto que não está sendo utilizada a tecla de atalho neste exemplo. Para tanto um retângulo (figura no excel) deve ser adicionado sobre as colunas ID, Produto e Fornecedor. Cada retângulo pode ser colorido ou algo escrito neles, indicando que servem para classificar a coluna correspondente.

  http://www.3st.com.br

 O próximo passo é abrir o editor de Visual Basic a fazer os necessários ajustes. Não queremos criar uma macro diferente para cada coluna a ser sorteada. Primeiro uma olhada no editor.

 http://www.3st.com.br

No lado esquerdo em cima, temos o seletor de componentes da planilha ou do projeto VB. O item marcado em azul é o Módulo1 que foi criado com a gravação automática da macro. Do lado direito temos o programa gerado e que queremos alterar.

 

Sub SortGenerico()
'
' SortGenerico Macro
' Macro gravada em 16/10/2009 por 3st
'
'
    Range('A10:E19').Sort Key1:=Range('B11'), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub 

 Note o termo 'Range('A10:E19')'. Este define que a classificação será sempre aplicada à área de seleção entre as Células A10 e E19. Mas isso é muito limitado. O termo '=Range('B11')' define a coluna e a linha a partir da qual a classificação será feita. Vamos transformar essas constantes em variáveis e criar meios de receber essas informações variáveis.

 O primeiro passo é definir como vamos passar as informações variáveis para a Macro. Pode-se fazer isso pela criação de Parâmetros de Chamada. Observe o código alterado abaixo:

 

Sub SortGenerico(ps_range as string, ps_inicio as string)
'
' SortGenerico Macro
' Macro gravada em 16/10/2009 por 3st
'
'
    Range(ps_range).Sort Key1:=Range(ps_inicio), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
End Sub 

Note que a declaração da Sub-rotina Sub foi alterado de SortGenerico() para SortGenerico(ps_range as string, ps_inicio as string). Nesta alteração foram definidas duas variáveis de chamada em formato caractere (string). A primeira define o valor variável para o Range('A10:A19'). A segunda define o valor para o ponto de partida Range('B11'). Note que agora aparecem no lugar das declarações constantes originais.

Pois bem, essa Sub-rotina, ou Classe, precisa ser chamada por outro código que forneça as informações corretas para a SortGenerico, o Range (área de seleção) e o Range (ponto de partida). Para tanto vamos criar uma Classe (Sub) para cada um dos botões que criamos acima das colunas da planilha. Vamos chamá-los Sub SortColA, Sub SortColB e Sub SortColC. Adicionalmente, vamos criar uma 4a. Classe que fará o trabalho comum para as três anteriores, ou seja, uma que calcule o range (área) a ser sorteada com base nos dados existentes. A esta chamaremos de Sub CalculaArea,

O código mostrado abaixo está comentado para indicar, passo a passo, o que está sendo feito e com que objetivo.

 

Function CalculaArea()
    '
    ' CalculaArea Macro
    ' Criada em 16/10/2009 por 3st
    ' Objetivo - Calcular ou definir a área a ser sorteada.
    ' Os critérios para definição da área será a coluna A
    ' primeira linha, até a coluna E, última linha.
    '
    ' Resultado - Fornece um valor alfa-numerico no formato
    ' Coluna-Linha-Inicio e Coluna-Linha-Final
    ' ex. A10:E19
    '
    ' Definição das variáveis a serem utilizadas
    ' DIM declara a variável nome como tipo de dado
    ' vi_indice será um número inteiro
    ' vs_area será uma string ou conjunto alfa-numerico
    '
    Dim vi_indice As Integer
    Dim vs_area As String
   
    ' Incializamos essas variáveis para evitar que fiquem nulas
    ' O índice inicializamos com o valor 1
    ' A área inicializamos com um conjunto vazio
    '
    vi_indice = 1
    vs_area = ''
    ' Para obter a área posicionamos o cursor na primeira linha
    ' e primeira coluna, no caso, A10
    ' A linha de cabeçalho deve ser incluída na área
    '
    Range('A10').Select
   
    ' Forçamos o cursor, agora na última linha com valor diferene
    ' de espaços ou '' usando um comando VBA de movimentação do
    ' cursor. xlDown força para baixo e xlUp força para cima
    '
    Selection.End(xlDown).Select
    ' Com o cursor posicionado na última linha com informação,
    ' podemos definir a posição de linha final.
    '
    vi_indice = Selection.Row
    vs_area = 'A10' + ':E' + Trim(Str(vi_indice))
    ' Trim - Elimina espaços
    ' Str  - Converte números para caracteres
    ' Assim obtemos o valor em vs_area 'A10:E19'
   
    ' Para retornar o valor calculado, atribuímos ao Nome da Função
    ' o valor calculado ou obtido
    CalculaArea = vs_area
 
    ' Voltamos o cursor para a posição original
    Range('A10').Select
   
End Function
 

Sub SortColA()
    '
    ' SortColA Macro
    ' Criada em 16/10/2009 por 3st
    ' Objetivo - Chamar a Classe SortGenerico para classificar
    ' a coluna A da planilha exemplo
    '
   
    ' Definição das variáveis que serão utilizada paras armazenar os valores
    ' exigidos pela Classe SortGenerico. As duas são definidas para conter
    ' caracteres alfa-numéricos string
    '
    Dim vs_inicio As String
    Dim vs_area As String
   
    ' Agora atribuimos os valores para essas variáveis.
    ' Primeiro a vs_inicio
    ' Como estamos nesta Classe classificando a coluna A
    ' atribuímos A11 que é a primeira linha com dado
    ' que será classificado na coluna A
    ' A linha de cabeçalho não é incluída como início
    '
    vs_inicio = 'A11'
   
    ' Para vs_area pedimos que a CalculaArea faça esse trabalho
    ' chamando-a conforme abaixo.
    '
    vs_area = CalculaArea()
    ' A área calculada pela CalculaArea será assim atribuido a vs_area
   
    ' Agora basta executar a Classe SortGenerico, fornecendo os valores
    ' necessários
    ' Para chamarmos uma Classe (não Função), basta escrever seu nome
    ' e fornecer à frente as variáveis esperadas
    '
    SortGenerico vs_area, vs_inicio
   
End Sub
 

Sub SortColB()
    '
    ' SortColB Macro
    ' Criada em 16/10/2009 por 3st
    ' Objetivo - Chamar a Classe SortGenerico para classificar
    ' a coluna B da planilha exemplo
    '
   
    ' Definição das variáveis que serão utilizada paras armazenar os valores
    ' exigidos pela Classe SortGenerico. As duas são definidas para conter
    ' caracteres alfa-numéricos string
    '
    Dim vs_inicio As String
    Dim vs_area As String
   
    ' Agora atribuimos os valores para essas variáveis.
    ' Primeiro a vs_inicio
    ' Como estamos nesta Classe classificando a coluna A
    ' atribuímos B11 que é a primeira linha com dado
    ' que será classificado na coluna B
    ' A linha de cabeçalho não é incluída como início
    '
    vs_inicio = 'B11'
   
    ' Para vs_area pedimos que a CalculaArea faça esse trabalho
    ' chamando-a conforme abaixo.
    '
    vs_area = CalculaArea()
    ' A área calculada pela CalculaArea será assim atribuido a vs_area
   
    ' Agora basta executar a Classe SortGenerico, fornecendo os valores
    ' necessários
    ' Para chamarmos uma Classe (não Função), basta escrever seu nome
    ' e fornecer à frente as variáveis esperadas
    '
    SortGenerico vs_area, vs_inicio
 
End Sub
 
Sub SortColC()
    '
    ' SortColC Macro
    ' Criada em 16/10/2009 por 3st
    ' Objetivo - Chamar a Classe SortGenerico para classificar
    ' a coluna C da planilha exemplo
    '
   
    ' Definição das variáveis que serão utilizada paras armazenar os valores
    ' exigidos pela Classe SortGenerico. As duas são definidas para conter
    ' caracteres alfa-numéricos string
    '
    Dim vs_inicio As String
    Dim vs_area As String
   
    ' Agora atribuimos os valores para essas variáveis.
    ' Primeiro a vs_inicio
    ' Como estamos nesta Classe classificando a coluna A
    ' atribuímos C11 que é a primeira linha com dado
    ' que será classificado na coluna B
    ' A linha de cabeçalho não é incluída como início
    '
    vs_inicio = 'C11'
   
    ' Para vs_area pedimos que a CalculaArea faça esse trabalho
    ' chamando-a conforme abaixo.
    '
    vs_area = CalculaArea()
    ' A área calculada pela CalculaArea será assim atribuido a vs_area
   
    ' Agora basta executar a Classe SortGenerico, fornecendo os valores
    ' necessários
    ' Para chamarmos uma Classe (não Função), basta escrever seu nome
    ' e fornecer à frente as variáveis esperadas
    '
    SortGenerico vs_area, vs_inicio
 
End Sub
 
Sub SortGenerico(ps_area As String, ps_inicio As String)
    '
    ' SortGenerico Macro
    ' Macro gravada em 16/10/2009 por 3st
    '
    ' Objetivo - Classificar os dados na área fornecida por ps_area, a
    ' partir do início ps_inicio
    '
   
    Range(ps_area).Sort Key1:=Range(ps_inicio), Order1:=xlAscending, Header:= _
        xlGuess, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom, _
        DataOption1:=xlSortNormal
       
    ' O comando acima tem as seguintes características e informações
    ' Range(ps_area).sort       - Instrui para classificar a área
    ' Key1:=Range(ps_inicio)    - Define o ponto de partida
    ' Order1:=xlAscending       - Classifica de modo Ascendente
    ' Header:= xlGuess          - Indica que existe uma linha de cabeçalho
    ' OrdemCustom:=1            - Da direita para esquerda (2 para o contrário
    ' MatchCase:=False          - Não serão diferenciadas maiúsculas de minúsculas
    ' Orientation:=xlTopBottom  - De cima para baixo
    ' DataOption1:=xlSortNormal - Utiliza um tipo básico de sort

   
End Sub 

 

O código para executar a classificação das colunas está pronto e listado acima. Agora vamos atribuir aos botões de classificação que criamos acima das colunas, as respectivas Macros, para que quando clicados, executem a classificação da coluna.

 http://www.3st.com.br

 

Com o botão selecionado, veja acima, usando o botão direito do mouse, escolhemos do menu popup a opção Atribuir Macro.

                   

É apresentada uma janela com a lista das Classes que criamos, SortColA, SortColB e SortColC. Para a coluna A devemos selecionar SortColA. Depois repetimos os mesmos passos para os botões das colunas B e C.

Está pronto. Ao clicarmos sobre cada botão os dados serão classificados.

Experimente criar linhas novas e excluir linhas. Observe que a classificação continuará funcionando. Lembre-se que não podem haver linhas em branco separando os dados. Se isto acontecer, os dados abaixo da linha em branco não serão incluídos na classificação.

O download do arquivo Excel com o exemplo deste artigo pode ser baixado com o botão abaixo.

                  http://www.3st.com.br

No próximo artigo abordaremos o tópico Depuração de Programas VBA (Debug).

 

Copyright © 2015 3ST Serviços em Tecnologia Última Atualização: 20/12/2015 Visitas à Página: 22.503
Skip Navigation Links