Excel VBA Apostila

Embed Size (px)

Citation preview

Excel Macros e Visual Basic for Applications(verso Draft)

ndice

MACROS.........................................................................................................................................................5 O QUE UMA MACRO?......................................................................................................................................6 CRIAR MACROS................................................................................................................................................6 Gravar uma Macro...................................................................................................................................6Exerccio ............................................................................................................................................................8 Procedimento BackGround do Excel...................................................................................................................9

Programao em Visual Basic for Applications....................................................................................10 EXECUTAR UMA MACRO..................................................................................................................................12 Tecla de Atalho Shortcut Key..............................................................................................................12 Boto na Toolbar ..................................................................................................................................13Associar uma Macro a um Boto.......................................................................................................................13 Dissociar uma Macro de um Boto....................................................................................................................15

Run ........................................................................................................................................................16 Comando no Menu.................................................................................................................................17Associao de uma Macro a um Comando do Menu.........................................................................................17 Dissociao.......................................................................................................................................................19

Editor de Visual Basic for Applications.................................................................................................20 REMOVER MACROS.........................................................................................................................................21 Remoo de Macros em Ambiente Excel...............................................................................................21 Remoo de Macros no Editor de VBA..................................................................................................21 EDITOR DE VISUAL BASIC FOR APPLICATIONS............................................................................22 PROJECT EXPLORER ........................................................................................................................................24 PROPERTIES WINDOW......................................................................................................................................26 JANELA DE EDIO.........................................................................................................................................27 OBJECT BROWSER...........................................................................................................................................28 HELP ON-LINE...............................................................................................................................................28 AS CORES DO VBA.......................................................................................................................................29 FUNES E SUB ROTINAS......................................................................................................................30 SUB ROTINAS.................................................................................................................................................32 Definio de Sub Rotinas.......................................................................................................................32 Trabalhar no Editor de VBA Criar uma Sub Rotina ..........................................................................33 Execuo de uma Sub Rotina.................................................................................................................34 FUNES........................................................................................................................................................34 Definio de Funes.............................................................................................................................34 Definio do tipo de parmetros e do tipo da funo............................................................................35 Trabalhar no Editor de VBA Criar uma Funo................................................................................36 Execuo de uma Funo......................................................................................................................37Execuo dentro de uma Clula.........................................................................................................................38 Execuo dentro de uma Rotina........................................................................................................................44

DIFERENAS ENTRE FUNES E ROTINAS............................................................................................................47 REGRAS PARA A PASSAGEM DE PARMETROS........................................................................................................47 ______________________________________________________________________________________ ISCTE / DCTI 1 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)VARIVEIS..................................................................................................................................................48 MANUSEAMENTO COM VARIVEIS.....................................................................................................................48 O que so variveis?..............................................................................................................................49 Associao de valores a variveis:........................................................................................................49 Utilizao de variveis como se fossem valores:...................................................................................50 TIPOS DE VARIVEIS .......................................................................................................................................50 DECLARAO DE VARIVEIS.............................................................................................................................52 VARIVEIS VANTAGENS DA UTILIZAO..........................................................................................................52 VARIVEIS DO TIPO OBJETO.............................................................................................................................53 Declarao da Varivel Objeto.............................................................................................................53 Atribuio de uma varivel Objeto........................................................................................................53 Utilizao Genrica da Varivel Objeto................................................................................................54 Utilizao Especifica da Varivel Objeto..............................................................................................54 VARIVEIS DECLARAO OPCIONAL E O TIPO VARIANT....................................................................................56 Os Prs e Contras da Utilizao do tipo Variants.................................................................................56Prs...................................................................................................................................................................56 Contras..............................................................................................................................................................56

Variveis Declarao Forada...........................................................................................................57 VARIVEIS TIPOS DEFINIDOS PELO USURIO - ESTRUTURAS..................................................................................58 Definio do Tipo...................................................................................................................................58 Utilizao das Estruturas de Dados.......................................................................................................59 VARIVEIS ARRAYS.....................................................................................................................................60 O que um Array ?................................................................................................................................60Array Uni-Dimensional.....................................................................................................................................60 Array Bi-Dimensional.......................................................................................................................................61

Declarao de um array........................................................................................................................62Arrays Uni-dimensionais...................................................................................................................................62 Arrays Bi-dimensionais.....................................................................................................................................62

Utilizao de um Array..........................................................................................................................63Para acessar ao elemento...................................................................................................................................63 Atribuio de valores ........................................................................................................................................63

Option Base e Array Bounds..................................................................................................................64 CONSTANTES.............................................................................................................................................65 O QUE SO CONSTANTES ?................................................................................................................................66 INPUTBOX E MSGBOX.............................................................................................................................68 O QUE SO ?..................................................................................................................................................68 INPUTBOX......................................................................................................................................................69 O que faz.............................................................................................................................................69 Sintaxe....................................................................................................................................................69 Parmetros.............................................................................................................................................69 MSGBOX.......................................................................................................................................................70 O que faz.............................................................................................................................................70 Sintaxe....................................................................................................................................................71 Parmetros.............................................................................................................................................71 Valores Produzidos.............................................................................................................................74 DOMNIO DAS VARIVEIS, CONSTANTES E ROTINAS.................................................................76 O QUE O DOMINIO?.......................................................................................................................................76 DOMNIO DAS VARIVEIS.................................................................................................................................77 mbito do Procedimento........................................................................................................................77 mbito do Mdulo..................................................................................................................................78 mbito do Projeto..................................................................................................................................79 DOMNIO DAS CONSTANTES .............................................................................................................................80 mbito do Procedimento........................................................................................................................81 ______________________________________________________________________________________ ISCTE / DCTI 2 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)mbito do Mdulo..................................................................................................................................81 mbito do Projeto..................................................................................................................................82 DOMNIO DE SUB ROTINAS E FUNES..............................................................................................................82 ESTRUTURAS DE CONTROLE ..............................................................................................................84 O QUE SO ESTRUTURAS DE CONTROLE?.............................................................................................................84 QUAIS AS ESTRUTURAS.................................................................................................................................85 IF-THEN-ELSE................................................................................................................................................85 Funo IF do Excel................................................................................................................................85 Sintaxe da Estrutura If-Then-Else..........................................................................................................86 Aplicao Prtica...................................................................................................................................86 A instruo adicional ElseIf..................................................................................................................89Aplicao Prtica...............................................................................................................................................89

FOR NEXT..................................................................................................................................................90 Sintaxe....................................................................................................................................................90 Aplicao Prtica...................................................................................................................................90A Funo das Variveis:....................................................................................................................................91 Construo do Ciclo:.........................................................................................................................................91 Traduo Integral..............................................................................................................................................92

Funcionamento do Ciclo:.......................................................................................................................92 Perigos associados utilizao do ciclo For-Next:..............................................................................93 Outra Aplicao ....................................................................................................................................93 WHILE-WEND................................................................................................................................................94 Sintaxe....................................................................................................................................................94 Aplicao Prtica...................................................................................................................................95A Funo das Variveis:....................................................................................................................................95 Construo do Ciclo:.........................................................................................................................................95 Traduo Integral..............................................................................................................................................96

Funcionamento do Ciclo........................................................................................................................96 Perigos associados utilizao do ciclo While-Wend...........................................................................97 Outra Aplicao.....................................................................................................................................97 DO LOOP..................................................................................................................................................101 Sintaxe..................................................................................................................................................101 Aplicaes Prticas..............................................................................................................................102 SELECT CASE...............................................................................................................................................104 Sintaxe..................................................................................................................................................104 Aplicao Prtica.................................................................................................................................105Construo da Estrutura...................................................................................................................................106

FOR EACH NEXT....................................................................................................................................107 Sintaxe..................................................................................................................................................107 Aplicaes Prticas..............................................................................................................................108Utilizando Arrays............................................................................................................................................108 Construo do Ciclo....................................................................................................................................109 Utilizando Colees de Objetos.......................................................................................................................110

