VBA Excel (Visual Basic for Excel), Traduções de Programação em Windows

Baixe VBA Excel (Visual Basic for Excel) e outras Traduções em PDF para Programação em Windows, somente na Docsity!

SERVIÇO NACIONAL DE APRENDIZAGEM COMERCIAL

SENAC - RIO GRANDE DO SUL

PROGRAMAÇÃO

VBA PARA EXCEL

Porto Alegre, 2010.

INTRODUÇÃO

Esta apostila foi desenvolvida pelo SENAC/RS, para apoio ao aluno durante o desenvolvimento do curso de Programação VBA para Excel.

A disciplina introduz o estudo da Linguagem de Programação, destacando a construção de Macros e Funções do Usuário. Abrange os conceitos fundamentais para criação de macros e funções interagindo com o Excel e outros aplicativos Microsoft.

Este material é composto de bases teóricas que abrangem o conteúdo programático do curso com exemplos da aplicação.

O conteúdo programático deste curso foi projetado com o propósito de, ao final do curso, tornar o aluno apto a desenvolver macros e funções aplicadas para o Excel, com objetivo de otimizar suas tarefas na realização dos seus trabalhos.

• INTRODUÇÃO.............................................................................................................................................
• VBA PARA EXCEL
  • MACROS.................................................................................................................................................
    • ROTINAS PÚBLICAS E PRIVADAS
  • PASSAGEM DE PARÂMETROS
    • PASSAGEM DE PARÂMETROS POR VALOR – BYVAL.....................................................................
    • PASSAGEM DE PARÂMETROS POR REFERENCIA – BYREF
    • CRIAR MACROS.................................................................................................................................
  • PROGRAMAÇÃO ORIENTADA A OBJETOS
    • OBJETO............................................................................................................................................
    • PROPRIEDADES
    • MÉTODOS
    • CLASSES E INSTÂNCIAS
    • EVENTOS
  • PROCEDIMENTOS FUNCTION X PROCEDIMENTOS SUB
    • DEFINIÇÃO DE FUNCTION
    • DEFINIÇÃO DE ROTINAS/SUBROTINAS (MACROS)
  • INPUTBOX E MSGBOX.........................................................................................................................
    • FUNÇÃO INPUTBOX
    • FUNÇÃO MSGBOX...........................................................................................................................
    • VARIÁVEL IMPLÍCITA ME
  • OBJETO RANGE...................................................................................................................................
    • PROPRIEDADE NAME
    • PROPRIEDADE VALUE
    • OBJETO RANGE X MÉTODO SELECT
    • PROPRIEDADE CELLS
    • PROPRIEDADE ACTIVECELL
    • OBJETO FONT
    • OBJETO INTERIOR
    • PROPRIEDADE HORIZONTALALIGNMENT E VERTICALALIGNMENT
    • PROPRIEDADES COLOR E COLORINDEX
    • COLEÇÃO BORDERS
    • PROPRIEDADE LINESTYLE E WEIGHT
    • PROPRIEDADE COUNT
    • PROPRIEDADE COLUMN E ROW....................................................................................................
    • PROPRIEDADE COLUMNS E ROWS
    • PROPRIEDADE RESIZE
    • PROPRIEDADE ADDRESS...............................................................................................................
  • PROPRIEDADE OFFSET..................................................................................................................
  • FUNÇÃO ISEMPTY
  • MÉTODO ACTIVATE
  • MÉTODO COPY................................................................................................................................
  • MÉTODO DELETE
  • MÉTODO MERGE.............................................................................................................................
  • MÉTODO CLEAR
  • MÉTODO SORT................................................................................................................................
