Program an Do Em Excel Com VBA 2010

UERJ / FEN / DEIN

C. Borges; H. Rocha

Programando em Excel com VBAPg. Sub-rotinas ou Macros Editor do VBA Linguagem orientada a objeto Variveis e constantes Funes Caixa de mensagem (MsgBox) e caixa de entrada (InputBox) Estruturas de controle Aprimorando objetos, propriedades e mtodos Escopo de variveis, constantes e procedimentos Exerccios Importando dados e limpando o cdigo gravado Exerccio final: Fluxo de caixa em macro Depurao no VBA 02 04 07 13 17 20 24 31 42 45 51 56 58

Programando em Excel com VBA

1

UERJ / FEN / DEIN

C. Borges; H. Rocha

Sub-rotinas ou macrosUma macro ou sub-rotina um pequeno programa com um conjunto de instrues. Estas instrues so escritas numa linguagem denominada Visual Basic for Applications (VBA). Usando o VBA podemos acelerar a realizao de qualquer tarefa no Excel.

As sub-rotinas so procedimentos delimitados pelas palavras-chave Sub e End sub. Sua declarao tem a seguinte estrutura: Sub ( ) End Sub Estas sub-rotinas ou macros so designadas pelo nome que lhe atribumos e no recebem parmetros do exterior. O corpo da macro composto por um conjunto de instrues, sendo que cada instruo diferente necessita de estar numa linha diferente. Contudo, quando se trata de instrues demasiado grandes pode-se fazer sua partio em diversas linhas, recorrendo ao operador _, de modo facilitar a leitura. Por exemplo, o procedimento seguinte torna B5 a clula 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 frmula na clula D6 usando o Visual Basic, voc no ter que selecionar o intervalo D6. Voc precisa apenas retornar o objeto Range e, em seguida, definir a propriedade Formula com a frmula desejada, conforme mostrado no exemplo seguinte. Sub EnterFormula() Worksheets("Plan1").Range("D6").Formula = "=SUM(D2:D5)" End Sub


2

UERJ / FEN / DEIN

C. Borges; H. Rocha

Gravador de macros O ambiente do VBA possui um gravador que registra todas suas aes tanto numa planilha quanto num processador de texto e que as traduz para a linguagem VBA. A gravao de macros muito til quando voc no tem experincia suficiente para escrever linhas de cdigo em uma macro. Entretanto, possui graves limitaes que somente so resolvidas por meio da programao. Gravando a primeira macro 1. Abra um novo arquivo e selecione uma das planilhas 2. Clique na clula A1 3. Acione o gravador de macro selecionando Ferramentas / Macro / Gravar nova macro. 4. Na caixa de dilogo que surge nomeie a macro por Absoluta e atribua a ela o atalho Ctrl+a. Clique OK. Observe que dever surgir na tela uma barra de ferramentas contendo dois botes: o primeiro, Parar gravao, e o segundo, Referncia relativa. Certifique-se de que este ltimo boto no esteja acionado. 5. Clique na clula A2 e escreva UERJ 6. Clique na clula A3 e escreva FEN 7. Clique na clula A4 e acione o boto Parar gravao. 8. Para testar a gravao, apague o que foi digitado nas clulas e posicione o cursor em qualque clula da planilha 9. Tecle Ctrl+a e observe. 10. Limpe novamente as clulas, posicione o cursor agora em uma outra clula qualquer e tecle novamente Ctrl+a. 11. Observe que a macro insere o texto digitado na mesma posio, independentemente de onde esteja o cursor. Gravando uma macro com referncias relativas Repita os passos anteriores, com as seguintes modificaes: na etapa 4, nomeie a macro por Relativa e atribua a ela o atalho Ctrl+r. Clique o boto OK e logo depois clique o boto de Referncia relativa. Siga os demais passos como na gravao anterior. Teste a gravao e observe. Compare com a macro Absoluta. Ao final, grave o arquivo com o nome Abs_vs_Relativa.


3

UERJ / FEN / DEIN

C. Borges; H. Rocha

Editor do VBAAs macros gravadas acima so convertidas em instrues que podem ser visualizadas e editadas em um ambiente de programao, o Editor do Visual Basic (Visual Basic Editor, VBE). O modo mais fcil 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 cdigo 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 cdigos que eles contm. Podem haver diversas pastas abertas por outros programas que se instalam como mdulos 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 contm dois folders. O primeiro, Microsoft Excel Objetos, concentrar os cdigos que forem construdos na pasta e em suas planilhas. O segundo, Mdulos, registra os mdulos onde so escritas ou gravadas as linhas de cdigo das macros. Inicialmente, poderemos ter um nico mdulo, ou mesmo dois, dependendo de como realizamos a gravao das macros. Alm disso, podemos inserir novos mdulos. Visualizando as linhas de cdigo das macros gravadas Com o VBE aberto, visualize as macros gravadas. Caso no estejam aparecendo, d um duplo clique no Mdulo1, no Project Explorer. Veja abaixo as linhas de cdigo criadas. Compare a macro Absoluta com a macro Relativa e identifique as diferenas. Faa pequenas mudanas nos cdigos e observe seus efeitos na execuo das duas macros. Podemos proceder execuo de uma macro estando no ambiente VBE. Basta colocarmos o cursor em qualquer linha do cdigo da macro e teclarmos F5 ou selecionarmos Executar / Executar Sub. Uma macro sempre comea com a declarao Sub, seguida de seu nome e os parntesis (). A ltima declarao End Sub. As linhas que comeam com apstrofo so linhas de comentrios e no so interpretadas quando da execuo do programa.

Sub Absoluta() ' ' Absoluta Macro ' Macro gravada em 21/7/2006 por Administrador Programando em Excel com VBA 4

UERJ / FEN / DEIN

C. Borges; H. Rocha

' ' 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 boto 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 boto Executar. Entretanto, podemos formatar um boto de acionamento especfico para cada macro de nossa planilha, tornando suprflua a memorizao de teclas de atalho. Para criar um boto de macro, torne visvel a barra de ferramentas de Formulrios. Clique no cone de Boto de comando e desenhe um boto de comando em algum lugar da planilha. Uma caixa de dilogo ir se abrir automaticamente, solicitando que seja informada qual macro dever ser correlacionada a este boto.


5

UERJ / FEN / DEIN

C. Borges; H. Rocha

Formatando o boto de macro Se clicarmos com o boto direito do mouse sobre o boto de macro, este pode ser arrastado e redimensionado, ou mesmo excludo. Sua fonte pode ser formatada e o nome do boto pode ser alterado.

Pequenas dicas sobre o VBE 1. Uma macro pode ser facilmente executada em ambiente VBE, se colocarmos o cursor sobre o cdigo da macro e teclarmos F5. 2. Se colocarmos o cursor sobre uma palavra-chave ou comando e teclarmos F1, ser aberta uma janela de Ajuda sobre este comando. 3. Teclando F2 ser aberto o Pesquisador de Objeto ou Object Browser, ferramenta de consulta para a edio de macros no VBE 4. Como em um processador de texto, podemos recortar, copiar ou deletar palavras das linhas de cdigo de uma macro


6

UERJ / FEN / DEIN

C. Borges; H. Rocha

Linguagem orientada a objetoToda programao 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 funes (ou mtodos). Bill Jelen Mr. Excel & Syrstad se utilizam de uma instruo do nosso cotidiano, chutar a bola, para ilustrar como a linguagem VBA funciona. Nesta linguagem, esta instruo seria escrita assim: Bola.Chutar Nesta instruo, Bola o objeto Chutar um mtodo associado ao objeto Bola. Se tivermos uma coleo de bolas de diversos tipos, podemos definir que a bola de futebol a que ser chutada. A coleo ser apresentada no plural, conforme abaixo: Bolas(Futebol).Chutar Objetos mais comuns do Excel Objetos so elementos caracterizados por um conjunto de propriedades e que possuem um determinado comportamento. Por exemplo, uma janela do Windows um objeto. Tambm so objetos o grfico, o boto 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 so Application, Workbook, Worksheet e Range. Form, Chart e ChartObject tambm so objetos bastante explorados. Application o objeto do topo hierrquico, representando o prprio programa Excel. O programa Excel pode conter diversas pastas (Workbooks) abertas. Cada pasta pode conter vrias planilhas (Worksheets), que contm inmeras clulas. O objeto Range consiste em uma ou mais clulas de uma planilha. Como exemplo, referenciar a planilha 1 dentro do arquivo Dados.xls seria de acordo com a expresso abaixo: Application.Workbooks(Dados.xls).Sheets(1) Como outro exemplo, poderamos registrar na clula A1 da mesma planilha o texto Receita da seguinte forma: Application.Workbooks(Dados.xls).Sheets(1).Range(A1).Value =Receita


7

UERJ / FEN / DEIN

C. Borges; H. Rocha

Entretanto, esta instruo pode ser simplificada. O objeto Application pode ser omitido, sendo referenciado de forma implcita. Alm disso, se estivermos interessados somente na pasta que estiver ativa, podemos eliminar a referncia a Workbooks(Dados.xls). Assim, a instruo acima ficaria simplificada conforme abaixo: Sheets(1).Range(A1).Value =Receita Porm, se estivermos concentrados na clula A1 da planilha ativa, a expresso pode ficar mais reduzida ainda: Range(A1).Value =Receita Um outro objeto bastante importante aquele que descreve a clula ativa. Trata-se de Activecell. (Na verdade, Activecell uma propriedade do objeto Worksheet que retorna um objeto Range.) Coleo Uma coleo um objeto que contm uma srie de outros objetos, geralmente, mas nem sempre, do mesmo tipo. No Microsoft Excel, por exemplo, o objeto Workbooks contm todos os objetos Workbook que estiverem abertos. No Visual Basic, a coleo Forms contm todos os objetos Form de um aplicativo. Os itens de uma coleo podem ser identificados por nmero 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 tambm pode manipular toda uma coleo de objetos caso os objetos compartilhem mtodos comuns. Por exemplo, o procedimento a seguir fecha todos os formulrios abertos. Sub CloseAll() Forms.Close End Sub Propriedades e mtodos de um objeto Um mtodo uma ao que um objeto pode executar. Por exemplo, ClearContents um mtodo que limpa as frmulas de um intervalo, ou os dados de um grfico, deixando intacta a formatao. Exemplos: Programando em Excel com VBA 8

UERJ / FEN / DEIN

C. Borges; H. Rocha

Worksheets("Sheet1").Range("A1:G37").ClearContents Charts("Chart1").ChartArea.ClearContents Uma propriedade um atributo de um objeto que define uma das caractersticas do objeto (assim como tamanho, cor ou localizao na tela) ou um aspecto do seu comportamento (assim como se ela est ativada ou visvel). Para alterar as caractersticas de um objeto, voc pode alterar os valores das suas propriedades. Para definir o valor de uma propriedade, coloque, aps a referncia a um objeto, um ponto, o nome da propriedade, um sinal de igualdade (=) e o novo valor da propriedade. Por exemplo, o procedimento a seguir altera a legenda de um formulrio do Visual Basic atravs da definio da propriedade Caption. Sub ChangeName(newTitle) myForm.Caption = newTitle End Sub Mr. Excel & Syrstad fazem uma analogia entre nossa Gramtica e os componentes de uma instruo no VBA, conforme tabela a seguir.