COLEES DE OBJETOS E OBJETOS...............................................................................................112 O QUE SO OBJETOS ?...................................................................................................................................113 OBJETOS: PROPRIEDADES, MTODOS E EVENTOS...............................................................................................113 Propriedades........................................................................................................................................113 Mtodos ...............................................................................................................................................113 Eventos.................................................................................................................................................114 OBJETOS MAIS UTILIZADOS NO EXCEL..............................................................................................................115Propriedades....................................................................................................................................................115 Mtodos...........................................................................................................................................................116 Propriedades....................................................................................................................................................117 Mtodos...........................................................................................................................................................118 Propriedades....................................................................................................................................................119

______________________________________________________________________________________ ISCTE / DCTI 3 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)Mtodos...........................................................................................................................................................120 Propriedades....................................................................................................................................................121 Mtodos...........................................................................................................................................................122

OBJETOS SINGULARES VS COLEES DE OBJETOS..............................................................................123 INDEXAO DE COLEES POR NMERO OU NOME...............................................................................................124 Indexao com Base em Nmeros........................................................................................................124 Indexao com Base no Nome.............................................................................................................125Vantagem .......................................................................................................................................................125

O Objeto Range uma exceo...........................................................................................................126Tratamento como objeto:.................................................................................................................................126 Tratamento como coleo de objetos:..............................................................................................................126

REFERNCIA IMPLCITA..................................................................................................................................127 Declarao implcita da aplicao:.....................................................................................................127 Declarao implcita do WorkBook:....................................................................................................128 Declarao implcita da WorkSheet:...................................................................................................128 Nvel de referncia a privilegiar..........................................................................................................129 MISCELLANEOUS...................................................................................................................................130 A INSTRUO WITH......................................................................................................................................131 Aplicao Prtica.................................................................................................................................131 OUTRAS FUNES TEIS DO VBA.................................................................................................................132

______________________________________________________________________________________ ISCTE / DCTI 4 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Macros

______________________________________________________________________________________ ISCTE / DCTI 5 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

O

QUE UMA

MACRO?

Uma macro um pequeno programa que contm uma lista de instrues a realizar no Excel. Como sendo um repositrio de operaes, uma macro pode executar um conjunto de tarefas atravs de um nico procedimento o qual pode ser invocado rapidamente. As instrues que formam o corpo da macro so escritas num cdigo prprio para que o computador as possa entender, essa linguagem designada por VBA Visual Basic for Applications.

CRIAR MACROSExistem duas possibilidades de criao de macros: Atravs do Gravador de Macros Utilizando o editor e programando em Visual Basic for Applications

Gravar uma Macro1.

Tools / Macro / Record New Macro. O Excel exibir a caixa de dilogo da fig.1.

Fig.1 caixa de dilogo para a gravao de macros.

______________________________________________________________________________________ ISCTE / DCTI 6 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) 2. O nome da Macro ser constitudo por um conjunto de caracteres que identificaro a Macro e a sua funcionalidade. 3. Shortcut Key composto por uma combinao de teclas que podero ser utilizadas para executar uma macro.4.

Opte por armazenar a macro numa das seguintes alternativas: This Workbook, New Workbook ou Personal Workbook, sendo que cada uma corresponde a macros de mbito diferente. Assim devero armazenar a macro na opo ThisWorkBook para que ela esteja activa sempre que o documento estiver aberto, e para que ela esteja armazenada no prprio arquivo no correndo o risco de ficar armazenada nas Macros do Excel. (na rede do ISCTE impossvel gravar a macro no Personal WorkBook.)

5. No campo Description, introduza um comentrio funo este poder ser importante para que no seja esquecido o respectivo objectivo e funcionalidade.6.

Clique em OK para iniciar a gravao da macro neste momento ser exibida uma toolbar semelhante da figura 2, e o cone do mouse ser transformado numa cassete, indicando o estado de gravao.

Fig.2 Toolbar exibida para a gravao de macros.

1. Na toolbar Stop Record, existem dois botes: Stop Recording e Relative Reference O boto de Stop Recording termina a gravao da macro, o boto de Relative Reference seleciona o modo de gravao da macro se feito com base em referncias relativas (boto selecionado) ou referncias absolutas. 2. No caso da toolbar Stop Record desaparecer, poder voltar a exibi-la fazendo no menu a sequncia: View / Toolbars / Stop Record (selecione a toolbar). Caso a toolbar no aparea listada a gravao de macros no est ativa. Nota: Fique atento aos passos que d quando est gravando pois tudo ser registrado, o que significa que quando for executar a macro, esses procedimentos sero efetuados.

______________________________________________________________________________________ ISCTE / DCTI 7 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

EXERCCIOObjetivo: Gravar uma macro testando a diferena entre a execuo de macros comreferncias absolutas e relativas. 1 Fase: Macro com referncias Absolutas 1. Acione a gravao da macro. Atribua-lhe o Short Key Ctrl+P 2. Certifique-se que o boto Relative Reference est desativado. 3. Clique na clula B3. 4. Escreva ISCTE 5. Formate a clula para Bold, Itlico, tamanho 18, Small Caps, (utilize o Format / Font) 6. Na clula B4 escreva: Av. Foras Armadas 7. Na clula B5 escreva: 1700 Lisboa 8. Pare a gravao da macro. A macro est criada. 9. Apague tudo o que escreveu nas clulas da coluna B. 10. Clique na clula L8. 11. Aperte simultaneamente nas teclas Ctrl e P 12. O que aconteceu ? 2 Fase: Macro com referncias Relativas 1. Selecione a clula D5. 2. Acione a gravao da macro. Atribua-lhe o Short Key Ctrl+R 3. Selecione o boto Relative Reference. 4. Escreva ISCTE na clula que havia sido selecionada. 5. Formate a clula para Bold, Itlico, tamanho 18, Small Caps, (utilize o Format / Font) 6. Na clula D6 escreva: Av. Foras Armadas 7. Na clula D7 escreva: 1700 Lisboa 8. Pare a gravao da macro. A macro est criada. 9. Apague tudo o que escreveu nas clulas da coluna D. 10. Clique na clula L8. 11. Carregue simultaneamente nas teclas Ctrl e R 12. O que aconteceu ? Porqu ?

______________________________________________________________________________________ ISCTE / DCTI 8 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

PROCEDIMENTO BACKGROUND

DO

EXCEL

O Excel quando grava uma macro cria um objeto designado por module no workbook onde registra todas as operaes gravadas em linguagem Visual Basic for Applications VBA. Este modulo no aparece no Excel com as restantes Sheets. Para ser visualizado necessrio abrir o Editor de Visual Basic for Applications: 1. Tools / Macro / Macros2.

Seleciona-se a Macro e Clica-se no boto Edit

3. Poder visualizar na rea do lado direito o cdigo VBA que est subjacente s macros que entretanto gravou. A poder executar as mesmas tarefas que num editor de texto normal: escrever, apagar, copiar, mover, mas instrues de VBA. 4. Tente fazer a leitura do que est escrito e compreenda o procedimento da macro. 5. Para regressar ao Excel basta File / Close and return to Microsoft Excel.

______________________________________________________________________________________ ISCTE / DCTI 9 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Programao em Visual Basic for Applications1.

Para acessar ao editor de Visual Basic for Applications: Tools / Macro / Visual Basic Editor (para se familiarizar mais com o editor consulte o capitulo Editor Visual Basic for Applications e o capitulo Funes e sub-rotinas)

Figura 3 Editor de Visual Basic for Applications

2. Para inserir um mdulo faa Insert / Module isto porque as macros que construir devero ser escritas dentro de um mdulo (repare na Figura 3 que o Module 1 est selecionado na janela de projeto e do lado direito tem-se uma rea de edio onde podero escrever as macros a executar)

______________________________________________________________________________________ ISCTE / DCTI 10 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) 3. Pode agora programar os procedimentos manualmente ou com recurso a algumas funcionalidades do Editor: a) Insert / Procedure insere um procedimento para o qual dever indicar o tipo de procedimento. b) Quando estiver a introduzir as variveis haver de ser exibida a lista de tipos de variveis possvel.4.

Sempre que precisar construir uma macro mais ou menos complicada, se no conhecer muito de VBA poder comear por gravar no Excel pequenas macros com operaes muito simples e depois no editor de VBA tentar compreender a sua funcionalidade e assim construir uma macro cada vez mais complexa, completa e adequada s suas exigncias.

