Dicas de VBA

Editor VBA

Programando no Editor do VBA - Visual Basic for Applications

Para acessar o editor de Visual Basic for Applications: Menu Ferramentas/ Macro / Editor do Visual Basic ou Alt + F11. Será aberta a janela abaixo:

Senão existir o Módulo1, vá até o Menu Inserir / Módulo, pois deverá de ter pelo menos um, para criar as macros, sub-rotinas, etc.

Project Explorer - É onde estão a lista das planilhas, módulos, formulários. Clique no Menu Exibir / Project Explorer ou Ctrl + R quando desejar exibi-la.
Código - É onde escrevemos a codificação de macros, functions, etc. Tecle F7 quando desejar exibi-la.
Propriedades - É onde estão as propriedades da planilha, formulários e objetos. Clique no Menu Exibir / Properties ou F4 quando desejar exibi-la.

Cores do VBA

Cor

Significado 

Azul

Palavras-Chave da linguagem. Ex: Sub, End Sub, Function, If, Then, Else, While, Loop,… A primeira letra fica em maiúscula.

Vermelho

Sempre que escreve na janela de código uma linha em linguagem VBA, o editor vai verificar a sintaxe e indicará se existe ou não algum erro de escrita. Se for detectado um erro a linha fica em vermelho e é exibida uma mensagem de erro.

Preto

Nome de variáveis, procedimentos, valores, operadores,… É o nome criado pelo criador (programador).

Verde

Comentário colocado no início dos procedimentos. Estes comentários servem para o criador poder colocar algumas explicações aos procedimentos realizados. Essas palavras com esta cor são ignoradas no procedimento, não produzem qualquer efeito na sua execução. Para inserir comentários bastará que o caractere ' (apóstofo) anteceda o texto.

Amarelo

Um sombreado amarelo poderá aparecer sobre a linha que identifica um procedimento. Esta cor pode simbolizar a ocorrência de um erro na execução do respectivo procedimento ou o a execução está parado nesta linha. O procedimento iniciou a execução, durante a qual detectou um erro e agora está parado, mas ainda em execução. Quando isto acontecer não deverá voltar a dar ordem de execução do procedimento, sem antes parar (Stop) a execução mal sucedida, a não ser que seja corrigida o erro.

Prefixos utilizados em módulos, formulários

Quando criamos módulos, formulários, estes objetos como outros que inserimos também dentro de formulários, possuem um prefixo, que facilitam a identificação dentro da codificação e mesmo fora.

Objeto
Prefixo
Módulo
mdl
Formulário
frm
Rótulo (Label)
lbl
Caixa de Texto (Text Box)
txt
Botão de Comando (Command Button)
cmd
Caixa de Combinação (Combo Box)
cmb
Moldura (Frame)
fra
Caixa de Seleção (Check Box)
chk
Botão de Opção (Option)
opt
Masked Edit Box
msk

Os objetos abaixo do formulário pertencem ao mesmo, e servem para criarmos a parte visual do projeto no VBA do excel, semelhante ao Visual Basic e outras linguagens visuais.

Criando um pequeno Controle de Despesas

A partir da célula A7, digite os dados:

Cadastro de Clientes
Código Nome Cidade Telefone

Vá até a célula I3 e digite: Área de recepção de dados e cole a partir da célula I4 das células A9 até D9 (Código, Nome, Cidade e Telefone).

Nomeando as células

Para facilitar o trabalho na codificação da macro, vamos nomear as células com o nome respectivo (como se fossem campo em uma tabela).

Para que o excel guarde cada linha (registro), há necessidade criar uma macro para receber estes dados.

Pressione Alt + F11. Senão existir um módulo, crie-o e dentro da janela de código (pressione F7 se a mesma não estiver visível), digite a macro abaixo:
Sub Recepcao_Cliente()
'Macro para receber os dados do cliente
Dim nCodigo As Long 'Declaração da variável como inteiro longo
Dim sNome As String * 40 'Declaração da variável com tamanho de 40 caracteres
Dim sCidade As String * 25
Dim sTelefone As String * 14 'Como tem traço, dois pontos, foi declarada como string

'Será solicitado os dados dos campos abaixo
nCodigo = Application.InputBox("Informe o Código:", , , , , , , 1)
sNome = Application.InputBox("Informe o Nome:", , , , , , , 2)
sCidade = Application.InputBox("Informe a Cidade:", , , , , , , 2)
sTelefone = Application.InputBox("Informe o Telefone:", , , , , , , 2)

