76151325 Program an Do Em Excel Com VBA 2010

UERJ / FEN / DEIN C. Borges; H. Rocha

Programando em Excel com VBA 1

Programando em Excel com VBA

Pág.

Sub-rotinas ou Macros 02

Editor do VBA 04

Linguagem orientada a objeto 07

Variáveis e constantes 13

Funções 17

Caixa de mensagem (MsgBox) e caixa de entrada (InputBox) 20

Estruturas de controle 24

Aprimorando objetos, propriedades e métodos 31

Escopo de variáveis, constantes e procedimentos 42

Exercícios 45

Importando dados e limpando o código gravado 51

Exercício final: Fluxo de caixa em macro 56

Depuração no VBA 58



Sub-rotinas ou macros

Uma macro ou sub-rotina é um pequeno programa com um conjunto de instruções. Estas

instruções são escritas numa linguagem denominada Visual Basic for Applications (VBA).

Usando o VBA podemos acelerar a realização de qualquer tarefa no Excel.

As sub-rotinas são procedimentos delimitados pelas palavras-chave Sub e End sub. Sua

declaração tem a seguinte estrutura:

Sub <nome_da_macro> ( )

<corpo_da_macro>

End Sub

Estas sub-rotinas ou macros são designadas pelo nome que lhe atribuímos e não recebem

parâmetros do exterior. O corpo da macro é 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 demasiado grandes pode-se fazer sua partição em diversas linhas, recorrendo ao

operador “_”, de modo facilitar a leitura.

Por exemplo, o procedimento seguinte torna B5 a célula ativa e, em seguida, a formata com

negrito.

Sub SetActive()

Worksheets("Plan1").Activate

Worksheets("Plan1").Range("B5").Activate

ActiveCell.Font.Bold = True

End Sub

Como outro exemplo, se você desejar inserir uma fórmula na célula D6 usando o Visual

Basic, você não terá que selecionar o intervalo D6. Você precisa apenas retornar o objeto

Range e, em seguida, definir a propriedade Formula com a fórmula desejada, conforme

mostrado no exemplo seguinte.

Sub EnterFormula()

Worksheets("Plan1").Range("D6").Formula = "=SUM(D2:D5)"

End Sub



Gravador de macros

O ambiente do VBA possui um gravador que registra todas suas ações tanto numa planilha

quanto num processador de texto e que as traduz para a linguagem VBA. A gravação de

macros é muito útil quando você não tem experiência suficiente para escrever linhas de

código em uma macro. Entretanto, possui graves limitações que somente são resolvidas por

meio da programação.

Gravando a primeira macro

1. Abra um novo arquivo e selecione uma das planilhas

2. Clique na célula A1

3. Acione o gravador de macro selecionando Ferramentas / Macro / Gravar nova macro.

4. Na caixa de diálogo que surge nomeie a macro por “Absoluta” e atribua a ela o atalho

Ctrl+a. Clique OK. Observe que deverá surgir na tela uma barra de ferramentas

contendo dois botões: o primeiro, “Parar gravação”, e o segundo, “Referência

relativa”. Certifique-se de que este último botão não esteja acionado.

5. Clique na célula A2 e escreva UERJ

6. Clique na célula A3 e escreva FEN

7. Clique na célula A4 e acione o botão Parar gravação.

8. Para testar a gravação, apague o que foi digitado nas células e posicione o cursor em

qualque célula da planilha

9. Tecle Ctrl+a e observe.

10. Limpe novamente as células, posicione o cursor agora em uma outra célula qualquer e

tecle novamente Ctrl+a.

11. Observe que a macro insere o texto digitado na mesma posição, independentemente de

onde esteja o cursor.

Gravando uma macro com referências relativas

Repita os passos anteriores, com as seguintes modificações: na etapa 4, nomeie a macro por

“Relativa” e atribua a ela o atalho Ctrl+r. Clique o botão OK e logo depois clique o botão de

Referência relativa. Siga os demais passos como na gravação anterior. Teste a gravação e

observe. Compare com a macro “Absoluta”. Ao final, grave o arquivo com o nome

Abs_vs_Relativa.



Editor do VBA

As macros gravadas acima são convertidas em instruções que podem ser visualizadas e

editadas em um ambiente de programação, o Editor do Visual Basic (Visual Basic Editor,

VBE). O modo mais fácil de entrar no VBE é teclando o atalho Alt+F11. Um outro modo é

selecionando Ferramentas / Macro / Editor do Visual Basic. Seguindo qualquer um desses

passos, encontraremos à direita do painel que se abre as linhas de código das duas macros até

o momento gravadas. À esquerda poderemos encontrar uma janela denominada Project

Explorer ou mesmo Project Browser. Esta janela se assemelha ao Windows Explorer e

permite que exploremos os arquivos que estiverem abertos no Excel, assim como os códigos

que eles contêm. Podem haver diversas pastas abertas por outros programas que se instalam

como módulos de macro no MS Office, tal como o Adobe PDF Writer e outros. O arquivo ou

pasta que salvamos contendo as macros gravadas aparece como um projeto VBA e contém

dois folders. O primeiro, Microsoft Excel Objetos, concentrará os códigos que forem

construídos na pasta e em suas planilhas. O segundo, Módulos, registra os módulos onde são

escritas ou gravadas as linhas de código das macros. Inicialmente, poderemos ter um único

módulo, ou mesmo dois, dependendo de como realizamos a gravação das macros. Além disso,

podemos inserir novos módulos.

Visualizando as linhas de código das macros gravadas

Com o VBE aberto, visualize as macros gravadas. Caso não estejam aparecendo, dê um duplo

clique no Módulo1, no Project Explorer. Veja abaixo as linhas de código criadas. Compare a

macro Absoluta com a macro Relativa e identifique as diferenças. Faça pequenas mudanças

nos códigos e observe seus efeitos na execução das duas macros. Podemos proceder à

execução de uma macro estando no ambiente VBE. Basta colocarmos o cursor em qualquer

linha do código da macro e teclarmos F5 ou selecionarmos Executar / Executar Sub.

Uma macro sempre começa com a declaração Sub, seguida de seu nome e os parêntesis (). A

última declaração é End Sub. As linhas que começam com apóstrofo são linhas de

comentários e não são interpretadas quando da execução do programa.

Sub Absoluta()

'

' Absoluta Macro

' Macro gravada em 21/7/2006 por Administrador



'

' Atalho do teclado: Ctrl+a

'

Range("A2").Select

ActiveCell.FormulaR1C1 = "UERJ"

Range("A3").Select

ActiveCell.FormulaR1C1 = "FEN"

Range("A4").Select

End Sub

______________________________________________________

Sub Relativa()

'

' Relativa Macro

' Macro gravada em 21/7/2006 por Administrador

'

' Atalho do teclado: Ctrl+r

'

ActiveCell.Offset(1, 0).Range("A1").Select

ActiveCell.FormulaR1C1 = "UERJ"


ActiveCell.FormulaR1C1 = "FEN"


End Sub

Criando um botão de macro

Quando desejamos executar uma macro em uma planilha, estando esta maximizada, podemos

usar as teclas de atalho da macro ou podemos selecionar Ferramentas / Macro / Macros,

marcar a macro desejada e clicar o botão Executar. Entretanto, podemos formatar um botão de

acionamento específico para cada macro de nossa planilha, tornando supérflua a memorização

de teclas de atalho. Para criar um botão de macro, torne visível a barra de ferramentas de

Formulários. Clique no ícone de Botão de comando e desenhe um botão de comando em

algum lugar da planilha. Uma caixa de diálogo irá se abrir automaticamente, solicitando que

seja informada qual macro deverá ser correlacionada a este botão.



Formatando o botão de macro

Se clicarmos com o botão direito do mouse sobre o botão de macro, este pode ser arrastado e

redimensionado, ou mesmo excluído. Sua fonte pode ser formatada e o nome do botão pode

ser alterado.

Pequenas dicas sobre o VBE

1. Uma macro pode ser facilmente executada em ambiente VBE, se colocarmos o cursor

sobre o código da macro e teclarmos F5.

2. Se colocarmos o cursor sobre uma palavra-chave ou comando e teclarmos F1, será

aberta uma janela de Ajuda sobre este comando.

3. Teclando F2 será aberto o Pesquisador de Objeto ou Object Browser, ferramenta de

consulta para a edição de macros no VBE

4. Como em um processador de texto, podemos recortar, copiar ou deletar palavras das

linhas de código de uma macro



Linguagem orientada a objeto

Toda programação em macro é baseada em modelos de objetos, sendo por isso a linguagem

VBA denominada “orientada a objeto”. Definamos objeto como uma parte de um ambiente

virtual que possui certas propriedades e funções (ou métodos).

Bill Jelen “Mr. Excel” & Syrstad se utilizam de uma instrução do nosso cotidiano, chutar a

bola, para ilustrar como a linguagem VBA funciona. Nesta linguagem, esta instrução seria

escrita assim:

Bola.Chutar

Nesta instrução, Bola é o objeto é Chutar é um método associado ao objeto Bola. Se tivermos

uma coleção de bolas de diversos tipos, podemos definir que a bola de futebol é a que será

chutada. A coleção será apresentada no plural, conforme abaixo:

Bolas(“Futebol”).Chutar

Objetos mais comuns do Excel

Objetos são elementos caracterizados por um conjunto de propriedades e que possuem um

determinado comportamento. Por exemplo, uma janela do Windows é um objeto. Também

são objetos o gráfico, o botão de comando, o arquivo, a figura, etc. Existem mais de 100

objetos no Excel, mas provavelmente você precisará conhecer de imediato apenas um

pequeno grupo de objetos.

Os objetos mais comuns do Excel são Application, Workbook, Worksheet e Range. Form,

Chart e ChartObject também são objetos bastante explorados. Application é o objeto do topo

hierárquico, representando o próprio programa Excel. O programa Excel pode conter diversas

pastas (Workbooks) abertas. Cada pasta pode conter várias planilhas (Worksheets), que

contêm inúmeras células. O objeto Range consiste em uma ou mais células de uma planilha.

Como exemplo, referenciar a planilha 1 dentro do arquivo Dados.xls seria de acordo com a

expressão abaixo:

Application.Workbooks(“Dados.xls”).Sheets(1)

Como outro exemplo, poderíamos registrar na célula A1 da mesma planilha o texto “Receita”

da seguinte forma:

Application.Workbooks(“Dados.xls”).Sheets(1).Range(“A1”).Value =“Receita”



Entretanto, esta instrução pode ser simplificada. O objeto Application pode ser omitido, sendo

referenciado de forma implícita. Além disso, se estivermos interessados somente na pasta que

estiver ativa, podemos eliminar a referência a Workbooks(“Dados.xls”). Assim, a instrução

acima ficaria simplificada conforme abaixo:

Sheets(1).Range(“A1”).Value =“Receita”

Porém, se estivermos concentrados na célula A1 da planilha ativa, a expressão pode ficar mais

reduzida ainda:

Range(“A1”).Value =“Receita”

Um outro objeto bastante importante é aquele que descreve a célula ativa. Trata-se de

Activecell. (Na verdade, Activecell é uma propriedade do objeto Worksheet que retorna um

objeto Range.)

Coleção

Uma coleção é um objeto que contém uma série de outros objetos, geralmente, mas nem

sempre, do mesmo tipo. No Microsoft Excel, por exemplo, o objeto Workbooks contém

todos os objetos Workbook que estiverem abertos. No Visual Basic, a coleção Forms

contém todos os objetos Form de um aplicativo.