5. Poder em cada macro fazer uma chamada a uma outra macro, bastando para tal escrever o respectivo nome. 6. No Editor de Visual Basic for Applications poder encontrar ajuda para o desenvolvimento do seu procedimento. Assim: 6.1.6.2.

Clique no cone do Object Browser ou View/Object Browser ou F2

Na Caixa de Drop-Down onde aparece referido , selecione a aplicao para a qual pretende ajuda neste caso Excel. Convm referir que poder utilizar as funes de toda e qualquer aplicao. Na rea intitulada por Classes aparecem todos os objetos especficos para o manuseamento da aplicao selecionada. A classe especial designada por Globals refere-se s funes que esto disponveis na aplicao independentemente dos objetos selecionados.6.3.

Selecione um dos objetos e visualize do lado direito os Members of 6.4.

6.5. Para cada membro da classe dispe de um help on-line que o esclarece sobre a respectiva funo e funcionamento, dando exemplo que poder testar. Para tal basta clicar sobre o boto de Help da janela do Object Browser.

______________________________________________________________________________________ ISCTE / DCTI 11 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

EXECUTAR

UMA

MACRO

A execuo de uma macro pode ser concretizada de diversas formas: Tecla de Atalho Shortcut Key Boto na Toolbar Run Comando no Menu Editor de Visual Basic for Applications

Tecla de Atalho Shortcut KeyA associao a teclas de atalho realizada quando da criao da macro.

______________________________________________________________________________________ ISCTE / DCTI 12 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Boto na Toolbar

ASSOCIAR

UMA

MACRO

A UM

BOTO

1. View / Toolbars / Customize2. 3.

Na janela do Customize selecione o Commands Tab Selecione a categoria Macro (visualizar a caixa de dilogo exibida na Figura 4)

Fig.4 caixa de dilogo para vinculao de boto a macros

______________________________________________________________________________________ ISCTE / DCTI 13 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

4.

Na rea respeitante aos Commands sero exibidos dois itens: Custom Menu Item e Custom Button. Selecione este segundo e arraste-o com o mouse at a Barra de Ferramentas onde pretende inseri-lo. Ateno s poder inserir o boto quando o ponteiro do mouse ficar com a forma I. Nesse momento libertar o boto do mouse utilizado para o arrastamento e verificar que ser criado um novo boto.

5. Na janela de Customize poder ainda utilizar dois botes que se encontram na rea do Selected Command: a) Description que exibe um texto explicando o que o comando selecionado faz. b) Modify Selection semelhante ao clique sobre o boto criado, exibe uma srie de tarefas possveis para configurao do boto ( ver operaes seguintes).

Fig.5 Menu para configurao do boto da toolbar

______________________________________________________________________________________ ISCTE / DCTI 14 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

6. Clique sobre o boto Modify Selection- abrindo um menu de tarefas possveis para a configurao do boto Ver Figura 5 a) No ltimo agrupamento de configuraes possveis existe uma opo designada por Assign Macro. Esta opo permite indicar qual a macro que dever ser executada sempre que se clica no boto.b)

No terceiro agrupamento existem 4 estilos diferentes de exibir o boto: s com texto, com texto e imagem ou somente com imagem. Se selecionar o estilo Texto e Imagem, ser exibido no boto para alm da imagem o nome associado ao boto. Na opo Name indique o nome que pretende ver associado ao boto, por padro aparece o nome da macro. (repare que aparece um & atrs da letra que aparece a sublinhado) Para alterar a imagem associada ao boto poder: optar por uma imagem diferente, alterar a que est a visualizar ou a construir a sua. Para isso clique sobre a tarefa de Change Button Image, selecione o logotipo pretendido, se nenhum o satisfizer e pretender criar o seu selecione o logotipo que representa um quadrado vazio. Para o poder (re)desenhar a imagem, na lista de tarefas disponibilizada pelo boto Modify Selection opte por Edit Button Image e crie a sua imagem que poder reeditar.

c)

d)

DISSOCIAR

UMA

MACRO

DE UM

BOTO

1. Tools / Customize 2. Arraste o boto da toolbar at ao documento 3. Solte-o

______________________________________________________________________________________ ISCTE / DCTI 15 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Run1. Tools / Macro / Macros2.

Na caixa de dilogo Macros seleciona-se a macro pretendida na lista da Macro Name (Figura 6)

3. Clique sobre o boto Run

Fig.6 Janela para seleo da macro a executar

______________________________________________________________________________________ ISCTE / DCTI 16 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Comando no Menu

ASSOCIAO MENU

DE UMA

MACRO

A UM

COMANDO

DO

1. View / Toolbars / Customize 2. Na janela do Customize encontra-se no Commands Tab3. 4.

Selecione a categoria Macro Na area relacionada aos Commands ser exibido um item de Custom Menu Item, selecione-o e arraste-o com o mouse at a uma posio do menu que lhe interesse por exemplo pode introduzir numa das listas de opes do menu (File, View,) ou ento criar como uma nova opo do Menu..

Fig.7 Caixa de dilogo para atribuir uma macro a um comando do menu

______________________________________________________________________________________ ISCTE / DCTI 17 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

5. Se pretender criar uma nova lista no menu dever : Selecionar a categoria New Menu b) Na rea dos Commands ser exibida a opo New Menu, que dever arrastar at barra dos menus c) Poder alterar o seu nome clicando no boto de Modify Selection d) Esta nova lista ter o mesmo comportamento que a outrasa)

Fig.8 Caixa de dilogo para criar um novo menu na barra dos menus

6.

Na janela de Customize poder ainda utilizar dois botes que se encontram na rea do Selected Command: Description que exibe um texto explicando o que o comando selecionado faz. d) Modify Selection semelhante ao clique sobre o menu ou comando de menu criado, exibe uma srie de tarefas possveis para configurao (Ver o item 6 do captulo Associar uma Macro a um Boto)c)

______________________________________________________________________________________ ISCTE / DCTI 18 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

DISSOCIAO1. Tools / Customize 2. Arraste o Menu ou Comando do Menu at ao documento e solte-o

______________________________________________________________________________________ ISCTE / DCTI 19 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Editor de Visual Basic for Applications1.

Tools / Macro / Visual Basic Editor - para acessar ao Editor

2. Posicionando-se no corpo de uma macro, na janela do lado direito inferior, poder executar a macro atravs de: Run / Run Sub-UserForms (figura 9) ou boto Run.

Fig.9 Execuo de uma macro no Editor de Visual Basic for Applications

______________________________________________________________________________________ ISCTE / DCTI 20 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

REMOVER MACROSA remoo das macros poder ser feita: no ambiente Excel, ou no Editor de VBA

Remoo de Macros em Ambiente Excel1. Tools / Macro / Macros2.

Seleciona-se a Macro a remover

3. Clica-se no boto Delete 4. Pede a confirmao e apaga se confirmar

Remoo de Macros no Editor de VBA2.

Tools / Macro / Visual Basic Editor - para acessar ao Editor

3. Podem-se apagar as macros que se encontrem na janela do lado direito inferior e que tm inicio com a palavra Sub e fim com as palavras End Sub (Ver capitulo de funes e procedimentos)

______________________________________________________________________________________ ISCTE / DCTI 21 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Editor de Visual Basic for Applications

______________________________________________________________________________________ ISCTE / DCTI 22 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Para acessar o editor de Visual Basic for Applications: Tools / Macro / Visual Basic Editor

Figura 10 Editor de Visual Basic for Applications

Encontrar a tela dividida em trs grandes reas: Project Explorer, Properties Window e do lado direito janela de edio de texto.

______________________________________________________________________________________ ISCTE / DCTI 23 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

PROJECT EXPLORERSe a janela no estiver visvel ative-a atravs do View / Project Explorer ou utilizando o boto ou combinaes de letras associadas a esta tarefa. Nesta janela poder visualizar a hierarquia dos projetos de Visual Basic for Applications ativos nesse momento. Entre eles dever encontrar um cujo nome corresponde ao nome do WorkBook do Excel com que est a trabalhar. E.g. VBAProject (Book1). dentro deste projeto que dever trabalhar para que todas as funcionalidades que implemente estejam nele ativas sempre que necessrio.

