Excel VBA

Tecnun Tecnologia em Informtica www.tecnun.com.br

Todos os direitos reservados. Nenhuma parte desta publicao poder ser reproduzida, guardada por algum sistema de recuperao ou transmitida de qualquer modo ou por qualquer outro meio, seja este eletrnico, mecnico, de fotocpia, de gravao, ou outros, sem prvia autorizao, por escrito, da F.A. Consultoria e Treinamento.

Responsvel: Henrique Matias Redao e organizao: Henrique Matias

ndiceO ambiente de desenvolvimento VBA .......................................................................1 Como acessar? ......................................................................................................1 A janela de Propriedades .......................................................................................3 Personalizando o ambiente ....................................................................................5 Programao orientada a eventos .............................................................................6 O que um objeto ..................................................................................................8 O que um mtodo ...............................................................................................8 O que uma propriedade ......................................................................................9 O que so eventos .................................................................................................9 Principais Objetos ....................................................................................................10 WorkBook.............................................................................................................10 WorkSheet ...........................................................................................................11 Principais Propriedades ...........................................................................................12 ActiveCell .............................................................................................................12 ActiveSheet ..........................................................................................................12 ActiveWorkBook ...................................................................................................13 Cells .....................................................................................................................13 FormulaR1C1 .......................................................................................................14 Value ....................................................................................................................14 Principais Mtodos ..................................................................................................15 Activate ................................................................................................................15 Clear.....................................................................................................................15 Open ....................................................................................................................16 Close ....................................................................................................................16 Save .....................................................................................................................17 SaveAs .................................................................................................................17 Quit ......................................................................................................................18 InputBox ...............................................................................................................19 Principais Eventos ...................................................................................................20 Eventos da pasta de trabalho...............................................................................20 Eventos de Planilhas ............................................................................................21 Funes do VBA ......................................................................................................22 Funes de Datas ................................................................................................22 Funes de texto ..................................................................................................23 Funes de validao ..........................................................................................24 Isdate ...................................................................................................................24 Isnumeric..............................................................................................................24 Funes de Converses ......................................................................................25 Msgbox.................................................................................................................26 Variveis de memria ..............................................................................................27

Definindo uma varivel .........................................................................................27 Declarando uma varivel......................................................................................27 Tipos de variveis ................................................................................................28 Controle de fluxo de execuo ................................................................................29 Instruo IF ..........................................................................................................29 Instruo Select Case ..........................................................................................30 Instruo Do While ...............................................................................................31 For Next ...............................................................................................................31 Criao/desenvolvimento de projetos ......................................................................33 Definindo necessidades .......................................................................................33 Montagem da planilha ..........................................................................................33 Criao da Macro .................................................................................................33 Executando uma aplicao ......................................................................................34 Executando utilizando o F5 ..................................................................................34 Executando com o F8 ..........................................................................................34 Depurar uma macro .............................................................................................34 Tratamento de Erros ................................................................................................35 Objeto Err. ............................................................................................................36 Variveis de memria ..............................................................................................37 Definindo uma varivel .........................................................................................37 Declarando uma varivel......................................................................................37 Tipos de variveis ................................................................................................38 Funes do VBA ......................................................................................................39 Funes de Datas ................................................................................................39 Funes de texto ..................................................................................................40 Funes de validao ..............................................................................................41 Isdate ...................................................................................................................41 Isnumeric..............................................................................................................41 Funes de Converses ..........................................................................................42 Msgbox.................................................................................................................43 Tcnicas avanadas de programao .....................................................................44 Array Unidimensional ...........................................................................................44 Array multidimensional .........................................................................................45 Redefinindo Arrays ...............................................................................................46 Navegando por Arrays .........................................................................................46 Constantes ...........................................................................................................46 Colees ..............................................................................................................47 Colees do Excel ...............................................................................................48 Navegando por colees......................................................................................48 Subrotinas................................................................................................................50 Criando subrotinas ...............................................................................................50

Passando parmetros ..........................................................................................50 A palavra reservada Call ......................................................................................50 O que uma funo? ...........................................................................................52 Diferena entre Subrotina e Funo ....................................................................52 Tratamento de Erros ................................................................................................53 Objeto Err ................................................................................................................54 Criando formulrios personalizados ........................................................................55 Alterando as propriedades do formulrio .................................................................57 Inserindo controles nos formulrios .........................................................................58 Entrando cdigo no formulrio .................................................................................60 Determinando a navegabilidade dos registros .........................................................61 Definindo as operaes bsicas ..............................................................................62 Boto Novo ..........................................................................................................62 Boto Salvar.........................................................................................................64 Boto excluir ........................................................................................................66 Boto Editar .........................................................................................................67 O boto Pesquisar ...............................................................................................68 Executando uma macro com hora marcada ............................................................69 Desabilitando o salvar e o imprimir de uma Pasta ...................................................70 Ferramenta de anlise com grficos ........................................................................71

O ambiente de desenvolvimento VBATodo o pacote Microsoft Office possui um ambiente de desenvolvimento VBA Visual Basic for Aplication para a edio ou criao de macros. Conhecer esse ambiente muito importante pois ele possui inmeros recursos que agilizaro o seu trabalho na criao e manuteno de macros.

Como acessar?Voc pode acessar esse ambiente de duas maneiras: 1. Clique no menu Ferramentas, Macros, Editor do Visual Basic... Clique no menu Ferramentas, Macros, comando Macros. Selecione a macro que desejar e clique no boto Editar

2.

FA CONSULTORIA E TREINAMENTO

1

Independente da maneira que voc acesse, a tela que ir aparecer ser a seguinte