Componente VBA Objeto Coleo

Anlogo a Nome Nome no plural

Nota

Normalmente especifica qual objeto da coleo. Ex.: Worksheets(1)

Mtodo Parmetro (Argumento) Propriedade

Verbo Advrbio

Object.Method Lista parmetros aps o mtodo. Separa o nome do parmetro do seu valor com :=

Adjetivo

Object.Property A propriedade pode ter seu valor definido ou simplesmente consultado; O separador entre o nome da propriedade e seu valor o sinal =

Voltando ao exemplo da instruo de chutar a bola, podemos planejar como a ao dever ser executada, atravs de parmetros. Por exemplo, nossa instruo poderia ficar assim: Bolas (Futebol).Chutar Direo:=Esquerda, Fora:=Muita Mr. Excel & Syrstad comentam que cada mtodo dentro do VBA tem sua lista de parmetros e que existe uma ordem de definio destes parmetros. Alm disso, a definio Programando em Excel com VBA 9

UERJ / FEN / DEIN

C. Borges; H. Rocha

de alguns opcional. Felizmente, podemos utilizar a Ajuda no ambiente VBE quando da codificao de uma instruo. 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 fcil diferenciarmos propriedades de mtodos para os objetos do Excel. Felizmente, na maioria das vezes no precisaremos nos preocupar com esta distino. Obs.: a ordem dos parmetros de um mtodo pode ser alterada, desde que eles sejam nomeados (vide adiante as funes MsgBox e InputBox). Obtendo informaes sobre objetos, propriedades e mtodos Para obtermos mais informaes sobre os objetos do Excel, suas propriedades e mtodos, podemos utilizar trs fontes que se complementam. A primeira usar a Ajuda do VBE. A expresso Objetos do Microsoft Excel dar acesso a praticamente todos os objetos que podem ser teis a quem pretende aprimorar seus conhecimentos em programao VBA. Quando desejamos conhecer os detalhes referentes a um determinado objeto, propriedade ou mtodo 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 tambm um excelente meio de se aprender a programao em VBA, apesar de possuir grandes limitaes. Se voc deseja manipular uma propriedade de um objeto especfico, como por exemplo as bordas ou a cor de fundo de uma clula, e no conhece exatamente o nome desta propriedade, pode gravar a ao que deseja ser transformada em macro. Em seguida, basta analisar as linhas de cdigo criadas pelo gravador. Cabe reforar que alguns objetos no so do alcance do gravador de macro. o caso de dois importantes objetos de comunicao entre o programa e o usurio: a caixa de mensagem e a caixa de entrada. Por ltimo, podemos tambm usar o Pesquisador de Objeto no VBE, que acionado pelo atalho F2. Esta alternativa mais aconselhvel para quem est mais familiarizado com o VBA. Eventos Um evento uma ao reconhecida por um objeto, como clicar o mouse ou pressionar uma tecla e para a qual voc pode escrever cdigo para responder. Podem ocorrer eventos em resposta a uma ao do usurio ou a um cdigo do programa. Programando em Excel com VBA 10

UERJ / FEN / DEIN

C. Borges; H. Rocha

Os eventos de pasta de trabalho entram no mdulo EstaPasta_de_trabalho, visvel no Project Explorer; os eventos de planilha entram no mdulo da planilha que eles afetam (como Plan); os eventos de grfico entram no mdulo da planilha de grfico que eles afetam (como Grf). Quando um mdulo EstaPasta_de_Trabalho, Plan1, Grf1 est ativo, os eventos correspondentes ficam disponveis por meio dos menus suspensos Objeto ( esquerda) e Procedimento ( direita). Depois que o objeto selecionado, o menu suspenso Procedimento atuializa a lista de eventos disponveis para esse objeto. Os eventos em nvel de planilha, por exemplo, ocorrem quando uma planilha ativada ou o usurio altera uma clula 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 grfico ou grfico incorporado ativado. O exemplo abaixo classifica o intervalo A1:A10 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 funo fora de seus prprios mdulos. 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 edio repare nas duas caixinhas que se encontram na parte superior. A do lado esquerdo indica Geral clique nela e selecione o elemento pasta, na caixinha da direita selecione o evento Open. 3 Automaticamente aparecer um procedimento na janela de edio cujo nome ser Worksheet_Open. Tudo o que for escrito no seu contedo ser executado quando o documento for aberto. Neste caso, indique o nome da macro desejada.


11

UERJ / FEN / DEIN

C. Borges; H. Rocha

Para visualizar os procedimentos de evento de uma planilha, clique com o boto direito na guia da planilha e clique em Exibir cdigo no menu de atalho. Selecione o nome do evento na caixa de listagem suspensa Procedimento.


12

UERJ / FEN / DEIN

C. Borges; H. Rocha

Variveis e constantesVariveis De acordo com Cinto & Ges, varivel um local nomeado da memria onde so guardados dados que podem ser modificados durante a execuo do programa. Por sua vez, constantes so itens nomeados que mantm um valor constante por toda a execuo de um programa. As regras de nomeao de constantes e variveis so: o nome deve comear com uma letra, mas pode ser alfanumrico; no pode haver espaos entre caracteres; no h distino entre maisculo e minsculo; no pode ter mais do que 255 caracteres; no podem ser usadas palavras j reservadas pelo VBA, tais como Range ou Sheet. Tipos de dados armazenados nas variveis Os tipos de dados armazenados em variveis ou em constantes no VBA so: Tipos de dados Byte Integer Long Single Bytes usados 1 2 4 4 Valor Nmeros sem sinal entre 0 e 255 Nmeros inteiros, de 32.768 a 32.767 Nmeros inteiros, de 2.147.483.648 a 2.147.483.647 Nmero real, 1.401298E-45 a 3.402823E38 (positivo ou negativo) Date Double 8 8 Datas Nmero real, de 4.94065645841247E-324 a 1.79769313486232E308 (positivo ou negativo) Boolean Currency Object Range String Variant 2 8 4 4 1 por caractere 16 Verdadeiro (True) ou Falso (False) Moeda Um objeto do Excel Um conjunto de clulas Conjunto de caracteres Qualquer tipo de dados

Declarando variveis Podemos formatar variveis que armazenem diferentes tipos de dados. Isto se faz por meio da declarao de variveis, utilizando-se a instruo Dim. Por exemplo, na linha a seguir exemplificamos uma declarao de varivel: Programando em Excel com VBA 13

UERJ / FEN / DEIN

C. Borges; H. Rocha

Dim IdadeMin As Integer Voc pode declarar diversas variveis em uma instruo. Para especificar um tipo de dados, voc deve incluir o tipo de dados para cada varivel. Na instruo a seguir, as variveis intX, intY e intZ so declaradas como tipo Integer. Dim intX As Integer, intY As Integer, intZ As Integer Se voc introduz um nome de varivel sem qualquer declarao prvia do tipo de dados, o VBA automaticamente assume que esta varivel do tipo Variant. Na instruo a seguir, intX e intY so declaradas como tipo Variant; apenas intZ declarada como tipo Integer. Dim intX, intY, intZ As Integer Variveis de diferentes tipos de dados podem ser declaradas em separado, mas tambm podem ser declaradas em conjunto, conforme abaixo: Dim Nome As String, Idade As Integer, Salario As Currency Benefcios na declarao de variveis Caso no declaremos o tipo referente a uma varivel, o que opcional para o VBA, esta poder armazenar dados de qualquer tipo. Isto parece vantajoso, mas na verdade a declarao da varivel tem diversas vantagens. Em primeiro lugar, torna o programa mais organizado, facilitando o entendimento por parte dos outros e a correo de erros por parte do programador. Sempre que voc declarar as variveis, o VBA o informar caso encontre alguma varivel sem declarao. Por exemplo, considere que voc declarou todas variveis 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 possvel varivel ainda no declarada e o informar, permitindo assim a correo 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 usurio do programa estar alimentando uma varivel com o tipo de dado definido na sua declarao. Por ltimo, com a declarao de variveis a macro ocupa menos espao na memria e executada mais rapidamente, o que torna-se perceptvel com macros mais pesadas. Como mencionado, a declarao de variveis benfica por diversos motivos. Deste modo, caso voc queira possvel forar tal procedimento, bastando incluir acima de todos os mdulos do programa a instruo Option Explicit. Essa instruo obriga-o a declarar explicitamente todas as variveis dentro do mdulo. Programando em Excel com VBA 14

UERJ / FEN / DEIN

C. Borges; H. Rocha

Caso um mdulo inclua a instruo Option Explicit, ocorrer um erro em tempo de compilao (perodo durante o qual o cdigo-fonte traduzido em cdigo executvel) quando o Visual Basic encontrar um nome de varivel que ainda no tenha sido declarada ou que apresente algum erro de digitao (Ajuda do VBA). Declarando constantes Por intermdio da declarao de uma constante, voc pode atribuir um nome significativo a um valor. Utilize a instruo Const para declarar uma constante e definir o seu valor. Depois que uma constante tiver sido declarada, no ser possvel modific-la ou atribuir-lhe um novo valor. As constantes podem ser declaradas como um destes tipos de dados: Boolean, Byte, Integer, Long, Currency, Single, Double, Date, String ou Variant. Como voc j conhece o valor de uma constante, pode especificar o tipo de dados em uma instruo Const. Voc pode declarar diversas constantes em uma instruo. Para especificar um tipo de dados, voc deve incluir o tipo de dados de cada constante. Constantes intrnsecas Uma constante intrnseca aquela fornecida por um aplicativo. As constantes do Visual Basic esto listadas na biblioteca de objetos e podem ser visualizadas com o Pesquisador de objeto. Como voc no pode desativar constantes intrnsecas, no pode criar uma constante definida pelo usurio com o mesmo nome. Como exemplo, xlLandscape (Paisagem) e xlPortrait (Retrato) so duas constantes que definem a orientao de uma pgina. Como exemplo de seu uso: ActiveSheet.PageSetut.Orientation = xlLandscape Cada constante intrnseca realmente possui um valor numrico particular. No exemplo abaixo podemos buscar na caixa de mensagem o valor da constante xlLandscape (igual a 2). Sub ShowValue() MsgBox xlLandscape End Sub Varivel de objeto Varivel de objeto uma varivel que contm uma referncia a um objeto. Este pode ser uma planilha, um grfico, uma Range, etc. A varivel de objeto deve ter as mesmas propriedades e mtodos do objeto referenciado. Voc pode tratar uma varivel de objeto exatamente como o objeto ao qual ela se refere. Programando em Excel com VBA 15

UERJ / FEN / DEIN

C. Borges; H. Rocha