Os itens de uma coleção podem ser identificados por número ou por nome. Por exemplo, no

procedimento a seguir, Workbooks(1) identifica o primeiro objeto Workbook aberto.

Sub CloseFirst()

Workbooks(1).Close

End Sub

Você também pode manipular toda uma coleção de objetos caso os objetos compartilhem

métodos comuns. Por exemplo, o procedimento a seguir fecha todos os formulários abertos.

Sub CloseAll()

Forms.Close

End Sub

Propriedades e métodos de um objeto

Um método é uma ação que um objeto pode executar. Por exemplo, ClearContents é um

método que limpa as fórmulas de um intervalo, ou os dados de um gráfico, deixando intacta a

formatação.

Exemplos:



Worksheets("Sheet1").Range("A1:G37").ClearContents

Charts("Chart1").ChartArea.ClearContents

Uma propriedade é um atributo de um objeto que define uma das características do objeto

(assim como tamanho, cor ou localização na tela) ou um aspecto do seu comportamento

(assim como se ela está ativada ou visível). Para alterar as características de um objeto, você

pode alterar os valores das suas propriedades.

Para definir o valor de uma propriedade, coloque, após a referência a um objeto, um ponto, o

nome da propriedade, um sinal de igualdade (=) e o novo valor da propriedade. Por exemplo,

o procedimento a seguir altera a legenda de um formulário do Visual Basic através da

definição da propriedade Caption.

Sub ChangeName(newTitle)

myForm.Caption = newTitle

End Sub

“Mr. Excel” & Syrstad fazem uma analogia entre nossa Gramática e os componentes de uma

instrução no VBA, conforme tabela a seguir.

Componente VBA Análogo a Nota

Objeto Nome

Coleção Nome no plural Normalmente especifica qual objeto da

coleção. Ex.: Worksheets(1)

Método Verbo Object.Method

Parâmetro

(Argumento)

Advérbio Lista parâmetros após o método. Separa o

nome do parâmetro do seu valor com :=

Propriedade Adjetivo Object.Property

A propriedade pode ter seu valor definido ou

simplesmente consultado; O separador entre o

nome da propriedade e seu valor é o sinal =

Voltando ao exemplo da instrução de chutar a bola, podemos planejar como a ação deverá ser

executada, através de parâmetros. Por exemplo, nossa instrução poderia ficar assim:

Bolas (“Futebol”).Chutar Direção:=Esquerda, Força:=Muita

“Mr. Excel” & Syrstad comentam que cada método dentro do VBA tem sua lista de

parâmetros e que existe uma ordem de definição destes parâmetros. Além disso, a definição



de alguns é opcional. Felizmente, podemos utilizar a Ajuda no ambiente VBE quando da

codificação de uma instrução.

Por sua vez, sendo a cor uma das propriedades da bola de futebol, esta cor poderia ser

definida conforme exemplo abaixo:

Bolas (“Futebol”).Cor=Branca

Nem sempre é fácil diferenciarmos propriedades de métodos para os objetos do Excel.

Felizmente, na maioria das vezes não precisaremos nos preocupar com esta distinção.

Obs.: a ordem dos parâmetros de um método pode ser alterada, desde que eles sejam

nomeados (vide adiante as funções MsgBox e InputBox).

Obtendo informações sobre objetos, propriedades e métodos

Para obtermos mais informações sobre os objetos do Excel, suas propriedades e métodos,

podemos utilizar três fontes que se complementam. A primeira é usar a Ajuda do VBE. A

expressão “Objetos do Microsoft Excel” dará acesso a praticamente todos os objetos que

podem ser úteis a quem pretende aprimorar seus conhecimentos em programação VBA.

Quando desejamos conhecer os detalhes referentes a um determinado objeto, propriedade ou

método presente em uma linha de comando de uma macro, podemos colocar o cursor sobre o

seu nome e teclar o atalho F1. Um arquivo da Ajuda será aberto imediatamente.

O gravador de macro também é um excelente meio de se aprender a programação em VBA,

apesar de possuir grandes limitações. Se você deseja manipular uma propriedade de um objeto

específico, como por exemplo as bordas ou a cor de fundo de uma célula, e não conhece

exatamente o nome desta propriedade, pode gravar a ação que deseja ser transformada em

macro. Em seguida, basta analisar as linhas de código criadas pelo gravador. Cabe reforçar

que alguns objetos não são do alcance do gravador de macro. É o caso de dois importantes

objetos de comunicação entre o programa e o usuário: a caixa de mensagem e a caixa de

entrada.

Por último, podemos também usar o Pesquisador de Objeto no VBE, que é acionado pelo

atalho F2. Esta alternativa é mais aconselhável para quem está mais familiarizado com o

VBA.

Eventos

Um evento é uma ação reconhecida por um objeto, como clicar o mouse ou pressionar uma

tecla e para a qual você pode escrever código para responder. Podem ocorrer eventos em

resposta a uma ação do usuário ou a um código do programa.



Os eventos de pasta de trabalho entram no módulo EstaPasta_de_trabalho, visível no Project

Explorer; os eventos de planilha entram no módulo da planilha que eles afetam (como Plan);

os eventos de gráfico entram no módulo da planilha de gráfico que eles afetam (como Gráf).

Quando um módulo EstaPasta_de_Trabalho, Plan1, Gráf1 está ativo, os eventos

correspondentes ficam disponíveis por meio dos menus suspensos Objeto (à esquerda) e

Procedimento (à direita). Depois que o objeto é selecionado, o menu suspenso Procedimento

atuializa a lista de eventos disponíveis para esse objeto.

Os eventos em nível de planilha, por exemplo, ocorrem quando uma planilha é ativada ou o

usuário altera uma célula de planilha. O exemplo seguinte ajusta o tamanho das colunas de A

até F sempre que a planilha é recalculada.

Private Sub Worksheet_Calculate()

Columns("A:F").AutoFit

End Sub

Como outro exemplo, o evento Activate ocorre quando uma pasta de trabalho, planilha, folha

de gráfico ou gráfico incorporado é ativado. O exemplo abaixo classifica o intervalo A1:A10

quando a pasta de trabalho é ativada.

Private Sub Worksheet_Activate()

Range("a1:a10").Sort Key1:=Range("a1"), Order:=xlAscending

End Sub

Os eventos podem ainda fazer chamadas de sub-rotina ou de função fora de seus próprios

módulos.

Exemplo:

Suponha que você pretenda executar uma macro sempre que abrir uma determinada pasta.

1º Crie a macro que pretende executar.

2º No Editor de Visual Basic, na janela Project Explorer selecione o objeto Esta Pasta. Na

janela de edição repare nas duas caixinhas que se encontram na parte superior. A do lado

esquerdo indica Geral clique nela e selecione o elemento pasta, na caixinha da direita

selecione o evento Open.

3º Automaticamente aparecerá um procedimento na janela de edição cujo nome será

Worksheet_Open. Tudo o que for escrito no seu conteúdo será executado quando o

documento for aberto. Neste caso, indique o nome da macro desejada.



Para visualizar os procedimentos de evento de uma planilha, clique com o botão direito na

guia da planilha e clique em Exibir código no menu de atalho. Selecione o nome do evento

na caixa de listagem suspensa Procedimento.



Variáveis e constantes

Variáveis

De acordo com Cinto & Góes, variável é um local nomeado da memória onde são guardados

dados que podem ser modificados durante a execução do programa. Por sua vez, constantes

são itens nomeados que mantêm um valor constante por toda a execução de um programa. As

regras de nomeação de constantes e variáveis são: o nome deve começar com uma letra, mas

pode ser alfanumérico; não pode haver espaços entre caracteres; não há distinção entre

maiúsculo e minúsculo; não pode ter mais do que 255 caracteres; não podem ser usadas

palavras já reservadas pelo VBA, tais como Range ou Sheet.

Tipos de dados armazenados nas variáveis

Os tipos de dados armazenados em variáveis ou em constantes no VBA são:

Tipos de dados Bytes usados Valor

Byte 1 Números sem sinal entre 0 e 255

Integer 2 Números inteiros, de –32.768 a 32.767

Long 4 Números inteiros, de –2.147.483.648 a 2.147.483.647

Single 4 Número real, 1.401298E-45 a 3.402823E38 (positivo ou

negativo)

Date 8 Datas

Double 8 Número real, de 4.94065645841247E-324 a

1.79769313486232E308 (positivo ou negativo)

Boolean 2 Verdadeiro (True) ou Falso (False)

Currency 8 Moeda

Object 4 Um objeto do Excel

Range 4 Um conjunto de células

String 1 por caractere Conjunto de caracteres

Variant 16 Qualquer tipo de dados

Declarando variáveis

Podemos formatar variáveis que armazenem diferentes tipos de dados. Isto se faz por meio da

declaração de variáveis, utilizando-se a instrução Dim. Por exemplo, na linha a seguir

exemplificamos uma declaração de variável:



Dim IdadeMin As Integer

Você pode declarar diversas variáveis em uma instrução. Para especificar um tipo de dados,

você deve incluir o tipo de dados para cada variável. Na instrução a seguir, as variáveis intX,

intY e intZ são declaradas como tipo Integer.

Dim intX As Integer, intY As Integer, intZ As Integer

Se você introduz um nome de variável sem qualquer declaração prévia do tipo de dados, o

VBA automaticamente assume que esta variável é do tipo Variant. Na instrução a seguir, intX

e intY são declaradas como tipo Variant; apenas intZ é declarada como tipo Integer.

Dim intX, intY, intZ As Integer

Variáveis de diferentes tipos de dados podem ser declaradas em separado, mas também

podem ser declaradas em conjunto, conforme abaixo:

Dim Nome As String, Idade As Integer, Salario As Currency

Benefícios na declaração de variáveis

Caso não declaremos o tipo referente a uma variável, o que é opcional para o VBA, esta

poderá armazenar dados de qualquer tipo. Isto parece vantajoso, mas na verdade a declaração

da variável tem diversas vantagens. Em primeiro lugar, torna o programa mais organizado,

facilitando o entendimento por parte dos outros e a correção de erros por parte do

programador. Sempre que você declarar as variáveis, o VBA o informará caso encontre

alguma variável sem declaração. Por exemplo, considere que você declarou todas variáveis de

seu programa, e que uma destas, nomeada ReceitaVenda, foi formatada para armazenar

valores em moeda. Caso, por exemplo, ao longo do programa você digite por engano a

palavra RecitaVenda, o VBA tratará este termo como uma possível variável ainda não

declarada e o informará, permitindo assim a correção imediata de um erro que de outra forma

levaria bastante tempo para ser corrigido.

Uma outra vantagem importante é a de que o VBA sempre checará se o usuário do programa

estará alimentando uma variável com o tipo de dado definido na sua declaração. Por último,

com a declaração de variáveis a macro ocupa menos espaço na memória e é executada mais

rapidamente, o que torna-se perceptível com macros mais pesadas.

Como mencionado, a declaração de variáveis é benéfica por diversos motivos. Deste modo,

caso você queira é possível forçar tal procedimento, bastando incluir acima de todos os

módulos do programa a instrução Option Explicit. Essa instrução obriga-o a declarar

explicitamente todas as variáveis dentro do módulo.



Caso um módulo inclua a instrução Option Explicit , ocorrerá um erro em tempo de

compilação (período durante o qual o código-fonte é traduzido em código executável) quando

o Visual Basic encontrar um nome de variável que ainda não tenha sido declarada ou que

apresente algum erro de digitação (Ajuda do VBA).