Muito bem, vamos conhecer as particularidades dessa janela. Sempre que desejamos trabalhar com macros recomendado utilizar o gravador. Ai voc me pergunta: Porque ento terei que utilizar o Editor? A resposta e bem simples! O gravador no permite a edio das macros, portanto qualquer mudana que voc deseje tem que ser feita pelo o editor. Ento terei que saber programar? De certo modo sim. O gravador faz muita coisa a nosso favor s que tambm fica devendo em muitas outras por exemplo, o gravador no trabalha com a parte lgica da macro! difcil No, no . O ambiente possui vrios recursos que ajudam vocs na hora da programao. Veremos esses recursos mais adiante nesta apostila

FA CONSULTORIA E TREINAMENTO

2

A Project Explorer Quando estamos trabalhando com o Editor (Lembrem-se! O Editor nosso ambiente de desenvolvimento) o Excel determina que todas as pastas abertas sero um projeto. Portanto a Project Explorer exibe uma lista hierrquica dos projetos e de todos os itens contidos e referenciados em cada um dos projetos. Observe que o nome do projeto o mesmo da pasta de trabalho. Os demais itens dessa janela sero discutidos no momento adequado.

A janela de PropriedadesEssa janela exibe as propriedades (caractersticas) de algum item que voc selecionar na Project Explorer. As propriedades sero discutidas no capitulo 2.

FA CONSULTORIA E TREINAMENTO

3

A Code Window A Code Window a rea em voc desenvolver as suas macros, ou seja, todo a programao feita nessa janela. Ela possui duas opes principais Objetos : onde voc seleciona o objeto do projeto que voc deseja programar Procedimentos: o local o qual voc escolhe o procedimento (esse o nome que a macro recebe enquanto voc desenvolve) que deseja programar!

FA CONSULTORIA E TREINAMENTO

4

Personalizando o ambienteVoc pode modificar o posicionamento de todas as janelas citadas. Para isso clique sobre o ttulo da janela e arraste para a nova posio Caso voc no esteja visualizando alguma janela citada clique no menu Exibir e selecione a janela que no est aparecendo. Podemos configurar tambm vrias outras opes do ambiente atravs do menu Ferramentas , Opes. Estudaremos essas configuraes quando necessrio

FA CONSULTORIA E TREINAMENTO

5

Programao orientada a eventosO que a programao orientada a eventos? Para entender esse conceito vamos analisar uma situao. Imaginem dois objetos quaisquer, por exemplo uma caneta e um monitor. Agora pensem nas caratersticas desses objetos. Pense no que eles podem fazer. Pense no que voc pode fazer com eles. A caneta e o monitor so dois objetos. Porm tem caractersticas diferentes, fazem coisas diferentes e voc utiliza cada um de uma maneira diferente. Vamos analisar os dois objetos: Objeto: caneta Caractersticas: Cor: Azul Tamanho: Pequeno O que ela faz? Escreve O que voc pode fazer com ela? Escrever Objeto: monitor Caractersticas: Cor: Preto Tamanho: 17 O que ele faz? Exibe imagens O que voc pode fazer com ele? Ver imagens. Caso crie uma hipottica para mudar a cor da caneta o cdigo ser o seguinte:

FA CONSULTORIA E TREINAMENTO

6

Observe que sintaxe utilizado foi a seguinte: Objeto.Caracteristica = Novo Valor Agora vamos criar uma hipottica macro que utiliza a caneta para escrever um texto. Observe que a sintaxe bem semelhante a macro anterior.

Para finalizar vamos criar uma hipottica macro que muda a cor da caneta na hora em que a caneta comear a escrever.

FA CONSULTORIA E TREINAMENTO

7

Analisando os exemplos mencionados podemos entender como funciona a programao orientadas a eventos no Excel. Tudo que possa ter uma representao no Excel chamado de Objeto. A pasta de trabalho, as planilhas, as clulas ativas etc. Todos esses objetos possuem caractersticas que so definidas de acordo com o tipo do objeto. Essas caractersticas so chamadas de propriedades. Nome, tamanho e cor so exemplos de propriedades. Todos os objetos podem executar algumas aes. Essas aes so chamadas de mtodos. Salvar, Imprimir so exemplos de mtodos. Quando desenvolvemos macros, podemos querer que seja executada algum procedimento em um momento especfico. Posso querer exibir uma mensagem na hora em que a pasta de trabalho aberta. Esses momentos que ocorrem chamamos de Eventos. Portanto a programao orientada a eventos permite que executemos algumas tarefas quando disparado um evento pelo sistema ou usurio.

O que um objeto tudo que pode ter uma representao no Excel. Exemplos de Objetos: WorkBook (pasta de trabalho) WorkSheet (planilha)

O que um mtodoUm mtodo uma ao que o objeto pode executar: Eles sempre sero invocados a partir do nome do objeto. Exemplos de Mtodos: Workbook.Save (salva a pasta de trabalho) ActiveSheet.Print (imprime a planilha ativa)

FA CONSULTORIA E TREINAMENTO

8

O que uma propriedadeUma propriedade uma caracterstica do objeto. As propriedades sempre sero invocadas atravs do nome do objeto. Voc pode atribuir um valor uma propriedade ou simplesmente ler o valor atual de uma determinada propriedade de um objeto. Exemplo de propriedades: ActiveSheet.Name = Folha de pagamento MsgBox ActiveSheet.Name

O que so eventosSo momentos que ocorrem com um objeto nos quais podemos executar algum cdigo. Exemplo de eventos: WoorkBook_Open() WorkSheet_Click()

FA CONSULTORIA E TREINAMENTO

9

Principais ObjetosNeste capitulo vamos conhecer dois dos principais objetos do Excel:

WorkBookWorkBook um objeto que representa a Pasta de Trabalho. Ele pertence a uma coleo chamada WorkBooks. Qualquer ao que voc queira fazer com a pasta de trabalho ser atravs desse objeto. Eles so acessados atravs de uma coleo. A macro do exemplo salva a pasta de trabalho chamada Book1

FA CONSULTORIA E TREINAMENTO

10

WorkSheet um objeto que representa uma planilha. Ele pertence a uma coleo chamada WorkSheets. Qualquer ao que voc queira fazer com a pasta de trabalho ser atravs desse objeto. Eles so acessados atravs de uma coleo. A macro do exemplo renomeia a Sheet1 para Curso de Macros

FA CONSULTORIA E TREINAMENTO

11

Principais PropriedadesO Excel possui vrias propriedades que so importantes para desenvolver macros eficientes e produtivas. Veremos algumas das principais.

ActiveCellEsta propriedade se refere a clula que est ativa na planilha. A macro a seguir altera ao valor da clula ativa de uma planilha.

ActiveSheetEsta propriedade se refere a planilha que est ativa no momento. A macro a seguir altera o nome de uma ativa.

FA CONSULTORIA E TREINAMENTO

12

ActiveWorkBookEsta propriedade se refere a pasta de trabalho que est ativa no momento. A macro a seguir altera o nome de uma pasta de trabalho ativa.

CellsEssa propriedade permite trabalhar com clulas atravs da notao linha e coluna. A macro do exemplo a seguir altera o valor da clula que se encontra na linha 1 e na coluna 2, ou seja, a clula B1.

FA CONSULTORIA E TREINAMENTO

13

FormulaR1C1Essa propriedade permite que voc trabalhe com formulas na notao Linha e coluna. A macro a seguir insere a funo Soma na clula A3 somando as clulas A1 e A2.

ValueEssa propriedade altera o valor de uma clula. como se voc estivesse digitando. A macro a seguir altera o contedo da clula A1.

FA CONSULTORIA E TREINAMENTO

14

Principais MtodosAssim como as propriedades temos tambm mtodos importantes que se aplicam aos objetos. Vamos ver alguns dos principais.

ActivateEle muito utilizado para ativar uma planilha ou uma pasta de trabalho. A macro a seguir ativa a planilha com nome de Sheet1.

ClearEssa propriedade limpa o valor de uma clula ou seleo. A macro a seguir limpa o contedo da clula A1.

FA CONSULTORIA E TREINAMENTO

15

OpenEssa propriedade est diretamente ligada ao objeto WorkBooks. Ela serve para que voc abra outras pastas de trabalho durante a execuo da sua Macro. A macro a seguir abre uma pasta de trabalho chamada Teste.xls.

CloseEssa propriedade est diretamente ligada ao objeto WorkBooks. Ela serve para que voc feche outras pastas de trabalho durante a execuo da sua Macro. A macro a seguir fecha a pasta de trabalho chamada Teste.xls.

FA CONSULTORIA E TREINAMENTO

16

SaveEsse mtodo salva uma pasta de trabalho. Ele equivalente ao comando Salvar do menu Arquivo. A macro a seguir salva a pasta de trabalho Teste.xls

SaveAsEsse mtodo salva uma pasta de trabalho permitindo que voc fornea tambm um nome. Ele equivalente ao comando Salvar Como do menu Arquivo.

FA CONSULTORIA E TREINAMENTO

17

QuitEssa propriedade fecha o Excel. Ela est associado ao objeto Application. Esse objeto o responsvel pelo controle das funes do Excel. A macro a Seguir fecha o Excel. Esse comando equivalente a se clicar no X da janela do Excel.

Find Esse mtodo localiza valores na planilha e permite que voc trabalhe com esses valores. A macro seguinte localiza todas as clulas do intervalo A1:A500 na planilha um que contm o valor 2 e o altera para 5. No se preocupe muito com os demais comandos. Eles sero vistos mais adiante.

FA CONSULTORIA E TREINAMENTO

18

InputBoxO mtodo InputBox exibe uma caixa de dilogo para que o usurio entre com alguma informao. Essa informao pode ser utilizada na macros. A macro a seguir salva um arquivo solicitando o nome ao usurio!

FA CONSULTORIA E TREINAMENTO

19

Principais EventosEventos o pilar da programao no VBA. Vamos conhecer dois desses eventos.

Eventos da pasta de trabalhoSo os eventos relativos a pasta de trabalho. Podemos inserir algum cdigo, por exemplo, na hora em que o arquivo for aberto. Na Project Explorer localize o seu projeto (Lembre-se que o seu projeto tem o nome da sua pasta de trabalho) e expanda caso necessrio. D um duplo clique no item Esta Pasta de trabalho. A code window deve aparecer. Na code windows selecione o objeto WorkBook . Depois escolha o procedimento relativo ao evento que voc deseja trabalhar.

Agora s digitar o cdigo que deseja. A macro seguinte exibe uma saudao de bom dia ao usurio no momento em que o arquivo aberto.

FA CONSULTORIA E TREINAMENTO

20

Eventos de PlanilhasSo os eventos relativos a planilha. Podemos inserir algum cdigo, por exemplo, na hora em que o usurio estiver digitando podemos verificar qual a clula que est sendo utilizada e tomar alguma providncia. Na Project Explorer localize o seu projeto (Lembre-se que o seu projeto tem o nome da sua pasta de trabalho) e expanda caso necessrio. D um duplo clique na planilha desejada. A code window deve aparecer. Na code windows selecione o objeto Worksheet . Depois escolha o procedimento relativo ao evento que voc deseja trabalhar.

