Apostila de Macros e VBA
Apostila de Macros e VBA
Apostila de Macros e VBA
VBA - Excel
MARCELO VENSKE
CONSULTOR INSTRUTOR:
LOCAL E DATA:
http://www.cavalcanteassociados.com.br/
NDICE
1. Visual Basic for Application - VBA ............................................................................... 5
1.1.
Macros Gravadas .................................................................................................... 5
1.2.
Macros Escritas....................................................................................................... 6
2. A Elaborao das Macros ........................................................................................... 7
2.1.
Macros Gravadas .................................................................................................... 7
2.1.1.
Gravao Absoluta e Relativa ............................................................................. 8
2.1.2.
Localizando uma Macro no editor do VBA. ........................................................ 10
2.2.
Entendendo Melhor a Programao ...................................................................... 13
2.2.1.
Rotinas ou Sub Rotina....................................................................................... 13
2.2.2.
Objetos das Rotinas .......................................................................................... 14
2.2.3.
Propriedades e Mtodos ................................................................................... 15
2.3.
Estrutura de Objetos do Excel ............................................................................... 17
2.4.
Objetos Range e Cells .......................................................................................... 18
2.5.
Deslocamento na planilha utilizando a propriedade Offset .................................... 20
2.6.
Objetos Sheets e Plan ......................................................................................... 22
2.7.
Uso da Instruo With ........................................................................................... 24
3. Caixas de Dilogo (MsgBox e InputBox) ................................................................. 25
3.1.
InputBox (Caixa de Entrada de Dados) ................................................................ 25
3.2.
MsgBox - (Caixa de Mensagem) ......................................................................... 27
4. Utilizando Variveis ................................................................................................... 30
4.1.
Tipos de Variveis ................................................................................................. 31
4.2.
Abrangncia ou Domnio das Variveis ................................................................. 34
5. Estruturas de Deciso ............................................................................................... 36
5.1.
Estrutura IF... Then ............................................................................................. 36
5.2.
Operadores And e Or em estruturas IF... Then .................................................... 39
5.2.1.
Operador And ................................................................................................... 39
5.2.2.
Operador Or ..................................................................................................... 40
6. Estruturas de Repetio (Loop)................................................................................. 41
6.1.
Estrutura Do While... Loop ................................................................................... 41
6.2.
Estrutura Do Until... Loop .................................................................................... 44
7. Macros Auto-Executveis.......................................................................................... 46
7.1.
Macro Auto Executvel de Planilha ....................................................................... 47
7.2.
Macro Auto Executvel em Clula de Planilha ...................................................... 49
7.3.
Macro Auto Executvel em Arquivo ....................................................................... 51
8.
12.
Utilizando Funes do VBA ................................................................................... 67
12.1.
Funes Matemticas ....................................................................................... 67
12.2.
Funes de Data e Hora ................................................................................... 68
12.3.
Funes de Converso ..................................................................................... 69
12.4.
Funes de Informao ..................................................................................... 70
12.5.
Funes de Tratamento de Texto (String) ......................................................... 71
12.5.1. Funo: UCase ................................................................................................. 71
12.5.2. Funo: LCase .................................................................................................. 71
12.5.3. Funo: StrConv ............................................................................................... 72
12.5.4. Funo: Len ...................................................................................................... 72
12.5.5. Funo: Left ...................................................................................................... 73
12.5.6. Funo: Right .................................................................................................... 73
12.5.7. Funo: Mid ...................................................................................................... 74
12.5.8. Funo: InStr ..................................................................................................... 75
12.5.9. Funo: InStrRev .............................................................................................. 75
12.5.10.
Funo: StrReverse ....................................................................................... 76
12.5.11.
Funes: Trim / LTrim / RTrim ....................................................................... 77
12.5.12.
Funo: Space .............................................................................................. 78
12.5.13.
Funo: String ............................................................................................... 79
13.
13.1.
13.2.
13.3.
13.4.
14.
14.1.
14.2.
14.3.
14.4.
15.
Conexo com Banco de Dados Via VBA ............................................................. 101
15.1.
Objetos de Conexo VBA ................................................................................ 101
15.2.
Utilizando o ADO para criar uma conexo. ...................................................... 102
15.2.1. Estabelecendo a referncia com biblioteca ADO ............................................. 102
15.2.2. Declarando o objeto e abrindo a Conexo....................................................... 103
15.2.3. Declarando o objeto e abrindo um Recordset .................................................. 105
15.2.3.1.
Configurao dos Parmetros do Recordset ............................................... 106
15.3.
Criando Uma Conexo com Access via ADO............................................... 108
15.3.1. Importando Registros do Access ..................................................................... 109
15.3.2. Inserindo Registros do Excel para o Access .................................................... 111
15.3.3. Editando Registros do Excel no Access .......................................................... 113
15.3.4. Excluindo Registros do Access Atraves do Excel ............................................ 115
15.4.
Criando Conexo com SQL Server via ADO ................................................ 117
15.5.
Criando Conexo com Outro Arquivo Excel via DAO .................................... 119
15.5.1. Estabelecendo a referncia com biblioteca DAO ............................................. 119
15.5.2. Importando Registros de Outro Arquivo Excel ................................................. 120
16.
17.
Rotinas
Rotina uma seqncia de cdigos e instrues de programao com a finalidade de
executar uma ou vrias tarefas. As rotinas em VBA constuma-se chamar de macros.
Macros
Costuma-se chamar de macro as rotinas (tarefas) de programao desenvolvidas dentro
dos aplicativos do pacote Office. Ento uma macro um pequeno programa que contm
um conjunto de instrues em linguagem VBA(Visual Basic for Application) dispostas de
forma lgica com a finalidade de automatizar tarefas ou aes dentro dos aplicativos
Office.
1.1.
Macros Gravadas
1.2.
Macros Escritas
Macros Gravadas
Fazendo uma analogia a criao das macros gravadas seguem o mesmo procedimento da
gravao de uma fita-K7 em um gravador, onde voc liga e a partir daquele momento,
tudo o que for feito ser gravado.
No caso da macro gravada o procedimento o mesmo, voc inicia a gravao e todos os
procedimentos que forem executados como exemplo: Formatar clulas, Copiar, Colar,
Ativar Planilha, Abrir e Fechar Arquivo, etc. ser gravado e quando a macro for executada
todos os procedimentos gravados sero repetidos.
Dessa forma durante a gravao, tenha a certeza que todos os procedimentos
necessrios a sua macro foram executados e aps todos os procedimentos feitos, nunca
esquea de parar a gravao.
Nome do Macro: Deve conter at 255 caracteres sem espao em branco, iniciando com
caractere Alfa (Letras).
Tecla de Atalho: Ser a tecla utilizada para executar a macro (poder ser um boto, que
ser visto mais adiante)
7
Nova pasta de trabalho: Cria uma nova pasta de trabalho para armazenar a macro.
Pasta de Trabalho pessoal de macros: Esta opo armazenar sua macro numa
pasta chamada Pasta Pessoal de Macro, localizada no diretrio de inicio do Excel, desta
forma as macros armazenadas neste local estaro sempre disponveis quando o
programa Excel for inicializado.
Descrio: Opcional, um comentrio que voc pode inserir. Este poder ser importante
para que no seja esquecido o objetivo de funcionamento.
Clique em OK para iniciar a gravao da macro neste momento ser exibida uma
pequena barra de ferramentas, indicando o incio da gravao.
Agora execute todos os procedimentos que devem ser gravados e quando terminar no
esquea de parar a gravao, clicando no boto em formato de um quadrado azul na
pequena barra chamada Para Gravao.
Dica!! Antes de iniciar a gravao da macro pratique os procedimentos sem
gravar, se for o caso anote em um papel todos os comandos que tero que ser
feitos, assim voc ter menos chance de errar e ter que refazer a gravao.
2.1.1.
Toda vez que voc grava uma macro possvel determinar que tipo de gravao se
deseja em ralao ao posicionamento das clulas.
Existem dois tipos de gravao: As de endereo absoluto e de endereo relativo. Essa
escolha feita atravs da barra Parar Gravao na opo Referncia Relativa.
2.1.2.
Ento ser aberto o Visual Basic Editor (VBE), exibindo o cdigo da macro selecionada.
Dica!!
O editor do VBA poder ser aberto clicando ALT + F11, porm dessa forma,
voc mesmo ter que localizar a macro desejada.
10
Caixa de Objetos
Caixa de Procedimento
Janela de Projeto
As macros so armazenadas em mdulos, se logo aps for criado uma nova macro,
essa nova macro ser armazenada no mesmo mdulo.
Porm, quando o arquivo for fechado, e posteriormente aberto, a nova macro ser
armazenada em um novo mdulo.
Botes de Macros
Outra maneira de iniciar a gravao e executar uma macro atravs de Botes de
Macro, para gravar uma macro iniciando pela insero do boto, faa o seguinte:
Exiba a barra de ferramentas formulrio, clicando em:
Ferramentas Formulrios
Menu Exibir
Barra de
11
Logo aps soltar o boto do mouse aparecer uma tela chamada Atribuir macro, nesta
tela clique no boto Gravar.
Neste momento em diante siga os procedimentos da gravao da macro detalhados
anteriormente.
12
2.2.
A programao em VBA gerada pela gravao possui uma srie de cdigos, comandos,
comentrios, objetos, etc. O texto envolvido em cada procedimento dotado de uma
srie de cores diferentes, sendo que cada uma tem o seu significado, isto ajuda a
entender melhor os cdigos.
As Cores dos cdigos VBA
Cor
Significado
Azul
Palavras-Chave da linguagem. Ex: Sub, End Sub, Function, If, Then, Else, While, Loop,
Vermelho
Sempre que escreve uma linha em linguagem VBA, o editor vai retificar a sintaxe da
linguagem por forma a indicar se existe ou no algum erro de escrita. Se for detectado um
erro a linha fica em vermelho e exibida uma mensagem de erro, seno todas as palavras
so reconhecidas, coloridas da cor da respectiva categoria e alteradas para letras maisculas
ou minsculas, conforme esteja pr-definido.
Preto
Verde
Amarelo
Um sombreado amarelo poder aparecer sobre a linha que identifica um procedimento. Esta
cor simboliza a ocorrncia de um erro na execuo do respectivo procedimento e o estado de
execuo do mesmo agora est parado, mas ainda em execuo. Quando isto acontecer
voc no dever voltar a dar ordem de execuo do procedimento, sem antes clicar em
redefinir na barra de ferramentas do VBE.
2.2.1.
Cada macro criada d origem a uma Sub Rotina, ento se pode dizer que uma Sub
Rotina uma macro. As Sub Rotinas so aquelas cuja definio delimitada pelas
palavras-chave Sub e End Sub.
Exemplo:
Sub (nome_do_macro) ( )
(corpo_da_macro)
End Sub
13
Dica!! Quando tratar de linha de instruo muito longa, voc poder fazer quebra de
linhas no cdigo introduzindo espao e underline e logo em seguida clicando ENTER.
Exemplo:
Espao e Underline
2.2.2.
14
2.2.3.
Significado
Arquivo Excel
Planilha de arquivos
Clulas da planilha
rea de clulas
Grficos
Colunas
Linhas
Propriedades e Mtodos
Os objetos possuem uma hierarquia que deve ser entendida e respeitada para que os
comandos possam ser empregados corretamente.
Existem duas formas de apresentao hierrquica dos objetos. A Propriedade e o Mtodo.
Propriedades
So as caractersticas do objeto, tanto na aparncia (cor, tamanho, texto do objeto, etc),
quanto na posio dele no arquivo ou nas planilhas.
Nome do objeto
Propriedades
Valor da propriedade
15
Mtodos
aquilo que o objeto faz, ou seja, as aes executadas pelo ou sobre o objeto.
No mesmo caso acima, que a clula A2 teve a cor de preenchimento alterada para
amarelo, agora a clula A2 sofrer a ao do mtodo, que ser o de limpar o contedo da
clula.
A sintaxe do cdigo para definir o contedo limpo da clula :
Range("a2").ClearContents
Nome do objeto
Mtodo
O Excel possui mais de 300 mtodos, que sero escritos da forma: Objeto . Mtodo
Portugus
Name
Nome
ActiveCell
ClulaAtiva
Address
Endereo
Offset
Exemplo de Uso
a = ActiveSheet.Name
ActiveCell.Interior.ColorIndex = 15
a = ActiveCell.Address
Deslocamento ActiveCell.Offset(1,0).Select
a = ActiveSheet.Name
Clulas
Cells.Clear
Limpar
Range("A1").Clear
ActiveSheet
PlanilhaAtiva
Cells
Clear
Resultado do Cdigo
Copiar
Range("A1").Copy
16
Recortar
EntireColumn
ColunaInteira
EntireRow
LinhaInteira
Hidden
Range("A1").Cut
Range("a1").EntireColumn.Delete
Range("A1").EntireRow.Clear
Oculta
Range("A1").EntireRow.Hidden =
True
Bold
Negrito
Range("A1").Font.Bold = True
Font
Fonte
Range("A1").Font.Italic = True
Activate
Ativar
Sheets(1).Activate
Visible
Visvel
Sheets(1).Visible = false
Add
Adicionar
Sheets.Add
Count
Contagem
a = Sheets.Count
Armazena na varivel a a
quantidade planilhas e existentes no
arquivo.
2.3.
17
2.4.
Objeto Range
Permite selecionar uma rea de qualquer tamanho na planilha. Identificando a rea pelo
endereo da Clula. Ex : A1 ou A1:B20
Em todos os exemplos abaixo, o comando iria preencher a rea correspondente com o
nmero 50
Ex1: Selecionar o intervalo A10: B20
Range( A10: B20).Value = 50
18
Ex5: As clulas podem ser referenciadas com intervalos nomeados. Este exemplo
selecionar 2 intervalos nomeados diferentes, Area1 e Area2
Range(Area1, Area2).Value = 50
Objeto Cells
Utiliza essa instruo quando quiser se basear na posio da clula na planilha e no pelo
nome da clula como no objeto Range.
A posio da clula dada primeiro pelo nmero da linha e depois o nmero da coluna
da clula a ser referenciada.
Ex1: Selecionar a clula B1
Cells(1, 2).Select
19
Obs.: Caso no seja especificado qual a clula a propriedade Cells executa em todas
as clulas.
2.5.
A propriedade Offset deve ser empregada para determinar uma referncia de acordo com
um deslocamento, que pode ser um dos quatro sentidos: acima, abaixo, esquerda ou para
direita.
Offset uma propriedade das mais utilizada na criao de macros.
Sintaxe:
ActiveCell.Offset(Linha, Coluna).Propriedade
20
Para selecionar a clula localizada trs linhas a cima da clula ativa, digitem:
ActiveCell.Offset(-3, 0).Select
Para selecionar a clula localizada uma coluna esquerda e uma linha acima da clula
ativa, a linha de cdigo ser:
ActiveCell.Offset(-1, - 1).Select
21
2.6.
Quando se tratar de identificar uma planilha poderemos fazer uso de trs mtodos
diferentes em VBA.
A) Identificando pelo nome da planilha
Utiliza-se o nome da planilha como referncia.
Sintaxe:
Sheets(Despesas).
Digita-se entre aspas e parntese o
nome da planilha
Sheets(2).
Digita-se o nmero da posio da mesma na
guia de planilhas (sem aspas)
22
Obs.: Neste caso a ordem das planilhas sejam alteradas, o mesmo no ocorrer
automaticamente no cdigo VBA, devendo ser feita a alterao no cdigo manualmente
23
2.7.
fcil perceber que um objeto pode possuir diversas propriedades e diversos mtodos.
Nestes casos para facilitar a digitao e simplificao das instrues do cdigo,
poderemos utilizar a instruo With / End With da seguinte forma:
Esta macro alteraria vrias propriedades da mesma rea (cor, tamanho, negrito, itlico). A
instruo With permite agrupar para o objeto ActiveCell a propriedade Font que possui
vrios mtodos .
Ou seja, a instruo With permitiu reduzir a nossa digitao. Todas as propriedades
colocadas dentro da instruo With sero entendidas como da Activecell.Font., que est
no incio da instruo.
24
3.1.
O que faz
1. Exibe uma janela com uma caixa de texto para a insero de dados.
2. Espera que o utilizador introduza os dados e/ou acione um dos botes.
3. Como uma funo, produz um valor final. Este consiste nos dados inseridos pelo
utilizador na forma textual - String.
Sintaxe da InputBox
Obs.: Numa primeira avaliao da funo, pode-se dizer que a mesma possui diversos
parmetros, mas somente o primeiro obrigatrio, sendo que todos os outros quando
ignorados assumem valores atribudos por padro.
Veja a tabela a seguir para entender melhor esses parmetros.
25
Texto Inteno
(Obrigatrio )
Comentrio
Expresso textual exibida como mensagem na janela de input.
A dimenso mxima de 1024 caracteres.
Se pretender construir uma mensagem com mais de uma linha poder utilizar o
caractere Enter - Chr(13). A juno dos elementos que constituem a mensagem
realizada atravs do operador &.
Xpos
(Facultativo)
Exemplo:
"A Soma de 3 com 5 :" & Chr(13) & " 8 "
Titulo da janela de input. Se este for omitido, aparecer por padro o nome da
aplicao.
Expresso inserida por padro na caixa de insero de dados e constituir a
resposta se o utilizador no introduzir outra.
Se este parmetro for omitido, ficar vazia.
Nmero que identifica a distncia horizontal entre o lado esquerdo do cran e a
janela de input. Se este valor for omitido a janela aparecer centrada
horizontalmente.
Ypos
(Facultativo)
Texto de Ttulo
(Facultativo)
Texto Padro
(Facultativo)
Exemplo Prtico
26
3.2.
O que faz
1. Exibe na tela uma janela com uma mensagem.
2. Espera que o utilizador acione um dos botes.
3. Como uma funo produz um valor final. Devolve um nmero inteiro indicando o
boto que foi clicado.
Ttulo Caixa
Texto Caixa
Estilo Boto
Sintaxe da MsgBox
Obs.: Tanto a MsgBox quanto a InputBox, possui diversos parmetros, mas somente o
primeiro obrigatrio, sendo que todos os outros quando ignorados assumem valores
atribudos por padro.
Parmetros da sintaxe
Parmetro
Texto da Caixa
(Obrigatrio)
Comentrio
Expresso textual exibida como mensagem na janela de Msgbox.
A dimenso mxima de 1024 caracteres.
Se pretender construir uma mensagem com mais do que uma linha poder
utilizar o caractere Enter Chr(13)
Exemplo:
Estilo Botes
(Facultativo)
Titulo da Cx
(Facultativo)
27
Na janela ser exibida a mensagem "Rotina Executada com Sucesso!!!", o estilo ser o
vbInformation boto OK e o titulo da janela ser SISTEMA CAVALCANTE.
Estilos da Caixa
Os estilos disponveis so vrios podendo ainda serem combinados. Voc
poder ainda informar os estilos pelo nmero cdigo. A tabela abaixo apresenta
todos os estilos que podero ser utilizados
Cdigos de Botes que podem se utilizados na construo da sintaxe
Estilo do Boto
Cod
Descrio
VbOKOnly
VbOKCancel
VbAbortRetryIgnore
VbYesNoCancel
VbYesNo
VbRetryCancel
VbCritical
VbQuestion
VbExclamation
VbInformation
VbDefaultButton1
VbDefaultButton2
VbDefaultButton3
VbDefaultButton4
0
1
2
3
4
5
16
32
48
64
0
256
512
768
VbApplicationModal
VbSystemModal
4096
28
Resultado da Caixa
Resultado da Caixa
29
4. Utilizando Variveis
Um dos recursos mais importantes na criao de programas so as variveis. A varivel
constitui num espao de memria utilizado para armazenar temporariamente informaes
(dados), que podero ser utilizados para diversos fins.
Toda varivel deve possuir um nome que no pode ter mais de 255 caracteres e necessita
comear por um caractere alfabtico.
aconselhvel que toda varivel seja declarada, para facilitar o entendimento da rotina e
evitar possveis conflitos entre cdigos do sistema.
A Declarao permite identificar a varivel para o sistema e definir qual o tipo informao
ela ir armazenar.
A declarao consiste de inserir a instruo Dim antes do nome da varivel.
30
4.1.
Tipos de Variveis
O tipo de varivel est associado ao gnero (Numero, Texto, Data, etc.) de informao
que ser armazenada.
Tamanho
Descrio
Boolean
2 bytes
Byte
1 Byte
Currency
8 bytes
Date
8 Bytes
Decimal
14 bytes
Double
8 bytes
Integer
2 bytes
Long
4 bytes
Permite armazenar nmeros inteiros entre -2 147 483 648 e 2 147 483 648
Object
4 bytes
Single
4 bytes
String
1 byte por
Permite armazenar conjuntos de caracteres em forma de texto.
caractere
Variant
16 bytes
31
Obs.: Quando no for declarado o Tipo da Varivel, esta assume por padro o tipo
Variant, conforme a tabela de variveis acima.
32
Dim MinhaVariavel
Contras
Aumenta o tempo de execuo o VBA primeiro precisa reconhecer o tipo de dados
com os quais ir trabalhar.
O tipo Variante consome mais memria (uma vez que tem que alocar espao para
qualquer tipo de dados que lhe seja atribudo) 16 bytes mais um byte por caractere
se for String problemas de performance para Sub Rotinas grandes.
33
4.2.
Ir depender de cada caso para que seja definido em qual domnio pertencer a varivel.
1 mbito de Procedimento: O nvel mais baixo. A varivel ter abrangncia apenas na
Sub Rotina aonde ela foi declarada, ou seja, no procedimento onde ela estiver.
2 - mbito de Mdulo: Esse nvel permite que a varivel seja reconhecida e utilizada em
todo o mdulo em que foi declarada. Para isso, a varivel deve ser declara na Seo de
Declaraes, que se localiza na parte superior dos mdulos.
Quando for declarar a varivel em nvel de modulo voc poder tambm utilizar a
instruo Private ao invs de Dim.
34
3 mbito de Projeto: Esse o nvel mais alto. Este nvel disponibiliza a varivel para
ser reconhecida em qualquer mdulo do seu projeto. Isso significa que, se a varivel for
declarada no Mdulo1, ela poder ser utilizada e reconhecida tambm no Mdulo2, assim
como em qualquer outro mdulo do projeto.
Para declarar variveis em nvel de projeto, utilize a instruo Public ou Global ao invs
de Dim tambm na seo de declaraes gerais do mdulo, podendo ser em qualquer
mdulo.
35
5. Estruturas de Deciso
As estruturas de deciso permitem efetivamente programar situaes aonde o sistema
seguir um ou outro caminho. No VBA existem duas formas de montar uma estrutura de
deciso, atravs das estruturas:
IF... Then
5.1.
If (condio) Then
Else
End If
Comentrio sobre a sintaxe acima:
1. Avalia-se a condio, que dever ser uma expresso (Verdadeiro ou Falso) colocada
como primeiro parmetro;
2. Se a condio for Verdadeira, ento(Then) realiza as operaes colocadas no
segundo parmetro, abaixo da linha de teste.
3. Caso contrrio(Else), realiza as operaes que formam o terceiro parmetro, abaixo
da linha do Else.
Obs.: A palavra Else opcional, sendo que no caso de ser omitida, a avaliao negativa
da condio implica uma sada automtica da instruo IF, executando a linha logo abaixo
da instruo End If
If (condio 1) Then
(Instrues a ser realizadas se o teste acima for verdadeiro)
Else
(Instrues a ser realizadas se nenhum teste acima for verdadeiro)
End If
37
Obs.: Poder ser colocado tantos ElseIf na estrutura, quanto for necessrio.
38
5.2.
Para testar vrias condies, na mesma linha do IF voc pode utilizar o operador And
e/ou o operadir Or aps cada condio adicionada.
5.2.1.
Operador And
Este operador far uma conjuno lgica, que determina que todas as condies inseridas
devem ser verdadeiras para que o resultado seja verdadeiro. Se uma das condies for
falsa o resultado ser falso.
Sintaxe da Estrutura IF... Then e And
Else
(Instrues a ser realizadas se uma ou mais condies forem falsas)
End If
39
5.2.2.
Operador Or
Este operador deve ser empregado quando for efetuar uma disjuno lgica, ou seja,
quando pelo menos uma das condies seja Verdadeira. Somente se todas as condies
forem Falsas o resultado ser falso.
Sintaxe da Estrutura IF... Then e Or
Else
(Instrues a ser realizadas se todas as condies forem falsas)
End If
40
6.1.
Do While.... Loop
Do Until Loop
Realiza um loop (Repetio das linhas de cdigo) enquanto a condio da estrutura for
Verdadeira.
A estrutura Do While Loop pode assumir 2 maneiras diferentes. Com o teste da
condio sendo feito no incio ou com o teste no fim da estrutura.
Estrutura 1 - Teste no Incio da estrutura
Do While (condio)
(Instrues a realizar em cada interao)
Loop
Exemplo Prtico
41
J essa forma, executa as instrues pelo menos uma vez visto que ela somente testa a
condio no final da estrutura. Caso seja verdadeira, o loop ser executado novamente
at que a condio seja falsa.
Exemplo Prtico
42
Comentrios do Cdigo:
O Loop ocorrer, ou seja, os cdigos se repetiro, enquanto a clula testada seja
diferente de empty (vazio). Assim que a clula testada estiver vazia o Loop termina,
seguindo a leitura do cdigo para logo abaixo da palavra Loop.
43
6.2.
Exemplo Prtico
44
Exemplo Prtico
Comentrio:
Geralmente a estrutura Do While Loop mais utilizada do que a estrutura
Do Until... Loop , visto que as duas so muitssimo parecidas, sendo a nica
diferena entre elas a lgica empregada.
45
7. Macros Auto-Executveis
As macros auto executveis so aquelas que so executadas com a ocorrncia de um
evento ou ao.
Por Exemplo:
Ao selecionar/ativar uma planilha
Ao abrir um arquivo
Ao fechar um arquivo
Ao salvar um arquivo
Ao selecionar uma clula
Ao inserir dados em uma clula
A maneira de criar uma macro auto-executvel a mesma que criar uma macro comum
que chamada atravs de um boto, a diferena onde dever ser colocada a rotina.
Uma macro comum criada em pgina dentro de um mdulo, as macros autoexecutveis ficaram na pgina de cdigo dentro de uma planilha ou de um arquivo, ir
depender da opo que voc desejar. A seleo do objeto: Worksheet (planilha) ou
Workbook (arquivo) dever ser feita na Janela de Objetos e o evento na Janela de
Procedimentos ou Eventos.
Veja a figura abaixo:
Janela de Procedimento ou
Eventos
Janela de Objetos
46
7.1.
Para criar uma macro auto executvel em planilha voc dever escrever o cdigo na
planilha onde a macro ser executada.
Exemplo de Macro Auto Executvel em Planilha
O exemplo ser feito na chamada Resultado. Ser inserida uma macro ao abrir essa
planilha.
Procedimentos:
1 - Abra o Editor do VBA.
2 - Na Caixa de Projetos ao lado esquerdo da tela, d um duplo clique na planilha de
deseja inserir a macro.(no caso a Resultado)
3 - Na Caixa de Objetos escolha a opo Worksheet
4 - Na Caixa de Eventos escolha a opo Activate
Cx. de Objetos
Cx. de Eventos
Caixa de Projetos
47
Obs.: Essa macro ser executada quando a planilha Resultado por ativada/selecionada e
ela ir desabilitar o cabealho de linha e coluna e tambem exibir a planilha em tela
inteira.
Como desejamos que essas opes sejam somente para a planilha Resultado, ento
devemos criar outra macro para voltar tudo como antes.
Para isso devemos criar outra macro, agora com ela ser executada quando sair dessa
planilha.
Procedimentos da 2 rotina
1 - Na Caixa de Eventos escolha a agora a opo Deactivate
2 - Em seguida escreva o cdigo dentro da Private Sub que apareceu.
Resultado da Macro:
Quando trocar a seleo dessa planilha por outra ou seja desativ-la (Deactivate), o
processo ser o inverso da macro anterior, os cabealhos de linha e coluna voltaro a
aparecer e a tela ser exibida em formato normal.
48
7.2.
Procedimentos:
1 - Abra o Editor do VBA.
2 - Na Caixa de Projetos ao lado esquerdo da tela, d um duplo clique na planilha de
deseja inserir a macro.(no caso a Resultado)
3 - Na Caixa de Objetos escolha a opo Worksheet
4 - Na Caixa de Eventos escolha a opo SelectionChange
5 - Em seguida escreva o cdigo dentro da Private Sub Worksheet_SelectionChange
que apareceu.
49
Resultado da Macro:
Toda vez que digitar algo em clulas da coluna 6 ( F ), aparecer na clula da direita a
data e horrio da digitao.
Obs.: Como voc pode perceber a escolha do evento fundamental, por ele que o
sistema sabe quando a macro dever ser executada. Abaixo apresentado uma tabela
com todos os eventos para planilha.
Tabela de Eventos de Planilha que o VBA disponibiliza
Evento
Descrio
Activate
Deactivate
Change
Ocorre quando alterada uma clula da planilha, seja pelo usurio ou por um
vnculo externo
BeforeDoubleClick
BeforeRightClick
Calculate
FollowHyperlink
SelectionChange
50
7.3.
Caixa de Projetos
Cx. de Objetos
Cx. de Eventos
51
52
Cx. de Objetos
Cx. de Eventos
53
Application.CommandBars("MinhaBarra").Visible = True
Application.CommandBars("MinhaBarra").Visible = False
54
Evento
Descrio
BeforeClose
Open
BeforePrint
BeforeSave
Deactivate
NewSheet
SheetBeforeDoubleClick
SheetBoforeRightClick
SheetCalculate
SheetChange
SheetDeactivate
SheetFollowHyperkink
SheetSelectionChange
WindowActivate
WindowDeactivate
WindowResize
DICA!!
Para que uma macro Auto_Open ou Auto_Close no seja executada,
mantenha pressionada a tecla SHIFT ao abrir ou fechar o arquivo.
55
Exemplo1
Utilizando a funo de planilha SOMA para somar os valores do intervalo A2:A50 e
retornando o resultado na clula A1.
Na planilha:
=SOMA(A2:A50)
No VBA:
Range("a1") = Application.WorksheetFunction.Sum(Range("A2:A50"))
Obs.: A funo Soma chama-se Sum em ingls
Exemplo2
Utilizando a funo de planilha MEDIA para calcular a mdia aritimtica dos valores do
intervalo A2:A50 e D2:D50 e retornando o resultado na clula A1.
Na planilha:
=MDIA(A2:A50 ; D2:D50)
No VBA:
56
Exemplo3
Utilizando a funo de planilha PROCV para localizar o valor contido na clula A5 da
planilha ativa no intervalo das colunas A:B da planilha chamada TABELA DIARIA e
retornando o resultado na clula A1 da planilha ativa.
Na planilha:
=PROCV(A5;'TABELA DIARIA'!A:B;2;FALSO)
No VBA:
Range("A1") = Application.WorksheetFunction.VLookup(Range("A5"), _
Sheets("TABELA DIARIA").Range("A:B"), 2, False)
Ingls
Portugus
Ingls
CONT.VALORES
CountA
ARRED
Round
CONT.NUM
Count
DIA.DA.SEMANA
Weekday
CONT.SE
CountIf
ESCOLHER
Choose
MEDIA
Average
BDSOMAR
DSum
MAXIMO
Max
BDCONTAR
DCount
MINIMO
Min
BDMEDIA
DAverage
SOMASE
SumIf
MENOR
Small
SOMARPRODUTO
SumProduct
MAIOR
Large
PROCV
VLookup
PROJ.LIN
LinEst
PROCH
HLookup
TENDENCIA
Trend
CORRESP
Match
PGTO
Pmt
LOCALIZAR
Seach
TAXA
Rate
INDICE
Index
TIR
Irr
ORDEM
Rank
VPL
Npv
57
9. Formatao no VBA
Voc poder formatar valores textos diretamente no VBA, para isso existem algumas
funes para executar isso.
9.1.
A funo Format possibilita que voc defina o tipo de formatao que deseja, como por
exemplo: apresentar ou no o smbolo da moeda, quantidades de casas decimais, formato
percentual, etc. Format reconhecida por qualquer verso do Office.
Retorna R$15.404,72
Valor = Format (15404.72, "###,###.0000")
Retorna 15.404,7200
Valor = Format (15404.72, "###,##0,0")
Retorna 15.405
Valor = Format (15404.72, "###,###,000")
Retorna 15.405
Valor = Format (0.5, 0.00%" )
Retorna 50,00%
ATENO:
58
9.2.
Exemplos:
Valor = FormatNumber (15.8, 2 )
Retorna 15,80
Valor = FormatNumber (15.8845, 1)
Retorna 15,9
Valor = FormatNumber (15.8, 0)
Retorna 16
9.3.
59
Exemplos:
Valor = FormatPercent (0.58, 1)
Retorna 58,0%
Valor = FormatPercent (0.0058, 3)
Retorna 0,580%
Valor = FormatPercent (1, 0)
Retorna 100%
9.4.
Exemplos:
Valor = FormatCurrency (100 , 2)
Retorna R$ 100,00
Valor = FormatCurrency (100.5 , 2)
Retorna R$ 100,50
Valor = FormatCurrency (100.58 , 0)
Retorna R$ 101
60
10.
Erro de Sintaxe O mais fcil de interceptar, pois enquanto ainda estiver escrevendo
o cdigo aparecer uma mensagem e a cor da linha de cdigo ser alterada para
vermelha.
O objetivo desta parte tratar os dois ltimos tipos de erro: Erro no retorno de
WorksheetFunction e Erro em Tempo de Execuo
Tratar erro em tempo de execuo na realidade antever um possvel erro que poder
ocorrer e dessa forma definir para o sistema o que deve ser feito, para que assim seja
evitado a interrupo da execuo da macro.
10.1.
61
Essa instruo bastante simples e fcil de utilizar, pois assim que o sistema leia a
instruo On Error Resume Next todos os erros dali em diante, at o final da rotina, sero
simplesmente ignorados e assim a execuo no ser interrompida.
Como voc pode perceber essa instruo arriscada, visto que enquanto estamos na
fase de construo de uma rotina importante que erros apaream para que possamos
identific-los e dessa forma corrigi-los. Ento o ideal manter a interrupo dos erros por
algumas linhas de cdigo apenas e depois fazer com que os erros voltem a serem
detectados, para isso voc poder utilizar uma instruo que uma forma de antdo
anterior ou seja, desativa a On Error Resume Next, essa instruo On Error GoTo 0.
No exemplo acima foi desativado a instruo On Error Resume Next duas linhas depois,
incluindo a instruo On Error Goto 0
possvel introduzir essas instrues quantas vezes forem necessrias em uma rotina.
Ateno: Aps o Goto 0(zero) e no a letra O
62
10.2.
Essa instruo permite um tratamento mais sofisticado, podendo por exemplo abrir uma
caixa de mensagem explicando ao usurio o que ocorreu.
Para entendermos melhor necessrio saber que cada erro que ocorre durante a
execuo tem um nmero de identificao. Atravs da instruo Err.Number possivel
detectar o nmero desse erro e direcionar ou avisar o usurio o que foi que aconteceu.
Exemplo
A macro a seguir, o valor de X definido como 10 e Y como resultado de uma InputBox
No caso acima, enquanto o divisor no for 0 (zero), no haver problemas, porm caso
seja informado 0 (zero) o sistema no ter como executar a rotina, e ento ser
interrompida, surgindo a seguinte mensagem:
Observe que nessa mensagem aparece o nmero do erro, no caso 11. O erro de
execuo nmero 11 ento o erro de diviso por Zero. Todos tero um nmero que
voc poder ento utilizar para detectar o que ocorreu.
63
64
11.
Voc poder necessitar em algum momento chamar algum programa executvel a partir
de uma planilha do Excel, como por exemplo, a calculadora do Windows, um arquivo
Word, arquivo do bloco de notas, etc
Nestes casos a forma mais fcil e rpida utilizando o comando Shell
Voc dever inserir o comando Shell e logo em seguida entre aspas () o nome e o
endereo completo do programa que dever ser executado. Voc poder ainda definir a
forma que a janela do programa ser apresentada:
Obs.: Caso no seja informado o tipo da janela, ela ser carregada no formato
vbMinimizedFocus
___________________________________________________
Sub Chama_Calculadora( )
Shell "calc"
End Sub
___________________________________________________
Exemplo 2 - Chamando um arquivo especfico. No caso o arquivo se chama
MinhaAnotacao
Sub Chama_UmArquivoSalvo1( )
Shell "Notepad C:MinhaAnotacao.txt", vbNormalFocus
End Sub
___________________________________________________
65
___________________________________________________
66
12.
O Visual Basic Of Application possui vrias funes que voc poder utilizar em diversas
situaes. As funes no VBA so parecidas com as funes de planilhas, elas podero
ser utilizadas para: arredondar valores numricos, efetuar clculos matemticos, capturar
do sistema horas e datas atuais, manipular e converter seqncias de textos, etc.
12.1.
Funes Matemticas
Funo
Descrio
Exemplo
Abs
Abs ( -5)
Atn
Atn ( 10 ) 1,471127...
Cos
Cos ( 0 )
Exp
Exp ( 2 ) 7,38905...
Fix
Int
5
1
Fix ( 70.8 )
70
Log
Retorna o
nmero.
logaritmo
natural
de
um
Rnd
Rnd( )
Round
Sgn
Sgn ( 25 ) 1
Sgn ( - 25 ) -1
Sgn ( 0 ) 0
Sin
Sin ( 2 )
Sqr
Tan
Log ( 9 ) 2,197224...
Um numero aleatrio
0,909297...
Sqr (16 ) 4
Sqr ( 100 ) 10
Sqr ( 0 )
0
Tan ( 2 ) -2,1850...
67
12.2.
Funo
Descrio
Date
DateDiff
Exemplo
Date Data atual
DateDiff (interval, date1, date2)
DateDiff("m", "01/01/06", "01/01/07") 12
Day
Month
Year
DateSerial(98, 4, 5) 05/04/1998
DateValue
DateValue(Now)
forma de texto
Weekday
Retorna um nmero de 1 a 7
representando o dia da semana de
uma data
WeekdayName
Weekday(Now)
O nmero do dia
da semana atual. Domingo ser 1 assim
em diante at sbado 7.
WeekdayName (weekday, abbreviate,
firstdayofweek)
Now
DateSerial
A data atual em
MonthName
Retorna o nome do ms
Hour
Hour (now)
atual do sistema
Minute
Minute (now)
O numero dos
minutos da hora atual do sistema
68
Time
Timer
TimeValue
TimeSerial
12.3.
Second (now)
O numero dos
segundos da hora atual do sistema
Time A hora atual completa do
sistema (horas, minutos, segundos)
Timer
A quantidade de minutos
existente deste a meia-noite desse dia.
TimeValue("4:35:17 PM") Retorna
16:35:17 em formato hora
TimeSerial(14, 30, 55) 14:30:55
Funes de Converso
Funo
Descrio da Converso
CBool
CByte
CCur
Converte
(Moeda)
CDate
para
tipo
Exemplo
CBool (1)
CBool (0)
True
False
Currency
CDate ("5/8/1989") 05/05/1989
CDbl
CDec
CInt
CLng
CInt (4.5)
CInt (3.5)
CInt (3.6)
CInt (3.5)
4
4
4
4
CLng (25427.45 )
CLng (25427.55 )
25427
25428
69
CStr
CSng
CVar
Val
12.4.
(Texto)
Val ("3588")
3588
Val ("3 58 8") 3588
Val ("35 e 88") 3588
Funes de Informao
Descrio
IsDate
IsEmpty
IsError
IsNull
IsNumeric
IsObject
StrComp
for
for
for
ou
TypeName
Retorna:
Retorna:
Retorna:
Retorna:
-1
0
1
Null
70
12.5.
12.5.1.
Funo: UCase
UCase (string)
Argumentos:
String a seqncia de texto a ser convertido
Exemplo:
_____________________________________________________
Texto = Cavalcante Consultores
Range(A1) = UCase (texto)
_____________________________________________________
Este exemplo converte a seqncia para CAVALCANTE CONSULTORES.
12.5.2.
Funo: LCase
LCase (string)
Argumentos:
String a seqncia de texto a ser convertido
Exemplo:
_____________________________________________________
Texto = Cavalcante Consultores
71
_____________________________________________________
Este exemplo converte a seqncia para cavalcante consultores.
12.5.3.
Funo: StrConv
Converte uma cadeira de caracteres em um formato que pode ser: tudo maisculo, tudo
minsculo ou a apenas a inicial de cada palavra maiscula.
Sintaxe:
StrConv(string, conversion)
Argumentos:
string
seqncia de texto a ser convertido
conversion O tipo de formato para o qual ser convertida a string. Pode ser:
1 (vbUpperCase) - Tudo maisculos;
2 (vbLowerCase) - Tudo minsculos;
3 (vbProperCase) - Primeira letra de cada palavra em maiscula.
Exemplo:
_____________________________________________________
Texto = CAvALcanTE coNSULToReS
Range(A1) = StrConv( texto, vbProperCase)
_____________________________________________________
Este exemplo ir converter a seqncia para Cavalcante Consultores. Ou seja, a
primeira letra de cada palavra em maisculo.
12.5.4.
Funo: Len
Len (string)
Argumentos:
String
72
Exemplo:
_____________________________________________________
Texto = Cavalcante Consultores
Range(A1) = Len (Texto)
_____________________________________________________
Este exemplo faz com que retorne o nmero 22 para clula A1, visto que 22 a
quantidade de caracteres do texto Cavalcante Consultores, incluindo os espaos.
12.5.5.
Funo: Left
Left(string, length)
Argumentos:
string A seqncia de texto a ser convertido
length A quantidade de caracteres a serem extrados
Exemplo:
_____________________________________________________
Texto = Cavalcante Consultores
Range(A1) = Left (Texto , 5 )
_____________________________________________________
Este exemplo faz com que retorne a seqncia Caval para clula A1, visto que foi
pedida a extrao de 5 caracteres a partir da esquerda (Left).
12.5.6.
Funo: Right
73
_____________________________________________________
Este exemplo faz com que retorne a seqncia Consultores para a clula A1, visto que
foi pedida a extrao de 11 caracteres a partir da direita (Right)
12.5.7.
Funo: Mid
Argumentos:
string Seqncia de caracteres onde ser feita a pesquisa
start A posio do primeiro caractere a ser extrado
length A quantidade de caracteres a serem extrados a partir da esquerda
Exemplo:
_____________________________________________________
Texto = Hoje dia 30 de Dezembro de 2005
Range(A1) = Mid (Texto , 8, 6 )
_____________________________________________________
Este exemplo faz com que retorne a seqncia dia 30 para a clula A1, visto que foi
pedida 6 caracteres a partir do 8 caractere da seqncia dada com: Hoje dia 30 de
Dezembro de 2005
74
12.5.8.
Funo: InStr
Sintaxe:
Argumentos:
start Argumento opcional. Define a posio inicial de cada pesquisa. Se omitido, a
pesquisa iniciar na posio do primeiro caractere.
string1 Obrigatrio. Texto de onde ser feita a pesquisado
string2 Obrigatrio. Texto ou a seqncia de caracteres procurada
Exemplo:
_____________________________________________________
Texto = Lanamento dia 25/11/05
Range(A1) = InStr (1, Texto , dia )
_____________________________________________________
Este exemplo faz com que retorne o nmero 12 para a clula A1, visto que 12 representa
a posio onde inicia a seqncia de caracteres procurada dia dentro da cadeia de
caracteres: Lanamento dia 25/11/05.
Obs.: Caso no seja encontrada a seqncia procurada, a funo InStr retornar o valor
0 (zero).
12.5.9.
Funo: InStrRev
Essa funo o contrrio da InStr, ela pesquisa a partir da direita (do final da seqncia)
a posio de uma seqncia de caracteres e retorna o nmero que representa a posio
da primeira ocorrncia da seqncia dentro do texto.
Sintaxe:
Argumentos:
string1 Obrigatrio. Texto de onde ser feita a pesquisa
string2 Obrigatrio. Texto ou a seqncia de caracteres procurada
75
Exemplo:
_____________________________________________________
Texto = "Lanamento dia 25/11/05"
a = InStrRev(Texto, "dia ")
_____________________________________________________
Este exemplo faz com que retorne o nmero 12 para a clula A1, visto que o nmero 12
representa a posio onde inicia a seqncia de caracteres procurada dia dentro da
cadeia de caracteres: Lanamento dia 25/11/05.
Obs.: 1) Caso no seja encontrada a seqncia procurada, a funo InStrRev retornar o
valor 0 (zero).
2) S haver diferena nas pesquisas entre InStr e InStrRev se houver no texto
pesquisado duas ou mais seqncias iguais pesquisadas.
StrReverse ( string )
Argumentos
string Seqncia de caractere que ser invertida
Exemplo:
_____________________________________________________
Texto = planilhas
Range(A1) = StrReverse ( Texto )
_____________________________________________________
76
Este exemplo faz com que retorne a seqncia sahlinalp, que representa o inverso do
texto planilhas, ou seja, planilhas de traz para frente.
Trim
Sintaxe:
Trim ( string )
Argumento:
string Seqncia de caracteres de onde ser excludo os espaos vazios
Exemplo: Trim
_____________________________________________________
Texto = Cavalcante Consultores
Range(A1) = Trim( Texto )
_____________________________________________________
O retorno ser Cavalcante Consultores
Exemplo: LTrim
_____________________________________________________
Texto = Cavalcante Consultores
Range(A1) = LTrim( Texto )
_____________________________________________________
O retorno ser Cavalcante Consultores
77
Exemplo: RTrim
_____________________________________________________
Texto = Cavalcante Consultores
Range(A1) = RTrim( Texto )
_____________________________________________________
O retorno ser
Cavalcante Consultores
Space ( number )
Argumento:
number A quantidade de espaos que deseja inserir
Exemplo:
_____________________________________________________
Range(A1) = Cavalcante & Space( 10 ) & Consultores
_____________________________________________________
Este exemplo retorna para a clula A1 a seqncia Cavalcante
Consultores,
visto que foi pedido para inserir 10 espaos vazios entre Cavalcante Consultores.
78
Argumentos
number
Nmero de vezes que o caractere ser repetido
character O caractere que ser repetido
Exemplo:
_____________________________________________________
Range(A1) = Valor R$ 10.000,00 & String (10, x)
_____________________________________________________
Este exemplo faz com que retorne a seqncia Valor R$ 10.000,00xxxxxxxxxx, ou
seja, repete-se 10 vezes o caractere x aps a seqncia informada.
79
13.
80
13.1.
Como exemplo prtico criaremos algumas funes que o Aplicativo Excel no possui em
sua ferramenta Colar Funo
Exemplo 1
Iremos criar uma funo para calucular o Valor Futuro de uma operao
Simples
a Juros
Procedimentos:
1. Estando em qualquer planilha do Excel clique em Menu Ferramentas Macro
Editor do VBA
81
A
T
E
N
Obs.1 : Foi dado o nome para a funo de VFJS, significando: Valor Futuro a
Juros Simples. O nome poderia ser qualquer outro que voc quisesse, desde
que no possusse espaos e caracteres do tipo: / , ?, % , # etc.
Obs.2 : Os parmetros foram dados como VP, TAXA, PRAZO. Poderiam
tambm ser qualquer outro texto que voc quisesse, desde que tambm no
utilizasse espao e caracteres do tipo: /, ?, %, # etc.
Exemplo 2
Criao da funo Raiz Ensima
nmero a qualquer base.
Procedimentos
Se voc j estiver no Editor do VBA com um mdulo aberto, basta inserir os cdigos
(procedimento de nmero 3 ) da nova funo, abaixo da funo anterior.
Caso voc j tenha fechado o Editor do VBA, execute os procedimento 1 e 2 descritos na
elaborao da funo anterior.
82
13.2.
Uma vez criada as funes no Editor do VBA, necessrio testa-las quando ao seu
funcionamento.
Procedimentos
1. Volte para uma planilha qualquer.
2. Clique em Inserir Funo
3. Escolha a Categoria Definida pelo usurio
4. Observe que as funes aparecero na tela da esquerda com os nomes que voc
definiu para elas.
83
5. Escolha uma delas e clique OK - Para nosso teste escolha a funo VFJS
6. Preencha os argumentos conforme a figura abaixo.
84
13.3.
possvel inserir uma descrio para cada nova funo, possibilitando assim descrever a
funo para as pessoas que iro utiliz-las, dessa forma dando um toque totalmente
profissional.
Alem disso ainda possvel definir em qual categoria a funo criada deve aparecer na
caixa de inserir funo do Excel. Poder ser inserida na categoria: Financeira,
Matemtica Trigonomtrica, Estatstica, Lgica, etc.
Procedimentos
Para inserir a descrio e definir a categoria, necessrio elaborar uma nova rotina em
VBA.
1. Abra o editor do VBA. Caso tenha esquecido como isto se faz, veja nos procedimentos
anteriores.
2. J dentro do Editor do VBA clique em Inserir Mdulo
85
86
13.4.
As funes que voc criou estaro sempre disponveis para uso, desde que o arquivo
onde elas foram criadas e salvas esteja ativo, ou seja, aberto.
Porm possvel disponibilizar as funes no aplicativo Excel atravs da criao e
instalao de um AddIn.
AddIns so suplementos(*.xla) que podem ser disponibilizados para o Microsoft Excel.
Na opo Nome do arquivo: digite um nome que voc desejar para o arquivo.
87
2. Na Caixa Suplementos procure pelo nome do arquivo que voc havia criado o
suplemento e marque a opo do nome do arquivo, logo em seguida clique em OK.
Pronto!! Feito isto voc disponibilizou as funes criadas em VBA neste aplicativo.
88
89
14.
Formulrios so telas criadas para interagir com o usurio, fazendo com que a aplicao
seja customizada, bem mais fcil e segura que aquela em que o usurio atua diretamente
na planilha.
No VBA, a interface dos formulrios so criadas atravs de UserForms, nos quais voc
pode inserir os controles que desejar e colocar botes para responder aos controles,
tornando a aplicao personalizada.
Formulrio
Caixa de Ferramentas
Propriedades do Objeto
90
91
14.1.
A utilizao dos objetos do formulrio muitas vezes requer alteraes em relao a Cor,
Texto, alinhamento, Nome, Etc. Essa alteraes so feitas pela tela de propriedades.
Para alterar as propriedades, selecione o objeto a ser modificado para que a janela
Propriedades exiba as caractersticas desse objeto.
Essa janela encontra-se no canto inferior esquerdo do editor do VBA. Caso essa janela
no aparea, clique em Exibir Janela Propriedades
Por exemplo, caso voc selecione o objeto Label1, na janela de propriedades aparecer
as propriedades desse objeto.
92
14.2.
Obs.: Vale ressaltar que essa uma lista geral de propriedades, e que os objetos
possuem algumas propriedades, no possuindo outras.
Propriedade
Name
Accelerator
AutoSize
AutoTab
AutoWordSelect
Descrio
Nome do objeto. Esse nome far referncia ao objeto no cdigo
a tecla de atalho, fazendo com que o controle seja acessado pelo teclado.
Exemplo Accelerator F : Ao pressionar ALT+F o controle ser acionado
Indica se o objeto ser redimensionado de acordo com o tamanho do seu rtulo,
fazendo com que o texto possa ser lido. Pode ser True ou False
Especifica se haver tabulao automtica ao digitar o nmero mximo de
caractere em um TextBox ou na parte da caixa de texto de um ComboBox,
mudando o foco para outro objeto. Pode ser True (ocorre tabulao) ou
False(no ocorre tabulao)
Especifica o modo de seleo de palavras em um TextBox ou ComboBox. Se
True, ao arrastar o mouse pelos caracteres de uma palavra para selecionar,
quando iniciar a seleo da prxima palavra, ambas ficaro automaticamente
selecionadas. Se False, ao arrastar o mouse para selecionar, somente sero
selecionados os caracteres, no selecionando automaticamente a palavra inteira.
BackColor
BackStyle
BorderColor
BoundColumn
Cancel
Caption
ColumnCount
ControlSource
ControlTypeText
Default
DragBehavior
Enabled
Font
ForeColor
a cor da fonte
Height
HideSelection
93
MatchEntry
MatchRequired
Max
MaxLenght
Min
MouseIcon
O cone personalizado para o mouse . Para que essa propriedade seja vlida
preciso que o valor da propriedade MousePointer seja 99
MousePointer
MultiLine
PasswordChar
Picture
Source
ScrollBars
SmallChange
SpecialEffect
94
14.3.
Aps concluir o formulrio com todos os objetos, voc ir querer cham-lo atravs de um
boto de acionamento na planilha.
Veja o formulrio modelo abaixo. Neste formulrio foi dada a propriedade name como
FrmMeses
Tipo: Rtulo
Tipo: Caixa de Texto
Tipo: Moldura
95
Obs.1: O nome da Sub rotina foi dada como Chama_Formulrio_Meses, mas poderia ser
qualquer outro nome.
Obs.2: A nica linha de cdigo diz o Nome do formulrio seguido da propriedade .Show
Feito isto, basta agora criar um boto de macro na planilha e atribuir a macro
Chama_Formulrio_Meses a ele.
Agora toda vez que for acionado esse boto a tela ir aparecer na planilha.
14.4.
Existem dois objetos para uso em formulrios que permitem fornecer uma relao de itens
em forma de lista.
ComboBox
ListBox
97
98
Propriedade: RowSource
Pronto!!
Agora sempre ir ser mostrado na caixa de a lista de itens da rea nomeada de meses.
99
Caixa de Eventos
100
15.
Uma grande vantagem de se trabalhar com VBA-Excel a opo de criar conexes entre
planilhas Excel e Banco de Dados verdadeiros do tipo Access, SQL Server, Oracle e
tambm de arquivo texto ou do prprio Excel.
O Excel por si s j possui ferramentas que possibilitam importar dados rapidamente,
porm so limitada e no possibilita exportar dados, ou seja, as ferramentas do Excel
acaba sendo de um nico sentido, no caso importao. Criando conexes VBA, voc
poder: importar, exportar, atualizar, incluir, excluir, etc., ou seja, mover registros em todas
as direes possibilitando assim um gerenciamento total de dados sem sair do Excel.
15.1.
O ADO(ActiveX Data Objects) mais novo que o DAO(Data Access Objects), mas ambos
so seqncias de comandos e cdigos para manipular bibliotecas de objetos de banco
de dados isto , colees de objetos que voc pode utilizar no cdigo VBA. Voc pode
utilizar para isso o ADO ou DAO, mas nunca os dois simultaneamente.
Como foi dito ambos os objetos podem ser utilizados, mas voc pode estar perguntando
qual a diferena entre eles e qual o melhor para utilizar??
O DAO tem uma abordagem mais antiga, onde o principal mecanismo de conexo era o
Jet. O mecanismo de banco de dados do Access chamado de mecanismo Jet. e o DAO
otimizado para trabalhar com banco de dados Jet.. O que vale dizer ento que quando
voc est trabalhando com banco de dados Access o melhor objeto de conexo para
utilizar seria o DAO.
Se voc nunca fosse lidar com bancos de dados diferentes daqueles que usam o Jet,
haveria uma razo para utilizar apenas o DAO. Mas a Microsoft tem se distanciado do Jet
no, porm do Access. A Microsoft desenvolveu outros produtos mais novos, entre eles
o SQL Server, que foi planejado para fornecer um mecanismo mais robusto e tratar muito
mais trfego de usurio do que o Jet. e o DAO ineficaz para trabalhar com o SQL Server
entre outros.
Ento voc dever saber trabalhar com um objeto de conexo que possa trabalhar alm
do Access com outros programas tambm, como o prprio SQL Server por exemplo. Esse
objeto ento ser o ADO(ActiveX Data Objects).
Neste nosso treinamento daremos nfase ao objeto ADO pela sua capacidade de
trabalhar tanto com o Access quanto com outros programas de banco de dados mais
novos.
101
15.2.
15.2.1.
Antes de inserir no VBE(Visual Basic Editor) os cdigos de conexo via ADO, voc
primeiro ter que estabelecer uma referencia a biblioteca ADO no seu arquivo. Para isso
v ao VBE e clique em Ferramentas Referncia ...
102
15.2.2.
Detalhes do cdigo:
Conex a varivel para o objeto ADODB.Connection. Poderia ser dado qualquer
nome a varivel ao invs de Conex.
Abrindo a conexo
Depois de ter declarado uma varivel de objeto como ADODB.Connection, voc deve criar
uma nova instncia dela.
103
104
15.2.3.
Para voc poder manipar e gerenciar os registros efetivamente no basta apenas criar e
configurar um objeto de conexo com o banco de dados, como j foi explicado
anteriormente, voc ainda ir necessitar de um outro objeto que possua a biblioteca de
manipulao dos dados. Esse objeto ser um Redordset que agora iremos entender.
Ateno: Voc s poder utilizar um objeto Recordset em uma rotina, quando j tenha
sido criado e configurado uma objeto de Conexo nessa mesma rotina.
Veja o Exemplo
Este exemplo apresenta a criaao de uma conexo ADODB e tambm do Recordset.
Neste tpico iremos apenas nos ater a criao e configurao do Recordset.
Nessa linha declarada uma varivel com o nome de Rs sendo ela um Recordset.
sempre melhor fazer as declaraes de variveis na parte superior da rotina.
Agora a varivel Rs carregada com uma nova instncia de Recordset. Lembre-se, a partir dessa
instruo a varivel de objeto Rs agora um Recordset e necessita ser configurado.
Rs.ActiveConnection = Conex
Aqui definido a conexo ADODB que o Recordset ir utilizar. Foi informado que a conexo ser
o objeto Conex. Lembre-se que no cdigo a varivel de objeto Conex j estaria configurada com
todas as informaes necessrias relacionadas ao Provedor utilizado, Endereo do banco de
dados, Senha de Acesso entre outros.
Rs.Open "TVenda"
105
Muito Importante!!
Um Recordset s poder conectar uma tabela de cada vez. Caso necessite trabalhar
com duas tabelas em uma mesma rotina, voc dever criar outra Connection e um outro
Recordset.
15.2.3.1.
Quando se abre um Recordset, alguns parmetros devem ser configurados para que a
manipulaao dos dados ocorram perfeitamente. Esses parametros so definidos no
mtodo Open do Recordset e apresentam as seguinte propriedades:
Propriedade CursorType
A propriedade CursorType determina se alteraes que outros usurios poderiam fazer
nos registros da fonte aparecem em seu Recordset e se todos os campos e registros so
trazidos para o Recordset quando for aberto.
106
Propriedade LockType
A propriedade LockType permite controlar se e como possvel ao cdigo alterar os
dados na fonte do recordset. H quatro valores possveis:
AdLockBatchOptimistic Todas as edies que seu cdigo poderia fazer no
Recordset so salvas at que um mtodo Updatebatch seja encontrado. Nesse ponto,
as edies so salvas na fonte do Recordset. Isso no feito necessariamente registro
por registro. Voce poderia, por exemplo, editar cada registro no Recordset antes de
uma atualizao fazer com que as alteraes fossem salvas.
AdLockOptimistic Neste caso durante o processo de edio, o registro no
bloqueado e pode ser editado por outros usurios. Ele bloqueado durante a
atualizao de Recordset e ento liberado novamente.
AdLockPessimistic Neste caso outro usurio no pode editar um registro que voc
j comeou a editar at depois de voc ter atualizado o Recordset.
AdLockReadOnly Essa a configurao padro. Ou seja se no for informando
nada essa informaco ser este caso outro usurio no pode editar um registro que
voc j comeou a editar at depois de voc ter atualizado o Recordset.
107
15.3.
Access
Para ficar mais fcil e dinmico o aprendizado, iremos trabalhar com um mesmo arquivo
Access e uma mesma tabela para todos dos casos. As informaes quanto ao arquivo e
suas tabelas esto abaixo:
O nome do arquivo: BaseDados.mdb
Local do Arquivo: C:\Pasta Principal\BaseDados.mdb
Tabelas contidas no arquivo: TFuncionario e TVenda
Tabela: TFuncionario - com seus respectivos campos e registros
108
15.3.1.
Neste caso iremos criar uma rotina para importar todos os registros da tabela TVenda
109
Rs.ActiveConnection = Conex Define a conexo ADO que ser utilizada. Neste caso est
utilizando a Conex - lembre-se que nessa varivel j contem todas as informaes da conexo.
Rs.Open TVenda O Recordset aberto (.Open), e logo em seguida informado a Tabela
do arquivo Access que ser manipulada (TVenda) s pode ser uma tabela por Recordset.
110
15.3.2.
Neste exemplo iremos enviar os registros contidos numa planilha para uma tabela Access.
Para isso voc j deve ter a tabela no Access criada e configurada com seus respectivos
campos. No exemplo a tabela do Access se chama TVenda
Os procedimentos de declarao das variveis e abertura da conexo so os mesmos do
caso anterior, o que ir mudar a partir da Abre o Recordset. Os comentrios desse
cdigo seguem aps a figura.
Cdigo completo da Rotina
111
Rs.ActiveConnection = Conex Define a conexo ADO que ser utilizada. Neste caso est
sendo utilizado a Conex - lembre-se que nessa varivel j contem todas as informaes da
conexo.
Rs.Open TVenda O Recordset aberto (.Open), e logo em seguida informado a Tabela
do arquivo Access que ser manipulada (TVenda) s pode ser uma tabela por Recordset.
112
15.3.3.
Neste exemplo iremos editar registros contidos em uma tabela Access atravs do Excel.
Para isso voc dever ter os registros em uma planilha no Excel. No nosso exemplo, a
tabela do Access se chama TVenda
Os procedimentos de declarao das variveis e abertura da conexo e do Recordset so
os mesmos do caso anterior, o que ir mudar a partir da Localizao e Edio dos
Registros. Os comentrios desse cdigo seguem aps a figura.
113
Existem 2 loops nessa parte do cdigo. O primeiro Loop controla a linha da planilha onde est o
registro a ser editado. O segundo utilizado para fazer a procura do registro na tabela do Access.
Lin = 2
Carrega a varivel Lin com 2, pois o primeiro registro a ser editado est na segunda linha da
planilha.
Do While Cells(Lin, 1) <> Empty
Inicia o primeiro loop o qual dever ser feito enquanto a clula testada da planilha for diferente(< >)
de Empty (Vazio)
Rs.MoveFirst
Move o cursor do Recordset para o primeiro registro (MoveFirst) da tabela do Access.
Do While Not Rs.EOF
Inicia o segundo Loop que deve ser feito enquanto no (Not) for o fim dos registros da
tabela.(Rs.EOF) . EOF quer dizer End Of File ou seja final do Arquivo
If Rs("Fatura") = Cells(Lin, 1) Then
Esse IF testa se a informao contida no campo Fatura daquele registro da tabela Access igual
a informao contida na clula da planilha. Se for igual significa que esse o registro que deve ser
alterado e ento a alterao inicia, campo por campo. Caso contrrio ser executado a instruo
que est aps o End If que a Rs.MoveNext (mover para o prximo registro).
Rs("DtVenda") = Cells(Lin, 2)
Rs("Produto") = Cells(Lin, 3)
Rs("Cliente") = Cells(Lin, 4)
Rs("Cidade") = Cells(Lin, 5)
Rs("Prazo") = Cells(Lin, 6)
Rs("Quant") = Cells(Lin, 7)
Rs("Vendedor") = Cells(Lin, 8)
A etapa da edio dos registros efetivamente. Os campos no Access so identificados pelo seu
nome, ento fundamental que voc conhea a tabela do Access e seus campos para pod-los
identificar.
Exit Do
Essa instruo encerra o loop. Foi colocada visto que uma vez localizado e editado o registro no
necessita que o loop continue.
End If
Encerra o bloco If
Rs.MoveNext
Move o cursor do Recordset para o prximo registro do Access. Essa instruo ser feita at que
se localize o registro que deve ser editado.
Loop
Encerra o a estrutura Loop interna.
Lin = Lin + 1
Incrementa a varivel Lin. Essa varivel controla numero da linha onde est o registro na planilha
Excel.
Loop
Encerra o a estrutura Loop externa.
114
15.3.4.
Neste exemplo iremos excluir registros que esto numa tabela do Access.
Neste cdigo a localizao do registro no Access ser feita pelo nmero da Fatura. A
tabela do Access ser a mesma dos casos anteriores chamada de TVenda .
Os procedimentos de declarao das variveis e abertura do ADODB.Connection e do
Recordset so os mesmos dos casos anteriores. A parte do cdigo onde possui as
instrues para Localizao e Excluso do Registro ser comentado abaixo, porem
muito semelhante do caso anterior.
115
116
15.4.
O procedimento para elaborar uma conexo com uma base SQLServer utilizando o
ADO(ActiveX Data Objects) praticamente igual ao que foi visto no caso do Access.
O que ir ocorrer que a String de Conexo com SQLServer necessita mais informaoes
do que no caso do Access. Isso porque o SQLServer um banco de dados Cliente /
Servidor e necessita de informacoes complementares.
Para montar a String de Conexo voc vai ter que informar:
- Provedor No caso poder ser o SQLOLEDB.1
- Servidor O endereo do seu SQL Server
- Arquivo O nome do arquivo que ser acessado da base SQLServer
- Usuario A identificaao do usuario para o Login
- Senha Senha de acesso do usuario
O Cdigo abaixo mostra a abertura da conexo e do Recordset de um arquivo do
SQLServer, chamado NorthWind.
A tabela para o Recordset desse arquivo foi a
Products.
117
ATENO
118
15.5.
A conexo e importao de dados de outro arquivo Excel pode ser feita utilizando tambm
o ADO(ActiveX Data Objects) mas voc ficar limitado a apenas importar dados sem ter a
possibilidade de enviar dados para o outro arquivo. Em razo desse detalhe usaremos o
objeto DAO(Data Access Objects) para essa conexo, e assim alm de importar, enviar
dados entre arquivos Excel.
Obs.: O objeto ADO e DAO no do suporte para voc editar ou excluir registros de
uma planilha Excel, ficando assim limitado apenas a importar e exportar registros entre
arquivos Excel.
15.5.1.
Como agora voc ir utilizar uma conexo DAO voc primeiro ter que estabelecer agora
uma referncia a biblioteca DAO no seu computador. Para isso v ao VBE e clique em
Ferramentas Referncia ...
Na tela que ir aparecer escolha uma referencia ao Microsoft DAO 3.6 Objects Library
Caso voc no encontre a verso 3.6 em seu computador, voc poder escolher uma
verso mais baixa.
119
15.5.2.
Neste caso iremos criar uma rotina para importar todos os registros localizados da planilha
chamada FUNCIONRIOS de um arquivo chamado BaseDados.xls
DICA:
Ao invs de utilizar o nome da planilha para identificar a rea que ser
importada, voc poder utilizar o nome da rea dado a ela. Dessa forma voc
iria substituir [FUNCIONRIOS$] por simplesmente o Nome de rea, sem
colchetes [ e sem o cifro $
120
15.5.3.
Neste caso iremos criar uma rotina para exportar um registro que est na 2 linha da
planilha ativa, enviando para a planilhas chamada INDICE_DIARIO e um arquivo
chamado BaseDados.xls
Obsero:
Todos os exemplos de conexo apresentados nessa apostila esto disponveis
no CD entregue aos participantes desse treinamento.
121
16.
Assim como pode ser inserida senha de proteo de arquivo e de planilha, possvel
tambm inserir senha no VBE(Visual Basic Editor).
Este procedimento importante para que seus cdigos fiquem protegidos contra
alteraes por outros usurios.
122
123
17.
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Application.EnableCancelKey = xlDisabled
124