Figura 11 Janela do Project Explorer

______________________________________________________________________________________ ISCTE / DCTI 24 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Assim se fizer o desdobramento do seu VBAProject, encontrar uma pasta cuja designao Microsoft Excel Objects, e uma outra designada Modules (se esta no aparecer significa que o seu projeto ainda no possui qualquer macro implementada. Para criar esta pasta dever fazer: Insert / Module). Na pasta do Microsoft Excel Objects, encontrar todos os objetos que fazem parte do seu documento: as WorkSheets e o WorkBook (que no fundo o conjunto de WorkSheets). Se clicar duplamente em cada um destes objetos, uma nova janela ser visualizada na rea da direita, em cada uma dessas reas poder definir a ao a ser executada em funo dos eventos de cada objeto. Na Pasta Modules, aparecer o conjunto de pastas (mdulos) onde poder programar as suas macros. Clicando duplamente em cada um dos mdulos indicados poder visualizar as macros, que o compem, na janela da direita.

______________________________________________________________________________________ ISCTE / DCTI 25 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

PROPERTIES WINDOWSe a janela das propriedades no estiver visvel ative-a atravs do View / Properties Window ou utilizando o boto ou combinaes de letras associadas a esta tarefa.

Figura 12 Janela de propriedades

Nesta janela poder visualizar e alterar as propriedades que definem cada objeto: o nome - neste caso.

______________________________________________________________________________________ ISCTE / DCTI 26 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

JANELA

DE

EDIO

A janela de edio exibir a cada momento o cdigo em Visual Basic for Applications associado ao elemento selecionado na janela do Project Explorer.

Figura 13 Janela de edio

______________________________________________________________________________________ ISCTE / DCTI 27 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

OBJECT BROWSERNo Editor de Visual Basic for Applications poder encontrar ajuda para o desenvolvimento do seu procedimento. Assim: Clique no cone do Object Browser ou View/Object Browser ou F2 Na Caixa de Drop-Down onde aparece referido , selecione a aplicao para a qual pretende ajuda neste caso Excel. Convm referir que poder utilizar as funes de toda e qualquer aplicao.

Na rea intitulada por Classes aparecem todos os objetos especficos para o manuseamento da aplicao selecionada. A classe especial designada por Globals refere-se s funes que esto disponveis na aplicao independentemente dos objetos selecionados. Selecione um dos objetos e visualize do lado direito os Members of

Para cada membro da classe dispe de um help on-line que o esclarece sobre a respectiva funo e funcionamento, dando um exemplo que poder testar. Para tal basta clicar sobre o boto de Help da janela do Object Browser.

HELP ON-LINEO Editor de Visual Basic for Applications, disponibiliza ao usurio um sistema de ajuda constante. Assim, quando se constroem procedimentos, na janela de edio, medida que a linguagem escrita o editor abre oportunamente listas de opes para ajudar a escrever o cdigo.

______________________________________________________________________________________ ISCTE / DCTI 28 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

AS CORES

DO

VBA

Na Janela de Edio so programados, armazenados e exibidos os procedimentos VBA criados. Porm, o texto envolvido em cada procedimento dotado de uma srie de cores diferentes, sendo que cada uma tem significado diferente.

CorAzul Vermelho

SignificadoPalavras-Chave da linguagem. Ex: Sub, End Sub, Function, If, Then, Else, While, Loop, Sempre que escreve na janela uma linha em linguagem VBA, o editor vai retificar a sintaxe da linguagem por forma a indicar se existe ou no algum erro de escrita. Se for detectado um erro a linha fica a vermelho e exibida uma mensagem de erro, seno todas as palavras so reconhecidas, coloridas da cor da respectiva categoria e alteradas para letras maisculas ou minsculas, conforme esteja pr-definido. Nome de variveis, procedimentos, valores, operadores, Comentrios introduzidos no meio dos procedimentos. Estes comentrios servem para o usurio poder associar algumas explicaes aos procedimentos realizados. De referir que as palavras com esta cor so ignoradas no procedimento, i.e., no produzem qualquer efeito na sua execuo. Para introduzir comentrios bastar que o caractere anteceda o texto a introduzir. Um sombreado amarelo poder aparecer sobre a linha que identifica um procedimento. Esta cor simboliza a ocorrncia de um erro na execuo do respectivo procedimento e o estado de execuo do mesmo, i.e., o procedimento iniciou a execuo, durante a qual detectou um erro e agora est parado, mas ainda em execuo. Quando isto acontecer no dever voltar a dar ordem de execuo do procedimento, sem antes parar (Stop) a execuo mal sucedida.

Preto Verde

Amarelo

______________________________________________________________________________________ ISCTE / DCTI 29 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Funes e Sub Rotinas

______________________________________________________________________________________ ISCTE / DCTI 30 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Como foi referido anteriormente, quando se grava uma macro no Excel, este tem um comportamento em background que realiza a respectiva codificao para Visual Basic for Applications. Este resultado bem visvel quando procedemos edio de uma macro no Editor de Visual Basic for Applications. Cada macro que se cria tem um comportamento concreto e autnomo relativamente a outras macros implementadas, e tem como objetivo executar um determinado nmero de instrues1 que respondam s exigncias do usurio. Cada macro criada d origem a um procedimento ou rotina. Existem dois tipos de rotinas: as sub-rotinas ou rotinas Sub, e as funes.

1

Entenda-se instruo como uma tarefa a executar que corresponde a uma linha de cdigo.

______________________________________________________________________________________ ISCTE / DCTI 31 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

SUB ROTINASDefinio de Sub RotinasAs Sub Rotinas so aquelas cuja definio delimitada pelas palavras-chave Sub e EndSub. Assim se reparar todas as macros que grava no Excel so deste tipo. Repare ainda como que so definidas: Sub ( ) End Sub Estas Sub Rotinas so designadas pelo nome2 que lhe atribumos e no recebem parmetros3 do exterior, tm como funo desempenhar um conjunto de tarefas que compem o seu corpo. O corpo da macro, assim 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 o editor faz a sua partio por diversas linhas, recorrendo ao operador _, por forma a facilitar a leitura.

2

O nome da rotina pode ser qualquer um desde que no contenha espaos, comece por caracteres alfa

3

Para ter a noo do que so parmetros recorde-se do funcionamento das funes do Excel, que para executarem determinada funo necessitavam de receber parmetros que colocamos entre parntesis separados por virgulas. ______________________________________________________________________________________ ISCTE / DCTI 32 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Trabalhar no Editor de VBA Criar uma Sub RotinaPara criar uma Sub Rotina necessrio que exista um mdulo onde se possa escrever. Uma vez posicionado nesse mdulo poder: Escrever a macro integralmente, ou Recorrer ao Insert / Procedure para que o Visual Basic for Applications lhe crie a estrutura (Figura 14)

Figura 14 Caixa de Dilogo para a criao de uma nova rotina

Assim dever indicar o tipo de rotina a criar Sub e o nome que pretende dar macro (ou rotina ou procedimento ou Sub Rotina) 4. Automaticamente ele criar a estrutura da rotina, neste caso ficaria: Sub Macro1( ) End Sub

4

Nomes alternativos para fazer referncia a um conjunto de instrues. ______________________________________________________________________________________ ISCTE / DCTI 33 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Execuo de uma Sub RotinaSub Rotina e Macro so duas designaes para a mesma realidade, portanto tudo o que foi referido relativamente a Macros vlido tambm para as Sub Rotinas. (Consultar captulo das Macros) Neste contexto a execuo de Sub Rotinas segue o mesmo mecanismo das macros. Porm neste momento j se pode referir uma nova forma de executar as macros ou Sub Rotinas dentro de outras rotinas, i.e., quando se escreve dentro de uma rotina o nome de outra a execuo da rotina tem continuidade na execuo da rotina que est a ser invocada.