Pronto agora s digitar cdigo que deseja. A macro seguinte verifica a clula que esta sendo selecionada e exibe uma mensagem..

FA CONSULTORIA E TREINAMENTO

21

Funes do VBA

O VBA possui diversas funes que auxiliam no desenvolvimento. Vejamos as principais

Funes de Datas As datas e horas so armazenadas internamente como nmeros. Isto permite que voc faa clculos com as datas, tais como adicionando dias ao dia de hoje para ter uma nova data num futuro. O Visual Basic prove funes para retorno da data e hora atual, e para gerar datas baseado em strings ou outra expresso. Funo Date Time Now Hour, Minute, Second Day, Month, Year DataDiff Retorna a data atual Retorna a hora atual Retorna a data e hora atual Retornam Hora, Minuto e Segundo atual Retornam Dia, Ms e Ano de uma data qualquer Retorna a diferena entre duas datas Descrio

A macro a seguir testa vrias funes de datas

FA CONSULTORIA E TREINAMENTO

22

Funes de textoQuando precisamos manipular strings o Visual Basic traz algumas funes para trabalharmos com elas. Funo Left (strText, iCont) Right(strText, iCont) Mid( strText, iStart[,iCont]) UCase(strText) LCase(strText) Len(strText) Descrio Retorna iCont letras de strText a partir da esquerda Retorna iCont letras de strText a partir da direita Retorna iCont letras de strText a partir da posio iStart Retorna strText em maisculas Retorna strText em minsculas Retorna o tamanho de strText

A macro a seguir testa vrias funes de string.

FA CONSULTORIA E TREINAMENTO

23

Funes de validaoSo funes que validam dados. Veremos as duas mais importantes

IsdateVerifica se um valor uma data valida. A rotina a seguir solicita uma entrada do usurio e verifica se ele digitou uma data. Esta funo retorna True ou False.

IsnumericVerifica se um valor um nmero. A rotina a seguir solicita uma entrada ao usurio e verifica se ele digitou um nmero.

FA CONSULTORIA E TREINAMENTO

24

Funes de ConversesQuando voc se torna mais interessado com o desempenho do seu programa voc declara variveis de tipos diferentes, voc precisar de converter variveis entre diferentes tipos de dados dependendo do que voc est tentando calcular ou processar e o que precisa exibir. Visual Basic executa algumas converses automaticamente. Por exemplo, se a String 1234 colocada numa varivel do tipo Integer, Visual Basic automaticamente converte para integer a string. Outro exemplo, quando uma string 100 adicionada a um valor numrico usando a formula 100 + 10, o Visual Basic converter a string para nmero para executar a formula. Existem alguns casos, entretanto, que o Visual Basic poder gerar resultados estranhos. Por exemplo, se duas strings so adicionadas, o Visual Basic ir concatenar as strings (100 + 10 = 10010). Voc pode evitar bugs em seu programa convertendo explicitamente, usando as funes de converso, antes de us-las. Tambm quando os valores so convertidos explicitamente, o seu programa executado mais rapidamente porque voc minimiza o trabalho extra do Visual Basic. Funo CBool CCur CDbl CLng CStr Converte para Boolean Currency Double Long String Funo CByte CDate CInt CSng CVar Converte para Byte Date Integer Single Variant

FA CONSULTORIA E TREINAMENTO

25

MsgboxEssa funo exibe uma mensagem ao usurio. A macro a seguir exibe uma mensagem perguntando ao usurio se ele deseja realmente executar a macro.

FA CONSULTORIA E TREINAMENTO

26

Variveis de memria Definindo uma varivelUma varivel um local de armazenamento de dados, identificada por um nome. Ela pode ter seu contedo alterado durante a execuo de seu programa. Contedo de uma varivel pode ser texto, data, nmeros, valores lgicos (TRUE ou FALSE), objetos, etc.

Declarando uma varivelPara declarar uma varivel devemos definir o tipo e utilizar a palavra reservada Dim. A macro a seguir declara algumas variveis

FA CONSULTORIA E TREINAMENTO

27

Tipos de variveisVejamos os tipos de variveis mais utilizados no VBA. Tipo Byte Boolean Integer Long Single Double Currency Date Object String Variant Descrio inteiro simples valor TRUE ou FALSE inteiro curto inteiro longo preciso simples preciso dupla monetrio data referncia a um objeto cadeia de caracteres qualquer tipo de varivel Exemplo 5 TRUE 528 7430180 93.16 492.028433 $ 735.16 10/01/95 frmLogSys Operao OK Qualquer valor Tamanho 1 byte 2 bytes 2 bytes 4 bytes 4 bytes 8 bytes 8 bytes 8 bytes 4 bytes 10 bytes + string 22 bytes + tipo de dado

FA CONSULTORIA E TREINAMENTO

28

Controle de fluxo de execuo Instruo IFEstruturas de Seleo permitem a execuo de um bloco de cdigo de acordo com o resultado de um teste lgico (comparao). If...then...else Sintaxe: If teste lgico Then [comandos...] [ElseIf teste lgico Then [comandos...] ] [Else [comandos...] ] End If A macro a seguir testa uma resposta fornecida pelo usurio.

FA CONSULTORIA E TREINAMENTO

29

Instruo Select CaseTem a mesma funo do IF s que mais fcil de desenvolver e entender. Select Case expresso Case valor expresso1 [comandos...] [Case valor expresso2 [comandos...] ] [Case Else [comandos...] ] End Select A macro a seguir testa uma resposta fornecida pelo usurio.

FA CONSULTORIA E TREINAMENTO

30

