Baixe 76151325 - Program - an - Do - Em - Excel - Com - VBA - 2010 e outras Notas de estudo em PDF para Informática, somente na Docsity!
Programando em Excel com VBA
- Sub-rotinas ou Macros Pág.
- Editor do VBA
- Linguagem orientada a objeto
- Variáveis e constantes
- Funções
- Caixa de mensagem ( MsgBox ) e caixa de entrada ( InputBox )
- Estruturas de controle
- Aprimorando objetos, propriedades e métodos
- Escopo de variáveis, constantes e procedimentos
- Exercícios
- Importando dados e limpando o código gravado
- Exercício final: Fluxo de caixa em macro
- Depuração no VBA
Sub-rotinas ou macros
Uma macro ou sub-rotina é um pequeno programa com um conjunto de instruções. Estas instruções são escritas numa linguagem denominada Visual Basic for Applications (VBA). Usando o VBA podemos acelerar a realização de qualquer tarefa no Excel.
As sub-rotinas são procedimentos delimitados pelas palavras-chave Sub e End sub. Sua declaração tem a seguinte estrutura: Sub <nome_da_macro> ( ) <corpo_da_macro> End Sub Estas sub-rotinas ou macros são designadas pelo nome que lhe atribuímos e não recebem parâmetros do exterior. O corpo da macro é composto por um conjunto de instruções, sendo que cada instrução diferente necessita de estar numa linha diferente. Contudo, quando se trata de instruções demasiado grandes pode-se fazer sua partição em diversas linhas, recorrendo ao operador “_”, de modo facilitar a leitura. Por exemplo, o procedimento seguinte torna B5 a célula ativa e, em seguida, a formata com negrito. Sub SetActive() Worksheets("Plan1").Activate Worksheets("Plan1").Range("B5").Activate ActiveCell.Font.Bold = True End Sub
Como outro exemplo, se você desejar inserir uma fórmula na célula D6 usando o Visual Basic, você não terá que selecionar o intervalo D6. Você precisa apenas retornar o objeto Range e, em seguida, definir a propriedade Formula com a fórmula desejada, conforme mostrado no exemplo seguinte. Sub EnterFormula() Worksheets("Plan1").Range("D6").Formula = "=SUM(D2:D5)" End Sub
Editor do VBA
As macros gravadas acima são convertidas em instruções que podem ser visualizadas e editadas em um ambiente de programação, o Editor do Visual Basic ( Visual Basic Editor , VBE). O modo mais fácil de entrar no VBE é teclando o atalho Alt+F11. Um outro modo é selecionando Ferramentas / Macro / Editor do Visual Basic. Seguindo qualquer um desses passos, encontraremos à direita do painel que se abre as linhas de código das duas macros até o momento gravadas. À esquerda poderemos encontrar uma janela denominada Project Explorer ou mesmo Project Browser. Esta janela se assemelha ao Windows Explorer e permite que exploremos os arquivos que estiverem abertos no Excel, assim como os códigos que eles contêm. Podem haver diversas pastas abertas por outros programas que se instalam como módulos de macro no MS Office, tal como o Adobe PDF Writer e outros. O arquivo ou pasta que salvamos contendo as macros gravadas aparece como um projeto VBA e contém dois folders. O primeiro, Microsoft Excel Objetos, concentrará os códigos que forem construídos na pasta e em suas planilhas. O segundo, Módulos, registra os módulos onde são escritas ou gravadas as linhas de código das macros. Inicialmente, poderemos ter um único módulo, ou mesmo dois, dependendo de como realizamos a gravação das macros. Além disso, podemos inserir novos módulos.
Visualizando as linhas de código das macros gravadas
Com o VBE aberto, visualize as macros gravadas. Caso não estejam aparecendo, dê um duplo clique no Módulo1, no Project Explorer. Veja abaixo as linhas de código criadas. Compare a macro Absoluta com a macro Relativa e identifique as diferenças. Faça pequenas mudanças nos códigos e observe seus efeitos na execução das duas macros. Podemos proceder à execução de uma macro estando no ambiente VBE. Basta colocarmos o cursor em qualquer linha do código da macro e teclarmos F5 ou selecionarmos Executar / Executar Sub. Uma macro sempre começa com a declaração Sub, seguida de seu nome e os parêntesis (). A última declaração é End Sub. As linhas que começam com apóstrofo são linhas de comentários e não são interpretadas quando da execução do programa.
Sub Absoluta() ' ' Absoluta Macro ' Macro gravada em 21/7/2006 por Administrador
' Atalho do teclado: Ctrl+a ' Range("A2").Select ActiveCell.FormulaR1C1 = "UERJ" Range("A3").Select ActiveCell.FormulaR1C1 = "FEN" Range("A4").Select End Sub
Sub Relativa() ' ' Relativa Macro ' Macro gravada em 21/7/2006 por Administrador ' ' Atalho do teclado: Ctrl+r ' ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "UERJ" ActiveCell.Offset(1, 0).Range("A1").Select ActiveCell.FormulaR1C1 = "FEN" ActiveCell.Offset(1, 0).Range("A1").Select End Sub
Criando um botão de macro
Quando desejamos executar uma macro em uma planilha, estando esta maximizada, podemos usar as teclas de atalho da macro ou podemos selecionar Ferramentas / Macro / Macros, marcar a macro desejada e clicar o botão Executar. Entretanto, podemos formatar um botão de acionamento específico para cada macro de nossa planilha, tornando supérflua a memorização de teclas de atalho. Para criar um botão de macro, torne visível a barra de ferramentas de Formulários. Clique no ícone de Botão de comando e desenhe um botão de comando em algum lugar da planilha. Uma caixa de diálogo irá se abrir automaticamente, solicitando que seja informada qual macro deverá ser correlacionada a este botão.
Linguagem orientada a objeto
Toda programação em macro é baseada em modelos de objetos, sendo por isso a linguagem VBA denominada “orientada a objeto”. Definamos objeto como uma parte de um ambiente virtual que possui certas propriedades e funções (ou métodos). Bill Jelen “Mr. Excel” & Syrstad se utilizam de uma instrução do nosso cotidiano, chutar a bola, para ilustrar como a linguagem VBA funciona. Nesta linguagem, esta instrução seria escrita assim:
Bola.Chutar
Nesta instrução, Bola é o objeto é Chutar é um método associado ao objeto Bola. Se tivermos uma coleção de bolas de diversos tipos, podemos definir que a bola de futebol é a que será chutada. A coleção será apresentada no plural, conforme abaixo: Bolas(“Futebol”).Chutar
Objetos mais comuns do Excel
Objetos são elementos caracterizados por um conjunto de propriedades e que possuem um determinado comportamento. Por exemplo, uma janela do Windows é um objeto. Também são objetos o gráfico, o botão de comando, o arquivo, a figura, etc. Existem mais de 100 objetos no Excel, mas provavelmente você precisará conhecer de imediato apenas um pequeno grupo de objetos. Os objetos mais comuns do Excel são Application , Workbook , Worksheet e Range. Form , Chart e ChartObject também são objetos bastante explorados. Application é o objeto do topo hierárquico, representando o próprio programa Excel. O programa Excel pode conter diversas pastas (Workbooks) abertas. Cada pasta pode conter várias planilhas (Worksheets), que contêm inúmeras células. O objeto Range consiste em uma ou mais células de uma planilha. Como exemplo, referenciar a planilha 1 dentro do arquivo Dados.xls seria de acordo com a expressão abaixo:
Application.Workbooks(“Dados.xls”).Sheets(1)
Como outro exemplo, poderíamos registrar na célula A1 da mesma planilha o texto “Receita” da seguinte forma:
Application.Workbooks(“Dados.xls”).Sheets(1).Range(“A1”).Value =“Receita”
Entretanto, esta instrução pode ser simplificada. O objeto Application pode ser omitido, sendo referenciado de forma implícita. Além disso, se estivermos interessados somente na pasta que estiver ativa, podemos eliminar a referência a Workbooks(“Dados.xls”). Assim, a instrução acima ficaria simplificada conforme abaixo:
Sheets(1).Range(“A1”).Value =“Receita”
Porém, se estivermos concentrados na célula A1 da planilha ativa, a expressão pode ficar mais reduzida ainda:
Range(“A1”).Value =“Receita”
Um outro objeto bastante importante é aquele que descreve a célula ativa. Trata-se de Activecell. (Na verdade, Activecell é uma propriedade do objeto Worksheet que retorna um objeto Range.)
Coleção
Uma coleção é um objeto que contém uma série de outros objetos, geralmente, mas nem sempre, do mesmo tipo. No Microsoft Excel, por exemplo, o objeto Workbooks contém todos os objetos Workbook que estiverem abertos. No Visual Basic, a coleção Forms contém todos os objetos Form de um aplicativo. Os itens de uma coleção podem ser identificados por número ou por nome. Por exemplo, no procedimento a seguir, Workbooks(1) identifica o primeiro objeto Workbook aberto.
Sub CloseFirst() Workbooks(1).Close End Sub
Você também pode manipular toda uma coleção de objetos caso os objetos compartilhem métodos comuns. Por exemplo, o procedimento a seguir fecha todos os formulários abertos.
Sub CloseAll() Forms.Close End Sub
Propriedades e métodos de um objeto
Um método é uma ação que um objeto pode executar. Por exemplo, ClearContents é um método que limpa as fórmulas de um intervalo, ou os dados de um gráfico, deixando intacta a formatação. Exemplos:
de alguns é opcional. Felizmente, podemos utilizar a Ajuda no ambiente VBE quando da codificação de uma instrução. Por sua vez, sendo a cor uma das propriedades da bola de futebol, esta cor poderia ser definida conforme exemplo abaixo:
Bolas (“Futebol”).Cor=Branca
Nem sempre é fácil diferenciarmos propriedades de métodos para os objetos do Excel. Felizmente, na maioria das vezes não precisaremos nos preocupar com esta distinção. Obs.: a ordem dos parâmetros de um método pode ser alterada, desde que eles sejam nomeados (vide adiante as funções MsgBox e InputBox ).
Obtendo informações sobre objetos, propriedades e métodos
Para obtermos mais informações sobre os objetos do Excel, suas propriedades e métodos, podemos utilizar três fontes que se complementam. A primeira é usar a Ajuda do VBE. A expressão “Objetos do Microsoft Excel” dará acesso a praticamente todos os objetos que podem ser úteis a quem pretende aprimorar seus conhecimentos em programação VBA. Quando desejamos conhecer os detalhes referentes a um determinado objeto, propriedade ou método presente em uma linha de comando de uma macro, podemos colocar o cursor sobre o seu nome e teclar o atalho F1. Um arquivo da Ajuda será aberto imediatamente. O gravador de macro também é um excelente meio de se aprender a programação em VBA, apesar de possuir grandes limitações. Se você deseja manipular uma propriedade de um objeto específico, como por exemplo as bordas ou a cor de fundo de uma célula, e não conhece exatamente o nome desta propriedade, pode gravar a ação que deseja ser transformada em macro. Em seguida, basta analisar as linhas de código criadas pelo gravador. Cabe reforçar que alguns objetos não são do alcance do gravador de macro. É o caso de dois importantes objetos de comunicação entre o programa e o usuário: a caixa de mensagem e a caixa de entrada. Por último, podemos também usar o Pesquisador de Objeto no VBE, que é acionado pelo atalho F2. Esta alternativa é mais aconselhável para quem está mais familiarizado com o VBA.
Eventos
Um evento é uma ação reconhecida por um objeto, como clicar o mouse ou pressionar uma tecla e para a qual você pode escrever código para responder. Podem ocorrer eventos em resposta a uma ação do usuário ou a um código do programa.
Os eventos de pasta de trabalho entram no módulo EstaPasta_de_trabalho , visível no Project Explorer ; os eventos de planilha entram no módulo da planilha que eles afetam (como Plan ); os eventos de gráfico entram no módulo da planilha de gráfico que eles afetam (como Gráf ). Quando um módulo EstaPasta_de_Trabalho, Plan1, Gráf1 está ativo, os eventos correspondentes ficam disponíveis por meio dos menus suspensos Objeto (à esquerda) e Procedimento (à direita). Depois que o objeto é selecionado, o menu suspenso Procedimento atuializa a lista de eventos disponíveis para esse objeto. Os eventos em nível de planilha, por exemplo, ocorrem quando uma planilha é ativada ou o usuário altera uma célula de planilha. O exemplo seguinte ajusta o tamanho das colunas de A até F sempre que a planilha é recalculada.
Private Sub Worksheet_Calculate() Columns("A:F").AutoFit End Sub
Como outro exemplo, o evento Activate ocorre quando uma pasta de trabalho, planilha, folha de gráfico ou gráfico incorporado é ativado. O exemplo abaixo classifica o intervalo A1:A quando a pasta de trabalho é ativada.
Private Sub Worksheet_ Activate () Range("a1:a10").Sort Key1:=Range("a1"), Order:=xlAscending End Sub
Os eventos podem ainda fazer chamadas de sub-rotina ou de função fora de seus próprios módulos. Exemplo: Suponha que você pretenda executar uma macro sempre que abrir uma determinada pasta. 1º Crie a macro que pretende executar. 2º No Editor de Visual Basic, na janela Project Explorer selecione o objeto Esta Pasta. Na janela de edição repare nas duas caixinhas que se encontram na parte superior. A do lado esquerdo indica G era l clique nela e selecione o elemento pasta , na caixinha da direita selecione o evento Open. 3º Automaticamente aparecerá um procedimento na janela de edição cujo nome será Worksheet_Open. Tudo o que for escrito no seu conteúdo será executado quando o documento for aberto. Neste caso, indique o nome da macro desejada.
Variáveis e constantes
Variáveis
De acordo com Cinto & Góes, variável é um local nomeado da memória onde são guardados dados que podem ser modificados durante a execução do programa. Por sua vez, constantes são itens nomeados que mantêm um valor constante por toda a execução de um programa. As regras de nomeação de constantes e variáveis são: o nome deve começar com uma letra, mas pode ser alfanumérico; não pode haver espaços entre caracteres; não há distinção entre maiúsculo e minúsculo; não pode ter mais do que 255 caracteres; não podem ser usadas palavras já reservadas pelo VBA, tais como Range ou Sheet.
Tipos de dados armazenados nas variáveis
Os tipos de dados armazenados em variáveis ou em constantes no VBA são: Tipos de dados Bytes usados Valor Byte 1 Números sem sinal entre 0 e 255 Integer 2 Números inteiros, de –32.768 a 32. Long 4 Números inteiros, de –2.147.483.648 a 2.147.483. Single 4 Número real, 1.401298E-45 a 3.402823E38 (positivo ou negativo) Date 8 Datas Double 8 Número real, de 4.94065645841247E-324 a 1.79769313486232E308 (positivo ou negativo) Boolean 2 Verdadeiro (True) ou Falso (False) Currency 8 Moeda Object 4 Um objeto do Excel Range 4 Um conjunto de células String 1 por caractere Conjunto de caracteres Variant 16 Qualquer tipo de dados
Declarando variáveis
Podemos formatar variáveis que armazenem diferentes tipos de dados. Isto se faz por meio da declaração de variáveis, utilizando-se a instrução Dim. Por exemplo, na linha a seguir exemplificamos uma declaração de variável:
Dim IdadeMin As Integer
Você pode declarar diversas variáveis em uma instrução. Para especificar um tipo de dados, você deve incluir o tipo de dados para cada variável. Na instrução a seguir, as variáveis intX, intY e intZ são declaradas como tipo Integer.
Dim intX As Integer, intY As Integer, intZ As Integer
Se você introduz um nome de variável sem qualquer declaração prévia do tipo de dados, o VBA automaticamente assume que esta variável é do tipo Variant. Na instrução a seguir, intX e intY são declaradas como tipo Variant; apenas intZ é declarada como tipo Integer.
Dim intX, intY, intZ As Integer
Variáveis de diferentes tipos de dados podem ser declaradas em separado, mas também podem ser declaradas em conjunto, conforme abaixo:
Dim Nome As String, Idade As Integer, Salario As Currency
Benefícios na declaração de variáveis
Caso não declaremos o tipo referente a uma variável, o que é opcional para o VBA, esta poderá armazenar dados de qualquer tipo. Isto parece vantajoso, mas na verdade a declaração da variável tem diversas vantagens. Em primeiro lugar, torna o programa mais organizado, facilitando o entendimento por parte dos outros e a correção de erros por parte do programador. Sempre que você declarar as variáveis, o VBA o informará caso encontre alguma variável sem declaração. Por exemplo, considere que você declarou todas variáveis de seu programa, e que uma destas, nomeada ReceitaVenda , foi formatada para armazenar valores em moeda. Caso, por exemplo, ao longo do programa você digite por engano a palavra RecitaVenda , o VBA tratará este termo como uma possível variável ainda não declarada e o informará, permitindo assim a correção imediata de um erro que de outra forma levaria bastante tempo para ser corrigido. Uma outra vantagem importante é a de que o VBA sempre checará se o usuário do programa estará alimentando uma variável com o tipo de dado definido na sua declaração. Por último, com a declaração de variáveis a macro ocupa menos espaço na memória e é executada mais rapidamente, o que torna-se perceptível com macros mais pesadas. Como mencionado, a declaração de variáveis é benéfica por diversos motivos. Deste modo, caso você queira é possível forçar tal procedimento, bastando incluir acima de todos os módulos do programa a instrução Option Explicit. Essa instrução obriga-o a declarar explicitamente todas as variáveis dentro do módulo.
Para criar uma variável de objeto, (1) declare a variável de objeto e (2) atribua a variável de objeto a um objeto.
Declarando uma variável de objeto
Utilize a instrução Dim ou uma das outras instruções de declaração ( Public , Private ou Static ) para declarar uma variável de objeto. Uma variável que se refira a um objeto deve ser uma Variant , um Object ou um tipo específico de objeto. Por exemplo, as declarações a seguir são válidas:
' Declara MyObject como o tipo de dados Variant. Dim MyObject ' Declara MyObject como o tipo de dados Object. Dim MyObject As Object ' Declara MyObject como o tipo de dados Range. Dim MyObject As Range
Observação Se você utilizar uma variável de objeto sem declará-la primeiro, o seu tipo de dados será, como padrão, Variant. Você pode declarar uma variável de objeto com o tipo de dados Object em situações onde o tipo de objeto específico só se torna conhecido na execução do procedimento. Utilize o tipo de dados Object para criar uma referência genérica a qualquer objeto. Caso conheça o tipo de objeto específico, deve declarar a variável de objeto como pertencendo a esse tipo de objeto, pois a declaração de tipos de objeto específicos proporciona a verificação automática do tipo, código mais rápido e melhoria em legibilidade.
Atribuindo uma variável de objeto a um objeto
Utilize a instrução Set para atribuir um objeto a uma variável de objeto. O exemplo abaixo declara uma variável de objeto e logo depois a atribui a um objeto.
Sub ObjTest() Dim theRange As Range Set theRange = ActiveSheet.Range(“A1:C25”) TheRange.Value = 19 End Sub
Você também pode combinar a declaração de uma variável de objeto com a atribuição de um objeto a ele utilizando a palavra-chave New com a instrução Set. Por exemplo:
Set MyObject= New Object ' Cria e atribui
Funções
Comparando Funções e Sub-rotinas
Funções e sub-rotinas ou macros são constituídas de uma série de instruções do VB que executam ações. A primeira diferença entre elas é que funções geralmente retornam valor, enquanto sub-rotinas não. Uma segunda diferença é que podemos criar macros utilizando o gravador de macros, embora possamos criar macros mais refinadas no ambiente VBA. Funções só podem ser criadas no ambiente VBA. Para criar uma função ou uma sub-rotina no ambiente VBA é necessário selecionar um módulo na Janela de código. Caso não haja algum, podemos criar um novo módulo selecionando Inserir / Módulo. Selecionado o módulo, podemos escrever a macro ou a função integralmente ou podemos recorrer ao caminho Inserir / Procedimento para que o VBA crie a estrutura da rotina.
Funções
Funções são procedimentos delimitados pelas palavras-chave Function e End Function. Todas as funções utilizadas no Excel são desse tipo. Sua declaração tem a seguinte estrutura:
Function <nome da função> (<argumento ou parâmetro1>, ,...) <nome da função> = <Valor ou expressão> End Function
A função é identificada pelo nome, pelo número e tipo de argumentos recebidos, e tem como objetivo executar um conjunto de instruções e produzir um valor final. Isto é, sempre que se pretender executar uma função é sabido a priori que ela produzirá um valor. Entretanto, algumas funções não precisam de argumentos, como p. ex. as funções Rand() e Agora(). Outras não retornam valor, como p. ex. a função MsgBox.
Definição do tipo de parâmetros e do tipo da função
Todos os elementos de entrada e saída de uma função têm um tipo de dados atribuído. Assim, cada parâmetro poderá ser definido com o tipo de dado respectivo e a função também poderá ser definida com o tipo de dado que ela enviará para o exterior. Podemos então refinar a definição de uma função:
Function <Nome da Função> ( <parâmetro1> As , … ) As
SaldoLiq = Valor - ValorCPMF MsgBox "Saldo Líquido = " & SaldoLiq End Sub
Obs.: Se a função CPMF fosse de escopo Private , não seria acessível a sub-rotina de outro módulo que não o seu. Confira.
Funções de planilha ou Worksheet functions
As funções normalmente usadas nas planilhas também podem ser usadas nas macros, a não ser que o VBA já possua uma função que desempenhe o mesmo papel. Por exemplo, podemos registrar em uma célula a soma de uma Range:
Worksheets(1).Range(“D6”).Formula = ” = Sum(D2:D5)”
Porém, caso a Range esteja associada a uma variável objeto (p. ex., variável “Faixa” abaixo), torna-se necessária a propriedade WorksheetFunction:
Worksheets(1).Range(“D6”).Formula = ” = Application.WorksheetFunction.Sum( Faixa )”
Caixa de mensagem e caixa de entrada
O VBA oferece ao usuário duas interfaces de comunicação com o programa ou macro. A primeira, MsgBox , é uma interface de saída que exibe apenas uma mensagem e aguarda que o usuário selecione um botão. A segunda, InputBox , é uma interface de entrada de dados que exibe uma mensagem e aguarda uma resposta do usuário. Enquanto MsgBox é uma função do VBA, InputBox pode ser tanto uma função quanto um método, como veremos adiante.
Função MsgBox
A função MsgBox exibe uma mensagem ( Prompt ) em uma caixa de diálogo com um ou mais botões. Quando o usuário seleciona um deles, a função retorna um integer que indica qual botão foi clicado.
Sintaxe resumida:
MsgBox(prompt, buttons, title)
Prompt (parâmetro necessário): expressão exibida como mensagem na caixa de diálogo. Se prompt consistir em mais de uma linha, você poderá separar as linhas utilizando um caractere de retorno de carro (Chr(13)), um caractere de alimentação de linha (Chr(10)) ou uma combinação de caracteres de retorno de carro e alimentação de linha (Chr(13) & Chr(10)) entre cada linha. Buttons (parâmetro opcional): Expressão numérica que é a soma de valores que especifica o número e o tipo de botões a exibir, o estilo de ícone a utilizar, a identidade do botão padrão e a modalidade da caixa de mensagem. Se omitido, o valor padrão para buttons é 0. Title (parâmetro opcional): Expressão de seqüência de caracteres exibida na barra de título da caixa de diálogo. Se você omitir title , o nome do aplicativo será inserido na barra de título. As definições do argumento buttons são as seguintes: Constante Valor Descrição Definindo o número e o tipo de botões exibidos: VbOKOnly 0 Exibe somente o botão OK. VbOKCancel 1 Exibe os botões OK e Cancelar. VbAbortRetryIgnore 2 Exibe os botões Abortar, Repetir e Ignorar. VbYesNoCancel 3 Exibe os botões Sim, Não e Cancelar.