FUNESDefinio de FunesFunes so rotinas cuja definio comea com a palavra-chave Function e termina com as palavras End Function. Todas as funes que utiliza no Excel so deste tipo de rotina. A sua definio tem a estrutura seguinte: Function ( , ,) = End Function A funo identificada pelo nome, pelo nmero e tipo de parmetros recebidos, e tem como objetivo executar um conjunto de instrues e produzir um valor final. Isto , sempre que se pretender executar uma funo sabido priori que ela produzir um valor. Recorde-se como exemplo a funo SUM, esta recebe por parmetro um conjunto de valores que se pretendem somar, sabe-se que o resultado da aplicao dessa funo ao conjunto de valores ser o respectivo somatrio. Para definir o valor produzido por uma funo basta no seu interior, atribuir ao nome da funo um determinado valor ou expresso.______________________________________________________________________________________ ISCTE / DCTI 34 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Definio do tipo de parmetros e do tipo da funoTodos os elementos de input e output de uma funo tm um tipo de dados atribudo. Assim os parmetros devero ser definidos com o tipo de dados respectivo e a funo dever ser definida do tipo de dados que ela envie para o exterior. Aps isto poder-se- refinar a definio de uma funo: Function ( As , ) As = End Function Nota: Se os tipos no forem definidos ser assumido por padro como sendo do tipo Variant

______________________________________________________________________________________ ISCTE / DCTI 35 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Trabalhar no Editor de VBA Criar uma FunoPara criar uma Funo necessrio que exista um modulo onde se possa escrever. Uma vez posicionado nesse mdulo poder: Escrever a macro integralmente, ou Recorrer ao Insert / Procedure para que o Visual Basic for Applications lhe crie a estrutura (Figura 15)

Figura 15 Caixa de Dilogo para a criao de uma nova rotina - funo

Nesta caixa de dilogo dever indicar o tipo de rotina a criar Function e o nome que pretende dar funo. Automaticamente o Visual Basic for Applications criar a estrutura da rotina, que neste caso ficaria: Function IVA( ) End Function

______________________________________________________________________________________ ISCTE / DCTI 36 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Execuo de uma FunoUma vez que uma funo produz um valor ela poder ser executada: dentro de uma clula numa WorkSheet, semelhana de qualquer uma outra funo do Excel dentro de qualquer outra funo ou SubRotina. Com base na seguinte funo, analisemos as formas de execuo com mais detalhadamente:

Figura 16 Janela de Edio com a funo IVA

______________________________________________________________________________________ ISCTE / DCTI 37 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

EXECUO

DENTRO DE UMA

CLULA

______________________________________________________________________________________ ISCTE / DCTI 38 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

______________________________________________________________________________________ ISCTE / DCTI 39 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

______________________________________________________________________________________ ISCTE / DCTI 40 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

______________________________________________________________________________________ ISCTE / DCTI 41 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

______________________________________________________________________________________ ISCTE / DCTI 42 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) 1. Posicione-se na clula onde pretende inserir a funo 2. Insert / Function 3. Selecione a categoria User Defined repare que aparece listada a funo que acabou de criar IVA (Figura 17)

Figura 17 Caixa de Dilogo para introduo da funo

4. Clique em OK e de imediato uma janela de ajuda vir auxiliar a utilizao da sua funo (Figura 18)

______________________________________________________________________________________ ISCTE / DCTI 43 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Figura 18 Caixa de Dilogo para apoio utilizao da funo

5. Introduza os parmetros e clique em OK (Figura 19)

Figura 19 Caixa de Dilogo para apoio utilizao da funo introduo de valores

Em suma:Qualquer funo poder ser chamada a partir da WorkSheet do Excel, sendo que a sua chamada ser realizada semelhana de qualquer outra funo: numa clula = ( , )

EXECUO

DENTRO DE UMA

ROTINA

Quando dentro de uma rotina se faz referncia ao nome de uma outra rotina a execuo da primeira passa pela execuo daquela que est a ser invocada.______________________________________________________________________________________ ISCTE / DCTI 44 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) No exemplo que se segue, a funo Valor_Liquido faz uma chamada funo IVA por forma a que, o valor por esta produzido, seja adicionado varivel Valor, e assim produzir o output final da funo Valor_Liquido.

Figura 20 Funo Valor_Liquido ao ser executada d ordens de execuo funo IVA

Um outro exemplo poder elucidar melhor:

______________________________________________________________________________________ ISCTE / DCTI 45 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Figura 21 A Subrotina Ident_Maior ao ser executada d ordens de execuo funo Ver_Maior

______________________________________________________________________________________ ISCTE / DCTI 46 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

DIFERENAS

ENTRE

FUNES

E

ROTINAS

As funes so similares s sub rotinas, existem simplesmente trs diferenas:1.

As Funes Comeam com a palavra-chave Function e terminam com as palavras End Function

2. As Funes podem ser chamadas a partir de frmulas introduzidas numa WorkSheet1.

As funes retornam valores para as frmulas ou sub rotinas que as chamarem

REGRAS

PARA A PASSAGEM DE PARMETROS

Regra 1: Como uma funo retorna um valor, esta dever ser utilizada numa expresso. Quando uma funo utilizada do lado direito de uma associao, ou como argumento de uma outra rotina, se devero passar os parmetros dentro de parntesis Regra 2: Pode-se chamar uma funo ou sub rotina com a palavra-chave Call, neste caso se devero colocar todos os parmetros entre parntesis. Regra 3: Quando uma rotina chamada de forma isolada e sem a palavra-chave Call, no se deve utilizar parntesis.

______________________________________________________________________________________ ISCTE / DCTI 47 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Variveis

MANUSEAMENTO

COM

VARIVEIS

______________________________________________________________________________________ ISCTE / DCTI 48 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

O que so variveis?As variveis constituem repositrios temporrios de dados, podendo ser utilizadas para diversos fins.

Figura 22 Manuseamento de Variveis

Associao de valores a variveis:______________________________________________________________________________________ ISCTE / DCTI 49 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) Quando se pretende atribuir valores a variveis dever-se indicar o nome da varivel, o operador "=" e o valor que se pretende que a varivel armazene. = Pela observao do procedimento Adio - Figura 22- podemos verificar que as variveis Parcela_1 e Parcela_2 armazenam os valores introduzidos pelo usurio atravs das InputBox (Ver captulo "InputBox e MsgBox"). Tambm varivel Total vai ser atribudo o valor resultante da adio das duas parcelas - Total = Parcela_1 + Parcela_2.

Utilizao de variveis como se fossem valores:O nome da varivel representa o contedo da mesma, i.e., sempre que mencionar o nome da varivel o seu contedo que ser considerado. No exemplo da figura 22, pode-se constatar que na expresso: Total = Parcela_1 + Parcela_2 Parcela_1 representa o primeiro valor introduzido e Parcela_2 representa o segundo valor, no se trata de adicionar o nome de duas variveis, mas adicionar os contedos que elas armazenam.

TIPOS

DE VARIVEIS

______________________________________________________________________________________ ISCTE / DCTI 50 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) O tipo de varivel est associado ao gnero de informao que esta tem hiptese de armazenar Boolean 2 bytes Permite armazenar valores Boolenaos True ou False Byte 1 Byte permite armazenar nmeros sem sinal entre 0 e 255 Currency - 8 bytes permite armazenar moeda Date 8 Bytes permite armazenar datas Double 8 bytes permite armazenar um real desde -1.79769313486232E308 at -4.94065645841247E-324 para valores negativos, e desde 1.79769313486232E308 at 4.94065645841247E-324 para valores positivos. Single 4 bytes permite armazenar um real desde -3.402823E38 at -1.4011298E-45, para valores negativos e desde 3.402823E38 at 1.4011298E-45, para valores positivos Integer 2 bytes - permite armazenar nmeros inteiros entre -32.768 e 32767 Long 4 bytes permite armazenar nmeros inteiros entre -2 147 483 648 e 2 147 483 648 Object 4 bytes utilizado para fazer referncia a um objeto do Excel String 1 byte por caractere permite armazenar conjuntos de caracteres Variant 16 bytes - permite armazenar qualquer tipo de dados User-Defined permite armazenar valores de tipos diferentes

______________________________________________________________________________________ ISCTE / DCTI 51 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