• OBJETO WORKSHEET.........................................................................................................................
  • PROPRIEDADE CODENAME
  • PROPRIEDADE NAME
  • PROPRIEDADE VISIBLE
  • PROPRIEDADE ACTIVESHEET
  • MÉTODO ADD
  • MÉTODO ACTIVATE
  • MÉTODO COPY................................................................................................................................
  • MÉTODO DELETE
  • MÉTODO MOVE
  • MÉTODO SELECT
  • EVENTO DE UM WORKSHEET
  • EVENTO ACTIVATE
  • EVENTO DEACTIVATE.....................................................................................................................
  • EVENTO BEFOREDOUBLECLICK....................................................................................................
  • EVENTO BEFORERIGHTCLICK
  • EVENTO CALCULATE
  • EVENTO CHANGE
  • EVENTO SELECTIONCHANGE
• OBJETO WORKBOOK
  • PROPRIEDADE FULLNAME
  • PROPRIEDADE PATH
  • MÉTODO ADD
  • MÉTODO ACTIVATE
  • MÉTODO CLOSE..............................................................................................................................
  • MÉTODO SAVE
  • EVENTOS DE UM WORKBOOK
  • EVENTO ACTIVATE
  • EVENTO OPEN
  • EVENTO NEWSHEET.......................................................................................................................
  • EVENTO SHEETCHANGE
• OBJETO APPLICATION
  • PROPRIEDADE THISWORKBOOK...................................................................................................
    • PROPRIEDADE DISPLAYALERTS
    • PROPRIEDADE SCREENUPDATING
    • MÉTODO QUIT
    • MÉTODO ONKEY
  • VARIÁVEIS DO TIPO OBJETO
  • SUPLEMENTOS....................................................................................................................................
    • CONVERTENDO UMA PASTA DE TRABALHO EM UM SUPLEMENTO
    • INSTALANDO O SUPLEMENTO
    • FECHANDO SUPLEMENTOS
  • USERFORMS........................................................................................................................................
    • CONTROLES DE INTERFACE..........................................................................................................
    • MÉTODOS DE UM USERFORM
    • EVENTOS EM UM USERFORM........................................................................................................
    • CRIANDO UM USERFORM
    • PROGRAMANDO O USERFORM
    • PROGRAMANDO CONTROLES
• REFERÊNCIAS BIBLIOGRAFICAS

OBS.: Sub-rotinas são públicas por padrão, portanto não é obrigatório escrever a palavra-chave Public , podemos apenas escrever Sub .

PASSAGEM DE PARÂMETROS

Ao declarar uma função ou procedimento, é possível declarar um ou mais argumentos. Ao utilizar uma função ou procedimento, você deve informar os valores para os argumentos, na mesma ordem em que foram definidos durante a declaração da função ou procedimento. Este processo é conhecido como passagem de parâmetros para a função ou procedimento, ou seja, ao chamar a função ou procedimento, passamos valores que serão utilizados pelo código da função ou procedimento.

Existem duas maneiras diferentes de fazer a passagem dos parâmetros, que são passagem por valor - ByVal e passagem por referência – ByRef.

PASSAGEM DE PARÂMETROS POR VALOR – BYVAL

Quando declarar os argumentos de uma função/procedimento, não é necessário usar a palavra ByVal já que os argumentos assumem automatricamente a opção ByVal, pois este é o método padrão. As duas declarações a seguir são equivalentes:

Sub Dobro(Num As Integer) Ou Sub Dobro(ByVal Num As Integer) Na segunda declaração, explicitamente, usa-se a opção ByVal, para indicar que o parâmetro Num será passado por valor.

O que significa a passagem de um parâmetro por valor? Na passagem de parâmetro por valor, ao iniciar a execução, a função faz uma cópia dos valores passados para serem utilizados nas operações. Supondo que o valor passado para a função fosse uma variável criada por você. Uma cópia deste valor seria efetuada durante a execução da função, não alterando em nada o valor original de sua variável. Significa que o procedimento receberá apenas o valor do parâmetro e não uma referência ao endereço de memória onde está armazenado o valor do parâmetro.

