Baixe o app para aproveitar ainda mais
Prévia do material em texto
Excel VBA - Módulo I TE 1621/1_WEB Excel VBA - Módulo I Créditos 4 Excel VBA - Módulo I Coordenação Geral Marcia M. Rosa Coordenação Editorial Henrique Thomaz Bruscagin Supervisão Editorial Simone Rocha Araújo Pereira Atualização Beatriz Silva Ramos Revisão Ortográfica e Gramatical Maria Rosa Carnicelli Kushnir Diagramação Shelida Letícia Lopes Edição nº 1 | Cód.: 1621/1_WEB Outubro/2013 Este material constitui uma nova obra e é uma derivação da seguinte obra original, produzida por TechnoEdition Editora Ltda. em Out/2010: Excel 2010 VBA – Módulo I Autoria: Viviane Martins Ferreira Copyright © TechnoEdition Editora 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 TechnoEdition Editora 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.” Crédito das imagens: Banco de Imagens Digital Juice, Inc. Sumário 5 Informações sobre o treinamento ..............................................................................................9 Capítulo 1 - Manipulação de Macros .......................................................................................11 1.1. Introdução ao VBA .................................................................................12 1.2. Macros ..................................................................................................12 1.3. Editor do Visual Basic ............................................................................12 1.3.1. Janela Project Explorer ...........................................................................17 1.3.2. Janela Verificação imediata ....................................................................18 1.4. Objetos .................................................................................................20 1.4.1. Propriedades e métodos ........................................................................20 1.4.2. Coleções ...............................................................................................21 1.4.3. Hierarquia de objetos ............................................................................21 1.5. Elaborando macros no Visual Basic ........................................................22 1.5.1. Automacros ...........................................................................................23 1.5.2. Macros em diferentes pastas de trabalho ...............................................25 1.5.3. Pontos de interrupção ...........................................................................25 1.6. Sistema de ajuda ...................................................................................27 1.7. Pesquisador de objeto ...........................................................................28 Teste seus conhecimentos.......................................................................................................... 31 Mãos à obra! ...................................................................................................................................... 35 Capítulo 2 - Programação de macros .....................................................................................37 2.1. Introdução .............................................................................................38 2.2. Inserindo macros sem o gravador ..........................................................39 2.3. Propriedades e métodos de alguns objetos ............................................46 2.3.1. Application ............................................................................................47 2.3.1.1. Propriedades .........................................................................................47 2.3.1.2. Métodos ................................................................................................52 2.3.2. Workbooks ............................................................................................52 2.3.2.1. Propriedades .........................................................................................52 2.3.2.2. Métodos ................................................................................................53 2.3.3. Worksheets ...........................................................................................55 2.3.3.1. Propriedades .........................................................................................55 2.3.3.2. Métodos ................................................................................................58 2.3.4. Range ....................................................................................................61 2.3.4.1. Propriedades .........................................................................................61 2.3.4.2. Métodos ................................................................................................62 2.3.5. Referenciando uma célula ou um intervalo de células ............................65 2.3.6. Referenciando uma célula ativa ou seleção de células ............................66 2.3.7. Referenciando de acordo com um deslocamento ...................................66 2.4. Caixas de entrada (InputBox) .................................................................72 2.5. Instruções With e End With ....................................................................74 6 Excel VBA - Módulo I 2.6. Formatando propriedades .....................................................................78 2.6.1. Cor do preenchimento ...........................................................................78 2.6.2. Fonte .....................................................................................................80 2.6.3. Alinhamento ..........................................................................................82 2.6.4. Bordas ...................................................................................................85 2.6.5. Número .................................................................................................88 2.7. Caixa de mensagem (MsgBox) ...............................................................90 2.7.1. Exibindo uma caixa de mensagem .........................................................91 2.7.2. Constantes utilizadas nas caixas de mensagem .....................................93 2.7.3. Identificando o botão clicado .................................................................95 2.8. Método GetOpenFilename......................................................................96 2.9. Método GetSaveAsFilename ...................................................................98 2.10. Caixas de diálogo do Excel ....................................................................99 2.10.1. Controlando a exibição dos itens da janela ..........................................102 2.10.2. Operador NOT .....................................................................................104 2.10.3. Trabalhando em linhas e colunas inteiras ............................................106 2.10.3.1. Inserindo e excluindo linhas, colunas e células ....................................106 2.10.3.2. Ajustando a largura e a altura de linhas e colunas ...............................107 Teste seus conhecimentos....................................................................................................... 111 Mãos à obra! ...................................................................................................................................115 Capítulo 3 - Operadores e variáveis .................................................................................... 119 3.1. Introdução ...........................................................................................120 3.2. Operadores .........................................................................................120 3.2.1. Operadores aritméticos .......................................................................121 3.2.2. Operadores de comparação .................................................................122 3.2.3. Operadores lógicos .............................................................................123 3.2.4. Operadores de concatenação ...............................................................124 3.2.5. Ordem das operações ..........................................................................125 3.3. Variáveis .............................................................................................125 3.3.1. Declarando variáveis ............................................................................128 3.3.1.1. Declarando no procedimento ...............................................................130 3.3.1.2. Declarando no módulo ........................................................................133 3.3.1.3. Declarando no projeto .........................................................................134 3.3.1.4. Option Explicit.....................................................................................135 Teste seus conhecimentos....................................................................................................... 137 Mãos à obra! ................................................................................................................................... 141 Capítulo 4 - Estruturas de decisão e repetição ............................................................... 145 4.1. Introdução ...........................................................................................146 4.2. GoTo ...................................................................................................146 4.3. If e suas estruturas ..............................................................................147 4.3.1. If...Then ..............................................................................................148 4.3.2. If...Then...Else .....................................................................................152 4.3.3. If aninhado ..........................................................................................158 7 Sumário 4.3.4. Inserindo várias condições com And e Or ............................................160 4.4. Select Case ..........................................................................................163 4.5. Loop e suas estruturas ........................................................................165 4.5.1. Do...Loop ............................................................................................165 4.5.1.1. Do While...Loop ...................................................................................166 4.5.1.2. Do Until...Loop ....................................................................................169 4.6. While...Wend .......................................................................................171 4.7. For...Next e For Each...Next .................................................................172 Teste seus conhecimentos....................................................................................................... 179 Mãos à obra! ................................................................................................................................... 183 Capítulo 5 - Funções ................................................................................................................... 189 5.1. Introdução ...........................................................................................190 5.2. Funções matemáticas ..........................................................................190 5.3. Funções de data e hora .......................................................................191 5.4. Funções de tratamento de strings ........................................................198 5.5. Funções de conversão .........................................................................205 5.6. Utilizando funções de planilha do Excel ...............................................206 5.7. Criando funções ..................................................................................210 5.7.1. Utilizando as funções criadas ..............................................................211 5.8. Utilizando funções em diversas pastas de trabalho ..............................212 5.9. Assistente de Função ...........................................................................215 Teste seus conhecimentos....................................................................................................... 219 Mãos à obra! ................................................................................................................................... 223 Capítulo 6 - Formulários (UserForms) ................................................................................. 225 6.1. Importância dos formulários ................................................................226 6.2. Criando formulários ............................................................................226 6.2.1. Inserindo controles em um formulário .................................................228 6.2.1.1. Caixa de ferramentas ..........................................................................229 6.2.2. Configurando as propriedades dos controles .......................................233 6.2.3. Configurando a aparência dos controles ..............................................241 6.2.4. Aplicando códigos aos controles .........................................................242 6.2.5. Elaborando um formulário ...................................................................248 6.2.6. Exibindo um formulário .......................................................................274 Teste seus conhecimentos....................................................................................................... 279 Mãos à obra! ................................................................................................................................... 283 Capítulo 7 - Eventos não associados a objetos .............................................................. 285 7.1. Introdução ...........................................................................................286 7.2. Método OnKey .....................................................................................286 7.3. Método OnTime ...................................................................................290 Teste seus conhecimentos....................................................................................................... 295 Mãos à obra! ................................................................................................................................... 299 8 Excel VBA - Módulo I Capítulo 8 - Variáveis arrays ................................................................................................... 301 8.1. Arrays .................................................................................................302 8.1.1. Declarando arrays ...............................................................................303 8.1.2. Arrays multidimensionais ....................................................................305 8.1.3. Arrays dinâmicos .................................................................................308 Teste seus conhecimentos....................................................................................................... 311 Mãos à obra! ...................................................................................................................................315 Capítulo 9 - Tratamento de erros .......................................................................................... 317 9.1. Introdução ...........................................................................................318 9.2. Tipos de erros .....................................................................................318 9.3. Tratamento em tempo de execução .....................................................319 9.3.1. Erros interceptáveis .............................................................................325 9.4. Depurador do VBA ...............................................................................331 9.4.1. Pontos de interrupção .........................................................................331 9.4.1.1. Janela Verificação imediata ..................................................................336 9.4.1.2. Janela Inspeções de variáveis ...............................................................339 Teste seus conhecimentos....................................................................................................... 343 Mãos à obra! ................................................................................................................................... 347 Informações sobre o treinamento 9 Para que os alunos possam obter um bom aproveitamento deste curso de Excel VBA - Módulo I, é imprescindível que eles tenham participado dos nossos cursos de Excel - Módulo II e Introdução à Lógica de Programação, ou possuam conhecimentos equivalentes. 1 Manipulação de Macros 9 Introdução ao VBA; 9 Macros; 9 Editor do Visual Basic; 9 Objetos; 9 Elaborando macros no Visual Basic; 9 Sistema de ajuda; 9 Pesquisador de objeto. 12 Excel VBA - Módulo I 1.1. Introdução ao VBA Neste capítulo, 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 clicarmos na guia Desenvolvedor e, em seguida, no botão Visual Basic do grupo Código, ou utilizarmos o atalho ALT + F11. Para voltar à tela do Excel, podemos pressionar ALT + F11 novamente. 13 Manipulação de Macros 1 A imagem a seguir ilustra a janela do VBE: Para ativarmos a guia Desenvolvedor, devemos acessar a guia Arquivo e clicar 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. Janela Propriedades Janela Verificação imediata Project Explorer Janela Código Barra de ferramentasBarra de menus 14 Excel VBA - Módulo I • 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, 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. 15 Manipulação de Macros 1 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. 16 Excel VBA - Módulo I • 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, podemos clicar no comando Project Explorer do menu Exibir, clicar no botão Project Explorer ( ) ou utilizar 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, podemos clicar na opção Janela ‘Propriedades’ do menu Exibir, clicar no botão Janela ‘Propriedades’ ( ) ou utilizar 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, podemos clicar na opção Janela ‘Verificação imediata’ do menu Exibir ou utilizar o atalho CTRL + G. 17 Manipulação de Macros 1 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. 18 Excel VBA - Módulo I 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 aseguir, 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 19 Manipulação de Macros 1 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 executarmos o resultado de uma função. Para isso, basta utilizarmos o ponto de interrogação (?) seguido da função desejada e pressionarmos a tecla ENTER. A resposta ao comando aparecerá na linha inferior, conforme o exemplo seguinte: 20 Excel VBA - Módulo I 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 obtermos o valor de uma propriedade de um objeto, a sintaxe é NomeDoObjeto.Propriedade. Assim, utilizando o exemplo da célula B2, para obtermos seu valor como retorno, determinamos 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. 21 Manipulação de Macros 1 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 é bem complexa 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 22 Excel VBA - Módulo I Automaticamente, o Excel identifica que o comando será executado na planilha selecionada do arquivo em questão. 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 (Pasta de trabalho) Worksheets (Planilha) Range (Célula) 1.5. Elaborando macros no Visual Basic Para criarmos 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. 23 Manipulação de Macros 1 1.5.1. 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 utilizarmos 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 Neste exemplo, ao ser aberto o arquivo (pasta de trabalho), será exibida a seguinte mensagem: Observemos 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 24 Excel VBA - Módulo I 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 capítulos 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 será exibida automaticamente 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. 25 Manipulação de Macros 1 1.5.2. 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 clicarmos na pasta Módulos da pasta de trabalho correspondente. Para visualizar o código do módulo, devemos clicar duas vezes sobre o mesmo, ou selecioná-lo e clicar no botão Exibir código, exibido na parte superior da janela Project Explorer. 1.5.3. 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 tornara 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; Contas.xlsm Pasta1.xlsx 26 Excel VBA - Módulo I 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, 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. Para removermos 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. 27 Manipulação de Macros 1 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. 28 Excel VBA - Módulo I 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 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. 29 Manipulação de Macros 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; • 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; • 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. 1Manipulação de Macros Teste seus conhecimentos 32 Excel 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. 33 Manipulação de Macros 1 3. Indique a alternativa 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 ☐ e) Janela Project Explorer 34 Excel 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. ☐ e) Ao pressionar a tecla F1, temos acesso à ajuda do VBA. 1Manipulação de Macros Mãos à obra! 36 Excel 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. 3. Deixe as janelas Propriedades e Project Explorer visíveis. 2 Programação de macros 9 Introdução à programação de macros; 9 Inserindo macros sem o gravador; 9 Propriedades e métodos de alguns objetos; 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. 38 Excel VBA - Módulo I 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 macro pode ser considerado como 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 gravamos 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 as informaçõ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 39 Programação de macros 2 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, usaruma 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. 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’; 40 Excel VBA - Módulo I 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. 41 Programação de macros 2 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. Então, a pasta Módulos será criada e, dentro dela, teremos o Módulo1 ativo. 42 Excel VBA - Módulo I 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 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; 43 Programação de macros 2 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. 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. Argumentos do comando Range 44 Excel VBA - Módulo I 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. É 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 Métodos e propriedades do objeto Range 45 Programação de macros 2 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. 46 Excel 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 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. 47 Programação de macros 2 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 Podemoscapturar o texto da barra de títulos do Excel usando o seguinte código: Application.Caption 48 Excel VBA - Módulo I Segue exemplo que exibe o conteúdo da barra de títulos em uma caixa de mensagem: Sub titulo() MsgBox Application.Caption End Sub • ActiveCell Esta propriedade retorna a célula ativa. É importante lembrar que, para trabalhar com a célula ativa, ela deve estar na janela ativa. Observemos 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 Célula ativa A1 recebe um valor 49 Programação de macros 2 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” • 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 50 Excel VBA - Módulo I • 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 (assunto abordado no último capítulo). Devemos digitar a linha a seguir quando não queremos permitir a interrupção da macro: Application.EnableCancelKey = xlDisabled 51 Programação de macros 2 • 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 52 Excel VBA - Módulo I 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 planilhas de 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 53 Programação de macros 2 • 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. 54 Excel VBA - Módulo I • 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 tem função igual ao comando Ferramentas / Proteger / Proteger pasta de trabalho. A sintaxe para esse método é a seguinte: Protect(Password,Structure,Windows) Segue exemplo de 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. 55 Programação de macros 2 • Unprotect Este método tira a proteção da pasta de trabalho. Sua sintaxe é a seguinte: Unprotect(Password) Segue exemplo de 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 56 Excel VBA - Módulo I Consideremos o seguinte exemplo: Msgbox “O arquivo possui “ & sheets.count & “ planilha(s)” • 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 oudefinir 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 = “” 57 Programação de macros 2 • 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. 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. 58 Excel VBA - Módulo I 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 xlWordSheet, que corresponde à planilha padrão, ou xlExcel4MacroSheet, que corresponde à folha de macro do Excel 4.0. 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 59 Programação de macros 2 • 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 60 Excel VBA - Módulo I • 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) 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” 61 Programação de macros 2 2.3.4. Range Este objeto representa as células de uma planilha. Pode representar apenas uma ou mais células ao mesmo tempo. 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 retornar ou 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 62 Excel VBA - Módulo I • 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” 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) 63 Programação de macros 2 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”) • 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 64 Excel VBA - Módulo I • 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 • 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 ao longo da apostila. 65 Programação de macros 2 2.3.5. 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]) Observemos um exemplo que utiliza o comando Cells para selecionar a célula B3: Cells(3,2) 66 Excel VBA - Módulo I 2.3.6. Referenciando uma célula ativa ou seleção de células Nas situações em que 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. Isso quer 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 2.3.7. 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” 67 Programação de macros 2 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). 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) 68 Excel VBA - Módulo I 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: 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: 69 Programação de macros 2 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. Criemos, 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 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 70 Excel VBA - Módulo I É 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 71 Programação de macros 2 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 72 Excel VBA - Módulo I 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 2.4. 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] 73 Programação de macros 2 • 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 dolado 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. Consideremos o exemplo adiante: Sub Insere_Dado() Range(“A1048576”).End(xlUp).Offset(1, 0).Select ActiveCell = InputBox(“Digite seu Nome”, “Cadastro”) End Sub Title Prompt 74 Excel VBA - Módulo I Observemos, agora, o exemplo de um InputBox contendo um texto padrão (Default): Range(“B2”) = InputBox(“Estado”, “Cadastro de Clientes”,”SP”) 2.5. Instruções With e 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(“célula”).Value = valor_da_célula Range(“célula”).Font.ColorIndex = número_da_cor Range(“célula”).NumberFormat = “0.00” Range(“célula”).Font.Bold = True Range(“célula”).Interior.ColorIndex = número_da_cor End Sub 75 Programação de macros 2 Para evitar a repetição do mesmo objeto em casos como o que acabamos de verificar, ou seja, casos em que diversas propriedades são aplicadas a um mesmo objeto, podemos usar a estrutura With e End With. O objeto referenciado quando empregamos essa instrução deve ser colocado logo após a própria instrução. A sintaxe da estrutura End e End With é a seguinte: With Objeto Instruções End With Quando formos digitar as instruções, devemos sempre lembrar que o nome do objeto não deve ser citado e que deve haver um ponto antes de cada uma das propriedades. Todas as instruções que digitarmos entre With e End With serão realizadas no dado objeto. A seguir, temos o código que aplica algumas propriedades a uma célula por meio da instrução With: Sub Formatar() With Range(“célula”) .Value = valor_da_célula .Font.ColorIndex = número_da_cor .NumberFormat = “0.00” .Font.Bold = True .Interior.ColorIndex = número_da_cor End With End Sub Devemos sempre nos lembrar de que é necessário digitar um ponto antes de cada propriedade. Apesar de a propriedade Value ser uma propriedade padrão, não precisando digitá-la na maioria das vezes, deve-se digitá-la dentro da instrução With quando for desejado atribuir um valor em uma célula. 76 Excel VBA - Módulo I A seguir, temos uma macro que realiza diversas ações na planilha Plan3, ou seja, define o valor da célula A5 como 7, renomeia a planilha como Cadastro, move a planilha de forma que ela fique posicionada antes da planilha Plan1 e, então, exibe a planilha: Sub AlterarPlan() With Sheets(“Plan3”) .Range(“A5”).Value= 7 .Name = “Cadastro” .Move Before:=Sheets(“Plan1”) .Visible = True End With End Sub A instrução With pode também ser usada em conjunto com outras instruções With se inserirmos uma dentro da outra, fazendo um processo de aninhamento. Se tomarmos como exemplo o mesmo procedimento citado anteriormente e quisermos adicionar algumas propriedades apenas à célula A5, mas não a toda planilha Plan3, devemos inserir outro With referenciando a célula e, depois, outro End With quando terminarmos de digitar as ações para A5. Então, podemos, por exemplo, definir para a célula A5 o valor 7, o estilo de fonte como negrito e uma cor específica para a fonte, usando as seguintes instruções: Sub AlterarPlan() With Sheets(“Plan3”) With Range(“A5”) .Value = 7 .Font.Bold = True .Font.ColorIndex = numero_da_cor End With .Name = “Cadastro” .Move Before:=Sheets(“Plan1”) .Visible = True End With End Sub 77 Programação de macros 2 Também podemos inserir outro With para a fonte aninhando dois With ao primeiro, conforme a macro adiante: Sub AlterarPlan() With Sheets(“Plan3”) With Range(“A5”) .Value = 7 With .Font .Bold = True .ColorIndex = numero_da_cor End With End With .Name = “Cadastro” .Move Before:=Sheets(“Plan1”) .Visible = True End With End Sub O ponto em With.Font faz referência ao objeto A5. Para não haver ponto, a linha teria de ser With Range (“A5”).Font. Devemos sempre levar em consideração a importância de recuar as linhas, deixando a primeira e a última linha de cada bloco na mesma posição a partir do lado esquerdo. 78 Excel VBA - Módulo I 2.6. Formatando propriedades Os comandos para formatar planilhas são muitos e alguns até já foram abordados nesta apostila. A partir deste ponto, serão apresentadas as sintaxes de diversas propriedades, como bordas, cor de fundo e outras, que podem ser aplicadas às células. 2.6.1. Cor do preenchimento A cor do preenchimento, ou cor de fundo da célula, pode ser personalizada usando a propriedade Interior mais a propriedade que define o índice da cor ColorIndex. O índice da cor, ou seja, o número que corresponde a uma cor, pode variar entre 0 e 56. Consideremos o exemplo adiante e o resultado obtido a partir dele: Sub Ex_Formata() Range(“a2:c2”).Interior.ColorIndex = 35 End Sub 79 Programação de macros 2 Na seguinte tabela, temos a paleta de cores e os números referentes a cada uma das cores: Se usarmos o valor 0 (zero) ou xlNone para essa propriedade, então, a célula ficará sem preenchimento. Além disso, com essa mesma propriedade, podemos definir o padrão de preenchimento, ou seja, se haverá pontos ou hachuras na célula. O padrão também pode ser alterado por meio da janela Formatar Células, guia Preenchimento. 80 Excel VBA - Módulo I 2.6.2. Fonte Podemos formatar a fonte com a qual vamos trabalhar, ou seja, alterar as características do texto, usando a propriedade Font mais as propriedades que definem o estilo, a cor, o tamanho, entre outros. As principais propriedades de Font são as seguintes: • Name: Esta propriedade representa o nome da fonte, como Arial; • FontStyle: Esta propriedade representa o estilo da fonte. São quatro os estilos disponíveis: normal, negrito, itálico e negrito itálico. É recomendado o uso dessa propriedade quando as propriedades Bold e Italic não estão sendo usadas; • Bold: Esta propriedade pode aplicar ou retirar da fonte o estilo negrito quando definida como True ou False, respectivamente; • Italic: Esta propriedade pode aplicar ou retirar da fonte o estilo itálico quando definida como True ou False, respectivamente; • Size: Esta propriedade define o tamanho da fonte; • Strikethrough: Esta propriedade determina se a fonte será ou não tachada, ou seja, cortada com uma linha horizontal. Pode ser definida como True ou False; • Superscript: Esta propriedade determina se a fonte será formatada como sobrescrita. Pode ser definida como True ou False; • Subscript: Esta propriedade determina se a fonte será formada como subscrita. Pode ser definida como True ou False; • Underline: Esta propriedade refere-se ao sublinhado aplicado à fonte. Os valores para essa propriedade variam conforme o tipo de sublinhado desejado, portanto, pode ser: • xlUnderlineStyleNone: Para que a fonte não seja sublinhada; • xlUnderlineStyleSingle: Para sublinhado simples; 81 Programação de macros 2 • xlUnderlineStyleDouble: Para sublinhado duplo; • xlUndelineStyleSingleAccounting: Para sublinhado
Compartilhar