Excel Avancado

capa_contra.p65

1

16/6/2004, 17:39

indice.p65

1

17/6/2004, 10:44

© 2004 b y Dig er ati Books by Diger erati Todos os direitos reservados e protegidos pela Lei 5.988 de 14/12/1973. Nenhuma parte deste livro, sem autorização prévia por escrito da editora, poderá ser reproduzida ou transmitida sejam quais forem os meios empregados: eletrônicos, mecânicos, fotográficos, gravação ou quaisquer outros. Dir etor Editor ial Diretor Editorial Luis Matos Editor a-assistente Editora-assistente Monalisa Neves Redator Redatoraa Sandra Rita Pinto Pr epar ação e Re visão Prepar eparação Revisão Beatriz Utsumi Capa e Pr ojeto Gráf ico Projeto Gráfico Daniele Oliveira Ar te Arte Patricia Fernandez Ferrari Supor te Técnico e Atendimento ao Leitor Suporte [email protected] (11) 3217-2626

Treinamento Avançado em Excel Dig er ati Com unicação e Tecnolog ia Ltda. Diger erati Comunicação ecnologia Rua Haddock Lobo, 347 – 12º Andar CEP 01414-001 São Paulo/SP (11) 3217-2600 Fax (11)3217-2617 www.digerati.com Dir etor es Diretor etores Alessandro Gerardi – ([email protected]) Luis Afonso G. Neira – ([email protected]) Alessio Fon Melozo – ([email protected]) ISBN: 85-89535-38-X

indice.p65

2

17/6/2004, 10:44

Prefácio “Você faz uma vez e o Excel faz o resto por você”. Era com esse bordão que eu iniciava minhas explicações sobre o Microsoft Excel durante as aulas que ministrava. Passados alguns anos, continuo certo de que não existe no mercado nenhuma ferramenta de produtividade – incluindo as “made in Microsoft” – tão completa quanto o Excel. Com ele, você precisa apenas gastar algumas horas projetando os cálculos e estruturando as informações, para que depois o software faça tudo automaticamente, mediante um simples comando. Até parece mágica... O Treinamento Avançado em Excel integra uma lista de títulos lançados pela Digerati Books que inclui os livros 101 Dicas de Excel Vol. I e II, Desvendando o Microsoft Excel e Universidade Excel. Este livro, no entanto, tem uma proposta diferenciada: além de abordar os novos recursos do Excel 2003 e a criação de funções, aborda, em profundidade, a criação de macros no Excel. As macros permitem gravar seqüências de procedimentos, de modo que você possa executar uma série de ações repetidas vezes, no momento em que desejar. Por isso, elas são responsáveis pela automatização da maior parte das atividades de quem lida com dados e cálculos. Por meio da linguagem VBA (Visual Basic for Applications), as macros permitem que sejam desenvolvidas novas funcionalidades dentro do Excel, fazendo com que o programa se torne muito mais do que uma simples ferramenta de produtividade, ganhando, inclusive, o status de ferramenta de desenvolvimento. É a esse recurso altamente sofisticado que você será apresentado nas próximas páginas. Aproveite para familiarizar-se com ele, para, em pouco tempo, poder colocar o Excel no“piloto automático”, guardando seu tempo livre para coisas mais interessantes...

Luis Matos [email protected] Diretor Editorial

indice.p65

3

17/6/2004, 10:44

Índice CAPÍTULO 01 Algumas novidades na versão XP .......................... 09 Localizando planilhas ................................... 09 Exibindo detalhes ......................................... 10 Cabeçalhos e rodapés................................... 11 Diagramas ..................................................... 12 Inserindo novas caixas ........................... 13 Formatando as caixas do seu diagrama .. 13 Alterando o layout .................................. 14 Alinhamento de células ................................ 14 Botão AutoSoma ........................................... 15 Desenhando bordas ...................................... 15 A área de transferência ................................. 16 CAPÍTULO 02 Funções ............................................................... 17 Disponibilizando funções....................... 17 Funções de texto .......................................... 18 Função PROCURAR ................................ 18 Função LOCALIZAR ............................... 19 Funções ESQUERDA e LOCALIZAR ........ 19 Funções matemáticas ................................... 19 Função SUBTOTAL ................................. 19 Funções estatísticas ...................................... 21 Função FREQÜÊNCIA ............................ 21 Funções de data e hora ................................ 23 Função DATADIF .................................... 23 Funções de informações .............................. 24

indice.p65

4

17/6/2004, 10:44

Juntando mais de uma categoria de funções ...... 24 Função PROCV ....................................... 24 CAPÍTULO 03 Gerando gráficos automáticos ............................... 28 Procurando dados específicos ............... 29 Criando o gráfico.......................................... 30 CAPÍTULO 04 Noções de macro .................................................. 33 Macro para classificar dados em uma planilha .. 33 Formas de executar uma macro ................... 35 Teclas de atalho ...................................... 35 Comandos .............................................. 35 Criando botões na planilha .................... 36 Macro para encontrar subtotais.................... 36 Macros de endereços absolutos / relativos ... 37 Macros com endereços absolutos .......... 38 Editando a macro ................................... 38 Macros com endereços relativos ............ 39 Entendendo o código ............................. 40 CAPÍTULO 05 O editor do Visual Basic no Excel .......................... 41 A janela do editor do Visual Basic ................. 41 Módulos ................................................. 43 Objetos ................................................... 43 Eventos da planilha ................................ 44

indice.p65

5

17/6/2004, 10:44

CAPÍTULO 06 Exibindo mensagens ao usuário ............................ 47 Função MSGBOX .......................................... 47 Outros exemplos de mensagem ................... 48 Capturando respostas do usuário ................. 49 Função INPUTBOX ................................ 50 Adicionando comentários ao código ............ 51 CAPÍTULO 07 Criando um formulário no VBA ............................. 52 Criando um formulário ................................. 52 A barra de ferramentas do formulário .......... 53 Inserindo um controle ................................. 55 Criando um formulário simples ............. 55 Alterando o nome dos controles .................. 56 A propriedade Caption........................... 56 Alterando os controles do formulário .......... 56 Inserindo uma imagem .......................... 57 Alterando a ordem dos controles ........... 58 Preparando o ambiente dos dados ......... 58 Preparando a tabela de veículos ........ 59 Associando Veículos com o controle ... 59 Exibindo o formulário ............................ 60 Exibindo o preço do veículo ........................ 61 Descarregando o formulário da memória 61

indice.p65

6

17/6/2004, 10:44

CAPÍTULO 08 Manipulando células ............................................. 62 Métodos de manipulação de células............. 62 Verificando a área selecionada ............... 62 Inserindo fórmulas em uma célula ......... 63 O método Select e a propriedade Selection .. 63 Exemplos de utilização do método Range ... 63 Propriedade Offset ................................. 67 Atribuindo valores às células da planilha ..... 67 CAPÍTULO 09 Variáveis de memória ............................................ 69 Conceito de variáveis de memória ............... 69 Regras para nomeação em VBA .................... 69 Como definir variáveis ................................. 70 Declarando variáveis .................................... 71 Escopo das variáveis ..................................... 72 Variáveis do nível do procedimento ....... 73 Variável do nível de módulo ................... 73 Variáveis de nível global ......................... 73 Tipos de variável .......................................... 73 CAPÍTULO 10 Estruturas de programação.................................... 75

indice.p65

7

17/6/2004, 10:44

Estruturas condicionais ................................ 75 As estruturas If ....................................... 75 A estrutura Case Select ........................... 78 Estruturas de looping ................................... 79 Estrutura Do ... Loop .............................. 79 Estrutura For ... Next .............................. 80 Estrutura For Each ... Next...................... 81 Outros exemplos de criação de loops .... 82 Listando arquivos existentes .................. 82 Usando estrutura Do ... Until .................. 83 CAPÍTULO 11 Criando funções .................................................... 84 CAPÍTULO 12 Personalizando o sistema ...................................... 87 Limpeza dos dados para novo cadastramento 87 Criando uma barra de ferramentas ............... 88 Criando uma barra de menus internos ... 89 Anexando uma barra de ferramentas ao arquivo. 89 Anexando macros a botões .......................... 90 Códigos para ativar a barra de ferramentas .. 90 Ocultando a barra de ferramentas ................ 91 Posição de uma barra de ferramentas........... 92 Desabilitando um item da barra ................... 93 Inicializando o sistema ................................. 93 Finalizando seu sistema de cadastro ............. 94 Posicionando o cursor no 1°- registro em branco 94 Ativando o controle spiParcelas ............. 95 A obra completa ..................................... 95

indice.p65

8

17/6/2004, 10:44

Algumas Algumas novidades novidades na na versão versão XP XP Tem novidade no Excel XP? Tem, sim, senhor! A quinta versão do mais enigmático dos aplicativos do Office traz aqueles pequenograndes aperfeiçoamentos de praxe. Conheça, neste capítulo, as inovações relacionadas à localização de planilhas, cabeçalhos e rodapés e diagramas. Não se esqueça, é claro, da estréia dos novos botões de AutoSoma e Bordas Bordas.

• LOCALIZANDO PLANILHAS Muitas vezes geramos arquivos que depois de algum tempo não lembramos onde guardamos. Para resolver esse problema, podemos solicitar ao Excel que faça uma pesquisa em nosso computador. Como? Veja nos passos a seguir: 1 Clique no menu Ar quiv o>P esquisar Pesquisar esquisar. Arquiv quivo Aparecerá uma caixa de diálogo do lado direito da tela. Preencha-a da seguinte forma:

Te xto de pesquisa: pesquisa:Digite nesse campo o texto que está tentando localizar. Pesquisar em: Selecione o local (pasta) em que deseja pesquisar. O ideal é escolher a opção Meu Computador (como na figura a seguir), dessa forma, serão verificados disquetes, CDs, redes e pastas.

Algumas novidades na versão XP 9

cap_01.p65

9

16/6/2004, 17:40

Os resultados devem ser: Nesta, que é a última opção do menu, podemos escolher o tipo de arquivo que queremos pesquisar – podem ser arquivos de Word, Excel e Access (ou seja, qualquer arquivo do pacote Office), ou mesmo figuras.

Para iniciar a pesquisa, pressione o botão Pesquisar esquisar..

• EXIBINDO DETALHES

10 Treinamento avançado em Excel

cap_01.p65

10

16/6/2004, 17:40

Em versões anteriores ao Windows XP,para ocultar ou visualizar alguns detalhes existentes na planilha, utilizávamos o menu Ferramentas > Opções Opções,habilitando ou desabilitando os itens desejados.A partir da versão XP, o processo tornou-se muito mais rápido e podemos utilizar o menu Exibir Exibir,que fornece as seguintes opções: • Barra de fórmulas; • Painel de tarefas; • Barra de status.

• CABEÇALHOS E RODAPÉS Nas versões anteriores do Excel, para inserir cabeçalhos era igur ar > Ca beçalho/Rodapé necessário clicar em Exibir > Conf Configur igurar Cabeçalho/Rodapé beçalho/Rodapé. Nessa guia era possível selecionar um tipo de cabeçalho ou rodapé entre os pré-definidos, ou incluir um novo, clicando no botão Per sonalizar ca beçalho ersonalizar cabeçalho beçalho. A partir da versão XP, podemos agilizar essa tarefa no menu Exibir > odapé Ca beçalho e rrodapé Cabeçalho odapé, e criar um novo cabeçalho por meio do mesmo sonalizar ca beçalho –, o qual oferece algumas opções botão – Per ersonalizar cabeçalho que já existiam nas versões anteriores, e outras que são novidades da versão XP: Formata os caracteres selecionados, alterando fonte, tamanho, cor e estilo dos mesmos. Insere o código [&Página], o qual, no momento da impressão, é substituído pelo número da página atual. Insere o código [&Páginas], o qual, no momento da impressão, é substituído pelo número total de páginas existentes na planilha. Insere o código [&Data], o qual, no momento da impressão, é substituído pela data da impressão. Insere o código [&Hora], o qual, no momento da impressão, é substituído pela hora da impressão.

Algumas novidades na versão XP 11

cap_01.p65

11

16/6/2004, 17:40

(*) Insere o código &[Caminho]&[Arquivo], o qual, no momento da impressão, é substituído pelo caminho e nome da planilha atual. Insere o código &[Arquivo], o qual, no momento da impressão, é substituído pelo nome do arquivo. Insere o código &[Guia], o qual, no momento da impressão, é substituído pelo nome da guia da planilha atual. (*) Insere o código &[Figura], o qual, no momento da impressão, é substituído por uma figura pré-selecionada. (*) Formata a figura selecionada, permitindo alterar sua largura e altura, ou mesmo recortá-la. (*) Comandos incorporados a partir da versão XP.

• DIAGRAMAS Para inserir diagramas no Excel, clique em Inserir > Diagramas e siga estes passos: 1 Escolha o tipo de diagrama que preferir.

2 Clique na caixa desejada e digite o texto que quer inserir.

12 Treinamento avançado em Excel

cap_01.p65

12

16/6/2004, 17:40

Inserindo novas caixas Também é possível inserir novas caixas em seu diagrama. Para isso, faça o seguinte: 1 Clique na caixa que deseja reproduzir. ir ffor or ma. Isso fará com que a 2 Em seguida, clique no botão Inser Inserir orma. nova caixa seja inserida logo abaixo da caixa selecionada. 3 Tendo feito isso, basta digitar o texto desejado.

Formatando as caixas do seu diagrama Para dar uma aparência pessoal aos seus diagramas, a nova versão do Excel oferece opções especiais: 1 Dê um duplo clique sobre a caixa que deseja personalizar. 2 Aparecerá a caixa de diálogo de formatação, na qual é possível fazer as seguintes alterações:

Algumas novidades na versão XP 13

cap_01.p65

13

16/6/2004, 17:40

Fonte – permite alterar tamanho, cor, estilo e efeitos da fonte, além dela própria. Alinhamento – permite alterar o alinhamento do texto dentro da caixa. Cor es e Linhas – permite alterar as cores de preenchimento, Cores da linha (moldura) da caixa e das setas (quando existentes). Tamanho – permite dimensionar, girar e alterar a escala da caixa. Proteção – (*) permite bloquear a caixa e o texto existente nela. Mar gens – permite alterar as margens do texto dentro da caixa. Marg Web – em alguns navegadores irá exibir um texto alternativo para facilitar a pesquisa de objetos, mas no caso de figuras, esse texto é ignorado. (*) Para que este comando seja executado, é necessário proteger a planilha.