Para criar uma varivel de objeto, (1) declare a varivel de objeto e (2) atribua a varivel de objeto a um objeto. Declarando uma varivel de objeto Utilize a instruo Dim ou uma das outras instrues de declarao (Public, Private ou Static) para declarar uma varivel de objeto. Uma varivel que se refira a um objeto deve ser uma Variant, um Object ou um tipo especfico de objeto. Por exemplo, as declaraes a seguir so vlidas: ' 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 Observao Se voc utilizar uma varivel de objeto sem declar-la primeiro, o seu tipo de dados ser, como padro, Variant. Voc pode declarar uma varivel de objeto com o tipo de dados Object em situaes onde o tipo de objeto especfico s se torna conhecido na execuo do procedimento. Utilize o tipo de dados Object para criar uma referncia genrica a qualquer objeto. Caso conhea o tipo de objeto especfico, deve declarar a varivel de objeto como pertencendo a esse tipo de objeto, pois a declarao de tipos de objeto especficos proporciona a verificao automtica do tipo, cdigo mais rpido e melhoria em legibilidade. Atribuindo uma varivel de objeto a um objeto Utilize a instruo Set para atribuir um objeto a uma varivel de objeto. O exemplo abaixo declara uma varivel 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 tambm pode combinar a declarao de uma varivel de objeto com a atribuio de um objeto a ele utilizando a palavra-chave New com a instruo Set. Por exemplo: Set MyObject= New Object ' Cria e atribui 16


UERJ / FEN / DEIN

C. Borges; H. Rocha

FunesComparando Funes e Sub-rotinasFunes e sub-rotinas ou macros so constitudas de uma srie de instrues do VB que executam aes. A primeira diferena entre elas que funes geralmente retornam valor, enquanto sub-rotinas no. Uma segunda diferena que podemos criar macros utilizando o gravador de macros, embora possamos criar macros mais refinadas no ambiente VBA. Funes s podem ser criadas no ambiente VBA. Para criar uma funo ou uma sub-rotina no ambiente VBA necessrio selecionar um mdulo na Janela de cdigo. Caso no haja algum, podemos criar um novo mdulo selecionando Inserir / Mdulo. Selecionado o mdulo, podemos escrever a macro ou a funo integralmente ou podemos recorrer ao caminho Inserir / Procedimento para que o VBA crie a estrutura da rotina.

FunesFunes so procedimentos delimitados pelas palavras-chave Function e End Function. Todas as funes utilizadas no Excel so desse tipo. Sua declarao tem a seguinte estrutura: Function (, ,...) = End Function A funo identificada pelo nome, pelo nmero e tipo de argumentos recebidos, e tem como objetivo executar um conjunto de instrues e produzir um valor final. Isto , sempre que se pretender executar uma funo sabido a priori que ela produzir um valor. Entretanto, algumas funes no precisam de argumentos, como p. ex. as funes Rand() e Agora(). Outras no retornam valor, como p. ex. a funo MsgBox. Definio do tipo de parmetros e do tipo da funo Todos os elementos de entrada e sada de uma funo tm um tipo de dados atribudo. Assim, cada parmetro poder ser definido com o tipo de dado respectivo e a funo tambm poder ser definida com o tipo de dado que ela enviar para o exterior. Podemos ento refinar a definio de uma funo: Function ( As , ) As Programando em Excel com VBA 17

UERJ / FEN / DEIN

C. Borges; H. Rocha

= End Function Nota: Se os tipos no forem definidos ser assumido por padro como sendo do tipo Variant. Criando uma funo Exemplo: Escreva a funo CPMF como abaixo: Public Function CPMF(Valor As Currency, Taxa As Single) As Currency CPMF = Valor * (Taxa / 100) End Function Execuo de uma funo Uma Funo definida pelo usurio poder ser executada dentro de uma clula numa planilha, semelhana de qualquer uma outra funo do Excel, ou dentro de qualquer outra Funo ou Sub-rotina. Para executar a funo numa clula, devemos selecionar Inserir / Funo e optar pela categoria Definida pelo usurio. Experimente com a funo recm-criada. Uma funo dentro de outra funo Escreva a funo Saldo conforme abaixo: Public Function Saldo(Valor As Currency, Taxa As Single) Saldo = Valor - CPMF(Valor, Taxa) End Function Observe que o argumento Taxa da funo CPMF tambm deve ser definido como argumento da funo Saldo. Caso contrrio, o usurio no ser chamado a informar a taxa de juros e o VBA acusar erro. Uma funo dentro de uma Sub-rotina Escreva a Sub-rotina abaixo, que utiliza a funo CPMF criada. A sub-rotina e a funo CPMF podem ficar num nico mdulo ou em mdulos separados. Public Sub Calcula_Saldo_Liq() Dim Valor As Currency, ValorCPMF As Currency, SaldoLiq As Currency, Taxa _ As Single Valor = InputBox("Informe o valor da aplicao") Taxa = InputBox("Informe a taxa % da CPMF") ValorCPMF = CPMF(Valor, Taxa) Programando em Excel com VBA 18

UERJ / FEN / DEIN

C. Borges; H. Rocha

SaldoLiq = Valor - ValorCPMF MsgBox "Saldo Lquido = " & SaldoLiq End Sub Obs.: Se a funo CPMF fosse de escopo Private, no seria acessvel a sub-rotina de outro mdulo que no o seu. Confira. Funes de planilha ou Worksheet functions As funes normalmente usadas nas planilhas tambm podem ser usadas nas macros, a no ser que o VBA j possua uma funo que desempenhe o mesmo papel. Por exemplo, podemos registrar em uma clula a soma de uma Range: Worksheets(1).Range(D6).Formula = = Sum(D2:D5) Porm, caso a Range esteja associada a uma varivel objeto (p. ex., varivel Faixa abaixo), torna-se necessria a propriedade WorksheetFunction: Worksheets(1).Range(D6).Formula = = Application.WorksheetFunction.Sum(Faixa)


19

UERJ / FEN / DEIN

C. Borges; H. Rocha

Caixa de mensagem e caixa de entradaO VBA oferece ao usurio duas interfaces de comunicao com o programa ou macro. A primeira, MsgBox, uma interface de sada que exibe apenas uma mensagem e aguarda que o usurio selecione um boto. A segunda, InputBox, uma interface de entrada de dados que exibe uma mensagem e aguarda uma resposta do usurio. Enquanto MsgBox uma funo do VBA, InputBox pode ser tanto uma funo quanto um mtodo, como veremos adiante. Funo MsgBox A funo MsgBox exibe uma mensagem (Prompt) em uma caixa de dilogo com um ou mais botes. Quando o usurio seleciona um deles, a funo retorna um integer que indica qual boto foi clicado. Sintaxe resumida: MsgBox(prompt, buttons, title) Prompt (parmetro necessrio): expresso exibida como mensagem na caixa de dilogo. 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 alimentao de linha (Chr(10)) ou uma combinao de caracteres de retorno de carro e alimentao de linha (Chr(13) & Chr(10)) entre cada linha. Buttons (parmetro opcional): Expresso numrica que a soma de valores que especifica o nmero e o tipo de botes a exibir, o estilo de cone a utilizar, a identidade do boto padro e a modalidade da caixa de mensagem. Se omitido, o valor padro para buttons 0. Title (parmetro opcional): Expresso de seqncia de caracteres exibida na barra de ttulo da caixa de dilogo. Se voc omitir title, o nome do aplicativo ser inserido na barra de ttulo. As definies do argumento buttons so as seguintes: Constante Valor Descrio

Definindo o nmero e o tipo de botes exibidos: VbOKOnly VbOKCancel VbAbortRetryIgnore VbYesNoCancel 0 1 2 3 Exibe somente o boto OK. Exibe os botes OK e Cancelar. Exibe os botes Abortar, Repetir e Ignorar. Exibe os botes Sim, No e Cancelar.


20

UERJ / FEN / DEIN

C. Borges; H. Rocha

VbYesNo VbRetryCancel

4 5

Exibe os botes Sim e No. Exibe os botes Repetir e Cancelar.

Definindo o estilo de cone que aparece na caixa de dilogo: vbCritical vbQuestion vbExclamation vbInformation 16 32 48 64 Exibe o cone Mensagem crtica. Exibe o cone Consulta de aviso. Exibe o cone Mensagem de aviso. Exibe o cone Mensagem de informao.

Determinando qual boto o padro: vbDefaultButton1 vbDefaultButton2 vbDefaultButton3 0 256 512 O primeiro boto o padro. O segundo boto o padro. O terceiro boto o padro.

Os valores retornados pelo MsgBox so apresentados abaixo. Constante vbOK vbCancel vbAbort vbRetry vbIgnore vbYes vbNo Valor (integer) 1 2 3 4 5 6 7 Descrio OK Cancelar Abortar Repetir Ignorar Sim No

Cinto e Ges exemplificam abaixo o uso do MsgBox com uma macro que pergunta ao usurio se ele deseja salvar as alteraes, com as opes Sim, No e Cancelar. Sub Msg_SalvarAlteracoes() Opcao = MsgBox(Deseja salvar as alteraes?, 3+32+256, Confirmao) If Opcao = vbYes Then MsgBox(Clicou em Sim) Elseif Opcao = vbNo Then MsgBox(Clicou em No) Elseif Opcao = vbCancel Then MsgBox(Clicou em Cancelar) End If End Sub Programando em Excel com VBA 21

UERJ / FEN / DEIN

C. Borges; H. Rocha

A sequncia dos parmetros (prompt, buttons, title) pode ser alterada, desde que estes sejam nomeados. No exemplo abaixo, invertemos a ordem dos parmetros title e prompt e omitimos buttons. MsgBox Title:="Caixa de tarefa", Prompt:="Tarefa concluda" Funo InputBox A funo Inputbox exibe uma caixa de dilogo contendo uma mensagem (Prompt), uma caixa de texto e dois botes (OK e Cancelar). Quando escolhemos o boto OK, a funo retorna para a macro o valor inserido na caixa de texto, sob a forma de texto (String). Quando clicamos no boto Cancelar, a funo retorna False. Sintaxe resumida: InputBox(Prompt, Title, Default) Prompt (parmetro necessrio): a mensagem a ser exibida na caixa de dilogo. Pode ser uma seqncia de caracteres, um nmero, uma data ou um valor Booleano. Title (parmetro opcional): o ttulo da caixa de entrada. Se esse argumento for omitido, o ttulo padro ser "Entrada". Default (parmetro opcional): Especifica um valor que aparecer na caixa de texto quando a caixa de dilogo for inicialmente exibida. Se esse argumento for omitido, a caixa de texto ser deixada vazia. O exemplo abaixo pede um nmero ao usurio e atribui varivel myNum o valor digitado por ele. myNum = InputBox("Entre um nmero") Mtodo InputBox O objeto Application apresenta, dentre outros, o mtodo Inputbox. O mtodo InputBox difere da funo InputBox porque permite validao seletiva da entrada do usurio. Por exemplo, podemos necessitar que o usurio informe macro um endereo de uma clula ou conjunto de clulas, ou seja, informe um objeto Range. Esta restrio ao tipo de resposta que o usurio deve fornecer definida pelo argumento Type. Em distino ao mtodo InputBox, a funo InputBox no possui tal argumento. Sintaxe resumida: Application.InputBox(Prompt, Title, Default, Type). Programando em Excel com VBA 22

UERJ / FEN / DEIN

C. Borges; H. Rocha