DECLARAO

DE

VARIVEIS

Dim I_Numero As Integer Onde:Dim Palavra chave que indica uma declarao de variveis (abreviatura de dimenso) I_Numero - nome da varivel a utilizar As palavra chave utilizada para separar o nome da varivel do tipo de dados Integer tipo de dados atribudo varivel

possvel na mesma declarao de variveis declarar variveis de diversos tipos:

Dim var1 As Integer, var2 As Date, var3 As DoublePara declarar diversas variveis do mesmo tipo:

Dim var_1, var_2, var_3 As Currency

VARIVEIS VANTAGENS

DA UTILIZAO

Simplificam a codificao, principalmente quando se necessita de utilizar um valor especifico inmeras vezes Com variveis o cdigo mais rpido

______________________________________________________________________________________ ISCTE / DCTI 52 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

VARIVEIS

DO

TIPO OBJETO

Uma varivel objeto representa uma referncia a um objeto. Uma varivel de extrema importncia que facilita a codificao e melhora a performance da sub rotina.

Declarao da Varivel ObjetoDim As Object

Atribuio de uma varivel ObjetoSet =

Onde:Set palavra chave que indica a associao de uma varivel objeto - Varivel Objeto = - Operador de associao - Objeto a ser atribudo varivel

______________________________________________________________________________________ ISCTE / DCTI 53 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Utilizao Genrica da Varivel ObjetoA utilizao genrica do tipo Objeto serve para suportar qualquer tipo de objeto Excel (WorkBook,WorkSheet,Range,) Exemplo: Dim Range_1 as Object Range_1 = Worksheet(1).Range(A1) Range_1.Value = 10

Utilizao Especifica da Varivel ObjetoUtiliza-se o tipo exato do objeto que se pretende atribuir varivel. Esse tipo especifico de objetos coincide com o nome dos objetos em EXCEL. Dim Range_1 As Range Dim WB_1 As Workbook Dim WS_1 As WorkSheet Dim XL As Aplicativo Exemplo: Dim Range_1 as Range Range_1 = Worksheet(1).Range(A1) Range_1.Value = 10

______________________________________________________________________________________ ISCTE / DCTI 54 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) Contudo estas declaraes tambm podem ser feitas da seguinte forma genrica: Dim Range_1 As Object Dim WB_1 As Object Dim WS_1 As Object Dim XL As Object

Ento qual o interesse de definir as variveis de forma especifica se o podemos fazer de forma genrica?Por uma questo de performance, se utilizar um objeto genrico, o VBA antes de executar qualquer funo com o objeto tem que primeiramente o identificar (perdendo tempo) em sub rotinas simples essa diferena no substancial mas quando se trata de grandes sub rotinas j se denotam diferenas significativas.

______________________________________________________________________________________ ISCTE / DCTI 55 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

VARIVEIS DECLARAO OPCIONAL TIPO VARIANT

E O

A declarao de variveis opcional, se as variveis no forem declaradas o VBA faz a sua declarao por padro. Assim sempre que a instruo do Dim omitida para uma varivel, essa assume o tipo Variant.

Os Prs e Contras da Utilizao do tipo Variants

PRS Diminui o nmero de linhas de cdigo No necessrio estar preocupado se a varivel est ou no declarada porque o VBA automaticamente o faz

CONTRAS

Aumenta o tempo de execuo o VBA primeiro precisa reconhecer o tipo de dados com os quais est trabalhando. Este tipo de dados consome mais memria (uma vez que tem que alocar espao para qualquer tipo de dados que lhe seja atribudo) 16 bytes mais um byte por caractere se for String => problemas de performance para sub rotinas grandes. No possvel saber o tipo de dados que uma determinada varivel contm dificultando a deteco de erros.

______________________________________________________________________________________ ISCTE / DCTI 56 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Variveis Declarao ForadaPara que o VBA detecte um erro sempre que uma varivel no seja declarada dever fazer: Tools/Options Editor Tab Activar Require Variable Declaration

Ou ento, escrever no incio de cada mdulo Option Explicit Neste caso sempre que seja detectada uma varivel que ainda no foi declarada d uma mensagem de erro - Variable Not Defined

______________________________________________________________________________________ ISCTE / DCTI 57 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

VARIVEIS TIPOS DEFINIDOS USURIO - ESTRUTURASDefinio do TipoA primeira fase constituda pela definio do tipo: Type Dados_Pessoais Nome As String Idade As Integer DataNascimento As Date BI As Long End Type

PELO

Criou-se um tipo de dados que representa uma estrutura com dados de diferentes tipos. Esta definio dever ocorrer no incio do mdulo VBA. Onde: Type Dados_Pessoais Nome As String Idade As Integer DataNascimento As Date BI As Long End Type Palavra-Chave que indica a definio de um tipo de dados criado pelo usurio. Nome atribudo ao tipo de dados. Primeiro elemento da estrutura de dados definida. Segundo elemento da estrutura de dados definida. Terceiro elemento da estrutura de dados definida. Quarto elemento da estrutura de dados definida. Palavra-Chave que indica o fim da definio da estrutura de dados.

______________________________________________________________________________________ ISCTE / DCTI 58 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Utilizao das Estruturas de DadosComo utilizar as estruturas de dados: Sub Tipos_definidos_Usurio() Dim Pessoa As Dados_Pessoais Pessoa.Nome = Francisco Pessoa.DataNascimento = #8/7/73# Pessoa.Idade = WorksheetFunction.Year(Date)_ WorksheetFunction.Year(Pessoa.DataNascimento) Pessoa.BI = 103582915 MsgBox 5Pessoa.Nome & Chr(13) & , Idade & Pessoa.Idade & Chr(13) & _ , Data de Nascimento & Pessoa.DataNascimento & Chr(13) & _ , com o BI nmero _ & Pessoa.BI End Sub

5

Ver captulo InputBox e MsgBox ______________________________________________________________________________________ ISCTE / DCTI 59 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

VARIVEIS ARRAYSO que um Array ?Um Array uma varivel que representa um conjunto de variveis do mesmo tipo. Os Arrays podem ser multi-dimensionais, onde todas as dimenses so indexadas numericamente.

ARRAY UNI-DIMENSIONAL0 1 2 3 4 5 Um array uni-dimensional constitudo por uma nica lista de elementos indexveis. Esta lista tem um elemento inicial e um outro final sendo que a cada elemento da lista corresponde um nico ndice, traduo do lugar que ocupa na lista, que o identifica univocamente.

______________________________________________________________________________________ ISCTE / DCTI 60 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

ARRAY BI-DIMENSIONAL0 1 2 0 1 2 3

Um array bi-dimensional um pouco mais complexo e constitudo por um conjunto de listas do mesmo comprimento, este formato normalmente conhecido como array ou matriz. portanto constituda por linhas e colunas e cada elemento identificado por um ndice composto pela interseo dos nmeros da linha e da coluna.

______________________________________________________________________________________ ISCTE / DCTI 61 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Declarao de um array

ARRAYS UNI-DIMENSIONAISDim ArrayNumerico(10) As Integer Array_Numerico o nome da varivel array, o nmero entre parntesis indica o nmero de elementos que o array pode armazenar, isto Array_Numerico 0 1 2 3 4 5 6 7 8 9 Em que cada elemento do tipo Integer.

ARRAYS BI-DIMENSIONAISDim Tabela_Textual (5, 4 ) As String Tabela_textual o nome da varivel, os nmero entre parntesis indicam que a tabela ir ter 5 linhas e 4 colunas, podendo assim armazenar 20 elementos do tipo String. Tabela_Textual 0 1 2 0 1 2 3

______________________________________________________________________________________ ISCTE / DCTI 62 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Utilizao de um Array

PARA

ACESSAR AO ELEMENTO

([,, ])

ATRIBUIO

DE VALORES

([,, ]) =

Exemplo 1:Sub Pases() Dim Pases(3) As String Pases (0) = "Portugal" Pases(1) = "Brasil" Pases(2) = "Moambique" MsgBox "Pases Armazenados:" & Chr(13) & Pases(0) & Chr(13) & Pases(1) &_ Chr(13) & Pases(2) End Sub Aps a atribuio de valores a cada elemento do array, este passa a ter o contedo seguinte: Pases Portugal Brasil Moambique 0 1 2

