Excel e programaçao VBA

Laboratórios de Informática MIEA 2009/10

Profª Catarina Castro Tema3_1

Excel e programação VBA Um computador pode ajudar-nos a resolver problemas, especialmente aqueles que requerem um grande número de cálculos “repetitivos”. O Excel é um software muito utilizado para a resolução de problemas numéricos em engenharia. Tem como facilidades a criação de comandos Macro que irão dar ordens ao Excel para executar as tarefas repetidas e a possibilidade de escrita de código de programação para resolver problemas específicos. O VBA é a linguagem de programação associada ao Excel. Numa linguagem de programação clássica, os dados estão num ficheiro, geralmente com uma formatação rigorosa, ou então pedem-se dados específicos ao utilizador. O Excel tem já a interface com o utilizador e o código apenas se preocupa com o processamento de dados. O Excel tem muitas funcionalidades e pode tornar-se mais genérico sempre que as inexistentes forem programadas. Exemplo de gravação de uma Macro:

e não esquecer

Esta Macro1( ) pode ser gravada e executada sempre que necessário. Por exemplo, podemos começar uma folha nova e mandar executar esta Macro nessa folha, fazendo simplesmente



Macros ajudam-nos a cumprir objectivos que têm que ser repetidos mas não permitem resolver problemas numéricos que envolvem cálculos iterativos. Para isso é necessário escrever um código VBA adaptado à resolução do nosso problema. Para programar, podemos iniciar um código no Excel criando uma macro e em seguida editar o código da macro. Alternativamente podemos escrever o código começando por desenhar um botão de comando tal como veremos mais adiante. O HELP do Excel é uma enorme fonte de informações. Por exemplo, podemos aí encontrar as definições das funções intrínsecas do Excel e também aprender como escrever as nossas funções, funções VBA. Dialog / UserForm Pode-se criar dialogs, e inserir graficamente elementos. No VBA, após inserir um "UserForm" (dialog) fica visível uma Toolbox com os diversos tipos de objectos que se podem inserir, por exemplo: • Label / Frame – para colocar um texto • TextBox - uma caixa de texto em que o utilizador pode inserir um valor • ListBox / ComboBox - uma lista de opções em que o utilizador pode selecionar uma delas • CommandButton / OptionButton / CheckBox - vários tipos de botões, na qual o utilizador pode carregar de forma a executar um conjunto de instruções. Ao inserir uma UserForm, e objectos dentro dela, passa a ser objecto do Excel e permite associar um código. Uma propriedade comum a diferentes objectos é o "Value" tal como nas células. Eventos Cada tipo de objecto tem eventos que podem ser intersectados, sendo criado um procedimento que será chamado quando o evento ocorrer no objecto. Por exemplo, nos botões tem interesse intersectar o evento "Click". Na UserForm tem interesse intersectar o evento "Activate" para efectuar inicializações, e nas listas pode ter interesse intersectar o evento "Change" para efectuar alguma actualização após o elemento da lista ter sido alterado.



Procedimentos tipo Sub Quando queremos escrever o nosso código de programação é necessário guardá-lo num local associado à nossa folha de trabalho. Os dados do programa podem estar na folha Excel ou então ser introduzidos pelo utilizador e depois a escrita dos resultados feita na folha de trabalho. Podemos colocar o código de programação num procedimento associado ao VBAProject e que é acedido através do editor VBA. O procedimento pode ser executado quer usando a caixa de diálogo das Macros, quer criando uma short-cut através da caixa de diálogo das Macros, quer ainda criando um botão de comando e depois associando-o ao procedimento específico. Um procedimento a que chamamos do tipo Sub é um conjunto de instruções colocadas entre Sub e End Sub que executa acções mas que não retorna um valor específico. Como veremos adiante uma função é um conjunto de instruções colocadas entre Function e End Function que retorna um valor específico executa uma série de instruções. Exemplo de desenvolvimento de um procedimento:

e para executar esta instrução basta “clicar” no botão:



Exemplos de instruções são: A=A+10 B=2 Exemplo de declarações de variáveis: Dim A, B As Integer Se para escrever uma instrução é necessário utilizar mais do que uma linha o VBA permite fazer a mudança de linha usando o under scroll ( _ ). Exemplo: Resultado= 1+3*Math.sin(2)+_ 5*Math.cos(3) Mas cuidado, o under scroll não pode ser utilizado, por exemplo, com instruções do tipo MsgBox. Nesse caso será necessário considerar várias MsgBox. Comentários: Por vezes escrevemos centenas ou milhares de linhas de código sendo necessário comentar para mais tarde nos lembrarmos o que um determinado conjunto de instruções faz. Exemplo:

Atenção ao modo como se guardam os ficheiros com código VBA associado:



ou ainda Procedimentos tipo Funções O código está agrupado em funções e procedimentos, que são chamados de outra função ou quando um determinado evento ocorre. Uma função é um processo que permite converter um conjunto de dados de entrada num resultado de saída através de instruções/comandos bem definidos. Um procedimento do tipo função é um conjunto de instruções colocadas entre as instruções iniciadas por Function e End Function. Uma Function é semelhante a uma Sub mas a função pode ainda retornar um valor. Por exemplo a função seguinte converte valores da temperatura em graus Celsius em valores de temperatura Kelvin. Function CelsiusToKelvin (GrausCelsius As Double) As Double Dim ZeroAbsoluto As Double ZeroAbsoluto = 273.15 CelsiusToKelvin = GrausCelsius + ZeroAbsoluto End Function Uma das vantagens da utilização de funções é a possibilidade de extrair instruções repetidas de código, substituí-las por uma única linha de comando e assim estruturando o programa. Um programa bem estruturado é mais fácil de compreender, manter, alterar e encontrar erros. O utilizador pode criar funções e procedimentos que apenas ele utiliza, para desta forma melhor organizar o código e evitar repetição de comandos. Function <nome> ( <args> ) As <tipo> <comandos> <nome>=<valor> End Function Os <args> é uma lista de <var> As <tipo> e o tipo é o tipo de valor de retorno da função. No comando <nome>=<valor> é definido o valor a retornar pela função.



Objectos Os objectos mais relevantes que permitem editar um documento do Excel são: • Sheets("nome") - objecto de acesso a qualquer folha • Cells(linha,coluna) - objecto de acesso a qualquer célula na folha actual • Range("conjunto") - objecto de acesso a um conjunto de células • Selection - objecto de acesso ao conjunto de células selecionado • ActiveCell - objecto de acesso à célula activa Todos estes objectos têm propriedades e métodos, sendo ambos acedidos por um ponto a seguir ao nome do objecto, após o qual o Excel mostra todas as propriedades e métodos aplicáveis nesse objecto. Para realçar o mais importante, nas células existe a propriedade "Value" que tem o valor numérico da célula, e o método "Activate" para passar a ser a célula activa, e nos conjuntos existe o "Select" para passar a ser o conjunto seleccionado. Exemplo: Suponhamos que queremos preencher a coluna 6, linhas 1 a 20 com valores aleatórios entre 5 e 6. Então o código a considerar seria simplesmente: Dim i As Integer For i = 1 To 20 Cells(i, 5).Value = Rnd( ) + 5 Next i Variáveis É essencial numa linguagem ter entidades que se podem alterar. Podem-se declarar variáveis internas para uso apenas no código ou utilizar um valor de uma célula como variável. A declaração de uma variável em programação VBA é: Dim <variável> As <tipo> Os tipos mais utilizados são Integer, Double, String, e Boolean, para representar respectivamente um inteiro, um número real, um texto e um valor booleano. Integer são as variáveis do tipo inteiro guardadas em 2 bytes e variam entre -32 768 e 32 767. Double (reais de dupla precisão) são as variáveis guardadas como números em vírgula flutuante de 64-bit (8-bytes) e variam desde -1.79769313486231E308 até -4.94065645841247E-324 para valores negativos e desde 4.94065645841247E-324 até 1.79769313486232E308 para valores positivos. As variáveis do tipo Booleano são guardadas como números de 2 bytes e tomam apenas 2 valores: Verdadeiro ou Falso. Se o tipo de variável não for especificado então o VBA considera o tipo Variant (números de 16-bytes). Este tipo de variáveis ocupa muito espaço e deve ser evitado. Muitos programas VBA que resolvem problemas numéricos utilizam milhares de variáveis usando ciblos que são repetidos milhares de vezes. A nã declaração de variáveis iria tornar o programa extremamente pesado. Aconselha-se a utilização da instrução Option Explicit no início de cada programa/código o que obriga o programador a declarar todas as variáveis.