O parmetro ou argumento Type especifica o tipo de dados retornado. Se este argumento for omitido, a caixa de dilogo retornar texto (string), funcionando de modo idntico funo InputBox. O argumento Type pode ter um dos valores abaixo ou a soma deles. Valor 0 1 2 4 8 16 64 Significado Uma frmula Um nmero Texto (uma seqncia) Um valor lgico (True ou False) Uma referncia a clulas, como um objeto Range Um valor de erro, como #N/D Uma matriz de valores

Voc pode usar a soma dos valores permitidos para Type. Por exemplo, para uma caixa de entrada que possa aceitar tanto texto como nmeros, defina Type como 1 + 2. Comentrios 1. Observe que Application.InputBox chama o mtodo InputBox e InputBox sem qualificador de objeto chama a funo InputBox. 2. Quando Type 8, InputBox retorna um objeto Range. Voc precisa usar a instruo Set para atribuir o resultado a um objeto Range (varivel objeto). O exemplo abaixo pede ao usurio que selecione uma clula em Plan1. Worksheets("Plan1").Activate Set minhacelula = Application.InputBox(prompt:="Selecione uma celula", Type:=8) O valor 8 atribudo ao argumento Type assegura que o dado retornado pelo usurio seja um endereo de clula ou conjunto de clulas (um objeto Range). Tal endereo armazenado na varivel minhacelula.


23

UERJ / FEN / DEIN

C. Borges; H. Rocha

Estruturas de controleUma das vantagens de programarmos as macros a maior habilidade em se automatizar tarefas. Para isto necessrio ser capaz de se controlar o fluxo do programa. As estruturas de controle so bastante comuns s diversas linguagens de programao. O VBA permite que as utilizemos para a construo de macros versteis, que no esto ao alcance do gravador de macros. Estrutura If-Then-Else Esta a estrutura de controle mais utilizada no Excel, tendo o mesmo tipo de funcionamento da funo SE do Excel. Sintaxe da Estrutura If-Then-Else If < condio > Then < declarao > Else < declarao > End If A instruo Else opcional numa estrutura If-Then-Else. No caso de ser omitida, a avaliao negativa da condio implica uma sada automtica da Instruo If. Aplicao Prtica: uma aposta A rotina Aposta recebe uma aposta do usurio e mediante o sorteio a realizar pela respectiva funo verifica se o jogador ganhou ou no a aposta, comunicando-lhe esse fato. Public Sub Aposta() Dim NAposta As Integer Dim NSorteio As Integer NAposta = InputBox("Em que nmero aposta? (entre 1 e 50)") NSorteio = Sorteio() If NSorteio = NAposta Then MsgBox "Parabns! Acertou em cheio! O nmero sorteado foi o " & NSorteio Else MsgBox "Continue a tentar! O nmero sorteado foi o " & NSorteio End If End Sub Programando em Excel com VBA 24

UERJ / FEN / DEIN

C. Borges; H. Rocha

__________________________________ Public Function Sorteio() As Integer Sorteio = Int(Rnd() * (50 - 1) + 1) End Function As instrues If podem ser aninhadas, colocando uma instruo dentro da outra. A forma geral seria: If < condio > Then If Then < declarao > Else < declarao > End If Else < declarao > End If Entretanto, a colocao de mais do que trs instrues If aninhadas torna a macro bastante complicada. Instruo adicional ElseIf Esta instruo prope uma condio alternativa se o teste da condio anterior tiver tido um resultado negativo. Sua forma geral : If < condio > Then < declarao > ElseIf < condio > Then < declarao > ElseIf < condio > Then < declarao > .

Else < declarao > End If


25

UERJ / FEN / DEIN

C. Borges; H. Rocha

Aplicao Prtica Pretende-se criar uma macro que classifique etariamente um indivduo em funo da sua idade. A classificao pretendida a seguinte:

Idade Menos de 3 anos Dos 3 aos 12 Dos 13 aos 17 Dos 18 aos 25 Dos 26 aos 65 Mais de 65

Classe Etria Beb Criana Adolescente Jovem Adulto Idoso

Public Sub Classe_Etaria() Dim Idade As Integer Idade = InputBox("Introduza a idade: ", "Classificao Etria") If Idade < 3 Then MsgBox " um beb.", , "Classificao Etria" ElseIf Idade To < valor final > [ Step < valor a incrementar >] < instrues > Next O comando Step e o valor a incrementar so opcionais. O padro o incremento de uma unidade. Um exemplo prtico: A macro abaixo prepara uma coluna com taxas de juros. Public Sub Taxas() Dim Taxa As Integer, Contador As Integer Taxa = 0 Range("A1").Activate Range("A1").Value = "Taxas (%)" Range("A2").Value = 0 For Contador = 1 To 20 ActiveCell.Offset(Contador, 0).Value = Taxa + Contador Next Contador End Sub Obs.: As estruturas If-Then-Else e Select Case podem ser aninhadas dentro de um loop. Assim, a cada loop podem ser tomadas decises, e se por alguma deciso se pretenda sair do loop deve-se incluir a declarao Exit For. Estrutura While-Wend A estrutura While-Wend tem um funcionamento similar ao For-Next. Ela realiza um loop um determinado nmero de vezes, enquanto uma determinada condio for verdadeira. Sua sintaxe apresentada abaixo. While < condio > < declarao > Wend Estruturas de loop: For-Next While-Wend Do-While Do-Until


28

UERJ / FEN / DEIN

C. Borges; H. Rocha

Aplicao prtica: Na macro abaixo o usurio convidado a apostar num nmero entre 1 e 10. A instruo While-Wend lana nmeros aleatrios entre 1 e 10 e totaliza o nmero de lanamentos necessrios at que o apostador acerte. Public Sub Loteria() Dim Num_Loteria As Integer Dim Aposta As Integer Dim Num_Lanc As Integer Num_Lanc = 0 Num_Loteria = 0 Aposta = InputBox("Introduza a sua aposta! (entre 1 e 10)") While Num_Loteria Aposta Num_Loteria = Int(9 * Rnd() + 1) Num_Lanc = Num_Lanc + 1 Beep Wend MsgBox "A sua aposta foi conseguida depois de " & Num_Lanc & " lanamentos" End Sub Estrutura Do-While Trata-se de outra estrutura de loop, onde um conjunto de instrues executado enquanto uma condio determinada atendida. Sua sintaxe apresentada a seguir. Do While < condio > < declarao > Loop Uma variao desta estrutura apresentada abaixo. Do < declarao > Loop While < condio > Aplicao prtica: utilize essa estrutura para reformatar as macros anteriores, Taxas() e Loteria().


29

UERJ / FEN / DEIN

C. Borges; H. Rocha

Estruturas Do-Until uma estrutura que se contrape Do-While. Ela executa um conjunto de instrues at que uma determinada condio seja atendida. Sua sintaxe apresentada a seguir. Do Until < condio > < declarao > Loop Esta estrutura tambm possui uma variao, apresentada abaixo. Do < declarao > Loop Until < condio > Aplicao prtica: utilize tambm essa estrutura para reformatar as macros Taxas() e Loteria(). Estrutura GoTo e Instruo On Error GoTo GoTo a estrutura de controle mais simples no VBA, permitindo basicamente que voc passe por cima de algumas linhas de cdigo e salte para o ponto desejado. Sua maior utilidade est no tratamento de erros por meio da instruo On Error GoTo. O exemplo a seguir o de um programa que nomeia uma a uma as planilhas da pasta que estiv ativa no Excel, mesmo que desconheamos o nmero de planilhas que existam nesta pasta. Quando no h mais planilhas, ocorre um erro que aciona a instruo GoTo. O programa ento concludo de forma satisfatria. Observe que se trata de um erro que sabemos que ocorrer, mais cedo ou mais tarde. Public Sub Exemplo_GoTo() Dim x As Integer On Error GoTo Acabaram_Planilhas For x = 1 To 1000 Sheets(x).Name = "Planilha" & x Next x Acabaram_Planilhas: MsgBox "Acabaram as planilhas" End Sub


30

UERJ / FEN / DEIN

C. Borges; H. Rocha

Aprimorando objetos, propriedades e mtodosComo fazer referncia a clulas e intervalos Uma tarefa comum ao usar o Visual Basic especificar uma clula ou intervalo de clulas e, em seguida, fazer algo com elas, como inserir uma frmula ou alterar o formato. Geralmente, voc pode fazer isso em uma instruo que identifique o intervalo e tambm altere uma propriedade ou aplique um mtodo. Um objeto Range no Visual Basic pode ser uma nica clula ou um intervalo de clulas. Os tpicos seguintes mostram as maneiras mais comuns de identificar e trabalhar com objetos Range. Referir-se a clulas e intervalos usando a notao A1 Voc pode referir-se a uma clula ou intervalo de clulas no estilo de referncia A1 usando o mtodo Range. O procedimento Sub abaixo altera o formato das clulas A1:D5 para negrito. Sub FormatRange() Workbooks("Pasta1").Sheets("Plan1").Range("A1:D5") _ .Font.Bold = True End Sub A tabela seguinte ilustra algumas referncias em estilo A1 usando o mtodo Range. Referncia Range("A1") Range("A1:B5") Range("C5:D9,G9:H16") Range("A:A") Range("1:1") Range("A:C") Range("1:5") Range("1:1,3:3,8:8") Range("A:A,C:C,F:F") Significado Clula A1 Clulas de A1 at B5 Uma seleo de vrias reas Coluna A Linha 1 Colunas de A at C Linhas 1 at 5 Linhas 1, 3 e 8 Colunas A, C e F


31

UERJ / FEN / DEIN

C. Borges; H. Rocha

Referir-se a clulas usando nmeros de ndice Voc pode usar a propriedade Cells para referir-se a uma nica clula usando nmeros de ndice de linha e coluna. Essa propriedade retorna um objeto Range representando uma nica clula. No exemplo seguinte, Cells(6,1) retorna a clula A6 de Plan1. Em seguida, a propriedade Value definida como 10. Sub EnterValue() Worksheets("Sheet1").Cells(6, 1).Value = 10 End Sub A propriedade Cells funciona bem para loop atravs de um intervalo de clulas porque voc pode substituir os nmeros de ndice por variveis, conforme mostrado no exemplo seguinte. Sub CycleThrough() Dim counter As Integer For counter = 1 To 20 Worksheets("Plan1").Cells(counter, 3).Value = counter Next counter End Sub Observao Se voc desejar alterar as propriedades ou aplicar um mtodo a todo um intervalo de clulas de uma s vez, use a propriedade Range. Referir-se a linhas e colunas Use a propriedade Rows ou a propriedade Columns para trabalhar com linhas ou colunas inteiras. Essas propriedades retornam um objeto Range representando um intervalo de clulas. No exemplo seguinte, Rows(1) retorna a linha um em Plan1. Em seguida, a propriedade Bold do objeto Font do intervalo definida como True. Sub RowBold() Worksheets("Plan1").Rows(1).Font.Bold = True End Sub A tabela seguinte ilustra algumas referncias de linha e coluna usando as propriedades Rows e Columns.


32

UERJ / FEN / DEIN

C. Borges; H. Rocha

