Select Menu



por Alessandro Trovato

«
Proxima
Postagem mais recente
»
Anterior
Postagem mais antiga

Progredindo em VBA no Microsoft Excel

Combinando arrays com objetos type

Em artigos anteriores foi visto como usar arrays e objetos type. É possível combiná-los de forma que você possa trabalhar com um conjunto de objetos type de uma vez, sem precisar trabalhar com cada um individualmente. Desta forma, seu código pode ficar mais eficiente e rápido.

Suponha que tenha uma planilha onde você anota os históricos dos jogos do seu time, onde é anotado o nome do jogador, quantidade de gols marcados, cartões, substituições etc. Os dados entram por um formulário e depois são armazenados numa planilha. Para não ter que fazer um formulário com dezenas de componentes e ter muito trabalho para enviar cada um deles para uma variável específica, o ideal é criar componentes para apenas um jogador junto com botão de rotação para passar de um para outro e uma lista para apresentar o status atual, como na imagem abaixo:


Imagine como seria o formulário se tivéssemos componentes para cada jogador? Lembrando que uma súmula deve guardar dados de todos os reservas (há jogador que consegue levar cartão sem entrar em campo), dependendo da competição isso pode levar a mais de 20 jogadores por súmula.

O objeto type é a melhor forma de armazenar o conjunto de dados do jogador, pois é uma estrutura de dados em que eles não precisam ser do mesmo tipo. Vejamos como esse objeto deve ser criado:

Type tpJogador
    Numero      As Byte
    Nome        As String
    Titular     As Boolean
    Gols        As Byte
    Saida       As String
    Entrada     As String
    Amarelo     As Boolean
    Vermelho    As Boolean
End Type

Só para lembrar, byte é um formato de número que armazena de 0 a 255, por isso é utilizado aqui. Os tempos de entrada e saída estão como strings porque, dependendo da convenção usada, podemos escrever "46 do 2" ou "90+1". Gols poderia ser um array com os tempos de marcação, mas coloquei aqui apenas a quantidade para simplificar o exemplo.

Esta estrutura tpJogador comporta bem os dados de um jogador do formulário, mas como fazer para comportar todos os jogadores? Criamos um array com o formato do objeto type:

Private Jogadores(1 To 20) As tpJogador

Perceba que essa estrutura deve ser criada na área de declaração do formulário (observe o Private), ficando desta forma disponível para todas as sub-rotinas e eventos deste módulo.

O objeto que irá controlar a posição do jogador que está sendo editado no momento é o botão de rotação (ou spin button, em inglês). Devemos lembrar de alterar seus valores mínimo e máximo para 1 e 20, respectivamente, de forma que ele limitará o intervalo. Assim, não precisamos nos preocupar se o valor do array será inválido, pois ambos possuem os mesmos intervalos.

O evento de alteração do botão de rotação a princípio apenas alterará o rótulo de indicador de posição:

Private Sub spnJogador_Change()
    lblIndicador.Caption = spnJogador.Value & " de " & spnJogador.Max
End Sub

Desta forma, já está visível qual a posição qual o índice do jogador que está sendo editado no momento. Porém, usamos este controle, o valor anterior é perdido, ficando apenas o valor atual. Desta forma, não temos como usar esse botão para gravar os dados do jogador editado anteriormente. Poderíamos usar os eventos SpinDown e SpinUp e gravar os valores no índice posterior ou anterior, respectivamente, mas teríamos problemas quando estivéssemos no limite e pressionar de novo o botão, pois replicaria os dados atuais e apagaria os anteriores.

A melhor solução para este caso é usar os próprios eventos de saída dos controles para validar e alterar o valor correspondente:

Private Sub txtNumero_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsNumeric(txtNumero.Value) And txtNumero.Value > 0 And txtNumero.Value < 256 Then
        Jogadores(spnJogador.Value).Numero = txtNumero.Value
    Else
        MsgBox "Número " & txtNumero.Value & " inválido"
        txtNumero.Value = Jogadores(spnJogador.Value).Numero
    End If
End Sub

Este evento verifica se o valor recebido é numérico e se está no intervalo permitido para o tipo byte (até 255). Também não permite o zero, pois ninguém entra em campo com esse número. Caso o valor seja inválido, exibirá uma caixa de mensagem informativa e põe o valor anterior de volta (zero caso não tenha tido um valor adicionado anteriormente).