Valor estes que sero exibidos atravs da MsgBox.

______________________________________________________________________________________ ISCTE / DCTI 63 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Exemplo 2:Option Base 1 Sub Utilizacao_Array() Dim Lotaria(3) As Integer Lotaria(1)=int(10000*Rnd()) Lotaria(2)=int(10000*Rnd()) Lotaria(3)=int(10000*Rnd()) MsgBox Nmeros da lotaria: & Lotaria(1) & , & Lotaria(2) & , &_ Lotaria(3) End Sub

Option Base e Array BoundsA indexao de um array por padro tem inicio em 0, sendo que os respectivos ndices vo de 0 a dimenso-1. Para alterar a base de indexao por forma a ter inicio em 1 basta colocar no inicio do mdulo Option Base 1 Contudo se pretender que um array comece em outro nmero que no seja 0 ou 1, necessrio especificar os limites inferior e superior do ndice quando da declarao do array.

Exemplo:Sub Utilizacao_Array() Dim Lotaria(4 To 5) As Integer Lotaria(4)=int(10000*Rnd()) Lotaria(5)=int(10000*Rnd()) MsgBox Nmeros da lotaria: & Lotaria(4) & , & Lotaria(5) End Sub

______________________________________________________________________________________ ISCTE / DCTI 64 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Constantes

______________________________________________________________________________________ ISCTE / DCTI 65 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

O

QUE SO CONSTANTES

?

Constantes so valores que no alteram durante a execuo de uma rotina. So declaradas da mesma forma que as variveis, a nica diferena reside no fato da atribuio ser feita na mesma instruo da declarao, e s poder ser feita uma nica vez. Const As = Const As =

______________________________________________________________________________________ ISCTE / DCTI 66 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Figura 23 Manuseamento de Constantes

______________________________________________________________________________________ ISCTE / DCTI 67 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

InputBox e MsgBox

O

QUE SO

?

Para haver interao entre o usurio e uma macro ou programa necessrio que exista um interface de comunicao. Este poder ser mais ou menos complexo e completo, contudo existem dois elementos bsicos para estabelecer esta ligao: InputBox e MsgBox.

______________________________________________________________________________________ ISCTE / DCTI 68 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) Neste contexto a InputBox uma funo que permite ao usurio introduzir dados no programa portanto um mecanismo de input. O MsgBox um mecanismo de Output e permite ao usurio visualizar os dados produzidos pelo programa.

INPUTBOXO que faz1. 2. 3.

Exibe na tela uma janela com uma caixa text box para a insero de dados. Espera que o usurio introduza os dados e/ou acione um dos botes. Como uma funo produz um valor final. Este consiste nos dados inseridos pelo usurio na forma textual - String.

SintaxeInputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])Numa primeira avaliao da funo, pode-se dizer que a mesma possui diversos parmetros, mas somente o primeiro obrigatrio, sendo que todos os outros quando ignorados assumem valores atribudos por padro.

ParmetrosParmetroPrompt (Obrigatrio )

ComentrioExpresso textual exibida como mensagem na janela de input. A dimenso mxima de 1024 caracteres. Se pretender construir uma mensagem com mais do que uma linha poder utilizar o caractere Enter - Chr(13). A juno dos elementos que constituem a mensagem realizada atravs do operador &.

______________________________________________________________________________________ ISCTE / DCTI 69 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) Exemplo: "A Soma de 3 com 5 :" & Chr(13) & " 8 " Title (Facultativo) Default (Facultativo) Titulo da janela de input. Se este for omitido, aparecer por padro o nome da aplicao. Expresso inserida por padro na caixa de insero de dados e constituir a resposta por padro se o usurio no introduzir outra. Se este parmetro for omitido aparecer uma text box vazia. Nmero que identifica a distncia horizontal entre o lado esquerdo da tela e a janela de input. Se este valor for omitido a janela aparecer centrada horizontalmente. Nmero que identifica a distncia vertical entre o lado superior da tela e a janela de input. Se este valor for omitido a janela ficar posicionada a 1/3 da parte inferior da tela Nome do arquivo de Help que ser utilizado para dar apoio ao preenchimento desta janela. Se for indicado este parmetro o seguinte obrigatrio. Nmero do ndice do tpico de Help constante no arquivo HelpFile, e que corresponde janela em questo.

Xpos (Facultativo) Ypos (Facultativo) HelpFile (Facultativo) Context (Facultativo)

Ateno: Se pretender enviar mais que um parmetro optativo respeite a ordem atravs de , (virgulas) Exemplo:

InputBox(Introduza o Nome da Aplicao:, , Excel)Na janela de input ser exibida a mensagem Introduza o Nome da Aplicao:, o ttulo da caixa ser o definido por padro e o valor na caixa de insero ser Excel.

MSGBOXO que faz1.

Exibe na tela uma janela com uma mensagem.

______________________________________________________________________________________ ISCTE / DCTI 70 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)2.

Espera que o usurio acione um dos botes.

3. Como uma funo produz um valor final. Devolve um nmero inteiro indicando o boto que foi clicado.

SintaxeMsgBox(prompt[, buttons] [, title] [, helpfile, context]) semelhana da InputBox , pode-se dizer que a mesma possui diversos parmetros, mas somente o primeiro obrigatrio, sendo que todos os outros quando ignorados assumem valores atribudos por padro.

ParmetrosParmetroPrompt (Obrigatrio )

ComentrioExpresso textual exibida como mensagem na janela de input. A dimenso mxima de 1024 caracteres. Se pretender construir uma mensagem com mais do que uma linha poder utilizar o caractere Enter Chr(13)

______________________________________________________________________________________ ISCTE / DCTI 71 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) Exemplo: "A Soma de 3 com 5 :" & Chr(13) & " 8 " Buttons (Facultativo) Title (Facultativo) HelpFile (Facultativo) Context (Facultativo) Nmero que identifica o tipo de botes que se pretende visualizar na janela de output. Ver tabela seguinte. Se for omitido assumir o valor 0 por padro. Titulo da janela de input. Se este for omitido, aparecer por padro o nome da aplicao. Nome do arquivo de Help que ser utilizado para dar apoio ao preenchimento desta janela. Se for indicado este parmetro o seguinte obrigatrio. Nmero do ndice do tpico de Help constante no arquivo HelpFile, e que corresponde janela em questo.

Ateno: Se pretender enviar mais que um parmetro optativo respeite a ordem atravs de , (virgulas) Exemplo:

MsgBox(Erro de Sintaxe !!!, , Mensagem de Erro)Na janela de output ser exibida a mensagem Erro de Sintaxe, o boto exibido ser o de OK (padro) e o titulo da janela ser Mensagem de Erro.

Constante de VBAVbOKOnly VbOKCancel VbAbortRetryIgnore VbYesNoCancel VbYesNo VbRetryCancel

Valor0 1 2 3 4 5

DescrioExibe somente o boto de OK. Exibe os botes OK e Cancel. Exibe os botes Abort, Retry, e Ignore. Exibe os botes Yes, No, e Cancel . Exibe os botes Yes e No. Exibe os botes Retry e Cancel.

______________________________________________________________________________________ ISCTE / DCTI 72 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) VbCritical VbQuestion VbExclamation VbInformation VbDefaultButton1 VbDefaultButton2 VbDefaultButton3 VbDefaultButton4 VbApplicationModal VbSystemModal 16 32 48 64 0 256 512 768 0 4096 Exibe o cone de Critical Message. Exibe o cone de Warning Query. Exibe o cone de Warning Message. Exibe o cone de Information Message. O primeiro boto o selecionado por padro. O segundo boto o selecionado por padro. O terceiro boto o selecionado por padro. O quarto boto o selecionado por padro. Application modal o usurio s poder dar continuidade ao trabalho depois de responder MsgBox na aplicao corrente. System modal - o usurio s poder dar continuidade ao trabalho depois de responder MsgBox em qualquer aplicao em curso no sistema.