Referncia Rows(1) Rows Columns(1) Columns("A") Columns

Significado Linha um Todas as linhas da planilha Coluna um Coluna um Todas as colunas da planilha

Para trabalhar com vrias linhas ou colunas ao mesmo tempo, crie uma varivel de objeto e use o mtodo Union, combinando vrias chamadas propriedade Rows ou Columns. O exemplo seguinte altera para negrito o formato das linhas um, trs e cinco na planilha um da pasta de trabalho ativa. Sub SeveralRows() Worksheets("Plan1").Activate Dim myUnion As Range Set myUnion = Union(Rows(1), Rows(3), Rows(5)) myUnion.Font.Bold = True End Sub

Referir-se a clulas usando notao de atalho Voc pode usar o estilo de referncia A1 ou um intervalo nomeado entre colchetes com um atalho para a propriedade Range. Voc no precisa digitar a palavra "Range" nem usar aspas, conforme mostrado nos exemplos seguintes. Sub ClearRange() Worksheets("Plan1").[A1:B5].ClearContents End Sub __________________________________ Sub SetValue() [MyRange].Value = 30 End Sub


33

UERJ / FEN / DEIN

C. Borges; H. Rocha

Referir-se a intervalos nomeados Os intervalos so mais fceis de identificar por nome do que por notao A1. Para nomear um intervalo selecionado, clique na caixa de nome na extremidade esquerda da barra de frmulas, digite um nome e, em seguida, pressione ENTER. Referir-se a um intervalo nomeado O exemplo seguinte refere-se ao intervalo chamado "MyRange" na pasta de trabalho chamada "MyBook.xls". Sub FormatRange() Range("MyBook.xls!MyRange").Font.Italic = True End Sub Para selecionar um intervalo nomeado, use o mtodo GoTo, o qual ativa a pasta de trabalho e a planilha e, em seguida, seleciona o intervalo. Sub ClearRange() Application.Goto Reference:="MyBook.xls!MyRange" Selection.ClearContents End Sub O exemplo seguinte mostra como o mesmo procedimento seria escrito para a pasta de trabalho ativa. Sub ClearRange() Application.Goto Reference:="MyRange" Selection.ClearContents End Sub Referir-se a clulas relativas a outras clulas Uma maneira comum de trabalhar com uma clula em relao a uma outra clula usar a propriedade Offset. No exemplo seguinte, o contedo da clula que se encontra uma linha abaixo e a trs colunas da clula ativa na planilha ativa formatado com sublinhado duplo. Sub Underline() ActiveCell.Offset(1, 3).Font.Underline = xlDouble End Sub


34

UERJ / FEN / DEIN

C. Borges; H. Rocha

Observao. Voc pode gravar macros que usem a propriedade Offset em vez de referncias absolutas. No menu Ferramentas, aponte para Gravar macro e, em seguida, clique em Usar referncias relativas. Referir-se a clulas usando um objeto Range Quando voc define uma varivel de objeto para um objeto Range, voc pode facilmente manipular o intervalo usando o nome da varivel. O procedimento seguinte cria a varivel de objeto myRange e, em seguida, atribui a varivel ao intervalo A1:D5 de Plan1 na pasta de trabalho ativa. Instrues subseqentes modificam propriedades do intervalo substituindo o objeto de intervalo pelo nome da varivel. Sub Random() Dim myRange As Range Set myRange = Worksheets("Plan1").Range("A1:D5") myRange.Formula = "=RAND()" myRange.Font.Bold = True End Sub Referir-se a todas as clulas da planilha Quando voc aplica a propriedade Cells a uma planilha sem especificar um nmero de ndice, o mtodo retorna um objeto Range representando todas as clulas da planilha. O procedimento Sub seguinte limpa o contedo de todas as clulas de Plan1 na planilha ativa. Sub ClearSheet() Worksheets("Plan1").Cells.ClearContents End Sub Referir-se a vrios intervalos Usando o mtodo apropriado, voc pode facilmente referir-se a vrios intervalos. Use os mtodos Range e Union para referir-se a qualquer grupo de intervalos; use a propriedade Areas para referir-se ao grupo de intervalos selecionado em uma planilha. Usar a propriedade Range Voc pode referir-se a vrios intervalos com a propriedade Range colocando vrgulas entre duas ou mais referncias. O exemplo seguinte limpa o contedo dos trs intervalos de Sheet1. Sub ClearRanges() Programando em Excel com VBA 35

UERJ / FEN / DEIN

C. Borges; H. Rocha

Worksheets("Sheet1").Range("C5:D9,G9:H16,B14:D18").ClearContents End Sub Os intervalos nomeados tornam o uso da propriedade Range mais fcil ao trabalhar com vrios intervalos. O exemplo seguinte funciona quando todos os trs intervalos nomeados se encontram na mesma planilha. Sub ClearNamed() Range("MyRange, YourRange, HisRange").ClearContents End Sub Usar o mtodo Union Voc pode combinar vrios intervalos em um objeto Range usando o mtodo Union. O exemplo seguinte cria um objeto Range chamado myMultipleRange, define-o com os intervalos A1:B2 e C3:D4 e, em seguida, formata com negrito os intervalos combinados. Sub MultipleRange() Dim r1, r2, myMultipleRange As Range Set r1 = Sheets("Sheet1").Range("A1:B2") Set r2 = Sheets("Sheet1").Range("C3:D4") Set myMultipleRange = Union(r1, r2) myMultipleRange.Font.Bold = True End Sub Loop em um intervalo de clulas Ao usar o Visual Basic, voc freqentemente precisa executar o mesmo bloco de instrues em cada clula de um intervalo de clulas. Para fazer isso, voc combina uma instruo de loop com um ou mais mtodos para identificar cada clula, uma de cada vez, e executa a operao.

Estrutura For Each-NextA estrutura For Each-Next permite executar uma determinada instruo em todos os elementos de uma coleo de objetos, como por exemplo o conjunto de planilhas de um arquivo, o conjunto de clulas de uma planilha ou um intervalo de clulas de uma Range. Sua sintaxe apresentada abaixo. For Each < elemento > In < grupo > Programando em Excel com VBA 36

UERJ / FEN / DEIN

C. Borges; H. Rocha

< Instrues > Next Por exemplo, o procedimento a seguir formata o tamanho da fonte de todas as clulas de uma Range nomeada Faixa. Public Sub closefiles() For Each celula In Range("Faixa").Cells celula.Font.Size = 14 Next End Sub Se voc no souber os limites do intervalo pelo qual deseja fazer o loop, voc pode usar a propriedade CurrentRegion para retornar o intervalo que envolve a clula ativa. Por exemplo, o procedimento seguinte, quando executado de uma planilha, faz um loop atravs do intervalo que envolve a clula ativa, definindo como 0 (zero) qualquer nmero cujo valor absoluto seja menor que 0,01. Sub RoundToZero3() For Each c In ActiveCell.CurrentRegion.Cells If Abs(c.Value) < 0.01 Then c.Value = 0 Next End Sub Saindo de um loop For Each-Next antes de seu trmino Voc pode sair de um loop For Each-Next utilizando a instruo Exit For. Por exemplo, quando ocorrer um erro, utilize a instruo Exit For no bloco de instrues True de uma instruo If...Then...Else ou uma instruo Select Case que verifica especificamente o erro. Caso no ocorra o erro, a instruo IfThenElse ser False e a execuo do loop continuar conforme esperado. O exemplo a seguir testa a primeira clula preenchida do intervalo A1:B5 que no contenha um nmero. Se for encontrada uma clula desse tipo, ser exibida uma mensagem e Exit For sair do loop. Sub TestForNumbers() For Each celul In Range("A1:B5") If IsNumeric(celul.Value) = False Then MsgBox "Objeto contm um valor no-numrico." Exit For Programando em Excel com VBA 37

UERJ / FEN / DEIN

C. Borges; H. Rocha

End If Next celul End Sub Uma outra maneira de fazer loop atravs de um intervalo usar o loop For...Next com a propriedade Cells. Usando a propriedade Cells, voc pode substituir o contador do loop (ou outras variveis ou expresses) pelos nmeros de ndice das clulas. No exemplo seguinte, a varivel counter substituda pelo ndice de linha. O procedimento faz um loop atravs de um intervalo C1:C20, definindo como 0 (zero) qualquer nmero cujo valor absoluto seja menor que 0,01. Sub RoundToZero1() For counter = 1 To 20 Set curCell = Worksheets("Sheet1").Cells(counter, 3) If Abs(curCell.Value) < 0.01 Then curCell.Value = 0 Next counter End Sub Instruo With A instruo With executa uma srie de instrues em um objeto. Sua sintaxe : With objeto < instrues > End With As instrues With fazem os seus procedimentos serem executados com mais rapidez e ajudam a evitar digitao repetitiva. O exemplo a seguir preenche um intervalo de clulas com o nmero 30, aplica a formatao negrito e define como amarelo a cor interna das clulas. Sub FormatRange() With Worksheets("Planilha1").Range("A1:C10") .Value = 30 .Font.Bold = True .Interior.Color = RGB(255, 255, 0) End With End Sub


38

UERJ / FEN / DEIN

C. Borges; H. Rocha

Para melhor eficincia, voc pode aninhar as instrues With. O exemplo a seguir insere uma frmula na clula A1 e, em seguida, formata a fonte. Sub MyInput() With Workbooks("Livro1").Worksheets("Planilha1").Cells(1, 1) .Formula = "=SQRT(50)" With .Font .Name = "Arial" .Bold = True .Size = 8 End With End With End Sub Propriedade Formula e Propriedade FormulaR1C1 Tratam-se de propriedades que permitem a insero de frmulas em clulas ou conjuntos de clulas. A primeira define a frmula em notao de estilo A1. A segunda define a frmula usando notao em estilo L1C1 (R1C1), utilizada pelo gravador de macro. Exemplos: Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10" equivale a Worksheets("Sheet1").Range("A1").FormulaR1C1 = "=R4C1+R10C1" Worksheets("Sheet1").Range("B1").FormulaR1C1 = "=SQRT(R1C1)" equivale a Worksheets("Sheet1").Range("B1").Formula = "=SQRT(A1)" As propriedades Formula e FormulaR1C1 so intercambiveis quando se trata da insero de constantes ou textos em clulas. Mtodo Find Localiza informaes especficas em um intervalo, e retorna um objeto Range representando a primeira clula onde essas informaes se encontram. Retorna Nothing se nenhuma coincidncia for encontrada. No afeta a seleo da clula ativa. Sintaxe resumida: expresso.Find(What) Programando em Excel com VBA 39

UERJ / FEN / DEIN

C. Borges; H. Rocha

