98

91729948 Visual Basic Vba Excel 01

Embed Size (px)

Citation preview

Page 1: 91729948 Visual Basic Vba Excel 01

capa_contra.p65 16/6/2004, 17:391

Page 2: 91729948 Visual Basic Vba Excel 01

indice.p65 17/6/2004, 10:441

Page 3: 91729948 Visual Basic Vba Excel 01

© 2004 b© 2004 b© 2004 b© 2004 b© 2004 by Digy Digy Digy Digy Digerererererati Booksati Booksati Booksati Booksati BooksTodos os direitos reservados e protegidos pela Lei 5.988 de 14/12/1973.Nenhuma parte deste livro, sem autorização prévia por escrito daeditora, poderá ser reproduzida ou transmitida sejam quais foremos meios empregados: eletrônicos, mecânicos, fotográficos, grava-ção ou quaisquer outros.

DirDirDirDirDiretor Editoretor Editoretor Editoretor Editoretor EditorialialialialialLuis Matos

EditorEditorEditorEditorEditora-assistentea-assistentea-assistentea-assistentea-assistenteMonalisa Neves

RedatorRedatorRedatorRedatorRedatoraaaaaSandra Rita Pinto

PrPrPrPrPreparepareparepareparação e Reação e Reação e Reação e Reação e RevisãovisãovisãovisãovisãoBeatriz Utsumi

Capa e PrCapa e PrCapa e PrCapa e PrCapa e Projeto Gráfojeto Gráfojeto Gráfojeto Gráfojeto GráficoicoicoicoicoDaniele Oliveira

ArArArArArtetetetetePatricia Fernandez Ferrari

SuporSuporSuporSuporSuporte te te te te Técnico e Técnico e Técnico e Técnico e Técnico e Atendimento ao LeitorAtendimento ao LeitorAtendimento ao LeitorAtendimento ao LeitorAtendimento ao [email protected](11) 3217-2626

DigDigDigDigDigerererererati Comati Comati Comati Comati Comunicação e unicação e unicação e unicação e unicação e TTTTTecnologecnologecnologecnologecnologia Ltda.ia Ltda.ia Ltda.ia Ltda.ia Ltda.Rua Haddock Lobo, 347 – 12º AndarCEP 01414-001 São Paulo/SP(11) 3217-2600 Fax (11)3217-2617www.digerati.com

DirDirDirDirDiretoretoretoretoretoresesesesesAlessandro Gerardi – ([email protected])Luis Afonso G. Neira – ([email protected])Alessio Fon Melozo – ([email protected])

ISBN: 85-89535-38-X

Treinamento Avançado em Excel

indice.p65 17/6/2004, 10:442

Page 4: 91729948 Visual Basic Vba Excel 01

Prefácio

“Você faz uma vez e o Excel faz o resto por você”. Era com essebordão que eu iniciava minhas explicações sobre o Microsoft Exceldurante as aulas que ministrava.

Passados alguns anos, continuo certo de que não existe no mer-cado nenhuma ferramenta de produtividade – incluindo as “madein Microsoft” – tão completa quanto o Excel. Com ele, você precisaapenas gastar algumas horas projetando os cálculos e estruturandoas informações, para que depois o software faça tudo automatica-mente, mediante um simples comando. Até parece mágica...

O Treinamento Avançado em Excel integra uma lista de títuloslançados pela Digerati Books que inclui os livros 101 Dicas de ExcelVol. I e II, Desvendando o Microsoft Excel e Universidade Excel.Este livro, no entanto, tem uma proposta diferenciada: além de abor-dar os novos recursos do Excel 2003 e a criação de funções, abor-da, em profundidade, a criação de macros no Excel.

As macros permitem gravar seqüências de procedimentos, demodo que você possa executar uma série de ações repetidas vezes,no momento em que desejar. Por isso, elas são responsáveis pelaautomatização da maior parte das atividades de quem lida com da-dos e cálculos. Por meio da linguagem VBA (Visual Basic forApplications), as macros permitem que sejam desenvolvidas novasfuncionalidades dentro do Excel, fazendo com que o programa setorne 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á apresentadonas próximas páginas. Aproveite para familiarizar-se com ele, para,em pouco tempo, poder colocar o Excel no “piloto automático”, guar-dando seu tempo livre para coisas mais interessantes...

Luis [email protected]

Diretor Editorial

indice.p65 17/6/2004, 10:443

Page 5: 91729948 Visual Basic Vba Excel 01

Índice

CAPÍTULO 01

Algumas novidades na versão XP .......................... 09Localizando planilhas ................................... 09Exibindo detalhes ......................................... 10Cabeçalhos e rodapés................................... 11Diagramas ..................................................... 12

Inserindo novas caixas ........................... 13Formatando as caixas do seu diagrama .. 13Alterando o layout .................................. 14

Alinhamento de células ................................ 14Botão AutoSoma ........................................... 15Desenhando bordas ...................................... 15A área de transferência ................................. 16

CAPÍTULO 02

Funções ............................................................... 17Disponibilizando funções....................... 17

Funções de texto .......................................... 18Função PROCURAR................................ 18Função LOCALIZAR ............................... 19Funções ESQUERDA e LOCALIZAR ........ 19

Funções matemáticas ................................... 19Função SUBTOTAL ................................. 19

Funções estatísticas ...................................... 21Função FREQÜÊNCIA ............................ 21

Funções de data e hora ................................ 23Função DATADIF .................................... 23

Funções de informações .............................. 24

indice.p65 17/6/2004, 10:444

Page 6: 91729948 Visual Basic Vba Excel 01

Juntando mais de uma categoria de funções ...... 24Função PROCV ....................................... 24

CAPÍTULO 03

Gerando gráficos automáticos ............................... 28Procurando dados específicos ............... 29

Criando o gráfico.......................................... 30

CAPÍTULO 04

Noções de macro .................................................. 33Macro para classificar dados em uma planilha .. 33Formas de executar uma macro ................... 35

Teclas de atalho ...................................... 35Comandos .............................................. 35Criando botões na planilha .................... 36

Macro para encontrar subtotais .................... 36Macros de endereços absolutos / relativos ... 37

Macros com endereços absolutos .......... 38Editando a macro ................................... 38Macros com endereços relativos ............ 39Entendendo o código ............................. 40

CAPÍTULO 05

O editor do Visual Basic no Excel .......................... 41A janela do editor do Visual Basic ................. 41

Módulos ................................................. 43Objetos ................................................... 43Eventos da planilha ................................ 44

indice.p65 17/6/2004, 10:445

Page 7: 91729948 Visual Basic Vba Excel 01

CAPÍTULO 06

Exibindo mensagens ao usuário ............................ 47Função MSGBOX .......................................... 47Outros exemplos de mensagem ................... 48Capturando respostas do usuário ................. 49

Função INPUTBOX ................................ 50Adicionando comentários ao código............ 51

CAPÍTULO 07

Criando um formulário no VBA ............................. 52Criando um formulário................................. 52A barra de ferramentas do formulário .......... 53Inserindo um controle ................................. 55

Criando um formulário simples ............. 55Alterando o nome dos controles .................. 56

A propriedade Caption........................... 56Alterando os controles do formulário .......... 56

Inserindo uma imagem .......................... 57Alterando a ordem dos controles ........... 58Preparando o ambiente dos dados ......... 58Preparando a tabela de veículos........ 59Associando Veículos com o controle ... 59Exibindo o formulário ............................ 60

Exibindo o preço do veículo ........................ 61Descarregando o formulário da memória 61

indice.p65 17/6/2004, 10:446

Page 8: 91729948 Visual Basic Vba Excel 01

CAPÍTULO 08

Manipulando células ............................................. 62Métodos de manipulação de células ............. 62

Verificando a área selecionada ............... 62Inserindo fórmulas em uma célula ......... 63

O método Select e a propriedade Selection .. 63Exemplos de utilização do método Range ... 63

Propriedade Offset ................................. 67Atribuindo valores às células da planilha ..... 67

CAPÍTULO 09

Variáveis de memória ............................................ 69Conceito de variáveis de memória ............... 69Regras para nomeação em VBA .................... 69Como definir variáveis ................................. 70Declarando variáveis .................................... 71Escopo das variáveis ..................................... 72

Variáveis do nível do procedimento ....... 73Variável do nível de módulo ................... 73Variáveis de nível global ......................... 73

Tipos de variável .......................................... 73

CAPÍTULO 10

Estruturas de programação.................................... 75

indice.p65 17/6/2004, 10:447

Page 9: 91729948 Visual Basic Vba Excel 01

Estruturas condicionais ................................ 75As estruturas If ....................................... 75A estrutura Case Select ........................... 78

Estruturas de looping ................................... 79Estrutura Do ... Loop .............................. 79Estrutura For ... Next .............................. 80Estrutura For Each ... Next...................... 81Outros exemplos de criação de loops .... 82Listando arquivos existentes .................. 82Usando estrutura Do ... Until .................. 83

CAPÍTULO 11

Criando funções .................................................... 84

CAPÍTULO 12

Personalizando o sistema ...................................... 87Limpeza dos dados para novo cadastramento 87Criando uma barra de ferramentas ............... 88

Criando uma barra de menus internos ... 89Anexando uma barra de ferramentas ao arquivo. 89Anexando macros a botões .......................... 90Códigos para ativar a barra de ferramentas .. 90Ocultando a barra de ferramentas ................ 91Posição de uma barra de ferramentas........... 92Desabilitando um item da barra ................... 93Inicializando o sistema ................................. 93Finalizando seu sistema de cadastro ............. 94Posicionando o cursor no 1°- registro em branco 94

Ativando o controle spiParcelas ............. 95A obra completa ..................................... 95

indice.p65 17/6/2004, 10:448

Page 10: 91729948 Visual Basic Vba Excel 01

Algumas novidades na versão XP 9

Algumas novidades na versão XPTem novidade no Excel XP? Tem, sim, senhor! A quinta versão

do mais enigmático dos aplicativos do Office traz aqueles pequeno-grandes aperfeiçoamentos de praxe. Conheça, neste capítulo, as ino-vações relacionadas à localização de planilhas, cabeçalhos e rodapése diagramas. Não se esqueça, é claro, da estréia dos novos botões deAutoSomaAutoSomaAutoSomaAutoSomaAutoSoma e BordasBordasBordasBordasBordas.

• LOCALIZANDO PLANILHAS

Muitas vezes geramos arquivos que depois de algum tempo nãolembramos onde guardamos. Para resolver esse problema, podemossolicitar ao Excel que faça uma pesquisa em nosso computador.Como? Veja nos passos a seguir:

Clique no menu ArArArArArquivquivquivquivquivo o o o o > P P P P Pesquisaresquisaresquisaresquisaresquisar.Aparecerá uma caixa de diálogo do lado direito da tela. Preencha-ada seguinte forma:

TTTTTeeeeexto de pesquisa:xto de pesquisa:xto de pesquisa:xto de pesquisa:xto de pesquisa:Digite nesse campo o texto que estátentando localizar.

PPPPPesquisar em:esquisar em:esquisar em:esquisar em:esquisar em: Selecione o local (pasta) em que deseja pesquisar.O ideal é escolher a opção Meu Computador Meu Computador Meu Computador Meu Computador Meu Computador (como na figura aseguir), dessa forma, serão verificados disquetes, CDs, redes e pastas.

1

Algumas novidades na versão XP

cap_01.p65 16/6/2004, 17:409

Page 11: 91729948 Visual Basic Vba Excel 01

10 Treinamento avançado em Excel

Os resultados devem ser:Os resultados devem ser:Os resultados devem ser:Os resultados devem ser: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, qualquerarquivo do pacote Office), ou mesmo figuras.

Para iniciar a pesquisa, pressione o botão PPPPPesquisaresquisaresquisaresquisaresquisar.....

• EXIBINDO DETALHES

cap_01.p65 16/6/2004, 17:4010

Page 12: 91729948 Visual Basic Vba Excel 01

Algumas novidades na versão XP 11

Em versões anteriores ao Windows XP, para ocultar ou visualizar algunsdetalhes existentes na planilha, utilizávamos o menu Ferramentas Ferramentas Ferramentas Ferramentas Ferramentas >OpçõesOpçõesOpçõesOpçõesOpções, habilitando ou desabilitando os itens desejados. A partir da versãoXP, o processo tornou-se muito mais rápido e podemos utilizar o menuExibirExibirExibirExibirExibir, 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 eranecessário clicar em ExibirExibirExibirExibirExibir > ConfConfConfConfConfigurigurigurigurigurararararar > CaCaCaCaCabeçalho/Rodapébeçalho/Rodapébeçalho/Rodapébeç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ãoPPPPPererererersonalizar casonalizar casonalizar casonalizar casonalizar cabeçalhobeçalhobeçalhobeçalhobeçalho.

A partir da versão XP, podemos agilizar essa tarefa no menu Exibir Exibir Exibir Exibir Exibir >CaCaCaCaCabeçalho e rbeçalho e rbeçalho e rbeçalho e rbeçalho e rodapéodapéodapéodapéodapé, e criar um novo cabeçalho por meio do mesmobotão – PPPPPererererersonalizar casonalizar casonalizar casonalizar casonalizar cabeçalho beçalho beçalho beçalho beçalho –, o qual oferece algumas opçõesque já existiam nas versões anteriores, e outras que são novidadesda 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.

cap_01.p65 16/6/2004, 17:4011

Page 13: 91729948 Visual Basic Vba Excel 01

12 Treinamento avançado em Excel

(*) 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 Inserir Inserir Inserir Inserir > Diagramas Diagramas Diagramas Diagramas Diagramas esiga estes passos:

Escolha o tipo de diagrama que preferir.1

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

cap_01.p65 16/6/2004, 17:4012

Page 14: 91729948 Visual Basic Vba Excel 01

Algumas novidades na versão XP 13

Inserindo novas caixasTambém é possível inserir novas caixas em seu diagrama. Para isso,

faça o seguinte:

Clique na caixa que deseja reproduzir.

Em seguida, clique no botão InserInserInserInserInserir fir fir fir fir forororororma.ma.ma.ma.ma. Isso fará com que anova caixa seja inserida logo abaixo da caixa selecionada.

Tendo feito isso, basta digitar o texto desejado.

Formatando as caixas do seu diagramaPara dar uma aparência pessoal aos seus diagramas, a nova versão

do Excel oferece opções especiais:

Dê um duplo clique sobre a caixa que deseja personalizar.