Para entender melhor este conceito, o exemplo abaixo mostra uma função principal chamada Sub Passagem_valor , na qual uma variável X é declarada, do tipo Integer, e inicializada, esta variável passa, por valor, o parâmetro para o procedimento Sub Dobro.

Sub Passagem_valor() Dim x As Integer x = 3 MsgBox " Valor dado a variavel X é : " & x 'Aqui estou chamando a procedure que vai dobrar o valor de x (sub Dobro) Dobro (x) MsgBox "Valor de X Após a Execução: " & x End Sub Sub Dobro(ByVal Num As Integer) MsgBox " Valor passado como parâmetro:" & Num Num = Num * 2 MsgBox "Dobro do valor:" & Num End Sub Na prática, ao passar um parâmetro por valor, você passa apenas uma cópia do valor da variável e é nesta cópia que a função/procedimento chamado trabalha, sem afetar o valor original da variável passada como parâmetro.

PASSAGEM DE PARÂMETROS POR REFERENCIA – BYREF

Para poder alterar o valor original de uma variável, a função/procedimento, tem que receber o parâmetro por referência – ByRef, ou seja, a função/procedimento tem que receber uma referência ao endereço de memória da variável passada como parâmetro e não uma simples cópia do valor da variável. Ao receber um parâmetro por referência (ByRef), as alterações que a função/procedimento fizer, serão feitas diretamente na variável original, pois agora, a função/procedimento tem acesso ao endereço da variável na memória e não mais apenas uma cópia do seu valor.

Para que um procedimento possa receber um parâmetro por referência, você deve utilizar a palavra ByRef, conforme o exemplo a seguir:

Public Sub DobraValor(ByRef Num As Integer)

Para entender melhor este conceito, o exemplo abaixo mostra uma função principal chamada Sub Passagem_Ref , na qual uma variável Y é declarada, do tipo Integer e inicializada, em seguida é passada, por referência, como parâmetro para o procedimento Dobro_Ref.

PROGRAMAÇÃO ORIENTADA A OBJETOS

A POO é uma metodologia de programação muito versátil que se molda a todas as áreas de aplicação da Ciência da Computação e Análise de Sistemas. Pode ser usada por todas as classes de programadores.

Na linguagem de programação VBA, toda manipulação de elementos de uma planilha ou características do Excel segue um modelo de objetos, propriedades, métodos, eventos.

OBJETO

É a instância de uma classe. Cada objeto de uma classe tem identidade e é distinguível. Vários objetos de uma mesma classe ocupam diferentes posições de memória, embora compartilhem os métodos (que estão apenas uma vez na memória). Cada objeto conhece a sua classe. É uma variável do tipo de uma classe Em VBA, um objeto é uma unidade de dados que representa determinado elemento da sua planilha ou do Excel; em outras palavras, é um objeto que permite uma referência, por exemplo, às células de sua planilha, a um gráfico ou à janela do Excel.Também pode ser objetos de outros aplicativos em seu aplicativo Visual Basic .Você pode criar seus próprios objetos e definir propriedades e métodos adicionais para eles.

Formas de manipular um objeto:

Alterar o seu conjunto de propriedades Ativar métodos específicos do objeto para executar determinadas tarefas Associar procedimentos aos eventos que podem ocorrer sobre o objeto

Exemplo 1:

Range("A1").Value = "VBA - Excel" Range("A1").Font.Name = "Arial" Range("A1").Font.Size = 11 O valor Range("A1") é um objeto : manipulações feitas sobre ele implicam ações realizadas na célula A.

Exemplo 2 ActiveCell.Value = 10, O objeto ActiveCell se refere à célula ativa no momento da execução da macro: no exemplo acima, a célula selecionada receberá o valor 10,

PROPRIEDADES

São o estado interno de um objeto. São as variáveis internas de um objeto. As propriedades ou atributos são as características dos objetos. Quando definimos uma propriedade normalmente especificamos seu nome e seu tipo. Podemos ter a idéia de que as propriedades são algo assim como as variáveis onde armazenamos os dados relacionados com os objetos.

