Blog

Postado em em 10 de julho de 2020

Controlar Alterações no Excel com VBA

Você já precisou controlar alterações no Excel para saber quem foi a última pessoa a mexer na planilha? Nesse post vou te ensinar a fazer isso!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Clique aqui para baixar a planilha utilizada nessa publicação!

O que é um controle de alterações na planilha?

Controlar alterações no Excel é uma maneira se obter o registro de quem mexeu na planilha e quando as modificações foram salvas por cada pessoa.

Quando controlar alterações em sua planilha?

Este recurso é muito útil em planilhas que são utilizadas por muitas pessoas dentro da empresa, à fim de que nenhuma alteração comprometa o funcionamento da planilha para os demais usuários, dado que todas as alterações terão o registro de quem alterou e quando isto aconteceu.

Como controlar alterações no Excel?

Primeiramente, devemos criar uma aba na planilha onde será feito o registro de cada alteração. No nosso exemplo, chamamos esta aba de “Auxiliar”.

Devemos então criar um código VBA que, sempre que alguém salvar a planilha:
1 – Abra a aba “Auxiliar”;
2 – Encontre a última linha preenchida desta aba;
3 – Acrescente o novo registro na linha seguinte a última preenchida, contendo o horário em que a planilha foi salva e o nome do usuário que a salvou (obtido a partir do nome contido nas configurações de usuário do computador).
Para criarmos o código, primeiro abrimos o VBA utilizando o atalho alt+F11 (ou alt+fn+F11, caso alt+F11 não funcione).

Dentro do VBA, selecionamos “Esta pasta de trabalho”, no canto esquerdo, para fazermos nossas logs de alteração.

Selecionar esta pasta de trabalho

Selecionar “Esta pasta de trabalho”

Como nosso código tem uma relação de causalidade na planilha (causa: alguém salvou a planilha; efeito: o código é executado), temos que preparar o VBA antes de começarmos a escrever o código. Para isso, vamos utilizar as duas caixas superiores do painel, alterando a caixa “(Geral)” para “Workbook” e “(Declaração)” para “Before Save”, fazendo com que a macro seja executada antes de a planilha ser salva.
Você deve então chegar na seguinte tela:

Workbook e Before Save

Workbook e Before Save

Após isso, um código inicial é inserido automaticamente. A seguinte parte do código pode ser apagada sem problemas:

Private Sub Workbook_Open()

End Sub

Vamos então ao nosso código.
A primeira coisa que nosso código precisa é descobrir em qual linha da tabela a macro irá preencher a informação. Precisamos então saber qual a última linha preenchida para que a macro preencha a próxima linha, acrescentando a informação mais atual.
Para isso, precisamos descobrir o número da linha onde as informações serão inseridas. Para isso, vamos criar uma variável em nossa macro. Aqui chamamos esta variável de linha. A variável linha precisa então conter os seguintes passos:
1 – Clicar na aba auxiliar;
2 – Chegar na célula A1 e utilizar o atalho CTRL+ Seta para Baixo, para chegar a última linha preenchida da planilha;
3 – Descer uma linha, para chegar a próxima linha vazia.

A variável então ficará assim:

linha = Worksheets(“Auxiliar”).Range(“a1”).End(xlDown).Row + 1

Sendo que:
Worksheets(“Auxiliar”): Seleciona a aba auxiliar;
Range(“a1”): Seleciona a célula A1 da planilha;
End(xlDown): End é como se fosse o CTRL no VBA, e xlDown é o corresponde à Seta para Baixo. Chegamos então à última célula preenchida;
Row + 1: Traz o número da linha e acrescenta 1 (pega a última célula preenchida e desce uma linha)

Feito isso, precisamos agora acrescentar o horário na primeira coluna e o usuário na segunda coluna.
Para acrescentar o horário, utilizamos a seguinte fórmula:

Sheets(“auxiliar”).Cells(linha, 1) = Now

Que insere na aba auxiliar (Sheets(“auxiliar”)), linha trazida pela variável “linha” e primeira coluna (Cells(linha, 1)), o horário que a planilha foi salva, utilizando a expressão Now, que obtém a hora do computador em que está sendo executada.

Para acrescentar o nome, utilizamos a seguinte log de usuários:

Sheets(“auxiliar”).Cells(linha, 2) = Application.UserName

Que insere na aba auxiliar (Sheets(“auxiliar”)), linha trazida pela variável “linha” e segunda coluna (Cells(linha, 2)), o nome do usuário utilizando o comando Application.UserName, que é o comando em VBA que capta esta informação do computador.

Nossa expressão final, portanto, ficará assim:

Private Sub Workbook_BeforeSave(ByVal SaveAsUI As Boolean, Cancel As Boolean)

linha = Worksheets(“auxiliar”).Range(“a1”).End(xlDown).Row + 1

Sheets(“auxiliar”).Cells(linha, 1) = Now
Sheets(“auxiliar”).Cells(linha, 2) = Application.UserName

End Sub

Log de modificações no vba

Log de modificações no vba

Podemos fechar o vba e salvar a planilha, que o primeiro registro será realizado.

Hashtag Treinamentos

A Hashtag Treinamentos conta com mais de 13.000 alunos treinados em seus cursos online e presencial, clique para saber mais!


Quer aprender mais sobre VBA com um Minicurso Gratuito?


Quer participar do maior evento de Power BI da América Latina de forma 100% Online e Gratuita? Inscreva-se abaixo!