Declarando constantes

Por intermédio da declaração de uma constante, você pode atribuir um nome significativo a

um valor. Utilize a instrução Const para declarar uma constante e definir o seu valor. Depois

que uma constante tiver sido declarada, não será possível modificá-la ou atribuir-lhe um novo

valor.

As constantes podem ser declaradas como um destes tipos de dados: Boolean, Byte, Integer,

Long, Currency, Single, Double, Date, String ou Variant . Como você já conhece o valor

de uma constante, pode especificar o tipo de dados em uma instrução Const.

Você pode declarar diversas constantes em uma instrução. Para especificar um tipo de dados,

você deve incluir o tipo de dados de cada constante.

Constantes intrínsecas

Uma constante intrínseca é aquela fornecida por um aplicativo. As constantes do Visual Basic

estão listadas na biblioteca de objetos e podem ser visualizadas com o Pesquisador de

objeto. Como você não pode desativar constantes intrínsecas, não pode criar uma constante

definida pelo usuário com o mesmo nome. Como exemplo, xlLandscape (Paisagem) e

xlPortrait (Retrato) são duas constantes que definem a orientação de uma página. Como

exemplo de seu uso:

ActiveSheet.PageSetut.Orientation = xlLandscape

Cada constante intrínseca realmente possui um valor numérico particular. No exemplo abaixo

podemos buscar na caixa de mensagem o valor da constante xlLandscape (igual a 2).

Sub ShowValue()

MsgBox xlLandscape

End Sub

Variável de objeto

Variável de objeto é uma variável que contém uma referência a um objeto. Este pode ser uma

planilha, um gráfico, uma Range, etc. A variável de objeto deve ter as mesmas propriedades e

métodos do objeto referenciado.

Você pode tratar uma variável de objeto exatamente como o objeto ao qual ela se refere.



Para criar uma variável de objeto, (1) declare a variável de objeto e (2) atribua a variável de

objeto a um objeto.

Declarando uma variável de objeto

Utilize a instrução Dim ou uma das outras instruções de declaração (Public, Private ou

Static) para declarar uma variável de objeto. Uma variável que se refira a um objeto deve ser

uma Variant , um Object ou um tipo específico de objeto. Por exemplo, as declarações a

seguir são válidas:

' Declara MyObject como o tipo de dados Variant.

Dim MyObject

' Declara MyObject como o tipo de dados Object.

Dim MyObject As Object

' Declara MyObject como o tipo de dados Range.

Dim MyObject As Range

Observação Se você utilizar uma variável de objeto sem declará-la primeiro, o seu tipo de

dados será, como padrão, Variant .

Você pode declarar uma variável de objeto com o tipo de dados Object em situações onde o

tipo de objeto específico só se torna conhecido na execução do procedimento. Utilize o tipo

de dados Object para criar uma referência genérica a qualquer objeto. Caso conheça o tipo de

objeto específico, deve declarar a variável de objeto como pertencendo a esse tipo de objeto,

pois a declaração de tipos de objeto específicos proporciona a verificação automática do tipo,

código mais rápido e melhoria em legibilidade.

Atribuindo uma variável de objeto a um objeto

Utilize a instrução Set para atribuir um objeto a uma variável de objeto. O exemplo abaixo

declara uma variável de objeto e logo depois a atribui a um objeto.

Sub ObjTest()

Dim theRange As Range

Set theRange = ActiveSheet.Range(“A1:C25”)

TheRange.Value = 19

End Sub

Você também pode combinar a declaração de uma variável de objeto com a atribuição de um

objeto a ele utilizando a palavra-chave New com a instrução Set. Por exemplo:

Set MyObject= New Object ' Cria e atribui



Funções

Comparando Funções e Sub-rotinas

Funções e sub-rotinas ou macros são constituídas de uma série de instruções do VB que

executam ações. A primeira diferença entre elas é que funções geralmente retornam valor,

enquanto sub-rotinas não. Uma segunda diferença é que podemos criar macros utilizando o

gravador de macros, embora possamos criar macros mais refinadas no ambiente VBA.

Funções só podem ser criadas no ambiente VBA.

Para criar uma função ou uma sub-rotina no ambiente VBA é necessário selecionar um

módulo na Janela de código. Caso não haja algum, podemos criar um novo módulo

selecionando Inserir / Módulo. Selecionado o módulo, podemos escrever a macro ou a função

integralmente ou podemos recorrer ao caminho Inserir / Procedimento para que o VBA crie a

estrutura da rotina.

Funções

Funções são procedimentos delimitados pelas palavras-chave Function e End Function. Todas

as funções utilizadas no Excel são desse tipo. Sua declaração tem a seguinte estrutura:

Function <nome da função> (<argumento ou parâmetro1>, <argumento2>,...)

<nome da função> = <Valor ou expressão>

End Function

A função é identificada pelo nome, pelo número e tipo de argumentos recebidos, e tem como

objetivo executar um conjunto de instruções e produzir um valor final. Isto é, sempre que se

pretender executar uma função é sabido a priori que ela produzirá um valor.

Entretanto, algumas funções não precisam de argumentos, como p. ex. as funções Rand() e

Agora(). Outras não retornam valor, como p. ex. a função MsgBox.

Definição do tipo de parâmetros e do tipo da função

Todos os elementos de entrada e saída de uma função têm um tipo de dados atribuído. Assim,

cada parâmetro poderá ser definido com o tipo de dado respectivo e a função também poderá

ser definida com o tipo de dado que ela enviará para o exterior. Podemos então refinar a

definição de uma função:

Function <Nome da Função> ( <parâmetro1> As <Tipo>, …) As <Tipo>



<Nome da Função> = <Valor / Expressão>

End Function

Nota: Se os tipos não forem definidos será assumido por padrão como sendo do tipo Variant.

Criando uma função

Exemplo: Escreva a função CPMF como abaixo:

Public Function CPMF(Valor As Currency, Taxa As Single) As Currency

CPMF = Valor * (Taxa / 100)

End Function

Execução de uma função

Uma Função definida pelo usuário poderá ser executada dentro de uma célula numa planilha,

à semelhança de qualquer uma outra função do Excel, ou dentro de qualquer outra Função ou

Sub-rotina. Para executar a função numa célula, devemos selecionar Inserir / Função e optar

pela categoria Definida pelo usuário. Experimente com a função recém-criada.

Uma função dentro de outra função

Escreva a função Saldo conforme abaixo:

Public Function Saldo(Valor As Currency, Taxa As Single)

Saldo = Valor - CPMF(Valor, Taxa)

End Function

Observe que o argumento Taxa da função CPMF também deve ser definido como argumento

da função Saldo. Caso contrário, o usuário não será chamado a informar a taxa de juros e o

VBA acusará erro.

Uma função dentro de uma Sub-rotina

Escreva a Sub-rotina abaixo, que utiliza a função CPMF criada. A sub-rotina e a função

CPMF podem ficar num único módulo ou em módulos separados.

Public Sub Calcula_Saldo_Liq()

Dim Valor As Currency, ValorCPMF As Currency, SaldoLiq As Currency, Taxa _

As Single

Valor = InputBox("Informe o valor da aplicação")

Taxa = InputBox("Informe a taxa % da CPMF")

ValorCPMF = CPMF(Valor, Taxa)



SaldoLiq = Valor - ValorCPMF

MsgBox "Saldo Líquido = " & SaldoLiq

End Sub

Obs.: Se a função CPMF fosse de escopo Private, não seria acessível a sub-rotina de outro

módulo que não o seu. Confira.

Funções de planilha ou Worksheet functions

As funções normalmente usadas nas planilhas também podem ser usadas nas macros, a não

ser que o VBA já possua uma função que desempenhe o mesmo papel.

Por exemplo, podemos registrar em uma célula a soma de uma Range:

Worksheets(1).Range(“D6”).Formula = ” = Sum(D2:D5)”

Porém, caso a Range esteja associada a uma variável objeto (p. ex., variável “Faixa” abaixo),

torna-se necessária a propriedade WorksheetFunction:

Worksheets(1).Range(“D6”).Formula = ” = Application.WorksheetFunction.Sum(Faixa)”



Caixa de mensagem e caixa de entrada

O VBA oferece ao usuário duas interfaces de comunicação com o programa ou macro. A

primeira, MsgBox, é uma interface de saída que exibe apenas uma mensagem e aguarda que o

usuário selecione um botão. A segunda, InputBox, é uma interface de entrada de dados que

exibe uma mensagem e aguarda uma resposta do usuário.

Enquanto MsgBox é uma função do VBA, InputBox pode ser tanto uma função quanto um

método, como veremos adiante.

Função MsgBox

A função MsgBox exibe uma mensagem (Prompt) em uma caixa de diálogo com um ou mais

botões. Quando o usuário seleciona um deles, a função retorna um integer que indica qual

botão foi clicado.

Sintaxe resumida:

MsgBox(prompt, buttons, title)

Prompt (parâmetro necessário): expressão exibida como mensagem na caixa de diálogo. Se

prompt consistir em mais de uma linha, você poderá separar as linhas utilizando um caractere

de retorno de carro (Chr(13)), um caractere de alimentação de linha (Chr(10)) ou uma

combinação de caracteres de retorno de carro e alimentação de linha (Chr(13) & Chr(10))

entre cada linha.

Buttons (parâmetro opcional): Expressão numérica que é a soma de valores que especifica o

número e o tipo de botões a exibir, o estilo de ícone a utilizar, a identidade do botão padrão e

a modalidade da caixa de mensagem. Se omitido, o valor padrão para buttons é 0.

Title (parâmetro opcional): Expressão de seqüência de caracteres exibida na barra de título da

caixa de diálogo. Se você omitir title, o nome do aplicativo será inserido na barra de título.

As definições do argumento buttons são as seguintes:

Constante Valor Descrição

Definindo o número e o tipo de botões exibidos:

VbOKOnly 0 Exibe somente o botão OK.

VbOKCancel 1 Exibe os botões OK e Cancelar.

VbAbortRetryIgnore 2 Exibe os botões Abortar, Repetir e Ignorar.

VbYesNoCancel 3 Exibe os botões Sim, Não e Cancelar.



VbYesNo 4 Exibe os botões Sim e Não.

VbRetryCancel 5 Exibe os botões Repetir e Cancelar.

Definindo o estilo de ícone que aparece na caixa de diálogo:

vbCritical 16 Exibe o ícone Mensagem crítica.

vbQuestion 32 Exibe o ícone Consulta de aviso.

vbExclamation 48 Exibe o ícone Mensagem de aviso.

vbInformation 64 Exibe o ícone Mensagem de informação.

Determinando qual botão é o padrão:

vbDefaultButton1 0 O primeiro botão é o padrão.

vbDefaultButton2 256 O segundo botão é o padrão.

vbDefaultButton3 512 O terceiro botão é o padrão.

Os valores retornados pelo MsgBox são apresentados abaixo.

Constante Valor (integer) Descrição

vbOK 1 OK

vbCancel 2 Cancelar

vbAbort 3 Abortar

vbRetry 4 Repetir

vbIgnore 5 Ignorar

vbYes 6 Sim

vbNo 7 Não

Cinto e Góes exemplificam abaixo o uso do MsgBox com uma macro que pergunta ao usuário

se ele deseja salvar as alterações, com as opções Sim, Não e Cancelar.

Sub Msg_SalvarAlteracoes()

Opcao = MsgBox(“Deseja salvar as alterações?”, 3+32+256, “Confirmação”)

If Opcao = vbYes Then