Observe um dos exemplos abaixo: Range("A1").Value = "VBA - Excel!" Range("A1").Font.Name = "Arial" Range("A1").Font.Size = 11 Como dito anteriormente, estas instruções se aplicam ao objeto Range("A1") , ou seja, à célula A1. Elas alteram ou definem propriedades deste objeto. Propriedades são, portanto, características de cada objeto; por exemplo, não existe uma propriedade Value para o objeto Application , mas ela existe para uma célula (objeto Range ).

MÉTODOS

São a forma de se interagir com um objeto. São funções ou transformações que podem ser aplicadas aos objetos. São a única forma de alterarmos os atributos de um objeto. São a interface do objeto para o mundo externo. São os procedimentos e funções de um objeto. Métodos são ações aplicadas a um objeto. Ao contrário das propriedades, que modificam suas características, métodos realizam ações mais amplas.

Exemplo: ActiveCell.Delete Application.Quit

A lista à esquerda exibe todas as classes; ao selecionar um de seus itens, a região à direita exibirá todas as propriedades e métodos disponíveis para um objeto daquele tipo. Na figura acima, foi selecionada a propriedade Font dos objetos da classe Range.

As classes, métodos e propriedades subordinadas a um determinado objeto podem ser exibidos no momento da digitação de uma macro. Ao digitar o ponto após o nome do objeto em uso, esta lista será exibida automaticamente.

Para confirmar a escolha da opção pressione a tecla TAB.

Observe: Os métodos são exibidos com o ícone verde

EVENTOS

Eventos são ações que informam a um aplicativo que algo importante ocorreu, é algo que acontece aos objetos. Ocorre em resultado de uma ação do usuário, do sistema ou do próprio código. Por exemplo, quando o usuário clica em um formulário, o formulário pode desencadear um evento Click e chamar um procedimento que manipula o evento.

Outros exemplos: quando uma pasta de trabalho é aberta, dá-se o evento Open do objeto Workbook. O evento Activate ocorre quando uma pasta ou planilha é ativada. O evento BeforeSave ocorre antes da pasta de trabalho ser salva. Definindo Cancel, um dos parâmetros deste evento, como True, a pasta de trabalho será fechada sem ser salva.

Com os eventos você personaliza o comportamento dos objetos, tais como: pastas de trabalho, planilhas, gráficos, janelas, etc.

Os procedimentos correspondentes aos eventos têm todos a seguinte forma:

Private Sub Objeto_Evento(argumentos)

Exemplos:

Private Sub Workbook_Open()

Ocorre quando o arquivo é aberto por ação do método Open.

Private Sub Workbook_Activate()

Ocorre quando o arquivo passa a ser o arquivo ativo por ação do método Activate.

Private Sub Workbook_Newsheet(ByVal Sh As Object)

Ocorre quando é inserida uma nova planilha (Sh referencia a nova planilha).

Exemplo 1:

Criar uma função para obter o percentual do lucro. Para isso deve-se usar o preço de compra e preço de venda que se encontram na planilha. Conforme figura abaixo:

Function lucro(venda, compra)

lucro = (venda - compra) / compra

End Function

Exemplo 2:

Crie uma função que retorne uma string com a situação de cada aluno, conforme os critérios abaixo: A frequência deve ser acima de 75%, A média deve ser superior a 7,0.

Se a média menor que 7,0, escrever a mensagem “Reprovado por média”; Se a frequência inferior a 75% , escrever a mensagem “Reprovado por frequência”; Se a média maior e igual a 7,0 e menor 9,5, escrever a mensagem “Bom”; Se a média maior e igual a 9,5, escrever a mensagem “Ótimo”.

