Páginas seleccionadas […] Após o estudo aprofundado das facilidades da folha de cálculo Excel, impõe-se que nos debrucemos sobre a linguagem de programação Visual Basic for Applications, embutida naquela aplicação da Microsoft e que permite automatizar tarefas e extender as suas funcionalidades. O Excel 2007 e o VBA constituem, assim, uma ferramenta de desenvolvimento de aplicações que, assentando nas funcionalidades das folhas de cálculo, permite automatizá-las, personalizá-las e estendê-las através da programação, para criar poderosos sistemas de tratamento automático de dados. A Fig. 1.1 mostra a utilização de um programa de VBA que automatiza a efectuação de determinados cálculos. O programa assenta nas funcionalidades do Excel, nomeadamente nos seus objectos – folhas, células e botões – combina-as e automatiza-as, estendendo, assim, a funcionalidade do Excel. A 1 2 3 4 5 6
B
C
D
P100
560
100
.......
P110
789
89
.......
P120
89
65
P130
765
78
P140
432
87
........ Efectuar ........ Cálculos ........
........
........
.........
E Programa VBA
.........
Fig. 1.1: Programa em VBA que automatiza determinados cálculos e permite a sua efectuação através de um botão colocado na folha do Excel.
[…] 4.2.4. EXPRESSÕES DE COMPUTAÇÃO As expressões de computação são expressões aritméticas, expressões alfanuméricas ou expressões lógicas que o computador entende e pode calcular.
© FCA - Editora de Informática
1
Programação com Excel para Economia e Gestão Expressões aritméticas O VBA implementa várias operações aritméticas e funções matemáticas que, correctamente ligadas, formam expressões aritméticas.
Operações aritméticas Operação Adição Subtracção
Operador
Prioridade
Exemplo
Resultado
6 6
4+6 70-8
10 62
+
–
Resto MOD 5 5 MOD 2 1 Divisão inteira \ 4 81\8 10 Multiplicação * 3 3*9 27 Divisão / 3 25/2 12,5 Potenciação ^ 2 4^3 64 As operações entre parênteses têm prioridade 1. Podemos, portanto, alterar a ordem de execução das operações, colocando-as entre parênteses Exemplo de resolução, passo a passo, de uma expressão
5* ( 3 + 6 ^ 2 ) / 3 36 39 195 65 Resolução de expressões de computação em VBA 1. Cálculo de expressões de computação em VBA 1.1 Executar o editor de VBA 1.2 Inserir o módulo 1.3 Digitar o procedimento Public Sub Expressao() Msgbox 4+3*(2^3-7) End Sub 2
© FCA – Editora de Informática
Páginas seleccionadas 1.4 Seleccionar Run - Run Sub
[...]
[…] B) Estruturas repetitivas condicionais
A frase de controlo de ciclo das estruturas repetitivas condicionais inclui uma condição que determina o fim da repetição (Fig. 4.31). Há dois tipos de estruturas repetitivas condicionais: •
Repetir enquanto condição verdadeira;
•
Repetir até condição verdadeira.
Fig. 4.31: Tipos de estruturas repetitivas condicionais.
© FCA - Editora de Informática
3
Programação com Excel para Economia e Gestão A estrutura repetir enquanto Nesta estrutura repetitiva condicional, o corpo do ciclo é repetido enquanto determinada condição for verdadeira. A estrutura repetir enquanto pode subdividir-se em duas outras estruturas, uma com a frase de controlo no início do ciclo, e outra com a frase de controlo no fim do ciclo: I – Controlo no início do ciclo Repetir enquanto condição
II – Controlo no fim do ciclo Repetir
Corpo do ciclo Fimenq
Corpo do ciclo enquanto condição
Estrutura repetir enquanto com frase de controlo no início do ciclo Nesta estrutura repetitiva condicional: •
A condição é avaliada no início do ciclo;
•
O corpo do ciclo repete-se enquanto a condição for verdadeira;
•
A repetição do corpo do ciclo termina quando a condição se torna falsa;
•
O corpo do ciclo não é executado nenhuma vez se a condição for inicialmente falsa.
A Fig. 4.32 mostra em fluxograma a repetição das instruções do corpo do ciclo enquanto a condição de início do ciclo for verdadeira. Quando a condição se tornar falsa, é retomada a sequência de execução.
4
© FCA – Editora de Informática
Páginas seleccionadas Fig. 4.32: Fluxograma da estrutura repetir enquanto com frase de controlo no início do ciclo.
[...]
VBA da estrutura repetir enquanto condição Instruções diversas Do While condição Corpo do ciclo - conjunto de instruções que se repete enquanto a condição for verdadeira Loop Restantes instruções
[...] Exemplo de pesquisa linear de um vector
Procura do número 40 no seguinte vector: A=
20
15
40
60
70
1. Inserção à cauda do elemento A(6)=40
A=
20
15
40
60
70
40
2. Procura do número 40, comparando
I 1 2 3
A(I) 20 15 40
A(I)=40 F F V
Observação
40 é o elemento 3 do vectorA
A procura do mesmo número 40 no vector B, B=
20
15
35
60
70
terminava no elemento índice 6 que efectivamente não existe no vector. B= I 1 2
B(I) 20 15
20
15
B(I)=40 F F
35
60
70
40
Observação
© FCA - Editora de Informática
5
Programação com Excel para Economia e Gestão I 3 4 5
B(I) 35 60 70
6
40
B(I)=40 F F F
Observação
A procura terminou e 40 não existe no vector B
V
Top-down 1. Pesquisa linear de um número num vector com N elementos 1.1 Formação do vector por atribuição de valores aleatórios aos N elementos 1.2 Leitura do número a procurar 1.3 Inserção de um elemento fictício igual ao valor que procuramos 1.4 Procura do número enquanto não o encontrarmos e for possível encontrá-lo 1.4.1 Comparação dos números de vendedores com o número a procurar 1.5 Impressão do índice do elemento que for igual ao valor procurado, no caso de não termos encontrado, impressão de uma mensagem apropriada
Variáveis Variável A() I N X
Tipo Inteira + Inteira + Inteira + Inteira +
Significado Vector de números inteiros Índice linha de cada elemento do vector Número total de elementos do vector Número do vendedor a procurar
AlgoritmoPesquisaLinear Procura linear de um número num vector. INÍCIO 10 [ Formação do vector de N inteiros aleatórios compreendidos entre 100 e 200 ] Ler(N) Repetir para I=1 até N A(I)= Int((200 - 100 + 1) * Rnd + 100) Fimpara 20 [ Leitura do número a procurar ] Ler(X) 30 [ Inicialização da pesquisa linear: forçar a existência do número que procuramos e iniciar a procura no primeiro elemento ] A(N+1):=X I:=1 6
© FCA – Editora de Informática
Páginas seleccionadas 40 [ Procurar o número enquanto não o encontrarmos e for possível procurar ] Repetir enquanto A(I,1)<>X I:=I+1 Fimenq 50 [ Afixar o nome e volume de vendas ou mensagem apropriada ] Se I<>N+1 Então Escrever(X, “é o elemento índice”,I,”do vector”) Senão Escrever (“O número”,X, “ não existe no vector!!”) Fimse FIM
Procedimentos em VBA 1. Execute o editor de VBA e insira um módulo 2. Digite Option Base 1 Public Sub PESQUISALINEAR() Dim A() As Integer, Nomecaixa As String Dim I As Integer, N As Integer, X As Integer N = InputBox("Quantos elementos tem o vector?") ReDim A(N + 1) For I = 1 To N A(I) = Int((200 - 100 + 1) * Rnd + 100) Next I X = InputBox("Digite o número a procurar", Nomecaixa) A(N + 1) = X I = 1 Do While A(I) <> X I = I + 1 Loop If I <> (N + 1) Then MsgBox Str(X) + " é o elemento índice" + Str(I) + " do vector A", , Nomecaixa Else MsgBox "O número" + Str(X) + "não existe no vector!!", , Nomecaixa End If End Sub
[...] Exemplo da invocação de procedimentos
A Fig. 6.2 mostra o encadeamento da execução de três procedimentos. O procedimento ProgramaPrincipal, a certa altura da sua execução, invoca o procedimento P, passando-lhe três argumentos; P é então executado, fazendo corresponder aos seus três parâmetros os três argumentos que recebeu. Após a conclusão de P1, o controlo e execução passa para ProgramaPrincipal. A seguir, depois de excutar mais instruções suas, o ProgramaPrincipal invoca Q, passando-lhe quatro © FCA - Editora de Informática
7
Programação com Excel para Economia e Gestão argumentos; Q é executado, fazendo corresponder aos seus quatro parâmetros os quatro argumentos que recebeu. Após a conclusão da execução de Q, o controlo de execução é devolvido a ProgramaPrincipal que continua a executar as suas instruções.
Fig. 6.2: Encadeamento da execução de três procedimentos de acordo com as respectivas ordens de execução.
[…] FUNÇÕES DEFINIDAS PELO UTILIZADOR As funções definidas pelo utilizador destinam-se a suprir a falta de funções embutidas apropriadas; obedecem à sintaxe destas e executam-se analogamente, digitando-se numa célula: =NomedaFunção(lista de argumentos)
ou, se forem “Públicas”, recorrendo-se ao assistente de funções que as classifica na categoria User Defined. As funções definidas pelo utilizador são escritas em VBA, aplicando-se, portanto, as noções fundamentais que descrevemos anteriormente.
Programas de aplicação 1. Taxas efectivas periódicas 8
© FCA – Editora de Informática
Páginas seleccionadas Defina uma função que calcule as taxas de juro periódicas equivalentes a uma dada taxa efectiva anual. Top-down 1. Cálculo de uma taxa efectiva periódica dada a taxa efectiva anual 1.1 Cálculo da taxa equivalente periódica por aplicação da regra fundamental da equivalência: “Taxas de juro equivalentes, aplicadas ao mesmo capital inicial, durante o mesmo prazo, devem gerar o mesmo capital acumulado, independentemente do período de referência das taxas ou do período de capitalização” (Cadilhe, 1994). C0 ( 1 + txanual ) = C0 (1 + i ) ^ nper i = ( 1 + txanual ) ^ ( 1 / nper ) - 1 C0 - Capital inicial i - Taxa efectiva periódica nper - Número de períodos de capitalização por ano txanual - Taxa de juro anual efectiva
Resultado, parâmetros e variável Resultado Txefect Parâmetro Txanual Nper
Tipo Real + Inteiro + Variável Tx
Tipo Real +
Significado Taxa efectiva periódica
Significado Taxa anual efectiva Número de períodos de capitalização por ano Tipo Real +
Significado Taxa efectiva periódica
Função Txefect (Txanual, Nper) Cálculo da taxa efectiva periódica. INÍCIO 10 [ Cálculo da taxa equivalente periódica ] Txefect:= (1+Txanual)^(1/Nper)-1 REGRESSO
Procedimentos em VBA 1. Execute o editor de VBA e insira um módulo © FCA - Editora de Informática
9
Programação com Excel para Economia e Gestão 2. Digite Public Function Txefect(Txanual As Single, Nper As Integer) _ As String Dim Tx As Single Tx = (1 + Txanual) ^ (1 / Nper) - 1 Txefect = Format(Tx, "#.##0%") End Function
Execução da função 1. Digite
A 3 4 5 4
B
Taxa anual efectiva 6% 6%
Período Trimestre Quadrimestre Semestre
C
D
Número de períodos por ano 4 3 2
Taxa efectiva periódica
2. Digite =Txefect($A$4;C4) em D4 3. Copie D4 para D5:D6
D
10
3
Taxa efectiva periódica
4 5 6
1,467% 1,961% 2,956%
© FCA – Editora de Informática