Pela anlise desta tabela pode-se constatar que existem diferentes agrupamentos de cdigos: para definir o tipo de botes (0-5), para definir o tipo de cones (16,32,48,64), para definir o boto selecionado por padro (0,256,512,768) e para indicar o modo de execuo (0 e 4096). Pode-se adicionar os cdigos e assim fazer combinaes entre diversas opes destes 4 grupos, contudo nunca se deve adicionar mais do que um cdigo por agrupamento.

Exemplos:Para a instruo: MsgBox "Erro de Sintaxe!!!", 2 + 48 + 512 + 4096, "Mensagem de Erro" exibida a seguinte janela:______________________________________________________________________________________ ISCTE / DCTI 73 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Figura 24 MsgBox

Para a instruo: MsgBox "Erro de Sintaxe!!!", 5 + 64 + 256 + 4096, "Mensagem de Erro" exibida a seguinte janela:

Figura 25 MsgBox

Valores ProduzidosComo j foi referido, a funo MsgBox produz um valor em funo do boto acionado, assim produzir como output um dos valores constantes da tabela seguinte:

Constante de VBA

Valor

Boto Accionado

______________________________________________________________________________________ ISCTE / DCTI 74 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) vbOK vbCancel vbAbort vbRetry vbIgnore vbYes vbNo 1 2 3 4 5 6 7 OK Cancel Abort Retry Ignore Yes No

______________________________________________________________________________________ ISCTE / DCTI 75 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Domnio das variveis, constantes e rotinas

O

QUE O DOMINIO?

Scope ou domnio de um elemento refere-se rea na aplicao na qual esse mesmo elemento pode ser acessado e utilizado, ou seja onde que o elemento reconhecido.

______________________________________________________________________________________ ISCTE / DCTI 76 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

DOMNIO

DAS

VARIVEIS

Refere-se rea onde a varivel permanece ativa, mantendo o valor que lhe vai sendo atribudo. Existem 3 nveis de domnio para as variveis: mbito do Procedimento ou Procedimental mbito do Modulo ou Modular mbito do Projeto

As declaraes de tipos User Defined que so escritas no Declarations do mdulo tm domnio de Projeto.

mbito do ProcedimentoEstas variveis so declaradas no corpo do procedimento com recurso palavra chave Dim. So variveis criadas quando da execuo do procedimento e automaticamente destrudas quando o procedimento termina, sendo que s so reconhecidas durante o______________________________________________________________________________________ ISCTE / DCTI 77 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) procedimento que as declarou. Assim sendo, qualquer tentativa realizada por um procedimento no sentido de trabalhar com variveis definidas no corpo de um outro procedimento no ter xito.

ExemploSub Ambito_Procedimento() Dim Var_1 As Integer Var_1 = InputBox (Introduza um nmero Inteiro) MsgBox Foi este o nmero que introduziu: & Var_1 Ambito_Procedimento_2 End Sub Sub Ambito_Procedimento_2() MsgBox Foi este o nmero que introduziu: & Var_1 End Sub

A execuo destas rotinas resultar no seguinte: A primeira mensagem (MsgBox no Procedimento Ambito_Procedimento)exibir o nmero introduzido pelo usurio que fora armazenado na varivel Var_1. A segunda mensagem, oriunda da instruo MsgBox do Procedimento Ambito_Procedimento_2 no exibir o valor introduzido, porque a varivel Var_1 nele referida considerada como uma varivel interna do processo, e portanto criada dentro do procedimento Ambito_Procedimento_2. Como tal, embora possua o mesmo nome que a varivel da rotina Ambito_Procedimento_1 no existe qualquer ligao entre elas, so duas variveis distintas. Neste caso se pretendesse que o valor da primeira varivel fosse reconhecido no procedimento chamado, teria de o passar por parmetro.

mbito do MduloUma varivel como mbito do Mdulo pode ser acessada por todas as rotinas existentes no mdulo onde a varivel declarada. Para declarar uma varivel do nvel modular, dever declar-la com a palavra chave Dim na seo Declarations do respectivo mdulo VBA. Neste caso qualquer varivel a declarada ser considerada de nvel modular por padro, para tornar esse fato mais explicito poder-se utilizar a palavra chave Private na declarao:______________________________________________________________________________________ ISCTE / DCTI 78 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) Ex: Private Var_2 As String

Exemplo:Dim Var_1 As Integer ou Private Var_1 As Integer Sub Ambito_Modulo() Var_1 = InputBox (Introduza um nmero Inteiro) Ambito_Modulo_2 End Sub Sub Ambito_Modulo_2() MsgBox Foi este o nmero que introduziu: & Var_1 End Sub Neste caso como a varivel reconhecida no mdulo por qualquer rotina nele existente, desde que seja referida sempre pelo mesmo nome, o procedimento Ambito_Modulo_2 ir exibir o valor introduzido pelo usurio no procedimento Ambito_Modulo.

mbito do ProjetoAs variveis assim definidas tm o mbito correspondente a todo o projeto, isto podem ser acessadas e alteradas em todos e quaisquer mdulos. Para declarar variveis deste tipo deve-se faz-lo na seo Declarations de qualquer mdulo, para tal dever utilizar a palavra chave Public.

______________________________________________________________________________________ ISCTE / DCTI 79 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Exemplo:No Module_1 tem-se: Public Var_1 As Integer Sub Ambito_Projecto() Var_1 = InputBox (Introduza um nmero Inteiro) Ambito_Projecto _2 End Sub No Module_2 tem-se: Sub Ambito_Projecto _2() MsgBox Foi este o nmero que introduziu: & Var_1 End Sub A execuo do procedimento Ambito_Projecto no Module_1 mandou executar o procedimento Ambito_Projecto do Module_2, e o valor atribudo varivel Var_1 foi acedido posteriormente noutro procedimento de outro mdulo, dado tratar-se de uma varivel global.

DOMNIO

DAS

CONSTANTES

semelhana das variveis tambm as constantes tm 3 nveis de domnio: mbito do Procedimento ou Procedimental mbito do Modulo ou Modular mbito do Projeto

______________________________________________________________________________________ ISCTE / DCTI 80 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

mbito do ProcedimentoEstas constantes so declaradas no corpo do procedimento com recurso palavra chave Const. S tm existncia dentro do procedimento onde so declaradas.

Exemplo:Sub Ambito_Procedimento() Const Taxa_Desc As Single = 0.05 Dim Desconto As Double Desconto = InputBox (Introduza o montante das Compras) * Taxa_Desc MsgBox O desconto de : & Desconto End Sub

mbito do MduloUma constante com mbito do Mdulo pode ser utilizada por todas as rotinas existentes no mdulo onde definida. Para declarar uma constante a nvel modular, dever declar-la com a palavra chave Const na seco Declarations do respectivo mdulo VBA. Neste caso qualquer______________________________________________________________________________________ ISCTE / DCTI 81 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft) constante a declarada ser considerada de nvel modular por padro, para tornar esse fato mais explicito poder-se utilizar a palavra chave Private na declarao: Ex: Private Const Const_1 As String

mbito do ProjetoAs constantes assim definidas tm o mbito correspondente a todo o projeto, isto podem ser utilizadas em todo e qualquer mdulo. Para definir constantes deste tipo dever faz-lo na seo Declarations de qualquer mdulo, para tal dever utilizar a palavra chave Public. Ex: Public Const Const_1 As String

DOMNIO

DE

SUB ROTINAS

E

FUNES

Estas s tm dois nveis de Scope: o nvel do projeto e o nvel do mdulo. Por padro as rotinas so de mbito do projeto sem qualquer indicao adicional. Contudo poder tornar este fato mais explicito pela introduo da Palavra-Chave Public, que em termos operacionais no adicionar qualquer funcionalidade, mas em termos de leitura dar uma maior percepo.______________________________________________________________________________________ ISCTE / DCTI 82 / 132 [email protected]

Excel Macros e Visual Basic for Applications(verso Draft)

Exemplo:Public Sub Ambito_Procedimento() Const Taxa_Desc As Single = 0.05 Dim Desconto As Double Desconto = InputBox (Introduza o montante das Compras) * Taxa_Desc MsgBox O desconto de : & Desconto End S