Perceba que você irá precisar de tratar todos os eventos de saída dos componentes, isto é, para cada componente é necessário que o seu evento de saída atualize (e valide antes, se for o caso) o campo correspondente.

Para o campo Nome do jogador não há muita dificuldade, a não ser caso precise validá-lo em uma base de jogadores. Como é um exemplo, vou deixar da forma mais simples:

Private Sub cmbNome_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Jogadores(spnJogador.Value).Nome = cmbNome.Value
End Sub

Já o campo Titularidade é simples. Se o jogador for titular, o campo Titular correspondente será verdadeiro, caso contrário será falso. Como este controle está em um conjunto de opções, ou seja, ou é Titular ou é Reserva (não há outra combinação), podemos simplesmente igualar à opção de titular:

Private Sub frmTitular_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Jogadores(spnJogador.Value).Titular = optTitular.Value
End Sub

Poderia ser utilizado uma caixa de seleção ao invés dos botões de opção, mas há casos em que o usuário requer assim para ficar visualmente prático de entender.

Para o campo Gols, a ideia é a mesma do campo Número, com a diferença de permitir o zero:

Private Sub txtGols_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    If IsNumeric(txtGols.Value) And txtGols.Value >= 0 And txtGols.Value < 256 Then
        Jogadores(spnJogador.Value).Gols = txtGols.Value
    Else
        MsgBox "Quantidade de gols " & txtGols.Value & " inválida"
        txtGols.Value = Jogadores(spnJogador.Value).Numero
    End If
End Sub

Os campos Saída e Entrada são similares. Podemos validar se o valor recebido está de acordo com a convenção definida, mas aqui vamos apenas transferir o valor para o campo para não estender muito o artigo.

Private Sub txtSaida_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Jogadores(spnJogador.Value).Saida = txtSaida.Value
End Sub
Private Sub txtEntrada_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Jogadores(spnJogador.Value).Entrada = txtEntrada.Value
End Sub

Já os campos Amarelo e Vermelho são bem simples. O valor do campo corresponde ao valor da caixa de seleção:

Private Sub chkAmarelo_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Jogadores(spnJogador.Value).Amarelo = chkAmarelo.Value
End Sub
Private Sub chkVermelho_Exit(ByVal Cancel As MSForms.ReturnBoolean)
    Jogadores(spnJogador.Value).Vermelho = chkVermelho.Value
End Sub

Até aqui estamos guardando os dados de um jogador nos campos do índice correspondente ao botão de rotação. Porém, quando pressionamos esse botão, os valores armazenados não estão sendo carregados para o formulário. Precisamos então criar uma sub-rotina que carregue os dados do jogador para o formulário e depois editamos o evento spnJogador_Change para chamar essa sub-rotina:

Sub CarregarJogador()
    txtNumero.Value = Jogadores(spnJogador.Value).Numero
    cmbNome.Value = Jogadores(spnJogador.Value).Nome
    optTitular.Value = Jogadores(spnJogador.Value).Titular
    optReserva.Value = Not (Jogadores(spnJogador.Value).Titular)
    txtGols.Value = Jogadores(spnJogador.Value).Gols
    txtSaida.Value = Jogadores(spnJogador.Value).Saida
    txtEntrada.Value = Jogadores(spnJogador.Value).Entrada
    chkAmarelo.Value = Jogadores(spnJogador.Value).Amarelo
    chkVermelho.Value = Jogadores(spnJogador.Value).Vermelho
End Sub

Private Sub spnJogador_Change()
    lblIndicador.Caption = spnJogador.Value & " de " & spnJogador.Max
    CarregarJogador
End Sub

Vamos agora à inicialização do formulário. Da forma que está, nenhum valor é colocado e clicar em qualquer lugar sem colocar um valor em Número irá gerar a mensagem de alerta. Podemos facilitar também e colocar por padrão os valores iniciais dos números de 1 a 20 e definir os 11 primeiros como titulares, a fim de economizar tempo. Por fim, chamamos a sub-rotina CarregarJogador para trazer os dados do índice 1.

Há detalhes que devem ser considerados. Será que um dia vamos precisar alterar a quantidade de jogadores de 20 para 25, 18 ou outro valor qualquer? Neste caso não é uma boa ideia colocarmos valores fixos no código. Vamos inicializar os valores mínimo e máximo do botão de rotação, associando-os com os valores do array criado. Desta forma, apenas alterando a linha com a definição do array alteraremos também os valores do botão de rotação e não precisaremos alterar valores em vários pontos do código. Veja o código:

Private Sub UserForm_Initialize()
    Dim Iteracao As Byte
    spnJogador.Min = LBound(Jogadores)
    spnJogador.Max = UBound(Jogadores)
    lblIndicador.Caption = spnJogador.Value & " de " & spnJogador.Max
    For Iteracao = spnJogador.Min To spnJogador.Max
        Jogadores(Iteracao).Numero = Iteracao
        If Iteracao <= 11 Then
            Jogadores(Iteracao).Titular = True
        End If
    Next
    CarregarJogador
End Sub

Os valores mínimo e máximo do botão de rotação foram associados a LBound(Jogadores) e UBound(Jogadores), respectivamente, para que tenhamos os mesmos limites definidos no array. Em seguida, a estrutura de repetição utiliza esses valores mínimo e máximo para inserir os números iniciais e definir os 11 titulares. Por fim, o jogador da primeira ocorrência é carregado para o formulário.

Perceba que é uma técnica relativamente simples que evita ter de caçar valores definidos de forma fixa no código quando precisar fazer uma alteração. Se você precisar alterar a quantidade de jogadores para 25, basta editar a linha com a definição do array:

Private Jogadores(1 To 25) As tpJogador

Ao executar o formulário novamente, o funcionamento se mantém íntegro e perfeito, o que não seria possível se colocássemos valores fixos na estrutura de repetição e se não definíssemos os valores do botão de rotação na inicialização.

Por fim, para a parte do formulário ficar pronto, falta atualizar a caixa de listagem com a lista dos jogadores. Deve-se lembrar que aqui não iremos usar a propriedade RowSource para definir os dados, já que não estão disponíveis em uma planilha. A abordagem neste caso será diferente: usaremos o método AddItem para adicionar cada jogador à caixa de listagem. Também não devemos esquecer que este objeto começa com índice zero. Nesse índice será colocado o cabeçalho, para ajudar na visualização dos dados da lista. O objeto lbxLista foi definido com a propriedade ColumnCount valendo 8, que é o número de colunas que teremos.

Deve-se atentar que o AddItem só adiciona um elemento. Para preencher as outras colunas é necessário usar a propriedade List em conjunto com o índice da coluna para poder armazenar o valor correto. List é uma matriz com duas dimensões, sendo que a primeira é a linha e a segunda é a coluna, como é natural no Excel. Sabendo desses detalhes, fica mais fácil compreender a sub-rotina:

Sub PreencherLista()
    lbxLista.Clear
    lbxLista.ColumnHeads = False
    lbxLista.ColumnCount = 8
    lbxLista.AddItem "Num"
    lbxLista.List(0, 1) = "Nome"
    lbxLista.List(0, 2) = "T/R"
    lbxLista.List(0, 3) = "Gols"
    lbxLista.List(0, 4) = "Saída"
    lbxLista.List(0, 5) = "Entrada"
    lbxLista.List(0, 6) = "A"
    lbxLista.List(0, 7) = "V"
    Dim Iteracao As Byte
    For Iteracao = spnJogador.Min To spnJogador.Max
        lbxLista.AddItem Jogadores(Iteracao).Numero
        lbxLista.List(Iteracao, 1) = Jogadores(Iteracao).Nome
        lbxLista.List(Iteracao, 2) = IIf(Jogadores(Iteracao).Titular = True, "T", "R")
        lbxLista.List(Iteracao, 3) = Jogadores(Iteracao).Gols
        lbxLista.List(Iteracao, 4) = Jogadores(Iteracao).Saida
        lbxLista.List(Iteracao, 5) = Jogadores(Iteracao).Entrada
        lbxLista.List(Iteracao, 6) = IIf(Jogadores(Iteracao).Amarelo = True, "S", "N")
        lbxLista.List(Iteracao, 7) = IIf(Jogadores(Iteracao).Vermelho = True, "S", "N")
    Next
End Sub

Para funcionar, é preciso colocar uma chamada dessa sub-rotina no final de UserForm_Initialize. Assim, ao carregar o formulário, os valores padrões são definidos e os dados também são carregados na lista, conforme pode-se observar na imagem abaixo:


Falta agora atualizar a lista a cada edição de campo, para que o valor adicionado seja imediatamente visível na lista. Poderíamos chamar PreencherLista ao final de cada evento de saída de campo, mas não é prático, pois ele limparia e criaria uma lista nova do zero toda vez, o que não é eficiente. Basta apenas alterar a linha que está sendo editada no momento:

Sub AtualizarLista(Indice As Byte)
        lbxLista.List(Indice, 0) = Jogadores(Iteracao).Numero
        lbxLista.List(Indice, 1) = Jogadores(Iteracao).Nome
        lbxLista.List(Indice, 2) = IIf(Jogadores(Iteracao).Titular = True, "T", "R")
        lbxLista.List(Indice, 3) = Jogadores(Iteracao).Gols
        lbxLista.List(Indice, 4) = Jogadores(Iteracao).Saida
        lbxLista.List(Indice, 5) = Jogadores(Iteracao).Entrada
        lbxLista.List(Indice, 6) = IIf(Jogadores(Iteracao).Amarelo = True, "S", "N")
        lbxLista.List(Indice, 7) = IIf(Jogadores(Iteracao).Vermelho = True, "S", "N")
End Sub

Lembre-se que a caixa de listagem está com 21 linhas, do índice 0 ao 20, sendo o 0 preenchido pelo cabeçalho. O botão de rotação vai de 1 a 20, preenchendo perfeitamente os outros valores. Caso não tivéssemos o cabeçalho, seria necessário colocar Indice - 1 no código para usar os índices de 0 a 19.

Veja que a sub-rotina tem o parâmetro Indice. Esse é o valor que deve ser enviado para que a sub-rotina saiba em qual linha deve ser feita a alteração. Quem controla a posição no nosso formulário é o botão de rotação, portanto a chamada da sub-rotina deve ser feita da seguinte forma:

AtualizarLista spnJogador.Value

Coloque essa linha ao final de cada um dos oito eventos de saída de campo e o formulário está quase pronto. Falta apenas o evento de clique na caixa de listagem para que o jogador clicado passe a ser o jogador editado. Devemos lembrar que o índice da lista começa com zero e que essa linha é o cabeçalho, portanto ela não pode ser selecionada:

Private Sub lbxLista_Click()
    If lbxLista.ListIndex > 0 Then
        spnJogador.Value = lbxLista.ListIndex
    End If
End Sub

Assim que um jogador for clicado, o valor do botão de rotação passa a ser o índice do jogador selecionado. Essa mudança de valor dispara o evento spnJogador_Change, que por sua vez executa a sub-rotina CarregarJogador, que atualizará os valores dos campos no formulário.

Para terminar, falta fazer que o jogador que esteja sendo editado no momento seja selecionado na caixa de listagem. Isso é feito com a seguinte linha:

lbxLista.Selected(spnJogador.Value) = True

Essa linha deve ser colocada em dois pontos: no evento spnJogador_Change, para que assim que clicar no botão de rotação selecionar o próximo jogador, e na sub-rotina UserForm_Initialize, para que a primeira linha seja selecionada quando o formulário for carregado.

O artigo acabou ficando um tanto extenso e acabou ficando com alguns bônus além da ideia inicial. Você agora deve saber o poder de combinar arrays com objetos type. Certamente deve imaginar como seria muito trabalhoso ter um formulário com todos os campos para todos os jogadores. Essa combinação poupa o desenvolvedor de um trabalho braçal desnecessário e deixa o código menos sujeito a erros. Você também deve ter aprendido a usar o objeto ListBox sem usar um conjunto de células como origem (através do RowSource). Por fim, quem não conhece o comando Iif certamente deve ter visto no código. Como é similar à fórmula Se do Excel, nenhuma explicação foi dada.

Espero que este artigo seja de grande valia para seus projetos e que algumas das ideias sejam levadas adiante para deixar seus códigos mais eficientes.

Para dúvidas sobre o artigo, comentários ou sugestões, utilize os comentários abaixo. Até o próximo artigo!

Pedro Martins


Pós-graduando em Business Intelligence e Big Data pela Faculdade Impacta de Tecnologia. Formado em Tecnologia em Eletrônica Digital com Ênfase em Microprocessadores

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

3 comentários

  1. Parabéns Pedro!!! Ótima ideia e ótimo artigo!

    ResponderExcluir
  2. Parabéns Pedro! Você escreve com maestria! Já te falei que essa série de artigos dá um livro! Abraços.

    ResponderExcluir
    Respostas
    1. Já tenho uma ideia de fazer um livro. Mas seria para depois que terminar a pós-graduação.

      Excluir