expresso Obrigatria. Uma expresso que retorne um objeto Range. What Variant obrigatria. Os dados pelos quais procurar. Pode ser uma cadeia de caracteres ou qualquer tipo de dados do Microsoft Excel. Mtodo FindNext Continua uma pesquisa que tenha comeado com o mtodo Find. Sintaxe expresso.FindNext(After) expresso Obrigatria. Uma expresso que retorne um objeto Range. After Variant opcional. A clula depois da qual voc deseja procurar. Corresponde posio da clula ativa quando uma pesquisa feita a partir da interface do usurio. Observe que After precisa ser uma nica clula no intervalo. Lembre-se de que a pesquisa comea depois desta clula; a clula especificada no pesquisada at que o mtodo d a volta e chegue ela. Se esse argumento no for especificado, a pesquisa comear aps a clula do canto superior esquerdo do intervalo. O exemplo abaixo localiza todas as clulas do intervalo A1:A500 que contm o valor 2 e torna essas clulas cinzas. With Worksheets(1).Range("a1:a500") Set c = .Find(2, lookin:=xlValues) If Not c Is Nothing Then firstAddress = c.Address Do c.Interior.Pattern = xlPatternGray50 Set c = .FindNext(c) Loop While Not c Is Nothing And c.Address firstAddress End If End With Observao: Para encontrar clulas coincidentes com padres mais complicados, use uma instruo For Each...Next com o operador Like. Por exemplo, o cdigo seguinte pesquisa todas as clulas do intervalo A1:C5 que usam uma fonte cujo nome comea com as letras "Cour". Quando o Microsoft Excel encontra uma coincidncia, ele altera a fonte para Times New Roman. For Each c In [A1:C5] Programando em Excel com VBA 40

UERJ / FEN / DEIN

C. Borges; H. Rocha

If c.Font.Name Like "Cour*" Then c.Font.Name = "Times New Roman" End If Next Mtodo Replace Encontra e substitui caracteres em clulas dentro do intervalo especificado. O uso desse mtodo no altera a seleo nem a clula ativa. Sintaxe resumida: expresso.Replace(What, Replacement) expresso Obrigatria. Uma expresso que retorne um objeto Range. What String obrigatria. A cadeia de caracteres que voc quer que o Microsoft Excel procure. Replacement String obrigatria. A cadeia de caracteres de substituio. O exemplo abaixo substitui todas as ocorrncias da funo SIN trigonomtrico pela funo COS. O intervalo de substituio a coluna A de Plan1. Worksheets("Plan1").Columns("A").Replace _ What:="SIN", Replacement:="COS", _ SearchOrder:=xlByColumns, MatchCase:=True


41

UERJ / FEN / DEIN

C. Borges; H. Rocha

Escopo ou domnio de variveis, constantes e procedimentosO escopo ou domnio diz respeito disponibilidade de uma varivel, constante ou procedimento para ser utilizado por outro procedimento. H trs nveis de escopo: nvel de procedimento, nvel de mdulo privado e nvel de mdulo pblico. Escopo das variveis Definindo o escopo em nvel de procedimento Uma varivel ou constante definida dentro de um procedimento no visvel fora desse procedimento. Somente o procedimento que contm a declarao da varivel pode utiliz-la. No exemplo a seguir, o primeiro procedimento exibe uma caixa de mensagem que contm uma seqncia de caracteres. O segundo procedimento exibe uma caixa de mensagem em branco, pois, para o primeiro procedimento, a varivel strMsg local. Sub LocalVariable() Dim strMsg As String strMsg = "Esta varivel no pode ser utilizada fora deste procedimento." MsgBox strMsg End Sub _______________________________________________ Sub OutsideScope() MsgBox strMsg End Sub Definindo o escopo em nvel de mdulo privado Voc pode definir variveis (e constantes) em nvel de mdulo na seo Declaraes de um mdulo. Em nvel de mdulo, elas podem ser pblicas ou privadas. Quando declaradas pblicas, esto disponveis para todos os procedimentos em todos os mdulos de um projeto; quando privadas, esto disponveis somente para os procedimentos naquele mdulo. Como padro, o escopo das variveis declaradas com a instruo Dim ( e o das constantes declaradas por Const) na seo Declaraes privado. No entanto, quando se precede a varivel com a palavra-chave Private, ou a constante com a expresso PrivateConst, o escopo no seu cdigo se torna bvio. Programando em Excel com VBA 42

UERJ / FEN / DEIN

C. Borges; H. Rocha

No exemplo a seguir, a varivel de seqncia de caracteres strMsg est disponvel para os procedimentos definidos no mdulo. Quando o segundo procedimento for chamado, ele exibir o contedo da varivel de seqncia de caracteres strMsg em uma caixa de dilogo. ' Adiciona a seguinte seo Declaraes do mdulo. Private strMsg As String _______________________________________ Sub InitializePrivateVariable() strMsg = "Esta varivel no pode ser utilizada fora deste mdulo." End Sub ________________________________________ Sub InitializePrivateVariable() MsgBox strMsg End Sub Definindo o escopo em nvel de mdulo pblico Caso voc declare uma varivel (ou uma constante) em nvel de mdulo como pblica, ela estar disponvel para todos os procedimentos no projeto. No exemplo a seguir, a varivel de seqncia de caracteres strMsg pode ser utilizada por qualquer procedimento em qualquer mdulo do projeto. ' Insere a seo Declaraes do mdulo. Public strMsg As String O exemplo a seguir declara a constante Public conIdade como um Integer e atribui a ela o valor 34. Public Const conIdade As Integer = 34 Escopo ou domnio dos procedimentos (Sub-rotinas, Funes e Eventos) Sub-rotinas e Funes s tm dois nveis de escopo: o nvel do projeto e o nvel do mdulo. Por padro as rotinas so de mbito do projeto. Contudo este comportamento fica mais explcito pela introduo da palavra-chave Public, opcional, que em termos operacionais no adicionar qualquer funcionalidade, mas em termos de leitura dar uma maior percepo. A palavra-chave Private, opcional, indica que a rotina acessvel somente a outros procedimentos no mdulo em que declarado. Assim, para que uma rotina tenha o mbito do mdulo onde est definida, dever ser antecedida pela palavra-chave Private. Programando em Excel com VBA 43

UERJ / FEN / DEIN

C. Borges; H. Rocha

Enquanto os procedimentos Funo e Sub-rotina so pblicos por padro, os procedimentos de Evento so de domnio privado. Quando o Visual Basic cria um procedimento de evento, a palavra-chave Private automaticamente inserida antes da declarao do procedimento.


44

UERJ / FEN / DEIN

C. Borges; H. Rocha

ExercciosLista 1 de ExercciosUsando o gravador de macros e editando macros 1. Gravando a primeira macro, com referncias absolutas (pg. 3) 2. Gravando com referncias relativas (pg. 3) 3. Mudar o atalho da macro, seguindo os passos Ferramentas ; Macro; Macros; Opcoes. 4. Editando a macro de referncias absolutas: no Editor do VBA, alterar os endereos de clulas que aparecem na primeira macro gravada. Por exemplo, substituir Range(A2) por Range(D2) e executar a macro. Substituir a propriedade FormulaR1C1 por Formula. Executar a macro. Substituir: ActiveCell.FormulaR1C1 = UERJ por: ActiveCell.Value = UERJ 5. Editando a macro de referncias relativas: eliminar Range(A1) das linhas de comando. Executar. Simplificar as linhas de cdigo abaixo: ActiveCell.Offset(1,0).Select ActiveCell.FormulaR1C1 = UERJ substituindo por uma nica linha: ActiveCell.Offset(1,0).FormulaR1C1 = UERJ 6. Criar botes de macro (pg. 5) e nome-los. 7. Executar as macros de diferentes modos: Pela planilha Pelo Editor VBA 8. Executar as macros no Editor VB, passo a passo. Colocar o cursor em qualquer linha da macro desejada. Teclar F8 passo a passo e verificar o efeito na planilha. Programando com eventos 1. Eventos. Evento ao abrir pasta. Direcionando usurio para uma planilha. Abra uma pasta vazia. No Editor VBA, selecione no Project Explorer EstaPasta_de_trabalho. Na janela de cdigo, selecione Workbook no menu suspenso esquerda. No menu direita selecione Open. Digite as seguintes linhas de cdigo: MsgBox "Os dados so lanados na Plan3" Worksheets("Plan3").Activate Salve a pasta, feche-a e a reabra. Verifique o aparecimento da caixa de mensagem e a ativao da Planilha 3. 2. Eventos. Auto-ajuste das colunas. Criar uma macro que auto-ajuste todas as colunas (Columns) sempre que a Planilha1 for alterada (evento Change). Para tal, selecione Plan1 no Project Explorer. Nos menus suspensos selecione Worksheet ( esquerda) e Change ( direita). Digite a linha de cdigo: Columns.AutoFit V Planilha 1 e digite uma letra qualquer numa das clulas. Verifique. Programando em Excel com VBA 45

UERJ / FEN / DEIN

C. Borges; H. Rocha

Lista 2 de ExercciosTrabalhando com variveis e inserindo formulas 1. Funo de planilha SUM. Abra uma pasta vazia. Registre valores numricos quaisquer nas clulas de A2 at A5. Crie uma macro que calcule a soma destes valores (utilize a funo SUM) e registre o resultado na clula A1 (utilize a propriedade Formula). Verifique como a funo aparece na barra de frmulas na planilha. 2. Propriedade End(xlDown). A coluna A da planilha Plan1 possui valores de vendas cujos registros iniciam na linha 2 e cuja linha final indeterminada. Grave uma macro (de nome Vendas) que desa pela coluna at o ltimo valor de vendas. Utilize as teclas End + seta para baixo. Verifique a macro gravada no Editor VB. Observao: End(xlDown) falha quando s h uma linha preenchida na coluna (verifique!). O comando End(xlUp) mais confivel. 3. Propriedade Row. Na macro gravada na etapa anterior, substitua a propriedade Select por Row. Substitua o objeto Selection por Range(A1). Armazene o resultado em uma varivel (UltimaLinha). Execute a macro passo a passo teclando F8. Passe o cursor sobre o nome da varivel e observe o registro do nmero da ltima linha. 4. Na macro anterior, declare a varivel (comando Dim) como Integer. Acima da macro, escreva o comando Option Explicit. 5. Na mesma macro, registre na clula B1 a soma dos valores de vendas da coluna A, inserindo a varivel UltimaLinha na funo SUM. Observe a planilha e a barra de frmulas. Observar a sintaxe abaixo e a utilizao do operador &. Range("B1").Formula = "=sum(A1:A" & UltimaLinha & ")" 6. Inclua novos valores de vendas na coluna A e observe que a funo Soma no atualizada. 7. Utilizando macro de evento. Crie uma macro de evento (Worksheet_SelectionChange) que execute a macro Vendas sempre que a planilha Plan1 for modificada. Inclua novos valores de vendas na coluna A e observe novamente. 8. Alternativamente, registre a soma na coluna B, porm na ltima linha preenchida. Neste caso, a sintaxe ser: Columns(2).ClearContents (limpeza prvia da coluna B recomendvel) Range("B" & UltimaLinha).Formula = "=sum(A1:A" & UltimaLinha & ")"

Lista 3 de Exerccios1. Importando dados. Ler o exerccio Importando dados e limpando o cdigo gravado da apostila e executar a etapa Preparando para gravar a macro. Utilizar o arquivo Fatura.txt. 2. Corrija os possveis erros presentes no cdigo. Simplifique-o, eliminando o que for desnecessrio. 3. Modifique a macro, de modo que a tabela importada via Fatura.txt seja recortada e colada na Plan2 da prpria pasta que contm a macro. A pasta Fatura.txt criada pela importao pode ser fechada.