Aparecerá a caixa de diálogo de formatação, na qual é possívelfazer as seguintes alterações:

1

2

3

1

2

cap_01.p65 16/6/2004, 17:4013

Page 15: 91729948 Visual Basic Vba Excel 01

14 Treinamento avançado em Excel

FFFFFonteonteonteonteonte – – – – – permite alterar tamanho, cor, estilo e efeitos da fonte, alémdela própria.

Alinhamento – Alinhamento – Alinhamento – Alinhamento – Alinhamento – permite alterar o alinhamento do texto dentroda caixa.

CorCorCorCorCores e Linhas – es e Linhas – es e Linhas – es e Linhas – es e Linhas – permite alterar as cores de preenchimento,da linha (moldura) da caixa e das setas (quando existentes).

TTTTTamanho – amanho – amanho – amanho – amanho – permite dimensionar, girar e alterar a escala da caixa.Proteção – Proteção – Proteção – Proteção – Proteção – (*) permite bloquear a caixa e o texto existente nela.MarMarMarMarMargggggens – ens – ens – ens – ens – permite alterar as margens do texto dentro da caixa.WWWWWeb – eb – eb – eb – eb – em alguns navegadores irá exibir um texto alternativo

para facilitar a pesquisa de objetos, mas no caso de figuras, essetexto é ignorado.

(*) Para que este comando seja executado, é necessário protegera planilha.

Alterando o layoutPara alterar o layout das caixas, siga estes passos:

Selecione a caixa desejada.

Dê um clique no botão LaLaLaLaLayyyyyoutoutoutoutout.

Escolha um dos layouts existentes.

Outra maneira de alterar o layout é simplesmente clicar no bo-tão mostrado na figura:

1

2

3

• ALINHAMENTO DE CÉLULAS

Para alinhar rapidamente uma ou mais células, podemosutilizar os botões .

A partir da versão XP, encontramos também algumas opções dealinhamento no menu FFFFFororororormatar matar matar matar matar > Células Células Células Células Células > AlinhamentoAlinhamentoAlinhamentoAlinhamentoAlinhamento. São elas:

HorHorHorHorHorizontal – izontal – izontal – izontal – izontal – altera o alinhamento horizontal (da esquerda paraa direita) do conteúdo de uma célula.

cap_01.p65 17/6/2004, 10:3514

Page 16: 91729948 Visual Basic Vba Excel 01

Algumas novidades na versão XP 15

VVVVVererererertical – tical – tical – tical – tical – altera o alinhamento vertical (de cima para baixo) doconteúdo de uma célula.

ContrContrContrContrControle de teole de teole de teole de teole de texto – xto – xto – xto – xto – esta opção oferece três possibilidades:RetorRetorRetorRetorRetorno automático de teno automático de teno automático de teno automático de teno automático de texto – xto – xto – xto – xto – alinha o texto em mais de uma linha;Reduzir parReduzir parReduzir parReduzir parReduzir para ajustar – a ajustar – a ajustar – a ajustar – a ajustar – reduz o tamanho da fonte para que todo

o conteúdo da célula seja apresentado dentro dela;MescMescMescMescMesclar células – lar células – lar células – lar células – lar células – alinha o texto em mais de uma célula ao mes-

mo tempo, recurso bastante utilizado para casos em que os títulosestão na primeira linha.

OrOrOrOrOrientação – ientação – ientação – ientação – ientação – permite rotacionar o texto dentro da célula.DirDirDirDirDireção do teeção do teeção do teeção do teeção do texto –xto –xto –xto –xto – permite alterar a ordem de leitura dos dados exis-

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élu-las com valores e ativar o botão AAAAAutoSoma utoSoma utoSoma utoSoma utoSoma .

Para facilitar o trabalho, foram incorporadas as funções mais uti-lizadas 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 comlinhas na cor preta e praticamente com a mesma espessura. Quemquisesse modificar esse padrão tinha de utilizar o menu Formatar Formatar Formatar Formatar Formatar >CélulasCélulasCélulasCélulasCélulas > BorBorBorBorBordasdasdasdasdas. No Windows XP, além das bordas convencionais,encontramos a opção Desenhar borDesenhar borDesenhar borDesenhar borDesenhar bordasdasdasdasdas. Assim, podem-se criar bordascom os seguintes passos:

cap_01.p65 16/6/2004, 17:4015

Page 17: 91729948 Visual Basic Vba Excel 01

16 Treinamento avançado em Excel

Fórmulas – Fórmulas – Fórmulas – Fórmulas – Fórmulas – cola a última fórmula que foi copiada na planilha.VVVVValoraloraloraloralores – es – es – es – es – cola o valor da célula, ou seja, ignora a fórmula e inse-

re somente o resultado do cálculo.Sem bordas – Sem bordas – Sem bordas – Sem bordas – Sem bordas – cola a fórmula na célula atual e ignora a borda formatada.TTTTTrrrrranspor – anspor – anspor – anspor – anspor – cola as células em posição inversa. Suponha, por exem-

plo, que você tenha selecionado várias células que estejam dispostas emuma linha; na transposição, as mesmas células serão coladas, só queagora dispostas em colunas.

Colar vínculo – Colar vínculo – Colar vínculo – Colar vínculo – Colar vínculo – cola o vínculo do arquivo, ou seja, insere a fór-mula, bem como todo o caminho utilizado para a sua criação. Dessaforma, ao atualizar os dados da planilha de origem, a planilha dedestino é atualizada.

Colar especial – Colar especial – Colar especial – Colar especial – Colar especial – cola os dados como um objeto.

3

• A ÁREA DE TRANSFERÊNCIA

Para colar um objeto utilizamos o botão , o qual insere na po-sição atual do cursor o conteúdo da área de transferência. Na ver-são XP foram incorporadas novas tarefas ao botão:

Clique no botão Cor da linha:Cor da linha:Cor da linha:Cor da linha:Cor da linha:

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

Ative o botão Desenhar bordasDesenhar bordasDesenhar bordasDesenhar bordasDesenhar bordas.

Clique no botão Estilo da linha:Estilo da linha:Estilo da linha:Estilo da linha:Estilo da linha:

1

2

cap_01.p65 16/6/2004, 17:4016

Page 18: 91729948 Visual Basic Vba Excel 01

Funções 17

FunçõesAs 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 meiodas funções que conseguimos agilizar cálculos e elaborar planilhas sofisti-cadas. E há funções para todo tipo de operação. Desde as somas maissimples até intrincadas fórmulas estatísticas, esse é o caminho das pedras!

Neste capítulo, trataremos de uma parte muito importante doExcel: as funções avançadas de texto, matemática, estatística e de datae 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 seguin-tes 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çõesAlgumas funções podem não estar disponíveis quando você quiser

usá-las, sendo necessário torná-las disponíveis. Para isso, basta clicar emFerramentasFerramentasFerramentasFerramentasFerramentas > SuplementosSuplementosSuplementosSuplementosSuplementos. Surgirá a seguinte tela:

cap_02.p65 16/6/2004, 17:3917

Page 19: 91729948 Visual Basic Vba Excel 01

18 Treinamento avançado em Excel

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

• FUNÇÕES DE TEXTO

Função PROCURARImagine que você queira localizar uma determinada seqüência de

caracteres existente em uma célula – por exemplo, o código de umproduto 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 aseguinte sintaxe:

=PROCURAR(texto a localizar;célula com o texto;nº inicial)

Em que:TTTTTeeeeexto a localizarxto a localizarxto a localizarxto a localizarxto 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ênte-ses, sem nenhuma formatação. Para procurar o valor R$ 2500,00, porexemplo, digite apenas 2500. Se o conteúdo não for numérico (por exem-plo, o símbolo #), você deve digitá-lo entre aspas (“”).

cap_02.p65 16/6/2004, 17:3918

Page 20: 91729948 Visual Basic Vba Excel 01

Funções 19

Célula com o textoCélula com o textoCélula com o textoCélula com o textoCélula com o texto – é o endereço da célula em que o símbolo,valor ou texto deve ser procurado.

NNNNNooooo- inicial- inicial- inicial- inicial- inicial – é o parâmetro no qual você pode especificar a partirde 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 especifiqueo número inicial, o Excel vai considerar que a pesquisa deve ser iniciadaa partir do primeiro caractere (11111).

No exemplo a seguir, o Excel vai localizar o símbolo # dentro doconteúdo da célula A2:

Função LOCALIZAROutra função que faz pesquisa de caracteres é a LOCALIZAR.

A diferença entre ela e a função PROCURAR é que a segunda faz distinçãoentre maiúsculas e minúsculas, enquanto a LOCALIZAR não faz:

Usando a função ESQUERDA com a função LOCALIZARA função ESQUERDA retorna um número X de caracteres a partir

de um determinado texto, da esquerda para a direita. O número decaracteres X é você quem especifica.

Suponha que, na tabela da figura a seguir, você queira retirar apenasa descrição dos produtos. A melhor maneira de fazer isso é usar conjun-tamente as funções ESQUERDA e LOCALIZAR:

• FUNÇÕES MATEMÁTICAS

Função SUBTOTALEssa função retorna um subtotal em um banco de dados, podendo execu-

tar a soma, a média, o mínimo e o máximo, entre outras operações, em umalista de dados. A sintaxe da função SUBTOTAL é a seguinte:

cap_02.p65 16/6/2004, 17:3919

Page 21: 91729948 Visual Basic Vba Excel 01

20 Treinamento avançado em Excel

=SUBTOTAL (número da função; células a calcular)

Em que:Número da funçãoNúmero da funçãoNúmero da funçãoNúmero da funçãoNú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çãocorresponde a um número, como mostra a tabela a seguir:

Número da funçãoNúmero da funçãoNúmero da funçãoNúmero da funçãoNúmero da função FunçãoFunçãoFunçãoFunçãoFunção

11111 MÉDIA

22222 CONTA

33333 CONT.VALORES

44444 MÁX

55555 MÍN

66666 MULT

77777 DESVPAD

88888 DESVPADP

99999 SOMA

1010101010 VAR

1111111111 VARP

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

cap_02.p65 16/6/2004, 17:3920

Page 22: 91729948 Visual Basic Vba Excel 01

Funções 21

Nessa tabela, as células B13:B16 trazem os valores das faixas depreç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 issonão aparecer diretamente nos valores das células, a faixa posterior nãoinclui o último valor da faixa anterior – por exemplo, a segunda faixanã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 cadauma dessas faixas de preço no Excel:

Defina as faixas de dados que deseja analisar. No nosso exemplo,elas foram criadas nas células B13:B16.

Selecione as células adjacentes à coluna com as faixas de dados(C13:C17).

Clique sobre o botão Inserir funçãoInserir funçãoInserir funçãoInserir funçãoInserir função . Será aberta esta janela:

1

2

3

Função FREQÜÊNCIACalcula 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, porcidade, que se encaixam em determinadas faixas de preço – isto é, a fre-qüência dessas faixas de preço entre os modelos de carros. Esse exemplopode ser observado na tabela da figura a seguir:

• FUNÇÕES ESTATÍSTICAS

cap_02.p65 16/6/2004, 17:3921

Page 23: 91729948 Visual Basic Vba Excel 01

22 Treinamento avançado em Excel

Selecione a função FREQÜÊNCIA e dê OKOKOKOKOK.

Na caixa de diálogo que aparecer, clique no botão com a seta ver-melha ao lado do primeiro campo. Na planilha, selecione a área quecontém os dados a serem analisados (B2:E11):

4

5

Na janela que aparecer, clique no botão com a seta vermelha, noprimeiro 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ãoservir de base para o cálculo (B13:B16).

Pressione as teclas Ctrl + Shift + Enter para que sejam inseridos osvalores correspondentes a cada uma das faixas. Não use a tecla Enter sozi-nha, ou o resultado será inserido somente na primeira célula:

6

7

cap_02.p65 16/6/2004, 17:3922

Page 24: 91729948 Visual Basic Vba Excel 01

Funções 23

• FUNÇÕES DE DATA E HORA

Função DATADIFPara 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 aseguinte sintaxe:

=DATADIF(data_inicial;data_final;tipo de diferença desejado)

Na qual tipo de diferença desejado pode ser representado como:

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

Unidade Retorno

“Y” O número de anos completos entre a data inicial e adata final.

“M” O número de meses completos entre a data inicial e adata final.

“D” O número de dias completos entre a data inicial e adata final.

“MD” A diferença entre as datas inicial e final, sendo quemeses e anos são ignorados.

“YD” A diferença entre as datas inicial e final, sendo quedias e anos são ignorados.

cap_02.p65 16/6/2004, 17:3923

Page 25: 91729948 Visual Basic Vba Excel 01

24 Treinamento avançado em Excel

• JUNTANDO MAIS DE UMA CATEGORIA DE FUNÇÕES

Função PROCVA função PROCV efetua uma pesquisa no sentido vertical em uma

tabela de dados, retornando um dado existente em qualquer uma dascolunas. Sua sintaxe é:

=PROCV(valor procurado;matriz_tabela_número índice coluna)

Ou seja:

=PROCV(o que pesquisar;área a pesquisar;número da colunaa trazer)

Imagine a seguinte tabela:

• 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 úteispara verificar o tipo de conteúdo de determinadas células, se estão vaziasou não, se estão preenchidas com textos ou números etc. A tabela a seguirmostra exemplos das principais funções lógicas:

cap_02.p65 16/6/2004, 17:3924

Page 26: 91729948 Visual Basic Vba Excel 01

Funções 25

1 Nomeie a área com o catálogo dos filmes como FITAS, selecionando ascélulas e indo ao menu InserirInserirInserirInserirInserir > Nome Nome Nome Nome Nome > DefinirDefinirDefinirDefinirDefinir; ou então digitando onome diretamente na Caixa de nomeCaixa de nomeCaixa de nomeCaixa de nomeCaixa de nome e pressionando Enter.

Crie a tabela com os dados das vendas.

Posicione o cursor na célula D4 (ou na célula em que quiser queapareça o resultado da busca).

Clique no botão Inserir função Inserir função Inserir função Inserir função Inserir função . .

Selecione as funções de ProcuraProcuraProcuraProcuraProcura e ReferênciaReferênciaReferênciaReferênciaReferência.

2

3

4

5

6

7

Clique sobre a função PROCVPROCVPROCVPROCVPROCV e dê OKOKOKOKOK.

Insira os argumentos da função, conforme o exemplo abaixo:

Para verificar qual é o nome de determinado filme (título), siga estes passos:

cap_02.p65 16/6/2004, 17:3925

Page 27: 91729948 Visual Basic Vba Excel 01

26 Treinamento avançado em Excel

Pressione OKOKOKOKOK; com isso, será informado o nome do filme que vocêestá procurando. Copie para as células abaixo.

Experimente alterar o código do filme para ver o Excel preencheros campos automaticamente.

Se o usuário por acaso escolher um filme que não existe na lista, oExcel vai retornar este erro:

8

9

Para evitar esse tipo de problema, podemos criar uma combinaçãocom as funções lógicas SE, ÉERROS e PROCV, como mostra a explica-ção a seguir:

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)