Instruo Do WhileEstrutura de repetio permite a execuo de um bloco de cdigo repetidas vezes de acordo com o critrio (teste lgico) estabelecido. Do [ { While | Until } condio] [comandos...] [Exit Do] [comandos...] Loop A macro a seguir vai formatando as clulas enquanto o valor da clula ativa for diferente de vazio.

For NextExecuta um bloco de comando com um nmero limite especificado. Sintaxe: For contador = valor inicial To valor final [Step incremento] [comandos...] [Exit For] [comandos...] Next [contador]

FA CONSULTORIA E TREINAMENTO

31

A macro a seguir formata 10 clulas iniciando pela A1

FA CONSULTORIA E TREINAMENTO

32

Criao/desenvolvimento de projetos Definindo necessidadesEste deve ser o passo do projeto que mais deve-se tomar cuidado. Erros durante o levantamento de necessidades podem fazer com que voc crie o que o usurio no precisa e deixe de fazer o que ele realmente precisa. Antes de iniciar a construo das macros tenha certeza que o Excel no possui nenhuma funo ou ferramenta que execute a tarefa necessria. Caso o Excel no possua ou voc necessite de uma automao sofisticada j pense na macro.

Nunca se esquea: oua o usurio.

Montagem da planilhaMantenha a planilha com um layout bsico para o bom funcionamento da macro. Evite principalmente mesclagens pois o Excel no trabalha bem com esse recurso. Elabore sua planilha pensando exclusivamente na lgica da macro.

Criao da MacroFaa tudo o que for possvel utilizando o gravador de macros. Isso far com que voc dedique seu tempo exclusivamente a lgica da macro.

FA CONSULTORIA E TREINAMENTO

33

Executando uma aplicao Executando utilizando o F5Inicia a aplicao processando toda a macro

Executando com o F8Executa a macro linha por linha a cada toque no F8. muito til para entendermos o que est acontecendo com a macro.

Depurar uma macroDepurar uma macro significa acompanhar e controlar o fluxo de execuo da macro. O Edito do VBA possui vrios recursos para essa finalidade. Breakpoint um local no cdigo onde o Visual Basic para a execuo do programa e entra em break mode. Via teclado, pressione a tecla F9. Exibe o valor atual de uma expresso sobre o cursor ou que esteja selecionada. Lista todas as chamadas de funes durante a execuo da aplicao. Executa passo a passo o contedo de uma procedure, Via teclado, pressione a tecla F8. Roda o prximo cdigo executvel, passando atravs de cada linha de cdigo que se segue. Se o cdigo chamar uma procedure definida pelo usurio, esta procedure ser executa diretamente. Inicia a sua aplicao Entra em Break Mode Termina a sua aplicao

Instant Watch Calls Step Into Step Over

Start Break End

FA CONSULTORIA E TREINAMENTO

34

Tratamento de ErrosExistem 3 tipos de erros em macros: 1. 2. 3. Sintaxe: so erros provocados por erros de digitao de comandos Run-time: so erros durante a execuo do programa Lgicos: so erros causados por lgicas incorretas

O VBA possui uma estrutura lgica para esses tratamentos de erros. A macro a seguir utiliza essa estrutura

Em qualquer ponto que ocorrer o erro o programa salta automaticamente para o Label Erro, que o responsvel pelo tratamento desse Erro:

FA CONSULTORIA E TREINAMENTO

35

Objeto Err.Atravs desse objetos podemos verificar o tipo de erro que ocorreu durante o tratamento do mesmo. Isso facilita o trabalho e permite correes. A macro a seguir alm identifica o erro atravs do objeto Err.

FA CONSULTORIA E TREINAMENTO

36

Variveis de memria Definindo uma varivelUma varivel um local de armazenamento de dados, identificada por um nome. Ela pode ter seu contedo alterado durante a execuo de seu programa. Contedo de uma varivel pode ser texto, data, nmeros, valores lgicos (TRUE ou FALSE), objetos, etc.

Declarando uma varivelPara declarar uma varivel devemos definir o tipo e utilizar a palavra reservada Dim. A macro a seguir declara algumas variveis

FA CONSULTORIA E TREINAMENTO

37

Tipos de variveisVejamos os tipos de variveis mais utilizados no VBA. Tipo Byte Boolean Integer Long Single Double Currency Date Object String Variant Descrio inteiro simples valor TRUE ou FALSE inteiro curto inteiro longo preciso simples preciso dupla Monetrio Data referncia a um objeto cadeia de caracteres qualquer tipo de varivel Exemplo 5 TRUE 528 7430180 93.16 492.028433 $ 735.16 10/01/95 frmLogSys Operao OK Qualquer valor Tamanho 1 byte 2 bytes 2 bytes 4 bytes 4 bytes 8 bytes 8 bytes 8 bytes 4 bytes 10 bytes + string 22 bytes + tipo de dado

FA CONSULTORIA E TREINAMENTO

38

Funes do VBAO VBA possui diversas funes que auxiliam no desenvolvimento. Vejamos as principais.

Funes de DatasAs datas e horas so armazenadas internamente como nmeros. Isto permite que voc faa clculos com as datas, tais como adicionando dias ao dia de hoje para ter uma nova data num futuro. O Visual Basic prove funes para retorno da data e hora atual, e para gerar datas baseado em strings ou outra expresso. A macro a seguir testa vrias funes de datas:

Funo Date Time Now Hour, Minute, Second Day, Month, Year DataDiff Retorna a data atual Retorna a hora atual

Descrio

Retorna a data e hora atual Retornam Hora, Minuto e Segundo atual Retornam Dia, Ms e Ano de uma data qualquer Retorna a diferena entre duas datas

FA CONSULTORIA E TREINAMENTO