Alterando o layout Para alterar o layout das caixas, siga estes passos: 1 Selecione a caixa desejada. 2 Dê um clique no botão La yout Lay out. 3 Escolha um dos layouts existentes. Outra maneira de alterar o layout é simplesmente clicar no botão mostrado na figura:

• ALINHAMENTO DE CÉLULAS Para alinhar rapidamente uma ou mais células, podemos utilizar os botões . A partir da versão XP, encontramos também algumas opções de matar > Células > Alinhamento alinhamento no menu For ormatar Alinhamento. São elas: Hor izontal – altera o alinhamento horizontal (da esquerda para Horizontal a direita) do conteúdo de uma célula.

14 Treinamento avançado em Excel

cap_01.p65

14

17/6/2004, 10:35

Ver tical – altera o alinhamento vertical (de cima para baixo) do ertical conteúdo de uma célula. Contr ole de te xto – esta opção oferece três possibilidades: Controle texto Retor no automático de te xto – alinha o texto em mais de uma linha; Retorno texto Reduzir par paraa ajustar – reduz o tamanho da fonte para que todo o conteúdo da célula seja apresentado dentro dela; Mesc lar células – alinha o texto em mais de uma célula ao mesMesclar mo tempo, recurso bastante utilizado para casos em que os títulos estão na primeira linha. Or ientação – permite rotacionar o texto dentro da célula. Orientação Dir eção do te xto – permite alterar a ordem de leitura dos dados exisDireção texto tentes na célula, que pode ser da esquerda para a direita ou o contrário.

• BOTÃO AUTOSOMA A partir da versão XP, podemos selecionar um conjunto de células com valores e ativar o botão AutoSoma . Para facilitar o trabalho, foram incorporadas as funções mais utilizadas na criação de fórmulas, tais como SOMA, MÉDIA, CONTAR, MÁXIMO E MÍNIMO, entre outras.

• DESENHANDO BORDAS Nas versões anteriores do Excel, só era possível criar bordas com linhas na cor preta e praticamente com a mesma espessura. Quem quisesse modificar esse padrão tinha de utilizar o menu Formatar > Células > Bor das Bordas das. No Windows XP, além das bordas convencionais, das encontramos a opção Desenhar bor bordas das.Assim, podem-se criar bordas com os seguintes passos: Algumas novidades na versão XP 15

cap_01.p65

15

16/6/2004, 17:40

bordas. 1 Ative o botão Desenhar bordas 2 Clique no botão Estilo da linha:

3 Clique no botão Cor da linha:

Aparecerá um lápis indicando que deve ser riscada a célula na qual desejamos criar a linha:

• A ÁREA DE TRANSFERÊNCIA Para colar um objeto utilizamos o botão , o qual insere na posição atual do cursor o conteúdo da área de transferência. Na versão XP foram incorporadas novas tarefas ao botão:

Fórmulas – cola a última fórmula que foi copiada na planilha. es – cola o valor da célula, ou seja, ignora a fórmula e insealores Valor re somente o resultado do cálculo. Sem bordas – cola a fórmula na célula atual e ignora a borda formatada. Transpor – cola as células em posição inversa. Suponha, por exemplo, que você tenha selecionado várias células que estejam dispostas em uma linha; na transposição, as mesmas células serão coladas, só que agora dispostas em colunas. Colar vínculo – cola o vínculo do arquivo, ou seja, insere a fórmula, bem como todo o caminho utilizado para a sua criação. Dessa forma, ao atualizar os dados da planilha de origem, a planilha de destino é atualizada. Colar especial – cola os dados como um objeto. 16 Treinamento avançado em Excel

cap_01.p65

16

16/6/2004, 17:40

Funções As funções são o centro nervoso do Excel. Não, não se trata de recursos que causam chiliques a torto e a direito. Muito pelo contrário: é por meio das funções que conseguimos agilizar cálculos e elaborar planilhas sofisticadas. E há funções para todo tipo de operação. Desde as somas mais simples até intrincadas fórmulas estatísticas, esse é o caminho das pedras! Neste capítulo, trataremos de uma parte muito importante do Excel: as funções avançadas de texto, matemática, estatística e de data e hora.Afinal de contas, por mais que se trabalhe com uma ferramenta, há sempre alguma coisa a aprender! Aqui, você vai aprender a: • Adicionar novas funções ao Excel por meio de suplementos; • Localizar textos e códigos em células; • Trabalhar com duas ou mais funções em uma só; • Efetuar outros cálculos usando a função SUBTOTAL; • Efetuar cálculos estatísticos com a função FREQÜÊNCIA; • Identificar as mensagens de erro apresentadas em funções. O Microsoft Excel possui cerca de 329 funções, divididas nas seguintes categorias: • Financeiras • Data e hora • Matemática e trigonométrica • Estatística • Procura e referência • Banco de dados • Texto • Lógica • Informações

Disponibilizando funções Algumas funções podem não estar disponíveis quando você quiser usá-las, sendo necessário torná-las disponíveis. Para isso, basta clicar em Ferramentas > Suplementos Suplementos. Surgirá a seguinte tela: Funções 17

cap_02.p65

17

16/6/2004, 17:39

Habilite a caixa de seleção Ferramentas de análise para que todas as funções fiquem disponíveis. Para inserir funções, utilize o botão de Inserir função . Confira, agora, algumas funções interessantes:

• FUNÇÕES DE TEXTO Função PROCURAR Imagine que você queira localizar uma determinada seqüência de caracteres existente em uma célula – por exemplo, o código de um produto que esteja na mesma célula em que está o nome do produto, como no exemplo a seguir:

Para isso, você pode utilizar a função PROCURAR, que possui a seguinte sintaxe: =PROCURAR(texto a localizar;célula com o texto;nº inicial) Em que: Texto a localizar – é o texto que você deseja localizar. Se o conteúdo a procurar for um número, basta digitá-lo diretamente dentro dos parênteses, sem nenhuma formatação. Para procurar o valor R$ 2500,00, por exemplo, digite apenas 2500. Se o conteúdo não for numérico (por exemplo, o símbolo #), você deve digitá-lo entre aspas (“”).

18 Treinamento avançado em Excel

cap_02.p65

18

16/6/2004, 17:39

Célula com o texto – é o endereço da célula em que o símbolo, valor ou texto deve ser procurado. N o- inicial – é o parâmetro no qual você pode especificar a partir de qual caractere a pesquisa deve ser iniciada. É possível, por exemplo, procurar o símbolo # a partir da posição 20. Caso você não especifique o número inicial, o Excel vai considerar que a pesquisa deve ser iniciada 1). a partir do primeiro caractere (1 No exemplo a seguir, o Excel vai localizar o símbolo # dentro do conteúdo da célula A2:

Função LOCALIZAR Outra função que faz pesquisa de caracteres é a LOCALIZAR. A diferença entre ela e a função PROCURAR é que a segunda faz distinção entre maiúsculas e minúsculas, enquanto a LOCALIZAR não faz:

Usando a função ESQUERDA com a função LOCALIZAR A função ESQUERDA retorna um número X de caracteres a partir de um determinado texto, da esquerda para a direita. O número de caracteres X é você quem especifica. Suponha que, na tabela da figura a seguir, você queira retirar apenas a descrição dos produtos. A melhor maneira de fazer isso é usar conjuntamente as funções ESQUERDA e LOCALIZAR:

• FUNÇÕES MATEMÁTICAS Função SUBTOTAL Essa função retorna um subtotal em um banco de dados, podendo executar a soma, a média, o mínimo e o máximo, entre outras operações, em uma lista de dados. A sintaxe da função SUBTOTAL é a seguinte:

Funções 19

cap_02.p65

19

16/6/2004, 17:39

=SUBTOTAL (número da função; células a calcular) Em que: Número da função – é um número entre 1 e 11 que especifica qual função deve ser usada para calcular o subtotal. No Excel, cada função corresponde a um número, como mostra a tabela a seguir: Número da função

Função

1

MÉDIA

2

CONTA

3

CONT.VALORES

4

MÁX

5

MÍN

6

MULT

7

DESVPAD

8

DESVPADP

9

SOMA

10

VAR

11

VARP

Veja um exemplo de utilização da função SUBTOTAL:

20 Treinamento avançado em Excel

cap_02.p65

20

16/6/2004, 17:39

• FUNÇÕES ESTATÍSTICAS Função FREQÜÊNCIA Calcula a freqüência de determinados valores dentro de um intervalo de valores, retornando uma matriz. Imagine que você tenha, por exemplo, uma representação de veículos, e queira saber o número de modelos, por cidade, que se encaixam em determinadas faixas de preço – isto é, a freqüência dessas faixas de preço entre os modelos de carros. Esse exemplo pode ser observado na tabela da figura a seguir:

Nessa tabela, as células B13:B16 trazem os valores das faixas de preço: a primeira faixa vai até R$ 15.000,00; a segunda faixa vai de R$ 15.001,00 até R$ 25.000,00; e assim por diante.Veja que, apesar de isso não aparecer diretamente nos valores das células, a faixa posterior não inclui o último valor da faixa anterior – por exemplo, a segunda faixa não inclui o valor R$ 15.000,00, mas sim o valor seguinte, que é de R$ 15.001,00.Vejamos, agora, como fazer para saber a freqüência de cada uma dessas faixas de preço no Excel: 1 Defina as faixas de dados que deseja analisar. No nosso exemplo, elas foram criadas nas células B13:B16. 2 Selecione as células adjacentes à coluna com as faixas de dados (C13:C17). 3 Clique sobre o botão Inserir função

. Será aberta esta janela:

Funções 21

cap_02.p65

21

16/6/2004, 17:39

4 Selecione a função FREQÜÊNCIA e dê OK OK. 5 Na caixa de diálogo que aparecer, clique no botão com a seta vermelha ao lado do primeiro campo. Na planilha, selecione a área que contém os dados a serem analisados (B2:E11):

6 Na janela que aparecer, clique no botão com a seta vermelha, no primeiro campo, para voltar à tela de inserção da função FREQÜÊNCIA. Agora clique no botão com a seta vermelha ao lado do segundo campo. Na planilha, selecione as células que contêm as faixas de dados que vão servir de base para o cálculo (B13:B16). 7 Pressione as teclas Ctrl + Shift + Enter para que sejam inseridos os valores correspondentes a cada uma das faixas. Não use a tecla Enter sozinha, ou o resultado será inserido somente na primeira célula:

22 Treinamento avançado em Excel

cap_02.p65

22

16/6/2004, 17:39

• FUNÇÕES DE DATA E HORA Função DATADIF Para calcular a diferença entre duas datas, levando em conta o número de dias, meses ou anos, use a função DATADIF, que possui a seguinte sintaxe: =DATADIF(data_inicial;data_final;tipo de diferença desejado) Na qual tipo de diferença desejado pode ser representado como:

Unidade Retorno “Y”

O número de anos completos entre a data inicial e a data final.

“M”

O número de meses completos entre a data inicial e a data final.

“D”

O número de dias completos entre a data inicial e a data final.

“MD”

A diferença entre as datas inicial e final, sendo que meses e anos são ignorados.

“YD”

A diferença entre as datas inicial e final, sendo que dias e anos são ignorados.

A seguir, veja um exemplo de uso da função DATADIF com diferentes formatos para o retorno do valor:

Funções 23

cap_02.p65

23

16/6/2004, 17:39

• FUNÇÕES DE INFORMAÇÕES As funções de informações são aquelas que analisam fórmulas e condições e respondem com VERDADEIRO ou FALSO. Elas são bastante úteis para verificar o tipo de conteúdo de determinadas células, se estão vazias ou não,se estão preenchidas com textos ou números etc. A tabela a seguir mostra exemplos das principais funções lógicas:

• JUNTANDO MAIS DE UMA CATEGORIA DE FUNÇÕES Função PROCV A função PROCV efetua uma pesquisa no sentido vertical em uma tabela de dados, retornando um dado existente em qualquer uma das colunas. Sua sintaxe é: =PROCV(valor procurado;matriz_tabela_número índice coluna) Ou seja: =PROCV(o que pesquisar;área a pesquisar;número da coluna a trazer ) Imagine a seguinte tabela:

24 Treinamento avançado em Excel

cap_02.p65

24

16/6/2004, 17:39

Para verificar qual é o nome de determinado filme (título), siga estes passos: 1 Nomeie a área com o catálogo dos filmes como FITAS, selecionando as células e indo ao menu Inserir > Nome > Definir Definir; ou então digitando o nome diretamente na Caixa de nome e pressionando Enter.

2 Crie a tabela com os dados das vendas. 3 Posicione o cursor na célula D4 (ou na célula em que quiser que apareça o resultado da busca). 4 Clique no botão Inserir função

..

Referência. 5 Selecione as funções de Procura e Referência

6 Clique sobre a função PROCV e dê OK OK. 7 Insira os argumentos da função, conforme o exemplo abaixo:

Funções 25

cap_02.p65

25

16/6/2004, 17:39

8 Pressione OK OK; com isso, será informado o nome do filme que você está procurando. Copie para as células abaixo. 9 Experimente alterar o código do filme para ver o Excel preencher os campos automaticamente. Se o usuário por acaso escolher um filme que não existe na lista, o Excel vai retornar este erro:

Para evitar esse tipo de problema, podemos criar uma combinação com as funções lógicas SE, ÉERROS e PROCV, como mostra a explicação a seguir: 1 Vamos começar conhecendo a sintaxe de cada uma das três funções que vamos usar: =SE (condição; faça caso condição verdadeira; faça caso condição falsa) =ÉERROS(fórmula) =PROCV(valor procurado; local a procurar; coluna desejada) 2 Para juntar as três funções, devemos construir uma expressão com esta sintaxe: =SE(ÉERRROS (PROCV)); exiba 0; exiba o resultado de PROCV)

26 Treinamento avançado em Excel

cap_02.p65

26

16/6/2004, 17:39

3 Deixe o cursor no primeiro PROCV (título do filme) e pressione o botão Inserir função para editar o conteúdo da fórmula, pois agora você vai adicionar as funções SE e ÉERROS. nome, 4 Para inserir novas funções, utilize a área com a Caixa de nome que agora exibe a última função utilizada:

5 Utilize a função SE, inserindo os dados mostrados na figura:

6 Editando a função com a Caixa de nome nome, você pode ir de uma função a outra com um clique sobre a Barra de fórmulas fórmulas:

Funções 27

cap_02.p65

27

16/6/2004, 17:39

