Autor: Fernando de Carvalho Navarro E-mail: [email protected] Apresentação criada por Fernando de Carvalho Navarro [email protected]

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


2

FunçõesFunções do VBA • Funções de Planilha • Funções Definidas pelo Usuário


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.


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"))


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


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


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).


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


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


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",...)


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


12

Programação de EventosEventos Associados a Objetos • Eventos Não Associados a Objetos


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.


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


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


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


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


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


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 (%)


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


21

Tratamento de ErrosErros de Sintaxe • Erros de Compilação • Erros na Execução


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.


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


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


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


26


Sub Limpar_Planilhas() Dim Plan As Worksheet For Each Plan In ActiveWorkbook.Sheets Plan.Cells.ClearContents Next PlanEnd Sub


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


28


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


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


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