MsgBox(“Clicou em Sim”)

Elseif Opcao = vbNo Then

MsgBox(“Clicou em Não”)

Elseif Opcao = vbCancel Then

MsgBox(“Clicou em Cancelar”)

End If

End Sub



A sequência dos parâmetros (prompt, buttons, title) pode ser alterada, desde que estes sejam

nomeados. No exemplo abaixo, invertemos a ordem dos parâmetros title e prompt e omitimos

buttons.

MsgBox Title:="Caixa de tarefa", Prompt:="Tarefa concluída"

Função InputBox

A função Inputbox exibe uma caixa de diálogo contendo uma mensagem (Prompt), uma

caixa de texto e dois botões (OK e Cancelar). Quando escolhemos o botão OK, a função

retorna para a macro o valor inserido na caixa de texto, sob a forma de texto (String). Quando

clicamos no botão Cancelar, a função retorna False.

Sintaxe resumida:

InputBox(Prompt, Title, Default)

Prompt (parâmetro necessário): é a mensagem a ser exibida na caixa de diálogo. Pode ser uma

seqüência de caracteres, um número, uma data ou um valor Booleano.

Title (parâmetro opcional): É o título da caixa de entrada. Se esse argumento for omitido, o

título padrão será "Entrada".

Default (parâmetro opcional): Especifica um valor que aparecerá na caixa de texto quando a

caixa de diálogo for inicialmente exibida. Se esse argumento for omitido, a caixa de texto será

deixada vazia.

O exemplo abaixo pede um número ao usuário e atribui à variável myNum o valor digitado

por ele.

myNum = InputBox("Entre um número")

Método InputBox

O objeto Application apresenta, dentre outros, o método Inputbox . O método InputBox

difere da função InputBox porque permite validação seletiva da entrada do usuário. Por

exemplo, podemos necessitar que o usuário informe à macro um endereço de uma célula ou

conjunto de células, ou seja, informe um objeto Range. Esta restrição ao tipo de resposta que

o usuário deve fornecer é definida pelo argumento Type. Em distinção ao método InputBox, a

função InputBox não possui tal argumento.

Sintaxe resumida:

Application.InputBox(Prompt, Title, Default, Type).



O parâmetro ou argumento Type especifica o tipo de dados retornado. Se este argumento for

omitido, a caixa de diálogo retornará texto (string), funcionando de modo idêntico à função

InputBox. O argumento Type pode ter um dos valores abaixo ou a soma deles.

Valor Significado

0 Uma fórmula

1 Um número

2 Texto (uma seqüência)

4 Um valor lógico (True ou False)

8 Uma referência a células, como um objeto Range

16 Um valor de erro, como #N/D

64 Uma matriz de valores

Você pode usar a soma dos valores permitidos para Type. Por exemplo, para uma caixa de

entrada que possa aceitar tanto texto como números, defina Type como 1 + 2.

Comentários

1. Observe que Application.InputBox chama o método InputBox e InputBox sem qualificador

de objeto chama a função InputBox .

2. Quando Type é 8, InputBox retorna um objeto Range. Você precisa usar a instrução Set

para atribuir o resultado a um objeto Range (variável objeto). O exemplo abaixo pede ao

usuário que selecione uma célula em Plan1.


Set minhacelula = Application.InputBox(prompt:="Selecione uma celula", Type:=8)

O valor 8 atribuído ao argumento Type assegura que o dado retornado pelo usuário seja um

endereço de célula ou conjunto de células (um objeto Range). Tal endereço é armazenado na

variável “minhacelula”.



Estruturas de controle

Uma das vantagens de programarmos as macros é a maior habilidade em se automatizar

tarefas. Para isto é necessário ser capaz de se controlar o fluxo do programa. As estruturas de

controle são bastante comuns às diversas linguagens de programação. O VBA permite que as

utilizemos para a construção de macros versáteis, que não estão ao alcance do gravador de

macros.

Estrutura If-Then-Else

Esta é a estrutura de controle mais utilizada no Excel, tendo o mesmo tipo de funcionamento

da função SE do Excel.

Sintaxe da Estrutura If-Then-Else

If < condição > Then

< declaração >

Else

< declaração >

End If

A instrução Else é opcional numa estrutura If-Then-Else. No caso de ser omitida, a avaliação

negativa da condição implica uma saída automática da Instrução If.

Aplicação Prática: uma aposta

A rotina Aposta recebe uma aposta do usuário e mediante o sorteio a realizar pela respectiva

função verifica se o jogador ganhou ou não a aposta, comunicando-lhe esse fato.

Public Sub Aposta()

Dim NAposta As Integer

Dim NSorteio As Integer

NAposta = InputBox("Em que número aposta? (entre 1 e 50)")

NSorteio = Sorteio()

If NSorteio = NAposta Then

MsgBox "Parabéns! Acertou em cheio! O número sorteado foi o " & NSorteio

Else

MsgBox "Continue a tentar! O número sorteado foi o " & NSorteio

End If

End Sub



__________________________________

Public Function Sorteio() As Integer

Sorteio = Int(Rnd() * (50 - 1) + 1)

End Function

As instruções If podem ser aninhadas, colocando uma instrução dentro da outra. A forma

geral seria:


If <outra condição> Then

< declaração >

Else

< declaração >

End If

Else

< declaração >

End If

Entretanto, a colocação de mais do que três instruções If aninhadas torna a macro bastante

complicada.

Instrução adicional ElseIf

Esta instrução propõe uma condição alternativa se o teste da condição anterior tiver tido um

resultado negativo. Sua forma geral é:


< declaração >

ElseIf < condição > Then

< declaração >

ElseIf < condição > Then

< declaração >

.

Else

< declaração >

End If



Aplicação Prática

Pretende-se criar uma macro que classifique etariamente um indivíduo em função da sua

idade. A classificação pretendida é a seguinte:

Idade Classe Etária

Menos de 3 anos Bebê

Dos 3 aos 12 Criança

Dos 13 aos 17 Adolescente

Dos 18 aos 25 Jovem

Dos 26 aos 65 Adulto

Mais de 65 Idoso

Public Sub Classe_Etaria()

Dim Idade As Integer

Idade = InputBox("Introduza a idade: ", "Classificação Etária")

If Idade < 3 Then

MsgBox "É um bebê.", , "Classificação Etária"

ElseIf Idade <= 12 Then

MsgBox "É uma criança.", , "Classificação Etária"


MsgBox "É um adolescente.", , "Classificação Etária"


MsgBox "É um jovem.", , "Classificação Etária"


MsgBox "É um adulto.", , "Classificação Etária"

Else 'refere-se ao último ElseIf

MsgBox "É um idoso.", , "Classificação Etária"

End If

End Sub



Estrutura Select Case

Quando necessitamos que o VBA escolha entre várias diferentes opções, a estrutura Select

Case é a melhor solução. Sua forma geral é:

Select Case < expressão >

Case < expressão >

< declaração >

Case < expressão >

< declaração >

Case Else

< declaração >

End Select

Aplicação Prática

O problema resolvido com If –Then –Else – ElseIf poderia seria resolvido com a estrutura

Select Case. Veja abaixo.

Public Sub Classe_Etaria_2()

Dim Idade As Integer

Idade = InputBox("Introduza a idade: ", "Classificação Etária")

Select Case Idade

Case Is < 3

MsgBox "É um bebê.", , "Classificação Etária"

Case Is <= 12

MsgBox "É uma criança.", , "Classificação Etária"

Case Is <= 17

MsgBox "É um adolescente.", , "Classificação Etária"

Case Is <= 25

MsgBox "É um jovem.", , "Classificação Etária"

Case Is <= 65

MsgBox "É um adulto.", , "Classificação Etária"

Case Else 'caso residual

MsgBox "É um idoso.", , "Classificação Etária"

End Select

End Sub



Estrutura For-Next

Esta é uma das estruturas de loop do VBA, sendo talvez a mais utilizada. Ela utiliza um

contador para determinar o número de vezes em que um conjunto de instruções será

executado. Sua sintaxe é apresentada abaixo.

For “contador” = < valor inicial> To < valor final > [ Step < valor a incrementar >]

< instruções >

Next

O comando Step e o valor a incrementar são opcionais. O padrão é o incremento de uma

unidade.

Um exemplo prático: A macro abaixo prepara uma coluna com taxas de juros.

Public Sub Taxas()

Dim Taxa As Integer, Contador As Integer

Taxa = 0

Range("A1").Activate

Range("A1").Value = "Taxas (%)"

Range("A2").Value = 0

For Contador = 1 To 20

ActiveCell.Offset(Contador, 0).Value = Taxa + Contador

Next Contador

End Sub

Obs.: As estruturas If-Then-Else e Select Case podem ser aninhadas dentro de um loop.

Assim, a cada loop podem ser tomadas decisões, e se por alguma decisão se pretenda sair do

loop deve-se incluir a declaração Exit For.

Estrutura While-Wend

A estrutura While-Wend tem um funcionamento similar ao For-Next. Ela realiza um loop um

determinado número de vezes, enquanto uma determinada condição for verdadeira. Sua

sintaxe é apresentada abaixo.

While < condição >

< declaração >

Wend

Estruturas de

loop:

For-Next

While-Wend

Do-While

Do-Until



Aplicação prática:

Na macro abaixo o usuário é convidado a apostar num número entre 1 e 10. A instrução

While-Wend lança números aleatórios entre 1 e 10 e totaliza o número de lançamentos

necessários até que o apostador acerte.

Public Sub Loteria()

Dim Num_Loteria As Integer

Dim Aposta As Integer

Dim Num_Lanc As Integer

Num_Lanc = 0

Num_Loteria = 0

Aposta = InputBox("Introduza a sua aposta! (entre 1 e 10)")

While Num_Loteria <> Aposta

Num_Loteria = Int(9 * Rnd() + 1)

Num_Lanc = Num_Lanc + 1

Beep

Wend

MsgBox "A sua aposta foi conseguida depois de " & Num_Lanc & " lançamentos"

End Sub

Estrutura Do-While

Trata-se de outra estrutura de loop, onde um conjunto de instruções é executado enquanto

uma condição determinada é atendida. Sua sintaxe é apresentada a seguir.

Do While < condição >

< declaração >

Loop

Uma variação desta estrutura é apresentada abaixo.

Do

< declaração >

Loop While < condição >

Aplicação prática: utilize essa estrutura para reformatar as macros anteriores, Taxas() e

Loteria().



Estruturas Do-Until

É uma estrutura que se contrapõe à Do-While. Ela executa um conjunto de instruções até que

uma determinada condição seja atendida. Sua sintaxe é apresentada a seguir.

Do Until < condição >

< declaração >

Loop

Esta estrutura também possui uma variação, apresentada abaixo.

Do

< declaração >

Loop Until < condição >

Aplicação prática: utilize também essa estrutura para reformatar as macros Taxas() e

Loteria().

Estrutura GoTo e Instrução On Error GoTo

GoTo é a estrutura de controle mais simples no VBA, permitindo basicamente que você passe

por cima de algumas linhas de código e salte para o ponto desejado. Sua maior utilidade está

no tratamento de erros por meio da instrução On Error GoTo.

O exemplo a seguir é o de um programa que nomeia uma a uma as planilhas da pasta que

estivá ativa no Excel, mesmo que desconheçamos o número de planilhas que existam nesta

pasta. Quando não há mais planilhas, ocorre um erro que aciona a instrução GoTo. O

programa então é concluído de forma satisfatória. Observe que se trata de um erro que