46

UERJ / FEN / DEIN

C. Borges; H. Rocha

Lista 4 de ExercciosTransferindo dados entre pastas e planilhas 1. Criar 3 novos arquivos (Arq1, Arq2 e Arq3) no diretrio C. Fechar Arq1 e Arq2. Gravar uma macro em Arq3 que: abra Arq1 e Arq2; copie a clula A2 de Plan2 de Arq1 para a clula A2 de Plan2 de Arq2; salve e feche Arq1; idem, para Arq2. 2. Edite a macro gravada, de modo a copiar e colar em uma nica linha de comando, usando o mtodo Copy e o argumento Destination. 3. Repita o exerccio anterior, usando o mtodo Cut Trabalhando com Localizar e Substituir no ambiente VBA Localizar: 1. Abrir uma pasta vazia. Digitar a palavra inicial em algumas clulas aleatrias da planilha. 2. Gravar uma macro que localize, uma a uma na planilha, cada clula que contiver a palavra inicial. Use a sequncia Editar / Localizar. 3. Aps a gravao, observar os comandos gerados pela macro gravada ( ir ao ambiente VBA). Observar o comando Cells. 4. Editar a macro gerada pelo gravador, eliminado todos os argumentos do mtodo Find e do mtodo FindNext, exceto os argumentos What e After. 5. Executar a macro editada e observar seu funcionamento. Substituir: 1. Gravar uma macro que substitua a palavra inicial por inicio. Use a sequncia Editar / Substituir. 2. Aps a gravao, observar os comandos gerados pela macro gravada ( ir ao ambiente VBA) 3. Editar a macro gerada pelo gravador, eliminado todos os argumentos do mtodo Replace, exceto os argumentos What e Replacement. 4. Executar a macro editada e observar seu funcionamento. Pesquisando o uso do comando Cells Aproveite as macros geradas no exerccio com Localizar e Substituir. Coloque o cursor na palavra Cells e tecle F1. Observe o arquivo Ajuda que se abre e clique no link Exemplos. Avalie os exemplos apresentados para o comando Cells. Aplicando o comando Cells 1. Abrir uma pasta vazia. Digitar em uma clula a palavra inicial. 2. Escrever uma macro que localize essa palavra e registre sua localizao. Para tal, crie uma varivel para registrar o nmero da linha, e outra para o nmero da coluna. 3. Opcionalmente, use a MsgBox para informar ao usurio os nmeros de linha e coluna.


47

UERJ / FEN / DEIN

C. Borges; H. Rocha

Usando o mtodo Find Uma coluna de dados, de tamanho indefinido, tem em alguma clula da coluna imediatamente esquerda a palavra inicial. Um pouco abaixo, tem-se a palavra final. Vide abaixo. R$ 15.900,00 R$ 14.700,00 inicial R$ 15.900,00 R$ 14.700,00 R$ 22.950,00 R$ 22.950,00 R$ 13.500,00 final R$ 13.500,00 Mdia: R$ 13.500,00 R$ 14.100,00 R$ 14.100,00

?

Pede-se elaborar uma macro que localize a Range delimitada por estas palavras e registre esta Range em uma varivel-objeto. A macro deve tambm destacar a Range, alterando sua cor de fundo, e deve calcular a sua mdia, registrando o resultado conforme figura.

Localiza tabela de dados. Uso de For each...Next. Uso de CurrentRegion. Uma planilha contm uma nica tabela de dados, em uma localizao indefinida. Crie uma macro que localize qualquer clula da tabela e que armazene a tabela em uma varivel-objeto de mesmo nome. Esta tabela pode, por exemplo, ser copiada e colada em outra planilha, ou pode simplesmente ter sua cor de fundo modificada para maior destaque. Obs.: 1. Para varrer as clulas da planilha, utilize a estrutura For Each...Next, conforme abaixo. For Each celula In Cells ...... ...... Next celula 2. Para testar se uma clula est preenchida, utilize a estrutura If...End If, conforme abaixo. If celula.Value "" Then Set Tabela = celula.CurrentRegion Exit For End If 3. Quando for encontrada uma clula vazia, deve-se sair da estrutura For Each...Next. Para tal, utilizamos Exit For.


48

UERJ / FEN / DEIN

C. Borges; H. Rocha

Lista 5 de ExercciosCriando uma funcao Funo rateio. Uma tabela registra em uma coluna os centros de custo de uma pequena empresa e na coluna vizinha o rateio das despesas indiretas da empresa pelos centros de custo. Vide abaixo. Centro de Custos Despesas Indiretas (R$) Almoxarifado R$ 10.500 Laboratrio R$ 12.450 Montagem R$ 14.600 Manuteno R$ 21.000 Expedio R$ 5.500 % das Desp. Ind. ? (%)

Crie uma funo rateio que calcule o percentual de responsabilidade de um dado centro de custo. A funo dever ter dois argumentos a serem definidos pelo usurio: a Range contendo a lista de despesas indiretas e a Range contendo a despesa indireta do centro de custo. Funo sumbycolor. Uma tabela possui um conjunto de dados em clulas com diferentes cores de fundo, vide abaixo. Crie uma funo que totalize os valores associados a uma dada cor, definida pelo usurio. A funo ter dois argumentos: a Range com os dados e a Range de uma clula qualquer, contendo a cor definida pelo usurio. 4,3 5,0 7,0 9,0 1,0 3,0 4,0 6,0 5,0 2,0 regio amarela ? regio regio branca vermelha ? ?

Obs.: antes de criar a funo, utilize o gravador de macro para absorver os comandos associados definio de cor de fundo. Por exemplo, grave uma macro enquanto modifica a cor de fundo de uma clula qualquer. Depois, v ao VBE e estude as linhas de cdigo. Funo mediaponderada. Uma tabela registra os dados de vendas de uma empresa, com os respectivos prazos de recebimento, vide abaixo. Data Venda (R$) Cliente Prazo (dias) 1-fev 10.000 Yucatan 25 1-fev 8.000 Palermo 40 2-fev 9.500 Kashmir 40 2-fev 12.000 Rover 35 2-fev 5.000 Raimundos 35 3-fev 7.000 Annie Frank 15 50 4-fev 16.000 Hammerklavier Programando em Excel com VBA

Prazo mdio: ? dias

49

UERJ / FEN / DEIN

C. Borges; H. Rocha

Crie uma funo que calcule o prazo mdio de recebimento das vendas. A funo s precisar que o usurio selecione duas Ranges: a de Valores (ou Vendas) e a de Pesos (ou Prazos). Observaes: 1. O total de vendas (podemos chamar de Soma_dos_Valores) ser simplesmente a aplicao da funo de planilha SUM sobre a Range Valores. Assim: soma_valores = Application.WorksheetFunction.Sum(valores). 2. Para o produto de cada Valor por seu Peso precisamos fazer com que a linha 1 da Range Valores seja multiplicada pela linha 1 da Range Pesos, e assim por diante. E os produtos devem ser totalizados em uma varivel, que podemos nomear, p. ex., por "valor_vezes_peso". 3. As linhas das duas Ranges podem crescer desde o valor 1 at o total de linhas, de qualquer uma das Ranges. Podemos usar a estrutura ForNext para controlar este salto de linha para linha. Assim: For linha = 1 To valores.Rows.Count (Aqui temos que totalizar os produtos) Next linha Funo Payback. Usando a estrutura For each...Next. Uma tabela contm as projees de fluxo de caixa de um investimento, em bases anuais, mensais, ...etc. O primeiro fluxo de caixa tem sinal negativo, por ser o de investimento, e os demais so positivos, vide abaixo. Fluxos de caixa: (1.000) 240 240 240 240 240 240

Crie uma funo que calcule o Payback do investimento. Obs.: Payback o prazo para que o investimento seja recuperado. o instante em que o somatrio dos fluxos de caixa deixa de ser negativo.

Lista 6 de ExercciosUsando Caixa de Mensagem e Caixa de Entrada 1. Criar uma macro que por meio de InputBox pergunte ao usurio sua idade (no necessariamente sob a forma de numero) e que armazene a resposta numa varivel chamada idade. Esta varivel deve ter dimenso string. 2. Na macro anterior, se o usurio retornar False (boto Cancelar), uma MsgBox devera perguntar se ele quer omitir sua idade. Esta MsgBox devera ter os botes Sim e No. Se ele clicar Sim, a macro e concluda. Se ele clicar No, a macro retorna ao InputBox.


50

UERJ / FEN / DEIN

C. Borges; H. Rocha

Importando dados e limpando o cdigo gravadoImportando Dados de Fatura - Exerccio Fonte: Macros eVBA para Microsoft Excel (Mr. Excel& Syrstad), Ed. Campus Vamos supor que voc esteja trabalhando em um departamento de contabilidade. Todos os dias, voc recebe um arquivo texto do sistema da empresa mostrando todas as faturas produzidas no dia anterior. Esse arquivo texto tem seus campos separados por vrgulas. Ao chegar ao trabalho pela manh, voc importa manualmente este arquivo para o Excel. Adiciona uma linha de totais aos dados, coloca seus ttulos em negrito e imprime o relatrio para ser distribudo para alguns gerentes. Os dados de 5 de junho tem 12 faturas, conforme abaixo. A primeira linha apresenta os ttulos das colunas.

DataFatura,NumFatura,NumRepVendas,NumCliente,ReceitaProduto,ReceitaServio,CustoPr oduto 5/6/2004,123801,s82,c8754,639600,12000,325438 5/6/2004,123802,s93,c7874,964600,0,435587 5/6/2004,123803,s43,c4844,988900,0,587630 5/6/2004,123804,s54,c4940,673800,15000,346164 5/6/2004,123805,s43,c7969,513500,0,233842 5/6/2004,123806,s93,c8468,760600,0,355305 5/6/2004,123807,s82,c1620,894100,0,457577 5/6/2004,123808,s17,c3238,316200,45000,161877 5/6/2004,123809,s32,c5214,111500,0,62956 5/6/2004,123810,s45,c3717,747600,0,444162 5/6/2004,123811,s87,c7492,857400,0,410493 5/6/2004,123812,s43,c7780,200700,0,97937

Copie estes dados para um arquivo do programa Bloco de notas e salve com o nome Fatura.txt.


51

UERJ / FEN / DEIN

C. Borges; H. Rocha