39

Funes de textoQuando precisamos manipular strings o Visual Basic traz algumas funes para trabalharmos com elas. Funo Left (strText, iCont) Right(strText, iCont) Mid( strText, iStart[,iCont]) UCase(strText) LCase(strText) Len(strText) Descrio Retorna iCont letras de strText a partir da esquerda Retorna iCont letras de strText a partir da direita Retorna iCont letras de strText a partir da posio iStart Retorna strText em maisculas Retorna strText em minsculas Retorna o tamanho de strText

A macro a seguir testa vrias funes de string:

FA CONSULTORIA E TREINAMENTO

40

Funes de validaoSo funes que validam dados. Veremos as duas mais importantes:

IsdateVerifica se um valor uma data valida. A rotina a seguir solicita uma entrada do usurio e verifica se ele digitou uma data. Esta funo retorna True ou False.

IsnumericVerifica se um valor um nmero. A rotina a seguir solicita uma entrada ao usurio e verifica se ele digitou um nmero.

FA CONSULTORIA E TREINAMENTO

41

Funes de ConversesQuando voc se torna mais interessado com o desempenho do seu programa voc declara variveis de tipos diferentes, voc precisar de converter variveis entre diferentes tipos de dados dependendo do que voc est tentando calcular ou processar e o que precisa exibir. Visual Basic executa algumas converses automaticamente. Por exemplo, se a String 1234 colocada numa varivel do tipo Integer, Visual Basic automaticamente converte para integer a string. Outro exemplo, quando uma string 100 adicionada a um valor numrico usando a formula 100 + 10, o Visual Basic converter a string para nmero para executar a formula. Existem alguns casos, entretanto, que o Visual Basic poder gerar resultados estranhos. Por exemplo, se duas strings so adicionadas, o Visual Basic ir concatenar as strings (100 + 10 = 10010). Voc pode evitar bugs em seu programa convertendo explicitamente, usando as funes de converso, antes de us-las. Tambm quando os valores so convertidos explicitamente, o seu programa executado mais rapidamente porque voc minimiza o trabalho extra do Visual Basic. Funo CBool CCur CDbl CLng CStr Converte para Boolean Currency Double Long String Funo CByte CDate CInt CSng CVar Converte para Byte Date Integer Single Variant

FA CONSULTORIA E TREINAMENTO

42

MsgboxEssa funo exibe uma mensagem ao usurio. A macro a seguir exibe uma mensagem perguntando ao usurio se ele deseja realmente executar a macro.

FA CONSULTORIA E TREINAMENTO

43

Tcnicas avanadas de programaoArrays so matrizes, ou variveis indexadas, em Visual Basic, declaradas de forma semelhante a outras variveis. Arrays podem ser de qualquer tipo, inclusive tipos de dados definidos pelo usurio. Tambm podem ser estticos, locais ou Pblicas.

Array UnidimensionalE o Array que constitudo apenas por uma nica dimenso:

FA CONSULTORIA E TREINAMENTO

44

Array multidimensional um Array que constitudo por mais de uma dimenso. Veja o exemplo:

FA CONSULTORIA E TREINAMENTO

45

Redefinindo ArraysA palavra reservada ReDim apaga todos os valores anteriores do array. Para evitar isto, podemos usar a palavra Preserve: ReDim Preserve DynArray 1 To 10). Somente o limite superior da ltima dimenso de um array dinmico pode ser alterado quando se usa Preserve.

Navegando por ArraysAps criar e popular um array, voc poder navegar por eles utilizando as instrues de controle de fluxo de programa alterando simplesmente o subscrito. Veja o exemplo anterior.

ConstantesSo variveis que armazenam valores e mantm esses valores durante a execuo do programa, no podendo ser alterados.

FA CONSULTORIA E TREINAMENTO

46

ColeesAs colees so objetos que representam um conjunto de objetos. Elas so usadas para agrupar um conjunto de itens relacionados entre si. Todas as colees possuem mtodos e propriedades pr-definidas. Uma coleo possui um mtodo Item m uma propriedade Count e um mtodo _NewEnum. No Visual Basic podemos ter Colees de formulrios , Controles , drivers , etc... . De maneira geral se um objeto pode ser agrupado em um conjunto ento este objeto ter um objeto Collection. Sendo que o objeto Collection pode criar objetos do tipo Collection. Uma Coleo (collection) , ento um grupo de objetos que em si mesmo um objeto. O Visual Basic possui duas colees de objetos intrnsecas : A coleo Forms e a coleo Controls . Podemos usar as colees com a instruo For Each...Next para realizar aes em todos os objetos que ela contm. Vejamos um exemplo de utilizao onde minimizamos , atravs da procedure MinimezeAll, cada formulrio carregado em uma aplicao:

FA CONSULTORIA E TREINAMENTO

47

Colees do ExcelSabemos que tudo que temos no excel so objetos. Para facilitar a manipulao e o trabalho todos os objetos esto agrupados por colees. Um exemplo dessas colees e a Sheets(index).

Navegando por coleesNavegar com colees muito semelhante a navegao pelos Arrays. Para navegar por colees utilizaremos a instruo For each...next.

FA CONSULTORIA E TREINAMENTO

48

FA CONSULTORIA E TREINAMENTO

49

SubrotinasTodos os macros que criamos so chamadas de subrotinas. Muitas vezes nossas macros ficam muito grande e para facilitar a manuteno e mais interessante dividila em vrios macros menores, ou seja, subrotinas.

Criando subrotinasPara criar uma subrotina digite Sub..{Nome da sub}..() ...End Sub. bom lembrar que sempre que usamos o gravador e criada uma subrotina. Alm dessa maneira temos ainda a opo de clicar em Inserir...Procedimento.

