EISnt Centro de Formação em Tecnologia

Vou mostrar como podemos automatizar o Microsoft Excel usando a linguagem VBA através da criação de um programa para realizar o registo de informações sobre clientes. A aplicação irá permitir a navegação pelos dados na folha, inserção, alteração, eliminação de dados na folha Excel usando um formulário de registo e também o envio de e-mail aos clientes com atraso no pagamento ou a um cliente específico.

Abaixo vemos a figura da aplicação sendo executada exibindo o formulário de cadastro IMAGEM

É uma aplicação simples que mostra aos iniciantes e interessados automatizar tarefas no

Microsoft Excel usando a linguagem VBA.

Os recursos usados no projeto foram:  Microsoft Excel;

 Microsoft OutLook;

 Conhecimentos básicos sobre folhas de cálculo , células, e Visual Basic;

Nossa primeira tarefa será criar a folha onde iremos armazenar as informações dos clientes. Sim, isso mesmo, vamos armazenar as informações no próprio Excel.

Primeiro clique com o botão direito sobre a folha Folha1 e seleccione a opção

Renomear do menu suspenso informando o nome Clientes;

A seguir na primeira linha da folha digite o cabeçalho referente aos dados que desejamos gerir e em cada coluna informe os dados conforme a folha apresentada na imagem abaixo:

Obs: Lembre-se de formatar a coluna código como Número, e as demais como Texto. Para formatar seleccione a coluna e clique com o botão direito seleccione a opção Formatar Células;

Até o momento criamos a nossa folha com os dados dos clientes agora vamos partir para a automação do registo das informações usando VBA.

Para começar vamos incluir dois botões de comando na folha da seguinte forma: 1. No menu clique no separador Programador;

2. A seguir clique na opção Inserir para abrir a caixa de controlos disponíveis; 3. Clique então no controle Botão (Button) e a seguir clique na área vazia a direita

da folha conforme mostra a figura para incluir o primeiro Botão; 4. Repita a operação para incluir o segundo botão;

Quando incluir um botão na folha será aberta a janela para atribuir macro conforme mostra a figura a seguir:

Deve então fazer o seguinte:

Para o primeiro botão informe o nome Auto_Open e clique no botão Novo:

Será aberta a janela onde deve informar o código para abrir o formulário que iremos usar no projeto;

digite : frmRegistoClientes.Show, conforme a figura a seguir:

Para o outro botão iremos incluir o código para enviar o email mas faremos isso mais adiante:

Agora vamos alterar os textos dos botões:

1. Para alterar o texto do botão clique com o botão direito do mouse sobre o botão e a seguir seleccione: Editar Texto;

2. Informe os textos conforme indica a figura para os dois botões; Vamos agora incluir o formulário no projeto para fazer isso faça o seguinte:

1. Estando na folha aberta pressione Alt+F11 ou clique na opção Visual Basic no menu da folha para abrir o Editor Visual Basic;

2. Na janela do Editor aberto clique no menu Inserir e seleccione UserForm para incluir um formulário no projeto;

O formulário será incluído e a caixa de ferramentas com os controlos que vamos incluir no formulário será aberta. Posicione-a ao lado do formulário.

A seguir vamos incluir os controlos da caixa de ferramenta no formulário seleccionando e arrastando o controlo para o formulário. Iremos usar os seguintes controlos:

1. 8 Controles Label - usar o nome padrão para todos excepto para a Label que irá exibir os registos cujo nome deverá ser: lblRegisto;

2. 7 Controles TextBox : txtCodigo, txtNome, txtEndereco, txtCidade, txtLocalidade, txtCp, txtTelefone, txtEmail

3. 10 Controles Button : cmdEnviarEmail, cmdNovo, cmdAlterar, cmdExcluir, cmdOk, cmdCancelar, cmdPrimeiro, cmdAnterior, cmdProximo , cmdUltimo; O layout do formulário esta apresentado abaixo:

Para atribuir um nome e o texto a ser apresentado no controlo, quando pertinente, seleccione o controlo e pressione F4 para abrir a janela de propriedades;

A seguir defina o Nome na propriedade Name e o Texto na propriedade Caption:

Obs: A propriedade BackColor define a cor de fundo do formulário.

Já temos dessa forma o nosso formulário pronto para ser incluído código VBA que irá fazer funcionar o nosso projecto.

Iremos definir o código VBA do projecto nos eventos dos controles do formulário.

Na primeira parte está tudo pronto para que nesta segunda parte pudesse partir para a

definição do código VBA da aplicação. E assim será feito...

Estando com a folha aberta pressione Alt+F11 ou clique na opção Visual Basic no menu da folha para abrir o Editor Visual Basic;

Quando a janela do Editor Visual Basic estiver aberta expanda o item Formulário e clique no formulário frmRegistoClientes para apresentar o formulário conforme a figura

abaixo:

Antes de definir qualquer código no formulário temos que logo no início do mesmo declarar as variáveis que iremos usar no formulário. Dessa forma no início do código do formulário digite o código que declara essas variáveis conforme abaixo:

Option Explicit

'define constantes para controlar as colunas de dados

Const colCodigo As Integer = 1 Const colNome As Integer = 2 Const colEndereco As Integer = 3 Const colCidade As Integer = 4 Const colLocalidade As Integer = 5 Const colCp As Integer = 6 Const colTelefone As Integer = 7 Const colEmail As Integer = 8 Const indiceMinimo As Byte = 2

'define variavies para controlar a

Private alterar As Boolean Private novo As Boolean Private eliminar As Boolean

'define as constantes para as cores do textbox

Const corDesabilitaTextBox As Long = -2147483633 Const corHabilitaTextBox As Long = -2147483643 'define a folha usada e o indice do registo

Private wsRegistoClientes As Worksheet Private indiceRegisto As Long

Antes de partirmos para a definição do código dos eventos dos botões temos que definir o código que usaremos quando o formulário for aberto. Quando o formulário é aberto ocorre o evento Initialize() do formulário e nele incluímos o seguinte código:

Private Sub UserForm_Initialize() novo = False

alterar = False eliminar = False

Set wsRegistoClientes = ThisWorkbook.Worksheets("Clientes") Call HabilitaBotoesAlteracao

Call carregaDados Call DesabilitaControles End Sub

A seguir vou mostrar as 4 rotinas usadas no formulário: HabilitaBotoesAlteracao,

carregaDados e DesabilitaControles e HabilitaControles:

1- HabilitaBotoesAlteracao() - Esta rotina apenas desabilita os botões Alterar, Excluir, Novo, OK e Cancelar;

Private Sub HabilitaBotoesAlteracao() 'habilita os botões de alteração

cmdAlterar.Enabled = True cmdEliminar.Enabled = True cmdNovo.Enabled = True cmdOk.Enabled = False cmdCancelar.Enabled = False End Sub

2- carregaDados() - Carrega os dados o registo atual exibindo-os nos controles TextBox;

Private Sub CarregaRegisto()

'carrega os dados do primeiro registo

With wsRegistoClientes

If Not IsEmpty(.Cells(indiceRegisto, colNome)) Then Me.txtCodigo.Text = .Cells(indiceRegisto, colCodigo).Value Me.txtNome.Text = .Cells(indiceRegisto, colNome).Value Me.txtEndereco.Text = .Cells(indiceRegisto, colEndereco).Value Me.txtCidade.Text = .Cells(indiceRegisto, colCidade).Value Me.txtLocalidade.Text = .Cells(indiceRegisto, colLocalidade).Value Me.txtCp.Text = .Cells(indiceRegisto, colCp).Value

Me.txtTelefone.Text = .Cells(indiceRegisto, colTelefone).Value Me.txtEmail.Text = .Cells(indiceRegisto, colEmail).Value End If