Preparando para gravar a macro: A importao do arquivo e a formatao da tabela so tarefas perfeitas para uma macro. Abra uma pasta de trabalho em branco e salve-a com o nome MacroParaImportarFaturas.xls. Clique no boto Gravar macro ou selecione Ferramentas, Macro, Gravar nova macro. Na caixa de dilogo Gravar macro, modifique o nome da macro para ImportarFaturas. Verifique se as macros sero armazenadas em Esta Pasta de Trabalho. Digite a letra i no campo Tecla de atalho. Clique em OK e siga as etapas abaixo. 1. No menu, selecionar Arquivo, Abrir 2. Navegar at a pasta apropriada 3. Escolher Todos os arquivos (*.*) na lista suspensa Arquivos do tipo: 4. Selecionar Fatura.txt 5. Clicar em Abrir 6. No Assistente de importao de texto Etapa 1 de 3, selecionar Delimitado na seo Tipo de dados originais 7. Clicar em Avanar 8. No Assistente de importao de texto Etapa 2 de 3, desmarcar a tecla Tab e marcar Vrgula na seo Delimitadores 9. Clicar em Avanar 10. No Assistente de importao de texto Etapa 3 de 3, selecionar Geral na seo Formato dos dados da coluna e alter-lo para Data:DMA 11. Clicar em Concluir para importar o arquivo 12. Pressionar a tecla End seguida da seta para baixo (End+Down) para mover o cursor para a ltima linha de dados 13. Pressionar a seta para baixo mais uma vez para mover para a linha de totais 14. Digitar a palavra Totais 15. Pressionar a tecla seta para direita quatro vezes para mover para a coluna E da linha de totais 16. Clicar no boto AutoSoma e pressionar Ctrl+Enter para adicionar um total na coluna ReceitaProduto enquanto permanece nessa clula 17. Clicar na ala de autopreenchimento e arrastar da coluna E at a coluna G para copiar a frmula de totais para as colunas F e G 18. Realar a linha Total (ou usar Shift + Barra de espao) e clicar no cone Negrito para definir os totais em negrito 19. Realar a linha 1 e clicar no cone Negrito para definir os ttulos em negrito 20. Pressionar Ctrl+T para selecionar todas as clulas Programando em Excel com VBA 52

UERJ / FEN / DEIN

C. Borges; H. Rocha

21. No menu, selecionar Formatar, Coluna, AutoAjuste da seleo 22. Clicar no boto Parar gravao ou selecionar Ferramentas, Macro, Parar gravao. Execute a macro, clicando Ctrl+i ou selecionando Ferramentas, Macro, Macros e clicando Executar, tendo selecionada a macro ImportarFaturas. Depois, observe as linhas de cdigo da macro no Editor do Visual Basic. Executar a macro em outro dia produz resultados indesejados: No dia seguinte, o arquivo Fatura.txt tem novas linhas adicionais, apresentadas abaixo. 6/6/2004,123813,s07,c4913,750000,0,340000 6/6/2004,123814,s55,c7181,37900,0,19811 6/6/2004,123815,s43,c7570,582700,0,292000 Execute a macro ImportarFaturas novamente e avalie os resultados. Corrigindo e limpando o cdigo: Corrija os possveis erros presentes no cdigo. Simplifique-o, eliminando o que for desnecessrio. Siga os passos logo abaixo recomendados pelo Mr. Excel.

Cinco dicas para limpar o cdigo gravado (Mr. Excel)1. No selecione nada O cdigo do gravador de macro antes de ser aperfeioado: Rows(1:1).Select Selection.Font.Bold = True Aps a melhoria: Rows(1:1).Font.Bold = True 2. Percorra o intervalo de baixo para cima para encontrar a ltima linha No podemos garantir que todas clulas da planilha estaro preenchidas. Isso um problema quando usamos End + Down. Essa combinao de teclas o leva ltima linha com dados no intervalo atual, no necessariamente ltima preenchida. A melhor soluo comear na parte inferior da planilha do Excel e pressionar End +Up. Como o Excel tem 65.536 linhas, habitue-se a usar este cdigo para encontrar pelo VBA do Excel a ltima linha: Range(A65536).End(xlUp).Select Cells(Cells.Rows.Count, 1).End(xlUp).Select Programando em Excel com VBA 53 .... ou .....

UERJ / FEN / DEIN

C. Borges; H. Rocha

3. Use variveis O gravador de macros nunca grava uma varivel. Elas so muito fceis de usar. Por exemplo, defina a varivel FinalRow como a ltima linha de dados: FinalRow = Range (A65536).End(xlUp).Row . Ou . FinalRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row Assim, colocar a palavra Total na prxima linha fica... Range(A & FinalRow + 1).Value = Total 4. Aprenda a copiar e colar em uma nica instruo O cdigo gravado: Range(E14).Select Selection.Copy Range(F14).Select ActiveSheet.Paste

Cdigo melhorado: Range(E14).Copy Destination:=Range(F14)

5. Use With... End with se estiver executando mltiplas aes para a mesma clula ou intervalo de clulas O cdigo gravado: Range(A14:F14).Select Selection.Font.Bold = True Selection.Font.Size = 12 Selection.Font.ColorIndex = 5 Cdigo melhorado: With Range(A14:F14).Font .Bold = True .Size = 12 .ColorIndex = 5 End With


54

UERJ / FEN / DEIN

C. Borges; H. Rocha

Exerccio final: Fluxo de caixa em macroVoc trabalha no Departamento Financeiro de uma pequena empresa. A partir do prximo ano, as projees de caixa da empresa sero transferidas para uma pasta denominada Fluxo de Caixa.xls. Este arquivo possui 12 planilhas nomeadas segundo as siglas dos 12 meses do ano. Periodicamente voc receber um arquivo Fluxo.txt que conter as projees do ms dos fluxos de caixa dirios. Voc tem que criar uma rotina para importar o arquivo Fluxo.txt e destin-lo respectiva planilha. O arquivo Fluxo.txt tem na primeira linha a sigla que caracteriza o ms de projeo. Alm disso, tem uma linha de rtulos de colunas. Logo abaixo, vm as linhas com os seguintes dados tabulados: Data, Receita (Rec), Fornecedores (Fornec), Pessoal, Impostos (Imp), Outras Despesas (Outras), Despesas Bancrias (Banc), Emprstimos (Empr), Amortizao (Amort), Juros. Esses dados devem ser reorganizados na planilha com as seguintes colunas ordenadas: 1. Dia 4. Fornecedores 7. Outras despesas 10. Amortizao 13. Saldo final * * Colunas no trazidas do arquivo Fluxo.txt As clulas contendo fluxos de caixa devem ser formatadas em R$ com ponto separador em 1.000 e com duas casas decimais. A fonte Arial 10. A linha de cabealho deve ser formatada em negrito, com fonte tamanho 11. A pgina deve ter orientao paisagem. Sabe-se que: Os fluxos so lanados somente para dias teis, conforme dados no arquivo txt O saldo inicial do dia corresponde ao saldo final do dia anterior O saldo inicial do dia primeiro de janeiro de R$ 12.345,00 O saldo final do dia igual ao saldo inicial mais o saldo do dia. O saldo do dia a soma das entradas de caixa (receita e emprstimos) menos a soma das sadas (todas as despesas e as amortizaes de emprstimos). A empresa tem linha de crdito de emprstimos bancrios de curto prazo para cobrir necessidades de caixa. Os emprstimos so depositados no 5o. dia til do ms. Sua devoluo ao banco e o pagamento de juros ocorrem no 5o. dia til do ms subsequente. Os juros Programando em Excel com VBA 55 2. Saldo inicial * 5. Pessoal 8. Despesas bancrias 11. Juros 3. Receita 6. Impostos 9. Emprstimos 12. Saldo do dia *

UERJ / FEN / DEIN

C. Borges; H. Rocha

calculados em um ms devem ser armazenados para lanamento posterior como despesa no ms subsequente. A taxa de juros funo do volume a ser captado perante o banco, conforme tabela abaixo.

Emprstimo At R$ 2.000 De R$ 2.000 a R$ 5.000 De R$ 5.000 a R$ 10.000 De R$ 10.000 a R$ 15.000 De R$ 15.000 a 30.000

Taxa de juros 3% am 4% am 5% am 6% am 7% am

A planilha deve calcular o emprstimo necessrio, igualando-o diferena entre o saldo mnimo projetado do ms e o saldo mnimo desejado, se for negativa. O saldo mnimo desejado deve ser obtido do usurio por meio de InputBox. Para que a planilha simule o emprstimo, o usurio deve antes ser informado da taxa de juros e concordar com seu valor. Caso contrrio, a planilha conclui seu trabalho sem lanamento do emprstimo. Pede-se: 1. Criar a pasta Fluxo de Caixa.xls com as 12 planilhas nomeadas 2. Planejar os passos necessrios construo da rotina 3. Criar e testar a rotina


56

UERJ / FEN / DEIN

C. Borges; H. Rocha

Depurao no VBADepurao total F8 executa cada linha do cdigo Alt + Tab muda para o Excel Alt + Tab volta para o Editor Visual Basic Identificado o problema na macro, parar a execuo do cdigo selecionando Redefinir (barra de ferramentas de macro) ou Executar/Redefinir. Retornar ao Excel e desfazer o que a macro fez, ou fechar o arquivo sem salvar. Pontos de interrupo Clicar na rea da margem cinza esquerda da linha de cdigo que deseja interromper linha realada em marrom Executar a macro (F5) o programa pra antes do ponto de interrupo a linha do ponto de interrupo fica realada em amarelo. Executar a partir da, se quiser, em etapas, teclando F8. Aps depurao, remover os pontos de interrupo clicando no ponto marrom escuro da margem ou selecionando Depurar/Limpar todos os pontos de interrupo Movendo para frente e para trs Usar o mouse para arrastar a linha amarela para cima ou para baixo, para qualquer lugar que queira executar em seguida .. ou... clicar na linha que deseja pular e selecionar Depurar/Definir prxima instruo No parar em cada linha de cdigo Para no parar em cada linha de cdigo, uma opo selecionar Depurar/Executar at o cursor Usando a janela Verificao imediata Ctrl + G exibe a janela de Verificao imediata Pesquisas possveis: A clula selecionada no momento Print Selection.Address O nome da clula ativa Print ActiveCell.Name (Obs.: No tem ponto aps Print) O valor da clula ativa Print ActiveCell.Value Programando em Excel com VBA 57

UERJ / FEN / DEIN

C. Borges; H. Rocha

O endereo da clula ativa Print ActiveCell.Address A frmula da clula ativa Print ActiveCell.Formula Podemos executar o cdigo linha-a-linha teclando F8 e acompanhando na janela de Verificao imediata. Para repetir a mesma consulta coloque o cursor no final da linha da consulta anterior e tecle Enter. Os resultados das consultas anteriores vo sendo jogados para baixo. Consultando com o mouse Passar o mouse sobre uma expresso do cdigo. Esperar um segundo. Uma dica de ferramenta aparece mostrando o valor atual da expresso recurso valioso para loops Consultando com uma janela de inspeo de variveis Substitui a consulta com o mouse ou a verificao imediata na pesquisa do valor de qualquer expresso enquanto percorre o cdigo. Caminho: Depurar/Adicionar inspeo de variveis surge a caixa de dilogo expresso a digitar Resultado: janela Inspees de variveis no canto inferior Pressionando F8 o valor na janela vai sendo atualizado automaticamente Usando uma expresso para definir um ponto de interrupo Clicar com o boto direito no cone dos culos na janela Inspees de variveis editar inspeo de variveis inspeo de variveis selecionar interromper quando o valor for alterado Pressionar ento F5 para executar o cdigo. A macro pra somente quando algo importante (algo novo) acontece.


58

Documents

Program an Do Em Excel Com VBA 2010