sabemos que ocorrerá, mais cedo ou mais tarde.

Public Sub Exemplo_GoTo()

Dim x As Integer

On Error GoTo Acabaram_Planilhas

For x = 1 To 1000

Sheets(x).Name = "Planilha" & x

Next x

Acabaram_Planilhas:

MsgBox "Acabaram as planilhas"

End Sub



Aprimorando objetos, propriedades e métodos Como fazer referência a células e intervalos

Uma tarefa comum ao usar o Visual Basic é especificar uma célula ou intervalo de células e,

em seguida, fazer algo com elas, como inserir uma fórmula ou alterar o formato. Geralmente,

você pode fazer isso em uma instrução que identifique o intervalo e também altere uma

propriedade ou aplique um método.

Um objeto Range no Visual Basic pode ser uma única célula ou um intervalo de células. Os

tópicos seguintes mostram as maneiras mais comuns de identificar e trabalhar com objetos

Range.

Referir-se a células e intervalos usando a notação A1

Você pode referir-se a uma célula ou intervalo de células no estilo de referência A1 usando o

método Range. O procedimento Sub abaixo altera o formato das células A1:D5 para negrito.

Sub FormatRange()

Workbooks("Pasta1").Sheets("Plan1").Range("A1:D5") _

.Font.Bold = True

End Sub

A tabela seguinte ilustra algumas referências em estilo A1 usando o método Range.

Referência Significado

Range("A1") Célula A1

Range("A1:B5") Células de A1 até B5

Range("C5:D9,G9:H16") Uma seleção de várias áreas

Range("A:A") Coluna A

Range("1:1") Linha 1

Range("A:C") Colunas de A até C

Range("1:5") Linhas 1 até 5

Range("1:1,3:3,8:8") Linhas 1, 3 e 8

Range("A:A,C:C,F:F") Colunas A, C e F



Referir-se a células usando números de índice

Você pode usar a propriedade Cells para referir-se a uma única célula usando números de

índice de linha e coluna. Essa propriedade retorna um objeto Range representando uma única

célula. No exemplo seguinte, Cells(6,1) retorna a célula A6 de Plan1. Em seguida, a

propriedade Value é definida como 10.

Sub EnterValue()

Worksheets("Sheet1").Cells(6, 1).Value = 10

End Sub

A propriedade Cells funciona bem para loop através de um intervalo de células porque você

pode substituir os números de índice por variáveis, conforme mostrado no exemplo seguinte.

Sub CycleThrough()

Dim counter As Integer

For counter = 1 To 20

Worksheets("Plan1").Cells(counter, 3).Value = counter

Next counter

End Sub

Observação Se você desejar alterar as propriedades ou aplicar um método a todo um intervalo

de células de uma só vez, use a propriedade Range.

Referir-se a linhas e colunas

Use a propriedade Rows ou a propriedade Columns para trabalhar com linhas ou colunas

inteiras. Essas propriedades retornam um objeto Range representando um intervalo de células.

No exemplo seguinte, Rows(1) retorna a linha um em Plan1. Em seguida, a propriedade Bold

do objeto Font do intervalo é definida como True.

Sub RowBold()

Worksheets("Plan1").Rows(1).Font.Bold = True

End Sub

A tabela seguinte ilustra algumas referências de linha e coluna usando as propriedades Rows

e Columns.



Referência Significado

Rows(1) Linha um

Rows Todas as linhas da planilha

Columns(1) Coluna um

Columns("A") Coluna um

Columns Todas as colunas da planilha

Para trabalhar com várias linhas ou colunas ao mesmo tempo, crie uma variável de objeto e

use o método Union, combinando várias chamadas à propriedade Rows ou Columns. O

exemplo seguinte altera para negrito o formato das linhas um, três e cinco na planilha um da

pasta de trabalho ativa.

Sub SeveralRows()


Dim myUnion As Range

Set myUnion = Union(Rows(1), Rows(3), Rows(5))

myUnion.Font.Bold = True

End Sub

Referir-se a células usando notação de atalho

Você pode usar o estilo de referência A1 ou um intervalo nomeado entre colchetes com um

atalho para a propriedade Range. Você não precisa digitar a palavra "Range" nem usar aspas,

conforme mostrado nos exemplos seguintes.

Sub ClearRange()

Worksheets("Plan1").[A1:B5].ClearContents

End Sub

__________________________________

Sub SetValue()

[MyRange].Value = 30

End Sub



Referir-se a intervalos nomeados

Os intervalos são mais fáceis de identificar por nome do que por notação A1. Para nomear um

intervalo selecionado, clique na caixa de nome na extremidade esquerda da barra de fórmulas,

digite um nome e, em seguida, pressione ENTER.

Referir-se a um intervalo nomeado

O exemplo seguinte refere-se ao intervalo chamado "MyRange" na pasta de trabalho chamada

"MyBook.xls".

Sub FormatRange()

Range("MyBook.xls!MyRange").Font.Italic = True

End Sub

Para selecionar um intervalo nomeado, use o método GoTo, o qual ativa a pasta de trabalho e

a planilha e, em seguida, seleciona o intervalo.

Sub ClearRange()

Application.Goto Reference:="MyBook.xls!MyRange"

Selection.ClearContents

End Sub

O exemplo seguinte mostra como o mesmo procedimento seria escrito para a pasta de

trabalho ativa.

Sub ClearRange()

Application.Goto Reference:="MyRange"

Selection.ClearContents

End Sub

Referir-se a células relativas a outras células

Uma maneira comum de trabalhar com uma célula em relação a uma outra célula é usar a

propriedade Offset. No exemplo seguinte, o conteúdo da célula que se encontra uma linha

abaixo e a três colunas da célula ativa na planilha ativa é formatado com sublinhado duplo.

Sub Underline()

ActiveCell.Offset(1, 3).Font.Underline = xlDouble

End Sub



Observação. Você pode gravar macros que usem a propriedade Offset em vez de referências

absolutas. No menu Ferramentas, aponte para Gravar macro e, em seguida, clique em Usar

referências relativas.

Referir-se a células usando um objeto Range

Quando você define uma variável de objeto para um objeto Range, você pode facilmente

manipular o intervalo usando o nome da variável.

O procedimento seguinte cria a variável de objeto myRange e, em seguida, atribui a variável

ao intervalo A1:D5 de Plan1 na pasta de trabalho ativa. Instruções subseqüentes modificam

propriedades do intervalo substituindo o objeto de intervalo pelo nome da variável.

Sub Random()

Dim myRange As Range

Set myRange = Worksheets("Plan1").Range("A1:D5")

myRange.Formula = "=RAND()"

myRange.Font.Bold = True

End Sub

Referir-se a todas as células da planilha

Quando você aplica a propriedade Cells a uma planilha sem especificar um número de índice,

o método retorna um objeto Range representando todas as células da planilha. O

procedimento Sub seguinte limpa o conteúdo de todas as células de Plan1 na planilha ativa.

Sub ClearSheet()

Worksheets("Plan1").Cells.ClearContents

End Sub

Referir-se a vários intervalos

Usando o método apropriado, você pode facilmente referir-se a vários intervalos. Use os

métodos Range e Union para referir-se a qualquer grupo de intervalos; use a propriedade

Areas para referir-se ao grupo de intervalos selecionado em uma planilha.

Usar a propriedade Range

Você pode referir-se a vários intervalos com a propriedade Range colocando vírgulas entre

duas ou mais referências. O exemplo seguinte limpa o conteúdo dos três intervalos de Sheet1.

Sub ClearRanges()



Worksheets("Sheet1").Range("C5:D9,G9:H16,B14:D18").ClearContents

End Sub

Os intervalos nomeados tornam o uso da propriedade Range mais fácil ao trabalhar com

vários intervalos. O exemplo seguinte funciona quando todos os três intervalos nomeados se

encontram na mesma planilha.

Sub ClearNamed()

Range("MyRange, YourRange, HisRange").ClearContents

End Sub

Usar o método Union

Você pode combinar vários intervalos em um objeto Range usando o método Union. O

exemplo seguinte cria um objeto Range chamado myMultipleRange, define-o com os

intervalos A1:B2 e C3:D4 e, em seguida, formata com negrito os intervalos combinados.

Sub MultipleRange()

Dim r1, r2, myMultipleRange As Range

Set r1 = Sheets("Sheet1").Range("A1:B2")

Set r2 = Sheets("Sheet1").Range("C3:D4")

Set myMultipleRange = Union(r1, r2)

myMultipleRange.Font.Bold = True

End Sub

Loop em um intervalo de células

Ao usar o Visual Basic, você freqüentemente precisa executar o mesmo bloco de instruções

em cada célula de um intervalo de células. Para fazer isso, você combina uma instrução de

loop com um ou mais métodos para identificar cada célula, uma de cada vez, e executa a

operação.

Estrutura For Each-Next

A estrutura For Each-Next permite executar uma determinada instrução em todos os

elementos de uma coleção de objetos, como por exemplo o conjunto de planilhas de um

arquivo, o conjunto de células de uma planilha ou um intervalo de células de uma Range. Sua

sintaxe é apresentada abaixo.

For Each < elemento > In < grupo >



< Instruções >

Next

Por exemplo, o procedimento a seguir formata o tamanho da fonte de todas as células de uma

Range nomeada “Faixa”.

Public Sub closefiles()

For Each celula In Range("Faixa").Cells

celula.Font.Size = 14

Next

End Sub

Se você não souber os limites do intervalo pelo qual deseja fazer o loop, você pode usar a

propriedade CurrentRegion para retornar o intervalo que envolve a célula ativa. Por exemplo,

o procedimento seguinte, quando executado de uma planilha, faz um loop através do

intervalo que envolve a célula ativa, definindo como 0 (zero) qualquer número cujo valor

absoluto seja menor que 0,01.

Sub RoundToZero3()

For Each c In ActiveCell.CurrentRegion.Cells

If Abs(c.Value) < 0.01 Then c.Value = 0

Next

End Sub

Saindo de um loop For Each-Next antes de seu término

Você pode sair de um loop For Each-Next utilizando a instrução Exit For. Por exemplo,

quando ocorrer um erro, utilize a instrução Exit For no bloco de instruções True de uma

instrução If...Then...Else ou uma instrução Select Case que verifica especificamente o erro.

Caso não ocorra o erro, a instrução If…Then…Else será False e a execução do loop continuará

conforme esperado.

O exemplo a seguir testa a primeira célula preenchida do intervalo A1:B5 que não contenha

um número. Se for encontrada uma célula desse tipo, será exibida uma mensagem e Exit For

sairá do loop.

Sub TestForNumbers()

For Each celul In Range("A1:B5")

If IsNumeric(celul.Value) = False Then

MsgBox "Objeto contém um valor não-numérico."

Exit For



End If

Next celul

End Sub

Uma outra maneira de fazer loop através de um intervalo é usar o loop For...Next com a

propriedade Cells. Usando a propriedade Cells, você pode substituir o contador do loop (ou

outras variáveis ou expressões) pelos números de índice das células. No exemplo seguinte, a

variável counter é substituída pelo índice de linha. O procedimento faz um loop através de um

intervalo C1:C20, definindo como 0 (zero) qualquer número cujo valor absoluto seja menor

que 0,01.

Sub RoundToZero1()

For counter = 1 To 20

Set curCell = Worksheets("Sheet1").Cells(counter, 3)

If Abs(curCell.Value) < 0.01 Then curCell.Value = 0

Next counter

End Sub

Instrução With

A instrução With executa uma série de instruções em um objeto. Sua sintaxe é:

With objeto

< instruções >

End With

As instruções With fazem os seus procedimentos serem executados com mais rapidez e

ajudam a evitar digitação repetitiva.

O exemplo a seguir preenche um intervalo de células com o número 30, aplica a formatação

negrito e define como amarelo a cor interna das células.

Sub FormatRange()

With Worksheets("Planilha1").Range("A1:C10")

.Value = 30

.Font.Bold = True

.Interior.Color = RGB(255, 255, 0)

End With

End Sub



Para melhor eficiência, você pode aninhar as instruções With . O exemplo a seguir insere uma

fórmula na célula A1 e, em seguida, formata a fonte.

Sub MyInput()

With Workbooks("Livro1").Worksheets("Planilha1").Cells(1, 1)

.Formula = "=SQRT(50)"

With .Font

.Name = "Arial"

.Bold = True

.Size = 8

End With

End With

End Sub

Propriedade Formula e Propriedade FormulaR1C1

Tratam-se de propriedades que permitem a inserção de fórmulas em células ou conjuntos de

células. A primeira define a fórmula em notação de estilo A1. A segunda define a fórmula

usando notação em estilo L1C1 (R1C1), utilizada pelo gravador de macro.

Exemplos:

Worksheets("Sheet1").Range("A1").Formula = "=$A$4+$A$10" equivale a…

Worksheets("Sheet1").Range("A1").FormulaR1C1 = "=R4C1+R10C1"

Worksheets("Sheet1").Range("B1").FormulaR1C1 = "=SQRT(R1C1)" equivale a…

Worksheets("Sheet1").Range("B1").Formula = "=SQRT(A1)"

As propriedades Formula e FormulaR1C1 são intercambiáveis quando se trata da inserção de

constantes ou textos em células.

Método Find

Localiza informações específicas em um intervalo, e retorna um objeto Range representando a

primeira célula onde essas informações se encontram. Retorna Nothing se nenhuma

coincidência for encontrada. Não afeta a seleção da célula ativa.

Sintaxe resumida:

expressão.Find(What)



expressão Obrigatória. Uma expressão que retorne um objeto Range.

What Variant obrigatória. Os dados pelos quais procurar. Pode ser uma cadeia de caracteres

ou qualquer tipo de dados do Microsoft Excel.

Método FindNext

Continua uma pesquisa que tenha começado com o método Find.

Sintaxe

expressão.FindNext(After)


After Variant opcional. A célula depois da qual você deseja procurar. Corresponde à posição

da célula ativa quando uma pesquisa é feita a partir da interface do usuário. Observe que After

precisa ser uma única célula no intervalo. Lembre-se de que a pesquisa começa depois desta

célula; a célula especificada não é pesquisada até que o método dê a volta e chegue à ela. Se

esse argumento não for especificado, a pesquisa começará após a célula do canto superior

esquerdo do intervalo.

O exemplo abaixo localiza todas as células do intervalo A1:A500 que contêm o valor 2 e

torna essas células cinzas.

With Worksheets(1).Range("a1:a500")

Set c = .Find(2, lookin:=xlValues)

If Not c Is Nothing Then

firstAddress = c.Address

Do

c.Interior.Pattern = xlPatternGray50

Set c = .FindNext(c)

Loop While Not c Is Nothing And c.Address <> firstAddress

End If

End With

Observação:

Para encontrar células coincidentes com padrões mais complicados, use uma instrução For

Each...Next com o operador Like. Por exemplo, o código seguinte pesquisa todas as células

do intervalo A1:C5 que usam uma fonte cujo nome começa com as letras "Cour". Quando o

Microsoft Excel encontra uma coincidência, ele altera a fonte para Times New Roman.

For Each c In [A1:C5]



If c.Font.Name Like "Cour*" Then

c.Font.Name = "Times New Roman"

End If

Next

Método Replace

Encontra e substitui caracteres em células dentro do intervalo especificado. O uso desse

método não altera a seleção nem a célula ativa.

Sintaxe resumida:

expressão.Replace(What, Replacement)


What String obrigatória. A cadeia de caracteres que você quer que o Microsoft Excel

procure.

Replacement String obrigatória. A cadeia de caracteres de substituição.

O exemplo abaixo substitui todas as ocorrências da função SIN trigonométrico pela função

COS. O intervalo de substituição é a coluna A de Plan1.

Worksheets("Plan1").Columns("A").Replace _

What:="SIN", Replacement:="COS", _

SearchOrder:=xlByColumns, MatchCase:=True



Escopo ou domínio de variáveis, constantes e procedimentos O escopo ou domínio diz respeito à disponibilidade de uma variável, constante ou

procedimento para ser utilizado por outro procedimento. Há três níveis de escopo: nível de

procedimento, nível de módulo privado e nível de módulo público.

Escopo das variáveis

Definindo o escopo em nível de procedimento

Uma variável ou constante definida dentro de um procedimento não é visível fora desse

procedimento. Somente o procedimento que contém a declaração da variável pode utilizá-la.

No exemplo a seguir, o primeiro procedimento exibe uma caixa de mensagem que contém

uma seqüência de caracteres. O segundo procedimento exibe uma caixa de mensagem em

branco, pois, para o primeiro procedimento, a variável strMsg é local.

Sub LocalVariable()

Dim strMsg As String

strMsg = "Esta variável não pode ser utilizada fora deste procedimento."

MsgBox strMsg

End Sub

_______________________________________________

Sub OutsideScope()

MsgBox strMsg

End Sub

Definindo o escopo em nível de módulo privado

Você pode definir variáveis (e constantes) em nível de módulo na seção Declarações de um

módulo. Em nível de módulo, elas podem ser públicas ou privadas. Quando declaradas

públicas, estão disponíveis para todos os procedimentos em todos os módulos de um projeto;

quando privadas, estão disponíveis somente para os procedimentos naquele módulo. Como

padrão, o escopo das variáveis declaradas com a instrução Dim ( e o das constantes

declaradas por Const) na seção Declarações é privado. No entanto, quando se precede a

variável com a palavra-chave Private, ou a constante com a expressão PrivateConst, o

escopo no seu código se torna óbvio.



No exemplo a seguir, a variável de seqüência de caracteres strMsg está disponível para os

procedimentos definidos no módulo. Quando o segundo procedimento for chamado, ele

exibirá o conteúdo da variável de seqüência de caracteres strMsg em uma caixa de diálogo.

' Adiciona a seguinte seção Declarações do módulo.

Private strMsg As String

_______________________________________

Sub InitializePrivateVariable()

strMsg = "Esta variável não pode ser utilizada fora deste módulo."

End Sub

________________________________________

Sub InitializePrivateVariable()

MsgBox strMsg

End Sub

Definindo o escopo em nível de módulo público

Caso você declare uma variável (ou uma constante) em nível de módulo como pública, ela

estará disponível para todos os procedimentos no projeto. No exemplo a seguir, a variável de

seqüência de caracteres strMsg pode ser utilizada por qualquer procedimento em qualquer

módulo do projeto.

' Insere a seção Declarações do módulo.

Public strMsg As String

O exemplo a seguir declara a constante Public conIdade como um Integer e atribui a ela o

valor 34.

Public Const conIdade As Integer = 34

Escopo ou domínio dos procedimentos (Sub-rotinas, Funções e Eventos)

Sub-rotinas e Funções só têm dois níveis de escopo: o nível do projeto e o nível do módulo.

Por padrão as rotinas são de âmbito do projeto. Contudo este comportamento fica mais

explícito pela introdução da palavra-chave Public, opcional, que em termos operacionais não

adicionará qualquer funcionalidade, mas em termos de leitura dará uma maior percepção. A

palavra-chave Private, opcional, indica que a rotina é acessível somente a outros

procedimentos no módulo em que é declarado. Assim, para que uma rotina tenha o âmbito do

módulo onde está definida, deverá ser antecedida pela palavra-chave Private.



Enquanto os procedimentos Função e Sub-rotina são públicos por padrão, os procedimentos

de Evento são de domínio privado. Quando o Visual Basic cria um procedimento de evento, a

palavra-chave Private é automaticamente inserida antes da declaração do procedimento.



Exercícios Lista 1 de Exercícios

Usando o gravador de macros e editando macros

1. Gravando a primeira macro, com referências absolutas (pág. 3)

2. Gravando com referências relativas (pág. 3)

3. Mudar o atalho da macro, seguindo os passos Ferramentas ; Macro; Macros; Opcoes.

4. Editando a macro de referências absolutas: no Editor do VBA, alterar os endereços de células que aparecem na primeira macro gravada. Por exemplo, substituir Range(“A2”) por Range(“D2”) e executar a macro. Substituir a propriedade FormulaR1C1 por Formula. Executar a macro. Substituir: ActiveCell.FormulaR1C1 = “UERJ” por: ActiveCell.Value = “UERJ”

5. Editando a macro de referências relativas: eliminar Range(“A1”) das linhas de comando. Executar. Simplificar as linhas de código abaixo: ActiveCell.Offset(1,0).Select ActiveCell.FormulaR1C1 = “UERJ” substituindo por uma única linha:

ActiveCell.Offset(1,0).FormulaR1C1 = “UERJ”

6. Criar botões de macro (pág. 5) e nomeá-los.

7. Executar as macros de diferentes modos: Pela planilha Pelo Editor VBA

8. Executar as macros no Editor VB, passo a passo. Colocar o cursor em qualquer linha da macro desejada. Teclar F8 passo a passo e verificar o efeito na planilha.

Programando com eventos

1. Eventos. Evento ao abrir pasta. Direcionando usuário para uma planilha.

Abra uma pasta vazia. No Editor VBA, selecione no Project Explorer “EstaPasta_de_trabalho”. Na janela de código, selecione “Workbook” no menu suspenso à esquerda. No menu à direita selecione “Open”.

Digite as seguintes linhas de código:

MsgBox "Os dados são lançados na Plan3"


Salve a pasta, feche-a e a reabra. Verifique o aparecimento da caixa de mensagem e a ativação da Planilha 3.

2. Eventos. Auto-ajuste das colunas.

Criar uma macro que auto-ajuste todas as colunas (Columns) sempre que a Planilha1 for alterada (evento Change). Para tal, selecione Plan1 no Project Explorer. Nos menus suspensos selecione Worksheet (à esquerda) e Change (à direita). Digite a linha de código:

Columns.AutoFit

Vá à Planilha 1 e digite uma letra qualquer numa das células. Verifique.



Lista 2 de Exercícios

Trabalhando com variáveis e inserindo formulas

1. Função de planilha SUM. Abra uma pasta vazia. Registre valores numéricos quaisquer nas células de A2 até A5. Crie uma macro que calcule a soma destes valores (utilize a função SUM) e registre o resultado na célula A1 (utilize a propriedade Formula). Verifique como a função aparece na barra de fórmulas na planilha.

2. Propriedade End(xlDown). A coluna A da planilha Plan1 possui valores de vendas cujos registros iniciam na linha 2 e cuja linha final é indeterminada. Grave uma macro (de nome “Vendas”) que desça pela coluna até o último valor de vendas. Utilize as teclas End + seta para baixo. Verifique a macro gravada no Editor VB. Observação: End(xlDown) falha quando só há uma linha preenchida na coluna (verifique!). O comando End(xlUp) é mais confiável.

3. Propriedade Row. Na macro gravada na etapa anterior, substitua a propriedade Select por Row. Substitua o objeto Selection por Range(“A1”). Armazene o resultado em uma variável (UltimaLinha). Execute a macro passo a passo teclando F8. Passe o cursor sobre o nome da variável e observe o registro do número da última linha.