A declaração de variáveis pode ser feita: - dentro de um procedimento e então a variável é utilizada apenas ao nível do procedimento ou - no módulo e fora do procedimento e então a variável passa a ser utilizada ao nível do módulo e por qualquer dos procedimentos. (Não fica disponível para outro módulo.) Exemplo: Option Explicit Sub mensagem_minha( ) Dim mensagem As String mensagem = "Vamos ver este exemplo" MsgBox mensagem End Sub Sub mensagem_tua( ) Dim mensagem As String mensagem = "Qual exemplo?" MsgBox mensagem End Sub

Option Explicit Dim mensagem As String Sub mensagem_minha( ) mensagem = "Vamos ver este exemplo" MsgBox mensagem End Sub Sub mensagem_tua( ) mensagem = "Qual exemplo?" MsgBox mensagem End Sub

No entanto é necessário ter cuidado uma vez que variáveis declaradas a nível de módulo podem ser modificadas erradamente. Exemplo: Dim x, y As Integer Sub codigo( ) x = 10 y = minha_funcao( ) End Sub Function minha_funcao( ) As Integer minha_funcao=200*x x = 40 End Function Qual o valor de x e de y após a execução do procedimento/código? Operadores



Exemplo:

Funções de manipulação de texto

• Len (string) retorna o número de caracteres da string. • InStr([start,]string1,string2[,compare]) retorna a posição da primeira ocorrência de um

texto dentro de outro. • Ltrim(string), Rtrim(string) e Trim(string) retorna o texto sem deixar espaços à

esquerda, à direita ou ambos. • Right(string, length) retorna o texto com o número específico de caracteres contados a

partir da direita. • Left(string,N) – retorna os primeiros N caracteres da string • Mid(string,start[,length]) retorna o texto contendo um número específico de caracteres

de um texto. • Asc(string) retorna um inteiro representando o código do caracter correspondendo à

primeira letra de um texto. • Chr(charcode) retorna um texto contendo o caracter associado ao código específico.

A função Str(<Integer>) permite converter um inteiro num texto. Exemplo de codificação de uma mensagem: Dim texto, mensagem As String Dim i, n As Integer texto = "Estás bem?" mensagem = "" For i = 1 To Len(texto) n = Asc(Mid(texto, i, 1)) mensagem = mensagem + Chr(n + 20) Next Cells(2, 3) = mensagem As atribuições de valores a variáveis podem ser feitas por valor através do comando: Let x=3 em que x fica com o valor 3 ou y=" LI " em que y fica com o valor LI

Existe ainda uma atribuição por referência, destinada a objectos: Dim r As Range Set r =Range("A1:C3") a partir deste comando, utilizar r ou Range("A1:C3") é a mesma coisa.



Instruções Condicionais Uma instrução condicional em que o primeiro conjunto de instruções só é executado no caso da condição ser verdadeira, é, por exemplo:

If <condição> Then <bloco de instruções> Else <bloco de instruções> End If

Ciclos Um ciclo permite executar um bloco de instruções, um determinado número de vezes. Os ciclos podem ser implementados de diversas maneiras, a primeira é dependente de uma condição que enquanto for verdadeira o bloco de instruções é executado: While <condição> < um bloco de instruções > Wend Evidentemente que a condição deve depender de alguma variável alterada pelos comandos, caso contrário existiria um ciclo infinito. A segunda forma utiliza um contador para parar o ciclo: For <contador>=<começo> To <fim> < um bloco de instruções > Next <contador> A terceira forma serve para ser utilizada em conjuntos: For Each <elemento> In <conjunto> < um bloco de instruções > Next <elemento> Por exemplo se o conjunto fosse o "Select", o ciclo seria executado tantas vezes quantas as células que estiverem selecionadas: Dim y As String y = InputBox("Comentário") For Each x In Selection y = y + y If Len(x.Value) < 20 Then x.Value = y End If Next x



Exemplo de utilização de procedimentos Como exemplo de utilização de procedimentos vamos considerar o problema das Torres de Hanoi. As Torres de Hanoi são um quebra-cabeças muito antigo e conhecido. Considera-se de um conjunto de N discos de tamanhos diferentes e três pinos verticais, nos quais os discos podem ser encaixados. Cada pino pode conter uma pilha com qualquer número de discos, desde que cada disco não seja colocado acima de outro disco de menor tamanho. Na configuracão inicial todos os discos estão no pino A.