Gerando gráficos automáticos Gerando gráficos automáticos O que todo mundo quer, hoje em dia, é poder apertar um único botão e, com isso, resolver todos os problemas. Nada de errado nisso, pelo menos em se tratando de gerenciamento de dados. Que tal construir gráficos que se atualizem automaticamente quando a tabela de dados for alterada? Que tal ter à mão um sistema que varra a sua planilha e gere um gráfico a partir dos dados de que você precisa? Excelente, não? E o melhor: não é difícil. Ao menos, não com os recursos oferecidos pelo Excel. Veja como construir um gráfico dinâmico que incorpore recursos de formulários e lista de dados: 1 Crie, em uma planilha em branco, a seguinte tabela de dados:

2 Selecione as células com os nomes das publicações (A3:A8) e nomeie esse intervalo como PRODUTOS. 3 Selecione todos os dados (A2:G8) e nomeie esse intervalo como VENDAS. 4 Vá até a Plan2 e inclua nela estes dados:

5 Ative a barra de ferramentas de formulários com o comando er m ulár ios Barrra de ffer errramentas > For orm ulários ios. Exibir > Bar

28 Treinamento avançado em Excel

cap_03.p65

28

16/6/2004, 17:39

6 Insira um controle de caixa de combinação, clicando neste botão:

7 Agora você precisa ativar as propriedades do controle que acabou de criar. Para isso, clique nele com o botão direito do mouse, dê matar contr ole um clique sobre a opção For ormatar controle ole, e, depois, preencha os campos da janela como mostra a figura:

Procurando dados específicos Ainda usando o mesmo documento do Excel que você criou, vamos procurar alguns dados específicos, como, por exemplo, o nome de determinado produto. Isso pode ser feito usando-se a função ÍNDICE, que tem a seguinte sintaxe: =ÍNDICE(área a ser varrida; nº da linha; nº da coluna) Os nomes dos produtos estão no intervalo de células PRODUTOS; portanto, essa é a área que deve ser varrida. A linha que você procura é aquela na qual foi vinculado o controle de caixa de combinação – logo, trata-se do resultado existente em C3. Dessa forma, a função para verificar o nome do produto deverá ser:

Gerando gráficos automáticos 29

cap_03.p65

29

16/6/2004, 17:39

Se, em vez do nome de um produto, você quiser verificar os dados de JAN, basta aplicar esta fórmula:

Repare que, para os meses, você deve alterar apenas o número da coluna.Assim, quando for selecionar uma nova publicação, aparecerão os dados referentes às vendas expressas nessa coluna.

• CRIANDO O GRÁFICO Para criar um gráfico com os dados da planilha, comece selecionando as duas linhas que contêm os dados da venda, ou seja, a linha que apresenta os nomes dos meses e a linha que traz os valores das vendas. Feito isso, você já pode ativar o assistente de gráficos para criar um gráfico do tipo “pizza”, seguindo este passo-a-passo: 1 Selecione os dados. 2 Clique no botão Assistente de ggráf ráf ico ráfico ico.

30 Treinamento avançado em Excel

cap_03.p65

30

16/6/2004, 17:39

3 Clique na opção de gráfico do tipo “pizza”.

ançar. 4 Clique no botão Avançar 5 Defina a opção Sér ies em como Linhas e clique em Avançar Séries ançar.

6 Desabilite a opção Mostrar legenda na guia Legenda Legenda.

Gerando gráficos automáticos 31

cap_03.p65

31

16/6/2004, 17:39

dados, habilite as opções Porcentagem e Nome 7 Na guia Rótulo de dados categoria. da categoria

8 Pressione A v ançar e, na tela que surgir, selecione a opção em, para que o gráfico seja posicionado como um Como objeto em objeto na planilha.

9 A cada vez que você selecionar um produto diferente, o Excel irá criar um novo gráfico, de acordo com os dados selecionados.

32 Treinamento avançado em Excel

cap_03.p65

32

16/6/2004, 17:39

Noções de de macro Noções macro Imagine ter de digitar as mesmas informações todos os dias, a cada 15 minutos.Veja: isso não é uma obra de ficção.Para quem trabalha com serviços financeiros e administrativos – ou seja, 90% do pessoal do escritório de uma empresa –, esse tipo de situação pode ser realmente rotineiro. Mas, felizmente, existem as macros, pequenas aplicações que podem nos ajudar a economizar muito tempo e poupar nossa paciência. Neste capítulo, você verá que o sonho da rapidez no trabalho é possível. Uma macro nada mais é do que uma seqüência de comandos armazenada em um botão. Usando macros, você pode gerar gráficos com configurações especiais, aplicar cálculos extensos repetidas vezes, entre muitos outros procedimentos, apenas clicando em um botão. Basta criar macros bem elaboradas. Neste capítulo, você vai deparar freqüentemente com o termo Visual Basic ffor or Applications A, que é uma linguagem de prograpplications, o famoso VB VBA mação voltada para aplicativos. O VBA vem incorporado ao Office, e, por isso, pode ser utilizado em qualquer aplicativo desse pacote, como o MS Excel, o MS Word, o MS Access etc..Todo código gerado no VBA é compilado, e, por esse motivo, executa loops (laços) de programação rapidamente. Outra vantagem de utilizar o VBA é que o código gerado pode ser lido em qualquer aplicativo Microsoft, garantindo uma padronização no modo de programação.

• M A C R O PA R A C L A S S I F I C A R D A D O S E M UMA PLANILHA Para entender um pouco sobre macros, nada melhor do que partir para a prática e criar uma macro simples. Por enquanto, você vai selecionar a ação para a sua macro a partir de uma lista pré-definida. No caso, vamos criar uma macro para classificar os dados mostrados na planilha a seguir:

Algumas novidades na versão XP 33

cap_04.p65

33

16/6/2004, 17:38

1 A sua macro vai se chamar CLASSIFICA. Posicione o cursor em qualquer célula e vá ao menu Ferramentas > Macro > Gravar nova macro macro, para acessar esta tela:

2 Digite o nome da macro ( CLASSIFICA) e observe as opções disponíveis: Nome da macro – Procure escolher um nome significativo, e que tenha a ver com a função que a macro vai executar. Tec la de atalho – solicita ao MS Excel que rode todas as macros por meio ecla de teclas de atalho (por exemplo, Ctrl + C). Mas, cuidado com essa opção, pois, se existir algum comando que utilize as mesmas teclas, ele será desabilitado,e a macro passará a funcionar em seu lugar. Armazenar macro em – as tarefas a ser executadas são arquivadas em um determinado local. Esse local pode ser a Pasta pessoal de macros macros, que irá criar um arquivo Pessoal.XLS (uma pasta oculta que permanecerá na pasta de inicialização do MS Excel); uma Nova pasta de trabalho trabalho, que irá criar um arquivo (planilha) para armazenar somente a macro e a última opção;

34 Treinamento avançado em Excel

cap_04.p65

34

16/6/2004, 17:38

ou a pasta de trabalho atual (opção Esta pasta de trabalho trabalho), que faz com que o arquivo de macros seja armazenado juntamente com a planilha atual. Escolhendo essa última opção, se a planilha atual for movida ou copiada para um outro local, as macros irão junto com ela. OK, todos os comandos a ser executados na planilha 3 Ao pressionar OK serão armazenados. Por isso, fique atento: se você cometer algum erro, pode ser mais fácil excluir a macro e começar tudo de novo do que tentar consertar o erro. 4 Posicione o cursor no primeiro registro da tabela (célula B5) e icar utilize o menu Dados > Classif Classificar icar. Escolha, primeiro a opção País aís,e, em seguida, Produto Produto. gravação. 5 Depois de fazer a classificação, pressione o botão Parar gravação

• FORMAS DE EXECUTAR UMA MACRO Uma vez que você já tenha criado a sua macro, como fazer para rodá-la? É simples.Você pode usar as teclas de atalho – selecionadas durante o desenvolvimento da macro –, comandos, ou botões.

Teclas de atalho Para rodar uma macro usando teclas de atalho, basta pressionar as teclas definidas na hora da sua criação.

Comandos Para acessar o comando que apresenta as macros disponíveis, faça o seguinte: 1 Pressione as teclas Alt + F8. 2 Dê um clique no nome da macro que quer executar e pressione o ecutar botão Ex Executar ecutar.

Algumas novidades na versão XP 35

cap_04.p65

35

16/6/2004, 17:38

Criando botões na planilha Outra maneira de executar a macro é criar um botão para ela: 1 Ative a barra de ferramentas de formulários em Exibir > Barra de Formulários. ferramentas > Formulários 2 Dê um clique na opção Botão de comandos comandos.

3 Clique e arraste sobre a planilha no ponto em que deseja criar o botão. O Excel exibirá uma lista com as macros existentes. Clique sobre a macro que você quer associar ao botão:

4 Para alterar o texto do botão, basta clicar dentro dele e editar o texto:

• MACRO PARA ENCONTRAR SUBTOTAIS Vamos criar, ainda na mesma planilha em que você gravou a macro para classificação por País e Produto, uma segunda macro, para encontrar o subtotal de cada país. Siga estes passos: 1 Ative a gravação de novas macros, indo ao menu Ferramentas > Macros > Gravar nova macro macro.

36 Treinamento avançado em Excel

cap_04.p65

36

16/6/2004, 17:38

2 Nomeie a macro como SUBTOTAIS e dê OK OK. 3 Posicione o cursor sobre o primeiro registro (B5) e clique em Subtotais. Dados > Subtotais 4 Pressione OK e calcule um novo subtotal, agora por produto. Desta vez, não se esqueça de desabilitar a opção Substituir subtotais atuais ais. Dê OK OK.

5 Para executar a macro, desative os subtotais criados, indo ao menu Dados > Subtotais e pressionando o botão Remover todos e, em seguida, OK OK. 6 Crie um botão para a execução dessa macro:

• MACROS DE ENDEREÇOS ABSOLUTOS / RELATIVOS No tópico anterior, você criou duas macros. Se reparar nas referências que essas macros fazem às células que contêm as fórmulas, vai perceber que esses endereços são absolutos. O que isso quer dizer? Por exemplo: ao selecionarmos o intervalo de células A1:F4 e alterarmos o estilo para negrito, sempre que executarmos a macro, o mesmo endereço, tendo conteúdo ou não, será alterado para negrito. Ou seja, uma

Algumas novidades na versão XP 37

cap_04.p65

37

16/6/2004, 17:38

macro com endereço absoluto será executada sempre na mesma posição na planilha. Já uma macro com endereço relativo pode atuar sobre outras células que não a que serviu originalmente como base. Portanto, quando precisamos executar um procedimento em várias células da planilha, o melhor a fazer é utilizar um endereço relativo.

Macros com endereços absolutos Para entender a diferença entre os dois tipos de endereços, digite a planilha abaixo:

1 Posicione o cursor em A2 e ative o gravador de macros. 2 Nomeie a macro como ABSOLUTOS e dê OK OK. 3 Digite seu nome e pressione a tecla Tab. 4 Digite seu endereço e, novamente, pressione Tab. 5 Digite seu telefone e pare a gravação da macro. Para visualizar o efeito da macro que você acabou de criar, executea várias vezes.Você irá perceber que o cursor insere o nome, o endereço 2). e o telefone sempre na mesma linha (2

Editando a macro Para editar a macro, use o comando Ferramentas > Macros Macros. Na janela que aparecer, dê um clique sobre o nome da macro desejada e pressione o botão Editar Editar. Se preferir usar teclas de atalho para abrir o Editor do Visual Basic, basta pressionar Alt + F11. Editando a macro ABSOLUTOS no editor do Visual Basic, você verá estas instruções:

38 Treinamento avançado em Excel

cap_04.p65

38

16/6/2004, 17:38

Macros com endereços relativos Vamos, agora, criar uma macro com endereço relativo. Para isso,siga estes passos: 1 Apague o conteúdo da linha 2 para gravar uma nova macro. 2 Posicione o cursor na célula A2 e grave uma nova macro com o nome RELATIVOS. 3 Observe que, assim que o gravador de macros for iniciado, aparecerá na tela uma pequena barra de ferramentas com dois botões:

4 Clique sobre o botão Referência relativa

.

5 Na célula A2, digite seu nome e pressione a tecla Tab. 6 Em B2, digite seu endereço e pressione Tab. 7 Em C2, digite seu telefone. 8 Pressione a seta para baixo para passar para a linha de baixo. 9 Posicione o cursor sob a célula que contém o seu nome (A3). 10 Pare a gravação da macro. Execute a macro várias vezes e observe a diferença entre o comportamento dela e o da macro ABSOLUTOS.

Algumas novidades na versão XP 39

cap_04.p65

39

16/6/2004, 17:38

11 Edite a macro no editor do Visual Basic (Alt + F11).Você verá as seguintes instruções:

Entendendo o código Comando

Descrição

ActiveCell

Célula ativa.

ActiveCell.Select

Seleciona a célula ativa.

ActiveCell.FormulaR1C1=”Sandra”

Insere, na célula ativa, o conteúdo Sandra.

ActiveCell.Offset(0,1)

Desloca o cursor 0 linhas para baixo e uma coluna para a direita.

ActiveCell.Offset(1,-2)

Desloca o cursor uma linha para baixo e duas colunas para a esquerda.

40 Treinamento avançado em Excel

cap_04.p65

40

16/6/2004, 17:38

O editor do Visual Basic O editor do Visual Basic no Excel no Excel As macros realmente facilitam a vida, não? E o que vimos até aqui é apenas o começo. Se você quiser se aventurar pelo mundo da automatização de processos, vale a pena dedicar algum tempo ao estudo do Visual Basic. Neste capítulo, apresentaremos uma visão geral sobre o editor do VB que vem embutido no MS Excel. Com um pouco de perseverança, você poderá fazer parte daquele seleto clube de pessoas que dizem: “Fazer o relatório estatístico dessas dez planilhas? É simples: espera um minutinho que eu crio uma macro para isso...” A primeira coisa a fazer é familiarizar-se com o ambiente de trabalho oferecido pelo editor do Visual Basic. Só para lembrar, para abrir o editor basta pressionar Alt + F11 ou ir ao menu Fer errramentas > Macr os > Editor do Visual Basic Macros Basic. Se preferir abrir a tela do editor do VBA diretamente para uma macro que já exista no seu os > Macr o , clicar documento, você pode ir até Fer errramentas > Macr Macros Macro sobre o nome da macro a ser editada, e depois no botão Editar Editar.

• A JANELA DO EDITOR DO VISUAL BASIC

O editor do Visual Basic no Excel 41

cap_05.p65

41

16/6/2004, 17:38

Projeto – Nesta janela, encontramos a hierarquia do projeto que está sendo editado. Ela mostra o nome do arquivo de projeto (*.xls), as planilhas que ele contém etc.. Ou seja, a janela Projeto permite visualizar os objetos da aplicação atualmente aberta (pasta de trabalho, documentos e módulos). Caso não esteja visualizando essa área, pressione o botão ou tecle Ctrl + R. Nessa janela existem ainda três ícones que permitem alterar o modo de visualização da estrutura: Abre uma tela (página) com os módulos (rotinas) de classe da aplicação. Exibir código

Exibir objeto

Exibe a janela de módulo que contém o código em VBA que está associado ao objeto selecionado. Alterna entre a janela do Editor do Visual Basic e a janela original da aplicação, com o objeto selecionado.

Alternar entre pastas Faz com que a exibição da janela Pr Proojeto alterne entre a hierarquia dos objetos e uma lista simples dos mesmos. ojeto Na janela Pr Projeto ojeto, repare que há vários ícones diferentes. O significado deles está na tabela a seguir: Permite a abertura de todos os objetos de uma pasta de trabalho, como planilhas, formulários, módulos etc.. Pasta1 é o nome do arquivo que está sendo editado no momento; quando o arquivo é salvo, o nome é substituído pelo que você escolher. É a referência à pasta de trabalho que está aberta no momento. Mostra também todos os eventos (procedimentos) pertencentes a essa pasta, tais como abertura de arquivo, fechamento de arquivo etc. Exibe a folha de código com as rotinas para a planilha específica. Abre uma tela com os códigos existentes para a criação de rotinas (macros) diversas para essa pasta de trabalho.

42 Treinamento avançado em Excel

cap_05.p65

42

17/6/2004, 10:38

Abre uma tela (página) com os módulos (rotinas) de classe da aplicação. Abre o formulário criado pelo usuário. Propriedades – mostra o nome do módulo em uso e suas propriedades atuais. Esta janela se divide em duas seções, uma com as propriedades em ordem alfabética, e outra com as mesmas propriedades separadas por categorias de comandos. Módulo – esta janela exibe o módulo atual. Módulo é a unidade básica de um código em VBA, e funciona como se fosse um local em que são armazenadas todas as macros criadas. Podemos também descrever módulo como uma seqüência de instruções que ensina ao MS Excel o que fazer. Área de trabalho – trata-se da área disponível para a manipulação dos módulos.

Módulos Na tabela que acabamos de mostrar, falamos muito em módulo módulo. Como dissemos, um módulo é como um local em que são armazenadas todas as macros criadas.Todo módulo possui procedimentos, que são as normas para a condução da tarefa. No VBA, todos os procedimentos devem começar com a instrução SUB ou FUNCTION. Para entender melhor, observe o código da macro ABSOLUTOS, que você criou no começo deste capítulo:

O editor do Visual Basic no Excel 43

cap_05.p65

43

17/6/2004, 10:38

Objetos Outro conceito importante na programação com VBA é o de objetos. Temos vários objetos dentro de um aplicativo: por exemplo, uma barra de ferramentas, uma planilha aberta, uma caixa de texto etc.. Portanto, um objeto é um elemento controlável por meio da linguagem VBA, e que é utilizado na execução de tarefas. No VBA, quando você se refere a um objeto, na verdade está se referindo a uma coleção de objetos que inclui o objeto em si. Assim, a referência deve conter primeiramente o nome da coleção à qual o objeto pertence, e depois qual o item específico.Veja alguns exemplos de referências a objetos no VBA: Wor kSheets(“Plan1.xls”) orkSheets(“Plan1.xls”)

ksheet (planiColeção de objetos Wor orksheet lhas), referindo-se ao objeto Plan1.

Rang e(“ A1:C3”).Select Range(“ e(“A1:C3”).Select

e que se Coleção de objetos Rang Range refere às áreas.

Wor kBooks orkBooks

book pasksbook Coleção de objetos Works tas de trabalho.

Os objetos organizam-se em uma hierarquia. No topo dela encontra-se o objeto A pplication pplication, que, no nosso caso, é o próprio Excel. Abaixo de A pplication pplication, em ordem decrescente de nível hierárquikbook uBar co, aparecem os objetos Wor orkbook kbook, AddIn AddIn, Dialog Dialog,Men MenuBar uBar, ToolBar oolBar, w, e assim por diante. Para nos referir a um deles, precisamos Windo indow informar a que coleção pertence o objeto e qual é esse objeto:

O tipo mais comum de referência é aquele feito a uma planilha ou a células de uma planilha.Tanto planilhas quanto células são objetos ativos. A tabela a seguir mostra os objetos ativos no MS Excel:

44 Treinamento avançado em Excel

cap_05.p65

44

16/6/2004, 17:38

ActiveCell

ActiveChart

Activ eDialog ActiveDialog

Activ eX ActiveX

Activ eMen uBar ActiveMen eMenuBar

Activ eP anel ePanel ActiveP

Activ ePr inter ePrinter ActivePr

Activ eSheet ActiveSheet

Activ eW indo w ActiveW eWindo indow

Activ eW or kbook ActiveW eWor orkbook

Experimente, por exemplo, digitar as seguintes instruções logo abaixo do código da sua macro: Sub AlteraExibição() With ActiveWindow .DisplayGridlines = False .DisplayHeadings = False .Zoom = 75 End With End Sub Para que a macro seja executada na janela de códigos, utilize o botão ou a tecla F5. Ao voltar para a planilha, você verá que a macro realizou as seguintes tarefas:

Eventos da planilha Para alterar eventos de planilha, selecione uma das planilhas na ojeto e ative o objeto Wor ksheet janela Pr Projeto orksheet ksheet, como mostra a figura: Sub AlteraExibição() With ActiveWindow .DisplayGridlines = False .DisplayHeadings = False .Zoom = 75 End With End Sub

Início da macro (procedimento) Com a janela ativa Não exibir linhas de grades Não exibir cabeçalhos de linhas/colunas Zoom de 75% Finaliza a janela ativa Finaliza a macro

O editor do Visual Basic no Excel 45

cap_05.p65

45

16/6/2004, 17:38

Veja, na tabela a seguir, quais os principais eventos que podem ser associados a uma planilha:

Evento

Quando ocorre

Activ ate Activate

Quando uma planilha é ativada.

Bef or eDoub leClic k Befor oreDoub eDoubleClic leClick

Ao clicar duas vezes em uma planilha.

Bef or eRightClic k Befor oreRightClic eRightClick

Ao clicar com o botão direito do mouse sobre uma planilha.

Calculate

Depois de toda a planilha ser recalculada.

Chang e Change

Quando as células das planilhas são alteradas pelo usuário.

Deactiv ate Deactivate

Quando a planilha é desativada.

SelectionChang e SelectionChange

Quando a seleção é alterada em uma planilha.

46 Treinamento avançado em Excel

cap_05.p65

46

16/6/2004, 17:38

Exibindo mensagens ao usuário As caixas de mensagem funcionam como a voz do programa, o meio de comunicação entre o aplicativo e a pessoa que o está utilizando. São elas que avisam ao usuário quando acontece algum erro, permitem que ele escolha o que fazer a seguir, ou mesmo abrem espaço para que ele passe dados ao programa. O VBA oferece modelos prontos de caixa de mensagem; basta escolher o tipo adequado e configurá-lo. Com isso, você consegue facilmente guiar o usuário para que ele aproveite completamente o documento do Excel que você criou. Afinal de contas, quem não se comunica se estrumbica!

• FUNÇÃO MSGBOX É possível criar macros que exibam mensagens na tela, para informar o usuário sobre alguma operação que tenha sido executada, alertá-lo sobre erros etc. Só que, diferentemente do que temos feito até agora, a mensagem em si não pode ser criada apenas ligando o gravador de macros. Ela deve ser inserida como parâmetro para a função MSGBOX, que tem a seguinte sintaxe: MSGBOX “Mensagem”, tipo da caixa,“Título” Em que: Mensagem – é a mensagem que deverá aparecer na caixa. O texto deve ser digitado entre aspas, com tamanho máximo de 1024 caracteres. Se você quiser que o texto apresentado seja o valor de uma variável, digite o nome da variável, sem aspas. Tipo da caixa – define o tipo da caixa de diálogo (se será uma informação, um alerta de erro etc.). Título – Define o título da caixa de diálogo.

Exibindo mensagens ao usuário 47

cap_06.p65

47

16/6/2004, 17:38

Para testar a exibição de mensagens com a função MSGBOX, crie, na janela do Editor do Visual Basic, uma macro com as seguintes instruções: Sub TestaMensagem() MsgBox “Esta mensagem só contém o prompt” End Sub Execute a macro pressionando a tecla F5. Você verá esta caixa de mensagem:

• OUTROS EXEMPLOS DE MENSAGEM Há vários tipos de caixa de mensagem.Vejamos agora um exemplo contendo um botão de OK OK. Este é o código da caixa: Sub TestaMensagem2() MsgBox “Prompt - Texto da mensagem”, _ vbExclamation + vbOKOnly,”VBOK é o tipo _ de botão, e TITLE é o título” End Sub No código, repare no símbolo de underscore (_) no fim da segunda linha. Ele é usado para quebras de linha. Quando uma linha de comando não couber na linha da tela, quebre-a com um underscore. Assim, mantém-se a continuidade do comando. O resultado do código digitado é o seguinte:

48 Treinamento avançado em Excel

cap_06.p65

48

17/6/2004, 10:39

• CAPTURANDO RESPOSTAS DO USUÁRIO Na maioria das vezes, queremos que o usuário possa interagir com a caixa de mensagem, escolhendo clicar em um botão ou inserir algum dado. O VBA disponibiliza botões prontos para você incluir na sua caixa de mensagem. Cada um deles tem um número correspondente, bem como um código VBA. Veja quais são eles na tabela a seguir: Botão Botão

Número Número correspondente correspondente

Códig o A Código VBA Códig o VB VB A Código VBA

1 2 3 4 5 6 7

VbOk VbCancel VbAbort VbRetry VbIgnore VbYes VbNo

OK Cancelar An ular Anular Repetir Ignor ar Ignorar Sim Não

O código a seguir gera uma caixa de mensagem com três boular ar tões: An Anular ular, Repetir e Ignor Ignorar ar:

Sub TestaResposta () Resposta = MsgBox(“Pressione um dos _ botões abaixo:”, vbAbortRetryIgnore, _ “ATENÇÃO!!”) If Resposta = 3 Then MsgBox “Você pressionou ANULAR.” End If End Sub Nessa caixa de mensagem, o usuário deve clicar em um dos três botões:

Exibindo mensagens ao usuário 49

cap_06.p65

49

16/6/2004, 17:38

O código do botão selecionado pelo usuário será armazenado em uma variável chamada Resposta. Se o usuário pressionar o botão An ular Anular ular, o valor armazenado na variável será 3, e será gerada uma nova mensagem.

Função INPUTBOX Um tipo de caixa de mensagem ainda mais interativo é aquele em que o usuário pode inserir dados. Para isso, você pode usar a função INPUTBOX. Vejamos um exemplo. O código Sub Entrada() Resp = InputBox(“Qual é a palavra-chave?”) Range(“A1”) = Resp End Sub , faz com que apareça a caixa de mensagem a seguir, em que o usuário deve inserir determinada palavra-chave:

Neste outro exemplo, temos uma caixa de diálogo em que o usuário deve inserir um valor de salário:

50 Treinamento avançado em Excel

cap_06.p65

50

16/6/2004, 17:38

• ADICIONANDO COMENTÁRIOS AO CÓDIGO Comentários são trechos do código que não são executados, servindo apenas para manter uma documentação sobre ele. Eles são muito úteis, inclusive para a própria pessoa que criou o código – por exemplo, se você tiver que atualizar o programa depois de muito tempo, vai ter condições de entender o que está expresso nele. Para fazer com que um trecho do código seja um comentário no VBA, basta colocar um apóstrofo (‘) no início da linha, como no exemplo a seguir: Sub Entrada() Resp = InputBox(“Qual é a palavra‘chave?”,”Senha”) ‘Armazena em Resp (variável) a resposta da ‘pergunta de INPUTBOX Range(“A2”) = Resp ‘Armazena na célula A1 a resposta existen ‘te na variável End Sub

Exibindo mensagens ao usuário 51

cap_06.p65

51

16/6/2004, 17:38

Criando um formulário no VBA No dia-a-dia, não queremos nem ouvir falar em formulários: imediatamente nos vem à mente qualquer idéia relacionada com burocracia... Mas, na hora de criar interfaces de macros, inclusive no Excel, os formulários são essenciais. Eles funcionam, grosso modo, como caixas de mensagem turbinadas – muito turbinadas. Com um formulário, você pode coletar dados do usuário, pedir que ele escolha entre opções pré-existentes, entre muitas outras atividades interativas. E as informações recolhidas podem ser usadas para preencher uma planilha, por exemplo. Neste capítulo você vai aprender a criar e configurar formulários, bem como associar os campos e controles deles com planilhas.Tudo isso, claro, com os recursos do nosso velho conhecido VBA.

• CRIANDO UM FORMULÁRIO ir > No editor do Visual Basic do Excel, acesse o comando Inser Inserir UserForm. UserForm

Você pode definir características como nome, cor, estilo de borda, fonte de textos, entre outras, para o seu formulário. Para facilitar essas configurações, ative a janela Propriedades com o comando Exibir > Janela ‘Pr opr iedades’ ‘Propr opriedades’ iedades’, ou usando a tecla F4.

52 Treinamento avançado em Excel

cap_07.p65

52

17/6/2004, 10:40

• A BARRA DE FERRAMENTAS DE FORMULÁRIOS

Para que o usuário possa interagir com o formulário, você deve incluir controles nele – isto é, botões, caixas de textos etc. A maneira mais fácil de fazer isso é usar a opção Exibir > Caixa de ferramentas ferramentas, na qual basta clicar sobre o controle desejado para que ele passe a fazer parte do formulário.

A tabela a seguir apresenta os controles que podem ser incluídos no seu formulário: Controle

Nome Selecionar objetos Rótulo Caixa de texto Caixa de combinação

Utilidade Permite selecionar um ou mais objetos dentro do formulário. Insere títulos e textos explicativos dentro do formulário. Permite que o usuário insira dados em um campo. Utilizado para criar uma lista de dados, permitindo a escolha de um deles.

Criando um formulário no VBA 53

cap_07.p65

53

16/6/2004, 17:37

Caixa de listagem

Caixa de seleção Botão de opção Botão de ativação Moldura

Botão de comando

Faixa de tabulação

Multipágina

Barra de rolagem

Barra de rotação

Imagem

RefEdit

Semelhante à caixa de combinação, mas com a diferença de que os dados são exibidos em mais de uma linha ao mesmo tempo. Utilizado para criar uma opção de seleção. Utilizado para criar um botão que seleciona uma determinada opção. Utilizado para criar um botão que pode ser ativado ou não. Utilizado para criar uma moldura em volta de um conjunto de controles, agrupando-os. Utilizado para criar um botão de comando, como OK OK, Cancelar Cancelar, Salvar etc. Utilizado para apresentar um conjunto de informações em faixas separadas. Utilizado para apresentar várias guias de dados, cada uma com informações diferentes. Você pode, por exemplo, visualizar os dados pessoais de um cliente em uma guia, e os dados comerciais em outra. Utilizado para criar uma barra de rolagem no sentido horizontal ou vertical. Utilizado para incrementar valores em controles de textos (como número de prestações, % de juros etc.). Utilizado para inserir imagens no formulário. São aceitos formatos como .jpg, .bmp, .ico, entre outros. Utilizado para exibir o endereço de um intervalo de células inserido ou selecionado em planilhas.

54 Treinamento avançado em Excel

cap_07.p65

54

16/6/2004, 17:37

• INSERINDO UM CONTROLE Agora que você já conhece os controles que podem ser inseridos em um formulário, veja como incluí-los: 1 Na Caixa de ferramentas ferramentas, dê um clique sobre o controle desejado. 2 Na janela UserForm UserForm, posicione o cursor sobre o ponto em que deseja inseri-lo. 3 Clique e arraste o mouse sobre a tela para desenhar o controle.

Criando um formulário simples A Caixa de ferramentas facilita muito o trabalho de inserção de controles. Que tal colocar a mão na massa e partir para a criação de um formulário simples? Tente criar um modelo como o apresentado na figura a seguir:

Depois de construir o formulário, guarde-o.Vamos voltar a usá-lo mais adiante.

Criando um formulário no VBA 55

cap_07.p65

55

16/6/2004, 17:37

• ALTERANDO O NOME DOS CONTROLES Para capturar os dados que o usuário inserir nos controles do seu formulário, é necessário nomear os controles. Do contrário, não será possível referir-se a eles mais tarde. O nome de um controle é definido por sua propriedade Name Name, como descrito nos passos a seguir: UserForm, dê um clique sobre o controle desejado. 1 Na janela UserForm Propriedades, dê um clique sobre a propriedade Name Name. 2 Na janela Propriedades 3 Digite o nome que você quer dar ao controle. Uma dica: para facilitar a identificação dos controles, é interessante padronizar a nomenclatura. Um padrão muito utilizado é o de iniciar o nome com três letras indicando qual o tipo de controle criado. Por exemplo,lblVeículo: nesse caso, as letras“lbl” indicam que o controle é do tipo rótulo (em inglês, label); e o nome específico desse controle do tipo rótulo está expresso logo em seguida (Veículo).

A propriedade Caption Certos controles, como rótulo, botão de opção, caixa de texto, moldura e botão de comando, possuem a propriedade Caption Caption, a qual permite acrescentar um texto de apresentação do controle. Suponha que você tenha uma caixa de texto na qual o usuário deva digitar o nome dele. Para que ele saiba o que deve fazer, é necessário que exista um texto explicativo, como “Digite aqui o seu nome”. Esse texto é definido na propriedade Caption Caption.

• ALTERANDO OS CONTROLES DO FORMULÁRIO Agora chegou a hora de retomar o formulário que você criou há pouco.Você vai definir os nomes e os captions de alguns controles, seguindo a orientação da tabela a seguir:

56 Treinamento avançado em Excel

cap_07.p65

56

16/6/2004, 17:37

Objeto

Name

Caption

label1

lblNome

Nome:

Textbo x1 xtbox1

txtNome

Commandbutton1

cmdOk

OK

Commandbutton2

cmdCancelar

Cancelar

Label2

lblVeículo

Veículo:

ListBox1

ltbVeículo

Frame1

frmPagamento

Opções de pagamento

OptionButton1

optVista

À vista

OptionButton2

optParcelado

Financiado

label3

lblParcelas

Nº de parcelas

TextBo x2 xtBox2

txtParcelas

SpinButton1

spiParcelas

Label5

Preço:

CheckBox1

chkNovo

Label4

LBLPreço

Cliente novo [em branco]

Inserindo uma imagem Como fazer para incluir uma imagem em seu formulário? É simples. gem Você já sabe que o controle correspondente é o Ima Imag em. Aproveite o passo-a-passo a seguir para definir a imagem que deve ser associada ao controle Imagem que você já inseriu no seu formulário: 1 No formulário que você criou, dê um clique sobre o controle de imagem.

Criando um formulário no VBA 57

cap_07.p65

57

16/6/2004, 17:37

2 Na janela Propriedades Propriedades, vá até a propriedade Picture e clique no botão com as reticências. 3 Será aberta uma janela de navegador, para que você escolha a imagem a ser inserida. Procure o arquivo de imagem desejado e dê um duplo clique sobre ele. 4 Para que a figura não apareça cortada, altere a propriedade eSizeMode PictureSizeMode eSizeMode. Assim, a figura vai se adaptar ao tamanho da caixa. Pictur

Propriedades de texto Depois de alterar as propriedades Name e Caption dos controles, o seu formulário terá esta aparência (exceto pela figura, que será aquela que você tiver selecionado):

Agora, você deve definir uma propriedade para o objeto do tipo caixa de texto txtNome. Para permitir que o nome do cliente seja digitado em mais de uma linha, defina a propriedade MultiLine como True ue.Para isso, basta dar um clique sobre o controle txtNome no seu formulário e alterar a propriedade desejada na janela Propriedades Propriedades.

Alterando a ordem dos controles Com o formulário já construído, você pode navegar pelos controles existentes usando a tecla Tab. Com ela, os objetos são selecionados de acordo com a ordem em que foram criados. Mas é possível alterar essa ordem, para, por exemplo, torná-la compatível com a das colunas de uma planilha. Para isso, vá ao menu Exibir > Ordem de tabulação tabulação.

Preparando Preparando o o ambiente ambiente dos dos dados dados Na janela que aparece, clique sobre o controle que deseja mudar de posição e, depois, sobre os botões Mover para cima ou Mover para baixo baixo. 58 Treinamento avançado em Excel

cap_07.p65

58

16/6/2004, 17:37

Nosso intuito é relacionar o formulário que criamos com planilhas do Excel. Só para ter uma idéia de como ele vai ficar sobre a planilha, clique sobre o botão . Depois, feche o formulário sobre a planilha para retornar ao editor do VBA. Na planilha, vamos preparar o ambiente em que os dados serão recebidos. 1 Construa a seguinte planilha:

2 Nomeie a planilha como Cadastro.

Preparando a tabela com os dados dos veículos Em Plan2 devemos digitar a tabela com os veículos que temos na revendedora:

1 Renomeie a planilha como Veículos. 2 Selecione o intervalo A2:C19 e nomeie-o como VEÍCULOS.

Associando a tabela de veículos com o controle 1 No Editor do Visual Basic, selecione o objeto lbtVeículos.

Criando um formulário no VBA 59

cap_07.p65

59

16/6/2004, 17:37

2 Esse controle deverá apresentar a tabela de produtos que está na planilha Veículos. 3 Defina a propriedade Ro wSour ce como Veículos (nome da área RowSour wSource nomeada). 4 Para que sejam apresentadas as duas colunas com o modelo e o ano do veículo, altere a propriedade ColumnCount para 2. 5 Para exibir os títulos altere a propriedade ColumnHeads para True.

Exibindo o formulário Agora que já fizemos a associação entre o formulário e a planilha, vamos fazer com que a planilha adequada seja selecionada, e o formulário, exibido. Isso será feito digitando-se algumas linhas de código no editor do VBA: 1 Na janela Projeto, ative a janela de código com este botão:

2 Digite o seguinte código: Sub Cadastro() Sheets(“Cadastro”).Select ‘Selecionar a planilha Cadastro. frmVeículos.Show ‘Exibir o formulário com os veículos. End Sub 3 Esse código vai posicionar o cursor sobre a planilha Cadastro (Plan1) e abrir o formulário frmVeículos. 4 Execute a macro para ver o formulário no centro da tela.

60 Treinamento avançado em Excel

cap_07.p65

60

16/6/2004, 17:37

• EXIBINDO O PREÇO DO VEÍCULO No formulário frmVeículos existe um controle com o nome lblPreço. Ele vai servir para exibir o preço de determinado automóvel quando o usuário selecioná-lo a partir da lista de veículos. Portanto, vamos adicionar código ao controle ltbVeículo. Dê um duplo clique no controle ltbVeículo, para ir à janela de código. Digite estas linhas: Private Sub ltbVeículo_Click() Me.lblPreço.Caption = Sheets(“Veículos”). ‘Ao ser selecionado um veículo da lista, no ‘formulário atual (Me), altere o caption no ‘controle lblPreço Range(“c” & Me.ltbVeículos.ListIndex + _ 2).Value ‘Acesse a coluna C da planilha Veículos, ‘adicionando 2 ao índice, pois o índice inicial ‘é zero. Coluna A + 2 = C. ‘Exibe o valor do veículo. End Sub

Descarregando o formulário da memória Quando o usuário clicar sobre o botão Cancelar Cancelar, o formulário deverá ser removido da memória, ou, em outras palavras, descarregado. Para que isso aconteça, você deve associar um código a esse botão. Dê um clique duplo sobre ele para abrir a janela de código. Nela, digite: Private Sub cmdCancelar_Click() ‘Quando o botão Cancelar for pressionado, ‘o formulário deverá ser descarregado. Unload Me ‘Descarrega o objeto atual (Me). End Sub Você deve ter reparado que não associamos comandos a todos os controles do formulário. Isso porque controles como o botão de OK OK, por exemplo, exigem alguns conhecimentos que serão apresentados nos próximos capítulos.Mas não desanime! Ao terminar o livro, você terá um formulário totalmente funcional,e, o melhor,inteiramente feito por você.

Criando um formulário no VBA 61

cap_07.p65

61

16/6/2004, 17:37

Manipulando células Manipulando células Hora de mais diversão! Se,no capítulo anterior,você gostou de criar uma planilha que interage com um formulário, certamente vai ficar ainda mais feliz com o que as macros podem fazer pelo seu sistema de cadastro. Neste capítulo, você vai fazer o cursor do Excel movimentar-se sozinho pela planilha, enquanto os dados são coletados e manipulados – tudo automaticamente! Já pode começar a se sentir um programador emVBA:basta seguir os passo-apassos e prestar atenção à sintaxe dos objetos e propriedades.

• MÉTODOS DE MANIPULAÇÃO DE CÉLULAS Podemos deslocar o cursor de uma célula para outra simplesmente usando o cursor ou o mouse, ou ainda a Caixa de nome. Mas, suponha que você queira automatizar uma rotina que some os valores das células B2, C4 e G5, por exemplo. Para isso, o cursor deverá deslocar-se entre essas células e somar os seus valores. Usando alguns objetos e propriedades do VBA, podemos facilmente criar uma macro que realize esse deslocamento do cursor. De forma semelhante, você pode usar uma macro para inserir fórmulas em células específicas. O objetivo deste capítulo é que você se familiarize com o VBA.Portanto, você só vai voltar a usar o seu sistema de cadastro no final.

Verificando a área selecionada Em primeiro lugar, você vai criar uma macro que indica qual é o intervalo de células que está selecionado no momento. Por enquanto, você não vai usar o sistema de cadastro. Portanto, faça o seguinte: 1 Abra um novo documento no Excel e vá até o editor do VBA. Na janela de código, digite: Sub VerSeleção() MsgBox ActiveWindow.RangeSelection.Address `Na janela atual, área selecionada, endereço End Sub

62 Treinamento avançado em Excel

cap_08.p65

62

16/6/2004, 17:36

2 Para fazer um teste, volte para a planilha, rode a macro VerSeleção, e você verá uma caixa de mensagem como esta:

Inserindo fórmulas em uma célula Suponha que você queira inserir uma fórmula na célula D6 de uma planilha chamada Cadastro. Uma possibilidade interessante é usar o objeto Range e sua propriedade Formula Formula. O objeto Range define um intervalo de células, e a propriedade Formula permite associar uma fórmula a esse intervalo. e e For mula orm ula: Vamos ver um exemplo de como utilizar Rang Range 1 Em um novo documento do Excel, ou no mesmo em que você inseriu a macro VerSeleção,vá até a janela de código do editor de VBA e digite: Sub DigitarFormula() Worksheets(“Cadastro”).Range(“D6”).Formula _ = “=SUM(D2:D5)” End Sub Esse código fará com que a fórmula =SUM(D2:D5) seja inserida na célula D6.

• O MÉTODO SELECT E A PROPRIEDADE SELECTION O método Select ativa planilhas e objetos. Já a propriedade Selection retorna um objeto que representa a área selecionada na planilha ativa. Quando utilizamos o gravador de macros, o método Select e a propriedade Selection são ativados automaticamente.Veja um exemplo: Sub Selecionar() Sheets(“Sheet1”).Select Range(“A1”).Select ActiveCell.FormulaR1C1 = “Name”

Manipulando células 63

cap_08.p65

63

16/6/2004, 17:36

Range(“B1”).Select ActiveCell.FormulaR1C1 = “Address” Range(“A1:B1”).Select Selection.Font.Bold = True End Sub Outra maneira de realizar a mesma atividade efetuada por essa macro é sempre utilizar o objeto Sheet1 e fazer várias atividades ao mesmo tempo, sem a necessidade de indicar que o mesmo deve ser selecionado. Isso pode ser feito com a seguinte macro: Sub Labels() With Worksheets(“Sheet1”) .Range(“A1”) = “Name” .Range(“B1”) = “Address” .Range(“A1:B1”).Font.Bold = True End With End Sub

• EXEMPLOS DE UTILIZAÇÃO DO MÉTODO RANGE e é um dos métodos mais importantes na seleção de O Rang Range células. Veja um exemplo de como utilizá-lo (dessa vez, sem mexer na planilha que você criou anteriormente): Sub VerRange() Range(“A1”, “D1”).Select Range(ActiveCell, “B6”).Select Range(“B3:C8”).Select Range(“B2:E4”).Name = “IntervaloDeTeste” Range(“IntervaloDeTeste”).Select Range(“B2”).Select ActiveCell.Range(“B2”).Select Range(“IntervaloDeTeste”).Range(“A1”).Select End Sub

64 Treinamento avançado em Excel

cap_08.p65

64

16/6/2004, 17:36

Vamos ver o que faz cada linha do código:

Sub VerRange() Range(“A1”, “D1”).Select

Range(ActiveCell, “B6”).Select

Range(“B3:C8”).Select

Manipulando células 65

cap_08.p65

65

16/6/2004, 17:36

Range(“B2:E4”).Name = “IntervaloDeTeste” Range(“IntervaloDeTeste”).Select

Range(“B2”).Select

ActiveCell.Range(“B2”).Select

Range(“IntervaloDeTeste”).Range(“A1”).Select

End Sub

66 Treinamento avançado em Excel

cap_08.p65

66

16/6/2004, 17:36

Propriedade Offset A propriedade Offset retorna um objeto Range Range, que nada mais é do que um intervalo deslocado a partir da célula atual. Sua sintaxe é: Expressão.Offset(RowOffset, ColumnOffset) Em que: RowOffset – é o número de linhas – que pode ser um valor positivo, negativo ou 0 (zero) – que o intervalo deve ser deslocado.Valores positivos deslocam o intervalo para baixo, e os negativos, para cima. O valor padrão é 0. ColumnOf fset – é o número de colunas – que pode ser um ColumnOffset valor positivo, negativo ou 0 (zero) – que o intervalo deve ser deslocado. Valores positivos deslocam o intervalo para a direita, e os negativos, para a esquerda. O valor padrão é 0. fset Veja um exemplo de utilização da propriedade Of Offset fset: ActiveCell.Offset(10,7).Select De acordo com essa linha de código, o intervalo será deslocado dez linhas para baixo e sete colunas para a direita, a partir da célula ativa.

• ATRIBUINDO VALORES ÀS CÉLULAS DA PLANILHA Agora chegou o momento de retomar o seu sistema de cadastro. O objetivo do formulário que você criou é possibilitar que o usuário insira dados que serão passados para a planilha associada. É o que vamos fazer agora. A primeira medida a tomar para que esse procedimento dê certo é garantir que, no início da atividade da macro, o cursor esteja sempre na primeira célula que deve receber algum valor (no nosso caso, a célula A5 da planilha Cadastro). Depois, você deve deslocar o cursor ao longo da planilha, para que as células recebam os valores dos campos do formulário. Para isso, siga estes passos: 1 Abra a sua pasta de trabalho e vá até a planilha Cadastro. Nomeie a célula A5 como ini.

Manipulando células 67

cap_08.p65

67

16/6/2004, 17:36

2 Dê um duplo clique no botão OK OK. 3 No editor do VBA, abra a janela de código e digite a instrução: Private Sub cmdOK_Click Application.Goto reference:=”ini” ‘Posiciona o cursor no início da área de ‘cadastro. ActiveCell.Value = Me.txtNome.Text ‘A célula atual recebe o valor do controle ‘txtNome. ActiveCell.Offset(0,1).Activate ‘Desloca o intervalo uma célula para a direita. ActiveCell.Value = Me.ltbVeículo.Value ‘A célula atual recebe o valor do controle ‘ltbVeículos. ActiveCell.Offset(0,1).Activate ‘Desloca o intervalo uma célula para a direita. ActiveCell.Value = Me.lblPreço.Caption ‘Recebe o conteúdo de lblPreço. ActiveCell.Offset(0,1).Activate ‘Desloca o intervalo uma célula para a direita. ActiveCell.Value = Me.optVista.Value ‘Armazena o valor de optVista. ActiveCell.Offset(0,1).Activate ‘Desloca o intervalo uma célula para a direita. ActiveCell.Value = Me.txtParcelas.Value ‘Armazena o valor de txtParcelas. ActiveCell.Offset(0,1).Activate ‘Desloca o intervalo uma célula para a direita. ActiveCell.Value = Me.chkNovo.Value ‘Armazena o valor de chkNovo. ActiveCell.Offset(1,-5).Activate ‘Desloca o cursor uma linha para baixo. ‘e cinco colunas para a esquerda. End Sub Com esse código, você consegue fazer com que o cursor se desloque pela planilha, de modo a inserir dados em células diferentes.Assim, caminhamos mais um pouco em direção ao sistema de cadastro finalizado. 68 Treinamento avançado em Excel

cap_08.p65

68

16/6/2004, 17:36

Variáveis de memória O que você comeu ontem no almoço? Não se lembra, hein?... Talvez você não tenha reservado um espaço na sua memória para guardar essa informação. Imagine que você o fizesse, e separasse um canto da sua memória para a informação chamada “o que eu comi ontem no almoço”. Hoje, o valor dessa informação poderia ser “dobradinha”. Amanhã, o valor pode ser outro, como “frango xadrez”. Bem, no caso de nossa humilde equipe, esse valor varia conforme o cardápio do restaurante por quilo. Mas, independentemente do fator de variação do valor, o espaço na memória estará garantido, o que facilita muito o trabalho com programação. Neste capítulo, você verá como lidar com variáveis, que são elementos que recebem valores, os quais são armazenados no espaço reservado na memória. Por enquanto, vamos deixar um pouco de lado o formulário que você criou – mas isso não significa que ele será esquecido.

• CONCEITO DE VARIÁVEIS DE MEMÓRIA Uma variável é um valor que fica armazenado na memória do computador enquanto o procedimento está rodando. Assim que o procedimento termina, a variável é removida da memória – daí seu iáv el de memór ia nome ser var ariáv iável memória ia. As variáveis de memória no VBA armazenam valores de propriedades e resultados de cálculos.

• REGRAS PARA NOMEAÇÃO EM VBA Para nomear procedimentos, constantes, variáveis e argumentos em qualquer módulo, obedeça sempre às seguintes regras: • Não devem ser utilizados pontos, espaços, vírgulas, pontos de exclamação ou símbolos ($, @, # etc.).

Variáveis de memória 69

cap_09.p65

69

16/6/2004, 17:36

• O nome deve iniciar com uma letra. • O nome não deve exceder 255 caracteres. • Não devem ser utilizadas palavras reservadas do sistema, como, por exemplo, WorkSheet, que se refere a um objeto do Microsoft Excel.

• COMO DEFINIR VARIÁVEIS Para entender melhor como trabalham as variáveis, digite as seguintes linhas na janela de código do VBA: Sub ArmazenaVariáveis() MeuValor = 500 MeuTexto = “TEXTO” MeuBooleano = True MinhaData = #12/3/2001# MinhaMoeda = 12.5 ‘Atribuindo valores a variáveis End Sub Nesse exemplo, definimos cinco variáveis de tipos diferentes. Para ver como elas são armazenadas na memória, observe a janela Local Local, que pode ser acessada com o comando Exibir > Janela > Var iáv eis locais: ariáv iáveis

Logo ao acessar a janela Local Local, você vai notar que ela ainda não percebeu a existência das variáveis atribuídas. Para que ela o faça, é necessário

70 Treinamento avançado em Excel

cap_09.p65

70

17/6/2004, 10:41

solicitar ao VBA que execute o script passo a passo, o que pode ser feito pressionando a tecla F8. Depois de fazer isso, você verá que o Excel consegue definir cada uma das variáveis, mas continua parado na primeira linha do procedimento. Isso acontece porque as variáveis foram definidas, mas ainda não têm valores associados. Pressione F8 sucessivas vezes para que todas as variáveis sejam vistas:

Quando o procedimento chega ao fim, as variáveis são zeradas, e o módulo nem ao menos se lembra de que elas foram utilizadas. A janela Var iáv eis locais permite que você saiba qual valor está realmente arariáv iáveis mazenado na memória naquele instante. Essa propriedade é muito útil nos casos em que deparamos com um erro de cálculo e não sabemos o que o Excel está tomando como base. As variáveis podem armazenar valores e objetos. Para atribuir um valor a uma variável, podemos digitar seu nome, o sinal de igual e o valor que deve ser atribuído a ela:

Nomedavariável = valor

• DECLARANDO VARIÁVEIS O VBA não exige que as variáveis sejam declaradas, mas o ideal é fazê-lo sempre. Aliás, existe, no VBA, um recurso padrão para declarar as variáveis automaticamente, por meio da instrução Option Explicit Explicit, que veremos mais adiante.

Variáveis de memória 71

cap_09.p65

71

16/6/2004, 17:36

Ao declarar uma variável, estamos solicitando ao VBA que reserve um certo espaço na memória para armazenar o conteúdo dela. Existem diversos tipos de variável (dependendo do tipo de valor que ela pode receber, como caracteres, números, valores lógicos etc.). Especificandose o tipo da variável, minimiza-se o espaço de memória a ser reservado para ela. A declaração de variáveis é feita de acordo com esta sintaxe: Dim nomedavariável As tipodavariável Em que: Dim – é o termo proveniente de dimensionar, que, por sua vez, significa alocar. O Dim faz com que uma determinada quantidade de memória seja alocada para a variável. Como dissemos no início do capítulo, você pode utilizar a instrução Option Explicit (na seção de declarações, que fica no início do procedimento) para que o VBA passe a exigir que todas as variáveis sejam declaradas. Uma vez incluída essa instrução, o VBA verifica o código na primeira vez em que o procedimento de um módulo é executado, para assegurar que todas as variáveis tenham sido declaradas. Caso não encontre alguma das declarações, ele pára exatamente no ponto em que encontrou a variável não declarada, e retorna um aviso a respeito.

• ESCOPO DAS VARIÁVEIS O escopo de uma variável refere-se ao domínio dentro do qual ela pode ser acessada. O VBA permite que a variável tenha um destes níveis de escopo: • Disponível somente no procedimento; • Disponível somente no módulo; • Disponível somente na pasta de trabalho; • Disponível em todas as pastas de trabalho.

72 Treinamento avançado em Excel

cap_09.p65

72

16/6/2004, 17:36

Variáveis do nível de procedimento Para declarar uma variável cujo escopo seja restrito ao procedimento em que ela se encontra, utilize a instrução Dim Dim. Assim, ela ficará disponível somente nesse procedimento, e nenhum outro poderá acessá-la. Sub DefineVariáveis() Dim ctNome As String Dim curSalário As Currency Dim dtDataInício As Date .... End Sub

Variáveis do nível de módulo Para declarar uma variável de escopo restrito a um único módulo, iv ate inclua-a na seção de declarações usando a palavra-chave Pr Priv ivate ate: Option Private Module Private NomeLivro ate na seguniv No caso, poderíamos utilizar Dim no lugar de Pr Priv ivate da linha do código, mas, para enfatizar as variáveis declaradas no iv ate início do módulo, a instrução mais utilizada é a Pr Priv ivate ate.

Variáveis de nível global Para declarar uma variável disponível a todos os módulos exislic sem a instrutentes na pasta de trabalho, use a palavra-chave Pub Public iv ate Module ção Option Pr Priv ivate Module. O resultado será uma variável chamada Global Global, que deverá ser declarada na seção de declarações: Public strNomeLivro

• TIPOS DE VARIÁVEL Os tipos de variável disponíveis no VB são:

Variáveis de memória 73

cap_09.p65

73

16/6/2004, 17:36

cap_09.p65 16/6/2004, 17:36

iant é um tipo especial de variável, que pode conter qualquer (*) Var ariant tipo de dado. Se, na declaração de uma variável, não for especificado o iant ariant iant,que requer tipo, o Excel automaticamente irá criar uma variável Var mais espaço de memória do que os demais tipos.

74

74 Treinamento avançado em Excel

Tipo de dados

Prefixo

Exemplo

Memória requerida

Boolean

Bln

BlnReady

2 bytes

True ou false

Currency

Cur

CurCusto

8 bytes

922.337.203.685.477,5808 a 922.337.203.685.477,5808

Date

Dt

DtPgto

8 bytes

Double

Db

DbCont

8 bytes

1.797693134862232e308 a – 4.9406564584124e-324

Integer

Int

IntQtosFilhos

2 bytes

-32.768 a 32.767

Long

Lng

LngVendas

4 bytes

-2.147.483.648 a 2.147.483.648

Single

Sng

SngTaxaJuros

4 bytes

- 3.402823E38 a –1.401298E-45

String

Str

StrPrimNome 10 bytes + comprimento da string (texto)

Variant*

Vnt

VntResposta

16 bytes

Byte

Byt

BytTecla

1 byte

Intervalo de dados

1 de janeiro de 100 a 31 de dezembro de 9999

Zero a aproximadamente 2 bilhões de caracteres Byte 0 0a a255 255

Estruturas de programação Seus amigos o chamam para ir ao cinema em um dia cinzento.Você, resfriado, responde:“Se não chover eu vou, mas, se chover, prefiro ficar em casa...”. Pronto! Você acaba de usar uma estrutura de programação condicional. Como você vê, não é necessário ser um daqueles gênios dos filmes de ficção adolescente para compreender princípios de programação. Fluxos de código são mera questão de lógica.Você só precisa se familiarizar com a linguagem de programação e treinar um pouco. Neste capítulo, você verá como criar rotinas de programação que tomem diferentes decisões de acordo com a escolha do usuário, fazendo uso das estruturas de programação. No sistema de cadastro que você está construindo, por exemplo, vamos fazer com que o texto inserido na planilha varie conforme a opção que o usuário tiver escolhido no formulário.

• ESTRUTURAS CONDICIONAIS As estruturas condicionais criam situações em que o programa deve decidir por qual caminho seguir, de acordo com o resultado de um teste condicional.

As estruturas If As estruturas do tipo If disponibilizam opções para avaliar a entrada de dados do usuário, e executam afirmações em uma condição básica como resposta. Embora tenhamos três variações da estrutura If If, todas elas são iniciadas com a declaração If ... Then e, em geral, terminam com End If If. Essa declaração avalia se uma condição é verdadeira ou falsa, e direciona o fluxo do programa de acordo com o solicitado. Se mais de uma condição na estrutura é verdadeira, somente o código especificado como primeira condição verdadeira será executado. • If ...Then Em geral, usa-se essa estrutura quando o programa possui uma decisão do tipo “um ou outro”. Ela aceita dois tipos de sintaxe:

Estruturas de programação 75

cap_10.p65

75

16/6/2004, 17:35

If [condição] Then Claúsula Repare que, nesse modelo, não usamos a instrução End If If. Esse tipo de declaração pode ser usado nos casos em que a estrutura If se resumir a uma simples linha, como neste exemplo: Sub PreencheDatas() Dim MinhaData As String MinhaData = InputBox(“Digite o mês no _ formato MMM-AA”) If MinhaData <> “” Then MsgBox “Continuar _ a macro” End Sub If [condição] Then Claúsula End Sub Nesse segundo tipo de sintaxe, a cláusula da estrutura não está na mesma linha em que se encontra a palavra Then Then. Ou seja, a estrutura se estende por mais de uma linha. Nesse caso, devemos fechála com End If If, como no exemplo a seguir: Sub Demonstra If() If ActiveSheet.Name <> “EXEMPLO” Then MsgBox “Você está na planilha errada!” End If End Sub • Aplicando a estrutura If no formulário Vamos, agora, aplicar o que aprendemos sobre If no sistema de cadastro que você criou. No formulário de cadastro, é necessário gamento para definir qual verificar o conteúdo de Opções de pa pagamento caminho o programa vai seguir: se o usuário selecionar a opção À vista vista, deverá ser inserida a palavra À vista na célula; caso contrário, deverá ser inserida a palavra Financiado. Para fazer isso, siga este passo-a-passo:

76 Treinamento avançado em Excel

cap_10.p65

76

16/6/2004, 17:35

1 Abra o arquivo com o sistema de cadastro. No editor do VBA, dê um duplo clique sobre o botão cmdOK, para acessar seu código. 2 Agora você vai adicionar uma estrutura If para testar se a caixa de opção optVista está ativada. Portanto, precisamos substituir, no código, as linhas referentes ao controle optVista por novas instruções. Para fazer isso, procure as seguintes linhas no código do botão cmdOK: ActiveCell.Value = Me.optVista.Value ‘Desloca o intervalo uma célula para a direita. ‘Armazena o valor de optVista. E substitua-as por estas: If Me.optVista = True Then ‘verifica se optVista ‘está ativado ActiveCell.Value = “À vista” ActiveCell.Offset(0,1).Select ActiveCell.Value = “0” ‘número de parcelas ‘é 0 txtParcelas.Enabled = False Else ActiveCell.Value = “Financiado” ActiveCell.Offset(0,1).Select ActiveCell.Value = Me.spiParcelas.Value End If 3 Com essas linhas de código, se o usuário selecionar a opção optVista, a célula correspondente na planilha será preenchida com o valor “À vista”. Do contrário, será preenchida com o valor “Financiado”. Vamos, agora, inserir instruções para verificar se a caixa chkNovo foi ativada. Ainda no código para o botão cmdOK, procure estas linhas:

ActiveCell.Value = Me.chkNovo.Value ‘Desloca o intervalo uma célula para a direita. ‘Armazena o valor de chkNovo. E substitua-as pelas seguintes:

Estruturas de programação 77

cap_10.p65

77

16/6/2004, 17:35

If Me.chkNovo.Value = True Then ActiveCell.Value = “SIM” Else ActiveCell.Value = “NÃO” End If Essas linhas fazem com que, se o usuário for um cliente novo, a célula correspondente na planilha seja preenchida com o valor SIM e, se não for, ela seja preenchida com o valor NÃO.

A estrutura Case Select Esta estrutura é ideal para testar um valor dentre vários possíveis. Sua sintaxe é: Select Case [expressão] Case [expressão 1] Declaração Case [expressão 2] Declaração ... … Case [else] Declaração End Select A estrutura Case Select avalia o resultado da expressão uma única vez, comparando-o, depois, com outros valores, para determinar qual bloco de código será chamado. Vamos utilizar Case Select para manipular os dados a respeito do número de parcelas em nosso sistema de cadastro. Siga estes passos: 1 No código do botão cmdOK, procure esta linha:

ActiveCell.Value = Me.spiParcelas.Value E, logo abaixo dela, acrescente estas: Select Case txtParcelas Case 1 MsgBox (“Somente 01 parcela.”) Case 2

78 Treinamento avançado em Excel

cap_10.p65

78

16/6/2004, 17:35

MsgBox (“Tome cuidado... os juros vão _ aumentar.”) Case 3 MsgBox (“Nossa, aqui não é Kazas do _ Norte!”) Case 4 MsgBox (“Tá mal... agora agüenta!”) End Select Em uma estrutura Select Case Case, somente uma das declarações disponíveis será executada.

• ESTRUTURAS DE LOOPING Diferentemente das estruturas condicionais, as de looping (ou de repetição) permitem que uma mesma instrução seja executada repetidas vezes. É possível estipular um número definido de vezes que o programa deve rodar, ou atrelar esse valor a uma condição.

Estrutura Do ... Loop Esta estrutura pode ser utilizada nos casos em que desejamos repetir um mesmo bloco de instruções até que determinada condição seja satisfeita, ponto em que o código sai do loop e passa a executar a próxima instrução. Para sair de uma estrutura Do ... Loop deve-se utilizar a instrução Exit Do Do. O ideal é usá-la após a avaliação de alguma condição, como, por Then. Nesse caso, se a condição for verdadeira, o procediexemplo, If ...Then mento passará para a instrução existente imediatamente após o loop. Do [bloco de instruções] Loop condição Veja um exemplo de estrutura Do ... Loop Loop, que realiza o loop três vezes consecutivas: Sub TestandoDoLoop() Dim Contador As Integer ‘Declara a variável Contador,

que

irá

Estruturas de programação 79

cap_10.p65

79

16/6/2004, 17:35

‘armazenar números inteiros. Contador = 0 ‘ Inicializa a variável Contador. Do ‘ Inicia o loop MsgBox “O contador está em: “ & Contador Contador = Contador + 1 ‘ Incrementa Contador. If Contador = 3 Then ‘ Se a condição for True, MsgBox “Contador foi ativado três vezes” Exit Do ‘ sair da instrução Do. End If Loop Until Contador > 3 MsgBox “Saímos do loop agora.” End Sub Para visualizar o funcionamento desse procedimento, vá até a primeira linha do código e pressione a tecla F8. Você verá que o Excel lê e executa a linha de código, passando então para a linha seguinte, e prosseguindo até o final do bloco de instruções.

Estrutura For ... Next xt faz com que um bloco de instruções rode A estrutura For ... Ne Next um número predefinido de vezes. Esse número é estabelecido logo na xt primeira linha. A última linha deve conter apenas a palavra Ne Next xt. Entre For ... Ne xt essas duas instruções (F Next xt), insere-se o código a ser repetido. For Condição [bloco de instruções] Next No exemplo a seguir, criamos um contador (representado por uma variável) para controlar o número de vezes que o bloco de instruções deve ser executado. Confira:

80 Treinamento avançado em Excel

cap_10.p65

80

16/6/2004, 17:35

Sub DemonstraNext() Dim Contador As Integer ‘Define a variável Contador como um número ‘inteiro. ActiveSheet.Range(“A1”).Select ‘Posiciona o cursor em A1. For Contador = 1 To 10 ‘Executa a mesma instrução até que Contador ‘atinja o valor 10. ActiveSheet.Range(“A” & Contador).Select ‘Seleciona a coluna A + Contador (cujo ‘valor inicial é 1). ActiveCell.Value = Contador ‘Atribui o valor de Contador à célula A1. Application.StatusBar = “Estamos na linha _ “ & Contador ‘Exibe na barra de status o texto ‘“Estamos na linha:” + o valor de Contador. Next ‘Executa as mesmas instruções novamente. MsgBox (“Fim do contador”) ‘Após executar o bloco 10 vezes, exibe a ‘mensagem “Fim do contador”. End Sub Nesse código, repare no seguinte: or, seguida do nome da variável • O loop começa com a palavra For Contador e do valor inicial para a mesma. • Cada vez que o loop é processado, acrescenta-se uma unidade à variável Contador e é exibido o resultado na barra de status.

Estrutura For Each … Next Ao contrário da estrutura For ... Ne Next Each xt comum, a For Eac h ... Ne xt não exige que se defina o número máximo de vezes que o Next loop deverá ser executado. Por isso, ela permite trabalhar com todos os objetos existentes em uma coleção. Basta definir uma variável para representar o objeto desejado e sua respectiva coleção.

Estruturas de programação 81

cap_10.p65

81

16/6/2004, 17:35

For each Condição [bloco de instruções] Next h ... Ne xt Veja um exemplo de utilização do For Eac Each Next xt: Sub DemonstraForEach() Dim Plan As Object ‘Declara a variável Plan como tipo Objeto. Workbooks.Add ‘Adiciona nova planilha. Worksheets.Add ‘Adiciona uma nova guia. For Each Plan in This Workbook.sheets ‘Para cada planilha existente na pasta, Range(“A1”).Value = Plan.Name ‘a primeira célula irá conter o nome ‘da planilha. Next ‘Muda de planilha. End Sub

Outros exemplos de criação de loops As estruturas de looping podem ser usadas para aplicar inúmeros recursos aos seus programas.Veja um exemplo interessante:

Listando arquivos existentes Vejamos agora como verificar a existência de determinado arquivo .xls em um diretório, e exibir seu nome na primeira linha de uma planilha: Sub ListaArqs() Dim Linha As Integer Dim Arquivo As String ‘Define as variáveis Linha e Arquivo. Linha = 1 Arquivo = Dir(“*.xls”) ‘Atribui valores às variáveis. Cells(Linha, 1) = Arquivo ‘Atribui o conteúdo da variável Arquivo à ‘célula (1,1). Linha = Linha + 1 ‘Acrescenta 1 ao contador de linhas. Arquivo = Dir 82 Treinamento avançado em Excel

cap_10.p65

82

16/6/2004, 17:35

‘Atribui o nome do arquivo (diretório) à ‘variável Arquivo. Cells(Linha, 1) = Arquivo ‘A célula atual exibe o nome do arquivo ‘(conteúdo da variável). End Sub

Usando estrutura Do ... Until O código do exemplo anterior mostrava apenas um arquivo do diretório (no caso, o primeiro de uma lista). Se você quisesse prolongar esse mesmo código para que ele listasse todos os elementos de uma lista contendo 200 arquivos, ele ficaria imenso, e o trabalho exigido seria desumano. Para conseguir esse resultado de maneira inteligente e sem Until, de modo que a execução do suar a camisa, use a estrutura Do ... Until bloco de instruções seja repetida até a aplicação não encontrar mais nenhum arquivo .xls no diretório. Para isso, utilize este código: Sub ListaArqs() Dim Linha As Integer Dim Arquivo As String ‘Declara a existência das variáveis Linha ‘e Arquivo. Linha = 1 ‘Atribui o valor 1 à variável Linha. Arquivo = Dir(“*.xls”) ‘Armazena o nome do arquivo .xls na variável ‘Arquivo. Do Until Arquivo = “” ‘Repete o bloco de instruções até que ‘a variável Arquivo esteja em branco. Cells(Linha, 1) = Arquivo ‘Atribui à célula atual o conteúdo da ‘variável Arquivo. Linha = Linha + 1 ‘Incrementa o contador de linhas (Linha) ‘em uma unidade. Arquivo = Dir ‘Armazena o conteúdo de Dir (diretório) ‘na variável Arquivo. Loop ‘Caso encontre outros arquivos, repete o ‘bloco de instruções. End Sub Estruturas de programação 83

cap_10.p65

83

16/6/2004, 17:35

Criando funções O Excel tem mais de 400 funções. É função a rodo! A maioria dos usuários conhece apenas uma parte delas, já que algumas atendem a necessidades muito específicas, de interesse para poucas pessoas. Mas, mesmo dispondo dessa enorme quantidade de funções, você pode querer ou precisar de alguma que não conste da lista. O que fazer? É simples: crie a sua própria função! Veja, neste capítulo, como desenvolver o código para uma função de cálculo de financiamento simples. Uma vez criada, ela poderá ser usada quantas vezes você quiser, exatamente como qualquer função nativa do Excel.

• CRIANDO FUNÇÕES Até aqui, você trabalhou com a instrução Sub Sub, com a qual desenvolveu suas macros. Para criar funções, vamos começar a usar a instrução Function Function. As funções criadas ficam armazenadas na lista de funções do Excel, juntamente com as demais, como SOMA, MÉDIA, DATA etc.. Elas modificam apenas valores nas planilhas, nunca sua estrutura ou características. Para criar uma função, devemos utilizar a seguinte sintaxe: Function NOME(Argumento1 As Tipo, Argumento 2 As Tipo, ...) As Tipo instruções Nome = resultado End Function Experimente criar a sua própria função, de acordo com a orientação a seguir: 1 No editor do VBA, crie um novo módulo usando o comando Inser ir > Módulo Inserir Módulo. 2 Digite estas linhas de código para criar a função FACADA:

84 Treinamento avançado em Excel

cap_11.p65

84

16/6/2004, 17:34

Function FACADA (Empréstimo As Currency,_ Juros As Single, Parcelas as Integer) As _ Currency ‘Esta função precisa de três argumentos: ‘Empréstimo – do tipo Currency (moeda), ‘Juros – do tipo simples e ‘Parcelas – do tipo inteiro FACADA = Empréstimo * (1 + Juros) ^ Parcelas ‘Ao entrar com os valores de empréstimo, ‘vai calcular o valor dos juros elevado ao ‘número de parcelas. ‘Todas as instruções serão armazenadas na ‘função conhecida como FACADA. End Function Vamos, agora, testar a função criada: 1 No Excel, construa a seguinte planilha:

ir função 2 Clique sobre o botão Inser Inserir função. Na janela que se abrir, no or ia campo Ou selecione uma categ categor oria ia, escolha a opção Todas odas:

Criando funções 85

cap_11.p65

85

16/6/2004, 17:34

3 A função que você criou (FACADA) vai estar listada no menu.

4 Procure-a e dê um clique sobre ela. Depois, preencha os campos requeridos com os intervalos de células adequados, como mostra a figura a seguir:

Dê OK para ver o resultado.

86 Treinamento avançado em Excel

cap_11.p65

86

16/6/2004, 17:34

Personalizando o sistema O Excel possui algumas barras de ferramentas e uma barra de menus padrão para agilizar o trabalho. Mas você também pode criar suas próprias barras e menus, reunindo nela as funcionalidades que costuma usar com mais freqüência. E mais: é possível criar novos botões para as macros que você porventura tenha desenvolvido no Excel. É como brincar com um jogo de montar, cujo resultado seja um ambiente de trabalho personalizado. Neste capítulo, você verá como criar e manipular barras de ferramentas e menus tanto por meio de linhas de código quanto por meio de comandos.

• ROTINA DE LIMPEZA DOS DADOS PARA NOVO CADASTRAMENTO Nos capítulos anteriores, você associou ações aos controles do seu sistema de cadastro. Assim, o usuário pode entrar no formulário e inserir os dados, os quais serão usados para preencher as planilhas correspondentes. O que falta, portanto, é um método que permita que, quando o usuário terminar de efetuar o cadastro, o sistema possa ser zerado. Isto é, temos de criar uma rotina que limpe todos os campos, de forma que, ao se reiniciar o sistema de cadastro, eles apareçam em branco para a digitação de novos dados. É muito simples fazer isso: 1 Abra o documento do sistema de cadastro. 2 No editor do VBA, dê um duplo clique no botão cmdOK. Sub, digite o 3 Na janela de código, logo acima da instrução End Sub seguinte bloco de instruções: Me.txtNome = “” Me.txtParcelas = “” Me.ltBVeículo.ListIndex = -1 Me.optVista = False Me.lblPreço = “” Personalizando o sistema 87

cap_12.p65

87

16/6/2004, 17:31

Me.lblVeículo = “” Me.chkNovo = False Me.txtNome = SetFocus

• CRIANDO UMA BARRA DE FERRAMENTAS Qualquer aplicativo do Windows pode conter várias barras de ferramentas; você pode, inclusive, criar uma, utilizando comandos ou por meio de códigos. Criar uma nova barra de ferramentas utilizando comandos é muito simples: 1 Em um novo documento do Excel, vá ao menu Fer errramentas > sonalizar er sonalizar ou Exibir > Bar ersonalizar sonalizar. errramentas > Per ersonalizar Barrras de ffer Per 2 Clique na guia Bar er va Barrr as de ffer errr amentas amentas, e em seguida, em No Nov 3 Na caixa Nome da bar er barrr a de ffer errramentas amentas, nomeie a sua barra OK: como CADASTRO DE VEÍCULOS e clique em OK

4 Clique na guia Comandos Comandos. 5 Selecione uma opção na caixa Categ or ias Categor orias ias. Repare que uma pequena barra de ferramentas vazia aparecerá ao lado da janela que você está usando. Essa é a sua nova barra de ferramentas. 6 A partir da caixa Comandos Comandos, arraste o comando desejado para a barra de ferramentas que você criou:

88 Treinamento avançado em Excel

cap_12.p65

88

16/6/2004, 17:31

7 Se houver necessidade de alterar o texto ou a figura do botão, icar seleção basta pressionar o botão Modif Modificar seleção. 8 Após arrastar todos os comandos desejados para a barra, clique har em Fec echar har.

Criando uma barra de menus internos Em vez de criar uma barra de comandos com botões, você pode construir uma barra de menus contendo diversos comandos. Mas lembre-se: é aconselhável manter em uso apenas a barra de menus padrão do Excel (conhecida como barra de menus da planilha). De qualquer modo, se sentir necessidade de uma barra personalizada, basta seguir estes passos: 1 Vá ao menu Fer sonalizar errramentas > Per ersonalizar sonalizar, e clique na guia Barra de ffer er errramentas amentas. 2 Clique no botão No va e nomeie a nova barra de ferramentas. Clique Nov or ias us inter nos na guia Comandos e, na caixa Categ Categor orias ias, selecione Men Menus internos nos. 3 A partir da caixa Comandos Comandos, arraste o menu desejado para a barra de ferramentas exibida. 4 Depois de adicionar todos os botões e menus desejados, clique har em Fec echar har.

• ANEXANDO UMA BARRA DE FERRAMENTAS AO ARQUIVO 1 Abra a pasta de trabalho à qual deseja anexar uma barra de ferramentas. 2 Vá ao menu Fer sonalizar errramentas > Per ersonalizar sonalizar, e clique na guia Barras de ffer er xar errramentas amentas. Em seguida, clique em Ane Anexar xar. 3 Clique na barra de ferramentas que deseja anexar ao arquivo e clique em Copiar Copiar. Obs.: Certifique-se de salvar a pasta de trabalho depois de anexar uma barra de ferramentas. Personalizando o sistema 89

cap_12.p65

89

16/6/2004, 17:31

• ANEXANDO MACROS A BOTÕES Uma possibilidade interessante é a de acrescentar botões personalizados à sua barra de tarefas. Você pode, por exemplo, anexar uma macro a um botão personalizado. Veja como fazer isso: 1 Com a sua barra de ferramentas personalizada aberta, vá até o sonalizar ersonalizar sonalizar. menu Fer errramentas > Per 2 Dê um clique na guia Comandos Comandos. or ias os Categor orias ias, selecione Macr Macros os. 3 Na caixa Categ 4 Clique em Per sonalizar botão e arraste-o para sua barra. ersonalizar icar seleção Modificar seleção. 5 Para atribuir uma macro ao botão, pressione Modif ibuir Atribuir ibuir. 6 Clique sobre o nome da macro desejada, e, depois, em Atr

• UTILIZANDO CÓDIGOS PARA ATIVAR A BARRA DE FERRAMENTAS Agora vamos criar uma rotina que ative a barra de ferramentas personalizada. Esse código deve ser associado ao procedimento Open do objeto Wor kbook orkbook kbook, para que possa agir assim que o arquivo for aberto. Para isso, siga este passo-a-passo: 1 Na janela de P rro ojeto o, dê um duplo clique em asta_de_T balho. EstaPasta_de_T asta_de_Trra balho EstaP 2 Depois, clique sobre a caixa Objeto (que deve estar mostrando al) kbook a opção (Ger (Geral) al)) e selecione a opção Wor orkbook kbook.

90 Treinamento avançado em Excel

cap_12.p65

90

16/6/2004, 17:31

3 Digite as seguintes instruções: Private Sub Workbook_Open() MsgBox “Bom dia; este é o sistema de _ cadastro de vendas.” ‘Mensagem de abertura Application.CommandBars(“CADASTRO DE _ VEÍCULOS”).Visible = True ‘Abrir barra de ferramentas caso esteja ‘fechada. End Sub Essas instruções serão executadas toda vez que o arquivo de formulário for aberto, exibindo uma mensagem ao usuário e tornando visível a barra de ferramentas criada.

• OCULTANDO A BARRA DE FERRAMENTAS Se você quiser deixar de visualizar alguma barra de ferramentas, basta ocultá-la. Isso pode ser feito por meio do comando Fer errramentas > Per sonalizar ersonalizar sonalizar, ou de linhas de código, desde que você saiba o nome da barra em questão. Veja um exemplo de como desabilitar uma barra de ferramentas usando código, o qual deverá ser digitado no procedimento Close Close, ou seja, ao fechar a pasta de trabalho: 1 Vá até a janela de código. Se ela não estiver com o objeto kbook ativado, acesse-o na caixa Objeto orkbook Objeto. Wor Sub, digite: 2 Logo acima da instrução End Sub Application.CommandBars(“Standard”).Visible = _ False Application.CommandBars(“Formatting”).Visible = _ False ‘Ocultar as barras de ferramentas Padrão e ‘Formatação.

Personalizando o sistema 91

cap_12.p65

91

16/6/2004, 17:31

• ALTERANDO A POSIÇÃO DE UMA BARRA DE FERRAMENTAS Veja agora como mudar a posição de uma barra de ferramentas na tela. No exemplo a seguir, vamos posicionar a barra personalizada CADASTRO DE VEÍCULOS na parte direita da área de trabalho do Excel: 1 Novamente no código do objeto Wor kbook orkbook kbook, logo acima da instrução End Sub Sub, digite: Sub MudaBarra() Application.CommandBars(“CADASTRO DE _ VEÍCULOS”). Visible = True ‘Torna visível a barra de ferramentas CADASTRO ‘DE VEÍCULOS. Application.CommandBars(“CADASTRO DE _ VEÍCULOS”). Position = msoBarRight ‘Posiciona a barra de ferramentas do lado ‘direito da janela. End Sub Você também pode posicionar a barra de ferramentas em outros pontos da tela; basta alterar o valor do parâmetro Position, como mostra a tabela abaixo: Position = msoBarLeft

Posiciona a barra à esquerda.

Position = msoBarRight

Posiciona a barra à direita.

Position = msoBarTop

Posiciona a barra no topo.

Position = msoBarFloating

Torna a barra flutuante.

Para barras flutuantes, devemos estipular suas margens e sua largura a partir das bordas esquerda e superior da janela. Veja, a seguir, um exemplo de instrução para definir as características de uma barra flutuante: .left = 500, .top = 200, .width = 100

92 Treinamento avançado em Excel

cap_12.p65

92

16/6/2004, 17:31

• DESABILITANDO UM ITEM DA BARRA Assim como podemos desabilitar uma barra de ferramentas, também é possível desabilitar apenas um ou mais itens de uma barra. Suponha que, no caso do seu sistema de cadastro, por exemplo, o usuário ainda não tenha inserido dados no formulário. Nesse momento, seria interessante desabilitar o botão de impressão, uma vez que ainda não existe nada para imprimir. A seguir, veja um exemplo em que ocultamos o segundo botão da barra personalizada CADASTRO DE VEÍCULOS: Sub, digite: 1 Na janela de código, logo acima da instrução End Sub Sub Oculta2() Application.CommandBars(“CADASTRO DE _ VEÍCULOS”). Controls(2).Enabled = False ‘Oculta o botão 2 da barra de ferramentas. End Sub

• INICIALIZANDO O SISTEMA É possível fazer com que, toda vez que você iniciar um arquivo, suas barras de menus e de ferramentas sejam visualizadas. Para isso, kbook basta associar ao objeto Wor orkbook kbook, no procedimento Open Open, o seguinte código: Application.CommandBars(“CADASTRO DE VEÍCULOS”). _ Visible = True ‘Torna a barra de ferramentas CADASTRO DE VEÍCULOS ‘ativa. MsgBox “Bom trabalho!” ‘Exibe a mensagem “Bom Trabalho!”. frmVeículos.Show ‘Exibe o formulário frmVeículos.

Personalizando o sistema 93

cap_12.p65

93

16/6/2004, 17:31

• FINALIZANDO SEU SISTEMA DE CADASTRO Chegou a hora de aplicar os toques finais no seu sistema de cadastro.Temos de resolver apenas duas coisas: – Garantir que, assim que um cadastro seja finalizado, o cursor se mova para a primeira célula da linha seguinte; – Ativar o botão de rotação associado ao número de parcelas (spiParcelas). Mãos à obra!

Posicionando o cursor no primeiro registro em branco Até aqui, quando o usuário clicava sobre o botão OK do formulário, o cursor era transportado à célula nomeada como ini (no caso, a célula A5). Ou seja, cada novo cadastro sobrescrevia o anterior. Para que, em vez disso, o cursor seja posicionado na primeira célula em branco do sistema, faça o seguinte: 1 Na janela de código,localize o comando associado ao botão cmdOK. 2 Procure a seguinte linha de código (que está logo abaixo da instrução Sub cmdOK cmdOK): Application.Goto reference:=”ini” 3 Substitua essa linha pelo seguinte código: Range(“A1”).Select ‘Posiciona o cursor em A1. Selection.End(xlDown).Select ‘Posiciona o cursor na primeira célula em branco ‘após A1. Selection.End(xlDown).Select ‘Posiciona o cursor na primeira célula em branco ‘na área de dados. ActiveCell.Offset(1, 0).Range(“A1”).Select ‘Move o cursor uma linha para baixo da atual.

94 Treinamento avançado em Excel

cap_12.p65

94

16/6/2004, 17:31

Ativando o controle spiParcelas Para exibir o valor das parcelas toda vez que o usuário clicar sobre o botão spiParcelas, siga estes passos: 1 Na janela de código, localize o controle spiParcelas. 2 Logo acima da instrução End Sub Sub, digite este código: Private Sub spiParcelas_Change() txtParcelas.Text = Me.spiParcelas.Value End Sub

A obra completa Parabéns! Você criou um sistema de cadastro de clientes funcional e associado a planilhas. Para que você possa ter uma visão geral do seu programa, mostramos a seguir como deve ser o seu código completo. Para facilitar a visualização, removemos as linhas de comentário. Confira: Private Sub cmdOK_Click() Range(“A1:F1”).Select Selection.End(xlDown).Select Selection.End(xlDown).Select Selection.End(xlDown).Select ActiveCell.Offset(1, 0).Range(“A1”).Select ActiveCell.Value = Me.txtNome.Text ActiveCell.Offset(0, 1).Activate ActiveCell.Value = Me.ltBVeículo.Value ActiveCell.Offset(0, 1).Activate ActiveCell.Value = Me.lblPreço.Caption ActiveCell.Offset(0, 1).Activate If Me.optVista = True Then ActiveCell.Value = “À vista” ActiveCell.Offset(0, 1).Select Me.spiParcelas.Value = “0” txtParcelas.Enabled = False Else ActiveCell.Value = “Financiado” ActiveCell.Offset(0, 1).Select

Personalizando o sistema 95

cap_12.p65

95

16/6/2004, 17:31

ActiveCell.Value = Me.spiParcelas.Value Select Case txtParcelas Case 1 MsgBox(“Somente 1 parcela”) Case 2 MsgBox(“Tome cuidado ... os _ juros vão aumentar.”) Case 3 MsgBox(“nossa, aqui não é Kazas _ do Norte !”) Case 4 MsgBox(“Tá mal ... agora _ aguenta!”) End Select End If ActiveCell.Offset(0, 1).Activate ActiveCell.Value = Me.txtParcelas.Value If Me.chkNovo.Value = True Then ActiveCell.Value = “Sim” Else ActiveCell.Value = “Não” End If ActiveCell.Offset(1, -5).Activate Me.txtNome = “” Me.txtParcelas = “” Me.ltBVeículo.ListIndex = -1 Me.optVista = False Me.lblPreço = “” Me.lblVeículo = “” Me.chkNovo = False Me.txtNome = SetFocus End Sub Nada mau, hein? E isso é só o começo. Com os conceitos apresentados neste livro, você já tem subsídios para criar muitas outras aplicações em VBA – e para tornar-se um verdadeiro craque em Excel. Até a próxima!

96 Treinamento avançado em Excel

cap_12.p65

96

16/6/2004, 17:31