Function resultado (freq As Single , med As Single ) As String If freq < 0.8 Then resultado = "reprovado por frequência" ElseIf med < 7 Then resultado = "reprovado por média" ElseIf med <= 9 Then resultado = "satisfatório" Else resultado = "ótimo" End If End Function Execução de uma Função Uma vez que uma função produz um valor ela poderá ser executada: dentro de uma célula numa planilha, à semelhança de qualquer uma outra função do Excel. dentro de qualquer outra função ou SubRotina.

Ou pode utilizar o assistente de função:

Clique em OK para a próxima etapa do assistente de função, conforme abaixo:

Preencha as caixas de diálogo Freq e Med com a referência das respectivas células clique em OK.

Exemplo 3 Usando uma função do Excel em uma função no módulo VBA: Function Circulo(raio) Circulo = Application.Pi() * 2 * raio End Function

O corpo da macro pode ser composto por um conjunto de instruções, sendo que cada instrução diferente necessita de estar numa linha diferente. Contudo, quando se trata de instruções muito longas o editor faz a sua partição por diversas linhas, recorrendo ao operador “_”, com objetivo de facilitar a leitura.

INPUTBOX E MSGBOX

Permitem a interação entre o usuário e uma macro ou programa, funciona como uma interface de comunicação. Mostraremos dois elementos básicos para estabelecer esta ligação: InputBox e MsgBox.

FUNÇÃO INPUTBOX

InputBox é uma função que permite ao usuário introduzir dados no programa é portanto um mecanismo de entrada.

Exibe uma janela com uma caixa de texto para a inserção de dados. Espera que o utilizador introduza os dados e/ou acione um dos botões OK ou Cancela. Conforme figura abaixo:

Como é uma função produz um valor final. Este consiste nos dados inseridos pelo usuário no formato texto que normalmente são armazenados temporariamente em variáveis de uma macro ou programa.

Sintaxe: InputBox(prompt[, title] [, default] [, xpos] [, ypos] [, helpfile, context])

O único argumento da função que não está entre colchetes é a mensagem do prompt, pois é o único argumento obrigatório; os outros são opcionais. Vamos conferir cada um:

Prompt – É a mensagem a ser exibida ao usuário quando executada a inputbox.

Title – É o título da caixa de mensagem, situado na barra de identificação (barra azul).

Default – É um valor que já aparece preenchido na inputbox como valor padrão.

XPos, YPos – É a posição em que a caixa de mensagem situa-se na tela. O padrão é o centro.

HelpFile, Context – Utilizados para designar um arquivo e um contexto de ajuda para a caixa de mensagem.

FUNÇÃO MSGBOX

O MsgBox é um mecanismo de saída e permite ao usuário visualizar os dados produzidos pelo programa. Também pode receber entradas através de cliques em seus botões.

Sintaxe MsgBox (prompt[, buttons] [, title] [, helpfile, context])

MsgBox também possui diversos parâmetros, mas somente o primeiro é obrigatório, sendo que todos os outros quando ignorados assumem valores atribuídos por padrão.

Prompt – É a mensagem a ser exibida na caixa de mensagem. Se construir uma mensagem muito longa poderá utilizar o caractere Enter Chr(13); Exemplo:

MsgBox "A Soma de 3 com 5 é :" & Chr(13) & " 8 "

Buttons – Aqui temos várias opções de configuração de uma MsgBox. Se for omitido assumirá o valor 0 por padrão. A configuração “buttons” pode ser dividida em botões, comunicação visual e botões padrão; Veremos detalhes abaixo:

Title – É o título da caixa de mensagem. Se este for omitido, aparecerá o padrão que é o nome da aplicação.

HelpFile - Nome do arquivo que será utilizado para ajuda. Se for indicado este parâmetro o seguinte é obrigatório.

Context - Número do índice do tópico de ajuda. Exemplo: MsgBox "Erro de Sintaxe !!!", , "Mensagem de Erro"

Na janela de saída será exibida a mensagem “Erro de Sintaxe ”, o botão exibido será o de OK (por padrão) e o título da janela será “ Mensagem de Erro ”.