Passando parmetrosAs subrotinas podem receber parmetros, ou seja, valores que sero utilizados pelo cdigo da subrotina.

A palavra reservada CallEssa palavra utilizada para chamar uma subrotina a partir da rotina atual.

FA CONSULTORIA E TREINAMENTO

50

FA CONSULTORIA E TREINAMENTO

51

Funes O que uma funo?Uma funo uma subrotina que permite o retorno de valores.

Diferena entre Subrotina e FunoA nica diferena entre elas e o retorno, uma subrotina no retorna nenhum valor ao passo que a funo retorna valores. Veja o exemplo

FA CONSULTORIA E TREINAMENTO

52

Tratamento de ErrosExistem 3 tipos de erros em macros: 1. Sintaxe: so erros provocados por erros de digitao de comandos 2. Run-time: so erros durante a execuo do programa 3. Lgicos: so erros causados por lgicas incorretas O VBA possui uma estrutura lgica para esses tratamentos de erros. A macro a seguir utiliza essa estrutura

Em qualquer ponto que ocorrer o erro o programa salta automaticamente para o Label Erro, que o responsvel pelo tratamento desse Erro

FA CONSULTORIA E TREINAMENTO

53

Objeto Err.Atravs desse objetos podemos verificar o tipo de erro que ocorreu durante o tratamento do mesmo. Isso facilita o trabalho e permite correes. A macro a seguir alm identifica o erro atravs do objeto Err.

FA CONSULTORIA E TREINAMENTO

54

Criando formulrios personalizadosQuando o formulrio de dados padro do Excel no lhe atende, utilize o objeto UserForm. Pa utilizar o UserForm siga os seguintes passos: 1. 2. Selecione a planilha que contem os dados: Clique no menu Ferramentas, Macros, Editor do visual basic.

1

2

3.

No ambiente de Inserir,UserForm.

desenvolvimento

do

Visual

Basic,

clique

em

3

FA CONSULTORIA E TREINAMENTO

55

Observe que surgiu um UserForm1 pronto para ser desenhado de acordo com suas necessidades. Junto com este UserForm1 tambm fica disponvel a Caixa de controles, que permite inserir vrios tipos de controle no seu formulrio.

FA CONSULTORIA E TREINAMENTO

56

Alterando as propriedades do formulrioComo todos os objetos do Excel, os formulrios tambm apresentam propriedades que podem ser modificadas enquanto voc constri sua aplicao. Vejamos um resumo das principais propriedades: Propriedade Name Funo Define um nome para o objeto, este nome passara a identific-lo todas as vezes que voc precisar fazer uma referncia ao objeto. Define o texto que ser exibido na barra de ttulo do formulrio. Define a Font padro que ser utilizada para todos os novos controle do formulrio bem como tamanho etc. Define a posio inicial que o formulrio ser exibido.

Caption Font StartUpPosition

Para altera uma propriedade utilizamos a janela de propriedades. Esta janela sempre exibe os valores atuais das propriedades do objeto selecionado. Para modificarmos sigam os seguintes passos: 1. 2. Deixe o formulrio selecionado. Na janela de propriedades selecione a propriedade Name, e altere seu valor.

FA CONSULTORIA E TREINAMENTO

57

Inserindo controles nos formulriosDesenhar os controles a principal operao a ser realizada em um formulrio. So eles que iro definir a interface do aplicativo com o usurio. Para inserir um controle no formulrio siga os seguintes passos: 1. Clique e arraste o controle para o local desejado do formulrio.

1

2.

Para alterar as caractersticas de um controle, selecione o controle e utilize a janela de propriedades.

Importante: Os controle apresentam funcionalidades diferentes um dos outros. Portanto uma propriedade de uma caixa de texto no existe obrigatoriamente em um Rtulo.

FA CONSULTORIA E TREINAMENTO

58

Vejamos um exemplo de um formulrio com todas as suas propriedades alteradas:

FA CONSULTORIA E TREINAMENTO

59

Entrando cdigo no formulrioDesenhar o formulrio no tem funcionalidade nenhuma sem inserirmos o cdigo necessrio para que ele interaja com a planilha. Para o seu formulrio interagir com a planilha siga os seguintes passos. 1. D um duplo clique no formulrio e insira as seguintes variveis.

2.

O prximo passo ser definir os eventos de inicializao do formulrios. D o duplo clique no formulrio e no evento Initialize digite o seguinte cdigo:

FA CONSULTORIA E TREINAMENTO

60

Determinando a navegabilidade dos registrosA navegabilidade do registro ser determinada atravs da barra de rolagem que inserimos no formulrio. 1. D um duplo clique na barra e insira o seguinte cdigo:

2.

Teste a aplicao

FA CONSULTORIA E TREINAMENTO

61

Definindo as operaes bsicas Boto NovoVamos comear criando o boto Novo. Este boto ser responsvel por preparar o formulrio para a incluso de um novo registro. Para isso, siga os seguintes passos: 1. Desenhe um boto no formulrio e altere suas propriedades para Name para cmdNovo e caption para Novo.

1

2.

Insira o cdigo de acordo com a figura abaixo na Geral do formulrio.

FA CONSULTORIA E TREINAMENTO

62

2

3.

De um duplo clique no boto novo e insira o seguinte cdigo:

3

4.

Navega at algum registro e teste a funcionalidade do boto.

FA CONSULTORIA E TREINAMENTO

63

Boto SalvarAps digitar os dados no formulrio, necessrio inserir uma rotina que salve estas informaes na planilha. Para criar essa rotina siga os seguintes passos: Insira um boto no formulrio e altere suas propriedades Name para cmdSalvar e Caption para Salvar.