Para juntar as três funções, devemos construir uma expressão comesta sintaxe:

=SE(ÉERRROS (PROCV)); exiba 0; exiba o resultado de PROCV)

1

2

cap_02.p65 16/6/2004, 17:3926

Page 28: 91729948 Visual Basic Vba Excel 01

Funções 27

Deixe o cursor no primeiro PROCV (título do filme) e pressione obotão Inserir funçãoInserir funçãoInserir funçãoInserir funçãoInserir função para editar o conteúdo da fórmula, pois agoravocê vai adicionar as funções SE e ÉERROS.

Para inserir novas funções, utilize a área com a Caixa de nomeCaixa de nomeCaixa de nomeCaixa de nomeCaixa de nome,que agora exibe a última função utilizada:

3

4

5

6

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

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

cap_02.p65 16/6/2004, 17:3927

Page 29: 91729948 Visual Basic Vba Excel 01

28 Treinamento avançado em Excel

Gerando gráficos automáticos

O que todo mundo quer, hoje em dia, é poder apertar um únicobotão e, com isso, resolver todos os problemas. Nada de errado nis-so, pelo menos em se tratando de gerenciamento de dados. Que talconstruir gráficos que se atualizem automaticamente quando a ta-bela de dados for alterada? Que tal ter à mão um sistema que varraa 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ãocom os recursos oferecidos pelo Excel.

Veja como construir um gráfico dinâmico que incorpore recur-sos de formulários e lista de dados:

Crie, em uma planilha em branco, a seguinte tabela de dados:1

Selecione as células com os nomes das publicações (A3:A8) enomeie esse intervalo como PRODUTOS.

Selecione todos os dados (A2:G8) e nomeie esse intervalocomo VENDAS.

Vá até a Plan2 e inclua nela estes dados:

2

3

4

Ative a barra de ferramentas de formulários com o comandoExibirExibirExibirExibirExibir > BarBarBarBarBarrrrrra de fa de fa de fa de fa de ferererererrrrrramentasamentasamentasamentasamentas > FFFFFororororormmmmmuláruláruláruláruláriosiosiosiosios.

5

Gerando gráficos automáticos

cap_03.p65 16/6/2004, 17:3928

Page 30: 91729948 Visual Basic Vba Excel 01

Gerando gráficos automáticos 29

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

Agora você precisa ativar as propriedades do controle que aca-bou de criar. Para isso, clique nele com o botão direito do mouse, dêum clique sobre a opção FFFFFororororormatar contrmatar contrmatar contrmatar contrmatar controleoleoleoleole, e, depois, preencha oscampos da janela como mostra a figura:

7

Procurando dados específicosAinda usando o mesmo documento do Excel que você criou,

vamos procurar alguns dados específicos, como, por exemplo, onome 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 PRODU-TOS; portanto, essa é a área que deve ser varrida. A linha que vocêprocura é aquela na qual foi vinculado o controle de caixa de com-binação – logo, trata-se do resultado existente em C3. Dessa forma,a função para verificar o nome do produto deverá ser:

cap_03.p65 16/6/2004, 17:3929

Page 31: 91729948 Visual Basic Vba Excel 01

30 Treinamento avançado em Excel

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

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

• CRIANDO O GRÁFICO

Para criar um gráfico com os dados da planilha, comece selecio-nando as duas linhas que contêm os dados da venda, ou seja, a linhaque apresenta os nomes dos meses e a linha que traz os valores dasvendas. Feito isso, você já pode ativar o assistente de gráficos paracriar um gráfico do tipo “pizza”, seguindo este passo-a-passo:

Selecione os dados.

Clique no botão Assistente de gAssistente de gAssistente de gAssistente de gAssistente de gráfráfráfráfráficoicoicoicoico.

1

2

cap_03.p65 16/6/2004, 17:3930

Page 32: 91729948 Visual Basic Vba Excel 01

Gerando gráficos automáticos 31

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

4 Clique no botão AAAAAvvvvvançarançarançarançarançar.

Defina a opção SérSérSérSérSéries emies emies emies emies em como LinhasLinhasLinhasLinhasLinhas e clique em AAAAAvvvvvançarançarançarançarançar.5

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

cap_03.p65 16/6/2004, 17:3931

Page 33: 91729948 Visual Basic Vba Excel 01

32 Treinamento avançado em Excel

Na guia Rótulo de dadosRótulo de dadosRótulo de dadosRótulo de dadosRótulo de dados, habilite as opções PorcentagemPorcentagemPorcentagemPorcentagemPorcentagem e NomeNomeNomeNomeNomeda categoriada categoriada categoriada categoriada categoria.

7

Pressione AAAAAvvvvvançarançarançarançarançar e, na tela que surgir, selecione a opçãoComo objeto emComo objeto emComo objeto emComo objeto emComo objeto em, para que o gráfico seja posicionado como umobjeto na planilha.

8

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

9

cap_03.p65 16/6/2004, 17:3932

Page 34: 91729948 Visual Basic Vba Excel 01

Algumas novidades na versão XP 33

Noções de macro

Imagine ter de digitar as mesmas informações todos os dias, a cada15 minutos. Veja: isso não é uma obra de ficção. Para quem trabalha comserviços financeiros e administrativos – ou seja, 90% do pessoal do escri-tório de uma empresa –, esse tipo de situação pode ser realmente roti-neiro. Mas, felizmente, existem as macros, pequenas aplicações que po-dem 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 arma-zenada em um botão. Usando macros, você pode gerar gráficos comconfigurações especiais, aplicar cálculos extensos repetidas vezes, entremuitos outros procedimentos, apenas clicando em um botão. Bastacriar macros bem elaboradas.

Neste capítulo, você vai deparar freqüentemente com o termo VVVVVisualisualisualisualisualBasic fBasic fBasic fBasic fBasic for or or or or AAAAApplicationspplicationspplicationspplicationspplications, o famoso VBVBVBVBVBAAAAA, que é uma linguagem de progra-mação voltada para aplicativos. O VBA vem incorporado ao Office, e, porisso, pode ser utilizado em qualquer aplicativo desse pacote, como o MSExcel, o MS Word, o MS Access etc.. Todo código gerado no VBA é compi-lado, e, por esse motivo, executa loops (laços) de programação rapida-mente. Outra vantagem de utilizar o VBA é que o código gerado pode serlido em qualquer aplicativo Microsoft, garantindo uma padronização nomodo de programação.

• MACRO PARA CLASSIFICAR DADOS EMUMA PLANILHA

Para entender um pouco sobre macros, nada melhor do que partirpara a prática e criar uma macro simples. Por enquanto, você vai seleci-onar 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 planilhaa seguir:

Noções de macro

cap_04.p65 16/6/2004, 17:3833

Page 35: 91729948 Visual Basic Vba Excel 01

34 Treinamento avançado em Excel

A sua macro vai se chamar CLASSIFICA. Posicione o cursor emqualquer célula e vá ao menu FerramentasFerramentasFerramentasFerramentasFerramentas > Macro >Macro >Macro >Macro >Macro > Gravar novaGravar novaGravar novaGravar novaGravar novamacromacromacromacromacro, para acessar esta tela:

1

Digite o nome da macro (CLASSIFICA) e observe as opçõesdisponíveis:

Nome da macro Nome da macro Nome da macro Nome da macro Nome da macro – Procure escolher um nome significativo, e que tenhaa ver com a função que a macro vai executar.TTTTTecececececla de atalho la de atalho la de atalho la de atalho la de atalho – solicita ao MS Excel que rode todas as macros por meiode 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 Armazenar macro em Armazenar macro em Armazenar macro em Armazenar macro em – as tarefas a ser executadas são arquivadas em umdeterminado local. Esse local pode ser a Pasta pessoal de macrosPasta pessoal de macrosPasta pessoal de macrosPasta pessoal de macrosPasta pessoal de macros, que irácriar um arquivo Pessoal.XLS (uma pasta oculta que permanecerá na pastade inicialização do MS Excel); uma Nova pasta de trabalhoNova pasta de trabalhoNova pasta de trabalhoNova pasta de trabalhoNova pasta de trabalho, que irá criarum arquivo (planilha) para armazenar somente a macro e a última opção;

2

cap_04.p65 16/6/2004, 17:3834

Page 36: 91729948 Visual Basic Vba Excel 01

Algumas novidades na versão XP 35

ou a pasta de trabalho atual (opção Esta pasta de trabalhoEsta pasta de trabalhoEsta pasta de trabalhoEsta pasta de trabalhoEsta pasta de trabalho), que faz comque o arquivo de macros seja armazenado juntamente com a planilha atual.Escolhendo essa última opção, se a planilha atual for movida ou copiadapara um outro local, as macros irão junto com ela.

Ao pressionar OKOKOKOKOK, todos os comandos a ser executados na planilhaserã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 quetentar consertar o erro.

Posicione o cursor no primeiro registro da tabela (célula B5) eutilize o menu DadosDadosDadosDadosDados > ClassifClassifClassifClassifClassificaricaricaricaricar. Escolha, primeiro a opção PPPPPaísaísaísaísaís, e, emseguida, ProdutoProdutoProdutoProdutoProduto.

Depois de fazer a classificação, pressione o botão Parar gravaçãoParar gravaçãoParar gravaçãoParar gravaçãoParar gravação.

3

4

5

• FORMAS DE EXECUTAR UMA MACROUma vez que você já tenha criado a sua macro, como fazer para

rodá-la? É simples. Você pode usar as teclas de atalho – selecionadasdurante o desenvolvimento da macro –, comandos, ou botões.

Teclas de atalhoPara rodar uma macro usando teclas de atalho, basta pressionar as

teclas definidas na hora da sua criação.

ComandosPara acessar o comando que apresenta as macros disponíveis,

faça o seguinte:

Pressione as teclas Alt + F8.

Dê um clique no nome da macro que quer executar e pressione obotão ExExExExExecutarecutarecutarecutarecutar.

1

2

cap_04.p65 16/6/2004, 17:3835

Page 37: 91729948 Visual Basic Vba Excel 01

36 Treinamento avançado em Excel

Criando botões na planilhaOutra maneira de executar a macro é criar um botão para ela:

Ative a barra de ferramentas de formulários em ExibirExibirExibirExibirExibir > Barra deBarra deBarra deBarra deBarra deferramentasferramentasferramentasferramentasferramentas > FormuláriosFormuláriosFormuláriosFormuláriosFormulários.

Dê um clique na opção Botão de comandosBotão de comandosBotão de comandosBotão de comandosBotão de comandos.

1

2

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

3

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

• MACRO PARA ENCONTRAR SUBTOTAIS

Vamos criar, ainda na mesma planilha em que você gravou a macropara classificação por País e Produto, uma segunda macro, para encon-trar o subtotal de cada país. Siga estes passos:

Ative a gravação de novas macros, indo ao menu FerramentasFerramentasFerramentasFerramentasFerramentas >MacrosMacrosMacrosMacrosMacros > Gravar nova macroGravar nova macroGravar nova macroGravar nova macroGravar nova macro.

1

cap_04.p65 16/6/2004, 17:3836

Page 38: 91729948 Visual Basic Vba Excel 01

Algumas novidades na versão XP 37

Nomeie a macro como SUBTOTAIS e dê OKOKOKOKOK.

Posicione o cursor sobre o primeiro registro (B5) e clique emDadosDadosDadosDadosDados > SubtotaisSubtotaisSubtotaisSubtotaisSubtotais.

Pressione OKOKOKOKOK e calcule um novo subtotal, agora por produto. Des-ta vez, não se esqueça de desabilitar a opção Substituir subtotais atu-Substituir subtotais atu-Substituir subtotais atu-Substituir subtotais atu-Substituir subtotais atu-aisaisaisaisais. Dê OKOKOKOKOK.

2

3

Para executar a macro, desative os subtotais criados, indo ao menuDadosDadosDadosDadosDados > SubtotaisSubtotaisSubtotaisSubtotaisSubtotais e pressionando o botão Remover todosRemover todosRemover todosRemover todosRemover todos e, em se-guida, OKOKOKOKOK.

5

4

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ên-cias que essas macros fazem às células que contêm as fórmulas, vaiperceber que esses endereços são absolutos. O que isso quer dizer? Porexemplo: ao selecionarmos o intervalo de células A1:F4 e alterarmos oestilo para negrito, sempre que executarmos a macro, o mesmo endere-ço, tendo conteúdo ou não, será alterado para negrito. Ou seja, uma

cap_04.p65 16/6/2004, 17:3837

Page 39: 91729948 Visual Basic Vba Excel 01

38 Treinamento avançado em Excel

Para visualizar o efeito da macro que você acabou de criar, execute-a várias vezes. Você irá perceber que o cursor insere o nome, o endereçoe o telefone sempre na mesma linha (22222).

Editando a macroPara editar a macro, use o comando FerramentasFerramentasFerramentasFerramentasFerramentas > MacrosMacrosMacrosMacrosMacros. Na

janela que aparecer, dê um clique sobre o nome da macro desejada epressione o botão EditarEditarEditarEditarEditar.

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:

Posicione o cursor em A2 e ative o gravador de macros.

Nomeie a macro como ABSOLUTOS e dê OKOKOKOKOK.

Digite seu nome e pressione a tecla Tab.

Digite seu endereço e, novamente, pressione Tab.

Digite seu telefone e pare a gravação da macro.

1

2

3

4

5

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élu-las que não a que serviu originalmente como base. Portanto, quandoprecisamos executar um procedimento em várias células da planilha, omelhor a fazer é utilizar um endereço relativo.

Macros com endereços absolutosPara entender a diferença entre os dois tipos de endereços, digite a

planilha abaixo:

cap_04.p65 16/6/2004, 17:3838

Page 40: 91729948 Visual Basic Vba Excel 01

Algumas novidades na versão XP 39

Macros com endereços relativosVamos, agora, criar uma macro com endereço relativo. Para isso, siga

estes passos:

Apague o conteúdo da linha 2 para gravar uma nova macro.

Posicione o cursor na célula A2 e grave uma nova macro com onome RELATIVOS.

Observe que, assim que o gravador de macros for iniciado, apare-cerá na tela uma pequena barra de ferramentas com dois botões:

1

2

3

4

5

6

7

8

9

10

Clique sobre o botão Referência relativaReferência relativaReferência relativaReferência relativaReferência relativa .....

Na célula A2, digite seu nome e pressione a tecla Tab.

Em B2, digite seu endereço e pressione Tab.

Em C2, digite seu telefone.

Pressione a seta para baixo para passar para a linha de baixo.

Posicione o cursor sob a célula que contém o seu nome (A3).

Pare a gravação da macro. Execute a macro várias vezes e observe adiferença entre o comportamento dela e o da macro ABSOLUTOS.

cap_04.p65 16/6/2004, 17:3839

Page 41: 91729948 Visual Basic Vba Excel 01

40 Treinamento avançado em Excel

11

ComandoComandoComandoComandoComando DescriçãoDescriçãoDescriçãoDescriçãoDescrição

ActiveCell Célula ativa.

ActiveCell.Select Seleciona a célula ativa.

ActiveCell.FormulaR1C1=”Sandra” Insere, na célula ativa, oconteúdo Sandra.

ActiveCell.Offset(0,1) Desloca o cursor 0 linhaspara baixo e uma colunapara a direita.

ActiveCell.Offset(1,-2) Desloca o cursor umalinha para baixo e duascolunas para a esquerda.

Entendendo o código

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

cap_04.p65 16/6/2004, 17:3840

Page 42: 91729948 Visual Basic Vba Excel 01

O editor do Visual Basic no Excel 41

O editor do Visual Basic 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 mundoda automatização de processos, vale a pena dedicar algum tempoao estudo do Visual Basic. Neste capítulo, apresentaremos uma vi-são geral sobre o editor do VB que vem embutido no MS Excel. Comum pouco de perseverança, você poderá fazer parte daquele seletoclube de pessoas que dizem: “Fazer o relatório estatístico dessasdez planilhas? É simples: espera um minutinho que eu crio umamacro para isso...”

A primeira coisa a fazer é familiarizar-se com o ambiente de tra-balho oferecido pelo editor do Visual Basic. Só para lembrar, paraabrir o editor basta pressionar Alt + F11 ou ir ao menu FFFFFerererererrrrrramen-amen-amen-amen-amen-tastastastastas > MacrMacrMacrMacrMacrososososos > Editor do Editor do Editor do Editor do Editor do VVVVVisual Basicisual Basicisual Basicisual Basicisual Basic. Se preferir abrir a tela doeditor do VBA diretamente para uma macro que já exista no seudocumento, você pode ir até FFFFFerererererrrrrramentasamentasamentasamentasamentas > MacrMacrMacrMacrMacrososososos > MacrMacrMacrMacrMacrooooo, clicarsobre o nome da macro a ser editada, e depois no botão EditarEditarEditarEditarEditar.

• A JANELA DO EDITOR DO VISUAL BASIC

O editor do Visual Basicno Excel

cap_05.p65 16/6/2004, 17:3841

Page 43: 91729948 Visual Basic Vba Excel 01

42 Treinamento avançado em Excel

ProjetoProjetoProjetoProjetoProjeto – Nesta janela, encontramos a hierarquia do projeto queestá sendo editado. Ela mostra o nome do arquivo de projeto (*.xls), asplanilhas que ele contém etc.. Ou seja, a janela ProjetoProjetoProjetoProjetoProjeto permite visualizaros objetos da aplicação atualmente aberta (pasta de trabalho, docu-mentos e módulos). Caso não esteja visualizando essa área, pressioneo botão ou tecle Ctrl + R. Nessa janela existem ainda três íconesque 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 Exibe a janela de módulo que contémo código em VBA que está associado aoobjeto selecionado.

Alterna entre a janela do Editor do Vi-sual Basic e a janela original da aplica-ção, com o objeto selecionado.

Exibir objeto

Alternar entre pastas Faz com que a exibição da janela PrPrPrPrPro-o-o-o-o-jetojetojetojetojeto alterne entre a hierarquia dos ob-jetos e uma lista simples dos mesmos.

Na janela PrPrPrPrProjetoojetoojetoojetoojeto, repare que há vários ícones diferentes. O signifi-cado deles está na tabela a seguir:

Permite a abertura de todos os objetosde uma pasta de trabalho, comoplanilhas, formulários, módulos etc..Pasta1 é o nome do arquivo que estásendo editado no momento; quando oarquivo é salvo, o nome é substituídopelo que você escolher.

É a referência à pasta de trabalho que estáaberta no momento. Mostra também to-dos os eventos (procedimentos) perten-centes a essa pasta, tais como aberturade arquivo, fechamento de arquivo etc.

Exibe a folha de código com as rotinaspara a planilha específica.

Abre uma tela com os códigos existen-tes para a criação de rotinas (macros)diversas para essa pasta de trabalho.

cap_05.p65 17/6/2004, 10:3842

Page 44: 91729948 Visual Basic Vba Excel 01

O editor do Visual Basic no Excel 43

PropriedadesPropriedadesPropriedadesPropriedadesPropriedades – mostra o nome do módulo em uso e suas proprie-dades atuais. Esta janela se divide em duas seções, uma com as proprie-dades em ordem alfabética, e outra com as mesmas propriedades sepa-radas por categorias de comandos.

MóduloMóduloMóduloMóduloMódulo – esta janela exibe o módulo atual. Módulo é a unidadebásica de um código em VBA, e funciona como se fosse um local emque são armazenadas todas as macros criadas. Podemos tambémdescrever módulo como uma seqüência de instruções que ensinaao MS Excel o que fazer.

Área de trabalho Área de trabalho Área de trabalho Área de trabalho Área de trabalho – trata-se da área disponível para a manipulaçãodos módulos.

MódulosNa tabela que acabamos de mostrar, falamos muito em módulomódulomódulomódulomódulo.

Como dissemos, um módulo é como um local em que são armaze-nadas 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 damacro ABSOLUTOS, que você criou no começo deste capítulo:

Abre o formulário criado pelo usuário.

Abre uma tela (página) com osmódulos (rotinas) de classe da aplicação.

cap_05.p65 17/6/2004, 10:3843

Page 45: 91729948 Visual Basic Vba Excel 01

44 Treinamento avançado em Excel

ObjetosOutro conceito importante na programação com VBA é o de

objetos. Temos vários objetos dentro de um aplicativo: por exem-plo, uma barra de ferramentas, uma planilha aberta, uma caixa detexto etc.. Portanto, um objeto é um elemento controlável por meioda linguagem VBA, e que é utilizado na execução de tarefas.

No VBA, quando você se refere a um objeto, na verdade está sereferindo a uma coleção de objetos que inclui o objeto em si. As-sim, a referência deve conter primeiramente o nome da coleção àqual o objeto pertence, e depois qual o item específico. Veja algunsexemplos de referências a objetos no VBA:

Os objetos organizam-se em uma hierarquia. No topo dela en-contra-se o objeto AAAAApplicationpplicationpplicationpplicationpplication, que, no nosso caso, é o próprio Excel.Abaixo de AAAAApplicationpplicationpplicationpplicationpplication, em ordem decrescente de nível hierárqui-co, aparecem os objetos WWWWWorororororkbookkbookkbookkbookkbook, AddInAddInAddInAddInAddIn, DialogDialogDialogDialogDialog, MenMenMenMenMenuBaruBaruBaruBaruBar, TTTTToolBaroolBaroolBaroolBaroolBar,WWWWWindoindoindoindoindowwwww, e assim por diante. Para nos referir a um deles, precisamosinformar a que coleção pertence o objeto e qual é esse objeto:

WWWWWorororororkSheets(“Plan1.xls”)kSheets(“Plan1.xls”)kSheets(“Plan1.xls”)kSheets(“Plan1.xls”)kSheets(“Plan1.xls”) Coleção de objetos WWWWWorororororksheetksheetksheetksheetksheet (plani- lhas), referindo-se ao objeto Plan1.

RangRangRangRangRange(“e(“e(“e(“e(“A1:C3”).SelectA1:C3”).SelectA1:C3”).SelectA1:C3”).SelectA1:C3”).Select Coleção de objetos RangRangRangRangRangeeeee que se refere às áreas.

WWWWWorororororkBookskBookskBookskBookskBooks Coleção de objetos WWWWWooooorrrrrksksksksksbookbookbookbookbook pas- tas de trabalho.

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

cap_05.p65 16/6/2004, 17:3844

Page 46: 91729948 Visual Basic Vba Excel 01

O editor do Visual Basic no Excel 45

Sub AlteraExibição() Início da macro (procedimento) With ActiveWindow Com a janela ativa .DisplayGridlines = False Não exibir linhas de grades .DisplayHeadings = False Não exibir cabeçalhos de linhas/colunas .Zoom = 75 Zoom de 75% End With Finaliza a janela ativaEnd Sub Finaliza a macro

ActiveCellActiveCellActiveCellActiveCellActiveCell ActiveChartActiveChartActiveChartActiveChartActiveChart

ActivActivActivActivActiveDialogeDialogeDialogeDialogeDialog ActivActivActivActivActiveXeXeXeXeX

ActivActivActivActivActiveMeneMeneMeneMeneMenuBaruBaruBaruBaruBar ActivActivActivActivActivePePePePePanelanelanelanelanel

ActivActivActivActivActivePrePrePrePrePrinterinterinterinterinter ActivActivActivActivActiveSheeteSheeteSheeteSheeteSheet

ActivActivActivActivActiveWeWeWeWeWindoindoindoindoindowwwww ActivActivActivActivActiveWeWeWeWeWorororororkbookkbookkbookkbookkbook

Experimente, por exemplo, digitar as seguintes instruções logoabaixo do código da sua macro:

Sub AlteraExibição()With ActiveWindow

.DisplayGridlines = False

.DisplayHeadings = False

.Zoom = 75End With

End Sub

Para que a macro seja executada na janela de códigos, utilize obotão ou a tecla F5. Ao voltar para a planilha, você verá que amacro realizou as seguintes tarefas:

Eventos da planilhaPara alterar eventos de planilha, selecione uma das planilhas na

janela PrPrPrPrProjetoojetoojetoojetoojeto e ative o objeto WWWWWorororororksheetksheetksheetksheetksheet, como mostra a figura:

cap_05.p65 16/6/2004, 17:3845

Page 47: 91729948 Visual Basic Vba Excel 01

46 Treinamento avançado em Excel

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

EventoEventoEventoEventoEvento Quando ocorreQuando ocorreQuando ocorreQuando ocorreQuando ocorre

ActivActivActivActivActivateateateateate Quando uma planilha é ativada.

BefBefBefBefBeforororororeDoubeDoubeDoubeDoubeDoubleClicleClicleClicleClicleClickkkkk Ao clicar duas vezes em uma planilha.

BefBefBefBefBeforororororeRightCliceRightCliceRightCliceRightCliceRightClickkkkk Ao clicar com o botão direito do mousesobre uma planilha.

CalculateCalculateCalculateCalculateCalculate Depois de toda a planilha ser recalculada.

ChangChangChangChangChangeeeee Quando as células das planilhas sãoalteradas pelo usuário.

DeactivDeactivDeactivDeactivDeactivateateateateate Quando a planilha é desativada.

SelectionChangSelectionChangSelectionChangSelectionChangSelectionChangeeeee Quando a seleção é alterada em umaplanilha.

cap_05.p65 16/6/2004, 17:3846

Page 48: 91729948 Visual Basic Vba Excel 01

Exibindo mensagens ao usuário 47

Exibindo mensagens ao usuário

As caixas de mensagem funcionam como a voz do programa, omeio de comunicação entre o aplicativo e a pessoa que o está utili-zando. São elas que avisam ao usuário quando acontece algum erro,permitem que ele escolha o que fazer a seguir, ou mesmo abremespaço para que ele passe dados ao programa.

O VBA oferece modelos prontos de caixa de mensagem; bastaescolher o tipo adequado e configurá-lo. Com isso, você conseguefacilmente guiar o usuário para que ele aproveite completamente odocumento do Excel que você criou. Afinal de contas, quem não secomunica se estrumbica!

• FUNÇÃO MSGBOX

É possível criar macros que exibam mensagens na tela, para in-formar o usuário sobre alguma operação que tenha sido executada,alertá-lo sobre erros etc. Só que, diferentemente do que temos feitoaté agora, a mensagem em si não pode ser criada apenas ligando ogravador de macros. Ela deve ser inserida como parâmetro para afunção MSGBOX, que tem a seguinte sintaxe:

MSGBOX “Mensagem”, tipo da caixa, “Título”

Em que:Mensagem Mensagem Mensagem Mensagem 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.

TTTTTipo da caixa ipo da caixa ipo da caixa ipo da caixa ipo da caixa – define o tipo da caixa de diálogo (se será umainformação, um alerta de erro etc.).

TítuloTítuloTítuloTítuloTítulo – Define o título da caixa de diálogo.

cap_06.p65 16/6/2004, 17:3847

Page 49: 91729948 Visual Basic Vba Excel 01

48 Treinamento avançado em Excel

Para testar a exibição de mensagens com a função MSGBOX,crie, na janela do Editor do Visual Basic, uma macro com as se-guintes instruções:

Sub TestaMensagem() MsgBox “Esta mensagem só contém o prompt”End Sub

Execute a macro pressionando a tecla F5. Você verá esta caixade mensagem:

• OUTROS EXEMPLOS DE MENSAGEM

Há vários tipos de caixa de mensagem. Vejamos agora um exem-plo contendo um botão de OKOKOKOKOK. 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 segun-da linha. Ele é usado para quebras de linha. Quando uma linha decomando não couber na linha da tela, quebre-a com um underscore.

Assim, mantém-se a continuidade do comando. O resultado do códi-go digitado é o seguinte:

cap_06.p65 17/6/2004, 10:3948

Page 50: 91729948 Visual Basic Vba Excel 01

Exibindo mensagens ao usuário 49

• CAPTURANDO RESPOSTAS DO USUÁRIO

Na maioria das vezes, queremos que o usuário possa interagircom a caixa de mensagem, escolhendo clicar em um botão ou inse-rir algum dado. O VBA disponibiliza botões prontos para você in-cluir na sua caixa de mensagem. Cada um deles tem um númerocorrespondente, bem como um código VBA. Veja quais são eles natabela a seguir:

BotãoBotãoBotãoBotãoBotão Número correspondenteNúmero correspondenteNúmero correspondenteNúmero correspondenteNúmero correspondente CódigCódigCódigCódigCódigo o o o o VBVBVBVBVBAAAAA

OKOKOKOKOK 1 VbOkCancelarCancelarCancelarCancelarCancelar 2 VbCancelAnAnAnAnAnularularularularular 3 VbAbortRepetirRepetirRepetirRepetirRepetir 4 VbRetryIgnorIgnorIgnorIgnorIgnorararararar 5 VbIgnore

SimSimSimSimSim 6 VbYesNãoNãoNãoNãoNão 7 VbNo

O código a seguir gera uma caixa de mensagem com três bo-tões: AnAnAnAnAnularularularularular, RepetirRepetirRepetirRepetirRepetir e IgnorIgnorIgnorIgnorIgnorararararar:

Sub TestaResposta () Resposta = MsgBox(“Pressione um dos _ botões abaixo:”, vbAbortRetryIgnore, _ “ATENÇÃO!!”) If Resposta = 3 Then MsgBox “Você pressionou ANULAR.”

End IfEnd Sub

Nessa caixa de mensagem, o usuário deve clicar em um dostrês botões:

BotãoBotãoBotãoBotãoBotão Número correspondenteNúmero correspondenteNúmero correspondenteNúmero correspondenteNúmero correspondente CódigCódigCódigCódigCódigo o o o o VBVBVBVBVBAAAAA

cap_06.p65 16/6/2004, 17:3849

Page 51: 91729948 Visual Basic Vba Excel 01

50 Treinamento avançado em Excel

O código do botão selecionado pelo usuário será armazenadoem uma variável chamada Resposta. Se o usuário pressionar o botãoAnAnAnAnAnularularularularular, o valor armazenado na variável será 3, e será gerada umanova mensagem.

Função INPUTBOXUm tipo de caixa de mensagem ainda mais interativo é aquele

em que o usuário pode inserir dados. Para isso, você pode usar afunção INPUTBOX. Vejamos um exemplo. O código

Sub Entrada() Resp = InputBox(“Qual é a palavra-chave?”) Range(“A1”) = RespEnd 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áriodeve inserir um valor de salário:

cap_06.p65 16/6/2004, 17:3850

Page 52: 91729948 Visual Basic Vba Excel 01

Exibindo mensagens ao usuário 51

• ADICIONANDO COMENTÁRIOS AO CÓDIGO

Comentários são trechos do código que não são executados, ser-vindo apenas para manter uma documentação sobre ele. Eles sãomuito úteis, inclusive para a própria pessoa que criou o código – porexemplo, se você tiver que atualizar o programa depois de muitotempo, vai ter condições de entender o que está expresso nele.

Para fazer com que um trecho do código seja um comentário noVBA, basta colocar um apóstrofo (‘) no início da linha, como noexemplo 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ávelEnd Sub

cap_06.p65 16/6/2004, 17:3851

Page 53: 91729948 Visual Basic Vba Excel 01

52 Treinamento avançado em Excel

Criando um formulário no VBANo dia-a-dia, não queremos nem ouvir falar em formulários: imedia-

tamente nos vem à mente qualquer idéia relacionada com burocracia...Mas, na hora de criar interfaces de macros, inclusive no Excel, os formu-lários são essenciais. Eles funcionam, grosso modo, como caixas de men-sagem turbinadas – muito turbinadas. Com um formulário, você podecoletar dados do usuário, pedir que ele escolha entre opções pré-exis-tentes, entre muitas outras atividades interativas. E as informações reco-lhidas 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. Tudoisso, claro, com os recursos do nosso velho conhecido VBA.

• CRIANDO UM FORMULÁRIO

No editor do Visual Basic do Excel, acesse o comando InserInserInserInserInseriririririr >UserFormUserFormUserFormUserFormUserForm.

Você pode definir características como nome, cor, estilo de borda,fonte de textos, entre outras, para o seu formulário. Para facilitar essasconfigurações, ative a janela PropriedadesPropriedadesPropriedadesPropriedadesPropriedades com o comando ExibirExibirExibirExibirExibir >JJJJJanela anela anela anela anela ‘Pr‘Pr‘Pr‘Pr‘Propropropropropriedades’iedades’iedades’iedades’iedades’, ou usando a tecla F4.

cap_07.p65 17/6/2004, 10:4052

Page 54: 91729948 Visual Basic Vba Excel 01

Criando um formulário no VBA 53

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

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

ControleControleControleControleControle NomeNomeNomeNomeNome UtilidadeUtilidadeUtilidadeUtilidadeUtilidadeSelecionar objetosSelecionar objetosSelecionar objetosSelecionar objetosSelecionar objetos Permite selecionar um ou mais

objetos dentro do formulário.RótuloRótuloRótuloRótuloRótulo Insere títulos e textos explicativos

dentro do formulário.Caixa de textoCaixa de textoCaixa de textoCaixa de textoCaixa de texto Permite que o usuário insira da-

dos em um campo.Caixa de combinaçãoCaixa de combinaçãoCaixa de combinaçãoCaixa de combinaçãoCaixa de combinação Utilizado para criar uma lista de

dados, permitindo a escolha deum deles.

• A BARRA DE FERRAMENTAS DE FORMULÁRIOS

cap_07.p65 16/6/2004, 17:3753

Page 55: 91729948 Visual Basic Vba Excel 01

54 Treinamento avançado em Excel

Caixa de listagem Caixa de listagem Caixa de listagem Caixa de listagem Caixa de listagem Semelhante à caixa de combinação,mas com a diferença de que osdados são exibidos em mais de umalinha ao mesmo tempo.

Caixa de seleção Caixa de seleção Caixa de seleção Caixa de seleção Caixa de seleção Utilizado para criar uma opçãode seleção.

Botão de opção Botão de opção Botão de opção Botão de opção Botão de opção Utilizado para criar um botão queseleciona uma determinada opção.

Botão de ativação Botão de ativação Botão de ativação Botão de ativação Botão de ativação Utilizado para criar um botãoque pode ser ativado ou não.

Moldura Moldura Moldura Moldura Moldura Utilizado para criar uma moldu-ra em volta de um conjunto decontroles, agrupando-os.

Botão de comando Botão de comando Botão de comando Botão de comando Botão de comando Utilizado para criar um botão decomando, como OKOKOKOKOK, CancelarCancelarCancelarCancelarCancelar,SalvarSalvarSalvarSalvarSalvar etc.

Faixa de tabulação Faixa de tabulação Faixa de tabulação Faixa de tabulação Faixa de tabulação Utilizado para apresentar umconjunto de informações em fai-xas separadas.

Multipágina Multipágina Multipágina Multipágina Multipágina Utilizado para apresentar váriasguias de dados, cada uma cominformações diferentes. Vocêpode, por exemplo, visualizar osdados pessoais de um cliente emuma guia, e os dados comerciaisem outra.

Barra de rolagem Barra de rolagem Barra de rolagem Barra de rolagem Barra de rolagem Utilizado para criar uma barrade rolagem no sentido horizon-tal ou vertical.

Barra de rotação Barra de rotação Barra de rotação Barra de rotação Barra de rotação Utilizado para incrementar valo-res em controles de textos(como número de prestações, %de juros etc.).

Imagem Imagem Imagem Imagem Imagem Utilizado para inserir imagens noformulário. São aceitos formatoscomo .jpg, .bmp, .ico, entre outros.

RefEdit RefEdit RefEdit RefEdit RefEdit Utilizado para exibir o endereço deum intervalo de células inserido ouselecionado em planilhas.

cap_07.p65 16/6/2004, 17:3754

Page 56: 91729948 Visual Basic Vba Excel 01

Criando um formulário no VBA 55

• INSERINDO UM CONTROLE

Agora que você já conhece os controles que podem ser inseridosem um formulário, veja como incluí-los:

Na Caixa de ferramentasCaixa de ferramentasCaixa de ferramentasCaixa de ferramentasCaixa de ferramentas, dê um clique sobre o controle desejado.

Na janela UserFormUserFormUserFormUserFormUserForm, posicione o cursor sobre o ponto em quedeseja inseri-lo.

Clique e arraste o mouse sobre a tela para desenhar o controle.

Criando um formulário simplesA Caixa de ferramentasCaixa de ferramentasCaixa de ferramentasCaixa de ferramentasCaixa 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 umformulário simples? Tente criar um modelo como o apresentado nafigura a seguir:

1

2

3

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

cap_07.p65 16/6/2004, 17:3755

Page 57: 91729948 Visual Basic Vba Excel 01

56 Treinamento avançado em Excel

• ALTERANDO O NOME DOS CONTROLES

Para capturar os dados que o usuário inserir nos controles do seuformulário, é necessário nomear os controles. Do contrário, não será pos-sível referir-se a eles mais tarde. O nome de um controle é definido porsua propriedade NameNameNameNameName, como descrito nos passos a seguir:

Na janela UserFormUserFormUserFormUserFormUserForm, dê um clique sobre o controle desejado.

Na janela PropriedadesPropriedadesPropriedadesPropriedadesPropriedades, dê um clique sobre a propriedade NameNameNameNameName.

Digite o nome que você quer dar ao controle.

Uma dica: para facilitar a identificação dos controles, é interessantepadronizar a nomenclatura. Um padrão muito utilizado é o de iniciar onome com três letras indicando qual o tipo de controle criado. Por exem-plo, lblVeículo: nesse caso, as letras “lbl” indicam que o controle é do tiporótulo (em inglês, label); e o nome específico desse controle do tipo rótu-lo está expresso logo em seguida (Veículo).

A propriedade CaptionCertos controles, como rótulo, botão de opção, caixa de texto, mol-

dura e botão de comando, possuem a propriedade CaptionCaptionCaptionCaptionCaption, a qualpermite acrescentar um texto de apresentação do controle. Suponhaque você tenha uma caixa de texto na qual o usuário deva digitar onome dele. Para que ele saiba o que deve fazer, é necessário que existaum texto explicativo, como “Digite aqui o seu nome”. Esse texto é defini-do na propriedade CaptionCaptionCaptionCaptionCaption.

• 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:

1

2

3

cap_07.p65 16/6/2004, 17:3756

Page 58: 91729948 Visual Basic Vba Excel 01

Criando um formulário no VBA 57

ObjetoObjetoObjetoObjetoObjeto NameNameNameNameName CaptionCaptionCaptionCaptionCaption

label1label1label1label1label1 lblNome Nome:

TTTTTeeeeextboxtboxtboxtboxtbox1x1x1x1x1 txtNome

Commandbutton1Commandbutton1Commandbutton1Commandbutton1Commandbutton1 cmdOk OK

Commandbutton2Commandbutton2Commandbutton2Commandbutton2Commandbutton2 cmdCancelar Cancelar

Label2Label2Label2Label2Label2 lblVeículo Veículo:

ListBox1ListBox1ListBox1ListBox1ListBox1 ltbVeículo

Frame1Frame1Frame1Frame1Frame1 frmPagamento Opções de pagamento

OptionButton1OptionButton1OptionButton1OptionButton1OptionButton1 optVista À vista

OptionButton2OptionButton2OptionButton2OptionButton2OptionButton2 optParcelado Financiado

label3label3label3label3label3 lblParcelas Nº de parcelas

TTTTTeeeeextBoxtBoxtBoxtBoxtBox2x2x2x2x2 txtParcelas

SpinButton1SpinButton1SpinButton1SpinButton1SpinButton1 spiParcelas

Label5Label5Label5Label5Label5 Preço:

CheckBox1CheckBox1CheckBox1CheckBox1CheckBox1 chkNovo Cliente novo

Label4Label4Label4Label4Label4 LBLPreço [em branco]

Inserindo uma imagemComo fazer para incluir uma imagem em seu formulário? É simples.

Você já sabe que o controle correspondente é o ImaImaImaImaImagggggememememem. Aproveite opasso-a-passo a seguir para definir a imagem que deve ser associada aocontrole ImagemImagemImagemImagemImagem que você já inseriu no seu formulário:

No formulário que você criou, dê um clique sobre o controlede imagem.

1

cap_07.p65 16/6/2004, 17:3757

Page 59: 91729948 Visual Basic Vba Excel 01

58 Treinamento avançado em Excel

Na janela PropriedadesPropriedadesPropriedadesPropriedadesPropriedades, vá até a propriedade PicturePicturePicturePicturePicture e clique nobotão com as reticências.

Será aberta uma janela de navegador, para que você escolha a ima-gem a ser inserida. Procure o arquivo de imagem desejado e dê umduplo clique sobre ele.

Para que a figura não apareça cortada, altere a propriedadePicturPicturPicturPicturPictureSizeModeeSizeModeeSizeModeeSizeModeeSizeMode. Assim, a figura vai se adaptar ao tamanho da caixa.4

Propriedades de textoDepois de alterar as propriedades NameNameNameNameName e CaptionCaptionCaptionCaptionCaption dos controles, o

seu formulário terá esta aparência (exceto pela figura, que será aquelaque você tiver selecionado):

2

3

Agora, você deve definir uma propriedade para o objeto do tipo caixade texto txtNome. Para permitir que o nome do cliente seja digitado emmais de uma linha, defina a propriedade MultiLineMultiLineMultiLineMultiLineMultiLine como TTTTTrrrrrueueueueue. Para isso,basta dar um clique sobre o controle txtNome no seu formulário e alterara propriedade desejada na janela PropriedadesPropriedadesPropriedadesPropriedadesPropriedades.

Alterando a ordem dos controlesCom o formulário já construído, você pode navegar pelos controles

existentes usando a tecla Tab. Com ela, os objetos são selecionados deacordo com a ordem em que foram criados. Mas é possível alterar essaordem, para, por exemplo, torná-la compatível com a das colunas deuma planilha. Para isso, vá ao menu ExibirExibirExibirExibirExibir > Ordem de tabulaçãoOrdem de tabulaçãoOrdem de tabulaçãoOrdem de tabulaçãoOrdem de tabulação.

Preparando o ambiente dos dadosPreparando o ambiente dos dadosNa janela que aparece, clique sobre o controle que deseja mudar

de posição e, depois, sobre os botões Mover para cimaMover para cimaMover para cimaMover para cimaMover para cima ou MoverMoverMoverMoverMoverpara baixopara baixopara baixopara baixopara baixo.

cap_07.p65 16/6/2004, 17:3758

Page 60: 91729948 Visual Basic Vba Excel 01

Criando um formulário no VBA 59

Associando a tabela de veículos com o controle

No Editor do Visual Basic, selecione o objeto lbtVeículos.

1

2

1

Preparando a tabela com os dados dos veículosEm Plan2 devemos digitar a tabela com os veículos que temos

na revendedora:

1

Nosso intuito é relacionar o formulário que criamos com planilhasdo 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 edi-tor do VBA.

Na planilha, vamos preparar o ambiente em que os dados se-rão recebidos.

Construa a seguinte planilha:

2 Nomeie a planilha como Cadastro.

Renomeie a planilha como Veículos.

Selecione o intervalo A2:C19 e nomeie-o como VEÍCULOS.

cap_07.p65 16/6/2004, 17:3759

Page 61: 91729948 Visual Basic Vba Excel 01

60 Treinamento avançado em Excel

2

3

4

5

Esse controle deverá apresentar a tabela de produtos que está naplanilha Veículos.

Defina a propriedade RoRoRoRoRowSourwSourwSourwSourwSourcecececece como Veículos (nome da áreanomeada).

Para que sejam apresentadas as duas colunas com o modelo e oano do veículo, altere a propriedade ColumnCountColumnCountColumnCountColumnCountColumnCount para 2.

Para exibir os títulos altere a propriedade ColumnHeadsColumnHeadsColumnHeadsColumnHeadsColumnHeads para True.

Exibindo o formulárioAgora 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 noeditor do VBA:

Na janela Projeto, ative a janela de código com este botão:

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

Esse código vai posicionar o cursor sobre a planilha Cadastro(Plan1) e abrir o formulário frmVeículos.

Execute a macro para ver o formulário no centro da tela.

1

2

3

4

cap_07.p65 16/6/2004, 17:3760

Page 62: 91729948 Visual Basic Vba Excel 01

Criando um formulário no VBA 61

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óriaQuando o usuário clicar sobre o botão CancelarCancelarCancelarCancelarCancelar, o formulário deve-

rá ser removido da memória, ou, em outras palavras, descarregado. Paraque isso aconteça, você deve associar um código a esse botão. Dê umclique 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 oscontroles do formulário. Isso porque controles como o botão de OKOKOKOKOK, porexemplo, exigem alguns conhecimentos que serão apresentados nospróximos capítulos. Mas não desanime! Ao terminar o livro, você terá umformulário totalmente funcional, e, o melhor, inteiramente feito por você.

• 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 ousuário selecioná-lo a partir da lista de veículos. Portanto, vamos adicionarcódigo ao controle ltbVeículo. Dê um duplo clique no controleltbVeí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

cap_07.p65 16/6/2004, 17:3761

Page 63: 91729948 Visual Basic Vba Excel 01

62 Treinamento avançado em Excel

Manipulando células

Hora de mais diversão! Se, no capítulo anterior, você gostou de criar umaplanilha que interage com um formulário, certamente vai ficar ainda maisfeliz com o que as macros podem fazer pelo seu sistema de cadastro. Nestecapí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 em VBA: basta seguir os passo-a-passos 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 simplesmenteusando o cursor ou o mouse, ou ainda a Caixa de nome. Mas, suponha quevocê 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 essascélulas e somar os seus valores. Usando alguns objetos e propriedades doVBA, podemos facilmente criar uma macro que realize esse deslocamen-to do cursor. De forma semelhante, você pode usar uma macro parainserir fórmulas em células específicas.

O objetivo deste capítulo é que você se familiarize com o VBA. Portan-to, você só vai voltar a usar o seu sistema de cadastro no final.

Verificando a área selecionadaEm 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:

Abra um novo documento no Excel e vá até o editor do VBA. Najanela de código, digite:

Sub VerSeleção()MsgBox ActiveWindow.RangeSelection.Address`Na janela atual, área selecionada, endereço

End Sub

1

Manipulando células

cap_08.p65 16/6/2004, 17:3662

Page 64: 91729948 Visual Basic Vba Excel 01

Manipulando células 63

2

Inserindo fórmulas em uma célulaSuponha que você queira inserir uma fórmula na célula D6 de uma

planilha chamada Cadastro. Uma possibilidade interessante é usar oobjeto RangeRangeRangeRangeRange e sua propriedade FormulaFormulaFormulaFormulaFormula. O objeto RangeRangeRangeRangeRange define umintervalo de células, e a propriedade FormulaFormulaFormulaFormulaFormula permite associar umafórmula a esse intervalo.

Vamos ver um exemplo de como utilizar RangRangRangRangRangeeeee e FFFFFororororormmmmmulaulaulaulaula:

Em um novo documento do Excel, ou no mesmo em que você inse-riu 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 nacélula D6.

• O MÉTODO SELECT E A PROPRIEDADE SELECTION

O método SelectSelectSelectSelectSelect ativa planilhas e objetos. Já a propriedade SelectionSelectionSelectionSelectionSelectionretorna um objeto que representa a área selecionada na planilha ativa.

Quando utilizamos o gravador de macros, o método SelectSelectSelectSelectSelect e a propri-edade SelectionSelectionSelectionSelectionSelection são ativados automaticamente. Veja um exemplo:

Sub Selecionar() Sheets(“Sheet1”).Select Range(“A1”).Select ActiveCell.FormulaR1C1 = “Name”

1

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

cap_08.p65 16/6/2004, 17:3663

Page 65: 91729948 Visual Basic Vba Excel 01

64 Treinamento avançado em Excel

Range(“B1”).Select ActiveCell.FormulaR1C1 = “Address” Range(“A1:B1”).Select Selection.Font.Bold = TrueEnd SubOutra maneira de realizar a mesma atividade efetuada por essa

macro é sempre utilizar o objeto Sheet1Sheet1Sheet1Sheet1Sheet1 e fazer várias atividades aomesmo tempo, sem a necessidade de indicar que o mesmo deve serselecionado. 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 WithEnd Sub

• EXEMPLOS DE UTILIZAÇÃO DO MÉTODO RANGE

O RangRangRangRangRangeeeee é um dos métodos mais importantes na seleção decélulas. Veja um exemplo de como utilizá-lo (dessa vez, sem mexerna 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”).SelectEnd Sub

cap_08.p65 16/6/2004, 17:3664

Page 66: 91729948 Visual Basic Vba Excel 01

Manipulando células 65

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

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

Range(ActiveCell, “B6”).Select

Range(“B3:C8”).Select

cap_08.p65 16/6/2004, 17:3665

Page 67: 91729948 Visual Basic Vba Excel 01

66 Treinamento avançado em Excel

Range(“B2”).Select

ActiveCell.Range(“B2”).Select

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

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

End Sub

cap_08.p65 16/6/2004, 17:3666

Page 68: 91729948 Visual Basic Vba Excel 01

Manipulando células 67

Propriedade OffsetA propriedade Offset Offset Offset Offset Offset retorna um objeto RangeRangeRangeRangeRange, que nada mais é do

que um intervalo deslocado a partir da célula atual. Sua sintaxe é:

Expressão.Offset(RowOffset, ColumnOffset)

Em que:RowOffsetRowOffsetRowOffsetRowOffsetRowOffset – é o número de linhas – que pode ser um valor positi-

vo, negativo ou 0 (zero) – que o intervalo deve ser deslocado. Valorespositivos deslocam o intervalo para baixo, e os negativos, para cima. Ovalor padrão é 0.

ColumnOfColumnOfColumnOfColumnOfColumnOffsetfsetfsetfsetfset – é o número de colunas – que pode ser umvalor positivo, negativo ou 0 (zero) – que o intervalo deve ser deslo-cado. Valores positivos deslocam o intervalo para a direita, e os ne-gativos, para a esquerda. O valor padrão é 0.

Veja um exemplo de utilização da propriedade OfOfOfOfOffsetfsetfsetfsetfset:

ActiveCell.Offset(10,7).Select

De acordo com essa linha de código, o intervalo será deslocado dezlinhas 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. É oque vamos fazer agora. A primeira medida a tomar para que esseprocedimento dê certo é garantir que, no início da atividade damacro, o cursor esteja sempre na primeira célula que deve receberalgum valor (no nosso caso, a célula A5 da planilha Cadastro). De-pois, você deve deslocar o cursor ao longo da planilha, para que ascélulas recebam os valores dos campos do formulário. Para isso, sigaestes passos:

Abra a sua pasta de trabalho e vá até a planilha Cadastro. Nomeie acélula A5 como ini.

1

cap_08.p65 16/6/2004, 17:3667

Page 69: 91729948 Visual Basic Vba Excel 01

68 Treinamento avançado em Excel

Dê um duplo clique no botão OKOKOKOKOK.

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 desloquepela planilha, de modo a inserir dados em células diferentes. Assim, cami-nhamos mais um pouco em direção ao sistema de cadastro finalizado.

3

2

cap_08.p65 16/6/2004, 17:3668

Page 70: 91729948 Visual Basic Vba Excel 01

Variáveis de memória 69

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 paraguardar essa informação. Imagine que você o fizesse, e separasseum canto da sua memória para a informação chamada “o que eucomi ontem no almoço”. Hoje, o valor dessa informação poderiaser “dobradinha”. Amanhã, o valor pode ser outro, como “frangoxadrez”. Bem, no caso de nossa humilde equipe, esse valor variaconforme o cardápio do restaurante por quilo. Mas, independente-mente 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 ele-mentos que recebem valores, os quais são armazenados no espaçoreservado na memória. Por enquanto, vamos deixar um pouco delado o formulário que você criou – mas isso não significa que eleserá esquecido.

• CONCEITO DE VARIÁVEIS DE MEMÓRIA

Uma variável é um valor que fica armazenado na memória docomputador enquanto o procedimento está rodando. Assim que oprocedimento termina, a variável é removida da memória – daí seunome ser vvvvvararararariáviáviáviáviável de memórel de memórel de memórel de memórel de memóriaiaiaiaia.

As variáveis de memória no VBA armazenam valores de proprie-dades e resultados de cálculos.

• REGRAS PARA NOMEAÇÃO EM VBA

Para nomear procedimentos, constantes, variáveis e argumentosem qualquer módulo, obedeça sempre às seguintes regras:

• Não devem ser utilizados pontos, espaços, vírgulas, pontos deexclamação ou símbolos ($, @, # etc.).

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

Page 71: 91729948 Visual Basic Vba Excel 01

70 Treinamento avançado em Excel

• 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 doMicrosoft Excel.

• COMO DEFINIR VARIÁVEIS

Para entender melhor como trabalham as variáveis, digite as se-guintes 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áveisEnd Sub

Nesse exemplo, definimos cinco variáveis de tipos diferentes.Para ver como elas são armazenadas na memória, observe a janelaLocalLocalLocalLocalLocal, que pode ser acessada com o comando Exibir > Janela >Exibir > Janela >Exibir > Janela >Exibir > Janela >Exibir > Janela >VVVVVararararariáviáviáviáviáveis locais:eis locais:eis locais:eis locais:eis locais:

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

cap_09.p65 17/6/2004, 10:4170

Page 72: 91729948 Visual Basic Vba Excel 01

Variáveis de memória 71

solicitar ao VBA que execute o script passo a passo, o que pode ser feitopressionando a tecla F8. Depois de fazer isso, você verá que o Excelconsegue definir cada uma das variáveis, mas continua parado na primei-ra linha do procedimento. Isso acontece porque as variáveis foram defini-das, mas ainda não têm valores associados. Pressione F8 sucessivas vezespara que todas as variáveis sejam vistas:

Quando o procedimento chega ao fim, as variáveis são zeradas, e omódulo nem ao menos se lembra de que elas foram utilizadas. A janelaVVVVVararararariáviáviáviáviáveis locaiseis locaiseis locaiseis locaiseis locais permite que você saiba qual valor está realmente ar-mazenado na memória naquele instante. Essa propriedade é muito útilnos casos em que deparamos com um erro de cálculo e não sabemos oque o Excel está tomando como base.

As variáveis podem armazenar valores e objetos. Para atribuirum valor a uma variável, podemos digitar seu nome, o sinal de iguale 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 decla-rar as variáveis automaticamente, por meio da instrução OptionOptionOptionOptionOptionExplicitExplicitExplicitExplicitExplicit, que veremos mais adiante.

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

Page 73: 91729948 Visual Basic Vba Excel 01

72 Treinamento avançado em Excel

Ao declarar uma variável, estamos solicitando ao VBA que reserveum certo espaço na memória para armazenar o conteúdo dela. Existemdiversos tipos de variável (dependendo do tipo de valor que ela podereceber, como caracteres, números, valores lógicos etc.). Especificando-se o tipo da variável, minimiza-se o espaço de memória a ser reservadopara ela. A declaração de variáveis é feita de acordo com esta sintaxe:

Dim nomedavariável As tipodavariável

Em que:DimDimDimDimDim – é o termo proveniente de dimensionar, que, por sua vez,

significa alocar. O DimDimDimDimDim faz com que uma determinada quantidadede memória seja alocada para a variável.

Como dissemos no início do capítulo, você pode utilizar a ins-trução Option Explicit Option Explicit Option Explicit Option Explicit Option Explicit (na seção de declarações, que fica no iníciodo procedimento) para que o VBA passe a exigir que todas as variá-veis sejam declaradas. Uma vez incluída essa instrução, o VBA verifi-ca o código na primeira vez em que o procedimento de um móduloé executado, para assegurar que todas as variáveis tenham sido de-claradas. Caso não encontre alguma das declarações, ele pára exata-mente no ponto em que encontrou a variável não declarada, eretorna um aviso a respeito.

• ESCOPO DAS VARIÁVEIS

O escopo de uma variável refere-se ao domínio dentro do qualela pode ser acessada. O VBA permite que a variável tenha um des-tes 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.

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

Page 74: 91729948 Visual Basic Vba Excel 01

Variáveis de memória 73

Variáveis do nível de procedimentoPara declarar uma variável cujo escopo seja restrito ao procedi-

mento em que ela se encontra, utilize a instrução DimDimDimDimDim. Assim, elaficará disponível somente nesse procedimento, e nenhum outro po-derá 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óduloPara declarar uma variável de escopo restrito a um único módulo,

inclua-a na seção de declarações usando a palavra-chave PrPrPrPrPrivivivivivateateateateate:

Option Private ModulePrivate NomeLivro

No caso, poderíamos utilizar DimDimDimDimDim no lugar de PrPrPrPrPrivivivivivateateateateate na segun-da linha do código, mas, para enfatizar as variáveis declaradas noinício do módulo, a instrução mais utilizada é a PrPrPrPrPrivivivivivateateateateate.

Variáveis de nível globalPara declarar uma variável disponível a todos os módulos exis-

tentes na pasta de trabalho, use a palavra-chave PubPubPubPubPublicliclicliclic sem a instru-ção Option PrOption PrOption PrOption PrOption Privivivivivate Moduleate Moduleate Moduleate Moduleate Module. O resultado será uma variável chama-da GlobalGlobalGlobalGlobalGlobal, 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:

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

Page 75: 91729948 Visual Basic Vba Excel 01

74 Treinamento avançado em Excel

(*) VVVVVararararariantiantiantiantiant é um tipo especial de variável, que pode conter qualquertipo de dado. Se, na declaração de uma variável, não for especificado otipo, o Excel automaticamente irá criar uma variável VVVVVararararariantiantiantiantiant, que requermais espaço de memória do que os demais tipos.

Tip

o d

e d

ado

sT

ipo

de

dad

os

Tip

o d

e d

ado

sT

ipo

de

dad

os

Tip

o d

e d

ado

sP

refi

xo

Pre

fix

oP

refi

xo

Pre

fix

oP

refi

xo

Ex

emp

lo E

xem

plo

Ex

emp

lo E

xem

plo

Ex

emp

lo

Mem

óri

a re

qu

erid

a

Mem

óri

a re

qu

erid

a

Mem

óri

a re

qu

erid

a

Mem

óri

a re

qu

erid

a

Mem

óri

a re

qu

erid

a

In

terv

alo

de

dad

os

In

terv

alo

de

dad

os

In

terv

alo

de

dad

os

In

terv

alo

de

dad

os

In

terv

alo

de

dad

os

B

oo

lean

Bln

Bln

Rea

dy

2 b

ytes

Tru

e o

u f

alse

C

urr

ency

Cu

rC

urC

ust

o8

byt

es

Dat

eD

tD

tPgt

o8

byt

es

D

ou

ble

Db

Db

Co

nt

8 b

ytes

In

tege

rIn

t

In

tQto

sFil

ho

s2

byt

es

-3

2.7

68

a 3

2.7

67

Lo

ng

Lng

LngV

end

as4

byt

es

-

2.14

7.48

3.64

8 a

2.14

7.48

3.64

8

Si

ngl

eSn

g

Sn

gTax

aJu

ros

4 b

ytes

- 3

.402

823E

38 a

–1.

4012

98E-

45

St

rin

gSt

r

StrP

rim

No

me

Var

ian

t* V

nt

Vn

tRes

po

sta

16

byt

es

B

yte

B

yte

Byt

Byt

Tecl

a1

byt

e0

a 2

55

10 b

ytes

+ c

omp

rim

ento

da s

trin

g (t

exto

)

922.

337.

203.

685.

477,

5808

a -

922.

337.

203.

685.

477,

5808

1 d

e ja

nei

ro d

e 1

00

a 3

1 d

ed

ezem

bro

de

99

99

1.7

9769

3134

8622

32e3

08 a

–4.

9406

5645

8412

4e-3

24

0 a

255

Zer

o a

ap

rox

imad

amen

te2

bil

es d

e ca

ract

eres

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

Page 76: 91729948 Visual Basic Vba Excel 01

Estruturas de programação 75

Estruturas de programaçãoSeus amigos o chamam para ir ao cinema em um dia cinzento. Você,

resfriado, responde: “Se não chover eu vou, mas, se chover, prefiro ficarem casa...”. Pronto! Você acaba de usar uma estrutura de programaçãocondicional. Como você vê, não é necessário ser um daqueles gêniosdos filmes de ficção adolescente para compreender princípios de pro-gramação. Fluxos de código são mera questão de lógica. Você só precisase familiarizar com a linguagem de programação e treinar um pouco.

Neste capítulo, você verá como criar rotinas de programaçãoque tomem diferentes decisões de acordo com a escolha do usuá-rio, fazendo uso das estruturas de programação. No sistema de ca-dastro que você está construindo, por exemplo, vamos fazer comque 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 programadeve decidir por qual caminho seguir, de acordo com o resultadode um teste condicional.

As estruturas IfAs estruturas do tipo If If If If If disponibilizam opções para avaliar a

entrada de dados do usuário, e executam afirmações em uma con-dição básica como resposta. Embora tenhamos três variações daestrutura IfIfIfIfIf, todas elas são iniciadas com a declaração IfIfIfIfIf ... ThenThenThenThenThen e,em geral, terminam com End IfEnd IfEnd IfEnd IfEnd If.

Essa declaração avalia se uma condição é verdadeira ou falsa, edireciona o fluxo do programa de acordo com o solicitado. Se maisde uma condição na estrutura é verdadeira, somente o código espe-cificado como primeira condição verdadeira será executado.

• If ... Then

Em geral, usa-se essa estrutura quando o programa possui umadecisão do tipo “um ou outro”. Ela aceita dois tipos de sintaxe:

cap_10.p65 16/6/2004, 17:3575

Page 77: 91729948 Visual Basic Vba Excel 01

76 Treinamento avançado em Excel

If [condição] Then Claúsula

Repare que, nesse modelo, não usamos a instrução End IfEnd IfEnd IfEnd IfEnd If. Essetipo de declaração pode ser usado nos casos em que a estrutura IfIfIfIfIfse 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úsulaEnd Sub

Nesse segundo tipo de sintaxe, a cláusula da estrutura não estána mesma linha em que se encontra a palavra ThenThenThenThenThen. Ou seja, a estru-tura se estende por mais de uma linha. Nesse caso, devemos fechá-la com End IfEnd IfEnd IfEnd IfEnd If, como no exemplo a seguir:

Sub Demonstra If() If ActiveSheet.Name <> “EXEMPLO” Then MsgBox “Você está na planilha errada!” End IfEnd Sub

• • • • • Aplicando a estrutura IfIfIfIfIf no formulário

Vamos, agora, aplicar o que aprendemos sobre IfIfIfIfIf no sistema decadastro que você criou. No formulário de cadastro, é necessárioverificar o conteúdo de Opções de paOpções de paOpções de paOpções de paOpções de pagamentogamentogamentogamentogamento para definir qualcaminho o programa vai seguir: se o usuário selecionar a opção ÀÀÀÀÀvistavistavistavistavista, deverá ser inserida a palavra À vista na célula; caso contrário,deverá ser inserida a palavra Financiado. Para fazer isso, siga estepasso-a-passo:

cap_10.p65 16/6/2004, 17:3576

Page 78: 91729948 Visual Basic Vba Excel 01

Estruturas de programação 77

3

1

2

Abra o arquivo com o sistema de cadastro. No editor do VBA, dê umduplo clique sobre o botão cmdOK, para acessar seu código.

Agora você vai adicionar uma estrutura IfIfIfIfIf para testar se a caixa deopção optVista está ativada. Portanto, precisamos substituir, no código,as linhas referentes ao controle optVista por novas instruções. Parafazer 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).SelectActiveCell.Value = “0” ‘número de parcelas‘é 0 txtParcelas.Enabled = False

ElseActiveCell.Value = “Financiado”ActiveCell.Offset(0,1).SelectActiveCell.Value = Me.spiParcelas.Value

End If

Com essas linhas de código, se o usuário selecionar a opçãooptVista, a célula correspondente na planilha será preenchida como valor “À vista”. Do contrário, será preenchida com o valor “Finan-ciado”. Vamos, agora, inserir instruções para verificar se a caixachkNovo foi ativada. Ainda no código para o botão cmdOK, procu-re 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:

cap_10.p65 16/6/2004, 17:3577

Page 79: 91729948 Visual Basic Vba Excel 01

78 Treinamento avançado em Excel

If Me.chkNovo.Value = True ThenActiveCell.Value = “SIM”

ElseActiveCell.Value = “NÃO”

End If

Essas linhas fazem com que, se o usuário for um cliente novo, acélula correspondente na planilha seja preenchida com o valor SIMe, se não for, ela seja preenchida com o valor NÃO.

A estrutura Case SelectEsta estrutura é ideal para testar um valor dentre vários possí-

veis. Sua sintaxe é:

Select Case [expressão]Case [expressão 1]

DeclaraçãoCase [expressão 2]

Declaração... …

Case [else]Declaração

End Select

A estrutura Case SelectCase SelectCase SelectCase SelectCase Select avalia o resultado da expressão umaúnica vez, comparando-o, depois, com outros valores, para determi-nar qual bloco de código será chamado.

Vamos utilizar Case SelectCase SelectCase SelectCase SelectCase Select para manipular os dados a respeito donúmero de parcelas em nosso sistema de cadastro. Siga estes passos:

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

1

cap_10.p65 16/6/2004, 17:3578

Page 80: 91729948 Visual Basic Vba Excel 01

Estruturas de programação 79

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 CaseSelect CaseSelect CaseSelect CaseSelect Case, somente uma das declaraçõesdisponíveis será executada.

• ESTRUTURAS DE LOOPING

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

Estrutura Do ... LoopEsta 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 aexecutar a próxima instrução.

Para sair de uma estrutura Do ...Do ...Do ...Do ...Do ... Loop Loop Loop Loop Loop deve-se utilizar a instruçãoExit DoExit DoExit DoExit DoExit Do. O ideal é usá-la após a avaliação de alguma condição, como, porexemplo, If ...If ...If ...If ...If ... ThenThenThenThenThen. Nesse caso, se a condição for verdadeira, o procedi-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 ...Do ...Do ...Do ...Do ... Loop Loop Loop Loop Loop, que realiza o looptrês vezes consecutivas:

Sub TestandoDoLoop() Dim Contador As Integer ‘Declara a variável Contador, que irá

cap_10.p65 16/6/2004, 17:3579

Page 81: 91729948 Visual Basic Vba Excel 01

80 Treinamento avançado em Excel

‘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 IfLoop Until Contador > 3MsgBox “Saímos do loop agora.”

End Sub

Para visualizar o funcionamento desse procedimento, vá até aprimeira linha do código e pressione a tecla F8. Você verá que oExcel lê e executa a linha de código, passando então para a linhaseguinte, e prosseguindo até o final do bloco de instruções.

Estrutura For ... NextA estrutura FFFFFor ...or ...or ...or ...or ... Ne Ne Ne Ne Nextxtxtxtxt faz com que um bloco de instruções rode

um número predefinido de vezes. Esse número é estabelecido logo naprimeira linha. A última linha deve conter apenas a palavra NeNeNeNeNextxtxtxtxt. Entreessas duas instruções (FFFFFor ...or ...or ...or ...or ... Ne Ne Ne Ne Nextxtxtxtxt), insere-se o código a ser repetido.

For Condição[bloco de instruções]

Next

No exemplo a seguir, criamos um contador (representado poruma variável) para controlar o número de vezes que o bloco deinstruções deve ser executado. Confira:

cap_10.p65 16/6/2004, 17:3580

Page 82: 91729948 Visual Basic Vba Excel 01

Estruturas de programação 81

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:

• O loop começa com a palavra FFFFFororororor, seguida do nome da variávelContador 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 … NextAo contrário da estrutura FFFFFor ...or ...or ...or ...or ... Ne Ne Ne Ne Nextxtxtxtxt comum, a FFFFFor Eacor Eacor Eacor Eacor Each ...h ...h ...h ...h ...

NeNeNeNeNextxtxtxtxt não exige que se defina o número máximo de vezes que oloop deverá ser executado. Por isso, ela permite trabalhar com to-dos os objetos existentes em uma coleção. Basta definir uma variá-vel para representar o objeto desejado e sua respectiva coleção.

cap_10.p65 16/6/2004, 17:3581

Page 83: 91729948 Visual Basic Vba Excel 01

82 Treinamento avançado em Excel

For each Condição [bloco de instruções]Next

Veja um exemplo de utilização do FFFFFor Eacor Eacor Eacor Eacor Each ...h ...h ...h ...h ... Ne Ne Ne Ne Nextxtxtxtxt:

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 loopsAs estruturas de looping podem ser usadas para aplicar inúmeros

recursos aos seus programas. Veja um exemplo interessante:

Listando arquivos existentesVejamos 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

cap_10.p65 16/6/2004, 17:3582

Page 84: 91729948 Visual Basic Vba Excel 01

Estruturas de programação 83

‘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 ... UntilO código do exemplo anterior mostrava apenas um arquivo do

diretório (no caso, o primeiro de uma lista). Se você quisesse prolongaresse mesmo código para que ele listasse todos os elementos de umalista contendo 200 arquivos, ele ficaria imenso, e o trabalho exigido seriadesumano. Para conseguir esse resultado de maneira inteligente e semsuar a camisa, use a estrutura Do ... UntilDo ... UntilDo ... UntilDo ... UntilDo ... Until, de modo que a execução dobloco de instruções seja repetida até a aplicação não encontrar maisnenhum 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

cap_10.p65 16/6/2004, 17:3583

Page 85: 91729948 Visual Basic Vba Excel 01

84 Treinamento avançado em Excel

Criando funçõesO Excel tem mais de 400 funções. É função a rodo! A maioria

dos usuários conhece apenas uma parte delas, já que algumas aten-dem a necessidades muito específicas, de interesse para poucaspessoas. Mas, mesmo dispondo dessa enorme quantidade de fun-ções, você pode querer ou precisar de alguma que não conste dalista. 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 pode-rá ser usada quantas vezes você quiser, exatamente como qualquerfunção nativa do Excel.

• CRIANDO FUNÇÕES

Até aqui, você trabalhou com a instrução SubSubSubSubSub, com a qual desen-volveu suas macros. Para criar funções, vamos começar a usar a ins-trução FunctionFunctionFunctionFunctionFunction. As funções criadas ficam armazenadas na lista defunções do Excel, juntamente com as demais, como SOMA, MÉDIA,DATA etc.. Elas modificam apenas valores nas planilhas, nunca suaestrutura 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çõesNome = resultado

End Function

Experimente criar a sua própria função, de acordo com a orien-tação a seguir:

No editor do VBA, crie um novo módulo usando o comandoInserInserInserInserInseriririririr > MóduloMóduloMóduloMóduloMódulo.

Digite estas linhas de código para criar a função FACADA:

1

2

cap_11.p65 16/6/2004, 17:3484

Page 86: 91729948 Visual Basic Vba Excel 01

Criando funções 85

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 inteiroFACADA = 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:

2 Clique sobre o botão InserInserInserInserInserir funçãoir funçãoir funçãoir funçãoir função. Na janela que se abrir, nocampo Ou selecione uma categOu selecione uma categOu selecione uma categOu selecione uma categOu selecione uma categorororororiaiaiaiaia, escolha a opção TTTTTodasodasodasodasodas:

cap_11.p65 16/6/2004, 17:3485

Page 87: 91729948 Visual Basic Vba Excel 01

86 Treinamento avançado em Excel

3

4

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

Procure-a e dê um clique sobre ela. Depois, preencha os cam-pos requeridos com os intervalos de células adequados, como mos-tra a figura a seguir:

Dê OK OK OK OK OK para ver o resultado.

cap_11.p65 16/6/2004, 17:3486

Page 88: 91729948 Visual Basic Vba Excel 01

Personalizando o sistema 87

Personalizando o sistemaO Excel possui algumas barras de ferramentas e uma barra de

menus padrão para agilizar o trabalho. Mas você também pode cri-ar suas próprias barras e menus, reunindo nela as funcionalidadesque costuma usar com mais freqüência. E mais: é possível criar no-vos botões para as macros que você porventura tenha desenvolvi-do no Excel. É como brincar com um jogo de montar, cujo resulta-do seja um ambiente de trabalho personalizado.

Neste capítulo, você verá como criar e manipular barras de fer-ramentas e menus tanto por meio de linhas de código quanto pormeio de comandos.

• ROTINA DE LIMPEZA DOS DADOS PARA NOVOCADASTRAMENTO

Nos capítulos anteriores, você associou ações aos controles do seusistema de cadastro. Assim, o usuário pode entrar no formulário e inse-rir os dados, os quais serão usados para preencher as planilhas corres-pondentes. O que falta, portanto, é um método que permita que, quan-do 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 formaque, ao se reiniciar o sistema de cadastro, eles apareçam em brancopara a digitação de novos dados. É muito simples fazer isso:

Abra o documento do sistema de cadastro.

No editor do VBA, dê um duplo clique no botão cmdOK.

Na janela de código, logo acima da instrução End SubEnd SubEnd SubEnd SubEnd Sub, digite oseguinte bloco de instruções:

Me.txtNome = “”Me.txtParcelas = “”Me.ltBVeículo.ListIndex = -1Me.optVista = FalseMe.lblPreço = “”

1

2

3

cap_12.p65 16/6/2004, 17:3187

Page 89: 91729948 Visual Basic Vba Excel 01

88 Treinamento avançado em Excel

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

• CRIANDO UMA BARRA DE FERRAMENTAS

Qualquer aplicativo do Windows pode conter várias barras deferramentas; você pode, inclusive, criar uma, utilizando comandosou por meio de códigos. Criar uma nova barra de ferramentas utili-zando comandos é muito simples:

Em um novo documento do Excel, vá ao menu FFFFFerererererrrrrramentas amentas amentas amentas amentas >PPPPPererererersonalizar sonalizar sonalizar sonalizar sonalizar ou Exibir Exibir Exibir Exibir Exibir > BarBarBarBarBarrrrrras de fas de fas de fas de fas de ferererererrrrrramentasamentasamentasamentasamentas > PPPPPererererersonalizarsonalizarsonalizarsonalizarsonalizar.

Clique na guia BarBarBarBarBarrrrrras de fas de fas de fas de fas de ferererererrrrrramentasamentasamentasamentasamentas, e em seguida, em NoNoNoNoNovvvvvaaaaa

Na caixa Nome da barNome da barNome da barNome da barNome da barrrrrra de fa de fa de fa de fa de ferererererrrrrramentasamentasamentasamentasamentas, nomeie a sua barracomo CADASTRO DE VEÍCULOS e clique em OKOKOKOKOK:

1

2

3

Clique na guia ComandosComandosComandosComandosComandos.

Selecione uma opção na caixa CategCategCategCategCategorororororiasiasiasiasias. Repare que uma pe-quena barra de ferramentas vazia aparecerá ao lado da janela quevocê está usando. Essa é a sua nova barra de ferramentas.

A partir da caixa Comandos Comandos Comandos Comandos Comandos, arraste o comando desejado paraa barra de ferramentas que você criou:

4

5

6

cap_12.p65 16/6/2004, 17:3188

Page 90: 91729948 Visual Basic Vba Excel 01

Personalizando o sistema 89

Clique no botão NoNoNoNoNovvvvvaaaaa e nomeie a nova barra de ferramentas. Cliquena guia ComandosComandosComandosComandosComandos e, na caixa CategCategCategCategCategorororororiasiasiasiasias, selecione MenMenMenMenMenus interus interus interus interus internosnosnosnosnos.

A partir da caixa ComandosComandosComandosComandosComandos, arraste o menu desejado para abarra de ferramentas exibida.

Depois de adicionar todos os botões e menus desejados, cliqueem FFFFFecececececharharharharhar.

2

3

4

Se houver necessidade de alterar o texto ou a figura do botão,basta pressionar o botão ModifModifModifModifModificar seleçãoicar seleçãoicar seleçãoicar seleçãoicar seleção.

Após arrastar todos os comandos desejados para a barra, cliqueem FFFFFecececececharharharharhar.

7

Criando uma barra de menus internosEm vez de criar uma barra de comandos com botões, você pode

construir uma barra de menus contendo diversos comandos. Maslembre-se: é aconselhável manter em uso apenas a barra de menuspadrão do Excel (conhecida como barra de menus da planilha). Dequalquer modo, se sentir necessidade de uma barra personalizada,basta seguir estes passos:

Vá ao menu FFFFFerererererrrrrramentasamentasamentasamentasamentas > PPPPPererererersonalizarsonalizarsonalizarsonalizarsonalizar, e clique na guia Bar- Bar- Bar- Bar- Bar-rrrrra de fa de fa de fa de fa de ferererererrrrrramentasamentasamentasamentasamentas.

8

1

1

2

3

• ANEXANDO UMA BARRA DE FERRAMENTASAO ARQUIVO

Abra a pasta de trabalho à qual deseja anexar uma barrade ferramentas.

Vá ao menu FFFFFerererererrrrrramentasamentasamentasamentasamentas > PPPPPererererersonalizarsonalizarsonalizarsonalizarsonalizar, e clique na guia Bar-Bar-Bar-Bar-Bar-rrrrras de fas de fas de fas de fas de ferererererrrrrramentasamentasamentasamentasamentas. Em seguida, clique em AneAneAneAneAnexarxarxarxarxar.

Clique na barra de ferramentas que deseja anexar ao arquivo eclique em CopiarCopiarCopiarCopiarCopiar.

Obs.:Obs.:Obs.:Obs.:Obs.: Certifique-se de salvar a pasta de trabalho depois de ane-xar uma barra de ferramentas.

cap_12.p65 16/6/2004, 17:3189

Page 91: 91729948 Visual Basic Vba Excel 01

90 Treinamento avançado em Excel

• UTILIZANDO CÓDIGOS PARA ATIVAR A BARRADE FERRAMENTAS

Agora vamos criar uma rotina que ative a barra de ferramentaspersonalizada. Esse código deve ser associado ao procedimentoOpenOpenOpenOpenOpen do objeto WWWWWorororororkbookkbookkbookkbookkbook, para que possa agir assim que o arqui-vo for aberto. Para isso, siga este passo-a-passo:

Na janela de P rP rP rP rP ro je too je too je too je too je to, dê um duplo clique emEstaPEstaPEstaPEstaPEstaPasta_de_Tasta_de_Tasta_de_Tasta_de_Tasta_de_Trrrrraaaaabalhobalhobalhobalhobalho.

Depois, clique sobre a caixa ObjetoObjetoObjetoObjetoObjeto (que deve estar mostrandoa opção (Ger(Ger(Ger(Ger(Geral)al)al)al)al)) e selecione a opção WWWWWorororororkbookkbookkbookkbookkbook.

1

2

1

2

• ANEXANDO MACROS A BOTÕES

Uma possibilidade interessante é a de acrescentar botões perso-nalizados à sua barra de tarefas. Você pode, por exemplo, anexaruma macro a um botão personalizado. Veja como fazer isso:

Com a sua barra de ferramentas personalizada aberta, vá até omenu FFFFFerererererrrrrramentas amentas amentas amentas amentas > PPPPPererererersonalizarsonalizarsonalizarsonalizarsonalizar.

Dê um clique na guia ComandosComandosComandosComandosComandos.

Na caixa CategCategCategCategCategorororororiasiasiasiasias, selecione MacrMacrMacrMacrMacrososososos.

Clique em PPPPPererererersonalizar botãosonalizar botãosonalizar botãosonalizar botãosonalizar botão e arraste-o para sua barra.

Para atribuir uma macro ao botão, pressione ModifModifModifModifModificar seleçãoicar seleçãoicar seleçãoicar seleçãoicar seleção.

Clique sobre o nome da macro desejada, e, depois, em AtrAtrAtrAtrAtribuiribuiribuiribuiribuir.

3

4

5

6

cap_12.p65 16/6/2004, 17:3190

Page 92: 91729948 Visual Basic Vba Excel 01

Personalizando o sistema 91

Digite as seguintes instruções:3

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 deformulário for aberto, exibindo uma mensagem ao usuário e tor-nando 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 FFFFFerererererrrrrramen-amen-amen-amen-amen-tastastastastas > PPPPPererererersonalizarsonalizarsonalizarsonalizarsonalizar, ou de linhas de código, desde que você saiba onome da barra em questão. Veja um exemplo de como desabilitaruma barra de ferramentas usando código, o qual deverá ser digitadono procedimento CloseCloseCloseCloseClose, ou seja, ao fechar a pasta de trabalho:

Vá até a janela de código. Se ela não estiver com o objetoWWWWWorororororkbookkbookkbookkbookkbook ativado, acesse-o na caixa ObjetoObjetoObjetoObjetoObjeto.

Logo acima da instrução End SubEnd SubEnd SubEnd SubEnd Sub, digite:

Application.CommandBars(“Standard”).Visible = _ False

Application.CommandBars(“Formatting”).Visible = _ False

‘Ocultar as barras de ferramentas Padrão e ‘Formatação.

1

2

cap_12.p65 16/6/2004, 17:3191

Page 93: 91729948 Visual Basic Vba Excel 01

92 Treinamento avançado em Excel

Para barras flutuantes, devemos estipular suas margens e sualargura a partir das bordas esquerda e superior da janela. Veja, aseguir, um exemplo de instrução para definir as características deuma barra flutuante:

.left = 500, .top = 200, .width = 100

Position = msoBarLeft

Position = msoBarRight

Position = msoBarTop

Position = msoBarFloating

Posiciona a barra à esquerda.

Posiciona a barra à direita.

Posiciona a barra no topo.

Torna a barra flutuante.

• ALTERANDO A POSIÇÃO DE UMA BARRADE FERRAMENTAS

Veja agora como mudar a posição de uma barra de ferramentas natela. No exemplo a seguir, vamos posicionar a barra personalizada CA-DASTRO DE VEÍCULOS na parte direita da área de trabalho do Excel:

Novamente no código do objeto WWWWWorororororkbookkbookkbookkbookkbook, logo acima da ins-trução End SubEnd SubEnd SubEnd SubEnd 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 ou-tros pontos da tela; basta alterar o valor do parâmetro Position, comomostra a tabela abaixo:

1

cap_12.p65 16/6/2004, 17:3192

Page 94: 91729948 Visual Basic Vba Excel 01

Personalizando o sistema 93

• DESABILITANDO UM ITEM DA BARRA

Assim como podemos desabilitar uma barra de ferramentas, tam-bém é possível desabilitar apenas um ou mais itens de uma barra.Suponha que, no caso do seu sistema de cadastro, por exemplo, ousuário ainda não tenha inserido dados no formulário. Nesse mo-mento, seria interessante desabilitar o botão de impressão, uma vezque ainda não existe nada para imprimir.

A seguir, veja um exemplo em que ocultamos o segundo botãoda barra personalizada CADASTRO DE VEÍCULOS:

Na janela de código, logo acima da instrução End SubEnd SubEnd SubEnd SubEnd Sub, digite:

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,basta associar ao objeto WWWWWorororororkbookkbookkbookkbookkbook, no procedimento OpenOpenOpenOpenOpen, o se-guinte 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.

1

cap_12.p65 16/6/2004, 17:3193

Page 95: 91729948 Visual Basic Vba Excel 01

94 Treinamento avançado em Excel

1

2

3

• FINALIZANDO SEU SISTEMA DE CADASTRO

Chegou a hora de aplicar os toques finais no seu sistema decadastro. Temos de resolver apenas duas coisas:

– Garantir que, assim que um cadastro seja finalizado, o cursorse 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 brancoAté aqui, quando o usuário clicava sobre o botão OKOKOKOKOK do formu-

lá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élulaem branco do sistema, faça o seguinte:

Na janela de código, localize o comando associado ao botão cmdOK.

Procure a seguinte linha de código (que está logo abaixo dainstrução Sub cmdOKSub cmdOKSub cmdOKSub cmdOKSub cmdOK):

Application.Goto reference:=”ini”

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.

cap_12.p65 16/6/2004, 17:3194

Page 96: 91729948 Visual Basic Vba Excel 01

Personalizando o sistema 95

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

1

2

Ativando o controle spiParcelasPara exibir o valor das parcelas toda vez que o usuário clicar

sobre o botão spiParcelas, siga estes passos:

Na janela de código, localize o controle spiParcelas.

Logo acima da instrução End SubEnd SubEnd SubEnd SubEnd Sub, digite este código:

Private Sub spiParcelas_Change() txtParcelas.Text = Me.spiParcelas.ValueEnd Sub

A obra completaParabéns! Você criou um sistema de cadastro de clientes funcio-

nal e associado a planilhas. Para que você possa ter uma visão geraldo seu programa, mostramos a seguir como deve ser o seu códigocompleto. Para facilitar a visualização, removemos as linhas de co-mentário. Confira:

Private Sub cmdOK_Click()

cap_12.p65 16/6/2004, 17:3195

Page 97: 91729948 Visual Basic Vba Excel 01

96 Treinamento avançado em Excel

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 apre-sentados neste livro, você já tem subsídios para criar muitas outrasaplicações em VBA – e para tornar-se um verdadeiro craque emExcel. Até a próxima!

cap_12.p65 16/6/2004, 17:3196

Page 98: 91729948 Visual Basic Vba Excel 01

capa_contra.p65 16/6/2004, 17:391