Se você tem alguma dica, truque ou macete e gostaria de compartilhar conosco, utilize essa seção.
Avatar do usuário
Por Jimmy 11 Jan 2019 às 01:57
Membro 5 Estrelas
Mensagens: 1160
Reputação: 772
#40015
Olá,

Outro dia, respondendo a uma dúvida de um colega, me deparei com uma planilha com a seguinte macro no evento CHANGE:

Código: Selecionar todosPrivate Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, [B6:B12,B15:B17,R4:R14]) Is Nothing Then Exit Sub
    On Error Resume Next
    Target.Offset(, 1).Value = Target.Offset(, 1).Value + Target.Value
    Application.EnableEvents = False
    Target.Value = ""
    Application.EnableEvents = True
End Sub

Todo o valor que for digitado na Range em questão (B6:B12,B15:B17,R4:R14), é acumulado na célula ao lado (da coluna C), e logo em seguida é apagado.
São apenas 6 linhas mas muito para comentar.

Passei algumas dicas para o colega, que reproduzo aqui para acesso de mais pessoas. Embora estas dicas estejam baseadas no estudo do caso acima, podem ser aproveitadas para outros eventos como SELECTION CHANGE, ACTIVATE, etc.

1) Ver linha 2 da macro: If Intersect(Target...
Ao colocar uma SUB em um evento CHANGE, por exemplo, deve-se pensar que aquela SUB deve abrigar necessidades de eventos CHANGE de outras partes da planilha, e que podem nem ter relação entre si. Se a rotina de um determinado evento encerra a execução com um EXIT SUB, eventuais rotinas que venham a seguir, que fazem outras coisas, deixarão de funcionar. Seria bom se habituar a fazer com que a rotina seja executada dentro de um IF, se for o caso. Se não for, o IF não executa a rotina, mas também não a encerra, podendo haver diversos outros IFs abaixo, responsáveis por outras demandas.

2) Ver linhas 4 e 6 da macro: Target.Offset(, 1).Value =... / Target.Value = ""
Deve-se ter em mente que TARGET é um RANGE que geralmente tem uma só célula, mas pode ter mais. Imagine que o usuário digite o valor 3 na célula F3, fora da área monitorada pelo evento CHANGE acima. Como não pertence à faixa de atuação da SUB, nada ocorre. Em seguida ele tecla CTRL-C (copiar), seleciona B8:B12 e CTRL-V (colar). O evento CHANGE é acionado, e TARGET será um RANGE que contém 5 células, mas da forma que a macro foi feita (acima) não processará as 5 células. O melhor, geralmente, é processar todas elas, usando o EACH CEL IN RANGE.

3) Ver linhas 5 e 7 da macro: Application.EnableEvents =
Muitas vezes a macro do CHANGE tem a necessidade de desativar os eventos temporariamente, para alterar uma célula que ele próprio está monitorando. Sem desativar os eventos há possibilidade de loop infinito. Para SUBs que rodam muito pouco, digamos algumas centenas de vezes por dia, e com pouca carga de processamento (é o caso da macro acima) eu nem desativaria os eventos. Deixaria rodar. Isso gasta um pouco mais de processamento, mas é tão pouco que não vale a pena desativar. Em vez de desativar, eu evitaria o possível loop infinito da macro acima, quando a célula é apagada (cel.Value = "") incluindo mais um IF. Esse IF só permitiria que houvesse processamento se houver valor maior que zero em CEL.VALUE. Assim, quando CEL é apagada, o evento CHANGE é iniciado, mas não faz nada. Morre logo ali.
Mas qual a vantagem disso? Macros sempre poder dar pau, abortar, ou até serem canceladas pelo usuário. Se os eventos são desativamos, e a macro, por azar, é interrompida antes de reativá-los, o usuário ficará sem eventos. Geralmente, por não saber outra forma de fazer, e após perceber que há algo errado, o usuário sairá da planilha e a abrirá novamente. Isso pode dar a ele a impressão de pouca robustez no sistema.

Abaixo eu coloco minha versão da macro colocada acima como exemplo, já sem a sua intervenção nos eventos:
Código: Selecionar todosPrivate Sub Worksheet_Change(ByVal Target As Range)
    Set Onde = Intersect(Target, [B6:B12,B15:B17,R4:R14])
    If Not Onde Is Nothing Then
        For Each Cel In Onde
            If cel.Value > 0 Then
                On Error Resume Next
                cel.Offset(, 1).Value = cel.Offset(, 1).Value + cel.Value
                On Error Goto 0
                cel.Value = ""
            End If
        Next
    End If
    Set Onde = Nothing
End Sub


Finalizando, eu particularmente evito ao máximo usar o comando On Error Resume Next (linha 3 da macro original). Na macro original acredito que tenha sido colocado para o caso de um caractere não numérico ser digitado no RANGE, e assim, dar erro na soma, mas se houver um erro real na macro (uma divisão por zero, por exemplo), nenhum aviso vai ser dado. Na macro em questão, se foi usado para exitar erro por caractere não numérico, o melhor é, ao invés de usar ON ERROR RESUME NEXT, testar se o conteúdo de CEL é ou não numérico antes da soma ser feita. Outro detalhe é que, caso seja realmente necessário usar esse desvio de erro, tentar usá-lo apenas de forma localizada, o seja, desativar o RESUME NEXT após passado o perigo usando o On error goto 0.

Se esta mensagem te ajudou de alguma forma, peço que dê um Like clicando no botão com o positivo, acima e a direita.

Jimmy San Juan