1

1.

D um duplo clique no boto salvar e insira o seguinte cdigo: Private Sub cmdSalvar_Click() 'Essa rotina salva os dados digitados no formulrio na planilha 'Author: Henrique Matias 2 'Data: 20/05/2005 'Data de modificao: 20/05/2005 'Antes da confirmao, vamos verificar a integridade dos dados If Trim(txtNome.Text) = "" Then MsgBox "Nome Invlido", vbCritical, "Aviso de Sistema" txtNome.SetFocus Exit Sub ElseIf Trim(txtEndereco.Text) = "" Then MsgBox "Endereo Invlido", vbCritical, "Aviso de Sistema" txtEndereco.SetFocus Exit Sub ElseIf Trim(txtTelefone.Text) = "" Then

FA CONSULTORIA E TREINAMENTO

64

MsgBox "Telefone Invlido", vbCritical, "Aviso de Sistema" txtTelefone.SetFocus Exit Sub ElseIf Trim(txtStatus.Text) = "" Then MsgBox "Status Invlido", vbCritical, "Aviso de Sistema" txtStatus.SetFocus Exit Sub End If 'Solicitando a confirmao do usurio para salvar o registro If MsgBox("Deseja salvar esse registro!", vbYesNo + vbQuestion, _ "Aviso de Sistema") = vbNo Then Exit Sub End If Set PlanilhaAluno = Worksheets("Alunos") With PlanilhaAluno .Cells(TotalRegistros + 1, 1).Value = txtNome .Cells(TotalRegistros + 1, 2).Value = txtEndereco .Cells(TotalRegistros + 1, 3).Value = txtTelefone .Cells(TotalRegistros + 1, 4).Value = txtStatus End With 'Atualiza o total de registros TotalRegistros = TotalRegistros + 1 'Redefine o valor mximo do navegador Navegador.Max = TotalRegistros 'Fazendo a barrinha acompanhar Navegador.Value = TotalRegistros 'Salvar a pasta toda Workbooks("Alunos2.xls").Save 'Define uma mensagem para a status bar Application.StatusBar = "A pasta est salvada!!!!!!" End Sub 2. Teste sua aplicao.

FA CONSULTORIA E TREINAMENTO

65

Boto excluirMuitas vezes voc vai desejar excluir definitivamente um registro do seu banco de dados. Para criar esse boto siga os seguintes passos: 1. desenhe o boto na no formulrio e altere suas propriedades..

2.

D o duplo clique no boto excluir e digite o seguinte cdigo:

FA CONSULTORIA E TREINAMENTO

66

3.

Visualize um registro e teste o seu projeto.

Boto EditarMuitas vezes voc vai localizar um registro e ser necessrio efetuar alguma modificao nos dados. Essa ser a funo deste boto. Para inserir esse boto no formulrio, siga os seguintes passos. 1. Insira um boto e altere suas propriedades.

2.

Digite o cdigo como a figura a seguir e teste sua aplicao

FA CONSULTORIA E TREINAMENTO

67

O boto PesquisarO boto pesquisar e de muita importncia em bancos de dados grandes, pois ele facilitar a localizao de dados. Para inserir este boto siga os seguintes passos:

1.

Desenhe no formulrio um boto e altere suas propriedades.

2.

Digite o cdigo conforme a figura a seguir e teste sua aplicao.

FA CONSULTORIA E TREINAMENTO

68

Executando uma macro com hora marcadaPodemos programar um procedimento para ser executado em um momento especificado no futuro (seja em uma determinada hora do dia ou aps uma quantidade especfica de tempo decorrido). Para fazermos isso siga os seguintes passos:

A macro acima executa a macro PreparGrafico as 15:30:00. Este cdigo pode ser digitado em um mdulo ou em uma Procedure de objetos do Excel. A macro a seguir ser executada no instante em que o arquivo for aberto.

O cdigo a seguir desprograma uma macro. Basta voc passar o nome da macro e o horrio o qual ela estava programada.

FA CONSULTORIA E TREINAMENTO

69

Desabilitando o salvar e o imprimir de uma PastaPara desabilitar qualquer comando Salvar de uma pasta de trabalho insira o comando a seguir:

Para desabilitar o comando imprimir de uma pasta de trabalho insira o comando a seguir:

FA CONSULTORIA E TREINAMENTO

70

Ferramenta de anlise com grficosAs macros do Excel oferecem um possibilidade muito grande. Elas permitem a criao de ferramentas de anlise bem interessantes. Por exemplo. Podemos criar uma macro que de acordo com a cidade selecionada, ele busque automaticamente todos os dados das Planilhas. Para desenvolver essa planilha, faa o seguinte: 1. 2. Abra uma nova pasta e renomeie as planilhas para: Principal So Paulo e Rio de janeiro. Digite os dados abaixo na planilha So Paulo.

3.

Digite os dados abaixo na planilha Rio de Janeiro.

4.

Insira os dados na planilha principal conforme a tela abaixo:

FA CONSULTORIA E TREINAMENTO

71

5. 6.

Faa uma macro utilizando o gravador que insira um grfico na planilha na planilha principal com os dados de So Paulo. Altere o cdigo gerado com o gravador de acordo com a figura abaixo:

7.

V at a planilha principal e teste selecione uma cidade e verifique que o seu grfico fica automtico. Um erro muito comum que ocorre nessa macros e referente ao nome do grfico selecionado. Caso ocorra na hora que voc esteja testando, crie uma macro que selecione o grfico, verifique o cdigo e copie a linha que seleciona para sua

FA CONSULTORIA E TREINAMENTO

1