O objectivo é mover todos os discos para um dos outros pinos, sempre obedecendo à restrição de não colocar um disco sobre outro menor.Um algoritmo para resolver este problema é o seguinte: procedimento Hanoi(N, Orig, Dest, Temp) se N = 1 então mover o menor disco do pino Orig para o pino De st; senão Hanoi(N-1, Orig, Temp, Dest); mover o N-ésimo menor disco do pino Orig para o pino Dest; Hanoi(N-1, Temp, Dest, Orig); fim-se fim

Criou-se uma macro em que se a célula activa tiver um valor numérico, esta chama um procedimento TorresHanoi que recursivamente vai resolver o problema.

Sub Macro2( ) If IsNumeric(ActiveCell.Value) Then torres_hanoi ActiveCell.Value, "A", "B", "C" End If End Sub

Sub torres_hanoi(N, origem, temp, destino) If N = 1 Then ActiveCell.Range("A2").Activate ActiveCell.Value = origem + " para " + destino Else torres_hanoi N - 1, origem, destino, temp ActiveCell.Range("A2").Activate ActiveCell.Value = origem + " para " + destino torres_hanoi N - 1, temp, origem, destino End If End Sub



Primeiro, caso apenas se queira mover um só disco, a sub move-o sem utilizar o local auxiliar, caso contrário, move todos os discos mais pequenos para o local auxiliar chamando a função recursivamente, de seguida move o disco maior para o destino, e finalmente chama novamente a função recursivamente para mover os discos mais pequenos do local auxiliar para o destino. O resultado de executar esta sub com a célula com o 3 seleccionada, são as ordens de movimento descritas nas células abaixo:

3 A para C A para B C para B A para C B para A B para C A para C

Na configuracão final todos os discos estão no pino C.



Exercícios: 1. Gestão de Custos Pretende-se com este trabalho que seja desenvolvida uma aplicação, que permita a gestão de custos correntes de um aluno do MIEA. Os registos de custos (ou receitas), devem ter associados um valor, uma data, uma categoria e um projecto. Devem poder ser inseridos através de uma dialog, assim como devem poder ser adicionadas mais categorias e projectos. Como relatórios, dever-se-á apresentar um relatório por Categoria, por Projecto, para um dado mês ou ano, e os saldos dos Projectos em cada mês ao longo de um ano. Também poderá ser gerado um gráfico da evolução dos projectos,... Exemplo de aplicação:

2. Vencimentos. Considere a folha de cálculo Funcionários referente aos funcionários de uma empresa contendo uma tabela com as seguintes informações de cada funcionário: Código (string); Nome; Categoria (A, B ou C); Vencimento; Número de horas extras; Valor de uma hora extra (1% do vencimento); Total a receber.

Os vencimentos das categorias A, B e C são, respectivamente, de 1000€, 1500€ e 2000€.



O número de horas extras nunca pode ser superior a 20. Faça uma macro que permita inserir ou actualizar registos de empregados, sendo dado o código de cada empregado. Nos locais referentes aos vencimentos, aos valores das horas extras e aos totais a receber, devem ser inseridas fórmulas em vez de valores. 3. Métodos iterativos para cálculo de raízes de equações não lineares Pretende-se determinar a raíz de equações da forma f ( x ) = 0, onde f é uma função contínua numa vizinhança da raiz. Um método iterativo, consiste de um modo geral, numa aproximação inicial x0, e num processo de obter sucessivamente novas iteradas xn+1 a partir das anteriores xn, ... Desta forma, pretende-se obter uma sucessão que convirja para z, solução da equação f(x)=0, também designada por raiz da equação, ou zero da função f. MÉTODO da BISSECÇÃO. Sabendo que no intervalo [a, b] a equação f(x) = 0 tem apenas uma e uma só raiz , podem-se construir intervalos [ an, bn ] com metade do comprimento dos anteriores, onde se assegura a existência da raiz. O método pode-se esquematizar:

Intervalo Inicial : [ a0, b0 ] = [ a, b ] Repetir:

1) xn+1 = ( an + bn) / 2 2) Se f (xn+1) f(an) < 0

Então an+1 = an; bn+1 = xn+1 Senão an+1 = xn+1; bn+1 = bn

Até que : f(xn+1) = 0 ou |xn+1-xn| < ε

O critério de paragem utilizado é |xn+1-xn| < ε onde o valor ε>0 é um valor suficientemente pequeno, o erro absoluto verifica | en+1 | < ½ |an - bn| = |xn+1 - xn| < ε. Pode-se determinar facilmente um majorante do erro para uma iterada xn a partir do comprimento do intervalo inicial:

| en| < ½ |an-1 - bn-1| = (1 / 2)n | a0 - b0|

a) Escrever uma macro em VB tal que dada uma equação f ( x ) = 0 e um intervalo inicial [a, b] que contenha uma raiz dessa equação calcule a raiz e o erro absoluto associado.

b) Desenhar no EXCEL a função f ( x ) para encontrar intervalos que contenham uma única raiz e utilize a function desenvolvida para encontrar as raízes da equação.

Considere as equações x4 - 4 x3 - x + 5 = 0 e 2 ex - x sin(x+3) = 0 e outras. 4. Cálculo numérico do valor de um integral -- Regra dos Trapézios Composta



Trata-se de fazer uma aproximação da função integranda, usando um spline linear.

Para simplificar, consideremos que o tamanho desses sub-intervalos é constante = h. Define-se h = ( b - a ) / N, onde N é o número de sub-intervalos ( = número de nós - 1), e temos: xi = a + i h.

O valor do integral é igual à soma dos integrais nos sub-intervalos

Aplicando a regra dos trapézios simples a cada um desses sub-intervalos,

e reparando que há termos que aparecem repetidos na soma, pode simplificar-se a expressão obtendo-se a Regra dos Trapézios Composta:

Neste caso, a fórmula do erro da regra dos trapézios composta é dada por

Calcule uma aproximação para os integrais descritos abaixo e os respectivos erros usando programação estruturada VB:

a) 1 2

0( 0.746825)xe dx− ≈∫

b) 1

0

1( 0.5945)

0.1sen dx

x ≈∫ +



5. Jogo do Ganso Um jogo simples consiste num tabuleiro contendo um percurso de quadrados e um conjunto de peças coloridas. No início do jogo cada jogador recebe uma peça; todas as peças são inicialmente posicionadas imediatamente antes do primeiro quadrado do percurso. Em cada jogada, os jogadores jogam um par de dados, e movem as suas peças para a frente o número de quadrados correspondente à soma dos pontos obtidos nos dados. A ordem em que os jogadores jogam os dados é sempre a mesma (jogador A, depois jogador B, etc.). A maioria dos quadrados no tabuleiro são 'normais', mas alguns são 'armadilhas'. Considere, por exemplo, o tabuleiro da figura cujos quadrados são numerados de 1 a 48. Há exactamente três armadilhas neste tabuleiro.

Se a peça de um jogador cai numa armadilha no final do movimento de um jogador, o jogador perde a vez de jogar na jogada seguinte. Ou seja, ele/ela não joga os dados e a sua peça fica nessa jogada sem avançar. O vencedor do jogo é o jogador cuja peça alcança o final do percurso após o último quadrado. Note-se ainda que não há empates no jogo. Dado o número de jogadores, o número de quadrados no percurso e a posição das armadilhas escrever um programa que determine o vencedor do jogo. 6. Jogo do 4 em Linha Pretende-se com este trabalho que seja desenvolvida a interface do jogo do 4 em Linha entre dois jogadores humanos, não sendo pretendido que se implemente o jogador artificial. O jogo do 4 em linha joga-se numa matriz de 7 x 7, inicialmente vazia. Os jogadores jogam de forma alternada, cores diferentes, numa coluna que tenha casas vazias, preenchendo a casa vazia mais baixa. O objectivo do jogo é fazer 4 em linha (horizontal, vertical ou diagonal).

Deverá ser feito o teste de fim de jogo, e corrigir as jogadas que são feitas em casas inválidas para a casa correcta da mesma coluna. No caso de vitória, devem ser marcadas as quatro casas em linha. Devem também ser contabilizados os jogos ganhos e empatados por ambas as cores. 7. Jogo do galo Este jogo é muito conhecido e dispensa apresentações. Os jogadores jogam de forma alternada usando símbolos diferentes. Deverá ser feito o teste de fim de jogo, e anular as jogadas que são feitas em casas inválidas. Devem também ser contabilizados os jogos ganhos e empatados por cada jogador.



8. Outros jogos: Dados, mastermind... 9. Jogo das cores: Seleccionam-se 3 cores aleatoriamente. Se as 3 cores forem iguais o jogador recebe 10 euros, caso contrário perde 5 euros.

Documents

Excel e programaçao VBA