4. Na macro anterior, declare a variável (comando Dim) como Integer. Acima da macro, escreva o comando Option Explicit .

5. Na mesma macro, registre na célula B1 a soma dos valores de vendas da coluna A, inserindo a variável UltimaLinha na função SUM. Observe a planilha e a barra de fórmulas.

Observar a sintaxe abaixo e a utilização do operador “&”.

Range("B1").Formula = "=sum(A1:A" & UltimaLinha & " )"

6. Inclua novos valores de vendas na coluna A e observe que a função Soma não é atualizada.

7. Utilizando macro de evento. Crie uma macro de evento (Worksheet_SelectionChange) que execute a macro “Vendas” sempre que a planilha Plan1 for modificada. Inclua novos valores de vendas na coluna A e observe novamente.

8. Alternativamente, registre a soma na coluna B, porém na última linha preenchida. Neste caso, a sintaxe será: Columns(2).ClearContents (limpeza prévia da coluna B recomendável) Range("B" & UltimaLinha).Formula = "=sum(A1:A" & Ul timaLinha & ")"


1. Importando dados. Ler o exercício “Importando dados e limpando o código gravado” da apostila e executar a etapa “Preparando para gravar a macro”. Utilizar o arquivo Fatura.txt.

2. Corrija os possíveis erros presentes no código. Simplifique-o, eliminando o que for desnecessário.

3. Modifique a macro, de modo que a tabela importada via Fatura.txt seja recortada e colada na Plan2 da própria pasta que contém a macro. A pasta Fatura.txt criada pela importação pode ser fechada.




Transferindo dados entre pastas e planilhas

1. Criar 3 novos arquivos (Arq1, Arq2 e Arq3) no diretório C. Fechar Arq1 e Arq2. Gravar uma macro em Arq3 que: abra Arq1 e Arq2; copie a célula A2 de Plan2 de Arq1 para a célula A2 de Plan2 de Arq2; salve e feche Arq1; idem, para Arq2.

2. Edite a macro gravada, de modo a copiar e colar em uma única linha de comando, usando o método Copy e o argumento Destination.

3. Repita o exercício anterior, usando o método Cut

Trabalhando com Localizar e Substituir no ambiente VBA

Localizar: 1. Abrir uma pasta vazia. Digitar a palavra “inicial” em algumas células aleatórias da planilha. 2. Gravar uma macro que localize, uma a uma na planilha, cada célula que contiver a palavra “inicial”. Use a sequência Editar / Localizar. 3. Após a gravação, observar os comandos gerados pela macro gravada ( ir ao ambiente VBA). Observar o comando Cells. 4. Editar a macro gerada pelo gravador, eliminado todos os argumentos do método Find e do método FindNext, exceto os argumentos “What” e “After”. 5. Executar a macro editada e observar seu funcionamento. Substituir: 1. Gravar uma macro que substitua a palavra “inicial” por “inicio”. Use a sequência Editar / Substituir. 2. Após a gravação, observar os comandos gerados pela macro gravada ( ir ao ambiente VBA) 3. Editar a macro gerada pelo gravador, eliminado todos os argumentos do método Replace, exceto os argumentos “What” e “Replacement”. 4. Executar a macro editada e observar seu funcionamento.

Pesquisando o uso do comando Cells

Aproveite as macros geradas no exercício com Localizar e Substituir. Coloque o cursor na palavra Cells e tecle F1. Observe o arquivo Ajuda que se abre e clique no link Exemplos. Avalie os exemplos apresentados para o comando Cells.

Aplicando o comando Cells

1. Abrir uma pasta vazia. Digitar em uma célula a palavra “inicial”. 2. Escrever uma macro que localize essa palavra e registre sua localização. Para tal, crie uma variável para registrar o número da linha, e outra para o número da coluna. 3. Opcionalmente, use a MsgBox para informar ao usuário os números de linha e coluna.



Usando o método Find

Uma coluna de dados, de tamanho indefinido, tem em alguma célula da coluna imediatamente à esquerda a palavra “inicial”. Um pouco abaixo, tem-se a palavra “final”. Vide abaixo.

R$ 15.900,00 R$ 14.700,00

inicial R$ 15.900,00 R$ 14.700,00 R$ 22.950,00 R$ 22.950,00 R$ 13.500,00

final R$ 13.500,00 Média: ? R$ 13.500,00 R$ 14.100,00 R$ 14.100,00

Pede-se elaborar uma macro que localize a Range delimitada por estas palavras e registre esta Range em uma variável-objeto. A macro deve também destacar a Range, alterando sua cor de fundo, e deve calcular a sua média, registrando o resultado conforme figura.

Localiza tabela de dados. Uso de For each...Next. Uso de CurrentRegion.

Uma planilha contém uma única tabela de dados, em uma localização indefinida. Crie uma macro que localize qualquer célula da tabela e que armazene a tabela em uma variável-objeto de mesmo nome. Esta tabela pode, por exemplo, ser copiada e colada em outra planilha, ou pode simplesmente ter sua cor de fundo modificada para maior destaque. Obs.: 1. Para varrer as células da planilha, utilize a estrutura For Each...Next, conforme abaixo. For Each celula In Cells

......

...... Next celula 2. Para testar se uma célula está preenchida, utilize a estrutura If...End If , conforme abaixo. If celula.Value <> "" Then Set Tabela = celula.CurrentRegion Exit For End If

3. Quando for encontrada uma célula vazia, deve-se sair da estrutura For Each...Next. Para tal, utilizamos Exit For .




Criando uma funcao

Função rateio. Uma tabela registra em uma coluna os centros de custo de uma pequena empresa e na coluna vizinha o rateio das despesas indiretas da empresa pelos centros de custo. Vide abaixo. Centro de Custos Despesas Indiretas (R$) % das Desp. Ind.

Almoxarifado R$ 10.500 ? (%) Laboratório R$ 12.450 Montagem R$ 14.600

Manutenção R$ 21.000 Expedição R$ 5.500

Crie uma função “rateio” que calcule o percentual de responsabilidade de um dado centro de custo. A função deverá ter dois argumentos a serem definidos pelo usuário: a Range contendo a lista de despesas indiretas e a Range contendo a despesa indireta do centro de custo. Função sumbycolor. Uma tabela possui um conjunto de dados em células com diferentes cores de fundo, vide abaixo. Crie uma função que totalize os valores associados a uma dada cor, definida pelo usuário. A função terá dois argumentos: a Range com os dados e a Range de uma célula qualquer, contendo a cor definida pelo usuário.

4,3 região região região 5,0 amarela branca vermelha 7,0 ? ? ? 9,0 1,0 3,0 4,0 6,0 5,0 2,0

Obs.: antes de criar a função, utilize o gravador de macro para absorver os comandos associados à definição de cor de fundo. Por exemplo, grave uma macro enquanto modifica a cor de fundo de uma célula qualquer. Depois, vá ao VBE e estude as linhas de código. Função mediaponderada. Uma tabela registra os dados de vendas de uma empresa, com os respectivos prazos de recebimento, vide abaixo.

Data Venda (R$) Cliente Prazo (dias) 1-fev 10.000 Yucatan 25 Prazo médio: 1-fev 8.000 Palermo 40 ? dias 2-fev 9.500 Kashmir 40 2-fev 12.000 Rover 35 2-fev 5.000 Raimundos 35 3-fev 7.000 Annie Frank 15 4-fev 16.000 Hammerklavier 50



Crie uma função que calcule o prazo médio de recebimento das vendas. A função só precisará que o usuário selecione duas Ranges: a de Valores (ou Vendas) e a de Pesos (ou Prazos). Observações: 1. O total de vendas (podemos chamar de Soma_dos_Valores) será simplesmente a aplicação da função de planilha SUM sobre a Range Valores. Assim: soma_valores = Application.WorksheetFunction.Sum(valores). 2. Para o produto de cada Valor por seu Peso precisamos fazer com que a linha 1 da Range Valores seja multiplicada pela linha 1 da Range Pesos, e assim por diante. E os produtos devem ser totalizados em uma variável, que podemos nomear, p. ex., por "valor_vezes_peso". 3. As linhas das duas Ranges podem crescer desde o valor 1 até o total de linhas, de qualquer uma das Ranges. Podemos usar a estrutura For…Next para controlar este salto de linha para linha. Assim: For linha = 1 To valores.Rows.Count (Aqui temos que totalizar os produtos) Next linha Função Payback. Usando a estrutura For each...Next. Uma tabela contém as projeções de fluxo de caixa de um investimento, em bases anuais, mensais, ...etc. O primeiro fluxo de caixa tem sinal negativo, por ser o de investimento, e os demais são positivos, vide abaixo. Fluxos de caixa: (1.000) 240 240 240 240 240 240 Crie uma função que calcule o Payback do investimento. Obs.: Payback é o prazo para que o investimento seja recuperado. É o instante em que o somatório dos fluxos de caixa deixa de ser negativo.


Usando Caixa de Mensagem e Caixa de Entrada

1. Criar uma macro que por meio de InputBox pergunte ao usuário sua idade (não necessariamente sob a forma de numero) e que armazene a resposta numa variável chamada idade. Esta variável deve ter dimensão string. 2. Na macro anterior, se o usuário retornar False (botão Cancelar), uma MsgBox devera perguntar se ele quer omitir sua idade. Esta MsgBox devera ter os botões Sim e Não. Se ele clicar Sim, a macro e concluída. Se ele clicar Não, a macro retorna ao InputBox.



Importando dados e limpando o código gravado Importando Dados de Fatura - Exercício

Fonte: Macros eVBA para Microsoft Excel (“Mr. Excel”& Syrstad), Ed. Campus

Vamos supor que você esteja trabalhando em um departamento de contabilidade. Todos os

dias, você recebe um arquivo texto do sistema da empresa mostrando todas as faturas

produzidas no dia anterior. Esse arquivo texto tem seus campos separados por vírgulas. Ao

chegar ao trabalho pela manhã, você importa manualmente este arquivo para o Excel.

Adiciona uma linha de totais aos dados, coloca seus títulos em negrito e imprime o relatório

para ser distribuído para alguns gerentes.

Os dados de 5 de junho tem 12 faturas, conforme abaixo. A primeira linha apresenta os títulos

das colunas.

DataFatura,NumFatura,NumRepVendas,NumCliente,ReceitaProduto,ReceitaServiço,CustoPr

oduto

5/6/2004,123801,s82,c8754,639600,12000,325438

5/6/2004,123802,s93,c7874,964600,0,435587

5/6/2004,123803,s43,c4844,988900,0,587630

5/6/2004,123804,s54,c4940,673800,15000,346164

5/6/2004,123805,s43,c7969,513500,0,233842

5/6/2004,123806,s93,c8468,760600,0,355305

5/6/2004,123807,s82,c1620,894100,0,457577

5/6/2004,123808,s17,c3238,316200,45000,161877

5/6/2004,123809,s32,c5214,111500,0,62956

5/6/2004,123810,s45,c3717,747600,0,444162

5/6/2004,123811,s87,c7492,857400,0,410493

5/6/2004,123812,s43,c7780,200700,0,97937

Copie estes dados para um arquivo do programa “Bloco de notas” e salve com o nome

Fatura.txt.



Preparando para gravar a macro:

A importação do arquivo e a formatação da tabela são tarefas perfeitas para uma macro. Abra

uma pasta de trabalho em branco e salve-a com o nome MacroParaImportarFaturas.xls.