End With

Call AtualizaRegistoAtual End Sub

3- DesabilitaControles() - Desabilita os controles TextBox e altera a cor de cada um deles;

Private Sub DesabilitaControles() Me.txtNome.Locked = True Me.txtEndereco.Locked = True Me.txtCidade.Locked = True Me.txtLocalidade.Locked = True Me.txtCp.Locked = True Me.txtTelefone.Locked = True Me.txtEmail.Locked = True

'altera a cor dos controles

Me.txtNome.BackColor = corDesabilitaTextBox Me.txtEndereco.BackColor = corDesabilitaTextBox Me.txtCidade.BackColor = corDesabilitaTextBox Me.txtLocalidade.BackColor = corDesabilitaTextBox Me.txtCp.BackColor = corDesabilitaTextBox Me.txtTelefone.BackColor = corDesabilitaTextBox Me.txtEmail.BackColor = corDesabilitaTextBox End Sub

4- HabilitaControles() : Reabilita os controles TextBox e as cores;

Private Sub HabilitaControles() Me.txtNome.Locked = False Me.txtEndereco.Locked = False Me.txtCidade.Locked = False Me.txtLocalidade.Locked = False Me.txtCp.Locked = False Me.txtTelefone.Locked = False Me.txtEmail.Locked = False

'altera a cor dos controles

Me.txtNome.BackColor = corHabilitaTextBox Me.txtEndereco.BackColor = corHabilitaTextBox Me.txtCidade.BackColor = corHabilitaTextBox Me.txtLocalidade.BackColor = corHabilitaTextBox Me.txtCp.BackColor = corHabilitaTextBox Me.txtTelefone.BackColor = corHabilitaTextBox Me.txtEmail.BackColor = corHabilitaTextBox End Sub

Após isso agora vamos usar o evento Click de cada um dos Botões de comando existentes no formulário para realizar as operações que desejamos que seja executada na folha. Basta clicar duas vezes sobre o botão desejado para que a janela de código seja aberta com o evento pronto para receber o código. Faremos este procedimento para cada uma dos 10 botões de comando iniciando com o botão Novo e deixando por último o botão

Enviar Email;

1- Código dos botões que realiza as operações de manutenção de dados:

Private Sub cmdNovo_Click() novo = True eliminar = False alterar = False Call LimpaControles Call HabilitaControles Call DesabilitaBotoesAlteracao

'dá o foco ao primeiro controle de dados

txtNome.SetFocus End Sub

2- Botão Alterar: Define a variável alterar como True e verifica se o código do cliente foi informado, definindo o foco na caixa de texto Nome:

Private Sub cmdAlterar_Click() alterar = True

If txtCodigo.Text <> vbNullString And txtCodigo.Text <> "" Then Call HabilitaControles

Call DesabilitaBotoesAlteracao

txtNome.SetFocus Else

lblMensagem.Caption = "Não há registo a ser alterado" End If

End Sub

3- Botão Eliminar - Define a variável eliminar como True, verifica se o código do cliente foi informado e desabilita os botões de alteração:

Private Sub cmdEliminar_Click() eliminar = True

If txtCodigo.Text <> vbNullString And txtCodigo.Text <> "" Then Call DesabilitaBotoesAlteracao

lblMensagem.Caption = "Confirma a eliminação deste registo. (Para eliminar clique no botão OK.) "

Else

lblMensagem.Caption = "Não existe registo a ser eliminado" End If

End Sub

4- Botão OK - Este código irá realizar as operações conforme o valor da variável alterar, nome e eliminar:

Private Sub cmdOk_Click() 'valida campos do formulário

If ValidaCamposFormulario = False Then Exit Sub

End If

Dim proximoId As Long

'Alterar registos

If alterar = True Then

Call GravaRegisto(CLng(txtCodigo.Text), indiceRegisto) lblMensagem.Caption = "O Registo alterado com sucesso." alterar = False