Range("Codigo").Select 'Seleciona a célula Código
ActiveCell.FormulaR1C1 = nCodigo 'Armazena o código digitado acima nesta célula
Range("Nome").Select
ActiveCell.FormulaR1C1 = sNome
Range("Cidade").Select
ActiveCell.FormulaR1C1 = sCidade
Range("Telefone").Select
ActiveCell.FormulaR1C1 = sTelefone
End Sub

Pressione a tecla F5 para executar a macro. Será solicitado os dados referente a cada campo. Insira-os e a seguir vá até a planilha para conferir. Você notará que os dados foram colocados na sua respectiva célula.
Se você executar a macro novamente, verá que os dados são sobrepostos, pois o endereço é fixo. Na verdade esta macro é somente para receber os dados. Falta a macro para o transporte para a primeira área, que é na verdade onde os dados ficarão armazenados e não serão no final sobrepostos.

Macro Transporte

Faremos a criação desta macro, através do gravador da macro.

Já dentro da planilha, Selecione a célula I5 e faça os procedimentos a seguir:

Se você executar novamente a macro transporte, notará que os dados ainda se sobrepõem. Devemos fazer uma pequena alteração nesta macro.
Pressione Alt + F11 e no Project Explorer (onde estão a lista das planilhas, módulos), deverá existir o Módulo2. Selecione-o e pressione a tecla F4. Se a janela de propriedades não estava ativa, agora está. Altere a propriedade Name para: mdlTransporte_Cliente.
Na janela de código, altere a linha Range("A10").Select e no lugar coloque: ActiveCell.Offset(1, 0).Range("A1").Select para que o endereço fique relativo e não absoluto. Agora cada vez que você incluir pela macro recepção e pressionar Ctrl + T, os dados serão inseridos linha a linha.

Utilizando formulário no VBA

Pressione Alt + F11 e a seguir clique no Menu Inserir / UserForm. Será exibido o formulário com a caixa de Ferramentas.

Na janela Project Explorer conforme figura acima, aparece a pasta UserForm1 cm formulário UserForm1.

Alterando dados do formulário UserForm1

Pressione a tecla F4 para exibir a janela de Propriedades altere:
Name: frmCliente
Caption: Cadastro de Clientes
Caption é um título, ou seja: é o nome externo do formulário, enquanto que Name é o nome interno. Foi colocado como frmCliente, pois cada objeto tem seu prefixo, que neste caso identifica como sendo o formulário.

Inserindo Moldura

A moldura (frame) tem a função de agrupar objetos, ou seja: imagine que você tenha várias caixas de textos e deseja que as mesmas não fiquem habilitadas (elas ficaram inacessíveis), ao fazer isso com o frame, as caixas também serão afetadas.
Selecione a moldura na caixa de ferramentas e arraste-a dentro do formulário, alterando as propriedades:
Name: fraDados
Caption: Dados

Insira dentro da moldura, os objetos abaixo:

Objeto
Propriedade
Valor
Rótulo
Name
lblCodigo
  Caption Codigo
Caixa de Texto
Name
txtCodigo
  Text Deixar em branco
Rótulo
Name
lblNome
Caption
Nome
Caixa de Texto
Name
txtCidade
Text
Deixar em branco
Rótulo
Name
lblTelefone
Caption
Telefone
Caixa de Texto
Name
txtTelefone
Text
Deixar em branco
Botão de Comando
Name
cmdSalvar
Caption
Salvar
Botão de Comando
Name
cmdSair
Caption
Sair

O formulário deverá ter a aparência conforme figura abaixo:


Observe que foi inserido outra moldura para os botões.

Executando o formulário

Clique dentro do formulário e pressione a tecla F5 para executa-lo.

Codificando o formulário

Dê um duplo clique no botão Sair. Entre o Private Sub e o End Sub, aperte a tecla Tab e digite a instrução End. Pressione a tecla F5 para executa-lo. Dê um clique no botão Sair para finalizar o formulário.

Dê um duplo clique no botão Salvar. Entre o Private Sub e o End Sub, aperte a tecla Tab e digite os códigos abaixo:
'Seleciona cada campo e armazena os dados digitados nas caixas de
'texto, nas células de recepção dos dados da planilha
Range("Codigo").Select
ActiveCell.FormulaR1C1 = txtCodigo.Text
Range("Nome").Select
ActiveCell.FormulaR1C1 = txtNome.Text
Range("Cidade").Select
ActiveCell.FormulaR1C1 = txtCidade.Text
Range("Telefone").Select
ActiveCell.FormulaR1C1 = txtTelefone.Text
Transporte_Cliente 'Chama a macro para o transporte dos dados

Pressione a tecla F5 para executar. Digite os dados e a seguir, dê um clique no botão Salvar.

Salvar a pasta antes de encerrar o Excel. No Evento BeforeClose do objeto Workbook, digite o comando: ThisWorkbook.Save. Veja abaixo:
Private Sub Workbook_BeforeClose(Cancel As Boolean)
ThisWorkbook.Save ' Salva as alterações antes de fechar esta pasta de trabalho
End Sub
Seleciona a planilha
Private Sub Workbook_Open()
Sheets("Principal").Select ' Seleciona a planilha Principal
End Sub
Código automático (como se fosse um campo com numeração automática). No botão Novo do formulário VBA, digite os códigos abaixo:
Dim intervalo As Range
Dim valorPesquisa As Integer
Set intervalo = ThisWorkbook.Sheets("Cidades").Range("A:A")
valorPesquisa = Application.WorksheetFunction.Max(intervalo)
txtCodigo.Text = valorPesquisa + 1
Subrotina para fazer a conexão com Banco de Dados Access
Sub conectar()
Set cn = New ADODB.Connection
strDB = ThisWorkbook.Path & "\dbComercial.accdb"
cn.Open _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strDB & ";"
cn.CursorLocation = adUseClient
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
End Sub
Subrotina para criar o cabeçalho do Controle ListView
Sub cabecalho()
With lsvCidades
.ListItems.Clear
.View = lvwReport
.CheckBoxes = False
'.FullRowSelect = True
'.Gridlines = True
'.ColumnHeaders.Clear
.ColumnHeaders.Add Text:="Código", Width:=50, Alignment:=0
.ColumnHeaders.Add Text:="Nome", Width:=180, Alignment:=0
.ColumnHeaders.Add Text:="UF", Width:=35, Alignment:=0
End With
End Sub>
Subrotina para carregar dados do Controle ListView, nas caixas de texto do formulário
Sub carregar_ListView()
Dim linha As Double
On Error GoTo erro_Carregar_Dados
With lsvCidades
linha = .SelectedItem.Index 'Verifica qual linha o usuário clicou
txtCodigo.Value = .ListItems(linha).Text ' Obtém o número da primeira coluna
txtCidade.Value = .ListItems(linha).ListSubItems(1).Text ' Obtém o número da segunda coluna
cmbUF.Value = .ListItems(linha).ListSubItems(2).Text ' Obtém o número da terceira coluna
End With
Exit Sub
erro_Carregar_Dados:
MsgBox Err.Number & vbNewLine + Err.Description
End Sub
Subrotina para carregar dados no Controle ListView
Sub carregar_Dados()
Dim lista As Variant
Set cn = New ADODB.Connection
strDB = ThisWorkbook.Path & "\Banco_Dados.accdb"
cn.Open _
"Provider=Microsoft.ACE.OLEDB.12.0;" & _
"Data Source=" & strDB & ";"
cn.CursorLocation = adUseClient
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
strSQL = "SELECT * FROM tbcidades ORDER BY cidade"
rs.Open strSQL, cn, adOpenDynamic, adLockOptimistic
lsvCidades.ListItems.Clear ' Apaga os dados do listview, antes de preencher, pois caso contrário os dados a cada vez que passar nesta rotina, serão duplicados
While Not rs.EOF
With lsvCidades
Set lista = lsvCidades.ListItems.Add(Text:=rs(0))
lista.ListSubItems.Add Text:=rs(1)
lista.ListSubItems.Add Text:=rs(2)
End With
rs.MoveNext
Wend
If Not rs Is Nothing Then
rs.Close
Set rs = Nothing
End If
Set lista = Nothing
End Sub
Subrotina para editar os dados do formulário e salvar na tabela do banco de dados
Private Sub btnEditar_Click()
If txtCidade.Text = "" Then
MsgBox "Informe o nome da Cidade!", vbInformation, "Cidade"
txtCidade.SetFocus
Exit Sub
End If
If cmbUF.Text = "" Then
MsgBox "Informe a UF!", vbInformation, "UF"
cmbUF.SetFocus
Exit Sub
End If
If MsgBox("Deseja salvar?", vbQuestion + vbYesNo + vbDefaultButton1, "Editar") = vbYes Then
On Error GoTo erro_Salvar
Application.ScreenUpdating = False
Call conectar
' Insere dentro do Banco de Dados Access
strSQL = "UPDATE tbcidades SET cidade = '" & txtCidade.Text & "',"
strSQL = strSQL + " uf = '" & cmbUF.Text & "'"
strSQL = strSQL + " WHERE codigo=" & Val(txtCodigo.Text)
cn.Execute (strSQL)
MsgBox "Cidade editada com Sucesso!", vbInformation, "Editar"
Call carregar_Dados
cn.Close
Application.ScreenUpdating = True
End If
limpar
Exit Sub
erro_Salvar:
MsgBox Err.Number & vbNewLine & Err.Description
End Sub
Subrotina para excluir da tabela do banco de dados, o registro selecionado do formulário
Private Sub btnExcluir_Click()
If MsgBox("Deseja realmente excluir?", vbQuestion + vbYesNo + vbDefaultButton2, "Excluir") = vbYes Then
'On Error GoTo erro_Excluir
Dim codigo As Integer
codigo = Application.InputBox("Informe o código da cidade para exclusão:", "Excluir", , , , , , 1)
Call conectar
strSQL = "DELETE FROM tbcidades WHERE codigo=" & Val(codigo)
cn.Execute (strSQL)
MsgBox "Cidade excluída com Sucesso!", vbInformation, "Excluir"
Call carregar_Dados
End If
' Faz a exclusão na planilha do Excel
If Val(codigo) = 0 Then Exit Sub
With Worksheets(2).Range("A:XFD")
Set c = .Find(codigo, LookIn:=xlValues)
If Not c Is Nothing Then
Worksheets(2).Activate
Range(c.Address).Activate
linha = ActiveCell.Row
Rows(linha).Select
Selection.Delete shift:=xlUp
MsgBox "Cidade excluída com sucesso!", vbInformation, "Excluir"
Range("A1").Activate
Else
MsgBox "Não foi encontrado o registro!", vbInformation, "Excluir"
End If
End With
btnCancelar_Click
Exit Sub
erro_Excluir:
MsgBox Err.Number & vbNewLine & Err.Description
End Sub
Subrotina para limpar os dados das caixas de textos do formulário Private Sub limpar()
txtCidade.Text = ""
cmbUF.Text = ""
fraDados.Enabled = False
btnSalvar.Enabled = False
btnExcluir.Enabled = False
End Sub
Subrotina para salvar o registro na tabela do banco de dados
Private Sub btnSalvar_Click()
If MsgBox("Deseja salvar?", vbQuestion + vbYesNo + vbDefaultButton1, "Salvar") = vbYes Then
On Error GoTo erro_Salvar
Application.ScreenUpdating = False
Call conectar
' Insere dentro do Banco de Dados Access
strSQL = "INSERT INTO tbcidades (codigo, cidade, uf) VALUES ('" & txtCodigo.Text & "','" & txtCidade.Text & "','" & cmbUF.Text & "')"
cn.Execute (strSQL)
' Faz a inclusão dos dados na planilha do Excel
Sheets("Cidades").Select
Range("A1048576").End(xlUp).Select
ActiveCell.Offset(1, 0).Select ' Desce uma linha
ActiveCell.EntireRow.Insert ' Insere os dados na planilha nos respectivos campos
ActiveCell.Offset(0, 0) = Me.txtCodigo.Text
ActiveCell.Offset(0, 1) = txtCidade.Text
ActiveCell.Offset(0, 2) = cmbUF.Text
MsgBox "Cidade Cadastrada com Sucesso!", vbInformation, "Inserir"
Call carregar_Dados
Call limpar
Application.ScreenUpdating = True
End If
limpar ' Chama a subrotina que limpa as caixas de textos
Exit Sub
erro_Salvar:
MsgBox Err.Number & vbNewLine & Err.Description
End Sub