Clique no botão Gravar macro ou selecione Ferramentas, Macro, Gravar nova macro.

Na caixa de diálogo Gravar macro, modifique o nome da macro para ImportarFaturas.

Verifique se as macros serão armazenadas em Esta Pasta de Trabalho. Digite a letra i no

campo Tecla de atalho. Clique em OK e siga as etapas abaixo.

1. No menu, selecionar Arquivo, Abrir

2. Navegar até a pasta apropriada

3. Escolher Todos os arquivos (*.*) na lista suspensa Arquivos do tipo:

4. Selecionar Fatura.txt

5. Clicar em Abrir

6. No Assistente de importação de texto – Etapa 1 de 3, selecionar Delimitado na seção

Tipo de dados originais

7. Clicar em Avançar

8. No Assistente de importação de texto – Etapa 2 de 3, desmarcar a tecla Tab e marcar

Vírgula na seção Delimitadores

9. Clicar em Avançar

10. No Assistente de importação de texto – Etapa 3 de 3, selecionar Geral na seção

Formato dos dados da coluna e alterá-lo para Data:DMA

11. Clicar em Concluir para importar o arquivo

12. Pressionar a tecla End seguida da seta para baixo (End+Down) para mover o cursor

para a última linha de dados

13. Pressionar a seta para baixo mais uma vez para mover para a linha de totais

14. Digitar a palavra Totais

15. Pressionar a tecla seta para direita quatro vezes para mover para a coluna E da linha de

totais

16. Clicar no botão AutoSoma e pressionar Ctrl+Enter para adicionar um total na coluna

ReceitaProduto enquanto permanece nessa célula

17. Clicar na alça de autopreenchimento e arrastar da coluna E até a coluna G para copiar

a fórmula de totais para as colunas F e G

18. Realçar a linha Total (ou usar Shift + Barra de espaço) e clicar no ícone Negrito para

definir os totais em negrito

19. Realçar a linha 1 e clicar no ícone Negrito para definir os títulos em negrito

20. Pressionar Ctrl+T para selecionar todas as células



21. No menu, selecionar Formatar, Coluna, AutoAjuste da seleção

22. Clicar no botão Parar gravação ou selecionar Ferramentas, Macro, Parar gravação.

Execute a macro, clicando Ctrl+i ou selecionando Ferramentas, Macro, Macros e clicando

Executar, tendo selecionada a macro ImportarFaturas. Depois, observe as linhas de código da

macro no Editor do Visual Basic.

Executar a macro em outro dia produz resultados indesejados:

No dia seguinte, o arquivo Fatura.txt tem novas linhas adicionais, apresentadas abaixo.

6/6/2004,123813,s07,c4913,750000,0,340000

6/6/2004,123814,s55,c7181,37900,0,19811

6/6/2004,123815,s43,c7570,582700,0,292000

Execute a macro ImportarFaturas novamente e avalie os resultados.

Corrigindo e limpando o código:

Corrija os possíveis erros presentes no código. Simplifique-o, eliminando o que for

desnecessário. Siga os passos logo abaixo recomendados pelo Mr. Excel.

Cinco dicas para limpar o código gravado (Mr. Excel)

1. Não selecione nada

O código do gravador de macro antes de ser aperfeiçoado:

Rows(“1:1”).Select

Selection.Font.Bold = True

Após a melhoria:

Rows(“1:1”).Font.Bold = True

2. Percorra o intervalo de baixo para cima para encontrar a última linha

Não podemos garantir que todas células da planilha estarão preenchidas. Isso é um problema

quando usamos End + Down. Essa combinação de teclas o leva à última linha com dados no

intervalo atual, não necessariamente à última preenchida. A melhor solução é começar na

parte inferior da planilha do Excel e pressionar End +Up. Como o Excel tem 65.536 linhas,

habitue-se a usar este código para encontrar pelo VBA do Excel a última linha:

Range(“A65536”).End(xlUp).Select .... ou .....

Cells(Cells.Rows.Count, 1).End(xlUp).Select



3. Use variáveis

O gravador de macros nunca grava uma variável. Elas são muito fáceis de usar. Por exemplo,

defina a variável FinalRow como a última linha de dados:

FinalRow = Range (“A65536”).End(xlUp).Row …. Ou ….

FinalRow = Cells(Cells.Rows.Count, 1).End(xlUp).Row

Assim, colocar a palavra Total na próxima linha fica...

Range(“A” & FinalRow + 1).Value = “Total”

4. Aprenda a copiar e colar em uma única instrução

O código gravado:

Range(“E14”).Select

Selection.Copy

Range(“F14”).Select

ActiveSheet.Paste

Código melhorado:

Range(“E14”).Copy Destination:=Range(“F14”)

5. Use With... End with se estiver executando múltiplas ações para a mesma célula ou

intervalo de células

O código gravado:

Range(“A14:F14”).Select

Selection.Font.Bold = True

Selection.Font.Size = 12

Selection.Font.ColorIndex = 5

Código melhorado:

With Range(“A14:F14”).Font

.Bold = True

.Size = 12

.ColorIndex = 5

End With



Exercício final: Fluxo de caixa em macro

Você trabalha no Departamento Financeiro de uma pequena empresa. A partir do próximo

ano, as projeções de caixa da empresa serão transferidas para uma pasta denominada Fluxo de

Caixa.xls. Este arquivo possui 12 planilhas nomeadas segundo as siglas dos 12 meses do ano.

Periodicamente você receberá um arquivo Fluxo.txt que conterá as projeções do mês dos

fluxos de caixa diários.

Você tem que criar uma rotina para importar o arquivo Fluxo.txt e destiná-lo à respectiva

planilha.

O arquivo Fluxo.txt tem na primeira linha a sigla que caracteriza o mês de projeção. Além

disso, tem uma linha de rótulos de colunas. Logo abaixo, vêm as linhas com os seguintes

dados tabulados: Data, Receita (Rec), Fornecedores (Fornec), Pessoal, Impostos (Imp), Outras

Despesas (Outras), Despesas Bancárias (Banc), Empréstimos (Empr), Amortização (Amort),

Juros.

Esses dados devem ser reorganizados na planilha com as seguintes colunas ordenadas:

1. Dia 2. Saldo inicial * 3. Receita

4. Fornecedores 5. Pessoal 6. Impostos

7. Outras despesas 8. Despesas bancárias 9. Empréstimos

10. Amortização 11. Juros 12. Saldo do dia *

13. Saldo final *

* Colunas não trazidas do arquivo Fluxo.txt

As células contendo fluxos de caixa devem ser formatadas em R$ com ponto separador em

1.000 e com duas casas decimais. A fonte é Arial 10. A linha de cabeçalho deve ser formatada

em negrito, com fonte tamanho 11. A página deve ter orientação paisagem.

Sabe-se que:

Os fluxos são lançados somente para dias úteis, conforme dados no arquivo txt

O saldo inicial do dia corresponde ao saldo final do dia anterior

O saldo inicial do dia primeiro de janeiro é de R$ 12.345,00

O saldo final do dia é igual ao saldo inicial mais o saldo do dia.

O saldo do dia é a soma das entradas de caixa (receita e empréstimos) menos a soma das

saídas (todas as despesas e as amortizações de empréstimos).

A empresa tem linha de crédito de empréstimos bancários de curto prazo para cobrir

necessidades de caixa. Os empréstimos são depositados no 5o. dia útil do mês. Sua devolução

ao banco e o pagamento de juros ocorrem no 5o. dia útil do mês subsequente. Os juros



calculados em um mês devem ser armazenados para lançamento posterior como despesa no

mês subsequente. A taxa de juros é função do volume a ser captado perante o banco,

conforme tabela abaixo.

Empréstimo Taxa de juros

Até R$ 2.000 3% am

De R$ 2.000 a R$ 5.000 4% am

De R$ 5.000 a R$ 10.000 5% am

De R$ 10.000 a R$ 15.000 6% am

De R$ 15.000 a 30.000 7% am

A planilha deve calcular o empréstimo necessário, igualando-o à diferença entre o saldo

mínimo projetado do mês e o saldo mínimo desejado, se for negativa. O saldo mínimo

desejado deve ser obtido do usuário por meio de InputBox. Para que a planilha simule o

empréstimo, o usuário deve antes ser informado da taxa de juros e concordar com seu valor.

Caso contrário, a planilha conclui seu trabalho sem lançamento do empréstimo.

Pede-se:

1. Criar a pasta Fluxo de Caixa.xls com as 12 planilhas nomeadas

2. Planejar os passos necessários à construção da rotina

3. Criar e testar a rotina



Depuração no VBA

Depuração total

F8 executa cada linha do código

Alt + Tab muda para o Excel

Alt + Tab volta para o Editor Visual Basic

Identificado o problema na macro, parar a execução do código selecionando Redefinir (barra

de ferramentas de macro) ou Executar/Redefinir. Retornar ao Excel e desfazer o que a macro

fez, ou fechar o arquivo sem salvar.

Pontos de interrupção

Clicar na área da margem cinza à esquerda da linha de código que deseja interromper → linha

realçada em marrom

Executar a macro (F5) → o programa pára antes do ponto de interrupção → a linha do ponto

de interrupção fica realçada em amarelo. Executar a partir daí, se quiser, em etapas, teclando

F8.

Após depuração, remover os pontos de interrupção clicando no ponto marrom escuro da

margem ou selecionando Depurar/Limpar todos os pontos de interrupção

Movendo para frente e para trás

Usar o mouse para arrastar a linha amarela para cima ou para baixo, para qualquer lugar que

queira executar em seguida .. ou... clicar na linha que deseja pular e selecionar

Depurar/Definir próxima instrução

Não parar em cada linha de código

Para não parar em cada linha de código, uma opção é selecionar Depurar/Executar até o

cursor

Usando a janela Verificação imediata

Ctrl + G exibe a janela de Verificação imediata

Pesquisas possíveis:

A célula selecionada no momento → Print Selection.Address

O nome da célula ativa → Print ActiveCell.Name (Obs.: Não tem ponto após Print)

O valor da célula ativa → Print ActiveCell.Value



O endereço da célula ativa → Print ActiveCell.Address

A fórmula da célula ativa → Print ActiveCell.Formula

Podemos executar o código linha-a-linha teclando F8 e acompanhando na janela de

Verificação imediata. Para repetir a mesma consulta coloque o cursor no final da linha da

consulta anterior e tecle Enter. Os resultados das consultas anteriores vão sendo jogados para

baixo.

Consultando com o mouse

Passar o mouse sobre uma expressão do código. Esperar um segundo. Uma dica de ferramenta

aparece mostrando o valor atual da expressão → recurso valioso para loops

Consultando com uma janela de inspeção de variáveis

Substitui a consulta com o mouse ou a verificação imediata na pesquisa do valor de qualquer

expressão enquanto percorre o código.

Caminho: Depurar/Adicionar inspeção de variáveis → surge a caixa de diálogo → expressão

a digitar

Resultado: janela Inspeções de variáveis no canto inferior

Pressionando F8 o valor na janela vai sendo atualizado automaticamente

Usando uma expressão para definir um ponto de interrupção

Clicar com o botão direito no ícone dos óculos na janela “Inspeções de variáveis” → editar

inspeção de variáveis inspeção de variáveis → selecionar “interromper quando o valor for

alterado”

Pressionar então F5 para executar o código. A macro pára somente quando algo importante

(algo novo) acontece.

Documents

76151325 Program an Do Em Excel Com VBA 2010