End If

'Novo registo

If novo = True Then

proximoId = ObterProximoId 'pega a próxima linha

Dim proximoIndice As Long

proximoIndice = wsRegistoClientes.UsedRange.Rows.Count + 1 Call GravaRegisto(proximoId, proximoIndice)

txtCodigo = proximoId

lblMensagem.Caption = "Novo registo guardado com sucesso." novo = False

End If

'Elimina um registo

If eliminar = True Then

Dim resultado As VbMsgBoxResult

If resultado = vbYes Then

wsRegistoClientes.Range(wsRegistoClientes.Cells(indiceRegisto,

colCodigo), wsRegistoClientes.Cells(indiceRegisto, colCodigo)).EntireRow.Delete Call carregaDados

lblMensagem.Caption = "O Registo escolhido foi eliminado com sucesso." End If eliminar = False End If Call HabilitaBotoesAlteracao Call DesabilitaControles End Sub

5- Botão Cancelar - Cancela uma operação em execução. Private Sub cmdCancelar_Click()

cmdOk.Enabled = False cmdCancelar.Enabled = False Call DesabilitaControles Call carregaDados Call HabilitaBotoesAlteracao End Sub

As operações de cada um dos botões acima descritos usam as seguintes rotinas para realizar as tarefas pertinentes:

1- GuardaRegisto() - Guarda as informações na folha de Excel:

Private Sub GuardaRegisto(ByVal id As Long, ByVal indice As Long) With wsRegistoClientes

.Cells(indice, colCodigo).Value = id

.Cells(indice, colNome).Value = Me.txtNome.Text .Cells(indice, colEndereco).Value = Me.txtEndereco.Text .Cells(indice, colCidade).Value = Me.txtCidade.Text .Cells(indice, colLocalidade).Value = Me.txtLocalidade.Text .Cells(indice, colCp).Value = Me.txtCp.Text

.Cells(indice, colTelefone).Value = Me.txtTelefone.Text .Cells(indice, colEmail).Value = Me.txtEmail.Text End With

Call AtualizaRegistoAtual End Sub

2- ObterProximoId - Obtém a próxima posição do registo na folha:

Private Function ObterProximoId() As Long Dim rangeIds As Range

'armazena a range que se refere a toda a coluna do código (id)

Set rangeIds = wsRegistoClientes.Range(wsRegistoClientes.Cells(indiceMinimo, colCodigo), wsRegistoClientes.Cells(wsRegistoClientes.UsedRange.Rows.Count, colCodigo))

ObterProximoId = WorksheetFunction.Max(rangeIds) + 1 End Function

3- AtualizaRegistoAtual - Atualiza a informação do registo atual apresentando a posição atual do registo na Label do formulário:

Private Sub AtualizaRegistoAtual()

lblRegisto.Caption = indiceRegisto - 1 & " de " & wsRegistoClientes.UsedRange.Rows.Count - 1 End Sub

2- Código dos botões que permitem a navegação pelos dados da folha

Private Sub cmdPrimeiro_Click() Call limpaMensagem indiceRegisto = indiceMinimo If indiceRegisto > 1 Then Call CarregaRegisto End If End Sub

2- < - Registo Anterior - Limpa a mensagem e verifica o índice do registo posicionando-o nposicionando-o registposicionando-o anteriposicionando-or;

Private Sub cmdAnterior_Click() If indiceRegisto > indiceMinimo Then indiceRegisto = indiceRegisto - 1 End If If indiceRegisto > 1 Then Call CarregaRegisto End If End Sub

3- > - Próximo Registo - Limpa a mensagem e verifica se o índice do registo e menor que o total de linhas; posicionando-o no próximo registo;

Private Sub cmdProximo_Click() Call limpaMensagem

