Autor: Fernando de Carvalho Navarro E-mail: [email protected] Apresentação criada por...
-
Upload
samuel-alves-sampaio -
Category
Documents
-
view
221 -
download
5
Transcript of Autor: Fernando de Carvalho Navarro E-mail: [email protected] Apresentação criada por...
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
Apresentação criada por Fernando de Carvalho [email protected]
' Por Carlos Eduardo Guimarães' [email protected] CreateFileList(FileFilter As String, _ IncludeSubFolder As Boolean) As Variant ' returns the full filename for files matching ' the filter criteria in the current folder Dim FileList() As String, FileCount As Long CreateFileList = "" Erase FileList If FileFilter = "" Then FileFilter = "*.*" ' all files With Application.FileSearch .NewSearch .LookIn = CurDir .Filename = FileFilter .SearchSubFolders = IncludeSubFolder .FileType = msoFileTypeAllFiles If .Execute(SortBy:=msoSortByFileName, _ SortOrder:=msoSortOrderAscending) = 0 Then Exit Function Redim FileList(.FoundFiles.Count) For FileCount = 1 To .FoundFiles.Count FileList(FileCount) = .FoundFiles(FileCount) Next FileCount .FileType = msoFileTypeExcelWorkbooks ' reset filetypes End With CreateFileList = FileList Erase FileList End Function Sub TestCreateFileList() Dim FileNamesList As Variant, i As Integer 'ChDir "C:\My Documents" ' activate the desired startfolder for the filesearch FileNamesList = CreateFileList("*.*", False) ' performs the filesearch, includes any subfolders ' present the result Range("A:A").ClearContents For i = 1 To UBound(FileNamesList) Cells(i + 1, 1).Formula = FileNamesList(i) Next i End Sub
Microsoft ExcelVBA – Visual Basic para Aplicativos
Dezembro/2013Excel 2007
VBA 6.3Excel 2010
VBA 7.0Excel 2013
VBA 7.1
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
2
FunçõesFunções do VBA • Funções de Planilha • Funções Definidas pelo Usuário
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
3
FunçõesRecebem 0 ou mais argumentos (ou parâmetros) e retornam
um único valorFunções do Visual Basic
Funções matemáticas, trigonométricas e logarítmicas e suas derivadas; Funções de data e hora; Funções de manipulação de texto (string); Funções de conversão de tipos; Outras.
Funções de planilha do Excel Disponíveis também dentro do código em VBA.
Funções definidas pelo usuário Criadas em procedimentos Function.
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
4
Funções de PlanilhaSintaxe:
Application.WorksheetFunction.Função(Argumentos…)
“Application” pode ser omitido da sintaxe Os nomes de função precisam estar em inglês
Exemplos: Média = WorksheetFunction.Average(10.3, 14.1, 9.7)
Pag = WorksheetFunction.Pmt(0.0099, 36, 25000, 0, 0)
Nome = WorksheetFunction.Proper(Nome) MsgBox WorksheetFunction.Trim(Código_Produto) N = WorksheetFunction.CountBlank(Range("A5:H5"))
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
5
Funções Definidas pelo UsuárioCriadas diretamente no código VBAAceitam 0 ou mais argumentosPodem ter argumentos opcionaisRetornam um único valorPodem ser usadas dentro das planilhasPodem ser salvas como Suplementos (Add-ins)Sintaxe:
Function Nome(Arg1, Arg2, ...) Cálculos usando os argumentos ... Nome = resultadoEnd Function
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
6
Exemplos de Funções Definidas pelo UsuárioConversão para graus Fahrenheit (°F = 9 × °C / 5 + 32):
Function GrausF(GrausC) GrausF = 9 * GrausC / 5 + 32End Function
Variação Percentual (Δ% = valor final / valor inicial – 1):Function VARPER(v1 As Double, v2 As Double) As Double VARPER = v2 / v1 - 1End Function
Volume do cilindro (Vol = π × r2 × h):Function VCil(Diâmetro As Double, Altura As Double) Dim Raio As Double Raio = Diâmetro / 2 VCil = WorksheetFunction.Pi * Raio ^ 2 * AlturaEnd Function
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
7
Funções com Argumento Opcional ou Valor PadrãoNa declaração, preceder o nome da variável com Optional.O tipo de variável opcional dever ser Variant, caso
contrário a variável receberá valores padronizados para cada tipo.
Para definir um valor padrão a ser adotado na falta do argumento, usar o sinal de igual (=) e o valor na mesma linha da declaração.
A função IsMissing pode ser usada para verificar se o argumento está faltando (True) ou não (False).
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
8
Exemplo de Função com Argumento OpcionalFunção que calcula a área de um retângulo, onde são fornecidos
o comprimento e a largura. Se a largura for omitida, deve-se calcular a área de um quadrado, usando apenas o comprimento.
Function Ret(Comprimento As Double, _ Optional Largura As Variant) As Double
If IsMissing(Largura) Then Ret = Comprimento * Comprimento Else Ret = Comprimento * Largura End If
End Function
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
9
Exemplo de Função com Valor PadrãoFunção que calcula um valor final com desconto. São
fornecidos dois argumentos: o Valor e a Taxa que se deseja aplicar ao valor para calcular o desconto.
Function Desconto(Valor As Double, _ Optional Taxa = 0.1) As Double Desconto = Valor - Valor * TaxaEnd Function
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
10
Ajuda no Assistente de Função (VBA 7.0+)Oferece descrições para a função e argumentos quando a
função é iniciada pelo Assistente de Função.
Sintaxe:Application.MacroOptions _ Macro:="nome_da_macro", _ Description:="descrição_da_macro", _ Category:="nome_da_categoria", _ ArgumentDescriptions:=Array("descr1", "descr2",...)
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
11
Ajuda no Assistente de Função (Todas as versões) Versões do VBA anteriores à 7.0 não possuem o argumento
ArgumentDescriptions. Sintaxe (generalização com diretivas):
#If VBA7 Then Application.MacroOptions _ Macro:="nome_da_macro", _ Description:="descrição_da_macro", _ Category:="nome_da_categoria", _ ArgumentDescriptions:=Array("descr1", "descr2",...)#Else Application.MacroOptions _ Macro:="nome_da_macro", _ Description:="descrição_da_macro", _ Category:="nome_da_categoria"#End If
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
12
Programação de EventosEventos Associados a Objetos • Eventos Não Associados a Objetos
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
13
Eventos Associados a Objetos• Um Evento acontece quando o
usuário interage com um objeto: clicar, ativar, arrastar, selecionar, teclar, sair, abrir, fechar, imprimir, calcular, etc.
• Um Procedimento de Evento são linhas de código que são executadas quando o evento é disparado.
• Para escrever as linhas de código de evento associadas a um objeto, dê um duplo clique, ou use o botão direito e o comando Exibir Código.
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
14
Procedimentos de EventosNa janela de módulo é possível escolher o objeto e os eventos
associados.Sintaxe:
Private Sub Objeto_Evento(argumentos) instruções...End Sub
Seletor de objetos Seletor de eventos
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
15
Exemplo de Evento com Pastas de TrabalhoCódigo executado sempre que a pasta de trabalho é aberta:
Private Sub Workbook_Open() Select Case Hour(Now) Case Is < 12: MsgBox "Bom dia!" Case Is < 19: MsgBox "Boa tarde!" Case Else: MsgBox "Boa noite!" End SelectEnd Sub
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
16
Exemplo de Evento com Pastas de TrabalhoCódigo executado imediatamente antes da pasta de trabalho
ser impressa:
Private Sub Workbook_BeforePrint(Cancel As Boolean) Dim M As String M = "Verifique se há papel A3 na impressora" & _ vbCr & Application.ActivePrinter Beep If MsgBox(M, vbQuestion + vbOKCancel) = vbCancel Then Cancel = True End IfEnd Sub
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
17
Exemplos de Evento com Pastas e PlanilhasO código a seguir sempre posiciona a célula ativa em A1
quando qualquer planilha da pasta de trabalho for ativada:Private Sub Workbook_SheetActivate(ByVal Sh As Object) Range("A1").SelectEnd Sub
O código abaixo se aplica somente à Plan1 e é acionado cada vez que um intervalo é selecionado:Private Sub Worksheet_SelectionChange(ByVal _ Target As Range) Target.Interior.ColorIndex = Int(Rnd * 56) + 1End Sub
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
18
Método OnKeyEvento não associado a um objeto.Disparado pela ação do usuário via teclado.Prepara uma sequência de teclas que executará um
procedimento, caso pressionada.Sintaxe para ativação da sequência:
Application.OnKey teclas, procedimento• teclas: uma sequência de códigos de teclas• procedimento: nome da macro que será executada
Sintaxe para desativação da sequência: Application.OnKey teclas, ""
Sintaxe para restauração das funções originais das teclas: Application.OnKey teclas
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
19
Método OnKey – Códigos de TeclasTecla Código
Backspace {BACKSPACE}, {BS}
Break {BREAK}
Caps Lock {CAPSLOCK}
Clear {CLEAR}
Delete, Del {DELETE}, {DEL}
End {END}
Enter ~ (til)
Enter (teclado núm.) {ENTER}
Esc {ESCAPE}, {ESC}
F1 a F15 {F1} a {F15}
Help {HELP}
Home {HOME}
Tecla Código
Insert, Ins {INSERT}
Num Lock {NUMLOCK}
Page Down {PGDN}
Page Up {PGUP}
Return {RETURN}
Scroll Lock {SCROLLLOCK}
Tab {TAB}
{LEFT}
{RIGHT}
{UP}
{DOWN}
Teclas de Prefixo:Shift (+), Ctrl (^), Alt (%)
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
20
Método OnTimeEvento não associado a um objeto.Agenda uma data e hora para execução de uma macro.Sintaxe para fazer o agendamento da execução:
Application.OnTime horário, procedimento
Sintaxe para cancelar o agendamento da execução: Application.OnTime horário, procedimento, , False
Exemplos: Executa a macro “Backup” às 23:00 horas:• Application.OnTime TimeValue("23:00"), "Backup"
Executa o procedimento “Atualizar”, daqui a 5 minutos:• Application.OnTime Now + TimeValue("0:05"), "Atualizar"
Cancela o agendamento da macro “Backup”:• Application.OnTime TimeValue("23:00"), "Backup", , False
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
21
Tratamento de ErrosErros de Sintaxe • Erros de Compilação • Erros na Execução
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
22
Tipos de ErrosSintaxe: identificados imediatamente durante a digitação do
código.Compilação: identificados quando a macro é executada pela
primeira vez.Execução: ocorrem quando a macro está executando.Lógica: mais difíceis, requerem detalhada análise do código.
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
23
Tratamento Durante a ExecuçãoSintaxe para desviar para um rótulo de linha:
On Error GoTo rótulo_de_linha
Sintaxe para ignorar os erros: On Error Resume Next
Sintaxe para retomar o código após tratamento de erros: Resume
Sintaxe para terminar o tratamento de erros: OnError GoTo 0
Obtenção do código de erro (objeto Err): Err.Number
Obtenção da descrição do erro (objeto Err): Err.Description
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
24
Tratamento Durante a ExecuçãoExemplo 1 – Código sem tratamento:
Sub Calcular_Idade() Dim DataNasc As Date DataNasc = InputBox("Data de nascimento") MsgBox "Sua idade é " & _ DateDiff("yyyy", DataNasc, Date) & " anos."End Sub
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
25
Tratamento Durante a ExecuçãoExemplo 1 – Código com tratamento:
Sub Calcular_Idade() Dim DataNasc As Date On Error GoTo Tratar_Erro DataNasc = InputBox("Data de nascimento") MsgBox "Sua idade é " & _ DateDiff("yyyy", DataNasc, Date) & " anos." Exit SubTratar_Erro: MsgBox "Valor inválido."End Sub
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
26
Tratamento Durante a ExecuçãoExemplo 2 – Código sem tratamento:
Sub Limpar_Planilhas() Dim Plan As Worksheet For Each Plan In ActiveWorkbook.Sheets Plan.Cells.ClearContents Next PlanEnd Sub
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
27
Tratamento Durante a ExecuçãoExemplo 2 – Código que ignora os erros:
Sub Limpar_Planilhas() Dim Plan As Worksheet On Error Resume Next ' Ignora folhas de gráfico For Each Plan In ActiveWorkbook.Sheets Plan.Cells.ClearContents Next PlanEnd Sub
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
28
Tratamento Durante a ExecuçãoExemplo 3 – Código sem tratamento:
Sub Verificar_Estoque() Dim valor As Byte valor = InputBox("Digite a quantidade:") Select Case valor Case Is > 150: MsgBox "Estoque EXCELENTE!" Case Is > 75: MsgBox "Estoque BOM!" Case Else: MsgBox "Comprar produto!" End SelectEnd Sub
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
29
Tratamento Durante a Execução Exemplo 3 – Código com tratamento:
Sub Verificar_Estoque2() Dim valor As Byte On Error GoTo Tratar valor = InputBox("Digite a quantidade:") Select Case valor Case Is > 150: MsgBox "Estoque EXCELENTE!" Case Is > 75: MsgBox "Estoque BOM!" Case Else: MsgBox "Comprar produto!" End Select Exit SubTratar: Select Case Err.Number Case 6: MsgBox "Digite só inteiros de 0 a 255." Case 13: MsgBox "Digite apenas números." End SelectEnd Sub
Autor: Fernando de Carvalho NavarroE-mail: [email protected]
30
Depuração de Erros
• Pontos de Interrupção Ativar/Desativar: F9 Desativar todos: Ctrl+Shift+F9 Instrução Stop Continuação da execução: F5 Execução passo-a-passo: F8 Pular ou retroceder instruções Editar instruções
• Janela de Verificação Imediata Obter valores das variáveis e
programa suspenso Digitar instruções do VBA Permite saídas de valores pela
instrução Debug.Print• Janela Inspeções de
Variáveis Rastreia variáveis e expressões
durante a depuração Para incluir uma variável ou
expressão, basta arrastar e soltar na janela