Excel 2016 VBA - Módulo I
Excel 2016 VBA -
Módulo I
Excel 2016 VBA -
Módulo I
Créditos
Excel 2016 VBA
-Módulo I
Coordenação Geral Marcia M. Rosa Coordenação EditorialHenrique Thomaz Bruscagin
Autoria
Leonardo de Oliveira Costa
Revisão Ortográfica e Gramatical
Cristiana Hoffmann Pavan
Diagramação
Carla Cristina de Souza
Edição nº 1 | 1794_3_WEB
junho/ 2016
Copyright © Monte Everest Participações e Empreendimentos Ltda.
Todos os direitos autorais reservados. Este manual não pode ser copiado, fotocopiado, reproduzido, traduzido ou convertido em qualquer forma eletrônica, ou legível por qualquer meio, em parte ou no todo, sem a aprovação prévia, por escrito, da Monte Everest Participações e Empreendimentos Ltda., estando o contrafator sujeito a responder por crime de Violação de Direito Autoral, conforme o art.184 do Código Penal Brasileiro, além de responder por Perdas e Danos. Todos os logotipos e marcas utilizados neste material pertencem às suas respectivas empresas.
“As marcas registradas e os nomes comerciais citados nesta obra, mesmo que não sejam assim identificados, pertencem aos seus respectivos proprietários nos termos das leis, convenções e diretrizes nacionais e internacionais.”
Sumário
Informações sobre o treinamento ... 10
Capítulo 1 - Ambiente de programação Excel ... 11
1.1. Introdução a macros e VBA ... 12
1.2. Visual Basic Editor (VBE) ... 13
1.2.1. Janela do Editor do Visual Basic (VBE) ... 14
1.3. Criando sua primeira macro ... 21
1.3.1. Criando uma segunda macro... 27
1.4. Executando uma macro ... 28
1.4.1. Atribuindo uma tecla de atalho ... 28
1.4.2. Botão Executar ... 30
1.4.3. Botões de Controles de Formulário ... 30
1.4.4. Imagens, formas e figuras ... 32
1.4.5. Barra de Ferramentas de Acesso Rápido ... 34
1.4.6. Executar uma macro no ambiente VBE ... 35
1.4.7. A partir de outra macro ... 36
1.4.8. Auto_Open e Auto_Close ... 37
1.4.9. Botões ActiveX ... 38
1.4.10. Inúmeros eventos ... 41
1.4.11. Métodos OnKey e OnTime do objeto Application ... 41
1.4.12. Application.run ... 41
1.5. Segurança de macros ... 41
1.6. Depurando e editando o código ... 44
1.6.1. Depurando o código linha a linha de comando ... 45
1.6.2. Ponto de interrupção (break point) ... 47
1.6.3. Interropendo uma macro durante a execução ... 48
1.7. Conceitos básicos de estruturas e sintaxe ... 48
1.7.1. Entendendo objetos, coleções, hierarquias, propriedades e métodos ... 48
1.8. Sistema de ajuda ... 56
1.9. Pesquisador de objeto ... 57
1.10. Barra de ferramentas VBE ... 58
1.11. Janela Verificação imediata ... 60
Pontos principais ... 61
Teste seus conhecimentos ... 63
Mãos à obra! ... 67
Capítulo 2 - Principais objetos, propriedades e métodos ... 69
2.1. Introdução ... 70
2.2. Objeto Range - Célula(s) ... 70
2.2.1. Fazendo referência ... 70
2.2.1.1. Fazendo referência pelo número de índice ... 72
2.2.1.2. Fazendo referência pelo nome da célula ... 72
2.2.1.3. Utilizando a propriedade ActiveCell ... 73
2.2.1.4. Configurando o objeto Range como uma variável ... 75
Excel 2016 VBA - Módulo I
Sumário
2.2.2.4. End ... 80 2.2.2.5. Offset ... 80 2.2.2.6. ColorIndex ... 81 2.2.2.7. NumberFormat ... 83 2.2.2.8. CurrentRegion ... 832.2.2.9. Address, Column, Row ... 83
2.2.2.10. Borders ... 84 2.2.3. Principais métodos ... 84 2.2.3.1. Select ... 84 2.2.3.2. Clear ... 84 2.2.3.3. ClearContents ... 85 2.2.3.4. ClearFormats ... 85 2.2.3.5. Delete ... 85 2.2.3.6. Insert ... 86 2.2.3.7. Speak ... 86 2.2.4. Coleção Cells ... 86
2.3. Objeto Worksheet - Planilha(s) ... 87
2.3.1. Fazendo referência ... 87
2.3.1.1. Fazendo referência à planilha pelo nome (da aba da planilha) ... 88
2.3.1.2. Fazendo referência à planilha pelo número de índice ... 88
2.3.1.3. Usando o nome do VBA ... 89
2.3.1.4. ActiveSheet ... 90 2.3.2. Principais propriedades ... 90 2.3.2.1. Name ... 90 2.3.2.2. Visible ... 90 2.3.2.3. ScrollArea ... 92 2.3.3. Principais métodos ... 92 2.3.3.1. Select ... 92 2.3.3.2. Delete ... 92 2.3.3.3. Protect / Unprotect ... 93 2.3.3.4. Copy ... 94 2.3.3.5. Move ... 95 2.3.4. Coleção Sheets ... 95
2.4. Objeto Workbook - Arquivo(s) ... 97
2.4.1. Fazendo referência ... 97
2.4.1.1. Fazendo referência pelo nome do arquivo ... 97
2.4.1.2. Fazendo referência pelo número de índice ... 98
2.4.1.3. Fazendo referência por ActiveWorkbook e ThisWorkbook ... 98
2.4.2. Principais propriedades ... 98
2.4.2.1. Name, Path e FullName ... 99
2.4.2.2. Save ... 99 2.4.3. Principais métodos ... 99 2.4.3.1. Close ... 99 2.4.3.2. Open ... 100 2.4.3.3. Save ... 100 2.4.3.4. Protect ... 101 2.4.3.5. Unprotect... 101
Excel 2016 VBA - Módulo I
Sumário
2.5.1.1. Caption ... 102 2.5.1.2. ActiveWindow ... 103 2.5.1.3. ScreenUpdating ... 103 2.5.1.4. DisplayAlerts ... 104 2.5.1.5. EnableCancelKey ... 105 2.5.1.6. DataEntryMode ... 106 2.5.1.7. WindowState ... 106 2.5.1.8. StatusBar ... 107 2.5.1.9. Cursor ... 107 2.5.1.10. Dialogs ... 108 2.5.1.11. Version ... 109 2.5.2. Principais métodos ... 109 2.5.2.1. Quit ... 109 2.5.2.2. Calculate ... 109 2.5.2.3. FindFile ... 109 2.5.2.4. GetOpenFileName ... 110 2.5.2.5. GetSaveAsFileName ... 111 2.5.2.6. OnKey ... 113 2.5.2.7. OnTime ... 117 2.5.2.8. Wait ... 120 Pontos principais ... 121Teste seus conhecimentos ... 123
Mãos à obra! ... 127
Capítulo 3 - Operadores, variáveis, constantes e arrays ... 131
3.1. Operadores ... 132
3.1.1. Operadores aritméticos ... 132
3.1.2. Operadores de comparação ... 133
3.1.3. Operadores lógicos... 133
3.1.4. Operadores de concatenação ... 134
3.1.5. Ordem das operações ... 135
3.2. Variáveis ... 136 3.2.1. Declarando variáveis ... 138 3.2.1.1. Declarando no procedimento ... 141 3.2.1.2. Declarando no módulo ... 144 3.2.1.3. Declarando no projeto ... 145 3.2.1.4. Option Explicit ... 146
3.3. Macros com passagem de parâmetros ... 147
3.4. Constantes ... 147
3.5. Arrays ... 148
3.5.1. Arrays multidimensionais ... 151
3.5.2. Arrays dinâmicos ... 153
Pontos principais ... 154
Teste seus conhecimentos ... 155
Excel 2016 VBA - Módulo I
Sumário
Capítulo 4 - Estruturas de decisão e repetição ... 161
4.1. Introdução ... 162 4.2. Estruturas de repetição ... 162 4.2.1. Do...Loop ... 163 4.2.1.1. Do While...Loop ... 164 4.2.1.2. Do Until...Loop ... 166 4.2.2. While...Wend ... 168
4.2.3. For...Next e For Each...Next ... 169
4.3. GoTo ... 174
4.4. Estruturas de decisão ... 175
4.4.1. If...Then ... 175
4.4.2. If...Then...Else ... 179
4.4.3. If aninhado ... 184
4.4.4. Inserindo várias condições com And e Or ... 186
4.4.5. Select Case ... 189
4.5. Tratamento de erros ... 191
4.5.1. Tipos de erros ... 191
4.5.2. Tratamento em tempo de execução ... 192
4.5.2.1. On Error GoTo line ... 193
4.5.2.2. On Error Resume Next ... 196
4.5.2.3. On Error GoTo 0 ... 196
4.5.3. Erros interceptáveis ... 197
Pontos principais ... 205
Teste seus conhecimentos ... 207
Mãos à obra! ... 211
Capítulo 5 - Funções VBA ... 217
5.1. Introdução ... 218
5.2. Funções matemáticas ... 218
5.3. Funções financeiras ... 219
5.4. Funções de data e hora ... 220
5.5. Funções de texto ... 226
5.6. Funções de conversão ... 232
5.7. Funções de verificação ... 233
5.8. Acessando a biblioteca de funções de planilha do Excel ... 234
5.9. Criando funções personalizadas ... 237
5.9.1. Utilizando as funções criadas ... 238
5.10. Criando suplementos XLAM ... 239
Pontos principais ... 242
Teste seus conhecimentos ... 243
Excel 2016 VBA - Módulo I
Sumário
Capítulo 6 - Interagindo com o usuário ... 251
6.1. MsgBox ... 252
6.1.1. Exibindo uma caixa de mensagem ... 252
6.1.2. Constantes utilizadas nas caixas de mensagem ... 254
6.1.3. Identificando o botão clicado ... 257
6.2. Função InputBox ... 258
6.3. Método InputBox ... 260
6.4. Caixas de diálogo do Excel ... 262
6.5. Eventos mais utilizados e práticos do Excel ... 264
6.5.1. Procedimento de evento ... 267 6.5.2. Eventos do WorkBook ... 270 6.5.2.1. Open ... 271 6.5.2.2. BeforeClose ... 271 6.5.3. Eventos de Worksheet... 271 6.5.3.1. Worksheet_Activate ... 272 6.5.3.2. Worksheet_SelectionChange ... 272 6.5.3.3. Worksheet_Calculate... 272 6.5.3.4. Worksheet_Change ... 273
6.5.4. Eventos com UserForms ... 273
Pontos principais ... 276
Teste seus conhecimentos ... 277
Mãos à obra! ... 281
Capítulo 7 - Objeto UserForm ... 285
7.1. Introdução ... 286
7.2. Criando formulários ... 286
7.2.1. Inserindo controles em um formulário ... 287
7.2.1.1. Caixa de ferramentas ... 288
7.2.2. Configurando as propriedades dos controles ... 290
7.2.3. Configurando a aparência dos controles ... 298
7.3. Controles da Caixa de ferramentas do UserForm ... 299
7.3.1. Exibindo um formulário ... 323
Pontos principais ... 327
Teste seus conhecimentos ... 329
Informações sobre este treinamento
Para o melhor aproveitamento do curso Excel 2016 VBA – Módulo I, é imprescindível ter participado dos cursos Excel 2016 – Módulo I e Introdução à Lógica de Programação, ou possuir conhecimentos equivalentes.
1
9 Introdução a macros e VBA;
9 Visual Basic Editor (VBE);
9 Criando sua primeira macro;
9 Diversas maneiras de executar uma macro;
9 Segurança de macros;
9 Depurando e editando o código;
9 Conceitos básicos de estruturas e sintaxe;
9 Sistema de ajuda;
9 Pesquisador de objeto;
9 Barra de ferramentas VBE;
9 Janela de Verificação Imediata.
Ambiente de
Excel 2016 VBA - Módulo I
1.1. Introdução a macros e VBA
Macro é uma sequência de comandos escritos na linguagem VBA (Visual Basic for Application), armazenada em um módulo no VBE (Editor do Visual Basic), que pode ser executada sempre que você precisar.
No Excel, os arquivos podem ter alto teor de programação, gerando os mais variados tipos de aplicações. O VBA é um subconjunto do VB, desenvolvido para atuar junto com as aplicações do Office. É uma linguagem de programação visual, que utiliza o paradigma de orientação a objetos e eventos. Com o VBA, interagimos com os objetos Excel (Células, Planilhas, Arquivos etc.) através de suas propriedades, métodos e eventos.
EDITOR VBE MACRO(S)
Ambiente de programação Excel
1
Tanto o Excel quanto o VBE podem ser instalados em diversos idiomas, mas o código VBA é sempre no idioma inglês, com padrões americanos (números decimais separados por ponto em vez de vírgula, por exemplo).
As macros têm a função de automatizar tarefas e podem ser criadas com o gravador de macros ou com o editor do Visual Basic que é incorporado ao Excel. Focaremos no Editor do Visual Basic.
1.2. Visual Basic Editor (VBE)
O Editor do Visual Basic, ou VBE, é onde escrevemos, editamos ou deletamos macros. O Excel tem que estar aberto para utilizarmos o VBE.
Habilite a guia Desenvolvedor do Excel. Caso ela esteja oculta, é necessário ativá-la: pela guia Arquivo / Opções / Personalizar Faixa de Opções, marcar a guia Desenvolvedor:
Excel 2016 VBA - Módulo I
Outra maneira de acessar a opção Personalizar a Faixa de Opções é clicando com o botão direito do mouse em um dos nomes das guias do Excel:
Uma das formas de acessar o ambiente VBE é por meio da Guia Desenvolvedor
/ Visual Basic (primeiro botão do grupo Código):
O atalho ALT + F11 é muito utilizado para acessar o ambiente VBE. Para voltar a visualizar a planilha Excel, basta teclar ALT + F11 novamente.
1.2.1. Janela do Editor do Visual Basic (VBE)
Para inserir módulos no Editor do Visual Basic, vá no menu Inserir (do Editor VB) e escolha Módulo:
Ambiente de programação Excel
1
Outra maneira de inserir um módulo é pelo segundo botão da Barra de Ferramentas Padrão do VBE:
Para remover um módulo, clique com o botão direito do mouse sobre o nome do módulo e, então, na opção Remover:
Excel 2016 VBA - Módulo I
Embora não seja o caso, podemos exportar módulos para reutilizá-los posteriormente. Como não estamos trabalhando com importação e exportação de módulos, aqui, clicamos em Não:
É comum chamarmos o módulo de Módulo Padrão para diferenciá-lo do Módulo de Classe, (conteúdo que não será abordado neste treinamento).
Veja a descrição de algumas partes da janela do editor VBA:
JANELA PROJECT EXPLORER JANELA PROPRIEDADES JANELA DO CÓDIGO VBA
Ambiente de programação Excel
1
• Janela Project Explorer
Na janela Project Explorer, temos uma lista com todas as pastas de trabalho, incluindo suas respectivas planilhas, seus módulos padrão, módulos de classe e UserForms contidos nos arquivos abertos.
A janela Project Explorer, ilustrada na imagem a seguir, apresenta três botões e uma área principal, que exibe, em uma estrutura hierárquica, os projetos carregados, incluindo todos os seus itens e referências:
Os três botões da janela Project Explorer são descritos a seguir:
Opção Descrição
Exibir
código Abre a janela Code, utilizada para gravar e editar código
para o item selecionado. Exibir
objeto Abre a janela Objeto para o documento ou UserForm
selecionado. Alternar
pastas Exibe ou oculta as pastas. Quando as pastas ficam ocultas,
Excel 2016 VBA - Módulo I
A tabela a seguir descreve os itens que a área principal pode exibir. Podemos expandir ou contrair a exibição dos itens utilizando os botões de mais (+) e menos (-):
Opção Descrição
Representa um projeto.
Representa um UserForm (arquivos .frm do projeto). Representa um módulo (arquivos .bas do projeto).
Representa um módulo de classe (arquivos .cls do projeto). Representa ActiveX Designers, ou seja, os criadores (arquivos .dsr do projeto).
Representa as referências de outros projetos, definidas com o comando Referências do menu Ferramentas.
Os documentos são representados por ícones diferenciados, de acordo com seu tipo.
• Janela Propriedades
Na janela Propriedades, temos a lista das propriedades do objeto selecionado na janela Project Explorer.
Essas são as três janelas básicas do VBE. Podemos escolher fechá-las (Project
Ambiente de programação Excel
1
O padrão é estar com estas janelas abertas. Caso sua janela do Project Explorer ou a janela Propriedades não esteja aberta, podemos exibi-las pelo menu Exibir do Editor do VBA:
No menu Ferramentas / Opções / Formato do Editor, podemos escolher a fonte e tamanho para as letras do nosso código:
É muito comum para os desenvolvedores trabalhar com as janelas do Excel e do VBE lado a lado, ou restaurar a janela do VBE dentro da planilha. Assim, quando executarmos a macro, podemos ver, ao mesmo tempo, o que está acontecendo na planilha Excel.
Excel 2016 VBA - Módulo I
Para visualizar a janela do VBE dentro da planilha, basta redimensioná-la:
Alguns desenvolvedores preferem ir na Barra de Tarefas do Windows e, com o botão direito do mouse, escolher Mostrar janelas lado a lado:
Ambiente de programação Excel
1
1.3. Criando sua primeira macro
As macros são digitadas dentro de um módulo. Caso necessário, pode-se criar mais módulos além do primeiro, os quais serão disponibilizados na pasta
Módulos. Os módulos subsequentes serão nomeados como Módulo2, Módulo3
e, assim, sucessivamente.
É possível alterar o nome de um módulo seguindo os procedimentos adiante: 1. Clique no menu Exibir do VBE e, em seguida, na opção Janela 'Propriedades'; 2. Digite o nome do módulo dentro da propriedade Name:
Os módulos, além de armazenarem as macros, podem ser utilizados para organizar o projeto. Podemos, por exemplo, criar um módulo em que estarão disponíveis todas as macros referentes à formatação do projeto, e outro módulo em que estarão somente os cálculos realizados:
Excel 2016 VBA - Módulo I
Para criarmos uma macro, devemos seguir os procedimentos adiante: 1. Crie um arquivo no Excel;
2. Abra o editor de Visual Basic pressionando ALT + F11;
3. Crie a pasta Módulos, seguindo um dos procedimentos adiante: • Clique no menu Inserir, opção Módulo;
• Clique com o botão direito o mouse sobre a janela Project Explorer e selecione, no menu de atalho que surgir, Inserir / Módulo.
Então, a pasta Módulos será criada e, dentro dela, teremos o Módulo1 ativo:
4. Dentro do módulo, crie uma macro com o nome Teste. Digite a palavra-chave
Sub, insira um espaço, digite Teste e pressione ENTER. Essa ação faz com que
surjam dois parênteses após o nome da macro. A instrução End Sub surgirá abaixo, indicando o final do procedimento, como mostrado a seguir:
Sub Teste() End Sub
Devemos inserir o procedimento entre as duas instruções, portanto, o cursor deve estar posicionado na linha imediatamente abaixo do nome da macro.
Ambiente de programação Excel
1
5. Opcionalmente, insira um comentário dentro do procedimento. Para tanto, inicie a informação a ser comentada com um apóstrofo ('). Preferencialmente, o comentário deve ser escrito em uma linha anterior ao código a ser comentado, conforme o exemplo da macro Comentario1(), mostrada a seguir. No entanto, também é possível inserir o comentário na mesma linha de um código, o qual está exemplificado a seguir, na macro Comentario2():
Sub Comentario1()
'Teste de seleção de célula Range("a2").select
End Sub
Sub Comentario2()
Range("a2").select 'Teste de seleção de célula End Sub
6. Pressione a tecla TAB para criar um recuo. Isso faz com que o início e o final do procedimento fiquem destacados e o procedimento recuado. É recomendável criar as macros recuando as linhas que devem ser lidas como integrantes de um mesmo bloco de instruções. Perceberemos a importância do recuo, principalmente, na utilização de estruturas de decisões e laços – que veremos nos próximos capítulos;
7. Digite a seguinte linha:
Range (
Depois de digitar o parêntese, poderemos visualizar uma lista de parâmetros e argumentos do objeto, o que, de certa forma, facilita a digitação e diminui as chances de digitarmos argumentos errados. Apenas o argumento que estiver sendo editado no momento aparecerá em negrito, enquanto os nomes dos demais argumentos aparecerão em itálico. Argumentos opcionais aparecem entre colchetes []. O tipo de objeto é mostrado ao final dos nomes dos argumentos, que, neste caso, é As Range.
Excel 2016 VBA - Módulo I
Quando os argumentos retornam um valor específico na sintaxe de alguns métodos e funções, eles devem estar entre parênteses (). Porém, se o retorno tiver que ser ignorado, deve-se excluir os parênteses. Mais adiante, esse assunto será abordado com detalhes.
Argumentos do comando Range
8. Digite o endereço da célula A2 entre aspas, feche o parêntese e digite um ponto, como mostrado a seguir:
Range("A2").
Depois de digitar o ponto, poderemos visualizar uma lista exibindo, em ordem alfabética, os métodos e propriedades do objeto. Devemos lembrar que sempre que digitarmos um ponto depois do nome de um objeto essa lista será exibida. Os métodos são identificados por uma figura verde à esquerda, e as propriedades, pelo símbolo de uma mão.
9. Digite select em letras minúsculas e pressione ENTER para criar uma linha de espaço entre a linha atual e o final da macro.
Em vez de digitar o comando, pode-se clicar na propriedade ou método exibido na lista. Outra opção é digitar a letra inicial do comando para que, automaticamente, seja direcionada para a opção mais próxima contida na lista. Em seguida, basta pressionar a tecla de espaço. O comando será preenchido no código:
Métodos e propriedades do objeto Range
Ambiente de programação Excel
1
É recomendável digitar as palavras-chave em letras minúsculas, pois, quando pressionamos a tecla ENTER e o cursor muda de linha, a primeira letra da palavra-chave obrigatoriamente fica maiúscula. Essa é uma forma de sabermos que digitamos a palavra corretamente.
A macro deve estar da seguinte forma:
Sub Teste()
'Utilizando o objeto Range Range("a2").Select
End Sub
Agora, podemos testar a macro (que tem como objetivo selecionar a célula A2) posicionando o cursor em qualquer lugar do código entre as instruções Sub e
End Sub e pressionando a tecla F5. Então, a macro será executada e poderemos
confirmar isso ativando o Excel e constatando que a célula A2 está selecionada:
Excel 2016 VBA - Módulo I
A macro já está criada, porém, ainda podemos acrescentar mais comandos a ela, por exemplo, para determinar o conteúdo ou valor da célula. Nesse caso, devemos realizar os seguintes passos:
1. Posicione o cursor após a palavra Select e pressione ENTER. Uma nova linha será criada;
2. Determine o conteúdo ou valor da célula selecionada, como a palavra
"Produto", digitando a seguinte linha:
ActiveCell.Value = "Produto"
Em que:
• ActiveCell: Indica que a propriedade ou o método será aplicado na célula ativa (A2);
• Value: É a propriedade que retorna ou define o valor da célula.
A macro contextualizada em nosso exemplo está pronta para ser executada:
Sub Teste()
'Utilizando o objeto Range Range("a2").Select
ActiveCell.Value = "Produto" End Sub
Ambiente de programação Excel
1
1.3.1. Criando uma segunda macro
Crie um arquivo novo em branco na sua pasta com o nome 01 Diversas Maneiras
de Executar uma Macro.xlsm:
1. Utilize Novo Arquivo ou CTRL + O para criar um arquivo em branco;
2. Salve como 01 Diversas Maneiras de Executar uma Macro.xlsm e, na caixa
Tipo, escolha Pasta de Trabalho Habilitada para Macro do Excel:
3. Na célula A1, digite CLIENTES;
4. Acesse o Editor do Visual Basic (atalho ALT + F11) e, depois, insira um Módulo Padrão;
5. Digite o seguinte código:
Essa macro insere o nome do cliente informado pelo usuário na célula da coluna
Excel 2016 VBA - Módulo I
1.4. Executando uma macro
Uma vez criada a macro, existem inúmeras maneiras de executá-la. Veja, a seguir, algumas delas.
1.4.1. Atribuindo uma tecla de atalho
Para atribuir uma tecla de atalho à execução de uma macro, na guia
Desenvolvedor do Excel, no grupo Código, botão Macros, selecione a Macro
e clique em Opções. Ou ainda, utilize o atalho ALT + F8 e clique em Opções. Veja a demonstração a seguir:
Ambiente de programação Excel
1
A partir de agora, quando este arquivo estiver aberto e você utilizar CTRL + tecla de atalho, será executada a macro. Note que, se a tecla de atalho escolhida for maiúscula junto ao CTRL, você deverá pressionar também o SHIFT. Vamos testar com CTRL + K:
Outro detalhe que pode ser importante é que esse atalho sobrepõe atalhos existentes do Windows ou do próprio Excel. Por exemplo, se atribuirmos o atalho CTRL + C, quando este arquivo estiver aberto, em vez de copiar, que é o comportamento padrão, o Excel executará a Macro.
Excel 2016 VBA - Módulo I
1.4.2. Botão Executar
O botão Executar é acessado pela guia Desenvolvedor / grupo Código /
Macros (ou ALT + F8) que abre a caixa exibida a seguir. Então, selecione a
macro e clique em Executar:
1.4.3. Botões de Controles de Formulário
1. Para inserir botões de Controles de Formulário, na guia Desenvolvedor, grupo Controles, escolha Inserir:
Ambiente de programação Excel
1
Botão é o primeiro controle de formulário da primeira linha.
2. Clique no Botão e clique na planilha (em qualquer célula):
3. Selecione a macro que você quer atribuir a esse botão e clique em OK:
Excel 2016 VBA - Módulo I
1.4.4. Imagens, formas e figuras
Na planilha, podemos inserir imagens, formas ou figuras, inclusive, ícones ou figuras da Internet e, depois, atribuir uma macro. Veja, a seguir, mais detalhes:
1. Se optar por inserir uma forma, na terceira guia do Excel, Inserir, no segundo grupo, que é Ilustrações, clique no terceiro botão, Formas, para escolher uma forma. Nesse exemplo, foi escolhida a forma básica Bisel;
Ambiente de programação Excel
1
2. A vantagem, aqui, é que a possibilidade de formatação é maior. Faça a formatação necessária e clique com o botão direito do mouse na opção Atribuir
Macro:
Excel 2016 VBA - Módulo I
1.4.5. Barra de Ferramentas de Acesso Rápido
É possível personalizar a Barra de Ferramentas Acesso Rápido do seu arquivo com botões. Basta clicar com o botão direito do mouse em um dos botões da Barra de Ferramentas de Acesso Rápido, escolher a opção Personalizar Barras
de Ferramentas de Acesso Rápido, selecionar Macros em Escolher comandos em. Veja as imagens demonstrativas:
Em Personalizar a Barra de Ferramentas de Acesso Rápido, a caixa de diálogo exibida na imagem anterior é aberta. No canto superior direito, há a configuração que exibe em quais arquivos aparecerá esse botão. A opção padrão é que o botão na Barra de Ferramentas de Acesso Rápido aparecerá em todos os arquivos. Isso não é bom porque se este arquivo estiver fechado, mas outro arquivo estiver aberto, o botão continuará disponível. E se o usuário clicar no botão, o Excel vai tentar executar a macro, abrindo o arquivo que a contém. Mas há a possibilidade de configurar para que esse botão apareça apenas quando o arquivo que contém a macro estiver aberto.
Ambiente de programação Excel
1
Na parte superior da janela exibida na imagem anterior, na opção Escolher
comandos em, escolha Macros. Embaixo, escolha a macro desejada, clique
em Adicionar e, na parte inferior direita da janela, clique em Modificar para escolher o ícone. Pronto! Veja o resultado e teste:
1.4.6. Executar uma macro no ambiente VBE
Quando estamos desenvolvendo macros, é muito comum as testarmos dentro do próprio VBE. Para isso, basta deixar o cursor "piscando" dentro de qualquer parte da macro e pressionar a tecla F5.
Excel 2016 VBA - Módulo I
Note que, para facilitar a visualização, a linha do código desta macro foi quebrada. Para quebrar uma linha no código, no Editor do Visual Basic (VBE), com o cursor dentro da macro, basta pressionar a tecla F5:
Isso é muito interessante para os desenvolvedores, pois permite ver o resultado de seu código, sua programação e sua macro a partir próprio Editor do Visual Basic.
1.4.7. A partir de outra macro
Podemos executar uma macro a partir de outra macro. Essa é uma prática muito comum nos aplicativos. Para isso, no código, basta usar a palavra-chave Call e o nome da macro a ser executada. Veja o exemplo adiante em que, pela macro
Ambiente de programação Excel
1
A boa prática é usar a palavra-chave Call, porém, na maioria das situações no VBA, ela é facultativa, ou seja, se deixar somente o nome da macro a ser executada, funcionará também. Faça o teste.
1.4.8. Auto_Open e Auto_Close
A macro com o nome Auto_Open é executada automaticamente quando o arquivo é aberto; da mesma forma, Auto_Close é executada quando o arquivo é fechado.
Veja o seguinte exemplo em um novo módulo, o qual testaremos abrindo e fechando o arquivo:
Excel 2016 VBA - Módulo I
Vamos inserir um novo módulo, criar e testar as macros Auto_Open e Auto_
Close, abrindo e fechando o arquivo:
Acrescentamos uma variável e uma constante do escopo Private no topo do módulo, e as duas macros com chamada automática ao abrir e ao fechar o arquivo. Note que são exemplos ainda bem simples, porque o foco ainda não é a macro, mas como rodá-la. Quando o arquivo for aberto, será perguntado o nome do usuário e, ao fechar o arquivo, será exibido um MsgBox com os dizeres: Até Breve mais o nome do usuário.
1.4.9. Botões ActiveX
Os controles ActiveX são objetos mais recentes que os controles de formulários e super programáveis, que têm uma gama bem maior de possibilidades de programação através da quantidade de propriedades e eventos que possuem. Aqui, usaremos o evento Click de um Command_Button.
1. Pela guia Desenvolvedor, no grupo Controles, vá em Controles ActiveX e escolha o primeiro controle: Botão de Comando;
Ambiente de programação Excel
1
3. Altere a propriedade Name para cmdNovoCliente e a propriedade Caption para NOVO CLIENTE. Name é a propriedade mais importante, pois é o nome que será utilizado no código. Caption é o nome que o usuário visualiza;
Outras propriedades muito utilizadas são BackColor (preenchimento de fundo),
ForeColor (cor da letra), Font (características da fonte escolhida), Picture (insere
uma figura no botão) e PicturePosition (configura a posição da figura no botão).
4. Deixe o BackColor branco, no ForeColor, escolha, na paleta, o Azul Escuro. Neste exemplo, a fonte usada é Calibri, Negrito, 12, mas fique à vontade na escolha;
5. Escolha uma imagem na Web e, na propriedade Picture, importe essa imagem para o botão;
6. Por último, configure a propriedade PicturePosition para a opção: 4 –
Excel 2016 VBA - Módulo I
Agora, vamos programar o evento Click deste botão.
7. Aplique dois cliques com o botão esquerdo do mouse e note que, nos eventos, o nome das macros já são definidos, portanto, a preocupação é só programar o que acontecerá quando este evento ocorrer (o clique do usuário neste botão):
8. Chame a macro novoCliente. Para testar o controle ActiveX, na guia
Desenvolvedor, segundo botão do grupo Controles, desabilite o Modo de Design (para programarmos, devemos estar no Modo de Design):
Ambiente de programação Excel
1
1.4.10. Inúmeros eventos
Mais adiante, veremos que, além do Click, temos outras dezenas de eventos. Qualquer ação de mouse ou de teclado feita pelo usuário disparará dezenas de eventos em que se tenha programado alguma macro, que o Excel executará.
MouseMove, por exemplo, é um evento disparado quando o usuário move o
mouse.
1.4.11. Métodos OnKey e OnTime do objeto Application
Podemos configurar uma tecla ou uma combinação de teclas ou, ainda, um horário para a execução de uma macro.
1.4.12. Application.run
O método Run do objeto Application é muito utilizado quando queremos executar uma macro armazenada em outro arquivo. Por exemplo, Application.
Run "nomeDoArquivo.xlsm! nomeDaMacro".
1.5. Segurança de macros
Desde o Excel 2007, os arquivos com macros devem ser salvos com a extensão
.xlsm. O padrão do Excel é .xlsx, mas com esta extensão as macros não são
salvas. Quando for salvar um arquivo novo, ou que passará a ter macros, na guia Arquivo, em Salvar Como, na caixa Tipo, escolha a segunda opção: Pasta
de Trabalho Habilitada para Macro do Excel. Com isso, seu arquivo já passa
Excel 2016 VBA - Módulo I
Ao abrir qualquer arquivo com macros, caso este documento não esteja configurado como confiável, o Excel exibe, por padrão, um aviso de segurança. Ele informa que as macros foram desabilitadas e oferece a possibilidade de habilitá-las, pelo botão Habilitar Conteúdo. Se este arquivo for de sua confiança, clique no botão.
Quando não habilitamos o conteúdo (macros) e tentamos executar qualquer macro do arquivo, ela não será executada pelo Excel e aparecerá o seguinte aviso:
A solução é fechar o arquivo e, ao abri-lo novamente, habilitar as macros. Mas só devemos habilitá-las quando o arquivo for de sua confiança, porque é extremamente fácil para um programador desenvolver um vírus de macro.
Caso você queira tornar seu arquivo confiável, evitando aparecer o aviso para habilitar macros, ao abrir o arquivo, na guia Desenvolvedor, grupo Código, clique na opção Segurança de Macro:
Ambiente de programação Excel
1
Com isso, é exibida uma caixa de diálogo da Central de Confiabilidade, com várias possibilidades de configuração:
Uma das configurações é a seguinte:
• Habilitar todas as macros (não recomendado; códigos possivelmente
perigosos podem ser executados): Essa opção habilita as macros
automaticamente ao abrir o arquivo. Essa configuração torna seu computador vulnerável a códigos potencialmente maliciosos e não é recomendada pela Microsoft.
Note que há várias opções de configurações de segurança. As opções mais usadas são as configurações de Macro e Locais Confiáveis:
Excel 2016 VBA - Módulo I
Uma vez que você habilita o arquivo como confiável, o Excel o abrirá, mesmo com macros, sem exibir o aviso de segurança.
Note que você pode fazer com que que essa configuração seja válida também para as subpastas, tornando-as confiáveis ao marcar a opção: As subpastas
deste local também são confiáveis.
1.6. Depurando e editando o código
Todas as macros são procedimentos. Existem três tipos de procedimentos: Subrotinas (Sub), Funções (Function) e Propriedades (Property). Iremos focar, por enquanto, nas Sub que seguem essa sintaxe básica:
Sub Nome_da_Macro
Instruções ou comandos ou comentários.. End Sub
Ou seja, começam com Sub e terminam com End Sub. O nome da macro não pode ter espaços, nem começar com números ou ser uma palavra reservada do VBA. Também não pode ser o mesmo nome de um módulo.
Ambiente de programação Excel
1
1.6.1. Depurando o código linha a linha de comando
Uma grande habilidade que deve ser desenvolvida é executar sua macro linha a linha, depurar o código. A grande vantagem de executar o código linha a linha (teclando F8) é que você vê o efeito de cada linha de código, ou seja, o resultado que cada linha de código está gerando. Para testar, clique em qualquer parte do código da macro e tecle F8. Surge uma seta amarela e a linha fica realçada em amarelo. A primeira instrução é o início da macro e, na verdade, não executa instrução alguma.
Tecle F8 e acompanhe o passo a passo da macro.
Note que, enquanto a macro está sendo executada passo a passo, o VBE exibe
Excel 2016 VBA - Módulo I
Outra dica importante ao acabar de analisar o código linha a linha, ou terminar de depurá-lo, é evitar permanecer no modo Interromper, ou executar até a última linha da macro (End Sub). Com F8 continua linha a linha e com F5 executa toda a macro de uma vez.
Ao clicar em Redefinir, o Excel sai do modo Interromper, terminando a depuração:
Outra boa dica de depuração é a possibilidade de arrastarmos a seta amarela para a linha de comando que queremos executar. Quando, por exemplo, temos um código bastante grande e queremos determinar uma linha de código específica a ser executada sem a necessidade de executar as linhas anteriores, podemos arrastar a seta amarela até a linha de código desejada. Para testar, inicie o modo de depuração com F8.
Clique e permaneça com o botão esquerdo do mouse em cima da seta amarela e arraste-o para a linha de código que você quer executar.
Ambiente de programação Excel
1
1.6.2. Ponto de interrupção (break point)
Outra maneira bastante usada para intervir na execução de uma macro é o recurso chamado breakpoint ou ponto de interrupção. Estando com o cursor piscando na linha em que queremos interromper ou pausar o código, podemos colocar um ponto de interrupção das seguintes maneiras: teclando F9, ativando o ponto de interrupção no menu Depurar ou, ainda, apenas clicando no canto da coluna ao lado do código:
Clique no canto da coluna ao lado do código tanto para ativar quanto para desativar um ponto de interrupção.
Isso é útil quando queremos que a macro seja executada do início até antes da linha do ponto de interrupção. Coloque um ponto de interrupção e execute a macro:
Excel 2016 VBA - Módulo I
1.6.3. Interropendo uma macro durante a execução
Se quisermos interromper uma macro longa durante sua execução, pressionamos CTRL + Break ou ESC.
Sub contador() Dim c as integer For c = 1 to 1000 Cells(c,1).select Cells(c,1) = c Next c End sub
1.7. Conceitos básicos de estruturas e sintaxe
No VBA, existem 3 tipos de procedimentos: subrotinas (Sub/End Sub), funções personalizadas (Function/End Function) e propriedades dos objetos que podemos criar nos módulos de classe (Property/End Property).
1.7.1. Entendendo objetos, coleções, hierarquias,
propriedades e métodos
No Excel, um objeto é algo que pode ser programado ou controlado. O Excel tem centenas de objetos. Tudo que você pode especificar as características (cor, nomes, valores, largura) e/ou com que pode realizar alguma ação (salvar, abrir, fechar) são objetos; por exemplo: planilha(s), célula(s), um gráfico(s), linha(s), coluna(s), comentário(s), pasta(s) de trabalho, etc.
Cada objeto do Excel tem um conjunto único de propriedades e métodos. Um dos segredos da programação em VBA é manipular os objetos do Excel, através de suas propriedades e métodos. O Excel possui dezenas, centenas, de objetos e cada objeto possui dezenas de propriedades e métodos. Quanto maior sua habilidade em manipular objetos, melhores resultados terá em suas macros. O VBA apresenta diversos objetos, propriedades e métodos diferentes, de modo que seria impossível memorizar todos eles. Na sintaxe de uma função ou
Ambiente de programação Excel
1
Os 4 principais objetos (e que formam a hierarquia básica de objetos Excel) são: • Range: Célula(s); é o objeto mais utilizado. Range("A1");
• Sheet: Planilha(s); Sheets("Plan1");
• Workbook: Pasta(s) de trabalho ou arquivo(s); Workbooks("Vendas.xlsm"). • Application: O Próprio Excel.
Quando representados de forma hierárquica, temos: Application.Workbook.
Sheet.Range.
Dentro do Excel (objeto Application), podemos ter vários arquivos abertos; cada arquivo (Workbook) pode conter uma ou várias planilhas, e cada planilha (Sheet) pode conter várias células (Range).
Gráfico, tabela dinâmica, linha, coluna, comentário são outros exemplos de objetos.
A sintaxe básica do VBA é a seguinte:
OBJETO.PROPRIEDADE
(objeto ponto propriedade) Ou
OBJETO.MÉTODO.
(objeto ponto método)
Do lado esquerdo do ponto, temos um objeto (exemplo 1), uma coleção (exemplo 2) ou uma hierarquia de objetos (exemplo 3). Do lado direito do ponto, temos a propriedade ou o método deste objeto.
• Exemplo 1
Excel 2016 VBA - Módulo I
Neste exemplo, estamos interagindo com o objeto Range (Célula) A1 e determinando sua propriedade Value (conteúdo da célula) como "Clientes".
Em programação, colocamos os textos entre aspas duplas.
• Exemplo 2
Cells.Clear
Neste exemplo, interagimos com a coleção Cells. Uma coleção é um objeto (também possui propriedades e métodos) que representa todos os objetos do mesmo tipo ou, tecnicamente falando, todos os objetos instanciados pela mesma classe. Nesse caso, Cells representa todas as células da planilha. E utilizamos o método Clear, que limpa o objeto, para limpar todas as células da planilha. É muito comum, no início das macros que geram relatórios, o uso de Cells.Clear.
• Exemplo 3
Range("A1").Font.Bold = True
Aqui, estamos usando uma hierarquia de objetos. Range("A1").Font retorna um objeto Font, que representa a fonte do objeto especificado e determinamos a propriedade Bold (Negrito) deste objeto.
O modelo de objetos do Excel possui centenas de objetos e milhares de hierarquias. Uma das poucas vantagens do gravador de macros se apresenta em casos específicos, como em uma empresa em que os administradores queiram que dados de uma base SAP sejam gerados no Excel em Gráficos de Dispersão. Neste caso, como em tantos outros, fica difícil saber com qual objeto, coleção ou hierarquia devemos interagir para obter os resultados esperados pelas macros. Ao fazer manualmente as tarefas e utilizar o gravador de macros, podemos verificar quais objetos, coleções, hierarquias, propriedades e métodos são utilizados. Daí o foco passa a ser adaptar o código gerado para nossa necessidade específica por meio da depuração do código (com F8), bem como da exclusão dos códigos extensos, e muitas vezes desnecessários, que foram gerados, além do acréscimo de loopings e estruturas de decisões.
Ambiente de programação Excel
1
O VBA, como tantas outras linguagens de programação, tem a sintaxe de ponto. Do lado direito fica a propriedade ou o método do objeto e, do lado esquerdo, fica uma hierarquia de objetos: Sheets("Plan1").Range("A1").interior.colorindex =
3, apenas o objeto: Sheets("Plan1").name = "Estoque" ou, ainda, uma coleção: Cells.clear. Lembrando que a coleção também é um objeto, com propriedades
e métodos próprios.
Os objetos, em geral, possuem propriedades e métodos. • Propriedades
Propriedades são descrições, características do objeto:
• O valor de uma célula pode ser determinado ou obtido pela propriedade
Value do objeto Range;
• A largura de uma coluna pode ser determinada ou obtida pela propriedade ColumnWidth do objeto Column;
• O nome de uma planilha pode ser determinado ou obtido pela propriedade Name do objeto Sheets;
• O caminho de um arquivo pode ser obtido pela propriedade Path do objeto Workbook;
• Podemos alterar o título Microsoft Excel da Barra de Títulos do Excel para o nome que especificarmos, utilizando a propriedade Caption do objeto Application.
Veja outros exemplos:
O valor de uma célula é a propriedade Value do objeto Range:
Range("A1").value = 10
O nome de uma planilha é a propriedade Name do objeto Sheet:
Excel 2016 VBA - Módulo I
O nome de um arquivo é a propriedade Name do objeto Workbook:
Msgbox ActiveWorkbook.name
A versão do Excel é a propriedade Version do objeto Application:
Msgbox Application.version
• Métodos
Métodos são ações realizadas no objeto ou pelo objeto:
• Limpamos uma célula com o método Clear do objeto Range; • Excluímos uma planilha com o método Delete do objeto Sheets; • Salvamos um arquivo com o método Save do objeto Workbook; • Saímos do Excel utilizando o método Quit do objeto Application;
• Atualizamos uma tabela dinâmica pelo método Refresh do objeto
PivotTables;
• Exibimos um UserForm pelo método Show do objeto UserForm. Veja outros exemplos:
Para limpar uma célula, use o método Clear do objeto Range:
ActiveCell.Clear
Para excluir uma planilha, use o método Delete do objeto Sheet:
ActiveSheet.Delete
Para salvar um arquivo, use o método Save do objeto Workbook:
Ambiente de programação Excel
1
Para sair do Excel, use o método Quit do objeto Application:
Application.Quit.
O Editor do Visual Basic tem uma série de facilidades para a criação do código VBA. Uma delas é a lista com todas as propriedades e métodos do objeto, que é exibida, na maioria dos casos, ao digitar o nome de um objeto (ou um nome que referência). Em vez de "decorar" o nome de métodos/propriedades, basta selecioná-los em uma lista.
• Referência à célula A1, objeto Range
Os métodos (ações) do objeto Range são identificados pelo ícone verde e as propriedades (características) pelo ícone com a "mãozinha".
Excel 2016 VBA - Módulo I
• Referenciando o primeiro arquivo aberto, objeto Workbook
• Referenciando o arquivo atual, objeto Workbook
A lista exibida é atualizada à medida que você digita o nome da propriedade ou do método do objeto que quer programar. Quando o método ou a propriedade estiver em negrito (selecionado/escolhido), basta pressionarmos a tecla TAB ou espaço que o VBE escreverá o nome completo da respectiva propriedade ou método, sem erros e mais rápido.
Ambiente de programação Excel
1
ActiveWorkbook.Save salva o arquivo atual. ActiveWorkbook faz referência
ao arquivo ativo (objeto) e Save é a ação de salvar (método).
Quando selecionamos um método, caso ele possua argumentos, eles serão exibidos após um espaço:
Aqui, foi utilizado o método Copy da célula A1. Note que, após o espaço, é mostrado o argumento deste método, ou seja, já podemos indicar para onde copiar a célula A1. Por exemplo:
Plan1.Range("A1").Copy Plan2.Range("A1")
Essa linha de código copia o conteúdo da célula A1 da Plan1 atual para a célula
A1 da Plan2.
Quando digitamos um comando incorretamente, o VBE, por padrão, emite uma mensagem e coloca em destaque este comando:
O código correto é:
Range("A1").Value = 10
Note que, na forma correta, usamos a sintaxe do ponto, entre o objeto e sua propriedade. A sintaxe básica do VBA é: OBJETO.PROPRIEDADE ou OBJETO. MÉTODO, ou seja, objeto + ponto + propriedade ou método. Em que a propriedade é a definição de uma característica ou descrição do objeto e o método representa uma ação realizada neste objeto ou por ele.
Excel 2016 VBA - Módulo I
Antes do ponto que segue a propriedade ou o método do objeto referenciado, podemos ter uma coleção (que também é um objeto) ou uma hierarquia de objetos:
Range("A1").Clear
Em que o objeto é Range("A1") e o método é Clear.
Cells.Clear
Em que a coleção é Cells e o método é Clear.
Sheets("Plan2").Range("A1").Clear
Em que a hierarquia é Sheets("Plan2").Range("a1") e o método é Clear.
1.8. Sistema de ajuda
Outra dica importante é a boa prática de acessar a ajuda da própria Microsoft. Deixe o cursor piscando numa instrução VBA e pressione F1, para acessar pela Web a ajuda da própria Microsoft.
Se a ajuda do Excel estiver instalada e você tiver acesso à Internet, terá a melhor "apostila" de VBA Excel, com dicas e exemplos da própria Microsoft:
Ambiente de programação Excel
1
1.9. Pesquisador de objeto
O Pesquisador de objeto é uma ferramenta disponível no VBE que permite localizar objetos por meio da navegação na estrutura hierárquica de objetos da aplicação. Para acessar essa ferramenta, cuja aparência é ilustrada a seguir, basta clicar na opção Pesquisador de objeto do menu Exibir ou pressionar a tecla F2:
A primeira caixa, localizada na parte superior esquerda, quando clicada, exibe uma lista com todas as bibliotecas de objetos disponíveis. Para visualizarmos os objetos do Excel, devemos selecionar a opção Excel. Na segunda caixa, abaixo da primeira, podemos inserir palavras-chave para buscar objetos. Para obter mais informações sobre qualquer item pesquisado, podemos selecioná-lo e pressionar F1.
Excel 2016 VBA - Módulo I
1.10. Barra de ferramentas VBE
No Editor do Visual Basic, temos a barra de ferramentas padrão, localizada abaixo da barra de menus. Ela exibe os principais comandos do programa, descritos na tabela a seguir:
Comando Descrição
(Exibir Microsoft Excel) Retorna para a janela do Excel.
(Inserir UserForm)
Insere um UserForm, um módulo, um módulo de classe ou um procedimento.
(Salvar ...)
Salva a pasta de trabalho.
(Recortar)
Recorta o texto ou o objeto selecionado, movendo-o para a área de transferência.
(Copiar)
Copia o texto ou o objeto selecionado na área de transferência.
(Colar)
Cola o conteúdo da área de transferência.
(Localizar) Abre a caixa de diálogo Localizar.
(Desfazer) Desfaz o último comando executado.
Ambiente de programação Excel
1
Comando Descrição
(Executar Sub/UserForm)
Executa o procedimento no qual o cursor está localizado ou o UserForm.
(Interromper) Pausa a execução do procedimento.
(Redefinir) Interrompe a execução da macro.
(Modo de criação)
Ativa o modo de criação, no qual nenhum evento é disparado.
(Project Explorer) Exibe a janela Project Explorer.
(Janela 'Propriedades')
Exibe a janela Propriedades.
(Pesquisador de Objeto)
Exibe o Pesquisador de objeto.
(Caixa de Ferramentas)
Ativa ou desativa a exibição da caixa de ferramentas. Este botão só é disponibilizado quando um UserForm é selecionado.
(Ajuda do Microsoft VBA)
Excel 2016 VBA - Módulo I
1.11. Janela Verificação imediata
Podemos utilizar a janela Verificação imediata para verificar resultados de variáveis durante a execução de uma macro. Além disso, é possível testar funções dentro desta janela.
No exemplo a seguir, com a utilização do comando debug.print, será enviada, para a janela Verificação imediata, uma informação (data e horário do sistema):
Sub Exemplo()
Debug.Print Date Debug.Print Time End Sub
Após executarmos a macro (tecla de função F5), teremos o seguinte resultado:
Estando com o cursor posicionado dentro da janela Verificação imediata, é possível executar o resultado de uma função. Para isso, basta utilizar o ponto de interrogação (?) seguido da função desejada e pressionar a tecla ENTER. A resposta ao comando aparecerá na linha inferior, conforme o exemplo seguinte:
Ambiente de programação Excel
1
Pontos principais
Atente para os tópicos a seguir. Eles devem ser estudados com muita atenção, pois representam os pontos mais importantes do capítulo.
• O VBA é uma linguagem de programação visual orientada a objetos cujos recursos são semelhantes aos recursos da linguagem Visual Basic;
• As macros têm a função de otimizar rotinas, ou seja, conjuntos de tarefas que são realizadas frequentemente;
• O Editor do Visual Basic, ou VBE, é uma aplicação em que podemos escrever e editar macros em VBA;
• A janela Project Explorer exibe os projetos em uma estrutura hierárquica, incluindo todos os seus itens e referências;
• A hierarquia básica de objetos do Excel é Application.Workbook.Worksheet.
Range;
• O objeto Application é o próprio aplicativo Excel;
• O objeto Workbook é a pasta de trabalho, o arquivo Excel. A coleção de um objeto Workbook é Workbooks, que representa o conjunto de pastas de trabalho abertas no objeto Application;
• O objeto Worksheet é a planilha. Em uma pasta de trabalho, pode ter uma ou várias planilhas, ou seja, dentro de um objeto Workbook podem ter vários Worksheets. A coleção de um objeto Worksheet é Worksheets, que representa o conjunto de planilhas existentes em um Workbook;
• Range refere-se a uma célula ou intervalos de células. A propriedade Cells retorna um objeto Range. A coleção de um objeto Range é Cells, que representa todas as células de uma planilha;
Excel 2016 VBA - Módulo I
• Além desses quatro objetos básicos, temos centenas de outros objetos e coleções, como row, rows, column, columns, comment, comments. A coleção também é um objeto e tem métodos e propriedades. Note que
uma característica da maioria das coleções é o nome no plural;
• Todos os objetos possuem propriedades e métodos e estão organizados em uma hierarquia. Enquanto uma propriedade é uma característica de um objeto, como posicionamento, cor ou tamanho, um método é qualquer ação executada com um objeto;
• O VBA apresenta diversos objetos, propriedades e métodos diferentes, de modo que seria impossível memorizar todos eles. Na sintaxe de uma função ou método, os argumentos vêm separados por vírgulas e são definidos de acordo com seu nome ou posição;
• Uma coleção é um objeto que reúne objetos da mesma categoria;
• Podemos acessar o sistema de ajuda do VBA posicionando o ponto de inserção na palavra do código sobre a qual desejamos informações e, em seguida, pressionando a tecla F1;
• O Pesquisador de objeto é uma ferramenta disponível no VBE que permite localizar objetos por meio da navegação na estrutura hierárquica de objetos da aplicação.
1
Ambiente de
programação Excel
Excel 2016 VBA - Módulo I
1. Onde é possível escrever e editar macros?
2. Para que serve a propriedade de um objeto?
☐ a) Na própria planilha. ☐ b) Na guia Desenvolvedor. ☐ c) No Visual Basic Editor (VBE). ☐ d) Na janela Project Explorer.
☐ e) Na janela Propriedades ou utilizando ALT + F4.
☐ a) Para definir a característica de um objeto. ☐ b) Para executar a macro no modo Interromper. ☐ c) Para definir a ação de um objeto.
☐ d) Objetos não possuem propriedades. ☐ e) Para cancelar a execução de uma macro.
Ambiente de programação Excel
1
3. Qual das alternativas a seguir está correta?4. Qual das alternativas a seguir refere-se ao local em que são exibidos os projetos carregados, em uma estrutura hierárquica, incluindo todos os seus itens e referências?
☐ a) Métodos são utilizados para indicar as ações dos
objetos.
☐ b) Podemos indicar pontos de interrupção com a
utilização da tecla de função F1.
☐ c) Todos os objetos possuem propriedades e métodos e
estão organizados em uma hierarquia.
☐ d) O atalho CTRL + F11 ativa a janela do VBE. ☐ e) Alternativas A e B estão corretas.
☐ a) Janela Propriedades ☐ b) Guia Desenvolvedor
☐ c) Janela Verificação imediata ☐ d) Barra de ferramentas
Excel 2016 VBA - Módulo I
5. Qual das alternativas a seguir está incorreta?
☐ a) O atalho CTRL + R exibe a janela Project Explorer. ☐ b) Na guia Desenvolvedor, encontramos opções para
gravação de macros e acesso ao VBE.
☐ c) As macros têm a função de otimizar rotinas e tarefas que
são realizadas frequentemente.
☐ d) A linguagem VBA não é considerada uma linguagem de
programação.
1
Ambiente de
programação Excel
Excel 2016 VBA - Módulo I
Laboratório 1
A – Conhecendo o Visual Basic Editor e seus componentes
1. Acesse o editor VBE;
2. Caso estejam abertas, feche as seguintes janelas: • Project Explorer;
• Verificação imediata; • Propriedades.
2
9 Objeto Range - Célula(s);
9 Objeto Worksheet - Planilha(s);
9 Objeto Workbook - Arquivo(s);
9 Objeto Application - O próprio Excel.
Principais objetos,
propriedades e
Excel 2016 VBA - Módulo I
2.1. Introdução
No Excel, temos milhares de objetos. Um dos poderes do VBA é manipular esses objetos através de suas propriedades (características) e métodos (ações).
Porém, é de fundamental importância saber fazer referência, ou seja, indicar ao
VBA com qual objeto você quer interagir.
Aqui, vamos ver os principais objetos do VBA, bem como suas principais propriedades e métodos e as formas de referenciar estes objetos.
2.2. Objeto Range - Célula(s)
A seguir, veremos as diferentes maneiras de fazer referência ao objeto Range, quais são suas principais propriedades e seus principais métodos, além da coleção Cells.
2.2.1. Fazendo referência
Inúmeras aplicações em VBA Excel acessam e/ou modificam valores de células de uma planilha. As maneiras mais comuns para acessar uma célula são através do objeto Range e de diversas propriedades que retornam um objeto Range, como Cells, ActiveCell, CurrentRegion, Offset e End.
Vamos fazer uma referência: 1. Feche todos arquivos; 2. Crie um arquivo novo; 3. Habilite para macro;
Principais objetos, propriedades e métodos
2
5. Insira um módulo;
6. Nomeie a propriedade Name deste módulo como Fazendo_Referência:
7. Nesse módulo, digite Sub F_01 e tecle ENTER:
Observe que os parênteses e o End Sub o VBE coloca automaticamente. 8. Insira o código:
9. Deixe o cursor posicionado dentro da macro e a execute com F5 ou F8.
Excel 2016 VBA - Módulo I
2.2.1.1. Fazendo referência pelo número de índice
Também podemos fazer referências utilizando o número de índice. Para fazer referência ao objeto Range pelo número de índice, usamos a propriedade Cells do objeto Application, que retorna um objeto Range. A forma padrão de usar
Cells é informando o número da linha e o número da coluna da célula com a
qual queremos interagir, separados por vírgula e entre parênteses.
Como veremos adiante, a propriedade NumberFormat = "$ #,##0.00" especifica o formato da moeda padrão local (conforme configuração do Windows).
2.2.1.2. Fazendo referência pelo nome da célula
Também podemos referenciar um objeto Range pelo nome da célula, abreviado e entre colchetes.
A propriedade Value é a propriedade padrão do objeto Range. Quando não a especificamos, o VBA subentende. Veja os exemplos a seguir:
• Range("A1").Value = "Clientes" é o mesmo que Range("A1") = "Clientes"; • Cells(1,1).Value = "Clientes" é o mesmo que Cells(1,1) = "Clientes";
Principais objetos, propriedades e métodos
2
• [A1].Value = "Clientes" é o mesmo que [A1] = "Clientes";
• Range("a1").end(xldown).offset(1,0).Value = "José" é o mesmo que
Range("a1").end(xldown).offset(1,0) = "José".
Logo, na prática, é muito comum não utilizar a propriedade Value, pois, em pouquíssimos casos, ela é realmente necessária.
2.2.1.3. Utilizando a propriedade ActiveCell
Também podemos utilizar a propriedade ActiveCell que retorna um objeto
Range que é a célula ativa. A propriedade Offset desloca um número de linhas
e colunas a partir da célula ativa.
Neste exemplo, começamos selecionando a última célula da coluna A.
Range("A1").End(xlDown).Row equivale a você estar com a célula A1 selecionada
e utilizar CTRL + seta para baixo.
Toda vez que quisermos encontrar a última célula preenchida (antes de uma célula em branco) em uma tabela, podemos selecionar a primeira célula e utilizar CTRL + seta pra baixo, que a célula ativa passará a ser a última preenchida. Esta é a propriedade End do objeto Range.
• End(xldown) equivale a CTRL + seta para baixo e faz referência à última célula antes da primeira em branco, no sentido de cima para baixo;
• End(xlup) equivale a CTRL + seta para cima e faz referência à última célula antes da primeira em branco, no sentido de baixo para cima;
Excel 2016 VBA - Módulo I
• End(xltoRight) equivale a CTRL + seta para direita e faz referência à última célula antes da primeira em branco, no sentido da esquerda para direita; • End(xltoLeft) equivale a CTRL + seta para esquerda e faz referência à última
célula antes da primeira em branco, no sentido da direita para esquerda. Por enquanto, usaremos End(xlDown) por ser mais simples, porém, em breve, usaremos End(xlUp) que é mais profissional.
Usamos a função do VBA InputBox para deixar o exemplo mais dinâmico. Ela permite ao usuário entrar com um dado.
Também podemos pedir para guardar, na variável linha, o número da última linha + 1, ou seja, o número da primeira linha em branco; inserir, na célula
linha, coluna 1, o Cliente e, na célula linha, coluna 2, o valor dele. Os dois
códigos têm o mesmo resultado:
Veja outros exemplos de referências ao objeto Range:
• Range("C5:D9,G9:H16") faz referência à uma área múltipla: da célula C5 a
D9 e da G9 a H16;
• Range("A:A") faz referência a toda coluna A; • Range("1:1") faz referência a toda linha 1;
Principais objetos, propriedades e métodos
2
• Range("1:5") faz a referência da linha 1 a linha 5 inteira;
• Range("1:1,3:3,8:8") faz referência às linhas 1, 3 e 8 completas; • Range("A:A, C:C, F:F") faz referência às colunas A, C e F.
2.2.1.4. Configurando o objeto Range como uma variável
Outra maneira de fazer referência a um objeto Range é configurando-o como uma variável. Para esse exemplo, insira uma planilha nova ou comece o código com Sheets.Add:
2.2.2. Principais propriedades
Vamos ver, agora, as principais propriedades do objeto Range, lembrando que elas são características ou descrições do objeto.
2.2.2.1. Value
A propriedade mais utilizada do objeto Range é Value, que representa o conteúdo/valor da célula.
Excel 2016 VBA - Módulo I
Crie uma planilha nova, em branco, com o nome Objeto Range 02.xlsm e digite os seguintes dados na linha 1 e 2. No VBE, insira um módulo com o nome
Principais_Propriedades e o seguinte código:
Caso o programador não coloque a propriedade ou um método em um objeto
Range, o Excel considera como propriedade Value, que é a propriedade padrão
e a mais utilizada deste objeto.
Normalmente, os programadores não colocam a propriedade Value, escrevem apenas o nome do objeto e o Excel entende que é esta propriedade.
2.2.2.2. Font
Principais objetos, propriedades e métodos
2
No VBA, toda vez que repetirmos uma hierarquia de objetos no código em nossas macros, podemos abreviá-la com uso do bloco With / End With:
2.2.2.3. Uso do With / End With
Para cada propriedade que definimos no Excel, é necessário referenciar o objeto ao qual ela será aplicada, mesmo quando o objeto for igual para diversas propriedades. O exemplo a seguir deixa clara a necessidade que existe de referenciar o objeto para cada uma das propriedades. Para a mesma célula, vamos definir um valor, aplicar uma cor à fonte, determinar que o valor desta célula é apresentado em duas casas decimais, aplicar à fonte o estilo negrito e aplicar ao fundo uma outra cor:
Sub Formatar() Range("A1").Value = 24.3 Range("A1").Font.ColorIndex = 4 Range("A1").NumberFormat = "0.00" Range("A1").Font.Bold = True Range("A1").Interior.ColorIndex = 6 End Sub