If indiceRegisto < wsRegistoClientes.UsedRange.Rows.Count Then indiceRegisto = indiceRegisto + 1 End If If indiceRegisto > 1 Then Call CarregaRegisto End If End Sub

4- >> - Último Registo - Limpa a mensagem e atribui o total de registo ao índice indo para último registo:

Private Sub cmdUltimo_Click() Call limpaMensagem

indiceRegisto = wsRegistoClientes.UsedRange.Rows.Count If indiceRegisto > 1 Then

Call CarregaRegisto End If

End Sub

3- Código do botão Enviar Email

Application.ScreenUpdating = False

Set aplicacaoOutlook = CreateObject("Outlook.Application") On Error GoTo limpa

Set OutMail = aplicacaoOutlook.CreateItem(0) On Error Resume Next

With OutLookMail

.Subject = "Aviso"

.Body = "Caro " & txtNome.Text _ & vbNewLine & vbNewLine & _

"Entre em contato com nosso serviço de cobrança " & _ "para tratar assunto de seu interesse com urgência" 'Podemos enviar um anexo

.Attachments.Add ("c:\dados\carta.txt") .Send

End With

On Error GoTo 0

Set OutLookMail = Nothing

MsgBox ("Email enviado com sucesso..." & " para " & txtEmail.Text) limpa:

Set aplicacaoOutlook = Nothing Application.ScreenUpdating = True End Sub

A rotina para enviar um email usa o Microsoft OutLook criando uma instância deste objeto e montando e enviando um email.

4- Rotinas de validação de dados usadas no formulário

Private Function ValidaCamposFormulario() As Boolean If Me.txtNome.Value = "" Then

Me.txtNome.SetFocus

MsgBox " 'Nome' é um campo obrigatório. ", vbOKOnly, " Campo Obrigatório " ValidaCamposFormulario = False

Exit Function

ElseIf Me.txtEndereco.Value = "" Then Me.txtEndereco.SetFocus

MsgBox " 'Endereço' é um campo obrigatório.", vbOKOnly, " Campo Obrigatório " ValidaCamposFormulario = False

Exit Function

ElseIf Me.txtCidade.Value = "" Then Me.txtCidade.SetFocus

MsgBox "'Cidade' é um campo obrigatório. ", vbOKOnly, " Campo Obrigatório " ValidaCamposFormulario = False

Exit Function

ElseIf Me.txtLocalidade.Value = "" Then Me.txtLocalidade.SetFocus

MsgBox "'Localidade' é um campo obrigatório. ", vbOKOnly, " Campo Obrigatório " ValidaCamposFormulario = False

Exit Function

ElseIf Me.txtCp.Value = "" Then Me.txtCep.SetFocus

MsgBox " 'Cp' é um campo obrigatório. ", vbOKOnly, " Campo Obrigatório " ValidaCamposFormulario = False

Exit Function

ElseIf Me.txtTelefone.Value = "" Then Me.txtTelefone.SetFocus

MsgBox "'Telefone' é um campo obrigatório.", vbOKOnly, " Campo Obrigatório " ValidaCamposFormulario = False

Exit Function

ElseIf Me.txtEmail.Value = "" Then Me.txtEmail.SetFocus

MsgBox "'Email' é um campo obrigatório.", vbOKOnly, " Campo Obrigatório " ValidaCamposFormulario = False

Exit Function End If

ValidaCamposFormulario = True End Function

Validação do Email informado no campo Email usando uma expressão regular: Private Sub txtEmail_Exit(ByVal Cancel As MSForms.ReturnBoolean)

With CreateObject("vbscript.regexp")

.Pattern = "^[\w-\.]+@([\w-]+\.)+[A-Za-z]{2,3}$" If Not .test(txtEmail.Value) Then

MsgBox "Email inválido." Cancel = True

End If End With End Sub

Ao executarmos o projeto abrindo a folha de Excel ao clicarmos no botão para enviar um email para o cliente seleccionado iremos obter: