Sumário
Introdução
XXIII
Obtendo resultados com VBA
O que contém este livra
Ultrapassando a curva de aprendizado
O poder do VBA do Excel
O material técnico necessário para produzir aplicações para terceiros
Este livro ensinará como usar o Excel?
Uma breve história das planilhas e macros
O futuro do VBA e do Excel
Elementos especiais e convenções tipográficas
Versões abordadas
Arquivos de código
Próximas etapas
XXIII
XXV
XXV
XXV
XXV
XXVI
XXVI
XXVII
XXVIII
XXIX
XXIX
XXIX
l AS PRIMEIRAS ETAPAS DA C U R V A DE APRENDIZADO DO VBA
l
Liberando a força do Excel com o VBA!
A força do Excel
Barreiras para entrar
O gravador de macros não funciona!
O Visual Basic não é parecido com o BASIC
A boa notícia - É fácil ultrapassar a curva de aprendizado
A excelente notícia - O Excel com VBA recompensa o esforço
Conhecendo suas ferramentas-A barra de ferramentas do Visual Basic
Segurança de macro
Segurança muito alta
Segurança alta
Segurança média
Segurança baixa
Visão geral da gravação, do armazenamento e da execução de uma macro
Preenchendo a caixa de diálogo Gravar macro
Executando uma macro
Criando um botão de macro
Atribuindo uma macro a um controle de formulário
Entendendo o editor do Visual Basic (VBE)
Configurações do VBE
O Project Explorer
A janela Propriedades
3
3
3
3
4
4
4
5
6
7
7
7
7
7
8
8
8
9
10
10
11
12
Sumário
2
3
Examinando o código na janela de programação
Executar a macro em outro dia produz resultados indesejados
Uma solução possível: usar referências relativas quando gravar
Frustração
Próximas etapas: Aprender o VBA é a solução
Não consigo entender este código
14
16
16
20
20
21
Isso parece o BASIC, então, por que não me é familiar?
21
Entendendo as partes do "discurso" do VBA
O VBA é realmente difícil? Não!
Arquivos da Ajuda do VBA - Usando F1 para descobrir qualquer coisa
Usando os tópicos da Ajuda
Examinando o código da macro gravada -Usando o Editor e a Ajuda do VB
Parâmetros opcionais
Constantes definidas
Propriedades podem retornar objetos
Usando as ferramentas de depuração para descobrir o código gravado
Depurando o código
Mais opções de depuração - Pontos de interrupção
Movendo para a frente e para trás
Não parar em cada linha de código
Consultar algo enquanto executa o código por etapas
Usando uma inspeção para definir um ponto de interrupção
A referência básica a todos os objetos, métodos, propriedades
Cinco dicas fáceis para limpar o código gravado
Dica 1: Não selecione nada
Dica 2: Percorra o intervalo de baixo para cima para encontrar a última linha
Dica 3: Use variáveis para evitar codificar linhas e fórmulas
Dica 4: Aprenda a copiar e colar em uma única instrução
Dica 5: Use With... End With se você estiver executando múltiplas ações
para a mesma célula ou intervalo de células
Juntando tudo-corrigindo o código gravado
Próximas etapas
22
26
26
27
28
29
29
33
33
34
36
37
37
37
41
42
44
45
46
47
47
47
48
51
o objeto Range
53
Fazendo referência a intervalos
53
Usando os cantos superior esquerdo e inferior direito de uma seleção para
especificar um intervalo
Atalho para referenciar intervalos
Intervalos nomeados
Fazendo referência a intervalos em outras planilhas
Fazendo uma referência relativa a outro intervalo
Usando a propriedade Cells para selecionar um intervalo
Usando a propriedade Cells na propriedade Range
54
54
55
55
56
56
57
Sumário
XI
Usando a propriedade Offset para referenciar um intervalo
58
Usando a propriedade Résize para alterar o tamanho de um intervalo
58
Usando as propriedades Columns e Rows para especificar um intervalo
60
Usando o método Union para juntar vários intervalos
61
Usando o método Intersect para criar um novo intervalo a partir de intervalos
sobrepostos
61
Usando a função IsEmpty para verificar se uma célula está vazia
62
Usando a propriedade CurrentRegion para selecionar rapidamente um intervalo de dados.. 62
Usando a coleção Áreas para retornar um intervalo não-contíguo
66
Próximas etapas
67
Criando funções definidas pelo usuário
69
Funções definidas pelo usuário
Funções úteis e personalizadas do Excel
69
71
Definir o nome da pasta de trabalho atual em uma célula
71
Definir o nome da pasta de trabalho atual e o caminho do arquivo em uma célula
Verificar se uma pasta de trabalho está aberta
Verificar se existe uma planilha em uma pasta de trabalho aberta
Contar o número de pastas de trabalho em um diretório
Recuperar Userld
Recuperar a data e a hora da última gravação
Recuperar a data e a hora permanentes
Validar um endereço de e-mail
Somar células com base na cor interior
Recuperar o nome ou índice da cor interior da célula
Recuperar o índice de cor do texto
71
72
72
73
74
75
76
76
78
79
83
Contar valores únicos
83
Remover as duplicatas de um intervalo
Localizar a primeira célula com o comprimento
diferente de zero em um intervalo
Substituir vários caracteres
Recuperar números de um texto misto
Converter o número da semana em data
Separar string delimitada
84
86
87
89
89
90
Classificar e concatenar
91
Classificar caracteres alfanuméricos
Pesquisar uma string com texto
Reverter o conteúdo de uma célula
Máximo múltiplo
Retornar endereço de hiperlink
Retornar a letra da coluna de um endereço de célula
92
94
95
96
97
97
Aleatório estático
97
Usando Select Case em uma planilha
Próximas etapas
98
99
XII
5
Sumário
Loops e controle de fluxo
101
Loops For...Next
Usando variáveis na instrução For
101
104
Variações sobre o loop For.. .Next
104
Saindo de um loop logo após uma condição ser atendida
Aninhando um loop dentro de outro loop
105
106
Loops Do
Usando a cláusula W h i l e ou Until nos loops Do
Loops W h i l e . . . Wend
O loop do VBA: For Each
112
Variáveis de objeto
112
Controle de fluxo: Usando If.. .Then.. .Else e Select Case
Controle de fluxo básico: I f... Then... El se
Condições
If...Then...End If
Decisões Ou/Ou: If.. .Then... El se... End If
Usando I f. ..El se I f... End I f para múltiplas condições
Usando Select Case... End Select para múltiplas condições
Expressões complexas nas instruções Case
Aninhando as instruções If
Próximas etapas
6
107
110
112
115
115
116
116
116
117
117
118
118
121
Fórmulas estilo L1C1
123
Fazendo referência a células: Referências Al versus L1C1
Por que se preocupar
com o estilo L1C1?
Não apenas uma preocupação
Mudando o Excel para exibir as referências no estilo L1C1
O milagre das fórmulas do Excel
Entrar uma fórmula uma vez e copiá-la mil vezes
O segredo - Não é surpreendente
Explicação de estilo de referência L1C1
Usando L1C1 com referências relativas
Usando L1C1 com referências absolutas
Usando L1C1 com referências mistas
Fazendo referência a colunas e linhas inteiras com o estilo L1C1
Substituindo muitas fórmulas Al por uma única fórmula L1C1
Lembrando que os números da coluna
são associados às letras da coluna
Formatação condicional - L1C1 requerida
Configurando uma formatação condicional na interface do usuário
Configurando formatos condicionais no VBA
As fórmulas de matriz exigem formatação condicional
Próximas etapas
123
123
124
124
125
125
126
127
127
128
128
129
129
131
132
132
132
135
.. 135
Sumário
XIII
Nomes
137
Nomes globais versus nomes locais
Adicionando nomes
Excluindo nomes
Tipos de nomes
Fórmulas
Strings
Números
Usando matrizes em nomes
Nomes reservados
Ocultando nomes
Verificando a existência de um nome
Próximas etapas
Níveis de eventos
137
139
140
140
140
141
142
143
143
144
145
147
149
Programação de eventos
149
Usando eventos
Parâmetros do evento
Ativando eventos
Eventos da pasta de trabalho
Eventos de planilha
Eventos de planilha de gráfico
Gráficos embutidos
Eventos no nível da aplicação
Próximas etapas
Métodos de interação com o usuário
150
150
150
151
157
161
161
165
169
171
UserForms - Uma introdução
171
Criando um Userform
Chamando e ocultando um Userform
Programando o UserForm
Usando controles de formulário básico
Decidindo usar caixas de listagem (listBoxes) ou caixas
de combinação (ComboBoxes) nos seus formulários
Adicionando botões de opção a um UserForm
Usando o controle multipáginas para combinar os formulários
Próximas etapas
172
173
174
175
176
178
181
182
XIV
Sumário
II AUTOMATIZANDO A FORÇA DO EXCEL NO VBA
10
Gráficos
185
Visão geral
Gráficos embutidos versus folhas de gráfico
Os gráficos em uma planilha de gráfico não têm contêiner
Criando um gráfico com o VBA
O tipo de gráfico padrão
Usando variáveis de objeto para simplificar o código
A anatomia de um gráfico
A área do gráfico-Nome VBA: ChartArea
A área de plotagem - Nome VBA: PlotArea
As seqüências de dados-Nome VBA: Series
Tabela de tipos de gráfico
Detalhes de vários tipos de gráficos
Configurações para gráficos 3D
Gráficos interativos
Usando eventos com gráficos
Exportando gráficos como imagens
Desenhando com gráficos X-Y
Gráficos personalizados com o VBA
Próximas etapas
O Filtro avançado é mais fácil no VBA do que no Excel
185
186
188
188
191
192
193
193
197
198
205
208
208
211
212
213
213
214
216
217
11 Pesquisando dados com o Filtro avançado
Usando o Filtro avançado para extrair uma lista de valores exclusivos
Extraindo uma lista de valores exclusivos com a interface com o usuário
Extraindo uma lista de valores exclusivos com o código VBA
Obtendo combinações únicas de dois ou mais campos
Usando o Filtro avançado com intervalos de critérios
Unindo múltiplos critérios com um OU lógico
Unindo dois critérios com um E lógico
Outros intervalos de critérios ligeiramente complexos
Os critérios mais complexos - Substituindo a lista de valores por uma condição
criada como o resultado de uma fórmula
Estando preparado para nenhum registro após o filtro
Usando "Filtrar a lista no local" no Filtro avançado
Nenhum registro correspondente ao usar Filtrar a lista no local
Mostrando todos os registros após Filtrar a lista no local
Usando Filtrar a lista no local com Somente registros exclusivos
O verdadeiro "burro de carga": xl Fi l terCopy com todos os registros em vez
de somente registros exclusivos
Copiando todas as colunas
Copiando um subconjunto de colunas e reordenando
AutoFiltros
Próximas etapas
217
218
218
220
225
226
227
228
228
229
235
236
237
237
237
237
238
239
245
246
Sumário
12 Tabelas dinâmicas
247
Versões
247
Criando uma tabela dinâmica genérica na interface do Excel
Construindo uma tabela dinâmica no VBA do Excel
Obtendo uma soma em vez de uma contagem
Não é possível mover ou alterar parte de um relatório dinâmico
Calculando o tamanho de uma tabela dinâmica concluída
Receita por cliente para um gerente de linha de produto
Eliminando células em branco na área de dados
Usando a AutoClassificação para controlar a ordem de classificação
Controlando a ordem de classificação manualmente
Alterando o formato numérico padrão
Suprimindo os subtotais para vários campos de linha
Suprimindo os totais gerais das linhas
Manipulando as dificuldades adicionais
Criando uma nova pasta de trabalho para armazenar o relatório
Movendo o resumo para uma planilha de relatório em branco
Preenchendo a visão de estrutura
Formatação final
Adicionando subtotais
Juntando tudo
Questões de lucratividade do produto com dois ou mais campos de dados
Definindo campos de dados calculados
Evitar itens calculados
Resumindo os campos de dados com agrupamentos
Agrupamento por semana
Medindo o tempo de espera do pedido pelo agrupamento de dois campos de data
Técnicas avançadas de tabela dinâmica
Usando o recurso AutoApresentação dos 10 primeiros para produzir visões executivas
Usando ShowDetai l da tabela dinâmica para filtrar um conjunto de dados
Usando um campo de página para criar relatórios para cada região ou produto
Filtrando manualmente para dois ou mais itens em um PivotField
Soma, Média, Contagem, Valor mínimo, Valor máximo e muito mais
Relatórios de percentagens
Próximas etapas
Usando o VBA para estender o Excel
Formatação condicional com mais de três condições
248
250
252
253
253
256
258
259
259
260
261
262
262
262
263
264
266
266
267
271
273
276
278
281
283
285
285
289
291
296
300
302
306
307
307
13 A força do Excel
O AutoFiltro com mais de duas condições
Operações de arquivos
Listar arquivos em um diretório
Excluir uma pasta de trabalho após uma data específica
Fechar e excluir..
307
308
309
310
311
.. 312
XVI
Sumário
Importar CSV
Ler todo o CSV para a memória e analisar
Combinando e separando as pastas de trabalho
Separar planilhas em pastas de trabalho
Combinar pastas de trabalho
Filtrar e copiar dados em planilhas separadas
Exportar os dados para o Word
Trabalhando com comentários de célula
Listar os comentários
Redimensionar os comentários
Redimensionar os comentários com centralização
Colocar um gráfico em um comentário
Utilitários para empolgar seus clientes
Usando formatação condicional para realçar a célula selecionada
Realçar a célula selecionada sem usar formatação condicional
Personalizar a transposição de dados
Selecionar/desfazer a seleção de células não-contíguas
Técnicas para profissionais em VBA
Configuração rápida de página
Calculando o tempo para executar o código
Desativar os itens recortar, copiar e colar
Personalizar a ordem de classificação
Indicador do progresso da célula
Caixa Senha protegida
Alterartipos de caixas minúsculas/maiúsculas
Evento Delete personalizado
Selecionando com SpecialCells
Excluir linhas com condições
Ocultar a barra de fórmula
Aplicações arrojadas
Cotações de fundos e ações históricas
Usando a extensibilidade do VBA para adicionar código às novas planilhas
Próximas etapas
Obtendo dados da Web
14 Lendo e escrevendo na Web
Criando manualmente uma consulta Web e atualizando com VBA
Usando o VBA para atualizar uma consulta Web existente
Construindo uma nova consulta Web com VBA
Usando fluxo de dados
Usando Application.OnTime para analisar os dados periodicamente
Procedimentos programados exigem o modo Pronto
Especificando uma janela de tempo para uma atualização
Cancelando uma macro agendada anteriormente
O encerramento do Excel cancelará todas as macros programadas pendentes
314
315
316
316
317
318
320
321
321
322
323
324
326
326
327
328
330
332
332
334
336
338
339
340
342
344
345
345
347
347
347
349
350
351
351
352
353
354
357
357
358
358
359
359
Sumário
Programando uma macro para executar em x minutos no futuro
Programando uma macro para executar a cada dois minutos
Publicando dados em uma página Web
Usando VBA para criar páginas Web personalizadas
Usando o Excel como um sistema de gerenciamento de conteúdo
Bônus - FTP a partir do Excel
Próximas etapas
15 XML no Excel 2003 Profissional
359
360
362
364
365
368
369
371
O que é XML?
Regras simples do XML
Formato de arquivo universal
XML como o novo formato de arquivo universal
A sopa de letras do XML
Usando o XML para realizar round-trip de uma pasta de trabalho do Excel
para o HTML e retornar
Próximas etapas
16
XVII
Automatizando o Word .
371
372
372
373
374
375
380
. . 381
Vinculação anterior
Erro de compilação: Impossível encontrar objeto ou biblioteca
Vinculação posterior
Criando e referenciando objetos
Palavra-chave New
Função CreateObject
Função GetObject
381
383
383
384
385
385
385
Objetos do Word
386
Objeto Document
Objeto Range
387
390
Indicadores
Próximas etapas
393
398
III MATERIAL TÉCNICO DE QUE VOCÊ PRECISARÁ PARA P R O D U Z I R
APLICAÇÕES PARA O ADMINISTRADOR EXECUTAR
17
Matrizes
,
Declarar uma matriz
Matrizes multidimensionais
Preencher uma matriz
Esvaziar uma matriz
As matrizes podem facilitar a manipulação de dados, mas isso é tudo?
Matrizes dinâmicas
Passando uma matriz
Próximas etapas
401
401
402
402
404
405
406
408
408
XVIII
18
19
Sumário
Processamento de arquivo de texto
409
Importando arquivos de texto
Importando arquivos de texto com menos de 65.536 linhas
Lendo arquivos de texto com mais de 65.536 linhas
Escrevendo arquivos de texto
Próximas etapas
409
409
415
420
421
Usando o Access como um back end para melhorar
o acesso multiusuário aos dados
423
ADO versus DAO
As ferramentas do ADO
Adicionando um registro ao banco de dados
Recuperando registros do banco de dados
Atualizando um registro existente
Excluindo registros via ADO
Resumindo os registros via ADO
Outros utilitários via ADO
Verificando a existência de tabelas
Verificando a existência de um campo
Adicionando uma tabela
Adicionando um campo
Próximas etapas
424
426
426
428
430
432
433
434
434
435
436
437
437
20 Criando classes, registros e coleções
21
439
Inserindo um módulo de classe
Capturando eventos de aplicação e de gráfico embutido
Eventos de aplicação
Eventos de gráfico embutido
Criando um objeto personalizado
Usando um objeto personalizado
Usando Property LeteProperty Get para controlar o modo como
os usuários utilizam os objetos personalizados
439
440
440
442
443
445
Coleções
448
Criando uma coleção em um módulo padrão
Criando uma coleção em um módulo de classe
Tipos definidos pelo usuário (UDTs, user defined types)
Próximas etapas
448
449
454
457
Técnicas avançadas de UserForm
459
Usando a barra de ferramentas UserForm no projeto dos controles nos UserForms
Controles e coleções
Mais controles do UserForm
Botões de ativação
459
460
462
462
446
Sumário
22
XIX
Faixa de tabulação
RefEdit
Userforms sem modo
Hyperlinks nos userforms
Adicionando controles em tempo de execução
Redimensionando o userform dinamicamente
Adicionando um controle dinamicamente
Dimensionando dinamicamente
Sem a funcionalidade AutoCompletar
Adicionando outros controles
Adicionando uma imagem dinamicamente
Juntando tudo
Usando uma barra de rolagem como uma barra deslizante para selecionar valores
Adicionando dicas de ajuda aos controles
Teclas aceleradoras
Texto de dica do controle
462
464
464
465
466
468
468
468
468
469
469
470
472
474
474
474
Ordem de tabulação
Colorindo o Active Control
Formulários transparentes
Próximas etapas
474
475
478
479
Application Programming Interface (API) do Windows
481
O que é a API do Windows?
Entendendo uma declaração de API
Usando uma declaração de API
Exemplos de API
Recuperar informações de resolução de exibição
Desativar o X do Excel para fechar a aplicação
Desativar o X para fechar um userform
Cronômetro de execução
Links clicáveis nos formulários
Executando sons
Recuperando um caminho de arquivo
Encontrando mais declarações de API
Próximas etapas
481
481
482
483
485
486
488
488
489
490
491
495
495
23 Tratamento de erros
O que acontece quando ocorre um erro
O erro de depuração dentro do código do userform pode ser um engano
Tratamento de erro básico com a sintaxe On Error Go To
Múltiplos manipuladores de erro
Manipuladores de erro genéricos
Tratando os erros e decidindo ignorá-los
Suprimindo os avisos do Excel..
497
497
498
500
502
502
502
.. 504
XX
24
25
Sumário
Treinando seus clientes
Erros durante o desenvolvimento versus erros posteriores
Erro.de tempo de execução 9: Subscrito fora do intervalo
Erro de tempo de execução 1004: Método 'Range' do objeto ' G l o b a l ' falhou
As doenças da proteção de código
Mais problemas com senhas
Erros causados por versões diferentes
Próximas etapas
505
505
505
506
507
508
509
509
Usando menus personalizados para executar macros
511
Criando um menu personalizado
Excluindo e criando um menu personalizado
Adicionando um único item de menu
Dividindo os itens em grupos
Adicionando um menu fly-out
Criando uma barra de ferramentas personalizada
Excluindo e criando a barra de ferramentas
Adicionando os botões à barra de ferramentas
Usando os códigos FacelD para adicionar ícones à barra de ferramentas
Adicionando controles suspensos à barra de ferramentas
Lembrando a posição de uma barra de ferramentas
Outras maneiras de executar uma macro
Atalho do teclado
Anexar um menu a um botão de comando
Anexar uma macro a um controle ActiveX
Próximas etapas
511
512
513
514
515
517
518
519
520
520
521
522
522
523
525
527
Suplementos
529
Características dos suplementos padrão
Convertendo uma pasta de trabalho do Excel em suplemento
Usando Salvar como para converter um arquivo em um suplemento
Usando o Editor do Visual Basic para converter um arquivo em um suplemento
Fazendo o cliente instalar o suplemento
Suplementos padrão não são seguros
Fechando os suplementos
Removendo suplementos
Usando uma pasta de trabalho oculta como uma alternativa para um suplemento
Próximas etapas
529
530
531
531
532
532
533
534
534
536
26 Estudo de caso: Projetando uma aplicação no Excel
Sobre Tushar Mehta
Usando o Excel para fazer algo mais do que calcular
A solução
537
537
538
538
Sumário
Implementando a solução no Excel e no VBA
Passo l - Conceitos de cima para baixo
Passo l - Componentes principais definidos
Passo 2 - Código de cima para baixo
Passo 2 - Componentes-chave
Passo'3-Código de cima para baixo completo
Passo 3 - Componentes-chave completos
Resumo
Próximas etapas
índice
XXI
539
540
541
542
544
545
548
548
549
. 550