Baixe o app para aproveitar ainda mais
Prévia do material em texto
Excel 2013 VBA - Módulo I (online) Cód.: TE 1710/0_EAD 2 Créditos Copyright © Impacta Participações e Empreendimentos Ltda. Todos os direitos autorais reservados. Este material de estudo (textos, imagens, áudios e vídeos) não pode ser copiado, reproduzido, traduzido, baixado ou convertido em qualquer outra forma eletrônica, digital ou impressa, ou legível por qualquer meio, em parte ou no todo, sem a aprovação prévia, por escrito, da Impacta 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.” Excel 2013 VBA - Módulo I (online) Coordenação Geral Marcia M. Rosa Coordenação Editorial Henrique Thomaz Bruscagin Supervisão de Desenvolvimento Digital Alexandre Hideki Chicaoka Produção, Gravação, Edição de Vídeo e Finalização Bruno Michel Vasconcellos de Andrade (Impacta Produtora) Luiz Felipe da Silva Porto (Impacta Produtora) Xandros Luiz de Oliveira Almeida (Impacta Produtora) Roteiro, Edição e Revisão final Alexandre Hideki Chicaoka Beatriz Silva Ramos Cristiana Hoffmann Pavan Guilherme Yuji Kinoshita Luiz Fernando Oliveira Marcos César dos Santos Silva Locução Valdirene Fátima Gamero Diagramação Bruno de Oliveira Santos Carla Cristina de Souza Edição nº 1 | 1710/0_EAD março/2015 Este material é uma nova obra derivada da seguinte obra original, produzida por TechnoEdition Editora Ltda, em Out /2013: Excel VBA – Módulo I Autoria: Beatriz Silva Ramos e Henrique Thomaz Bruscagin Sumário 3 Apresentação ..........................................................................................................................................7 1. Introdução a macros e VBA .........................................................................................................9 1.1. Introdução ao VBA ........................................................................................ 10 1.2. Macros .......................................................................................................... 10 1.3. Editor do Visual Basic .................................................................................... 10 1.3.1. Janela Project Explorer .................................................................................. 15 1.3.2. Janela Verificação imediata ............................................................................ 16 1.4. Objetos ......................................................................................................... 18 1.4.1. Propriedades e métodos ................................................................................ 18 1.4.2. Coleções ....................................................................................................... 19 1.4.3. Hierarquia de objetos .................................................................................... 19 1.5. Elaborando macros no Visual Basic ...............................................................20 1.5.1. Macros em diferentes pastas de trabalho ......................................................21 1.5.2. Pontos de interrupção ................................................................................... 22 1.6. Sistema de ajuda ........................................................................................... 23 1.7. Pesquisador de objeto ................................................................................... 24 Teste seus conhecimentos ...............................................................................................................25 Mãos à obra! ............................................................................................................................................29 2. Programação de macros – Criando macros ..........................................................................31 2.1. Introdução .................................................................................................... 32 2.2. Elaborando macros no Visual Basic ...............................................................33 2.2.1. Inserindo macros sem o gravador .................................................................34 2.2.2. Automacros .................................................................................................. 40 2.2.3. Macros em diferentes pastas de trabalho ......................................................43 2.3. Propriedades e métodos de alguns objetos ...................................................43 2.3.1. Application ................................................................................................... 44 2.3.1.1. Propriedades ................................................................................................. 44 2.3.1.2. Métodos ........................................................................................................ 48 2.3.2. Workbooks .................................................................................................... 48 2.3.2.1. Propriedades ................................................................................................. 48 2.3.2.2. Métodos ........................................................................................................ 49 2.3.3. Worksheets ................................................................................................... 51 2.3.3.1. Propriedades ................................................................................................. 51 2.3.3.2. Métodos ........................................................................................................ 53 2.3.4. Range ........................................................................................................... 55 2.3.4.1. Propriedades ................................................................................................. 56 2.3.4.2. Métodos ........................................................................................................ 57 Teste seus conhecimentos ...............................................................................................................61 Mãos à obra! ............................................................................................................................................65 4 Excel 2013 VBA - Módulo I (online) 3. Programação de macros - Referência de células, formatação de propriedades e uso de caixas .........................................................................................................67 3.1. Referenciando células ................................................................................... 68 3.1.1. Referenciando uma célula ou um intervalo de células ....................................68 3.1.2. Referenciando uma célula ativa ou seleção de células....................................69 3.1.3. Referenciando de acordo com um deslocamento ...........................................69 3.2. Caixas de entrada (InputBox) ........................................................................ 75 3.3. Instruções With e End With ............................................................................ 77 3.4. Formatando propriedades ............................................................................. 80 3.4.1. Cor do preenchimento .................................................................................. 80 3.4.2. Fonte ............................................................................................................ 81 3.4.3. Alinhamento ................................................................................................. 83 3.4.4. Bordas.......................................................................................................... 86 3.4.5. Número ........................................................................................................ 88 3.5. Caixa de mensagem (MsgBox) ....................................................................... 91 3.5.1. Exibindo uma caixa de mensagem ................................................................91 3.5.2. Constantes utilizadas nas caixas de mensagem ............................................94 3.5.3. Identificando o botão clicado ........................................................................ 96 3.6. Método GetOpenFilename ............................................................................. 97 3.7. Método GetSaveAsFilename ........................................................................... 99 3.8. Caixas de diálogo do Excel............................................................................ 100 3.8.1. Controlando a exibição dos itens da janela ...................................................103 3.8.2. Operador NOT .............................................................................................. 105 3.8.3. Trabalhando em linhas e colunas inteiras ......................................................107 3.8.3.1. Inserindo e excluindo linhas, colunas e células .............................................108 3.8.3.2. Ajustando a largura e a altura de linhas e colunas .........................................108 Teste seus conhecimentos ...............................................................................................................109 Mãos à obra! ............................................................................................................................................113 4. Operadores e variáveis .................................................................................................................117 4.1. Introdução .................................................................................................... 118 4.2. Operadores ................................................................................................... 118 4.2.1. Operadores aritméticos ................................................................................. 118 4.2.2. Operadores de comparação........................................................................... 119 4.2.3. Operadores lógicos ....................................................................................... 120 4.2.4. Operadores de concatenação ........................................................................ 121 4.2.5. Ordem das operações ................................................................................... 122 4.3. Variáveis ....................................................................................................... 122 4.3.1. Declarando variáveis ..................................................................................... 124 Sumário 5 4.3.1.1. Declarando no procedimento ........................................................................126 4.3.1.2. Declarando no módulo .................................................................................. 129 4.3.1.3. Declarando no projeto .................................................................................. 130 4.3.1.4. Option Explicit .............................................................................................. 131 Teste seus conhecimentos ...............................................................................................................133 Mãos à obra! ............................................................................................................................................137 5. Estruturas de decisão e repetição ............................................................................................139 5.1. Introdução .................................................................................................... 140 5.2. GoTo ............................................................................................................. 140 5.3. If e suas estruturas ....................................................................................... 141 5.3.1. If...Then ........................................................................................................ 141 5.3.2. If...Then...Else ............................................................................................... 145 5.3.3. If aninhado ................................................................................................... 151 5.3.4. Inserindo várias condições com And e Or ......................................................152 5.4. Select Case ................................................................................................... 155 5.5. Loop e suas estruturas .................................................................................. 157 5.5.1. Do...Loop ...................................................................................................... 157 5.5.1.1. Do While...Loop ............................................................................................. 158 5.5.1.2. Do Until...Loop .............................................................................................. 160 5.6. While...Wend ................................................................................................. 163 5.7. For...Next e For Each...Next ........................................................................... 164 Teste seus conhecimentos ...............................................................................................................169 Mãos à obra! ............................................................................................................................................173 6. Funções ................................................................................................................................................179 6.1. Introdução .................................................................................................... 180 6.2. Funções matemáticas .................................................................................... 180 6.3. Funções de data e hora ................................................................................. 181 6.4. Funções de tratamento de strings .................................................................187 6.5. Funções de conversão ................................................................................... 193 6.6. Utilizando funções de planilha do Excel ........................................................194 6.7. Criando funções ............................................................................................ 197 6.7.1. Utilizando as funções criadas ........................................................................198 6.8. Utilizando funções em diversas pastas de trabalho .......................................199 6.9. Assistente de Função .................................................................................... 202 Teste seus conhecimentos ...............................................................................................................205 Mãos à obra! ............................................................................................................................................209 6 Excel 2013 VBA - Módulo I (online) 7. Formulários (UserForms) ..............................................................................................................213 7.1. Importância dos formulários ......................................................................... 212 7.2. Criando formulários ...................................................................................... 212 7.2.1. Inserindo controles em um formulário ..........................................................213 7.2.1.1.Caixa de ferramentas .................................................................................... 214 7.2.2. Configurando as propriedades dos controles ................................................217 7.2.3. Configurando a aparência dos controles .......................................................223 7.2.4. Aplicando códigos aos controles ...................................................................224 7.2.5. Elaborando um formulário ............................................................................ 229 7.2.6. Exibindo um formulário ................................................................................ 257 Teste seus conhecimentos ...............................................................................................................261 Mãos à obra! ............................................................................................................................................265 8. Eventos não associados a objetos ...........................................................................................267 8.1. Introdução .................................................................................................... 268 8.2. Método OnKey .............................................................................................. 268 8.3. Método OnTime ............................................................................................ 272 Teste seus conhecimentos ...............................................................................................................275 Mãos à obra! ............................................................................................................................................279 9. Variáveis arrays ................................................................................................................................281 9.1. Arrays ........................................................................................................... 282 9.1.1. Declarando arrays ......................................................................................... 283 9.1.2. Arrays multidimensionais .............................................................................. 285 9.1.3. Arrays dinâmicos .......................................................................................... 287 Teste seus conhecimentos ...............................................................................................................289 Mãos à obra! ............................................................................................................................................293 10. Tratamento de erros ....................................................................................................................295 10.1. Introdução .................................................................................................... 296 10.2. Tipos de erros ............................................................................................... 296 10.3. Erros interceptáveis....................................................................................... 297 10.4. Tratamento em tempo de execução ..............................................................303 10.5. Depurador do VBA ........................................................................................ 308 10.5.1. Pontos de interrupção ................................................................................... 308 10.5.1.1. Janela Verificação imediata ........................................................................... 313 10.5.1.2. Janela Inspeções de variáveis ........................................................................ 316 Teste seus conhecimentos ...............................................................................................................319 Mãos à obra! ............................................................................................................................................323 Apresentação Excel 2013 VBA - Módulo I (online) 7 Apresentação Bem-vindo! É um prazer tê-lo como aluno do nosso curso online de Excel 2013 VBA - Módulo I. Se você pretende otimizar suas planilhas eletrônicas no Excel por meio dos recursos oferecidos pelo VBA (Visual Basic for Applications), este é o curso ideal. Aqui você poderá se aprimorar na criação de funções, formulários, cadastros para banco de dados, macros e objetos. O VBA é uma linguagem de programação visual orientada a objetos que oferece recursos semelhantes aos da linguagem Visual Basic. Com o Excel e o VBA, você é capaz de criar macros para aperfeiçoar tarefas realizadas frequentemente, fazendo com que várias ações sejam executadas em uma determinada ordem através de um único atalho de teclado, por exemplo. Para obter um bom aproveitamento do curso online de Excel 2013 VBA - Módulo I, é importante que você tenha participado do nosso curso de Excel 2013 - Módulo II e Introdução à Lógica de Programação, ou possua conhecimentos equivalentes. Bom aprendizado! Como estudar? Este curso conta com: Videoaulas sobre os assuntos que você precisa saber no curso de Excel 2013 VBA - Módulo I. Parte teórica, com mais exemplos e detalhes para você que quer se aprofundar no assunto da videoaula. Exercícios de testes e laboratórios para você pôr à prova o que aprendeu. Material de apoio para você testar os exemplos das videoaulas e fazer os laboratórios. Assista! Estude! Material de Apoio! Pratique! Leitura complementar Introdução a macros e VBA 9 Introdução ao VBA; 9 Macros; 9 Editor do VB; 9 Objetos; 9 Elaborando macros no VB; 9 Sistema de ajuda; 9 Pesquisador de objeto. 1 10 Excel 2013 VBA - Módulo I (online) 1.1.Introdução ao VBA Nesta leitura, abordaremos a aplicação dos recursos do VBA no Excel. O VBA, um acrônimo para Visual Basic for Application, é uma linguagem de programação visual orientada a objetos cujos recursos são semelhantes aos recursos da linguagem Visual Basic. 1.2.Macros As macros têm a função de otimizar rotinas, ou seja, conjuntos de tarefas que são realizadas frequentemente. Para exemplificar, podemos utilizar macros para aperfeiçoar rotinas de formatação ou filtragem de dados no Excel, fazendo com que várias ações sejam executadas em uma determinada ordem por meio de um único atalho de teclado, simplificando um processo que é frequentemente executado e tornando-o muito mais rápido. As macros podem executar rotinas das mais simples às mais complexas e podem ser criadas com o gravador de macros ou com o editor de Visual Basic que é incorporado ao Excel. 1.3.Editor do Visual Basic O Editor do Visual Basic, ou VBE, é uma aplicação em que podemos escrever e editar macros em VBA. Apesar de ser uma aplicação independente, é necessário que o Excel esteja aberto para utilizarmos o VBE. Para acessarmos o VBE, basta clicar na guia Desenvolvedor e, em seguida, no botão Visual Basic do grupo Código, ou utilizar o atalho ALT + F11. Para voltar à tela do Excel, podemos pressionar ALT + F11 novamente. Introdução a macros e VBA 11 A imagem a seguir ilustra a janela do VBE: Janela Código Janela Propriedades Janela Verificação imediata Project Explorer Barra de menus Barra de ferramentas Para ativar a guia Desenvolvedor, acesse a guia Arquivo e clique em Opções. Em seguida, basta clicar em Personalizar Faixa de Opções e ativar o item Desenvolvedor, contido na caixa Personalizar a Faixa de Opções. 12 Excel 2013 VBA - Módulo I (online) • Barra de menus A barra de menus do VBE possui aparência semelhante às barras de menus que encontramos na maioria dos programas. Por meio dos menus, temos acesso aos comandos que podemos utilizar com os componentes exibidos no VBE. • Barra de ferramentas padrão A barra de ferramentas padrão, localizada abaixo da barra de menus, 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 (formulário), 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. Introdução a macros e VBA 13 Comando Descrição (Refazer) Refaz o último comando desfeito. (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) Abre a ajuda do VBA. 14 Excel 2013 VBA - Módulo I (online) • Janela Project Explorer Exibe todas as pastas de trabalho do Excel atualmente abertas. A exibição é feita em uma estrutura hierárquica que inclui outros itens aptos a serem adicionados no VBE, como módulos ou formulários. Caso essa janela não esteja visível, para exibi-la, clique no comando Project Explorer do menu Exibir, clique no botão Project Explorer ( ) ou utilize o atalho CTRL + R. • Janela Propriedades Exibe as propriedades de um objeto selecionado na janela Project Explorer. Caso essa janela não esteja visível, clique na opção Janela ‘Propriedades’ do menu Exibir, clique no botão Janela ‘Propriedades’ ( ) ou utilize o atalho F4. • Janela Código Exibe o código VBA para o item que é selecionado com um clique duplo na janela Project Explorer. Essa janela também é exibida quando selecionamos um item na janela Project Explorer e clicamos no botão Exibir código ( ). • Janela Verificação imediata Utilizada para executarmos instruções VBA diretamente ou para depurarmos o código. Caso essa janela não esteja visível, clique na opção Janela ‘Verificação imediata’ do menu Exibir ou utilize o atalho CTRL + G. Introdução a macros e VBA 15 1.3.1. Janela Project Explorer 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: • 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, seus conteúdos ainda ficam visíveis. 16 Excel 2013 VBA - Módulo I (online) A lista 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 (-): • : 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. 1.3.2. 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 da janela Verificação imediata. No exemplo a seguir, com a utilização do comando debug.print, será enviada uma informação (data e horário do sistema) para a janela Verificação imediata, veja: Sub Exemplo() Debug.Print Date Debug.Print Time End Sub Introdução a macros e VBA 17 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: 18 Excel 2013 VBA - Módulo I (online) 1.4.Objetos Os objetos são partes integrantes de um aplicativo. No caso do Excel, são exemplos de objetos os gráficos, as planilhas e as células, entre muitos outros que podem ser controlados pelo VBA. Todos os objetos possuem propriedades e métodos e estão organizados em uma hierarquia. Os conceitos de propriedade, método e hierarquia serão abordados a seguir. 1.4.1. Propriedades e métodos Uma propriedade é uma característica de um objeto, como posicionamento, cor ou tamanho. Podemos transportar esse conceito para qualquer objeto do mundo real; uma bola, por exemplo, possui valores de propriedades como tamanho, cor e peso. A sintaxe utilizada para definir um valor de propriedade é NomeDoObjeto.Propriedade = ValorDaPropriedade. Assim, se o objeto “bola” do exemplo anterior pudesse ser gerenciado pelo Visual Basic, sua propriedade “cor” poderia ser definida com a sintaxe bola.cor = amarelo. Utilizando um exemplo de objeto do Excel, podemos definir o valor 15 para célula B2 com a sintaxe Range(“B2”).Value = “15”, em que Range(“B2”) é um objeto, no qual consta a célula B2, e Value é o nome da propriedade que define o conteúdo de uma célula. Para obter o valor de uma propriedade de um objeto, a sintaxe é NomeDoObjeto.Propriedade. Assim, utilizando o exemplo da célula B2, para obter seu valor como retorno, a sintaxe é Range(“B2”).Value. Um método é qualquer ação executada com um objeto. Transferindo esse conceito para o objeto “bola”, exemplificado anteriormente, podemos dizer que jogar, encher e esvaziar são métodos aplicáveis ao objeto. A sintaxe utilizada para um método é NomeDoObjeto.Método. Assim, se o objeto bola do exemplo anterior pudesse ser gerenciado pelo Visual Basic, seu método “jogar” utilizaria a sintaxe Bola.Jogar. Utilizando um exemplo de objeto do Excel, o código que limpa o conteúdo da célula B2 é Range (“B2”).ClearContents, em que ClearContents é o método responsável por limpar o conteúdo. Introdução a macros e VBA 19 1.4.2. Coleções Uma coleção é um objeto que reúne objetos da mesma categoria. Podemos entender esse conceito utilizando exemplos do mundo real, como um edifício residencial, que é um objeto que reúne vários objetos que pertencem à mesma categoria, os apartamentos. No Excel, existem diversas coleções de objetos, como a coleção de células de uma planilha, a coleção de planilhas de uma pasta de trabalho e a coleção de pastas de trabalho abertas. Todas essas coleções são úteis quando construímos um código que faz referência a vários objetos de uma mesma categoria. Podemos saber quantos objetos existem em uma coleção por meio de sua propriedade Count. Assim, para saber quantas linhas existem na coleção Rows, podemos utilizar a sintaxe Rows. Count. Para exibir esse resultado na célula A1, por exemplo, utilizamos Range(“A1”).Value = Rows.Count. Podemos fazer referência a um objeto de uma coleção mencionando seu número, como em Sheet(1), ou seu nome, como em Sheets(“Sheet 1”). 1.4.3. Hierarquia de objetos O Excel, assim como outras aplicações, possui vários objetos organizados de forma hierárquica. No topo da hierarquia desses objetos está o próprio Excel. Assim, temos um objeto principal, a própria aplicação, que contém vários outros objetos que, por sua vez, incluem outros, e assim sucessivamente. Essa estrutura é bemcomplexa por conter muitos objetos, porém, os objetos mais utilizados representam um número pequeno; a maioria dos objetos disponíveis raramente é utilizada. Se quisermos alterar o estilo da fonte da célula A2, já estando posicionados na planilha desejada, podemos utilizar o seguinte comando: Sub Alterafonte1() Range(“a2”).Font.Name = “tahoma” Range(“a2”).Font.ColorIndex = 3 End Sub Automaticamente, o Excel identifica que o comando será executado na planilha selecionada do arquivo em questão. 20 Excel 2013 VBA - Módulo I (online) Deixando explícita a hierarquia dos objetos, podemos atingir o mesmo objetivo com o comando adiante: Sub alterafonte2() Application.Workbooks(“pasta1.xlsm”). _ Worksheets(“plan1”).Range(“a2”).Font.Name = “tahoma” End Sub Podemos observar a sequência hierárquica de objetos que antecedem o objeto Range: Application (Aplicativo Excel ) Workbooks (Pastas de trabalho) Worksheets (Planilhas) Range (Células) 1.5.Elaborando macros no Visual Basic Para criar uma macro utilizando o Visual Basic, você deve seguir os passos adiante: 1. Clique na guia Desenvolvedor e, em seguida, no comando Visual Basic do grupo Código. A janela do editor do Visual Basic será aberta; 2. Clique no comando Módulo do menu Inserir, caso necessário (os módulos são criados de forma automática para todas as planilhas da pasta de trabalho); 3. Digite ou copie o código de macro na janela Código do módulo; 4. Pressione F5 para executar a macro a partir da janela de módulo. Introdução a macros e VBA 21 1.5.1. Macros em diferentes pastas de trabalho Quando duas ou mais pastas de trabalho estão abertas, estas são exibidas pelo VBE na janela Project Explorer. Para visualizar as macros de qualquer pasta de trabalho aberta, basta clicar na pasta Módulos da pasta de trabalho correspondente. Para visualizar o código do módulo, devemos clicar duas vezes sobre ele, ou selecioná-lo e clicar no botão Exibir código, exibido na parte superior da janela Project Explorer. Contas.xlsm Pasta1.xlsx 22 Excel 2013 VBA - Módulo I (online) 1.5.2. Pontos de interrupção Pontos de interrupção são pontos do código em que a ocorre a interrupção da execução das instruções de um procedimento. Podemos definir pontos de interrupção para tornar a análise de erros e a depuração do código mais fáceis. Para inserir ou remover um ponto de interrupção, você deve posicionar o ponto de inserção na parte do código em que deseja interromper a execução e, em seguida: 1. Clique na opção Ativar/Desativar pontos de interrupção do menu Depurar; 2. Pressione F9; 3. Clique no botão Ativar/Desativar pontos de interrupção ( ) da barra de ferramentas Depurar ou clique ao lado da linha do código, na barra indicadora de margem. Para acessar a barra de ferramentas Depurar, clique no menu Exibir e, em seguida, clique em Barra de ferramentas e Depurar: É importante lembrarmos que a interrupção sempre ocorrerá na primeira instrução da linha caso esta contenha várias instruções separadas por dois-pontos (:). A formatação aplicada às linhas que contêm pontos de interrupção é definida na guia Formato do editor da janela Opções, acessada por meio do comando Opções do menu Ferramentas. Introdução a macros e VBA 23 Para remover todos os pontos de interrupção de uma única vez, devemos clicar na opção Limpar todos os pontos de interrupção do menu Depurar ou utilizar o atalho CTRL + SHIFT + F9. Os pontos de interrupção não são salvos junto com o código. 1.6.Sistema de ajuda Podemos acessar o sistema de ajuda do VBA, exibido a seguir, posicionando o ponto de inserção na palavra do código sobre a qual desejamos informações e, em seguida, pressionando a tecla F1 ou, ainda, acessando o menu Ajuda e, em seguida, clicando na opção Visual Basic For Applications. 24 Excel 2013 VBA - Módulo I (online) 1.7.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 visualizar 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. Teste seus conhecimentos Introdução a macros e VBA 1 26 Excel 2013 VBA - Módulo I (online) 1. Onde é possível escrever e editar macros? 2. Para que serve uma 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 uma característica de um objeto. ☐ b) Para executar a macro no modo Interromper. ☐ c) Para definir uma ação de um objeto. ☐ d) Objetos não possuem propriedades. ☐ e) Para cancelar a execução de uma macro. Introdução a macros e VBA 27 3. Qual das alternativas a seguir está correta? 4. Qual é o 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) Na janela Propriedades. ☐ b) Na guia Desenvolvedor. ☐ c) Na janela Verificação imediata. ☐ d) Na barra de ferramentas. ☐ e) Na janela Project Explorer. 28 Excel 2013 VBA - Módulo I (online) 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. ☐ e) Ao pressionar a tecla F1, temos acesso à ajuda do VBA. Mãos à obra! Introdução a macros e VBA 30 Excel 2013 VBA - Módulo I (online) 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. 3. Deixe as janelas Propriedades e Project Explorer visíveis. Leitura complementar Programação de macros – Criando macros 9 Introdução à programação de macros; 9 Inserindo macros sem o gravador; 9 Propriedades e métodos de alguns objetos. 2 32 Excel 2013 VBA - Módulo I (online) 2.1.Introdução Quando desejamos criar macros, o melhor recurso que temos à disposição é o gravador de macros do Excel. Ao utilizá-lo, o processo de desenvolvimento de aplicações é bastante otimizado, uma vez que gastamos bem menos tempo com digitação. Além disso, o gravador de macros pode ser considerado um recurso para o aprendizado, pois, por meio dele, podemos conhecer os códigos correspondentes aos comandos. Devemos atentar para o fato de o gravador ter um limite de quantidade de gravação. Quando desejamos inserir uma condição em uma macro, caixas de entrada, caixas de mensagem, entre outras operações, o gravador não será capaz de suportar tantas informações, então, torna-se necessário digitar os comandos no módulo. Ao gravarmos uma macro, muitas informações são criadas, porém, podemos omitir muitas delas sem que isso cause efeitos negativos no produto final. Quando, por exemplo, alteramos o tipo de fonte de uma célula, o código que o gravador gera contém todas asinformações da caixa Fonte do Excel, ficando demasiado extenso. Temos adiante uma representação do código que é gerado quando simplesmente escolhemos o tipo de fonte Tahoma: Sub Tipo_de_Fonte() ‘ ‘ Tipo_de_Fonte Macro ‘ With Selection.Font .Name = “Tahoma” .Size = 11 .Strikethrough = False .Superscript = False .Subscript = False .OutlineFont = False .Shadow = False .Underline = xlUnderlineStyleNone .ThemeColor = xlThemeColorLight1 .TintAndShade = 0 .ThemeFont = xlThemeFontNone End With End Sub Programação de macros – Criando macros 33 Como podemos notar, o código gerado determina todas as propriedades da fonte da célula ativa, porém, nem todas elas são necessárias quando o que queremos é apenas alterar o tipo de fonte. Poderíamos, então, usar uma macro mais resumida, como mostrado a seguir: Sub Tipo_de_Fonte() Selection.Font.Name = Tahoma End Sub Ao excluirmos informações desnecessárias, a leitura do código torna-se mais simples e sua execução mais rápida. O gravador de macros pode ser muito útil quando não conhecemos um determinado comando no Excel (por exemplo, se precisamos atribuir uma cor em uma célula, mas não sabemos que comando utilizar). Apesar de gerar um código extenso, podemos empregar apenas o comando principal, desprezando as linhas de comandos desnecessárias. 2.2.Elaborando macros no Visual Basic Para criar uma macro utilizando o Visual Basic, você deve seguir os passos adiante: 1. Clique na guia Desenvolvedor e, em seguida, no comando Visual Basic do grupo Código. A janela do editor do Visual Basic será aberta; 2. Clique no comando Módulo do menu Inserir, caso necessário (os módulos são criados de forma automática para todas as planilhas da pasta de trabalho); 3. Digite ou copie o código de macro na janela Código do módulo; 4. Pressione F5 para executar a macro a partir da janela de módulo. 34 Excel 2013 VBA - Módulo I (online) 2.2.1. Inserindo macros sem o gravador O gravador é muito útil para a criação de macros, porém, também podemos criá-las sem o uso dele. 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: Programação de macros – Criando macros 35 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. Para criarmos uma macro sem o uso do gravador, 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. 36 Excel 2013 VBA - Módulo I (online) 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. 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 Programação de macros – Criando macros 37 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 em uma outra leitura; 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. 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. 38 Excel 2013 VBA - Módulo I (online) 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 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 É 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 Programação de macros – Criando macros 39 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 isto ativando o Excel e constatando que a célula A2 está selecionada: A tecla F5, sempre que pressionada, faz com que a macro na qual o cursor está posicionado seja executada. 40 Excel 2013 VBA - Módulo I (online) 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 2.2.2. Automacros É possível fazer com que uma macro seja executada automaticamente ao abrirmos ou fecharmos uma pasta de trabalho do Excel, sem a necessidade de utilizar comandos para executar a macro. Para que uma macro seja executada automaticamente logo após a abertura da pasta de trabalho que a contém, devemos definir seu nome como Auto_Open: Sub auto_Open() MsgBox “Bem Vindo ao Sistema”, vbInformation, “Data: “ & Date End Sub Programação de macros – Criando macros 41 Neste exemplo, ao abrir o arquivo (pasta de trabalho), será exibida a seguinte mensagem: Observe outro exemplo, que solicitará uma senha ao usuário no momento da abertura do arquivo. Se a senha estiver correta, será selecionada a planilha Dados, caso contrário, o arquivo será fechado: Sub auto_Open() Dim senha As String senha = InputBox(“Digite a Senha”) If senha = “123” Then MsgBox “Bem Vindo ao Sistema” Sheets(“Dados”).Select Else MsgBox “Senha Inválida” Workbooks(“teste.xls”).Close End If End Sub 42 Excel 2013 VBA - Módulo I (online) Logicamente que ao utilizar a macro em questão, o código em VBA também deverá estar protegido para que o usuário não consiga visualizar a programação. Devemos notar também que foram utilizados alguns comandos novos, os quais serão detalhados em leituras posteriores. Para que uma macro seja executada de forma automática antes do fechamento da pasta de trabalho, seu nome deve ser definido como Auto_Close. Sub auto_close() MsgBox “Até Breve!” End Sub Ao fechar o arquivo, automaticamente será exibida a mensagem adiante: Podemos impedir que macros Auto_Open e Auto_Close sejam executadas mantendo a tecla SHIFT pressionada quando abrirmos ou fecharmos a pasta de trabalho. Programação de macros – Criando macros 43 2.2.3. Macros em diferentes pastas de trabalho Quando duas ou mais pastas de trabalho estão abertas, estas são exibidas pelo VBE na janela Project Explorer. Para visualizar as macros de qualquer pasta de trabalho aberta, basta clicar na pasta Módulos da pasta de trabalho correspondente. Para visualizar o código do módulo, devemos clicar duas vezes sobre ele, ou selecioná-lo e clicar no botão Exibir código, exibido na parte superior da janela Project Explorer. Contas.xlsm Pasta1.xlsx 2.3.Propriedades e métodos de alguns objetos 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. A seguir, serão apresentados propriedades e métodos de alguns objetos. 44 Excel 2013 VBA - Módulo I (online) 2.3.1. Application O primeiro objeto a ser abordado é o Application, que é o próprio aplicativo, ou seja, o Excel. Este é o mais importante na hierarquia de objetos do Excel. 2.3.1.1. Propriedades Algumas das propriedades do objeto Application são as seguintes: • Caption Esta propriedade retorna ou define o texto da barra de títulos do Excel. Podemos definir a barra de títulos com um texto de nossa escolha, por exemplo, Orçamentos. Então, devemos usar o seguinte código: Application.Caption = “Orçamentos” Podemos fazer a barra de títulos exibir o texto original novamente, definindo para essa propriedade o valor Empty. Nesse caso, usaríamos o seguinte código: Application.Caption = Empty Podemos capturar o texto da barra de títulos do Excel usando o seguinte código: Application.Caption O exemplo seguinte exibe o conteúdo da barra de títulos em uma caixa de mensagem: Sub titulo() MsgBox Application.Caption End Sub Programação de macros – Criando macros 45 • ActiveCell Esta propriedade retorna a célula ativa. É importante lembrar que, para trabalhar com a célula ativa, ela deve estar na janela ativa. Observe os seguintes exemplos: Sub Exemplo1() Range(“a1”).Select Range(“a1”).Value = Application.Caption End Sub Sub Exemplo2() Range(“a1”).Select ActiveCell.Value = Application.Caption End Sub Os dois exemplos anteriores têm por finalidade selecionar a célula A1 e inserir em seu conteúdo o texto contido na barra de títulos. No primeiro exemplo, o endereço da célula está sendo indicado nas duas linhas de comandos. Já no segundo exemplo, é indicado que a célula selecionada (activecell) receba o valor. • ActiveWindow Esta propriedade retorna ou define o nome da janela ativa. O exemplo a seguir altera o nome da janela ativa para Mês de Fevereiro: Application.ActiveWindow.Caption = “Mês de Fevereiro” Como não é necessário digitar o termo Application, o código pode ser utilizado da seguinte forma: ActiveWindow.Caption = “Mês de Fevereiro” Célula ativa A1 recebe um valor 46 Excel 2013 VBA - Módulo I (online) • ScreenUpdating É uma propriedade do aplicativo Excel que permite determinar a atualização da tela, como True (opção padrão) ou False, no momento em que uma macro está sendo executada. Se usarmos False, a execução da macro se torna mais rápida, pois não é exibido o movimento de execução. O exemplo a seguir define esta propriedade como False: Application.ScreenUpdating = False • DisplayAlerts Com esta propriedade, podemos permitir ou não a exibição de mensagens de alerta durante a execução de uma macro. Portanto, devemos defini-la como True ou False. Por padrão, essa propriedade é definida como True. Se, por exemplo, não quisermos que apareça o alerta perguntando se desejamos salvar alterações quando uma pasta de trabalho é fechada por uma macro, devemos definir essa propriedade como False, como mostra a linha a seguir: Application.DisplayAlerts = False • EnableCancelKey Esta propriedade autoriza ou não a interrupção da execução de uma macro por meio das teclas do teclado ESC ou CTRL + BREAK. Podemos definir essa propriedade com um dos tipos a seguir: • xlInterrupt: Definido como padrão, permite o cancelamento da macro pelo teclado; • xlDisabled: Não permite o cancelamento; • xlErrorHandler: A interrupção é enviada para um procedimento em execução como um erro, podendo ser tratada com a utilização de comandos de tratamento de erros. Programação de macros – Criando macros 47 Devemos digitar a linha a seguir quando não queremos permitir a interrupção da macro: Application.EnableCancelKey = xlDisabled • DataEntryMode Esta propriedade possibilita definir o modo de entrada de dados. Para isso, devem-se considerar as seguintes informações: • xlOff: Permite que qualquer célula seja selecionada; • xlOn: Permite a seleção de uma célula depois de pressionada a tecla ESC; • xlStrict: Impede a seleção de uma célula mesmo quando pressionada a tecla ESC. Se digitarmos a linha a seguir, nenhuma célula de nenhuma planilha poderá ser selecionada: Application.DataEntryMode = xlStrict • WindowState Esta propriedade permite determinar se a janela do Excel será maximizada (com o valor xlMaximized), normal (com o valor xlNormal), ou minimizada (com o valor xlMinimized). O exemplo a seguir deixa a janela maximizada: Application.WindowState = xlMaximized 48 Excel 2013 VBA - Módulo I (online) 2.3.1.2. Métodos A seguir, vamos descrever dois métodos para o objeto Application: • Quit Este método fecha o Excel. Por padrão, um alerta perguntando se as alterações devem ser salvas surgirá. Para fechar o Excel sem salvar as alterações, basta definir a propriedade DisplayAlerts como False, como mostra o exemplo adiante: Application.DisplayAlerts = False Application.Quit • Calculate Quando usamos este método, todas as planilhasde todas as pastas de trabalho abertas são calculadas. Sua sintaxe é a seguinte: Application.Calculate 2.3.2. Workbooks Workbooks são os objetos correspondentes às pastas de trabalho, ou seja, aos arquivos. 2.3.2.1. Propriedades A seguir, serão listadas algumas propriedades do objeto Workbooks: • Name Esta propriedade retorna o nome do arquivo. É uma propriedade somente de leitura. O exemplo a seguir insere o nome do arquivo na célula ativa: ActiveCell = ActiveWorkbook.Name Programação de macros – Criando macros 49 • FullName Esta propriedade retorna o caminho completo do arquivo no disco. É uma propriedade somente de leitura. Vamos supor a existência do arquivo Dados.xlsx, na pasta Valores, na unidade C. O exemplo a seguir definiria o valor de NomeTot como C:\Valores\Dados.xlsx: NomeTot = Workbooks(“Dados.xlsx”).FullName O mesmo resultado poderia ser obtido com a utilização do comando a seguir: NomeTot = Activeworkbook.Fullname • Saved Para os arquivos que estão salvos, esta propriedade retorna o valor True; para os arquivos que não foram salvos, retorna o valor False. 2.3.2.2. Métodos Alguns dos métodos para o objeto Workbooks são os seguintes: • Close Este método fecha o arquivo. A seguir temos a representação de sua sintaxe e, em seguida, a descrição de cada um de seus elementos: Close(SaveChanges,FileName,RouteWorkbooks) • SaveChanges: Determina se o arquivo deve ou não ser salvo, portanto, pode ser definida como True ou False; • FileName: É o nome do arquivo; • RouteWorkbooks: Determina se o arquivo deve ser encaminhado ou não a um destinatário de circulação. 50 Excel 2013 VBA - Módulo I (online) • Open Devemos usar este método para abrir um arquivo. A seguir, temos a representação de sua sintaxe: Workbooks.Open FileName:=”Caminho do arquivo” O exemplo a seguir traz a linha de código que abre um arquivo: Workbooks.Open Filename:=”C:\documentos\RelMensal.xlsx” • Save Este método salva o arquivo. A seguir, temos a representação de sua sintaxe: Workbooks(<nome do arquivo>).Save O exemplo a seguir traz a linha de código que salva um arquivo: Workbooks(“RelMensal.xlsx”).Save • Protect Este método é utilizado para proteger a pasta de trabalho e é equivalente a ativar, na guia Revisão, o botão Proteger Pasta de Trabalho. A sintaxe para esse método é a seguinte: Protect(Password,Structure,Windows) Veja um exemplo de sua utilização: Workbooks(“teste1.xlsx”).Protect “123Senha”, True, True Nesse exemplo, será protegida a estrutura da pasta e da janela da pasta teste1.xlsx com a senha 123Senha. Programação de macros – Criando macros 51 • Unprotect Este método tira a proteção da pasta de trabalho. Sua sintaxe é a seguinte: Unprotect(Password) Veja um exemplo de sua utilização: Workbooks(“teste1.xlsx”).Unprotect “123Senha” 2.3.3. Worksheets Os Worksheets representam as planilhas de um arquivo. Chamamos de coleção de Worksheets o conjunto de planilhas de uma pasta de trabalho. Cada uma das planilhas é um objeto da coleção. Em alguns casos, os objetos são também chamados de Sheets. 2.3.3.1. Propriedades A seguir, temos algumas propriedades de Worksheets com suas respectivas descrições: • Count Esta propriedade faz a conta de quantas planilhas há em um determinado arquivo. Sua sintaxe é a seguinte: Worksheets.Count Considere o seguinte exemplo: Msgbox “O arquivo possui “ & sheets.count & “ planilha(s)” 52 Excel 2013 VBA - Módulo I (online) • Visible Por meio desta propriedade, podemos controlar a exibição de uma planilha, sendo True o valor que a mantém visível e False o valor que a mantém oculta. O exemplo a seguir mostra como ocultar a planilha Plan1: Worksheets(“Plan1”).Visible = False • ScrollArea Por meio desta propriedade, podemos estipular os limites de rolagem pela planilha, pois sua função é retornar ou definir a área de rolagem. O exemplo a seguir libera a rolagem entre A1 e L11 de uma planilha. Nesse caso, não será possível selecionar outras células que não pertençam a essa região. WorkSheets(“Plan1”).ScrollArea = “A1:L11” Para liberar a navegação em todas as células da planilha, basta digitar o comando a seguir: WorkSheets(“Plan1”).ScrollArea = “” • EnableSelection Com esta propriedade, podemos determinar quais células podem ser selecionadas quando a planilha estiver protegida, utilizando um dos valores a seguir: • xlNoRestrictions: Todas as células poderão ser selecionadas; • xlNoSelection: Nenhuma célula poderá ser selecionada; • xlUnlockedCells: Apenas as células que não estiverem travadas quando a planilha for protegida poderão ser selecionadas. Programação de macros – Criando macros 53 O exemplo a seguir não permite selecionar células na planilha Plan1: Worksheets(“Plan1”).EnableSelection = xlNoSelection É necessário que a planilha esteja protegida para que possamos usar esta propriedade. 2.3.3.2. Métodos A seguir, serão citados alguns métodos do objeto Worksheets: • Add Por meio deste método, podemos adicionar à pasta de trabalho folhas de planilha, macro do Excel 4.0 ou gráfico. Sua sintaxe é exibida adiante e, em seguida, está a descrição de cada um de seus elementos: Add(Before,After,Count,Type) • Before: Planilha antes da qual será inserida a nova; • After: Planilha após a qual será inserida a nova; • Count: Quantidade de planilhas que serão inseridas; • Type: Tipo da planilha que será inserida. Podemos usar o tipo xlWorkSheet, que corresponde à planilha padrão, ou xlExcel4MacroSheet, que corresponde à folha de macro do Excel 4.0. 54 Excel 2013 VBA - Módulo I (online) Segue exemplo para inserir uma planilha após a última planilha da pasta de trabalho: Sheets.Add , Sheets(Sheets.Count) • Delete Este método exclui planilhas da pasta de trabalho. A planilha a ser excluída pode ser indicada por seu nome, ou pela posição que ela ocupa no arquivo. O exemplo a seguir exclui a quarta planilha do arquivo: Sheets(4).Delete • Copy Este método copia uma planilha para outra posição no arquivo. A sintaxe deste método é: Copy(After,Before) O exemplo adiante faz uma cópia da plan2 para uma posição após a plan3: Sheets(“plan2”).Copy , Sheets(“plan3”) • Move Este método move a planilha para outra posição no arquivo. Sua sintaxe é a seguinte: Move(Before,After) O exemplo adiante move a planilha Plan1 para a última posição na pasta de trabalho: Sheets(“Plan1”).Move , Sheets(Sheets.Count) • Select Para selecionar uma planilha, devemos usar este método. A linha a seguir pode ser usada para selecionar a planilha Plan1: Sheets(“Plan1”).Select Programação de macros – Criando macros 55 • Protect Este método é utilizado para proteger a planilha e tem função igual ao comando para proteção de planilha, com a adição de um quarto argumento. A sintaxe para este método é a seguinte: Protect(Password,DrawingObjects,Contents,Scenarios,UserInterfaceOnly) Em que: Argumento Descrição Password Opcional. DrawingObjects True ou False: Utilizado para proteger formas (o valor padrão é True). Contents True ou False: Utilizado para proteger o conteúdo (o valor padrão é True). Scenarios True ou False: Utilizado para proteger cenários (o valor padrão é True). UserInterfaceOnly Utilizado para realizar a proteção na interface do usuário, porém, quando omitido, irá proteger tanto a interface quanto as macros. Observe o exemplo para proteger a planilha denominada Plan2: Sheets(“plan2”).Protect “1234” • Unprotect Este método tira a proteção da planilha. O exemplo a seguir desprotege a planilha Plan1, a qual possui uma senha: Sheets(“Plan1”).Unprotect “Senha” 2.3.4. Range Este objeto representa as células de uma planilha. Pode representar apenas uma ou mais células ao mesmo tempo. 56 Excel 2013 VBA - Módulo I (online) 2.3.4.1. Propriedades A seguir, vamos citar algumas propriedades para serem usadas com objeto Range: • Value Esta é a propriedade padrão de Range. Com ela, podemos retornarou definir o valor de uma ou mais células. As células podem ser indicadas não só pela sua posição, mas também pelo seu nome. Para inserir a palavra Impacta na célula A10, utilizamos o seguinte comando: Range(“A10”).Value = “Impacta” Para inserir um valor na célula B4 e também no intervalo de células C2 até C10, consideremos o comando a seguir: Range(“b4,c2:c10”) = 10 • Row Esta propriedade retorna o número da linha de uma célula. O exemplo a seguir insere na célula ativa o número da linha da célula B5: ActiveCell = Range(“B5”).Row • Column Esta propriedade retorna o número da coluna de uma célula, o qual pode variar entre 1, referente à coluna A, e 256, referente à coluna IV. O exemplo a seguir insere na célula A2 o número da coluna C3: Range(“A2”).Value = Range(“C3”).Column • Name Esta propriedade nomeia uma ou mais células. O exemplo a seguir coloca na célula A3 o nome de Preços: Range(“A3”).Name = “Preços” Programação de macros – Criando macros 57 2.3.4.2. Métodos A seguir, serão citados os métodos para o objeto Range: • Select Este método serve para selecionar células. É possível selecionar apenas uma célula, ou um intervalo de células, ou uma ou mais células e mais um intervalo etc. Enfim, podemos selecionar diversas células independentemente da posição que elas ocupam na planilha. Se usarmos a linha a seguir, a célula A5 da planilha ativa será selecionada: Range(“A5”).Select O próximo exemplo seleciona o intervalo entre a célula ativa e a célula B6: Range(ActiveCell, “B6”).Select • Copy O método Copy copia células, e o local onde ficará a cópia varia de acordo com a sintaxe. Para fazer uma cópia para o clipboard, devemos usar a sintaxe seguinte: Range(<célula de origem>).Copy Para que a cópia seja automaticamente colada no destino, a sintaxe é a seguinte: Copy(Destination) No exemplo adiante, será copiado o conteúdo do intervalo de células C2 até C4, a partir da posição da célula E6: Range(“c2:c4”).Copy Range(“E6”) • Cut Este método recorta células. O local para onde vão as células recortadas depende da sintaxe usada. O exemplo a seguir recorta as células B2:E14 e cola em G7: Range(“B2:E14”).Cut Range (“G7”) 58 Excel 2013 VBA - Módulo I (online) • Clear Este método equivale ao comando Limpar / Limpar tudo, ou seja, ele serve para limpar tudo o que há nas células. O exemplo a seguir limpa todos os elementos das células presentes no intervalo B2:B20: Range(“B2:B20”).Clear • ClearContents Este método limpa apenas o conteúdo da célula, mantendo as demais propriedades. É equivalente a selecionar a célula e pressionar DELETE. O exemplo a seguir limpa o conteúdo das células presentes no intervalo B2:B20: Range(“B2:B20”).ClearContents • ClearFormats Este método limpa apenas a formatação da célula, mantendo o seu conteúdo. O exemplo a seguir limpa apenas a formatação das células contidas no intervalo C2:C4: Range(“c2:c4”).ClearFormats • Delete Este método é usado para excluir células da planilha. Na sua sintaxe, mostrada a seguir, o argumento Shift não é obrigatório e serve para definir o local para onde serão deslocadas as células; se para a esquerda, devemos usar xlShiftToLeft, se para cima, xlShiftUp: Delete(Shift) O exemplo a seguir exclui as células contidas no intervalo, deslocando as demais células para a esquerda: Range(“c2:c4”).Delete xlToLeft Programação de macros – Criando macros 59 • Insert Este método insere células na planilha. Na sua sintaxe, mostrada a seguir, o argumento Shift não é obrigatório e serve para indicar o local para onde serão deslocadas as células; se para a direita, devemos usar xlShiftToRight, se para baixo, XlShiftDown: Insert(Shift) O exemplo a seguir insere as células contidas no intervalo, deslocando as demais células para baixo: Range(“c2:c6”).Insert xldown Até este ponto, já pudemos conhecer algumas propriedades e métodos de alguns objetos. Novos objetos, métodos e propriedades serão abordados mais adiante. Teste seus conhecimentos Programação de macros – Criando macros 2 62 Excel 2013 VBA - Módulo I (online) 1. Qual é a sintaxe utilizada para alterar o texto contido na barra de título do Excel? 2. Qual das alternativas a seguir refere-se à pasta de trabalho (arquivos)? ☐ a) Range(“a1”).Select ☐ b) Application.Caption = “Texto Qualquer” ☐ c) Selection.Title = “Texto Qualquer” ☐ d) Sheets(“Texto Qualquer”).Select ☐ e) As alternativas B e C estão corretas. ☐ a) Worksheets ☐ b) Workbooks ☐ c) Range ☐ d) Selection ☐ e) Application Programação de macros – Criando macros 63 3. Qual comando deve ser utilizado para inserir um texto na célula A4, sabendo-se que o cursor está posicionado sobre ela? 4. Qual é o resultado do comando Range(“g1”).select? ☐ a) Range(“a3”).Select ☐ b) Activecell.Value = “Texto” ☐ c) Range(“a4”).Select = “texto” ☐ d) Activecell = “Texto” ☐ e) As alternativas B e D estão corretas. ☐ a) Seleciona a célula G1. ☐ b) Escreve a palavra “select” na célula G1. ☐ c) Seleciona a célula ativa. ☐ d) Este comando não funciona. ☐ e) Seleciona a planilha G1. 64 Excel 2013 VBA - Módulo I (online) 5. Qual é o método utilizado para fechar o Excel? ☐ a) Select ☐ b) Range ☐ c) Finish ☐ d) Calculate ☐ e) Quit Mãos à obra! Programação de macros – Criando macros 2 66 Excel 2013 VBA - Módulo I (online) Laboratório 1 A – Utilizando o gravador de macros 1. Utilize o gravador de macros para criar uma macro que realize o seguinte: • Mescle a linha 1 inteira da planilha; • Escreva a palavra “Excel” na célula que foi mesclada; • Insira bordas (Todas as bordas) nas células A2 até V21; • Pinte a coluna A da célula A2 a A21 de verde-claro. Laboratório 2 A – Criando uma auto macro 1. Crie uma auto macro que seja executada ao iniciar a pasta de trabalho e que o conteúdo seja: Application.Caption = “Meu programa” 2. Crie uma auto macro que seja executada ao fechar a pasta de trabalho, que o seu conteúdo seja: MsgBox “Obrigado por acessar!”. Leitura complementar Programação de macros - Referência de células, formatação de propriedades e uso de caixas 9 Referenciando células; 9 Caixas de entrada (InputBox); 9 Instruções With e End With; 9 Formatando propriedades; 9 Caixa de mensagem (MsgBox); 9 Método GetOpenFileName; 9 Método GetSaveAsFileName; 9 Caixas de diálogo do Excel. 3 68 Excel 2013 VBA - Módulo I (online) 3.1.Referenciando células Há diferentes formas de referenciar uma célula. Podemos utilizar as propriedades Range, Cells, ActiveCell e Offset. A seguir, veja quando e como utilizá-las. 3.1.1. Referenciando uma célula ou um intervalo de células As propriedades Range e Cells, que usamos para nos referir às células de uma planilha, retornam um objeto Range que representa tais células. A propriedade Range deve ser usada para fazer referência a uma célula por meio de seu endereço. Sua sintaxe é a seguinte: Range(Cell1[,Cell2]) Devemos sempre nos lembrar de que os parâmetros que aparecem entre colchetes são parâmetros opcionais. No caso da propriedade Cells, também temos como retorno uma ou mais células, porém, a diferença é que a posição da célula na planilha será a base dessa propriedade. A referência à posição da célula é feita primeiro pelo número da linha, RowIndex, e depois pelo número da coluna, ColumnIndex. Quando não há argumento especificado, serão consideradas todas as células, ou seja, Cells retorna todas as células. A sintaxe de Cells é a seguinte: Cells([RowIndex][,ColumnIndex]) Observe um exemplo que utiliza o comando Cells para selecionar a célula B3: Cells(3,2) Programação de macros - Referência de células, formatação de propriedades e uso de caixas 69 3.1.2. Referenciando uma célula ativa ou seleção de células Quando desejamos executar ações na célula selecionada, devemos usar a propriedade ActiveCell, já abordada nesta apostila. Esta propriedade se refere somente a uma célula, aquela que está ativa. Issoquer dizer que, quando quisermos referenciar mais de uma célula selecionada, esta propriedade não servirá, uma vez que se aplica a somente uma única célula. Para que uma ação seja executada em várias células de uma seleção, devemos usar a propriedade Selection. Sub Ex_Selection() Range(“a2:c10”).Select Selection.Interior.ColorIndex = 35 Selection.Font.Italic = True End Sub 3.1.3. Referenciando de acordo com um deslocamento A propriedade Offset é uma das mais empregadas quando se fala em criação de macros. Ela determina uma referência conforme um deslocamento para a direita, para a esquerda, para cima ou para baixo. Então, Offset retorna uma Range que se refere a um deslocamento em linhas, colunas ou em linhas e colunas ao mesmo tempo. Na sintaxe de Offset, mostrada a seguir, RowOffset é o número de linhas e ColumnOffset é o número de colunas: Offset(RowOffset, ColumnOffset) No exemplo adiante, será inserida uma informação na segunda célula (RowOffset = 2) abaixo da célula selecionada e na mesma coluna (ColumnOffset=0): Activecell.offset(2,0).value = “Exemplo de texto” O deslocamento acontece levando em consideração o número positivo ou negativo de linhas ou colunas. Isto quer dizer que, quando o número de linhas é positivo, o deslocamento é feito para baixo e, quando negativo, para cima. No caso das colunas, segue-se a mesma regra, número positivo equivale a deslocamento para a direita e negativo, para a esquerda. A Range que a propriedade End retorna representa a última célula que contém dados no fim de uma determinada região. Esse fim pode ser determinado de quatro formas diferentes, dependendo da direção empregada, que pode ser para cima, para baixo, para a direita ou para a esquerda. Podemos fazer uma comparação dessa propriedade com o ato de pressionar a tecla CTRL mais uma das teclas de direção do teclado (seta à direita, seta à esquerda, seta acima ou seta abaixo). 70 Excel 2013 VBA - Módulo I (online) Selecionar uma célula e, depois, selecionar na mesma coluna a última célula que contém dados equivale a fazer referência à primeira célula selecionada e, na mesma linha de código, determinar a propriedade End. Na sintaxe da propriedade End, demonstrada a seguir, o elemento Direction representa a direção a partir da qual o fim será determinado e pode variar entre: xlToLeft, para o lado esquerdo; xlToRight, para o lado direto; xlUp, para cima; xlDown, para baixo: End(Direction) Activecell.end(xldown) A fim de compreendermos melhor as propriedades Offset e End, devemos, inicialmente, criar a planilha Cadastrar com o mesmo conteúdo da imagem a seguir: Mesmo que CTRL + Seta para baixo Programação de macros - Referência de células, formatação de propriedades e uso de caixas 71 Então, basta digitar a fórmula na célula E2, que será o produto da quantidade, o valor: =C2*D2. Devemos também preencher os demais campos (Modelo, Fabricante, Quantidade e Valor), conforme apresentado adiante: O objetivo do exemplo é copiar as informações preenchidas na linha 2 da planilha Cadastrar para a planilha Dados, na posição da próxima linha vazia após a última linha preenchida da lista. Após efetuar a cópia, será retornado para a planilha Cadastrar e as informações contidas no intervalo de A2:D2 serão limpas. 72 Excel 2013 VBA - Módulo I (online) Crie, então, a planilha Dados conforme o exemplo adiante: Podemos criar a macro no mesmo módulo, iniciando pelos comandos a seguir: Sub Cadastrar() End Sub Programação de macros - Referência de células, formatação de propriedades e uso de caixas 73 A macro deverá iniciar selecionando as células contidas na planilha Cadastrar. Então, na primeira linha dentro da instrução Sub, devemos digitar a sequência de códigos: Sheets(“Cadastrar”).select Range(“A2:E2”).select Na sequência, o código adiante copiará a região selecionada: Selection.copy É necessário, então, selecionar a planilha Dados e indicar em qual linha será inserida a informação copiada. Na primeira vez que for executada a macro, a informação será inserida a partir da célula A2, porém, nas demais vezes, a inserção ocorrerá nas linhas subsequentes (A”,“A4...). Sheets(“dados”).select A fim de posicionarmos na última linha preenchida, devemos selecionar a última célula da coluna A e subir (mesmo que CTRL + seta para cima) com a utilização da propriedade End: Range(“A1048576”).select Activecell.End(Xlup).select 1 1 2 2 74 Excel 2013 VBA - Módulo I (online) Agora, é necessário descer para a próxima linha vazia e colar as seguintes informações: Activecell.Offset(1,0).select Activecell.pastespecial Podemos retornar para a planilha Cadastrar, limpar os valores preenchidos, visto que já foram copiados para a outra planilha, e posicionar na célula A2 a fim de receber outro valor: Sheets(“Cadastrar”).select Range(“A2:D2”).ClearContents Range(“A2”).Select Programação de macros - Referência de células, formatação de propriedades e uso de caixas 75 A macro inteira deverá estar da seguinte maneira: Sub Cadastrar() Sheets(“Cadastrar”).Select Range(“a2:e2”).Select Selection.Copy Sheets(“Dados”).Select Range(“A1048576”).Select ActiveCell.End(xlUp).Select ActiveCell.Offset(1, 0).Select ActiveCell.PasteSpecial xlValues Sheets(“Cadastrar”).Select Range(“A2:D2”).ClearContents Range(“A2”).Select End Sub Podemos abreviar o código para posicionar na primeira linha vazia de uma listagem: Range(“A1048576”).End(xlUp).Offset(1, 0).Select 3.2.Caixas de entrada (InputBox) Dentre diversos modos de enviar informações para um computador, o uso de formulários é um dos principais métodos adotados pelos desenvolvedores de programas. Os formulários permitem interatividade com o usuário, são um meio seguro de colocar os dados em suas posições corretas e conferem consistência às informações. Na caixa de entrada do VBA, podemos digitar um dado para que este seja inserido em uma célula ou, ainda, usado como variável em outras operações. Acionamos essa caixa de entrada com a função InputBox. Esta função também torna possível a obtenção em texto dos dados de entrada de um usuário. A sintaxe da função InputBox e a descrição de cada um dos seus elementos são as seguintes: InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile], [context] 76 Excel 2013 VBA - Módulo I (online) Em que: • Prompt: Este argumento é o texto da mensagem da caixa de diálogo; • Title: Este argumento é o texto da barra de título da caixa. Se não definirmos nenhum texto para esse argumento, o nome Microsoft Excel aparecerá na barra de títulos; • Default: Quando a caixa tiver sido carregada, o texto definido em Default será exibido. Também podemos omitir essa informação para que a caixa de texto fique vazia; • Xpos: Este argumento define a posição da caixa a partir do lado esquerdo da tela, ou seja, horizontalmente. Essa informação deve ser dada em twips. Se omitirmos esse argumento, a posição horizontal da caixa fica definida automaticamente no centro da tela; • Ypos: Este argumento define a posição da caixa a partir da parte de cima da tela, ou seja, verticalmente. Essa informação deve ser dada em twips. Se omitirmos esse argumento, a posição vertical da caixa fica definida automaticamente no centro da tela; • Helpfile: Este argumento reconhece o arquivo de ajuda da caixa, desde que este exista; • Context: Se definirmos Helpfile, este argumento também deverá ser especificado. Ele representa o número do contexto da ajuda. Considere o exemplo adiante: Sub Insere_Dado() Range(“A1048576”).End(xlUp).Offset(1, 0).Select ActiveCell = InputBox(“Digite seu Nome”, “Cadastro”) End Sub Title Prompt Default Programação de macros - Referência de células, formatação de propriedades e uso de caixas 77 Observe, agora, o exemplo de um InputBox contendo um texto padrão (Default): Range(“B2”) = InputBox(“Estado”, “Cadastro de Clientes”,”SP”) 3.3.Instruções With e End
Compartilhar