Excel 2016 VBA - Módulo I

Principais objetos, propriedades e métodos 9 9 9 9

Objeto Range - Célula(s); Objeto Worksheet - Planilha(s); Objeto Workbook - Arquivo(s); Objeto Application - O próprio Excel.

Excel 2016 VBA - Módulo I

2.1. Introdução No Excel, temos milhares de objetos. Um dos poderes do VBA é manipular esses objetos através de suas propriedades (características) e métodos (ações). Porém, é de fundamental importância saber fazer referência, ou seja, indicar ao VBA com qual objeto você quer interagir. Aqui, vamos ver os principais objetos do VBA, bem como suas principais propriedades e métodos e as formas de referenciar estes objetos.

2.2. Objeto Range - Célula(s) A seguir, veremos as diferentes maneiras de fazer referência ao objeto Range, quais são suas principais propriedades e seus principais métodos, além da coleção Cells.

2.2.1. Fazendo referência Inúmeras aplicações em VBA Excel acessam e/ou modificam valores de células de uma planilha. As maneiras mais comuns para acessar uma célula são através do objeto Range e de diversas propriedades que retornam um objeto Range, como Cells, ActiveCell, CurrentRegion, Offset e End. Vamos fazer uma referência: 1. Feche todos arquivos; 2. Crie um arquivo novo; 3. Habilite para macro; 4. Salve como Objeto Range:

70

Principais objetos, propriedades e métodos

2

5. Insira um módulo; 6. Nomeie a propriedade Name deste módulo como Fazendo_Referência:

7. Nesse módulo, digite Sub F_01 e tecle ENTER:

Observe que os parênteses e o End Sub o VBE coloca automaticamente. 8. Insira o código:

9. Deixe o cursor posicionado dentro da macro e a execute com F5 ou F8.

Note que os dois pontos (:), assim como nas fórmulas do Excel, quer dizer "até".

71


2.2.1.1.Fazendo referência pelo número de índice Também podemos fazer referências utilizando o número de índice. Para fazer referência ao objeto Range pelo número de índice, usamos a propriedade Cells do objeto Application, que retorna um objeto Range. A forma padrão de usar Cells é informando o número da linha e o número da coluna da célula com a qual queremos interagir, separados por vírgula e entre parênteses.

Como veremos adiante, a propriedade NumberFormat = "$ #,##0.00" especifica o formato da moeda padrão local (conforme configuração do Windows).

2.2.1.2.Fazendo referência pelo nome da célula Também podemos referenciar um objeto Range pelo nome da célula, abreviado e entre colchetes.

A propriedade Value é a propriedade padrão do objeto Range. Quando não a especificamos, o VBA subentende. Veja os exemplos a seguir:

72

•

Range("A1").Value = "Clientes" é o mesmo que Range("A1") = "Clientes";

•

Cells(1,1).Value = "Clientes" é o mesmo que Cells(1,1) = "Clientes";


•

•

2

[A1].Value = "Clientes" é o mesmo que [A1] = "Clientes"; Range("a1").end(xldown).offset(1,0).Value = "José" é o mesmo que Range("a1").end(xldown).offset(1,0) = "José".

Logo, na prática, é muito comum não utilizar a propriedade Value, pois, em pouquíssimos casos, ela é realmente necessária.

2.2.1.3.Utilizando a propriedade ActiveCell Também podemos utilizar a propriedade ActiveCell que retorna um objeto Range que é a célula ativa. A propriedade Offset desloca um número de linhas e colunas a partir da célula ativa.

Neste exemplo, começamos selecionando a última célula da coluna A. Range("A1").End(xlDown).Row equivale a você estar com a célulaA1 selecionada e utilizar CTRL + seta para baixo. Toda vez que quisermos encontrar a última célula preenchida (antes de uma célula em branco) em uma tabela, podemos selecionar a primeira célula e utilizar CTRL + seta pra baixo, que a célula ativa passará a ser a última preenchida. Esta é a propriedade End do objeto Range. •

•

End(xldown) equivale a CTRL + seta para baixo e faz referência à última célula antes da primeira em branco, no sentido de cima para baixo; End(xlup) equivale a CTRL + seta para cima e faz referência à última célula antes da primeira em branco, no sentido de baixo para cima;

73


•

•

End(xltoRight) equivale a CTRL + seta para direita e faz referência à última célula antes da primeira em branco, no sentido da esquerda para direita; End(xltoLeft) equivale a CTRL + seta para esquerda e faz referência à última célula antes da primeira em branco, no sentido da direita para esquerda.

Por enquanto, usaremos End(xlDown) por ser mais simples, porém, em breve, usaremos End(xlUp) que é mais profissional. Usamos a função do VBA InputBox para deixar o exemplo mais dinâmico. Ela permite ao usuário entrar com um dado. Também podemos pedir para guardar, na variável linha, o número da última linha + 1, ou seja, o número da primeira linha em branco; inserir, na célula linha, coluna 1, o Cliente e, na célula linha, coluna 2, o valor dele. Os dois códigos têm o mesmo resultado:

Veja outros exemplos de referências ao objeto Range: •

74

Range("C5:D9,G9:H16") faz referência à uma área múltipla: da célula C5 a D9 e da G9 a H16;

•

Range("A:A") faz referência a toda coluna A;

•

Range("1:1") faz referência a toda linha 1;

•

Range("A:C") faz a referência da coluna A a coluna C inteira;


•

Range("1:5") faz a referência da linha 1 a linha 5 inteira;

•

Range("1:1,3:3,8:8") faz referência às linhas 1, 3 e 8 completas;

•

Range("A:A, C:C, F:F") faz referência às colunas A, C e F.

2

2.2.1.4.Configurando o objeto Range como uma variável Outra maneira de fazer referência a um objeto Range é configurando-o como uma variável. Para esse exemplo, insira uma planilha nova ou comece o código com Sheets.Add:

2.2.2. Principais propriedades Vamos ver, agora, as principais propriedades do objeto Range, lembrando que elas são características ou descrições do objeto.

2.2.2.1.Value A propriedade mais utilizada do objeto Range é Value, que representa o conteúdo/valor da célula.

75


Crie uma planilha nova, em branco, com o nome Objeto Range 02.xlsm e digite os seguintes dados na linha 1 e 2. No VBE, insira um módulo com o nome Principais_Propriedades e o seguinte código:

Caso o programador não coloque a propriedade ou um método em um objeto Range, o Excel considera como propriedade Value, que é a propriedade padrão e a mais utilizada deste objeto. Normalmente, os programadores não colocam a propriedade Value, escrevem apenas o nome do objeto e o Excel entende que é esta propriedade.

2.2.2.2. Font Veja as propriedades da hierarquia do objeto Font:

76


2

No VBA, toda vez que repetirmos uma hierarquia de objetos no código em nossas macros, podemos abreviá-la com uso do bloco With / End With:

2.2.2.3. Uso do With / End With Para cada propriedade que definimos no Excel, é necessário referenciar o objeto ao qual ela será aplicada, mesmo quando o objeto for igual para diversas propriedades. O exemplo a seguir deixa clara a necessidade que existe de referenciar o objeto para cada uma das propriedades. Para a mesma célula, vamos definir um valor, aplicar uma cor à fonte, determinar que o valor desta célula é apresentado em duas casas decimais, aplicar à fonte o estilo negrito e aplicar ao fundo uma outra cor: Sub Formatar() Range("A1").Value = 24.3 Range("A1").Font.ColorIndex = 4 Range("A1").NumberFormat = "0.00" Range("A1").Font.Bold = True Range("A1").Interior.ColorIndex = 6 End Sub

77


Para evitar a repetição do mesmo objeto em casos como o que acabamos de verificar, ou seja, casos em que diversas propriedades são aplicadas a um mesmo objeto, podemos usar a estrutura With e End With. O objeto referenciado quando empregamos essa instrução deve ser colocado logo após a própria instrução. A sintaxe da estrutura End e End With é a seguinte: With Objeto Instruções End With

Quando formos digitar as instruções, devemos sempre lembrar que o nome do objeto não deve ser citado e que deve haver um ponto antes de cada uma das propriedades. Todas as instruções que digitarmos entre With e End With serão realizadas no dado objeto. A seguir, temos o código que aplica algumas propriedades a uma célula por meio da instrução With: Sub Formatar() With Range("A1") .Value = 24.3 .Font.ColorIndex = 4 .NumberFormat = "0.00" .Font.Bold = True .Interior.ColorIndex = 6 End With End Sub

Devemos sempre nos lembrar de que é necessário digitar um ponto antes de cada propriedade. Apesar de a propriedade Value ser uma propriedade padrão, não precisando digitá-la na maioria das vezes, deve-se digitá-la dentro da instrução With quando for desejado atribuir um valor em uma célula.

78


2

A instrução With With pode pode também ser usada em conjunto com outras instruções With se With se inserirmos uma dentro da outra, fazendo um processo de d e aninhamento: Sub AlterarPlan() With Sheets("Planilha Sheets("Planilha3") 3") With Range("A5") .Value = 7 .Font.Bold = True .Font.ColorIndex = numero_da_cor End With .Name = "Cadastro" .Move Before:=Sheets("P Before:=Sheets("Plan1") lan1") .Visible = True End With End Sub

Também podemos inserir outro With para a fonte aninhando dois With ao primeiro.

Devemos sempre levar em consideração a importância de recuar as linhas, deixando a primeira e a última linha de cada bloco na mesma posição a partir do lado esquerdo. Para especificarmos a propriedade ou o método m étodo desta hierarquia que está abreviada,, temos que começar a instrução com ponto (. abreviada ( .). Ele indica a continuidade da hierarquia abreviada, na qual não precisamos repetir certas estruturas.

79


2.2.2.4. End Outra propriedade é End End,, muito utilizada para identificar a última linha ou mesmo selecionar a última linha de uma tabela. Como já vimos, é equivalente a utilizar CTRL + seta pra baixo:

Note que quando usamos o End(xldown) End(xldown),, caso haja "buracos", linhas em branco no meio da planilha, não será possível ir até a última linha, pois isso será interrompido no primeiro espaço em branco. Em breve, utilizares End(xlup) End(xlup)..

2.2.2.5.Offset A propriedade Offset desloca um número de linhas e um número de colunas a partir de uma célula:

80


2

Note que existem várias maneiras de escrever a mesma macro. Aqui, a P_08 P_08 e e a P_09 têm o mesmo efeito, mas foram escritas de formas bem diferentes.

2.2.2.6.ColorIndex Outra propriedade também muito utilizada é a propriedade Interior. Colorindex,, que representa as cores de preenchimento, disponíveis no balde Colorindex de preenchimento, que vai até a ColorIndex número 56.

Para ver as cores de ColorIndex ColorIndex 1 1 a 56, utilizaremos a estrutura de repetição For / Next Next para para deixar a nossa macro mais dinâmica:

81


Como ainda não abordamos as variáveis, neste caso, a variável Ncor não está declarada com Dim. Dim. Como o VBA a declarou como Variant Variant,, no topo do módulo módulo não pode estar escrito Option Explicit. Explicit. Se tiver, apague. Neste exemplo, criamos um laço com estrutura de repetição For / Next em Next em que a variável Ncor Ncor começa começa em 1 e repete até chegar a 56.

Ainda não vimos as variáveis nem os loops, porém, uma vez ou outra, os utilizaremos para deixar o código mais dinâmico. Utilizamos também o Sheets.Add Sheets.Add para para adicionar uma nova planilha. Outro comando que utilizaremos muito em nossas macros é o Columns.AutoFit Columns.AutoFit.. Columns é Columns é a coleção de colunas, ou seja, todas as colunas da planilha (Sheet ( Sheet). ). Auto Fit é Fit é um método do autoajuste da largura. Isso é interessante inserir no final das macros. Assim, o usuário não precisa utilizar os dois clicks para fazer o autoajuste da largura das colunas a fim de visualizá-las melhor, afinal, a macro já faz isso. Para os programadores acostumados a usar o padrão de cor RGB, no lugar da propriedade ColorIndex ColorIndex,, basta usar a propriedade .Color = RGB(255,0,0), RGB(255,0,0), por exemplo:

82


2

2.2.2.7.NumberForm NumberFormat at Outra propriedade útil é a NumberFormat NumberFormat,, que permite formatar a(s) célula(s) de acordo com um formato numérico. Utilizaremos muito o formato de moeda: Activecell.NumberFormat= "$ #,##0.00"

Aqui, temos a célula ativa com o formato de moeda.

2.2.2.8.CurrentRegio CurrentRegion n A propriedade CurrentRegion CurrentRegion faz faz referência à toda a tabela: Range("A1").CurrentRegion.Font.Bold = True

Aqui, teremos toda a tabela, que começa na célula A1 A1,, em negrito. Logo, toda vez que quisermos fazer referência à tabela inteira, e não houver nenhuma linha ou coluna em branco, podemos usar CurrentRegion CurrentRegion e, e, então, usamos a propriedade ou o método específico. É uma boa prática nomearmos a tabela com a propriedade Name Name:: Range("A1").CurrentRegion.Name = "Clientes"

Outras propriedades do objeto Range Range que que são menos utilizadas, mas também importantes, são apresentadas adiante.

2.2.2.9.Address, Column, Row As propriedades Address Address,, Column e Row Row retornam retornam o endereço da célula, c élula, o número da coluna e o número número da linha, respectivamente.

83


2.2.2.10.

Borders

A propriedade Borders Borders define define as bordas das células. Aqui, teremos toda a tabela com bordas padrão, pretas: Range("a1").CurrentRegion.Borders.ColorIndex = xlAutomatic

Já aqu aqui, i, as bor bordas das est estarã arão o na na cor cor 8: Range("a1").CurrentRegion.Borders.ColorIndex = 8

Podemos acessar um rico material de exemplos práticos ao deixarmos o cursor posicionado em qualquer uma dessas propriedades e pressionarmos a tecla F1, acessando a Ajuda da própria Microsoft na Internet.

2.2.3. Principais métodos Vamos, agora, abordar alguns dos métodos mais utilizados do objeto Range Range,, lembrando que métodos são ações realizadas no objeto ou por ele.

2.2.3.1.Select O primeiro método mais utilizado é o Select Select,, que seleciona uma célula: Range("A10").Select

2.2.3.2.Clear Este método equivale ao comando Limpar / Limpar tudo, tudo, ou seja, ele serve para limpar tudo o que há nas células. O exemplo a seguir limpa todos os elementos das células presentes no intervalo B2:B20 B2:B20:: Range("B2:B20").Clear

84


2

2.2.3.3.ClearContents Este método limpa apenas o conteúdo da célula, mantendo as demais propriedades. É equivalente a selecionar a célula e pressionar DELETE. O exemplo a seguir limpa o conteúdo das células presentes no intervalo B2:B20: Range("B2:B20").ClearContents

2.2.3.4.ClearFormats Este método limpa apenas a formatação da célula, mantendo o seu conteúdo. O exemplo a seguir limpa apenas a formatação das células contidas no intervalo C2:C4: Range("c2:c4").ClearFormats

2.2.3.5.Delete Este método é usado para excluir células da planilha. Na sua sintaxe, mostrada a seguir, o argumento Shift não é obrigatório e serve para definir o local para onde serão deslocadas as células; se para a esquerda, devemos usar xlShiftToLeft, se para cima, xlShiftUp: Delete(Shift)

O exemplo a seguir exclui as células contidas no intervalo, deslocando as demais células para a esquerda: Range("c2:c4").Delete xlToLeft

85


2.2.3.6. Insert Este método insere células na planilha. Na sua sintaxe, mostrada a seguir, o argumento Shift não é obrigatório e serve para indicar o local para onde serão deslocadas as células; se para a direita, devemos usar xlShiftToRight, se para baixo, XlShiftDown: Insert(Shift)

O exemplo a seguir insere as células contidas no intervalo, deslocando as demais células para baixo: Range("c2:c6").Insert xldown

2.2.3.7.Speak O método Speak fala, em inglês, o conteúdo da célula. Até este ponto, já pudemos conhecer algumas propriedades e métodos de alguns objetos. Novos objetos, métodos e propriedades serão abordados ao longo da apostila.

2.2.4. Coleção Cells Coleção é um objeto que tem propriedades e métodos e que representa todos os objetos de um mesmo tipo ou, tecnicamente falando, instanciados na mesma Classe. A coleção do objeto Range é Cells. Cells, portanto, é uma coleção que representa todas as células de uma planilha (Sheet). É muito comum o início das macros que geram relatórios ser este: Cells.Clear

Esta linha de código limpa todas as células da planilha.

86


2

2.3. Objeto Worksheet - Planilha(s) Os Worksheets representam as planilhas de um arquivo. Chamamos de coleção de Worksheets o conjunto de planilhas de uma pasta de trabalho. Cada uma das planilhas é um objeto da coleção. Em alguns casos, os objetos são também chamados de Sheets. 1. Crie um Arquivo Novo, em branco, com o nome 02 Objeto Sheet, e habilite para macro; 2. Insira um módulo e digite, na propriedade Name deste módulo, Fazendo_ Referência; 3. Comece inserindo 10 planilhas no arquivo: Sub F_01 Dim c as Byte For c = 1 to 10 Sheets.Add Next c End Sub

2.3.1. Fazendo referência Existem 4 maneiras mais utilizadas para fazer referência à uma planilha: •

Pelo nome (da aba da planilha);

•

Pela posição;

•

Pelo nome do VBA;

•

ActiveSheet.

87


2.3.1.1.Fazendo referência à planilha pelo nome (da aba da planilha) Sub F_02 Sheets("Plan7").Select End Sub

Note que é preciso existir uma planilha com esse nome, caso contrário, será retornado um erro: Subscrito fora do intervalo, indicando que o Excel não encontrou o objeto:

Este é um dos erros mais frequentes do VBA. Caso este erro tenha sido gerado, confira sempre o objeto referenciado. Neste exemplo, o objeto é sheets("Plan7"). Logo, tem que existir uma planilha no seu arquivo com o nome Plan7, caso contrário, o erro é exibido. Para consertar, crie uma planilha com este nome ou corrija o código, inserindo o nome de uma planilha existente.

2.3.1.2.Fazendo referência à planilha pelo número de índice Quase todos os objetos do VBA podem ser referenciadoa pelo nome, usando aspas, ou pelo número de índice. Quando fazemos referência pelo número (da planilha), será considerada a posição da esquerda para a direita.

88


2

Aqui, neste exemplo, será selecionada a segunda planilha da esquerda para a direita. Sub F_03 Sheets(2).Select End Sub

Se só existir uma planilha neste arquivo, será retornado o erro Subscrito fora do Intervalo, indicando que o Excel não encontrou esse objeto.

2.3.1.3. Usando o nome do VBA Nomeie uma planilha como Estoque e outra como Balanço Contábil. Observe que, na janela do Projeto – VBAProject, existem 2 nomes para a mesma planilha:

O nome que está do lado direito, entre parênteses, é o nome visualizado pelo usuário na aba da planilha e referenciado por Sheets("Nome_da_Planilha"), como já visto. Porém, podemos fazer a referência pelo nome que está no lado esquerdo, que é o nome interno do VBA. Neste caso, não usamos Sheets com aspas duplas, mas apenas o nome que está do lado esquerdo, seguido de ponto (.): Plan11.Range("A1") = "Estoque"

Neste exemplo (adapte seu código para o nome que está do lado esquerdo e direito), a Plan11 é referenciada pelo nome do VBA (que é o do lado esquerdo) e o texto Estoque é inserido na célula A1.

89


2.3.1.4.ActiveSheet ActiveSheet faz referência à planilha ativa. É muito comum quando inserimos uma planilha nova no arquivo (ela fica selecionada) e a nomeamos: Sheets.Add ActiveSheet.name = "VENDAS CONSOLIDADAS"

2.3.2. Principais propriedades Veja, a seguir, as principais propriedades da planilha.

2.3.2.1. Name A propriedade Name é o nome da aba da planilha: Sub P_01() ActiveSheet.Name = "COMPRAS" End Sub

2.3.2.2.Visible A propriedade Visible determina se a planilha está visível ou não. Tanto o Visible False quanto o xlVeryHidden ocultam a planilha, a diferença é que as planilhas ocultadas com xlVeryHidden só poderão ser reexibidas pelo VBA, enquanto que as ocultas com False podem ser reexibidas manualmente, quando o usuário clicar com o botão direito do mouse no nome da planilha e na opção Reexibir. Neste exemplo, vamos ocultar a planilha com False e, em seguida, exibi-la manualmente. Sub P_02() Sheets("Mês 1").Visible=False End Sub

90


2

Ao reexibir, a seguinte janela será aberta:

•

Agora, reexiba e oculte com XlVeryHidden: Sub P_03() Sheets("Mês 1").Visible=xlVeryHidden End Sub

Tente reexibir manualmente:

Note que a opção Reexibir não ficou disponível. Isso ocorreu porque uma vez que foi ocultado com XlVeryHidden, só poderá ser reexibido pelo código VBA: Sub P_04() Sheets("Mês 1").Visible=True End Sub

91


2.3.2.3.ScrollArea ScrollArea é outra propriedade interessante pois delimita o acesso a cliques do usuário nas células da planilha. ActiveSheet.ScrollArea = "A1:C3"

Por meio desta propriedade podemos estipular os limites de rolagem pela planilha, pois sua função é retornar ou definir a área de rolagem.

2.3.3. Principais métodos Agora, veremos os principais métodos da planilha.

2.3.3.1.Select O método mais utilizado é o Select, que seleciona a planilha: Sheets(1).Select

2.3.3.2.Delete O métoto Delete exclui a planilha. Porém, quando tentamos excluir a planilha manualmente, o Excel antes pergunta se o usuário deseja continuar, oferecendo a possibilidade de o usuário cancelar a exclusão:

92


2

No VBA, o Excel mantém o mesmo comportamento, exibindo esta pergunta. Então, quando tivermos uma macro que exclui a planilha, podemos alterar a propriedade do objeto Application que configura este comportamento para False:

Ao terminar a macro, a propriedade DisplayAlerts do objeto Application volta automaticamente a ser True.

2.3.3.3.Protect / Unprotect Protect protege a planilha e Unprotect a desprotege:

93


Na macro M_04, a planilha está sendo protegida com a senha "123" e, na M_05, a planilha está sendo desprotegida. Note que a senha deve ser informada também ao desproteger. Na M_06, exibimos uma situação muito comum, em que a planilha estará sempre protegida, mas permitirá, através desta macro, a inserção de dados na última linha + 1 das colunas 1 e 2 da planilha. Para proteger o código para visualização, basta ir no menu Ferramentas do VBA e em Opções / Propriedades:

2.3.3.4. Copy Este método copia uma planilha para outra posição no arquivo. A sintaxe deste método é: Copy(After,Before)

O exemplo adiante faz uma cópia da plan2 para uma posição após a plan3: Sheets(“plan2”).Copy , Sheets(“plan3”)

94


2

2.3.3.5.Move Este método move a planilha para outra posição no arquivo. Sua sintaxe é a seguinte: Move(Before,After)

O exemplo adiante move a planilha Plan1 para a última posição na pasta de trabalho: Sheets(“Plan1”).Move , Sheets(Sheets.Count)

2.3.4. Coleção Sheets A coleção Sheets é o objeto que representa todas as planilhas. Por meio deste método, podemos adicionar, à pasta de trabalho, folhas de planilha, macros do Excel 4.0 ou gráficos. Sua sintaxe é exibida adiante e, em seguida, está a descrição de cada um de seus elementos: Add(Before,After,Count,Type)

Em que: •

Before: Planilha antes da qual será inserida a nova;

•

After: Planilha após a qual será inserida a nova;

•

Count: Quantidade de planilhas que serão inseridas;

•

Type: Tipo da planilha que será inserida. Podemos usar o tipo xlWorkSheet, que corresponde à planilha padrão, ou xlExcel4MacroSheet, que corresponde à folha de macro do Excel 4.0.

95


Veja o exemplo para inserir uma planilha após a última planilha da pasta de trabalho: Sheets.Add , Sheets(Sheets.Count)

É muito comum utilizar esta coleção para adicionar uma planilha, ou seja, um elemento à coleção, e para contar quantas planilhas têm em um arquivo: Sheets.Add MsgBox "Neste Arquivo temos: " & Sheets.Count & " Planilhas!!!"

Note que, quando adicionamos planilha no arquivo, a planilha é adicionada no lado esquerdo da planilha que está selecionada. Para que a adição seja feita depois da última planilha, podemos utilizar o argumento after (depois da última planilha). Lembrando que o número (a posição) da última planilha é sempre sheets.count. Assim, podemos melhorar o código anterior: Sheets.Add after:=sheets(sheets.count) MsgBox "Neste Arquivo temos: " & Sheets.Count & " Planilhas!!!"

Agora, considere o seguinte exemplo: Msgbox "O arquivo possui " & sheets.count & " planilha(s)"

96


2

2.4. Objeto Workbook - Arquivo(s) Workbooks são os objetos correspondentes às pastas de trabalho, ou seja, aos arquivos.

2.4.1. Fazendo referência Crie um arquivo em branco, habilite para macro e salve com o nome 03 Objeto Workbook. Vamos fazer uma macro que adicionará um arquivo em branco. Para isso, digite o seguinte código: Sub M_01 Workbooks.Add End Sub

2.4.1.1.Fazendo referência pelo nome do arquivo Para fazer referência pelo nome, coloque o nome da coleção ( Workbooks) e, entre parênteses, especifique o elemento da coleção que você deseja referenciar. Como faremos pelo nome, use aspas duplas: Sub M_02 Workbooks("03 Objeto Workbook").Activate End Sub

97


2.4.1.2.Fazendo referência pelo número de índice Quando fazemos referência ao arquivo pelo número, o Excel considera a ordem de abertura: Sub M_03() Workbooks(2).Activate End Sub

2.4.1.3.Fazendo referência por ActiveWorkbook e ThisWorkbook ActiveWorkbook faz referência ao arquivo ativo. Se tenho vários arquivos abertos e a seguinte linha de código, será exibido o nome do arquivo selecionado: MsgBox ActiveWorkbook.Name

Já ThisWorkbook faz referência ao arquivo em que está escrita a macro. Independente do arquivo selecionado, será exibido para o código adiante, o nome do arquivo que contém a macro: MsgBox ThisWorkbook.Name.

2.4.2. Principais propriedades A seguir, serão listadas algumas propriedades do objeto Workbook.

98


2

2.4.2.1.Name, Path e FullName Escreva e execute esta macro e, então, confira o conteúdo das células A1, A2 e A3:

2.4.2.2.Save Para os arquivos que estão salvos, esta propriedade retorna o valor True; para os arquivos que não foram salvos, retorna o valor False.

2.4.3. Principais métodos Alguns dos métodos para o objeto Workbook são os seguintes:

2.4.3.1.Close Este método fecha o arquivo. Adiante, temos a representação de sua sintaxe e, em seguida, a descrição de cada um de seus elementos: Close(SaveChanges,FileName,RouteWorkbooks)

99


Em que: •

•

•

SaveChanges: Determina se o arquivo deve ou não ser salvo, portanto, pode ser definida como True ou False; FileName: É o nome do arquivo; RouteWorkbooks: Determina se o arquivo deve ser encaminhado ou não a um destinatário de circulação. ActiveWorkbook.Close SaveChanges:=True

2.4.3.2.Open Devemos usar este método para abrir um arquivo. A seguir, temos a representação de sua sintaxe: Workbooks.Open FileName:="Caminho do arquivo"

O exemplo a seguir traz a linha de código que abre um arquivo: Workbooks.Open Filename:="C:\documentos\RelMensal.xlsx"

2.4.3.3. Save Este método salva o arquivo. A seguir, temos a representação de sua sintaxe: Workbooks().Save

O exemplo a seguir traz a linha de código que salva um arquivo: Workbooks("RelMensal.xlsx").Save

100


2

2.4.3.4.Protect Este método é utilizado para proteger a pasta de trabalho e é equivalente a ativar, na guia Revisão, o botão Proteger Pasta de Trabalho. A sintaxe para esse método é a seguinte: Protect(Password,Structure,Windows)

Veja um exemplo de utilização: Workbooks("teste1.xlsx").Protect "123Senha", True, True

Nesse exemplo, será protegida a estrutura da pasta e da janela da pasta teste1. xlsx com a senha 123Senha.

2.4.3.5.Unprotect Este método tira a proteção da pasta de trabalho. Sua sintaxe é a seguinte: Unprotect(Password)

Veja o exemplo de utilização: Workbooks("teste1.xlsx").Unprotect "123Senha"

2.5. Objeto Application – O próprio Excel 2.5.1. Principais propriedades Algumas das propriedades do objeto Application são as seguintes:

101


2.5.1.1.Caption Esta propriedade retorna ou define o texto da barra de títulos do Excel. Podemos definir a barra de títulos com um texto de nossa escolha, por exemplo, Orçamentos. Então, devemos usar o seguinte código: Application.Caption = "Orçamentos"

Podemos fazer a barra de títulos exibir o texto original novamente, definindo para essa propriedade o valor Empty. Nesse caso, usaríamos o seguinte código: Application.Caption = Empty

Podemos capturar o texto da barra de títulos do Excel usando o seguinte código: Application.Caption

Segue exemplo que exibe o conteúdo da barra de títulos em uma caixa de mensagem: Sub titulo() MsgBox Application.Caption End Sub

Veja o resultado:

102


2

Outra maneira é a seguinte:

Em que Application.Caption = "" é o mesmo que Application.Caption = Empty.

2.5.1.2.ActiveWindow Esta propriedade retorna ou define o nome da janela ativa. O exemplo a seguir altera o nome da janela ativa para Mês de Fevereiro: Application.ActiveWindow.Caption = "Mês de Fevereiro"

Como não é necessário digitar o termo Application, o código pode ser utilizado da seguinte forma: ActiveWindow.Caption = "Mês de Fevereiro"

2.5.1.3.ScreenUpdating É uma propriedade do aplicativo Excel que permite determinar a atualização da tela, como True (opção padrão) ou False, no momento em que uma macro está sendo executada. Se usarmos False, a execução da macro se torna mais rápida, pois não é exibido o movimento de execução.

103


O exemplo a seguir define esta propriedade como False: Application.ScreenUpdating = False

Execute este código com e sem a linha do ScreenUpdating e veja a diferença:

Aqui, t3 é a duração de tempo que a macro levou para ser executada até o fim. Note que com o ScreenUptading e sem o tempo é diferente.

2.5.1.4.DisplayAlerts Com esta propriedade, podemos permitir ou não a exibição de mensagens de alerta durante a execução de uma macro. Portanto, devemos defini-la como True ou False. Por padrão, essa propriedade é definida como True. Se, por exemplo, não quisermos que apareça o alerta perguntando se desejamos salvar alterações quando uma pasta de trabalho é fechada por uma macro, devemos definir essa propriedade como False, como mostra a linha a seguir: Application.DisplayAlerts = False

104


2

Execute este código com e sem a linha do DisplayAlerts e veja a diferença:

2.5.1.5.EnableCancelKey Esta propriedade autoriza ou não a interrupção da execução de uma macro por meio das teclas do teclado ESC ou CTRL + BREAK. Podemos definir essa propriedade com um dos tipos a seguir: •

•

•

xlInterrupt: Definido como padrão, permite o cancelamento da macro pelo teclado; xlDisabled: Não permite o cancelamento; xlErrorHandler: A interrupção é enviada para um procedimento em execução, como um erro, podendo ser tratada com a utilização de comandos de tratamento de erros.

Devemos digitar a linha a seguir quando não queremos permitir a interrupção da macro: Application.EnableCancelKey = xlDisabled

105


2.5.1.6. DataEntryMode Esta propriedade possibilita definir o modo de entrada de dados. Para isso, devem-se considerar as seguintes informações: •

xlOff : Permite que qualquer célula seja selecionada;

•

xlOn: Permite a seleção de uma célula depois de pressionada a tecla ESC;

•

xlStrict: Impede a seleção de uma célula mesmo quando pressionada a tecla ESC.

Se digitarmos a linha a seguir, nenhuma célula de nenhuma planilha poderá ser selecionada: Application.DataEntryMode = xlStrict

2.5.1.7.WindowState Esta propriedade permite determinar se a janela do Excel será maximizada (com o valor xlMaximized), normal (com o valor xlNormal), ou minimizada (com o valor xlMinimized). O exemplo a seguir deixa a janela maximizada: Application.WindowState = xlMaximized

106


2

2.5.1.8.StatusBar Através da propriedade StatusBar do objeto Application, podemos configurar as mensagens da barra de status do Excel:

Quando executamos a macro P_06, o resultado é o seguinte:

Para retornarmos ao comportamento padrão da barra de status, execute a macro P_07.

2.5.1.9.Cursor Podemos, também, especificar qual cursor o Excel utilizará. Para o padrão, escolha xlDefault:

Veja que, na macro P_08, o cursor exibido é o do modo Espera.

107


2.5.1.10. Dialogs Através da propriedade Dialogs do objeto Application, podemos retornar qualquer caixa de diálogo do Excel. Veja o exemplo:

Na macro P_10, será mostrada a caixa de diálogo do Zoom:

Na macro P_11, será exibida a caixa de diálogo de Visualização de Impressão:

108


2

2.5.1.11. Version A propriedade Version retorna a versão do Excel:

2.5.2. Principais métodos A seguir, vamos descrever alguns métodos para o objeto Application.

2.5.2.1.Quit Este método fecha o Excel. Observe o exemplo, em que salvamos antes o arquivo para que não interrompa a macro perguntando se deseja salvar:

2.5.2.2.Calculate Quando usamos este método, todas as planilhas de todas as pastas de trabalho abertas são calculadas. Sua sintaxe é a seguinte: Application.Calculate

2.5.2.3.FindFile O método FindFile abre a caixa de diálogo Abrir (Arquivo), permitindo ao usuário escolher, clicar e abrir um arquivo Excel. Application.FindFile

109


2.5.2.4.GetOpenFileName Apesar de a função InputBox ser muito útil para inserção e envio de informações do usuário para o computador, nos casos em que o procedimento usado solicita o nome de um arquivo, ela não será a função mais apropriada. Isto porque é difícil para os usuários lembrarem e digitarem caminhos e diretórios de arquivos; além disso, erros de digitação podem ocorrer, dificultando o processo para encontrar um arquivo. O método GetOpenFilename é usado exatamente para simplificar o acesso a arquivos, garantindo que o nome e o caminho do arquivo estejam corretos. Esse método abre a mesma caixa de diálogo Abrir que é exibida quando selecionamos o comando Abrir, contido na guia Arquivo do Excel. Apesar de ser a mesma caixa de diálogo, a que visualizamos por meio do método GetOpenFilename não abre arquivos, mas apenas retorna como uma sequência de caracteres o nome do arquivo que o usuário selecionar. Depois, podemos escrever um código para usar o nome do arquivo como nos for conveniente. Para selecionar mais de um nome de arquivo por meio do método GetOpenFilename, é necessário definir como True o argumento MultiSelect para que o método retorne uma série de nomes de arquivos. O código usado precisa fazer um loop nos nomes de arquivos, ou seja, processar cada um deles para poder identificar todos eles. Sub Seleciona_Arquivo() Dim Caminho As String Caminho = Application.GetOpenFilename MsgBox Caminho End Sub

110


2

A variável Caminho irá armazenar o local correspondente ao arquivo selecionado.

O conteúdo da variável (caminho do arquivo) será exibido na caixa de mensagem.

2.5.2.5.GetSaveAsFileName Método semelhante ao que acabamos de descrever é o GetSaveAsFilename. Este método, porém, abre uma caixa de diálogo igual à caixa Salvar Como do Excel e obtém o nome e caminho do arquivo, mas sem que seja feita qualquer ação com essas informações. A sintaxe desse método e seus argumentos são os seguintes: object.GetSaveAsFilename([initialFilename], [fileFilter],[filterI ndex],[title],[buttonText])

111


Em que: •

•

•

InitialFilename: Este argumento determina qual será o nome padrão mostrado na caixa antes que o usuário escolha outro arquivo; FileFilter: Com este argumento, podemos determinar quais tipos de arquivos ficarão disponíveis na caixa de diálogo para o usuário escolher; Title: Este argumento determina o texto a ser exibido na barra de títulos da caixa de diálogo.

Este exemplo exibe o caminho e nome do arquivo em uma caixa de mensagem: MsgBox Application.GetSaveAsFilename

Pode ser que em alguns casos a informação que desejamos não seja o nome de um arquivo, mas sim o de uma pasta. Então, devemos usar o objeto FileDialog, como mostra o procedimento a seguir, para que uma caixa de diálogo onde possamos selecionar um nome de diretório apareça. Sub Identifica_Endereço() With Application.FileDialog(msoFileDialogFolderPicker) .InitialFileName = Application.DefaultFilePath & "\" .Title = "Escolha um local para salvar seus arquivos" .Show If .SelectedItems.Count = 0 Then MsgBox "Operação Cancelada" Else MsgBox .SelectedItems(1) End If End With End Sub

No objeto FileDialog, podemos especificar um valor para a propriedade InitialFilename para determinar o diretório inicial. Então, o código definirá como diretório inicial o caminho de arquivo padrão do Excel.

112


2

2.5.2.6.OnKey Por meio do método OnKey, podemos fazer com que a execução de um procedimento seja desencadeada quando pressionarmos uma combinação específica de teclas, uma vez que o Excel acompanha cada tecla que digitamos. É justamente essa combinação que o OnKey, cuja sintaxe é descrita a seguir, define: expressão.OnKey(Tecla_ou_conjunto_de_teclas, Procedimento)

A seguir, temos a descrição de cada um dos argumentos presentes na sintaxe de OnKey: •

•

•

expressão: Aqui, devemos inserir uma expressão que retorna um objeto Application; Tecla_ou_conjunto_de_teclas: Este argumento é uma string. É onde devemos inserir as teclas que desejamos combinar, as quais podem ser as teclas ALT, CTRL ou SHIFT (que também podem ser combinadas entre elas próprias), mais outra tecla. As teclas podem ser representadas por mais de um caractere; então, para a tecla A devemos digitar "a" e para a tecla ENTER, devemos digitar "{ENTER}"; Procedimento: Este argumento é uma variant e é a parte em que devemos especificar o nome do procedimento a ser utilizado. Seu uso não é obrigatório, ou seja, se utilizarmos um texto vazio (""), a combinação de teclas especificada em Tecla_ou_conjunto_de_teclas não gerará resultado algum. Esse modo de OnKey tem a capacidade de modificar as ações resultantes do pressionamento de teclas predefinidas pelo Excel, mas é possível retornar às ações originais omitindo o Procedimento. Então, se omitido, Tecla_ou_conjunto_de_teclas reverte seu resultado normal e as combinações determinadas com OnKey são excluídas.

113


Algumas teclas, quando pressionadas, não exibem um caractere correspondente a ela, como a tecla ENTER ou TAB. Para essas teclas, existem códigos que as representam, os quais serão mostrados na tabela seguinte: Tecla BACKSPACE BREAK CAPS LOCK CLEAR DELETE ou DEL END ENTER ENTER (teclado numérico) ESC F1 a F15 HELP HOME INSERT NUMLOCK PAGE DOWN PAGE UP RETURN SCROLL LOCK TAB Seta para a direita Seta para a esquerda Seta para cima Seta para baixo

114

Código {BACKSPACE} ou {BS} {BREAK} {CAPSLOCK} {CLEAR} {DELETE} ou {DEL} {END} ~ (til) {ENTER} {ESCAPE} ou {ESC} {F1} a {F15} {HELP} {HOME} {INSERT} {NUMLOCK} {PGDN} {PGUP} {RETURN} {SCROLLLOCK} {TAB} {RIGHT} {LEFT} {UP} {DOWN}


2

Além de serem utilizadas sozinhas, as teclas também podem ser aplicadas em adição a CTRL, ALT e SHIFT, sendo que estas três podem ser combinadas também entre elas. Para combinar uma tecla com SHIFT, o código desta tecla deve ser precedido por um sinal de mais (+). Para combinar com CTRL, devemos preceder o código com acento circunflexo (^). Já para combinar com ALT, preceder com o sinal de porcentagem (%).

Os procedimentos também podem ser atribuídos a caracteres especiais, como +, ^, % e outros. Nesse caso, o caractere especial deve estar entre chaves { }. A seguir, temos, como exemplo, um código que executa a macro Editar quando a tecla F7 é pressionada: Application.OnKey "{F7}", "Editar"

No próximo exemplo, a mesma macro Editar é executada quando pressionamos as teclas CTRL + F7: Application.OnKey "^{F7}", "Editar"

Já o código seguinte executa a macro Editar quando são pressionadas as teclas CTRL + ALT + F7: Application.OnKey "^%{F7}", "Editar"

O atalho somente começará a funcionar depois de executada a linha de código (tecla de função F5). O código a seguir faz com que a tecla F7 tenha seu resultado restaurado, ou seja, volte a ter sua função original, pois o nome da macro, ou procedimento, é emitida no código: Application.OnKey "{F7}"

115


Também é possível desativar a função de uma tecla ou atalho inserindo duas aspas ("") no lugar do procedimento, como mostra o exemplo seguinte, o qual desativa a função da tecla F7: Application.OnKey "{F7}", ""

O método OnKey, uma vez especificado, fica ativo enquanto o Excel estiver aberto, mesmo que a pasta de trabalho para o qual ele foi desenvolvido seja fechada. Então, cada vez que um atalho criado para essa pasta de trabalho for pressionado, ela abrirá novamente, a não ser que o código seja incluído no código de evento Workbook_BeforeClose. Por meio de macros, podemos definir um atalho para exibição de um formulário de cadastro, atalho este que deverá executar a macro Abrir_Cursos, responsável por exibir o formulário. A macro responsável por abrir o formulário é a seguinte: Sub Abrir_Cursos() frmCursos.Show End Sub

Já a macro que irá definir o atalho é descrita a seguir: Sub atalhos() Application.OnKey "{f2}", "Abrir_Cursos" End Sub

Também é possível definir o atalho criado para funcionamento apenas enquanto o arquivo estiver aberto. Sendo assim, devemos inserir o comando dentro do evento Workbook_BeforeClose do arquivo em questão, como mostrado adiante: Private Sub Workbook_BeforeClose(Cancel As Boolean) Application.OnKey "{F2}", "" End Sub

116


2

2.5.2.7.OnTime O método OnTime permite agendar a data e a hora para a execução de uma macro. Utilizar o método OnTime não impede o usuário de retornar à interação normal com o Excel e nem de utilizar outras macros, mesmo que esse método ainda não tenha sido executado. A seguir, temos a sintaxe do OnTime e, depois, a descrição de cada um de seus argumentos: expressão.OnTime(Início, Procedimento, Término, NovoProcedimento)

Em que: •

•

•

•

•

expressão: Aqui, devemos inserir uma expressão que retorna um objeto Application; Início: Este argumento é uma variant de uso obrigatório. É onde definimos o início da execução do procedimento; Procedimento: Este argumento é uma string de uso obrigatório. É o nome do procedimento a ser executado; Término: Este argumento é uma variant opcional. Ele determina até quando o procedimento poderá ser executado. Vamos supor que, quando a hora do Início for atingida, o Excel esteja executando outro procedimento e o Término tenha sido definido como Início+45. Sendo assim, 45 segundos serão aguardados até que o procedimento anterior termine sua execução. Se o Excel ainda não estiver pronto após 45 segundos, o procedimento não será executado; NovoProcedimento: Este argumento é uma variant opcional. Por meio dele, podemos definir um novo procedimento OnTime. Se o resultado for False, o procedimento anterior é excluído; se for True, que é o valor padrão, podemos programar um novo procedimento.

117


A função TimeValue é a representação do tempo do VBA. Quando utilizamos TimeValue(horário), o método OnTime é executado num horário específico. Porém, se utilizarmos Now+TimeValue(tempo decorrido), o método é executado depois de um determinado espaço de tempo, contando a partir do momento atual. A função TimeValue converte a cadeia de caracteres que definimos como tempo em um valor que o Excel consegue interpretar e acessar. A seguir, temos um exemplo de código que executa o procedimento Bombar em 27 segundos a partir do momento atual: Application.OnTime Now + TimeValue("00:00:27"), "Bombar"

No próximo exemplo, o procedimento Bombar será executado às 15:00: Application.OnTime TimeValue("15:00:00"), "Bombar"

Além de programar um horário, podemos programar o dia em que deve ser executado o procedimento utilizando o DateValue. O exemplo a seguir mostra um código que executa o procedimento Bombar às 20:00h do dia 12 de fevereiro de 2016: Application.OnTime DateValue("02/12/2016 8:00 pm"), "Bombar"

Para utilizar o DateValue, é necessário manter o computador funcionando. Além disso, a pasta de trabalho que contém o procedimento deve permanecer aberta. O evento OnTime também funciona em conjunto com o procedimento UpdateClock. Esse procedimento insere a hora em uma célula e programa outro evento para algum tempo depois, que é a sua própria execução outra vez. Dessa forma, a célula escolhida tem a hora atualizada periodicamente, a menos que o evento seja cancelado com o procedimento StopClock.

118


2

A seguir, temos, como exemplo, um código que insere, na célula B4, a hora atualizada a cada 21 segundos. Nesse código, NextTick é uma variável que armazena a hora para o próximo evento: Dim NextTick As Date Sub ExibeHorario() 'atualiza a célula B4 com o tempo atual Range("B4") = Time 'Prepara o próximo evento para que ocorra daqui a 02 segundos NextTick = Now + TimeValue("00:00:02") Application.OnTime NextTick, "ExibeHorario" End Sub

Como já foi dito, o método OnTime fica ativo mesmo que a pasta de trabalho para a qual ele foi desenvolvido seja fechada, o que quer dizer que, a cada 21 segundos (tomando como base o exemplo dado), a pasta de trabalho será aberta novamente, se o procedimento StopClock não tiver sido executado. Podemos evitar que isso ocorra utilizando o evento Workbook_BeforeClose com a seguinte instrução: Sub PararRelogio() On Error Resume Next ‘Cessa o relógio (OnTime é interrompido) Application.OnTime NextTick, "ExibeHorario", , False End Sub Private Sub Workbook_BeforeClose(Cancel As Boolean) Call PararRelogio End Sub

119


2.5.2.8.Wait É comum termos situações em que é necessário fazer com que a macro pause/ espere alguns segundos. Muitos programadores usam, para isso, um For/Next, mas o método Wait tem a função de aplicar essa pausa. Veja o exemplo adiante:

Note que houve uma pausa de 5 segundos entre os comandos do Range("C!") e Range("C2").

120


2

Pontos principais Atente para os tópicos a seguir. Eles devem ser estudados com muita atenção, pois representam os pontos mais importantes do capítulo. •

•

•

•

•

•

Para cada propriedade que definimos no Excel, é necessário referenciar o objeto ao qual ela será aplicada, mesmo quando o objeto for igual para diversas propriedades. Para evitar a repetição do mesmo objeto nos casos em que diversas propriedades são aplicadas a um mesmo objeto, podemos usar a estrutura With e End With; O método GetOpenFilename é usado para simplificar o acesso a arquivos, garantindo que o nome e o caminho do arquivo estejam corretos; O método GetSaveAsFilename abre uma caixa de diálogo igual à caixa Salvar Como do Excel e obtém o nome e caminho do arquivo, mas sem que seja feita qualquer ação com essas informações; As cerca de setecentas caixas de diálogo do Excel são muito úteis para agilizar o trabalho com programação, pois por meio delas os próprios usuários podem escolher diversas opções, como formatar fonte, preencher sequências, imprimir etc; Por meio do método OnKey, podemos fazer com que a execução de um procedimento seja desencadeada quando pressionarmos uma combinação específica de teclas, uma vez que o Excel acompanha cada tecla que digitamos. É justamente essa combinação que o OnKey define; O método OnTime permite agendar a data e a hora para a execução de uma macro. Utilizar o método OnTime não nos proíbe de retornar à interação normal com o Excel e nem de utilizar outras macros, mesmo que esse método ainda não tenha sido executado.

121

2


Teste seus conhecimentos


1. Qual é a sintaxe utilizada para alterar o texto contido na barra de título do Excel?

☐

a) Range(“a1”).Select

☐

b) Application.Caption = “Texto Qualquer”

☐

c) Selection.Title = “Texto Qualquer”

☐

d) Sheets(“Texto Qualquer”).Select

☐

e) As alternativas B e C estão corretas.

2. Qual das alternativas a seguir refere-se à pasta de trabalho (arquivos)?

124

☐

a) Worksheets

☐

b) Workbooks

☐

c) Range

☐

d) Selection

☐

e) Application


2

3. Qual comando deve ser utilizado para inserir um texto na célula A4, sabendo-se que o cursor está posicionado sobre ela?

☐

a) Range(“a3”).Select

☐

b) Activecell.Value = “Texto”

☐

c) Range(“a4”).Select = “texto”

☐

d) Activecell = “Texto”

☐

e) As alternativas B e D estão corretas.

4. Que comando executa uma macro automaticamente após 30min do horário atual?

☐

a) Application.Ontime now + TimeValue(“00:30:00”), ”MacroTeste”

☐

b) Application.Onkey 27

☐

c) Activewindow.OnTime agora + 30

☐

d) Workbooks now()+ 00:30

☐

e) Nenhuma das alternativas anteriores está correta

125


5. Qual é o método utilizado para fechar o Excel?

126

☐

a) Select

☐

b) Range

☐

c) Finish

☐

d) Calculate

☐

e) Quit

2

Principais objetos, propriedades e métodos Mãos à obra!


Laboratório 1 A – Criando uma macro pelo código em VBA para realizar a formatação da planilha 1. Crie a seguinte planilha:

2. Com a utilização do VBA, crie uma macro chamada Formatar, que deverá realizar uma formatação na planilha, conforme o exemplo a seguir: •

Fonte: Book Antiqua, sublinhado, cor vermelha;

Mesclar e centralizar células.

•

•

•

•

•

•

128

Negrito; Sombreamento verde; Centralizar texto nas células.

Conteúdo da planilha em itálico; Todas as bordas na tabela inteira.


2

3. Crie uma macro com o nome Limpar_Formatos, que deverá remover toda a formatação gerada pela macro anterior, inclusive, a de mesclagem de células. Após remover a formatação, deverá ser exibida uma caixa com a seguinte mensagem: Operação Concluída;

Para remover somente a formatação das células, utilize o método ClearFormats. 4. Insira dois botões de controle (mostrados nas imagens anteriores), atribuindo uma macro para cada um.

Laboratório 2 A – Utilizando o método OnTime 1. Desenvolva macros que deverão exibir uma caixa de texto para o usuário quando o arquivo já estiver aberto há 30 minutos, questionando se ele deseja salvar o arquivo. Se o usuário clicar no botão SIM, o arquivo deverá ser salvo. Caso contrário, a mensagem Operação Cancelada deverá ser exibida: •

•

Macro Salvar_Arq: Deverá exibir a função Msgbox() e salvar o arquivo, caso a resposta seja positiva; Macro Auto_Open: Deverá chamar a macro Salvar_Arq após o arquivo estar aberto por 30 minutos.

129

Operadores, variáveis, constantes e arrays 9 9 9 9 9

Operadores; Variáveis; Macros com passagem de parâmetros; Constantes; Arrays.


3.1. Operadores O tipo de cálculo feito com os elementos de uma fórmula é definido pelo operador utilizado. O VBA possui quatro categorias de operadores, que conheceremos nos próximos tópicos. São eles: •

Operadores aritméticos;

•

Operadores de comparação;

•

Operadores lógicos;

•

Operadores de concatenação.

3.1.1. Operadores aritméticos Os operadores aritméticos, descritos e exemplificados na próxima tabela, são empregados na realização de cálculos matemáticos: Operador + * / \ Mod ^

132

Descrição Exemplos Soma dois valores. 7+7 resulta em 14. Subtrai dois valores. 7-2 resulta em 5. Multiplica dois valores. 3*3 resulta em 9. Divide dois valores. 8/2 resulta em 4. Retorna a parte inteira do resultado 17\2 resulta em 8. de uma divisão. Retorna o resto de uma divisão. 17 Mod 2 resulta em 1. Valores não inteiros utilizados na 19 Mod 4 resulta em 3. divisão são arredondados. 19 Mod 4.2 resulta em 3. Calcula a exponenciação. 3^3 resulta em 27.

Operadores, variáveis, constantes e arrays

3

3.1.2. Operadores de comparação Os operadores de comparação, descritos e exemplificados na tabela a seguir, são utilizados para comparar valores de duas expressões, retornando um resultado True (para comparações verdadeiras), False (para comparações falsas) ou Null (caso uma das expressões utilizadas na comparação contenha dados inválidos): Operador = <> > < >= <= Is Like

Descrição

Exemplos Igual a. 20=15+15 resulta em False. Diferente de. 25<>20+20 resulta em True. Maior que. 50>70-25 é igual a True. Menor que. 20<10+10 resulta em False. Maior ou igual a. 50>=10*7 é igual a False. Menor ou igual a. 30<=15+15 resulta em True. Objeto Is Var resulta em True, Compara duas variáveis supondo que Objeto seja igual a de referência a objetos. X e que X seja igual a Var. Compara duas sequências "FnnnF" Like "F*F" resulta em de caracteres. True.

3.1.3. Operadores lógicos Os operadores lógicos são utilizados em operações lógicas e, assim como os operadores de comparação, retornam um resultado True, False ou Null. A próxima tabela descreve esses operadores: Operador

And

Descrição Acrescenta condições a um teste lógico. Retorna True caso todas as condições sejam verdadeiras, False caso uma delas seja falsa e Null caso uma delas seja nula.

Exemplos 12>5 And 8<7 resulta em False. 12>5 And 8>7 resulta em True.

133


Operador

Or

Not

Eqv

Xor

Descrição Acrescenta condições em um teste lógico, assim como o operador And. Porém, retorna True caso uma ou mais condições sejam verdadeiras, False caso todas sejam falsas e Null caso uma delas seja nula. Inverte a lógica de uma expressão, criando uma negação lógica. Verifica uma equivalência lógica, retornando True caso as duas expressões sejam verdadeiras ou falsas e False ou Null caso uma delas seja nula. Faz uma exclusão lógica, retornando True caso uma expressão seja verdadeira e a outra, falsa. Caso as duas expressões sejam verdadeiras ou falsas, retorna False, e caso uma delas seja nula, retorna Null.

Exemplos

12>5 Or 8<7 resulta em True.

Not 12>5 resulta False.

em

12>5 Eqv 8>5 resulta em True. 12<5 Eqv 8<5 resulta em True. 9>7 Xor 7<5 resulta em True. 9>7 Xor 7>5 resulta em False.

3.1.4. Operadores de concatenação O operador de concatenação do VBA é o & (e comercial). A concatenação é utilizada para criar um único texto a partir da associação de duas ou mais sequências de caracteres de texto. Para exemplificar, podemos imaginar a concatenação de dois valores de texto: um fixo, como Bem-vindo, (inclusive com a vírgula), e outro variável, como um nome que é preenchido em um campo. Supondo que o nome preenchido seja Marcos, a concatenação "Bem-vindo, "&"Marcos"&"!" resulta no texto Bem-vindo, Marcos!. Podemos dizer que o operador de concatenação também tem a função de unir valores distintos, como o conteúdo de um texto com a data definida pelo sistema operacional da máquina, ou ainda exibir em uma única resposta, uma string com o resultado de uma variável.

134


3

No exemplo a seguir, será exibida, na barra de status do Excel, a palavra data, seguida da data do sistema: Sub Exibe_Data() Application.StatusBar = "Data: " & Date End Sub

Para exibir uma Caixa de Mensagem com diversas linhas, utilizamos a instrução vbCrLf para realizar a quebra de linhas. Desta forma, utilizaremos o símbolo concatenador para unir o texto com o comando de quebra de linha. Sub mensagem() MsgBox "Data: " & Date & vbCrLf & "Bem Vindo ao Sistema!" End Sub

Ao executar a macro, teremos o resultado a seguir:

3.1.5. Ordem das operações A ordem em que as operações ocorrem no VBA é a mesma do Excel: primeiramente, são calculadas todas as exponenciações, em seguida, são feitas as multiplicações e divisões e, por último, as adições e subtrações. Qualquer operação envolvida por parênteses, porém, é resolvida antes de qualquer outra. Assim, a expressão 5+3*2 resulta em 11, enquanto a expressão (5+3)*2 traz como resultado o número 16.

Os parênteses adicionais ou desnecessários (5+(3*2), por exemplo) não afetam os cálculos e podem ser utilizados como um recurso para facilitar o entendimento do código.

135


3.2. Variáveis As variáveis são amplamente utilizadas em muitas linguagens de programação, inclusive no Visual Basic. Uma variável é um espaço nomeado da memória, utilizado para armazenar um valor temporariamente.

O nome de uma variável deve ser iniciado por um caractere alfabético e conter, no máximo, 255 caracteres. Para atribuir informação em uma variável, devemos digitar o nome da variável seguido pelo sinal = (igual) mais o conteúdo desejado. Caso a informação a ser atribuída seja alfanumérica, devemos colocá-la entre aspas. Em se tratando de informação numérica, devemos apenas digitar o número desejado. Observe o exemplo adiante: nome="Ana Paula" idade=18

Podemos usar o comando Msgbox para exibir o conteúdo da variável para o usuário. Neste caso, devemos apenas incluir o nome da variável (sem aspas) para que seu conteúdo seja exibido, conforme apresentado a seguir: Texto = "Impacta Tecnologia" Msgbox Texto

136


3

Também podemos exibir mais de uma variável dentro da caixa de mensagem, juntam jun tament entee com com um tex texto to qua qualqu lquer. er. Considere o seguinte conteúdo a ser exibido na caixa de mensagem: Nome: Ana Paula Idade: 18 anos

Neste caso, devemos escrever o seguinte código: Nome = "Ana Paula" Idade = 18 Msgbox "Nome: " & Nome & vbCrLf & "Idade: " & Idade & " anos"

E o resultado será este:

Neste último caso, foi utilizado o operador de concatenação & e o comando vbCrLf para para efetuar a quebra de linha dentro da caixa de mensagem. É possível definir variáveis a partir de valores armazenados em outras variáveis: Sub calcula_total() qtde = InputBox("Digite a quantidade") preco = 10.25 Total = qtde * preco MsgBox "Valor a pagar = " & Total End Sub

137


A variável total irá retornar o resultado do produto dos valores contidos nas variáveis qtde e preco preco,, obtendo, assim, o conteúdo numérico resultante da quantidade que for digitada multiplicada pelo valor 10.25 que está na variável preco.. Veja, no exemplo a seguir, o resultante de ter digitado 2 para quantidade: preco

3.2.1. Declarando variáveis As variáveis precisam ser declaradas antes de serem utilizadas, ou seja, precisam ter seu tipo definido. A definição do tipo de uma variável otimiza a utilização do espaço da memória, pois cada um dos tipos ocupa quantidades de espaço diferentes, de acordo com os dados que vão armazenar. Isso faz com que a memória seja ocupada de acordo com as necessidades reais de armazenamento de informações. Os tipos definem como os dados são armazenados na memória: como data, como número inteiro ou como número decimal, por exemplo. O VBA possui recursos para manipular essas informações automaticamente, mas com algumas desvantagens, como lentidão na execução e um uso ineficiente de memória (todas as variáveis não declaradas assumem o tipo ti po Variant Variant,, que pode armazenar qualquer tipo de dado, mas faz com que o VBA reserve mais memória que o necessário e faça verificações que consomem muito tempo). Por esse motivo, o ideal é que as variáveis sejam sempre declaradas, principalmente, nas aplicações mais complexas.

138


3

A próxima tabela descreve os tipos de d e variáveis, lista seus valores permitidos e o tamanho que ocupam na memória: Tipo Byte Boolean Integer Long Single (vírgula flutuante de precisão simples) Object Date

Valores De 0 a 255. True ou False False.. De -32.768 a 32.767. De -2.147.483.648 a 2.147.483.647. 2.147.483.647 . Para valores negativos, de -3,402823E38 a -1,401298E-45; para valores positivos, de 1,401298E-45 a 3,402823E38.

Qualquer referência a um objeto. De 01/01/0100 a 31/12/9999. Para valores negativos, de Double (vírgula -1,7976931348623 -1,79769313486231E308 1E308 a flutuante de -4,94065645841247E-324; -4,9406564584124 7E-324; para valores dupla precisão) positivos, de 4,94065645841247E-3 4,94065645841247E-324 24 a 1,79769313486232E308. Currency De -922.337.203.685.477,5808 a (número inteiro 922.337.203.685.477,5807. em escala) String (comprimento variável)

Decimal

Variant (numérica)

De 0 a 2.000.000.000 de caracteres (aproximadamente).

Tamanho 1 byte 2 bytes 2 b y te s 4 bytes 4 bytes 4 bytes 8 bytes

8 bytes

8 bytes 10 bytes somados ao comprimento da sequência.

+/79.228.162.514.264.337.593.543.950.335 (sem vírgula decimal) e +/7,9228162514264337593543950335 12 bytes (com 28 casas decimais à direita). O menor número diferente de zero é +/0,0000000000000000000000000001. Qualquer número até o intervalo de um 16 bytes Double.. Double

139


Tipo Variant (texto)

Valores

Tamanho 22 bytes De 0 a 2.000.000.000 de caracteres somados ao (aproximadamente). comprimento da sequência. Definido pelo De 1 a 65.400 caracteres (aproximadamente). comprimento da sequência.

String (comprimento fixo) Definido pelo usuário (usando Depende da definição. Type)

Definido pelo tipo de dado.

No exemplo a seguir, a variável media receberá o resultado do cálculo da média aritmética entre 4 notas inseridas nas variáveis nota1, nota2, nota3 e nota4. Observe que as variáveis foram declaradas como single single,, que permite aceitar valor decimal de precisão simples. Sub calcula_media() Dim nota1 As Single, nota2 As Single, nota3 As Single, nota4 As Single, media As Single nota1 = 10 nota2 = 5.5 nota3 = 3 nota4 = 8.5 media = (nota1 + nota2 + nota3 + nota4) / 4 Range("a2") = media End Sub

O resultado inserido na célula A2 será 6,75 6,75..

As variáveis podem ser declaradas nos níveis de procedimento, de módulo ou de projeto. Conheceremos, a seguir, a declaração em cada um desses níveis.

140


3

3.2.1.1.Declarando no procedimento Declarar uma variável no nível de procedimento significa declará-la dentro do procedimento em que ela será utilizada. Assim, nenhum outro procedimento, mesmo que seja do mesmo módulo, será capaz de utilizar essa variável. Para declarar uma variável no nível de procedimento, devemos inserir a instrução Dim,, o nome da variável, a palavra-chave As Dim As e e um tipo, como no exemplo a seguir. Caso digitemos apenas a instrução Dim Dim e e o nome da variável, seu tipo será definido como Variant Variant.. Dim As

Considere o seguinte exemplo: Dim variavel_1 As Decimal Dim valor As Decimal

Após declarar a variável, é necessário definir seu valor. Se definirmos um valor incompatível com o tipo declarado, será exibido um erro de execução da macro. Para variáveis do tipo Object Object,, devemos atribuir a referência do objeto, utilizando a instrução Set Set seguida seguida do nome do objeto, de um sinal de igual (= ( =) e da referência do objeto, como exibe a sintaxe a seguir:

O exemplo a seguir irá exibir em uma caixa de mensagem o valor contido na célula A4 A4,, pertencente na planilha denominada Plan1 Plan1.. Sub exibe_msg() Dim celula as Object Set celula = worksheets("plan1" worksheets("plan1").range("a4") ).range("a4") Msgbox "O Conteúdo da célula A4 é: " & celula End Sub

141


Considere o exemplo a seguir, em que a célula ativa (selecionada) estará recebendo o valor contido na célula B5 B5,, pertencente à planilha Dados Dados:: Sub insere_conteudo() Dim conteudo As Object Set conteudo = Worksheets("Dados Worksheets("Dados").Range("b5") ").Range("b5") ActiveCell.Value = conteudo.Value End Sub

Podemos definir outras propriedades de um objeto na célula ativa, como o procedimento a seguir, que formata o estilo da fonte e a cor do preenchimento:

142


3

Podemos fazer com que o objeto deixe de ser associado ao nome que definimos, utilizando a instrução Set seguida do nome do objeto, de um sinal de igual (=) e do valor definido como Nothing, como o próximo exemplo, que dissocia a variável nome_da_planilha e o objeto ao qual fazia referência: Set nome_da_planilha = Nothing

As variáveis em geral têm seus valores restaurados após o término do procedimento. É possível, porém, declarar uma variável como estática, através da palavra-chave Static, como mostra a sintaxe a seguir. As variáveis desse tipo só têm seus valores restaurados quando a pasta de trabalho é fechada. Static as

Considere o seguinte exemplo: Sub Incremento() Static num As Integer MsgBox num num = num + 1 End Sub

No exemplo anterior, podemos observar que cada vez que a macro for executada, o valor 1 será somado ao conteúdo da variável. Neste caso, diferentemente dos demais tipos de variáveis, a variável do Static mantém o conteúdo mesmo após a execução da macro em questão.

Assim como as outras variáveis declaradas no nível de procedimento, as variáveis estáticas não podem ser utilizadas por outros procedimentos, mesmo que façam parte do mesmo módulo.

143


3.2.1.2. Declarando no módulo As variáveis declaradas no nível do módulo podem ser utilizadas por qualquer procedimento que faça parte do módulo. Para declarar uma variável no módulo, devemos utilizar a instrução Private e a seção Declarações, localizada na parte superior do módulo. Uma linha horizontal é exibida automaticamente abaixo da declaração.

Verificamos, então, que o valor da variável Curso, o qual foi atribuído na macro Nome_Curso, será utilizado na macro Dados.

Para efeitos de compatibilidade com versões anteriores, a instrução Dim também pode ser utilizada para declarar variáveis no nível do módulo.

144


3

3.2.1.3.Declarando no projeto As variáveis declaradas no nível do projeto podem ser utilizadas por quaisquer módulos que façam parte do projeto. Para declarar uma variável no projeto, devemos utilizar a instrução Public e a seção Declarações, localizada na parte superior do módulo. Podemos criar a variável curso em um dos módulos do projeto, utilizando a seguinte instrução: Public curso As String

Considere, ainda, a seguinte macro: Sub atribui_valor() curso = "Excel VBA" End sub

Após esta macro ser executada, o valor da variável será "VBA". Como a variável curso foi definida como Public, poderemos utilizá-la em qualquer outro módulo do projeto, como no exemplo a seguir: Sub utiliza_variavel() Dim nomealu as string Nomealu = inputbox("Digite o nome do aluno") ange("b2") = nomealu Range("b3") = curso End sub

145


3.2.1.4.Option Explicit É possível que um erro de digitação faça com que o VBA crie uma nova variável durante o procedimento, em vez de redefinir seu valor anterior. Isso faz com que essa variável, por não estar declarada, assuma o tipo Variant, ocupando um espaço desnecessário da memória, e que a variável anterior não tenha seu valor redefinido. Para evitar esse tipo de problema, podemos aplicar uma obrigatoriedade de declaração de variáveis, por meio da instrução Option Explicit no início do módulo, antes da declaração de procedimentos. Ao fazer isso, o código não será capaz de executar uma variável não declarada, o que evita a ocorrência de erros. A instrução Option Explicit só se aplica ao módulo em que foi inserida. Portanto, normalmente, é necessário inserir a instrução em cada um dos módulos em que se deseja tornar a declaração de variáveis obrigatória. É possível, porém, configurar a caixa de diálogo Opções, exibida a seguir, para fazer com que todos os módulos possuam a instrução Option Explicit na seção Declarações. Para isso, basta clicarmos em Opções, disposta no menu Ferramentas, e ativarmos a opção Requerer declaração de variável:

146


3

3.3. Macros com passagem de parâmetros Podemos passar parâmetros entre macros. Veja o seguinte exemplo: Public Preco_Venda Sub Precificacao() custo = InputBox("informe o Custo do Produto?") margem = 2 Call Calculo_Preco_Venda(custo, margem) MsgBox Preco_Venda End Sub Sub Calculo_Preco_Venda(custo, margem) Preco_Venda = custo * margem End Sub

Aqui, temos duas macros. Note que a macro Precificacao, através do comando Call, chama a macro Calculo_Preco_Venda enviando dois parâmetros: custo e margem. A macro Calculo_Preco_Venda recebe esses dois parâmetros, executa o cálculo do Preco_Venda (variável pública) e volta para a macro Preco_Venda. É muito comum uma macro chamar a outra, para isso, usamos Call e, assim, ao chamar outra macro, podemos enviar parâmetros.

3.4. Constantes Assim como em várias linguagens de programação, temos constantes no VBA também. Assim como as variáveis, as constantes são um espaço reservado na memória RAM do computador com a finalidade de armazenar, guardar valor(es). Porém, como o próprio nome indica, uma constante não pode ser alterada ao longo do código.

147


Usamos Const como instrução para declarar uma constante e definir o seu valor. As regras são as mesmas para a criação de nomes de variáveis.

É muito comum termos constantes públicas utilizadas em diversas macros do projeto.

3.5. Arrays Os arrays, suportados pela maioria das linguagens de programação, são variáveis indexadas, sendo assim, armazenam elementos que são referenciados por um índice. O índice de uma variável array define quantos dados serão armazenados, bem como a posição de cada elemento em sequência. Todos os elementos contidos dentro de arrays devem ser do mesmo tipo de dado. Para referenciar uma dessas variáveis no array, basta utilizar o nome desta seguido de um número de índice. Para exemplificar, podemos imaginar um array chamado dias_da_semana que possua sete variáveis armazenadas do tipo String, uma para cada dia da semana, e referenciar cada elemento como dias_da_semana(1), dias_da_semana(2), dias_da_semana(3) e assim por diante.

148


3

Veja exemplos de arrays:

Array tamanho 4 (unidimensional) Array tamanho 2x3 (bidimensional)

Os arrays, assim como as variáveis, precisam ser declarados antes de serem utilizados. Podemos declará-los inserindo uma instrução Dim ou Public, mais o nome do array e os números do primeiro e último índices, seguidos da palavrachave As e de um tipo de dado. O exemplo a seguir cria um array chamado arrayexemplo de 50 números inteiros: Dim arrayexemplo(1 to 50) As Integer

A quantidade de elementos de um array é definida de acordo com os números que inserimos dentro dos parênteses. Devemos inserir o número do primeiro índice, a palavra-chave to e o número do último índice. É possível especificar apenas o último índice, caso em que o VBA assume 0 como o valor inicial. Os dois exemplos a seguir criam arrays de 51 elementos: Dim arrayexemplo(0 to 50) As Integer Dim arrayexemplo(50) As Integer

Ao executarmos os códigos adiante, observamos que a macro exemplo1 exibe, na caixa de mensagem, a informação "Quinta-Feira", contida no índice número 5. Nesse caso, foi indicado o valor inicial do índice como 1.

149


O exemplo2, por sua vez, exibirá a informação "Sexta-Feira", visto que o índice iniciou-se com o valor zero: Sub exemplo1() Dim dia_semana(1 To 7) As String dia_semana(1) = "Domingo" dia_semana(2) = "Segunda-Feira" dia_semana(3) = "Terça-Feira" dia_semana(4) = "Quarta-Feira" dia_semana(5) = "Quinta-Feira" dia_semana(6) = "Sexta-Feira" dia_semana(7) = "Sábado" MsgBox dia_semana(5) End Sub Sub exemplo2() Dim dia_semana(6) As String dia_semana(0) = "Domingo" dia_semana(1) = "Segunda-Feira" dia_semana(2) = "Terça-Feira" dia_semana(3) = "Quarta-Feira" dia_semana(4) = "Quinta-Feira" dia_semana(5) = "Sexta-Feira" dia_semana(6) = "Sábado" MsgBox dia_semana(5) End Sub

Podemos inserir a instrução Option Base 1 na seção Declarações de um módulo para que o VBA assuma 1 como o valor inicial. Com essa instrução, os dois exemplos a seguir criariam arrays de 50 elementos: Dim arrayexemplo(1 to 50) As Integer Dim arrayexemplo(50) As Integer

150


3

Com a execução do exemplo adiante, será exibido o mês de Fevereiro na caixa de mensagem:

3.5.1. Arrays multidimensionais Além dos arrays unidimensionais, como os criados nos exemplos anteriores, podemos criar arrays multidimensionais. Apesar de os arrays multidimensionais mais utilizados serem os que contêm duas ou três dimensões (que podem ser imaginadas como matriz e cubos, respectivamente), é possível criar arrays de até 60 dimensões no VBA. Os elementos de arrays multidimensionais são referenciados a partir da especificação de dois números, como ilustra o exemplo a seguir, que designa um valor para um elemento de um array multidimensional: Sub predio() Dim andar(2, 2) As String andar(0, 0) = "Maria" andar(0, 1) = "Matheus" andar(0, 2) = "Marina" andar(1, 0) = "Jorge" andar(1, 1) = "José" andar(1, 2) = "João" andar(2, 0) = "Pedro" andar(2, 1) = "Paulo" andar(2, 2) = "Patrícia" MsgBox "Moradores do primeiro Andar:" & vbCrLf & _ andar(1, 0) & "-" & andar(1, 1) & "-" & andar(1, 2) End Sub

151


Foram inseridos dois elementos na matriz andar, cada qual contendo três índices (0, 1 e 2). Podemos supor, então, que o primeiro elemento corresponda aos andares e o segundo, aos apartamentos, ou por linhas e colunas se formos pensar apenas na matriz em si. Assim, teríamos como primeiro elemento: índice 0 (térreo), índice 1 (primeiro andar) e índice 2 (segundo andar).

É possível especificar a quantidade de índices de uma macro multidimensional, conforme apresentado adiante: Sub regiao() Dim area(1 To 2, 1 To 2) As area(1, 1) = 11 area(1, 2) = 12 area(2, 1) = 21 area(2, 2) = 22 Range("a1").Value = area(1, Range("a2").Value = area(1, Range("b1").Value = area(2, Range("b2").Value = area(2, End Sub

Integer

1) 2) 1) 2)

Podemos utilizar a instrução Option Base 1, assumindo o valor 1 como sendo o valor inicial de um array, em vez de zero.

152


3

3.5.2. Arrays dinâmicos Os arrays dinâmicos se diferenciam dos demais por não possuírem um número predeterminado de elementos. A declaração de um array dinâmico é feita como no exemplo a seguir, em que não são inseridos índices nos parênteses: Dim arrayexemplo() As Tipo

Antes de utilizarmos um array dinâmico, devemos utilizar a instrução ReDim, que define a quantidade de elementos do array, quantia esta normalmente determinada durante a execução do código. Essa instrução pode ser utilizada quantas vezes forem necessárias para redimensionar o array, como no exemplo a seguir, que considera que a variável resultado possui um valor calculado anteriormente pelo código: ReDim arrayexemplo(resultado)

Por padrão, a instrução ReDim exclui os valores armazenados anteriormente nos elementos do array. Para evitar esse comportamento, podemos utilizar a palavra-chave Preserve, como no próximo exemplo: ReDim Preserve arrayexemplo(resultado)

Supondo que o array arrayexemplo possua atualmente sete elementos e que a variável resultado possua um valor atual igual a 10, a instrução ReDim redimensionará o array, que passará a ter dez elementos e manterá os sete anteriores. Caso a variável resultado seja igual a 5, o array terá sua a quantidade de elementos diminuída, mantendo apenas os cinco primeiros elementos dos sete que tinha anteriormente.

153



•

•

•

•

•

•

•

154

O VBA possui quatro categorias de operadores: aritméticos, de comparação, lógicos e de concatenação; A ordem em que as operações ocorrem no VBA é a mesma utilizada pelo Excel: primeiramente, são calculadas todas as exponenciações, em seguida, são feitas as multiplicações e divisões e, por último, as adições e subtrações. Qualquer operação envolvida por parênteses, porém, é resolvida antes de qualquer outra; Uma variável é um espaço nomeado da memória utilizado para armazenar um valor temporariamente; Os arrays, suportados pela maioria das linguagens de programação, são variáveis indexadas, sendo assim, armazenam elementos que são referenciados por um índice; Por padrão, a contagem das posições de uma variável array começa em zero, mas podemos iniciá-la com a posição 1; O comando Option Base 1 na seção Declarações de um módulo faz com que o VBA assuma 1 como o valor inicial; Os arrays possuem dimensões; Arrays podem ser dinâmicos, isto é, podem ter seu tamanho redefinido utilizando a instrução ReDim, que define a quantidade de elementos do array, quantia esta normalmente determinada durante a execução do código.


3



1. Qual das alternativas a seguir corresponde ao operador de concatenação do VBA?

☐

a) &

☐

b) *

☐

c) /

☐

d) _

☐

e) Nenhuma das alternativas anteriores está correta.

2. Qual das alternativas a seguir está correta com relação a variáveis?

156

☐

a) É um espaço reservado na memória, utilizado para armazenar uma informação temporariamente.

☐

b) É obrigatória a sua criação caso a instrução Option Explicit tenha sido indicada no módulo.

☐

c) São utilizadas para executar macros passo a passo.

☐

d) As alternativas A e B estão corretas.

☐



3

3. Qual das alternativas a seguir está correta?

☐

a) A variável array serve apenas para inserção de texto.

☐

b) Não é possível inserir valor do tipo string em uma variável array.

☐

c) A variável array permite que sejam armazenados diversos itens em uma variável definida como um nome único.

☐

d) O limite máximo de argumentos em uma variável matricial é de 20 itens.

☐


4. Qual a sintaxe para exibir, em uma caixa de mensagem, o conteúdo de uma variável array, o qual está na posição 8?

☐

a) Dim nome(8) as integer

☐

b) Dim nome(8) as string

☐

c) Msgbox nome(8)

☐

d) Msgbox Redim Nome(8)

☐

e) Range(a2) = dim nome(8)

157


5. Para atribuirmos um valor em uma variável, podemos utilizar qual comando?

158

☐

a) Dim nome as Double, byte

☐

b) Option Base 2

☐

c) Msgbox nome(4)

☐

d) Nome(4) = “Ana”

☐

e) Não é possível atribuir valor em uma variável matriz.

Operadores, variáveis, constantes e arrays Mãos à obra!

3


Laboratório 1 A – Trabalhando com operadores matemáticos no VBA 1. Desenvolva as macros a seguir utilizando o VBA, com base na seguinte planilha:

•

Macro Calcular

Esta macro deverá realizar as seguintes ações: •

•

•

Aceitar dois números em uma caixa de entrada, os quais serão inseridos nas células B5 e B7, respectivamente; Calcular a soma, subtração, multiplicação e divisão entre os dois números, exibindo o resultado nas células E5, E7, E9 e E11.

Macro Limpar •

•

Esta macro deverá realizar a seguinte ação: Remover somente os valores contidos nas células (B5, B7, E5, E7, E9 e E11).

Utilize o método ClearContents.

160

Estruturas de decisão e repetição 9 9 9 9

Estruturas de repetição; GoTo; Estruturas de decisão; Tratamento de erros.


4.1. Introdução O fluxo da maioria dos procedimentos do VBA é a execução do código linha a linha, a partir do início. Pode surgir, porém, a necessidade da execução de apenas alguns procedimentos, ou da execução um procedimento várias vezes de acordo com condições preestabelecidas, casos em que utilizamos estruturas de decisão e de repetição. Neste capítulo, abordaremos justamente as diferentes instruções que permitem formar estruturas de decisão e repetição. Por exemplo, a instrução GoTo, que muda o fluxo do programa, Loop, que repete uma ação por um número determinado de vezes, de acordo com uma condição, e instruções utilizadas para retornar um valor de acordo com o resultado de um teste lógico, como as instruções Select Case e If .

4.2. Estruturas de repetição A instrução Loop é utilizada para repetir a execução de blocos de código VBA por um número de vezes determinado previamente ou pelo valor de uma variável. Existem três tipos de estruturas que podem ser criadas com a instrução Loop:

162

•

Do...Loop;

•

Do While...Loop;

•

Do Until...Loop.

Estruturas de decisão e repetição

4

4.2.1. Do...Loop A estrutura Do...Loop pode utilizar a palavra-chave While para determinar que um conjunto de instruções será executado enquanto uma condição for verdadeira, ou a palavra-chave Until para determinar que um conjunto de instruções será executado enquanto uma condição for falsa. A sintaxe dessa estrutura é a seguinte: Do {While | Until} Condição Instruções Exit Do Instruções Loop

Essa estrutura é dividida em duas partes: •

•

Condição (opcional): Uma expressão numérica ou de sequência que seja igual a True ou False (condições Null são tratadas como False); Instruções: Uma instrução ou um conjunto de instruções que serão executadas repetidamente enquanto Condição for igual a True, ou até que isso aconteça.

Podemos utilizar a instrução Exit Do para fazer com que o fluxo da estrutura Do...Loop seja interrompido, caso em que a execução continua a partir da linha abaixo da instrução Loop. Nos casos de estruturas Do...Loop aninhadas, uma instrução Exit Do transfere a execução para a estrutura que está no nível imediatamente superior ao atual.

163


4.2.1.1.Do While...Loop A estrutura Do While...Loop permite fazer com que um conjunto de instruções seja executado enquanto uma condição for verdadeira. Essa estrutura tem duas sintaxes possíveis. •

Condição testada no início do loop

Com essa sintaxe, exibida a seguir, as instruções são executadas apenas se a condição for verdadeira (caso a condição seja falsa, a execução é iniciada a partir da linha abaixo da instrução Loop): Do While Condição Instruções Loop

Exemplo de um contador de 0 a 10: Sub do_while() Dim n As Byte Do While n <= 10 MsgBox n n = n + 1 Loop End Sub

Considere a seguinte planilha, em que deve ser calculado o total de cursos vendidos de Excel VBA:

164


4

O código a seguir descreve o procedimento que terá início na célula B2 e executará as instruções dentro do laço Do While enquanto a célula ativa estiver preenchida, ou seja, diferente de vazio. Em cada célula selecionada, será verificado se o conteúdo é igual a Excel VBA e, em caso afirmativo, será acrescido, na variável TOT, o valor correspondente, o qual está na célula da coluna ao lado da célula ativa (coluna C). Depois de verificada a consistência e independente da célula ativa ter o conteúdo Excel VBA ou não, será posicionado na célula posterior (próxima linha) e repetirá o processo novamente. O laço será encerrado somente quando a célula ativa (pertencente a coluna B) for vazia. Observe o exemplo: Sub Vendas_While() Dim tot As Integer Range("b2").Select 'faça enquanto célula ativa diferente de vazio Do While ActiveCell <> "" If ActiveCell = "Excel VBA" Then tot = tot + ActiveCell.Offset(0, 1) End If ActiveCell.Offset(1, 0).Select Loop MsgBox "Total Vendido (Excel VBA) = " & tot End Sub

Ao executarmos a macro, teremos o resultado a seguir:

165


•

Condição testada após as instruções

A sintaxe a seguir executa todas as instruções e testa a condição. Enquanto a condição for verdadeira, o loop será executado repetidamente: Do Instruções Loop While Condição

No exemplo adiante, serão executados os comandos dentro da estrutura Do, pelo menos uma vez. Isto porque primeiramente são executadas as instruções antes de efetuar a comparação. Sub do_while2() Dim n As Byte n = 20 Do MsgBox n n = n + 1 Loop While n <= 10 End Sub

A mensagem contendo o valor da variável n é exibida, sendo que o valor 1 será acrescido na variável que ficará com o valor 21. Se a variável for menor ou igual a 10, o procedimento não será satisfatório, encerrando, então, o laço.

4.2.1.2.Do Until...Loop A estrutura Do Until...Loop executa instruções enquanto uma condição for falsa. Da mesma forma que Do While...Loop, essa estrutura possui duas sintaxes possíveis: •

Condição testada no início do loop Do Until Condição Instruções Loop

166


4

Assim como no exemplo anterior, que utiliza a estrutura Do...While, no exemplo adiante será exibido um contador de 0 a 10. Neste caso, observamos a condição Faça até o valor da variável > 10, ou seja, somente quando a condição for satisfatória, o procedimento sairá do laço Do...Until. Sub do_until() Dim n As Byte Do Until n > 10 MsgBox n n = n + 1 Loop End Sub

Utilizando o laço Do..Until, observe o exemplo para calcular o total de cursos Excel VBA. Este exemplo foi demonstrado anteriormente com a utilização do laço Do..While. Perceba que a condição do laço Do..Until é inversa à condição utilizada no laço Do..while, porém, ambos os códigos terão o mesmo objetivo e apresentarão o mesmo resultado. Sub Vendas_Until() Dim tot As Integer Range("b2").Select 'faça até celula ativa for nula (vazia) Do Until ActiveCell = "" If ActiveCell = "Excel VBA" Then tot = tot + ActiveCell.Offset(0, 1) End If ActiveCell.Offset(1, 0).Select Loop MsgBox "Total Vendido (Excel VBA) = " & tot End Sub

167


•

Condição testada após as instruções Do Instruções Until Loop Condição

Para este caso, considere o seguinte exemplo: Sub do_until2() Dim n As Byte n = 8 Do MsgBox n n = n + 1 Loop Until n > 10 End Sub

Observe que o loop continua fazendo o cálculo, até que o número seja maior que 10.

4.2.2. While...Wend A estrutura While...Wend funciona da mesma forma que a estrutura Do While... Loop, com a exceção de que o teste da condição deve ser feito obrigatoriamente no início, como na sintaxe a seguir: While Condição Instruções Wend

Considere o seguinte exemplo: Sub while_wend() Dim n As Byte While n <= 10 MsgBox n n = n + 1 Wend End Sub

168


4

A condição é verificada repetidamente e, sempre que for igual a True True,, as instruções localizadas antes da instrução Wend Wend serão serão executadas. Se a condição for igual a False False,, a execução continuará a partir da linha abaixo da instrução Wend.. Wend Essa sintaxe é dividida em duas partes: •

•

Condição (obrigatória): Uma expressão numérica ou de sequência que seja igual a True True ou ou False False (condições (condições Null Null são são tratadas como False False); ); Instruções (opcional): Uma instrução ou um conjunto de instruções que serão executadas repetidamente enquanto Condição Condição for for igual a True True..

While...Wend; cada instrução É possível aninhar loops While...Wend; Wend corresponde Wend While mais corresponde à instrução While mais recente.

4.2.3. For...Next e For Each...Next O loop For...Next For...Next é é utilizado para definir a quantidade de vezes que um conjunto de instruções será repetido. Esse loop precisa de um contador (uma variável numérica) com um valor inicial, um valor final e um passo, como na sintaxe a seguir: For Contador = Valor inicial To Valor final Step Valor passo Instruções Next

169


Cada vez que o bloco de código entre as instruções For For e e Next Next é é executado, o contador, que é iniciado com o valor definido como inicial, inicial , é incrementado pelo valor definido como passo. O loop é executado quantas vezes forem necessárias para que o contador atinja seu valor final. Para exemplificar, se o valor inicial é 10,, o valor final é 20 10 20 e e o incremento é 2, as instruções do bloco entre o contador e a instrução Next Next serão serão executadas 6 vezes: Sub for_next() Dim n As Byte For n = 10 To 20 Step 2 MsgBox n Next End Sub

O valor inicial pode ser qualquer número, negativo ou positivo, inclusive 0 inclusive 0,, e o passo assume o valor 1 caso não seja especificado um valor diferente. Para contar o total vendido do curso Excel VBA, VBA, considere o exemplo a seguir, com a utilização do laço For...Next: Sub Vendas_For() Dim tot, lin As Integer Range("b2").Select 'executar da linha 2 até 10 For lin = 2 To 10 Cells(lin, 2).Select If ActiveCell = "Excel VBA" Then tot = tot + ActiveCell.Offset( ActiveCell.Offset(0, 0, 1) End If Next MsgBox "Total Vendido (Excel VBA) = " & tot End Sub

170


4

lin dentro Cells para Foi utilizada a variável lin dentro do comando Cells para efetuar o deslocamento das linhas a cada passagem For..Next.. pelo laço For..Next O loop For Each...Next funciona Each...Next funciona da mesma forma que o loop For...Next For...Next,, porém, o número de vezes que as instruções serão executadas é determinado pelo número de elementos de uma matriz ou coleção (como uma coleção de pastas de trabalho, células ou planilhas). A sintaxe desse loop é a seguinte: For Each Elemento In Grupo Instruções Exit For Instruções Next

Em que: •

•

•

Elemento (obrigatória): Variável que fará a iteração dos elementos de uma Elemento (obrigatória): matriz, caso em que deve ser obrigatoriamente do tipo Variant Variant;; ou coleção, caso em que pode ser uma variável de objeto genérica ou específica ou do tipo Variant Variant;; Grupo (obrigatória): O nome de uma matriz de objetos que não seja uma Grupo (obrigatória): matriz de tipos definidos pelo usuário; ou o nome de uma coleção; Instruções (opcional): Uma instrução (ou um conjunto de instruções) para Instruções (opcional): ser executada em cada item de Grupo Grupo.. 171


A instrução Exit For pode For pode ser utilizada para interromper um loop em qualquer ponto (normalmente é utilizada após a verificação de alguma condição), fazendo com que a execução continue a partir da linha abaixo de Next Next..

For...Each...Next não A instrução For...Each...Next não pode ser empregada com uma matriz de tipos definidos pelo usuário. Isto porque, ao Elemento deve ser uma variável do utilizarmos matrizes, Elemento Variant,, a qual não pode conter um tipo definido pelo tipo Variant usuário. Quando o loop é inserido, todas as instruções do bloco são executadas para o primeiro elemento de Grupo Grupo,, em seguida, para o segundo elemento e, assim, sucessivamente. Quando não houver mais elementos em Grupo Grupo,, a execução sai do loop e continua a partir da instrução abaixo da instrução Next Next.. Vale lembrar que o bloco For...Each For...Each só só é inserido se Grupo Grupo contiver contiver pelo menos um elemento. É possível aninhar loops For...Each...Next For...Each...Next,, mas cada elemento do loop deve ser exclusivo. Caso a instrução Next Next não não especifique Elemento Elemento,, a execução das instruções continua, como se Elemento Elemento estivesse estivesse incluído.

Um erro é exibido quando uma instrução Next é For correspondente. encontrada antes da instrução For correspondente.

172


4

No exemplo adiante, a variável denominada celula celula percorrerá percorrerá a região (grupo) de células, de B2 B2 à à B10 B10,, verificando se o conteúdo contido na célula é a palavra Excel VBA. VBA. Caso a condição seja atendida, a cor interior da célula será formatada para vermelho. Podemos usar a mesma planilha dos exemplos anteriores. Sub for_each() Dim celula As Object For Each celula In Range("B2:B10") If celula.Value = "Excel VBA" Then celula.Interior.ColorIndex celula.Interior.Col orIndex = 3 End If Next End Sub

Veja o resultado:

Observe o exemplo para alterar a visualização do zoom em todas as planilhas do arquivo. O objeto a ser percorrido, neste caso, é o Worksheets Worksheets (planilhas). (planilhas). Sub altera_zoom() Dim plan As Object 'variável plan irá percorrer todas as planilhas For Each plan In Worksheets plan.Select ActiveWindow.Zoom = 150 Next End Sub

173


4.3. GoTo A instrução GoTo GoTo faz faz com que uma instrução especificada por um rótulo de linha seja executada, ignorando outras possíveis instruções no fluxo normal dos procedimentos. Podemos especificar rótulos de linha para p ara quaisquer linhas do código, bastando inserir um nome alfanumérico iniciado por uma letra e terminado em dois-pontos (: (:). No exemplo de código adiante, quando uma condição for atendida, a instrução GoTo será GoTo será utilizada para executar a instrução identificada com o rótulo de linha Errologin:: Errologin Sub GoToTeste() Dim UserName as String UserName = InputBox ("Digite seu Nome: ") If UserName <> "Juliano Corinto" Then GoTo Errologin Endif MsgBox ("Olá, Juliano!") '...[seqüência do código]... Exit Sub Errologin: MsgBox "Acesso negado. Apenas Juliano Corinto tem permissão de acesso." End Sub

A função InputBox InputBox permite permite que o usuário digite um nome, que será verificado em seguida. Caso esse nome seja diferente de Jul de Julian iano o Cori Corinto nto,, a execução vai para o ponto identificado pelo rótulo de linha Errologin: Errologin:,, que exibe a mensagem do segundo comando MsgBox MsgBox.. Caso o nome digitado seja Jul seja Julian iano o Corinto Corinto,, a mensagem do primeiro comando MsgBox MsgBox será será exibida, o código terá sequência e será finalizado na instrução Exit Sub. Sub.

É importante notar que a leitura e interpretação de um código GoTo pode ser difícil. O VBA que possua muitas instruções GoTo oferece outras instruções mais adequadas e estruturadas para alterar o fluxo de execução dos procedimentos. É recomendável GoTo seja que a instrução GoTo seja utilizada apenas quando necessário (no rastreamento de erros, por exemplo).

174


4

4.4. Estruturas de decisão Podemos criar quatro tipos de estruturas de decisão com a instrução If : •

If...Then;

•

If...Then...Else;

•

If aninhado;

•

If combinado com os operadores And e Or.

4.4.1. If...Then A estrutura If...Then é utilizada quando é necessário executar um procedimento caso uma condição seja verdadeira. Se a condição for falsa, nenhum procedimento é executado. A seguir, vemos um exemplo da utilização da estrutura If...Then: If Condição Then Instruções

Essa estrutura é dividida em duas partes: •

•

Condição (obrigatória): Expressão que será avaliada como True ou False. O valor será considerado como True caso a expressão seja verdadeira, ou False caso a condição seja falsa ou nula; Instruções: Uma instrução (ou várias, separadas por dois-pontos) que será executada caso a condição retorne um valor True. As instruções são obrigatórias na forma de uma linha que não possua a cláusula Else e opcionais na forma de bloco.

175


Por exemplo: Sub If_Then() Dim n As Integer n = 5 If n < 10 Then MsgBox "menor que 10": InputBox ("Digite outro Número") End Sub

Podemos inserir várias instruções em uma única linha de código, logo após a palavra-chave Then, separadas por dois-pontos (:), como em If Condição Then Instrução1 : Instrução2 : Instução3, caso em que a instrução End If não é utilizada. O bloco de uma estrutura If...Then sempre é iniciado com If e encerrado com End If . Quando a condição inserida for verdadeira, todas as instruções entre essas duas cláusulas serão executadas. Caso a condição seja falsa, o código passa a ser executado a partir da linha abaixo da cláusula End If , e todas as instruções anteriores são ignoradas. A sintaxe utilizada é a seguinte: If Condição Then Instruções caso a condição seja verdadeira End If

O próximo exemplo possui uma condição que verifica se a variável Prc é maior que 15, caso em que seu valor será inserido na célula A5: Sub EstPreco() Dim Prc As Integer Prc = InputBox("Digite o valor do produto: ") If Prc > 15 Then Range("A5").Value = Prc End If Msgbox "Até Breve!" End Sub

176


4

Esse código, quando executado, exibirá uma InputBox, como na próxima imagem. Ao inserirmos qualquer valor maior que 15 e pressionarmos OK, faremos com que a condição seja verdadeira, e como resultado a célula A5 será preenchida com o valor digitado. Caso seja inserido um valor menor que 15, a célula permanecerá em branco, ou seja, nenhuma ação será executada.

Como o comando Msgbox está após a instrução End If , independente de a condição ser verdadeira ou não, será exibida a mensagem "Até Breve!".

É possível estruturar a função If em uma única linha de código. Sendo assim, não utilizamos a cláusula End If. If Prc > 15 Then Range("A5").Value = Prc Podemos inserir vários procedimentos para serem executados caso a condição seja verdadeira. Para isso, devemos acrescentar uma condição por linha, finalizando o bloco com a instrução End If .

177


O próximo exemplo contém instruções para inserir o valor da variável Prc na célula A5 e aplicar o estilo itálico a essa célula. Depois deverá somar o valor 50 na variável Prc e inserir este resultado na célula A6, alterar o tamanho da fonte dessa célula para 20 e exibir uma mensagem informando que a operação foi executada. Ao todo, são cinco instruções, uma em cada linha de código entre as instruções If e End If . Sub EstPreco() Dim Prc As Integer Prc = InputBox ("Digite o valor do produto: ") If Prc > 15 Then Range("A5").Value = Prc Range("A5").Font.Italic = True Range("A6") = Prc + 50 Range("A6").Font.Size = 20 MsgBox "Parabéns! Operação realizada com êxito." End If End Sub

A próxima imagem exibe o resultado desse código caso o valor inserido pelo usuário seja 25. A célula A5 é preenchida com o número 25, a célula A6 com 75, que é o resultado de 25+50, e exibe a mensagem informando que a operação foi realizada:

178


4

4.4.2. If...Then...Else A estrutura If...Then...Else permite inserir não apenas instruções para serem executadas caso as condições sejam verdadeiras, mas também instruções para serem executadas caso as condições sejam falsas. O bloco dessa estrutura também é iniciado com If e encerrado com End If , mas possui uma cláusula Else após as instruções para a condição verdadeira. Todas as instruções que forem inseridas entre as cláusulas Else e End If serão executadas apenas se a condição for falsa. A estrutura desse bloco é a seguinte: If Condição Then Instruções para a condição verdadeira Else Instruções para a condição falsa End If

A estrutura If...Then...Else é dividida em três partes: •

•

•

Condição (obrigatória): Expressão que será avaliada como True ou False. O valor será considerado como True caso a expressão seja verdadeira, ou False caso a condição seja falsa ou nula; Instruções para a condição verdadeira: Uma instrução (ou várias, separadas por dois-pontos) que será executada caso a condição retorne um valor True; Instruções para a condição falsa: Uma instrução (ou várias, separadas por dois-pontos) que será executada caso a condição retorne um valor False.

Para exemplificar a aplicação dessa estrutura, utilizaremos a função MsgBox para criar uma caixa de texto com os botões OK e Cancelar. De acordo com o botão escolhido pelo usuário, uma ação diferente será executada: o botão OK deve excluir todas as células e os dados de uma planilha, e o botão Cancelar deve finalizar o procedimento sem executar essa ação, exibindo apenas uma mensagem para o usuário.

179


O procedimento original para excluir todas as células de uma planilha seria o seguinte: Sub Apagar() Cells.Delete End Sub

O código exibido a seguir cria a variável Decisao, que assume um valor de acordo com a resposta do usuário na função MsgBox. Esse valor é utilizado na estrutura If...Then...Else: Sub Apagar() Dim Decisao As String Decisao = MsgBox ("Esta operação apagará todas as células e dados da planilha. Deseja prosseguir?", vbOKCancel + vbCritical, "Alerta") If Decisao = vbOK Then Cells.Delete MsgBox "Células e dados apagados." Else MsgBox "A operação foi anulada." End If End Sub

A função MsgBox possui vbOKCancel + vbCritical como segundo argumento para determinar, respectivamente, a exibição dos botões OK e Cancelar e a exibição do ícone de mensagem crítica. Quando esse procedimento for executado, a mensagem será exibida, como ilustra a próxima imagem:

180


4

Caso o usuário clique no botão OK, a variável Decisao armazenará o resultado vbOK (ou 1), o que fará com que a condição Decisao = vbOK seja verdadeira, caso em que todas as células e dados da planilha serão excluídos e que a mensagem Células e dados apagados será exibida.

Se o usuário clicar no botão Cancelar, a condição será falsa, o que fará apenas com que a mensagem A operação foi anulada seja exibida.

Podemos utilizar a função Msgbox sempre que quisermos obter o valor do botão pressionado pelo usuário. Neste caso, utilizamos uma variável que receberá esta informação.

181


•

ElseIf

Podemos utilizar a cláusula ElseIf para inserirmos várias condições para serem testadas. Neste caso, o código verifica cada condição e executa as instruções da primeira condição cujo resultado seja True. Caso nenhuma das condições seja verdadeira, ele executa a instrução (ou conjunto de instruções) abaixo da cláusula Else. Um bloco com ElseIf utiliza a seguinte estrutura: If Condição1 Then Instruções para o caso de Condição1=True ElseIf Condição2 Then Instruções para o caso de If=False e ElseIf=True Else Instruções para o caso de todas as condições anteriores =False End If

Em que: •

•

•

•

•

182

Condição1 (obrigatória): Expressão que será avaliada como True ou False. O valor será considerado como True caso a expressão seja verdadeira, ou False caso a condição seja falsa ou nula; Instruções para o caso de Condição1=True: Uma instrução (ou várias, separadas por dois-pontos) que será executada caso a Condição1 retorne um valor True. As instruções são obrigatórias na forma de uma linha que não possua a cláusula Else e opcionais na forma de bloco; Condição2 (opcional): Igual a Condição1; Instruções para If=False e ElseIf=True (opcional): Uma instrução (ou várias, separadas por dois-pontos) que será executada caso a Condição1 retorne um valor False e a Condição2 retorne um valor True; Instruções para o caso de todas as condições anteriores serem False (opcional): Uma instrução (ou várias, separadas por dois-pontos) que será executada caso nenhuma das condições anteriores retorne um valor True.


4

Considere o seguinte exemplo: Sub If_Elseif() Dim pagto As String pagto = Ucase(InputBox("Indique a forma de pagamento")) If pagto = "D" Then MsgBox "Dinheiro" ElseIf pagto = "C" Then MsgBox "Cheque" ElseIf pagto = "CC" Then MsgBox "Cartão de Crédito" Else MsgBox "Pagamento não definido. Cancelar venda!" End If End Sub

Será exibida a mensagem de acordo com a sigla inserida na caixa de texto (InputBox). Caso o usuário digite algo diferente das siglas (D, C ou CC), será executado o comando após a instrução Else.

É possível inserir quantas cláusulas ElseIf forem necessárias, mas apenas entre as instruções If e Else. Neste exemplo, temos apenas como condição válida os valores: D, C ou CC (em maiúsculo). Porém, caso fosse inserido, na Caixa de Entrada, um destes itens em letra minúscula, seriam executadas as ações dentro do Else, visto que todas as condições se referenciam às siglas digitadas em letras maiúsculas e o VBA diferencia letras maiúsculas de letras minúsculas.

183


Para resolver este problema, foi utilizada a função UCase(), que converte os caracteres em maiúsculo. Observe a linha de código: pagto = Ucase(InputBox("Indique a forma de pagamento"))

Caso o usuário digite, na Caixa de Entrada, o valor cc (em letras minúsculas), a função irá converter este valor para letras maiúsculas resultando em CC. Neste caso, a variável pagto terá o valor em maiúsculo. Lembrando que o programa não irá alterar os caracteres digitados dentro da Caixa de Entrada, e sim efetuar o comparativo a seguir: •

•

O valor digitado na Caixa de Entrada convertido em letras maiúsculas é igual a CC? O valor cc convertido em letras maiúsculas é igual a CC?

4.4.3. If aninhado É possível aninhar blocos If , ou seja, inserir um bloco If dentro de outro, como exibe a estrutura a seguir. É importante notar que todos os blocos If devem ser fechados com a instrução End If : If Condição1 Then Instruções para o caso de Condição1= verdadeiro If Condição2 Then Instruções para o caso de Condição2=verdadeiro End If End If

184


4

Observe o exemplo a seguir, que exibirá ao usuário a mensagem "Dar desconto de 10%" caso o pagamento seja à vista (se pressionado o botão "SIM" na função MsgBox) e o tipo de pagamento seja em dinheiro (se digitada a palavra "Dinheiro" na caixa de texto InputBox): Sub If_Aninhado() Dim tipo As String Dim pagto As String tipo = MsgBox("Pagamento à Vista?", vbYesNo) pagto = InputBox("Indique a forma de pagamento") If tipo = vbYes Then If ucase(pagto) = "DINHEIRO" Then MsgBox "Dar desconto de 10%" End If End If End Sub

Caso a resposta obtida na função MsgBox seja "SIM", será executada a segunda instrução If .

Devemos utilizar o recurso de indentação de comandos (deslocamento à direita com a tecla TAB) a fim de mostrar claramente os comandos que estão dentro de uma instrução If .

185


4.4.4. Inserindo várias condições com And e Or Por meio dos operadores And e Or, podemos executar uma ou mais instruções a partir do teste de várias condições. O operador And constrói uma conjunção lógica, ou seja, para que o resultado seja True, todas as condições devem ser verdadeiras (caso qualquer uma delas seja falsa, o resultado é False). Para utilizar o operador And, este deve ser inserido entre as condições que serão testadas, como exibe a estrutura a seguir: If Condição1 And Condição2 And Condição3...Then Instruções caso todas as condições sejam verdadeiras Else Instruções caso uma ou mais condições seja falsa End If

Considere o exemplo adiante: Sub Operador_And() Dim num As Byte num = 10 If num > 5 And num < 15 Then MsgBox "Valor dentro do intervalo!" End If End Sub

O conteúdo da variável Num é 10, sendo as condições: •

Num > 5 = verdadeiro, pois 10 é maior que 5;

•

Num < 15 = verdadeiro, pois 10 é menor que 15.

Como as duas condições são satisfatórias (verdadeiras), será executado o comando dentro da instrução If .

186


4

O operador Or constrói uma disjunção lógica, ou seja, para que o resultado seja True, basta que uma das condições seja verdadeira (caso todas elas sejam falsas, o resultado é False). O operador Or deve ser inserido entre as condições que serão testadas, da mesma forma que o operador And, como exibe a estrutura a seguir: If Condição1 Or Condição2 Or Condição3...Then Instruções caso uma ou mais das condições seja True Else Instruções caso todas as condições sejam False End If

Observe o exemplo: Sub Operador_Or() Dim valor As Byte Dim pagto As String valor = 50 pagto = "ato" If valor > 200 Or pagto = "ato" Then MsgBox "Fornecer desconto de 15%" End If End Sub

Neste caso, a mensagem será exibida se o prazo para pagamento for "Ato" (à vista) ou o valor da compra for superior a 200, ou seja, com a utilização do operador Or, a instrução é considerada verdadeira se uma ou outra condição estiver satisfatória. Em outro exemplo, devemos inserir na célula B6 o status do aluno, de acordo com a média e o percentual de faltas, considerando que o curso possui o total de 80 aulas. Observe as condições: •

•

•

Status "reprovado" se a média final for inferior a 5 OU o total de faltas for maior do que 25% do total de aulas; Status "aprovado" se a média final for superior ou igual a 7 E o total de faltas for menor ou igual a 25% do total de aulas; Status "recuperação" se a média final for entre 5 e 6,9 e o total de faltas por menor ou igual a 25%. 187


Sub resultado() Dim media as single, perc_falta As Double media = Range("b3") perc_falta = Range("b4") / 80 'reprovado: nota ruim OU muita falta If media < 5 Or perc_falta > 0.25 Then With Range("b6") .Value = "Reprovado" .Interior.ColorIndex = 3 .Font.ColorIndex = 2 End With Else 'aprovado: boa nota E pouca falta Range("b6").Interior.ColorIndex = 50 If media >= 7 And perc_falta <= 0.25 Then Range("b6") = "Aprovado" 'recuperação: se não satisfez nenhuma das condições acima Else Range("b6") = "Recuperação" End If End If End Sub

Na figura a seguir, o status exibido foi "Reprovado", pois o total de faltas foi superior a 25% do total de 80 aulas:

188


4

4.4.5. Select Case A instrução Select Case permite criar um fluxo de tomada de decisão em que uma única expressão pode ser comparada com várias outras expressões. São executadas as instruções correspondentes ao Case que coincidir com o valor da expressão. A instrução Select Case utiliza a estrutura a seguir: Select Case expressão Case expressão1 Instruções 1 Case expressão 2 Instruções 2 Case Else Instruções Else End Select

O exemplo seguinte declara a variável Modelo, cria uma InputBox para que o usuário defina seu valor e a compara com quatro valores utilizando a instrução Select Case: Sub Fabricante() Dim Modelo As String Modelo = InputBox("Digite o nome do modelo de carro: ") Select Case Ucase(Modelo) Case "COROLLA" MsgBox "A fabricante deste carro é a Toyota." Case "CIVIC" MsgBox "A fabricante deste carro é a Honda." Case "FUSION" MsgBox "A fabricante deste carro é a Ford." Case Else MsgBox "Não é possível identificar a fabricante." End Select End Sub

189


Esse exemplo compara a expressão Modelo (um nome de carro inserido na InputBox pelo usuário) e os Cases (condições). Quando a instrução encontrar um Case que coincida com a expressão Modelo, exibirá uma MsgBox com o nome do fabricante do veículo. Se nenhum Case coincidir com a expressão, serão executados os comandos contidos dentro da instrução Case Else. Neste exemplo, é exibida uma MsgBox informando que não foi possível identificar o fabricante. Observe a imagem a seguir, que ilustra a MsgBox exibida caso o usuário digite Corolla na InputBox:

Podemos comparar a expressão com faixas de valor, utilizando a palavra-chave To entre os valores inicial e final, por exemplo, 60 To 65 para indicar um intervalo entre 60 e 65 (incluindo os extremos). Observe o exemplo adiante, que determina a exibição da MsgBox de acordo com a faixa de valor inserida pelo usuário: Sub Estoque() Dim qtde_estoque As Integer estoque = InputBox("Qual a quantidade") Select Case estoque Case 0 To 10 MsgBox "Insuficiente" Case 11 To 30 MsgBox "Atenção" Case Is > 30 MsgBox "Ok" End Select End Sub

190


4

4.5. Tratamento de erros Se desejarmos trabalhar com VBA, uma das coisas mais importantes que devemos ter em mente é a probabilidade de ocorrerem erros. Dentre os erros possíveis, existem dois tipos muitos comuns, que são os erros de programação e os de tempo de execução. Por mais que, no momento de desenvolver um aplicativo, tenhamos removido as falhas do código e criado uma lógica sem defeitos, visando prevenir um mau funcionamento, ainda assim o código pode travar devido a um problema operacional. Em muitos casos, é exibido nada além de uma mensagem que nem sempre traz ajuda para o usuário solucionar o erro. Como último recurso, podemos usar um código que impede que as mensagens de erro do Excel sejam exibidas, com o intuito de prever e lidar com os erros antes que uma delas surja. Será fácil lidar com erros de programas bem escritos usando as ferramentas de identificação de erros que o próprio VBA disponibiliza.

4.5.1. Tipos de erros Dado o primeiro passo, que é saber das chances existentes de ocorrerem erros e da necessidade de corrigi-los para que se obtenha o resultado esperado, o segundo passo é conhecer os tipos de erros mais frequentes. Alguns tipos de erros são mais fáceis de serem corrigidos do que outros. Erros de sintaxe, por exemplo, podem ser facilmente impedidos com o recurso de verificação de sintaxe, o qual exibe uma mensagem informando o erro quando o cursor é posicionado em outra linha. Os erros de tempo de execução, que ocorrem quando uma operação não pode ser executada, como excluir uma célula de uma planilha protegida, também são impedidos sem dificuldades.

191


Porém, de solução mais complexa do que erros de sintaxe e de tempo de execução, são os de lógica. Isso porque é difícil identificar a causa de um resultado inesperado quando todos os comandos são bem executados. Um erro desse tipo ocorre, por exemplo, quando digitamos o sinal >= (maior ou igual a) onde deveria estar o sinal > (maior que) para inserir uma condição em uma estrutura condicional. Por mais simples que possa parecer, algumas vezes, perdemos muito tempo até encontrar um erro assim e, em alguns casos, pode ser que se faça necessário refazer todo um projeto.

Algumas práticas de grande valia, como inserir comentários no código e fazer recuo nas linhas, garantem mais agilidade no momento da manutenção do código.

4.5.2. Tratamento em tempo de execução Um erro pode ser tratado até mesmo enquanto o código é executado, desde que sua presença tenha sido percebida e, portanto, tenha sido feita uma configuração no código que permite seu tratamento. A instrução On Error é aplicada exatamente nesses casos, pois ela posiciona no procedimento e põe em funcionamento uma rotina de tratamento de erro.

Da mesma forma que ativa, a instrução On Error também pode ser usada para desativar uma rotina de tratamento de erro. As sintaxes da instrução On Error são três. A seguir, citamos cada uma delas, assim como a respectiva descrição.

192


4

4.5.2.1.On Error GoTo line Nesta sintaxe, o argumento line é obrigatório e determina a linha onde deve ser iniciada a rotina de tratamento. Podemos definir esse argumento como qualquer rótulo ou número de linha. Seu funcionamento se dá quando ocorre um erro em tempo de execução e o controle desvia para line, iniciando o tratamento do erro. Para que não ocorra erro em tempo de compilação, devemos atentar para o fato de que é necessário especificar a line no mesmo procedimento onde se encontra a instrução On Error. No exemplo a seguir, foi criada a variável idade para receber um valor numérico. Será solicitada a inserção de um valor dentro de uma caixa de entrada que irá atribuir o valor digitado dentro da variável. Sabemos que uma variável do tipo Byte não pode armazenar valores do tipo texto (string) e que esse tipo de variável aceita somente valores numéricos no intervalo de 0 a 255. Sub exemplo_erro() Dim idade As Byte idade = InputBox("Entre com a idade") MsgBox "A idade é " & idade End Sub

Se na caixa de texto for digitado um valor numérico, ele será exibido em uma caixa de mensagem. Caso seja digitado um texto, ocorrerá um erro de execução na macro, visto que a variável é do tipo numérica.

193


Ao pressionar o botão OK, surgirá o seguinte erro:

Poderemos, então, utilizar o tratamento de erros. Nesta situação, caso ocorra qualquer tipo de erro, serão executados os comandos contidos dentro do rótulo Tratamento. Para On Error GoTo Tratamento, em caso de erros, o processo seguirá para a instrução Tratamento, que, por sua vez, contém o comando para a exibição de uma mensagem. Sub exemplo_erro() On Error GoTo Tratamento Dim idade As Byte Idade = InputBox("Entre com a idade") MsgBox "A idade é " & idade Exit Sub Tratamento: MsgBox "Valor Inválido" End Sub

194


4

Caso não ocorram erros durante a execução, serão executados todos os comandos, inclusive o conteúdo de Tratamento, visto que as linhas de programação estão sendo executadas em cascata. Dessa forma, para que não seja exibida a mensagem Valor Inválido mesmo quando for digitado um valor numérico, é necessária a inserção da instrução Exit Sub na linha anterior à rotina Tratamento.

Informação digitada.

Comando contido dentro da rotina Tratamento.

É possível inserir quantas linhas de comandos forem necessárias dentro da rotina de tratamento de erros.

195


4.5.2.2. On Error Resume Next Esta sintaxe faz com que o controle passe para a instrução seguinte em caso de erro de tempo de execução, e, assim, a execução continua. Para acessar objetos, este será o tipo de tratamento mais apropriado. O exemplo a seguir consiste em inserir, nas linhas de uma coluna, o nome das barras de ferramentas contidas no Excel. Em caso de erros durante a execução, o objeto em questão será ignorado e o próximo objeto será executado (barra de ferramentas): Sub Nome_Barras() On Error Resume Next Dim barra As Object Range("a1") = "Nomes" Range("a2").Select For Each barra In CommandBars ActiveCell = barra.Name ActiveCell.Offset(1, 0).Select Next End Sub

4.5.2.3. On Error GoTo 0 Se utilizarmos esta sintaxe, todo manipulador de erro que estiver ativo no procedimento atual será desativado. Sub exemplo_erro1() On Error GoTo 0 Dim idade As Byte idade = InputBox("Entre com a idade") MsgBox "A idade é " & idade Exit Sub Tratamento: MsgBox "Valor Inválido" End Sub

É também por meio da utilização da instrução On Error que impedimos que as mensagens de erro sejam exibidas no momento da execução de uma macro.

196


4

Rotinas de tratamento de erros são seções identificadas por um rótulo ou número de linha, e não um procedimento Sub ou Function. Elas identificam a razão do erro por meio do valor na propriedade Number do objeto Err. Então, salvam e testam os valores relevantes, o que deve ser feito antes da ocorrência de outro erro ou da execução de um procedimento que cause um erro. Os valores de propriedade no objeto Err demonstram apenas o último erro que ocorreu. Em Err.Description está presente a mensagem de erro relacionada a Err.Number. Para cada erro que surge enquanto um procedimento é executado, existe um código correspondente. A propriedade Number da instrução Err permite identificar e tratar erros, assim como a instrução On Error. Suponhamos que seja necessária a inserção da quantidade de um produto dentro de uma caixa de texto e tenha sido criada uma variável do tipo Byte. Nesse caso, a informação não poderá ser do tipo string, nem possuir um valor acima de 255. Sub quantidade() On Error GoTo Tratar Dim qtde As Byte qtde = InputBox("Digite a quantidade") If qtde < 10 Then MsgBox "Estoque Insuficiente" End If Exit Sub Tratar: If Err.Number = 6 Then MsgBox "Valor Excedido. Máximo = 255" ElseIf Err.Number = 13 Then MsgBox "Digite um valor numérico" End If End Sub

4.5.3. Erros interceptáveis Em algumas situações, podemos nos deparar com erros interceptáveis. Eles ocorrem durante a execução de um aplicativo ou durante o desenvolvimento ou tempo de compilação. Para testar e responder a erros interceptáveis, devemos usar a instrução On Error ou o objeto Err.

197


A seguir, temos uma tabela com os números de erros interceptáveis. Como podemos notar, essa lista pula alguns números. Dentre os números pulados, os de 1 até 1000 serão usados futuramente pelo Visual Basic. Código do erro 3 5 6 7 9 10 11 13 14 16 17 18 20 28 35 47 48 49 51 52 53 54 55 57 58 59 61

198

Mensagem Retornar sem GoSub. Chamada de procedimento inválida. Estouro. Sem memória. Subscrito fora do intervalo. Esta matriz está fixa ou temporariamente bloqueada. Divisão por zero. Tipos incompatíveis. Sem espaço para sequência de caracteres. Expressão muito complexa. Não é possível executar a operação solicitada. Ocorreu uma interrupção do usuário. Continuar sem erro. Continuar sem erro. Sub, Function ou Property não definido. Clientes de aplicativo da DLL ou de recurso de código em excesso. Erro ao carregar recurso de código ou DLL. Convenção de chamada de recurso de código ou DLL incorreta. Erro interno. Nome ou número de arquivo incorreto. Arquivo não encontrado. Modo de arquivo incorreto. Arquivo já aberto. Erro de E/S do dispositivo. O arquivo já existe. Comprimento de registro incorreto. Disco cheio.


Código do erro 62 63 67 68 70 71 74 75 76 91 92 93 94 97 98 298 320 321 322 325 327 328 335 336 337 338 360 361

4

Mensagem Entrada após fim de arquivo. Número de registro incorreto. Arquivos em excesso. Dispositivo não disponível. Permissão negada. Disco não está pronto. Não é possível renomear com unidade diferente. Erro de acesso ao Caminho/Arquivo. Caminho não encontrado. Variável Object ou variável de bloco With não definida. For Loop não inicializado. Sequência de caracteres padrão inválida. Uso de Null inválido. Não é possível chamar o procedimento Friend ou um objeto que não seja uma ocorrência da classe de definição. Uma chamada de propriedade ou método não pode incluir uma referência a um objeto particular, tanto como um argumento ou como um valor de retorno. O recurso ou DLL do sistema não pôde ser carregado. Não é possível usar nomes de dispositivo de caracteres em nomes de arquivo especificados. Formato de arquivo inválido. Não é possível criar o arquivo temporário necessário. Formato inválido no arquivo de recursos. Valor de dados nomeado não encontrado. Parâmetro não permitido; não é possível gravar matrizes. Não foi possível acessar o Registro do sistema. Componente não registrado corretamente. Componente não encontrado. O componente não foi executado corretamente. Objeto já carregado. Não é possível carregar ou descarregar este objeto.

199


Código do Mensagem erro 363 Controle especificado não encontrado. 364 O objeto foi descarregado. 365 Não é possível descarregar dentro deste contexto. O arquivo especificado está desatualizado. Este programa 368 requer uma versão mais recente. O objeto especificado não pode ser usado como um formulário 371 de proprietário para Show. 380 Valor de propriedade inválido. 381 Índice de matriz de propriedades inválido. 382 Property Set não pode ser executado em tempo de execução. Property Set não pode ser usado com uma propriedade 383 somente leitura. 385 É necessário o índice de matriz de propriedades. 387 Property Set não permitido. 393 Property Get não pode ser executado em tempo de execução. Property Get não pode ser executado na propriedade 394 somente gravação. O formulário já está sendo exibido; não é possível exibir de 400 forma modal. 402 O código deve fechar primeiro o formulário modal superior. 419 Permissão para usar o objeto negada. 422 Propriedade não encontrada. 423 Propriedade ou método não encontrado. 424 Objeto obrigatório. 425 Uso inválido de objeto. O componente não pode criar objeto ou referência de retorno 429 para este objeto. 430 A classe não suporta Automação. Nome do arquivo ou nome da classe não encontrado durante 432 a operação de Automação. 438 O objeto não suporta esta propriedade ou método. 440 Erro de automação.

200


Código do erro 442 443 445 446 447 448 449 450 451 452 453 454 455 457 458 459 460 461 462 463 480 481 482 483 484

4

Mensagem A conexão com a biblioteca de tipos ou biblioteca de objetos para o processo remoto foi perdida. O objeto de automação não possui um valor padrão. O objeto não suporta esta ação. O objeto não suporta argumentos nomeados. O objeto não aceita a definição de localidade atual. Argumento nomeado não encontrado. Argumento não opcional ou atribuição de propriedade inválida. Número incorreto de argumentos ou atribuição de propriedade inválida. O objeto não é uma coleção. Ordinal inválido. Código de especificado não encontrado. Recurso de código não encontrado. Erro de bloqueio do recurso de código. Esta chave já está associada a um elemento desta coleção. A variável usa um tipo não suportado no Visual Basic. Este componente não suporta o conjunto de eventos. Formato inválido da Área de transferência. Método ou membro de dados não encontrado. A máquina do servidor remoto não existe ou não está disponível. Classe não registrada na máquina local. Não é possível criar uma imagem AutoRedraw. Figura inválida. Erro na impressora. A propriedade especificada não é suportada pelo driver da impressora. Problemas ao obter informações da impressora a partir do sistema. Certifique-se de que a impressora esteja configurada corretamente.

201


Código do Mensagem erro 485 Tipo de figura inválido. Não é possível imprimir a imagem do formulário neste tipo 486 de impressora. 520 Não é possível esvaziar a Área de transferência. 521 Não é possível abrir a Área de transferência. 735 Não é possível salvar arquivo no diretório TEMP. 744 Texto de pesquisa não encontrado. 746 Substituições muito longas. 31001 Sem memória. 31004 Nenhum objeto. 31018 A classe não está definida. 31027 Não é possível ativar o objeto. 31032 Não é possível criar objeto incorporado. 31036 Erro ao salvar no arquivo. 31037 Erro ao carregar do arquivo.

Criemos uma planilha de acordo com o exemplo a seguir, nomeando o intervalo das células como Estado:

202


4

Criaremos, então, uma macro onde o usuário deverá digitar uma sigla dentro de uma caixa de entrada para que seja procurado o estado correspondente. Chamaremos a função ProcV dentro do código em VBA. Utilizaremos o comando a seguir para executar uma função da planilha, estando dentro do código em VBA. Devemos observar que o nome da função deverá ser especificado em inglês: Application.WorksheetFunction. (argumentos da função)

Digitemos a seguinte macro:

A função VLookup (ProcV) utilizada a partir do VBA possui a mesma funcionalidade demonstrada quando é executada na planilha, ou seja, traz os dados correspondentes a uma informação encontrada. Caso não exista na tabela o item procurado, surgirá uma mensagem de alerta de erro. Na verdade, o fato de a sigla não existir na tabela não significa um erro de macro, mas sim um problema na localização da informação. Nesse caso, é necessário colocar o tratamento de erros no procedimento, fazendo com que seja exibida uma mensagem ao usuário caso não seja localizada determinada sigla.

203


Se, na macro Procura, não tivéssemos utilizado o tratamento de erros e, durante a execução da macro, fosse inserida uma sigla inexistente, seria exibida a seguinte mensagem:

204


4


•

•

•

•

•

As estruturas de decisão e repetição são utilizadas nos casos em que é necessário alterar o fluxo normal de execução das instruções, executando apenas alguns procedimentos ou executando um mesmo procedimento várias vezes de acordo com condições pré-estabelecidas; A instrução GoTo faz com que uma instrução especificada por um rótulo de linha seja executada, ignorando outras possíveis instruções no fluxo normal dos procedimentos; O operador And constrói uma conjunção lógica, ou seja, para que o resultado seja True, todas as condições devem ser verdadeiras. O operador Or constrói uma disjunção lógica, ou seja, para que o resultado seja True, basta que uma das condições seja verdadeira; A instrução Select Case permite criar um fluxo de tomada de decisão em que uma única expressão pode ser comparada com várias outras expressões. São executadas as instruções correspondentes ao Case que coincidir com o valor da expressão; A instrução Loop das estruturas Do...Loop, Do While...Loop e Do Until... Loop é utilizada para repetir a execução de blocos de código VBA por um número de vezes determinado previamente ou pelo valor de uma variável; A estrutura While...Wend funciona da mesma forma que a estrutura Do While...Loop, com a exceção de que o teste da condição deve ser feito obrigatoriamente no início;

205


•

•

•

•

206

O loop For...Next é utilizado para definir a quantidade de vezes que um conjunto de instruções será repetido. Esse loop precisa de um contador – uma variável numérica – com um valor inicial, um valor final e um passo; Se desejarmos trabalhar com VBA, uma das coisas mais importantes que temos que ter em mente é a probabilidade de ocorrerem erros. Dentre os erros possíveis, existem dois tipos muitos comuns, que são os erros de programação e os de tempo de execução; Alguns tipos de erro são mais fáceis de serem corrigidos do que outros. Erros de sintaxe, por exemplo, podem ser facilmente impedidos. Os erros de tempo de execução também são impedidos sem dificuldades. Porém, de solução mais complexa do que erros de sintaxe e de tempo de execução são os erros de lógica; Um erro pode ser tratado até mesmo enquanto o código é executado, desde que sua presença tenha sido percebida e, portanto, tenha sido feita uma configuração no código que permite seu tratamento.

4

Estruturas de decisão e repetição Teste seus conhecimentos


1. Qual das alternativas a seguir contém uma instrução condicional?

☐

a) If

☐

b) Select Case

☐

c) Do While

☐

d) As alternativas A e B estão corretas.

☐

e) As alternativas B e C estão corretas.

2. Em que tipo de estrutura o comando LOOP é utilizado?

208

☐

a) Do while

☐

b) If

☐

c) Do Until

☐

d) As alternativas A e C estão corretas.

☐

e) As alternativas A e B estão corretas.


4

3. Qual dos procedimentos a seguir permite ignorar um erro em tempo de execução, passando para a instrução posterior?

☐

a) Utilizar Exit sub.

☐

b) Pressionar a tecla de função F2.

☐

c) Utilizar On Error Resume Next.

☐

d) Utilizar Exit For.

☐

e) Utilizar a janela Verificação Imediata.

4. O que faz o comando On Error GoTo Teste?

☐

a) Exibe a mensagem de teste durante um erro.

☐

b) Ativa o depurador de funções em caso de erros.

☐

c) Em caso de erro durante a execução, executa a rotina denominada Teste.

☐

d) Encerra a execução da macro.

☐


209


5. Qual é a quantidade máxima de linhas que podemos inserir em uma rotina de tratamento de erros?

210

☐

a) 2

☐

b) 10

☐

c) 5

☐

d) 18

☐


4

Estruturas de decisão e repetição Mãos à obra!


Laboratório 1 A – Desenvolvendo códigos com a utilização de laços (Do While, Do Until ou For), através dos conceitos de macro relativa, com a utilização do comando Offset 1. Desenvolva uma macro chamada Tabuada, que deverá exibir, a partir da célula D5, o valor da tabuada do número digitado na célula C2, conforme o exemplo a seguir:

A macro deverá selecionar e alterar a cor das células que estiverem nas linhas pares. Ela poderá ser feita com um destes três laços: •

For...Next;

•

Do While... Loop;

•

Do Until... Loop.

2. O cálculo deverá ser feito através de um botão que executará a macro Tabuada.

No exemplo anterior, criamos três botões, cada um correspondente ao cálculo da tabuada com laços de repetição diferentes. Mas você pode apenas criar um botão chamado Tabuada que calcule com o código que você criou.

212


4

Laboratório 2 A – Utilizando a estrutura Do Until e a propriedade ColorIndex 1. Desenvolva uma macro chamada CORES: •

•

•

Nas células da coluna A, deverão ser exibidos os valores numéricos que representam as cores, utilizados pela propriedade ColorIndex; Atribua, respectivamente, as cores de cada valor numérico nas células da coluna B; Utilize o laço Do Until...Loop.

Observe o exemplo na planilha a seguir:

A propriedade ColorIndex aceita valores entre 0 e 56.

213


Laboratório 3 A – Utilizando tratamento de erros em tempo de execução 1. Desenvolva uma macro chamada Calcular, que deverá exibir, nas células C8 a C11, o resultado do cálculo entre os valores digitados nas células C5 e C6: •

•

Armazene os conteúdos das células C4 e C5 em variáveis do tipo BYTE; A macro só deverá exibir os resultados se as condições a seguir forem satisfatórias: •

•

•

•

Os conteúdos das células C5 e C6 deverão ser numéricos. Exibir a mensagem "Favor digitar valor numérico"; O conteúdo da célula C6 não poderá ser 0 (zero); Os conteúdos das células C5 e C6 não poderão ser superiores ao valor 255.

Caso essas condições não sejam satisfatórias, utilize tratamentos de erro exibindo uma mensagem de alerta ao usuário e limpar todos os valores: Condição Se o conteúdo de C5 e/ou C6 for texto. Se o conteúdo de C6 for 0 (zero). Se o conteúdo de C5 e/ou C6 for superior a 255. Um erro diferente dos três anteriores.

214

Mensagem caso a condição não seja satisfatória "Favor digitar valor numérico." "Não é possível dividir um valor por zero." "Os valores das células C5 e C6 não poderão ser superiores a 255." "Erro interno:" & Err.Number


4

Observe a planilha de exemplo:

2. Crie um botão que execute a macro Calcular.

215

Funções VBA 9 9 9 9 9 9 9 9

9 9

Introdução; Funções matemáticas; Funções financeiras; Funções de Data e Hora; Funções de texto; Funções de conversão; Funções de verificação; Acessando a biblioteca de funções de planilha do Excel; Criando funções personalizadas; Criando suplementos XLAM.


5.1. Introdução Conheceremos, neste capítulo, diversas funções que podemos utilizar no VBA (matemáticas, de data e hora, de tratamento de strings e de conversão de dados), além dos procedimentos para utilizar funções da planilha e criar funções personalizadas. Veremos, também, curiosidades sobre o Assistente de Função e suplementos.

5.2. Funções matemáticas As funções deste grupo realizam cálculos matemáticos. A próxima tabela exibe a lista dessas funções, juntamente com os dados que elas retornam: Função Abs Atn Cos Exp Fix

Int Log Rnd Sgn Sin Sqr Tan

218

Retorno O valor absoluto de um número. O arco tangente de um número. O cosseno de um ângulo. O resultado de e (base dos logaritmos naturais) elevado à potência de um número. A parte inteira de um número. Para exemplificar, Fix(70.7) retorna 70 e Fix(-70.7) retorna –70 (retorna o primeiro inteiro negativo maior ou igual ao número). A parte inteira de um número. Para exemplificar, Int(70.7) retorna 70 e Int(-70.7) retorna –71 (retorna o primeiro inteiro negativo que seja menor ou igual ao número). O logaritmo natural de um número, ou seja, o logaritmo na base e (constante equivalente a aproximadamente 2,718282). Um número aleatório entre 0 e 1. 1, -1 ou 0, de acordo com o sinal do número (positivo, negativo ou 0, respectivamente). Para exemplificar, Sgn(70) retorna 1, Sgn(-70) retorna -1 e Sgn(0) retorna 0. O seno de um ângulo. A raiz quadrada de um número maior ou igual a zero. Para exemplificar, Sqr(49) retorna 7, Sqr(81) retorna 9 e Sqr(0) retorna 0. A tangente de um ângulo.

Funções VBA

5

Considere o seguinte exemplo: Sub FuncoesMatematicas() MsgBox "Valor absoluto: " & Abs(-1) MsgBox "Arco tangente: " & Atn(15) MsgBox "Cosseno: " & Cos(90) MsgBox "Exponencial: " & Exp(2) MsgBox "Inteiro1: " & Fix(-50) MsgBox "Inteiro2: " & Int(-50.985) MsgBox "Logaritmo = " & Log(5) MsgBox "Aleatorio: " & Rnd(1) MsgBox "Tipo Num: " & Sgn(-10) MsgBox "Seno: " & Sin(90) MsgBox "Raiz: " & Sqr(49) End Sub

5.3. Funções financeiras Neste exemplo, você verá um exemplo da função financeira VF, Valor Futuro. Porém, assim como a VF, todas as funções financeiras do Excel têm uma função correspondente em VBA. Uma boa prática é acessar a Ajuda da Microsoft (pela Internet, com o F1 do teclado) e ver outras possibilidades do comando ou instrução em questão, pois, normalmente, sempre há outras opções de uso.

219


5.4. Funções de data e hora As funções de data e hora permitem manipular esse tipo de dado, realizando operações como extrair o dia, o mês ou o ano de uma data e obter a data ou o horário atual do computador. As datas são tratadas como números inteiros pelo VBA. Dentro do intervalo permitido de 01/01/100 a 31/12/9999, a data 30/12/1899 é representada pelo número 1, e as datas anteriores (29/12/1899, 28/12/1899 e assim por diante) são representadas por números inteiros negativos (-1, -2...). As datas posteriores a 30/12/1899 são representadas por números inteiros positivos.

O fato de o VBA representar datas por números inteiros permite também realizar operações de adição e subtração com esse tipo de dado, obtendo o número de dias existentes em um intervalo de datas, por exemplo.

220

Função

Sintaxe

Date

Date

Day

Day(date)

Month

Month(date)

Year

Year(date)

Now

Now

DateSerial

DataSerial(year,month,day)

DateValue

DataValue(date)

Retorno A data atual, de acordo com o calendário do computador. Também pode ser utilizada para definir a data do computador. O dia de uma data, representado por um número inteiro entre 1 e 31. O mês de uma data, representado por um número inteiro entre 1 e 12. O ano de uma data, representado por um número inteiro. A data e o horário atuais, de acordo com o relógio do computador. A data composta por ano, mês e dia especificados. A data representada por uma string (texto).

Funções VBA

Função Weekday

WeekdayName

MonthName Hour

Minute

Second

Time

Timer TimeValue TimeSerial

5

Sintaxe

Retorno O dia da semana de uma data, Weekday representado por um número de (date,[firstdayofweek]) 1 a 7. O nome de um dia da semana WeekdayName(weekday, (weekday é o número do dia da abbreviate, firstdayofweek) semana cujo nome se deseja obter). MonthName(month[, O nome de um mês. abbreviate]) A hora de um horário, representada Hour(time) por um número inteiro entre 0 e 23. Os minutos de um horário, Minute(time) representados por um número inteiro entre 0 e 59. Os segundos de um horário, Second(time) representados por um número inteiro entre 0 e 59. O horário atual, de acordo com o relógio do computador. Também Time pode ser utilizada para definir o horário do computador. A quantidade de segundos Timer contados a partir da meia-noite. Um horário a partir de um valor de TimeValue(time) texto. TimeSerial(hour, minute, Um horário composto pela hora, second) minutos e segundos especificados.

Neste contexto, considere o seguinte exemplo: Sub Exibedata() Range("a1") Range("a2") Range("a3") Range("a4") End Sub

= Date = Day(Date) = Month(Date) = Year(Date)

221


Supondo que a data do sistema operacional seja 09/05/2016, ao executar o código, serão exibidos os seguintes dados na planilha:

Observe o exemplo logo adiante, em que será exibida a idade de acordo com a data de nascimento digitada em uma função Inputbox. Neste caso, utilizamos o cálculo com datas: Sub idade() Dim nascimento As Date Dim idade As Double nascimento = InputBox("Digite sua data de nascimento") idade = Int((Date - nascimento) / 365) MsgBox "Você tem " & idade & " anos." End Sub

Como já visto, uma data é sempre representada por um valor numérico, ou seja, ao subtrairmos a data de nascimento da data atual, é retornado também um valor numérico que, neste caso, poderia representar os dias vividos. Imaginando que a data atual seja 06/04/2008 e a data de nascimento 17/07/2000, a subtração entre as duas datas será igual a 2820 (dias de diferença entre a data atual e nascimento). Então, dividimos por 365 para retornar a quantidade em anos, que resulta em 7,726027... Como precisamos desprezar os decimais e utilizar apenas o valor inteiro do resultado, utilizamos a função Int.

Ao dividir esse resultado por 365, dependendo da data indicada, talvez seja exibido um valor em decimais. Nesta situação, pode-se utilizar a função Int para retornar o valor inteiro.

222

Funções VBA

5

Para as funções Weekday e WeekdayName, firstdayofweek representa o primeiro dia da semana. Caso não seja especificado, o valor 1 corresponderá ao domingo e o valor 7 ao sábado. Sub Dia_Semana() Dim Dia_Data As Date Dia_Data = "10/05/2005" MsgBox Weekday(Dia_Data) MsgBox WeekdayName(Weekday(Dia_Data)) End Sub

No exemplo anterior, primeiramente, será exibido o valor 3 na caixa de mensagem. Em uma segunda caixa, será exibida a palavra terça-feira. O argumento abbreviate pode assumir o valor False (padrão), ou True, resultando em strings não abreviadas ou abreviadas, respectivamente (como Domingo ou Dom). MsgBox WeekdayName(Weekday(Dia_Data), True)

Devemos utilizar os comandos DateSerial e TimeSerial caso queiramos indicar uma data ou um horário partindo de valores indicados. Este exemplo insere, na célula A1, o valor 18:10:32: Range("A1") = TimeSerial(18,10,32)

Este exemplo, por sua vez, insere, na célula A2, o valor 10/05/2000: Range("A2") = DateSerial(2000, 5, 10)

223


Para preencher os itens em uma planilha, podemos utilizar o seguinte código: Sub Preenchimento() Range("a1") = "Data do Sistema: " & Date Range("a2") = "Dia: " & Day(Date) Range("a3") = "Mês: " & Month(Date) Range("a4") = "Ano: " & Year(Date) Range("b2") = WeekdayName(Weekday(Date)) Range("b3") = MonthName(Month(Date)) Range("d1") = "Horário do Sistema: " & Now Range("d2") = "Hora: " & Hour(Now) Range("d3") = "Minuto: " & Minute(Now) Range("d4") = "Segundo: " & Second(Now) Columns("a:d").AutoFit End Sub

O código a seguir exibirá uma mensagem ao usuário dependendo do horário do sistema operacional. A função Hour está retornando os dois dígitos que representam a hora do horário do sistema operacional. Sub Mensagem_Hora() Select Case Hour(Now) Case Is < 13 MsgBox "Bom Dia!" Case Is < 18 MsgBox "Boa Tarde!" Case Else MsgBox "Boa Noite" End Select End Sub

224

Funções VBA

5

Podemos utilizar o seguinte código para inserir o horário na célula selecionada e o minuto na célula ao lado, de acordo com o horário atual do sistema operacional: Activecell = Hour(Now) Activecell.offset(0,1) = Minute(Now)

Suponhamos que seja preciso calcular o tempo (diferença) entre o início de uma ocorrência e o horário atual. Para isso, utilizamos o seguinte código: Sub Dif_Horas() Dim Inicial, Diferenca As Date Inicial = "8:30" Diferenca = CDate(Time) - CDate(Inicial) MsgBox Diferenca End Sub

O horário inicial foi definido como 08:30. Caso o horário do sistema operacional no momento da execução seja 11:32, aparecerá a seguinte mensagem:

Foi utilizada a função CDate para que o VBA convertesse a variável Inicial e o horário do sistema (TIME) como Data/ Hora antes de efetuar o cálculo da diferença.

225


5.5. F unções de texto Existe um conjunto de funções utilizadas para trabalhar com texto e executar operações, como converter um texto para letras maiúsculas ou minúsculas e extrair sequências de caracteres a partir de uma determinada posição do texto. Algumas dessas funções são descritas a seguir: Função Lcase

226

Descrição Converte todos os caracteres de uma string para letras minúsculas.

Left

Extrai uma quantidade especificada de caracteres de um texto, da esquerda para a direita.

Len

Retorna a quantidade de caracteres de uma string.

Ltrim

Remove espaços à esquerda de uma string.

Mid

Extrai uma quantidade especificada de caracteres de um texto a partir de uma determinada posição, da esquerda para a direita.

Right

Extrai uma quantidade especificada de caracteres de um texto, da direita para a esquerda.

Rtrim

Remove espaços à direita de uma string.

Sintaxe LCase(string), em que string define a cadeia de texto. Left(string, length), em que string define a cadeia de texto e length define a quantidade de caracteres extraídos (caso seja definido como zero, a cadeia de texto terá comprimento zero ("")). Len(expression), em que expression define a cadeia de texto. Ltrim (string), em que string define a cadeia de texto. Mid(string, start[, length]), em que string define a cadeia de texto, start define a posição do primeiro caractere extraído e length define a quantidade de caracteres extraídos. Right(string, length), em que string define a cadeia de texto e length define a quantidade de caracteres extraídos (caso seja definido como zero, a cadeia de texto terá comprimento zero ("")). Rtrim (string), em que string define a cadeia de texto.

Funções VBA

Função Space

StrConv

String

StrReverse Trim Ucase

5

Descrição Insere espaços em uma string.

Sintaxe Space(number), em que number define a quantidade de espaços. StrConv(string, conversion, LCID), em que string define a cadeia de texto e conversion define o formato para o qual a string será convertida (1 para caracteres maiúsculos, 2 para caracteres minúsculos, 3 para Retorna um texto convertido a primeira letra de cada palavra em outro formato. em maiúscula e o restante em minúsculas e 64 para converter para Unicode, utilizando a página de código padrão do sistema). LCID define o local e o ID do sistema (normalmente não é especificado). Retorna uma cadeia de String(number, character), em caracteres, repetidos um que number define a quantidade determinado número de de vezes e character define o vezes. caractere que será repetido. Retorna uma string StrReverse(String), em que invertida. string define a cadeia de texto. Remove espaços à esquerda Trim (string), em que string e à direita de uma string. define a cadeia de texto. Converte todos os UCase(string), em que string caracteres de uma string define a cadeia de texto. para letras maiúsculas.

227


A partir de agora, apresentaremos exemplos utilizando algumas das funções descritas: •

UCase/LCase/StrConv

O procedimento adiante converte para formato maiúsculo todas as palavras contidas na coluna A da planilha, enquanto não houver uma célula vazia: Sub Maiusculo() Range("a1").Select Do Until ActiveCell = "" ActiveCell = UCase(ActiveCell) ActiveCell.Offset(1, 0).Select Loop End Sub

Podemos utilizar a mesma estrutura de procedimento para deixar os caracteres em formato minúsculo, alterando apenas a linha de comando que define que a célula ativa receberá o conteúdo já existente em formato minúsculo: ActiveCell = LCase(ActiveCell)

Para deixar apenas a primeira letra de cada palavra em formato maiúsculo e as demais em minúsculo, utilizamos o comando: Activecell = StrConv(Activecell,3) •

Left/Right/Mid

A partir de uma variável denominada Curso, com o conteúdo Microsoft Excel VBA, podemos extrair caracteres a partir da esquerda (função Left), da direita (função Right) ou de qualquer parte do texto (função Mid). Sub Extrair_Texto() Dim Texto As String Texto = "Microsoft Excel VBA" MsgBox Left(Texto, 9) & vbCrLf & _ Mid(Texto, 11, 5) & vbCrLf & _ Right(Texto, 3) End Sub

228

Funções VBA

5

1. Extrai 9 caracteres partindo da esquerda. 2. Extrai 5 caracteres a partir da posição 11. 3. Extrai 3 caracteres partindo da direita.

Observe a seguinte planilha:

Na coluna A consta o descritivo dos programas, sendo necessário inserir na coluna B somente o nome do programa na linha correspondente, desprezando a palavra Microsoft. Além disso, o nome de cada programa deverá estar em formato maiúsculo:

229


Por meio da função Mid, foi indicada a extração do texto a partir da posição 11 (neste caso, igual para todos) e determinada uma quantidade de até 2000 caracteres. Poderia ser indicada uma quantidade menor de caracteres, baseandose no exemplo da palavra Power Point, que é a maior. Para isso, definiríamos como 11 a quantidade máxima. •

Len

Veja o exemplo para retornar a quantidade de caracteres de uma string. Observe que o espaço também é contado como um caractere: Sub Quantidade_Caracteres() Dim texto As String texto = "Microsoft Excel" MsgBox "Quantidade: " & Len(texto) & " caracteres." End Sub

•

Trim/LTrim/RTrim

No exemplo a seguir, utilizamos as funções Trim, LTrim e RTrim a fim de extrair, respectivamente, os espaços posicionados no início e fim de um texto, somente os espaços à esquerda (início) ou somente os espaços à direita (final de um texto). Sub Tirar_Espacos() Dim texto, t_Trim, t_LTrim, t_RTrim As String texto = " Excel VBA " Range("a1") = texto Range("a2") = Trim(texto) Range("a3") = LTrim(texto) Range("a4") = RTrim(texto) End Sub

230

Funções VBA

•

5

Space

A fim de inserir uma quantidade determinada de espaços, podemos usar o seguinte código: Sub Insere_Espaco() Dim Produto, Espacos As String Dim Valor As Double Produto = "Impressora" Valor = 1150 Espacos = Space(8) MsgBox Produto & Espacos & Valor End Sub

•

String

Podemos empregar a função String a fim de repetir um caractere várias vezes. Neste caso, o primeiro argumento é o número de vezes que o caractere será repetido e o segundo argumento é o próprio caractere, entre aspas: Sub Repetir() Dim texto As String texto = "." MsgBox "Impressora" & String(22, ".") & "R$ 1050,00" End Sub

231


•

StrReverse

A fim de obter o conteúdo inverso de uma variável, podemos utilizar a função StrReverse: Sub exemplo() Dim Texto, Inverso as string Texto = "Casa" Inverso = StrReverse(Texto) Msgbox Inverso End Sub

5.6. Funções de conversão Utilizamos as funções de conversão, listadas na próxima tabela, para converter uma determinada expressão para um tipo de dado diferente do atual: Função CBool CByte CCur CDate CDbl CDec CInt CLng CStr CSng Cvar

232

Valor de retorno Boolean (True ou False) Byte Currency Date Double Decimal Integer Long String Single Variant

Funções VBA

5

Para qualquer uma dessas funções, a sintaxe é Função(expression), em que expression representa os dados que serão convertidos. A função Cdate, que converte uma expressão em data, é muito utilizada para converter dados inseridos em objetos InputBox e TextBox, que assumem por padrão todos os dados como texto (string). Devido ao tipo de dado resultante da conversão com as funções CInt e CLng, as frações são arredondadas.

5.7. Funções de verificação Estas funções verificam o tipo de dado e retornam um valor booleano: True ou False. As mais utilizadas são: •

IsNumeric: Verifica se é um dado numérico;

•

IsEmpty: Verifica se é vazio;

•

IsDate: Verifica se é data;

•

IsNull: Verifica se é nulo;

•

IsArray: Verifica se é um array. Sub V_01() MsgBox IsNumeric(Range("b1")) And Not IsEmpty(Range("b1")) End Sub Sub V_02() Do Until IsDate(d) d = InputBox("Informe a Data Inicial") Loop End Sub

233


5.8. Acessando a biblioteca de funções de planilha do Excel Pertencente ao objeto Application, a propriedade WorksheetFunction permite executar uma função da planilha a partir do código VBA, com exceção das funções já existentes em VBA. Para que o Excel reconheça a função, é necessário escrevê-la em inglês. Observe a seguinte sintaxe: Application.WorksheetFunction.()

Podemos omitir Application ou WorksheetFunction. Assim, existem mais duas sintaxes válidas e que produzem o mesmo efeito: WorksheetFunction.Nome da função(Argumentos da função) Application.Nome da função(Argumentos da função)

Considere o seguinte exemplo e planilha: Sub Funcao_Planilha() MsgBox WorksheetFunction.Sum(Range("a1:a4")) End Sub

234

Funções VBA

5

Veja a informação a ser exibida na caixa de mensagem:

No exemplo a seguir, é utilizada a função Romano(), sendo retornado, na caixa de mensagem, o valor correspondente, em algarismos romanos, de um número digitado na caixa de entrada: Sub Conversao_Romanos() Dim valor As Long valor = InputBox("Digite o número") MsgBox Application.WorksheetFunction.Roman(valor) End Sub

Na planilha a seguir, é necessário que seja inserido, nas células da coluna D, o descritivo de acordo com o tipo da contratação (1, 2, 3 ou 4). Com a utilização da função Procv(), a macro irá buscar a informação do tipo na coluna H e retornar o descritivo contido na coluna I.

235


A seguir, há um exemplo de utilização da função Procv():

Após a execução da macro, teremos o seguinte resultado na coluna D:

Ao utilizar uma função da planilha do Excel no código VBA, é necessário inserir todos os parâmetros contidos na sintaxe da função.

236

Funções VBA

5

5.9. Criando funções personalizadas Além das funções do VBA e das funções de planilha do Excel, podemos criar novas funções personalizadas para atender necessidades específicas. As funções criadas podem ser utilizadas no VBA ou nas planilhas do Excel. As funções podem ser criadas a partir da geração de uma macro iniciada com a instrução Function, seguida pelo nome da função, seus argumentos e tipos de dados, entre parênteses, como a sintaxe a seguir: Function NomeFunção(Arg1 As tipo, Arg2 as tipo) As tipo

A seguinte função define o texto "Impacta Tecnologia" como conteúdo da variável Nome_Emp e, em seguida, atribui à célula A5: Function Nome_Emp() Nome_Emp = "Impacta Tecnologia" Range("A5").Value = Nome_Emp End Function

Caso seja necessário executar a função a partir da planilha do Excel, basta digitar o seguinte em qualquer célula: =Nome_Emp()

A função a seguir requer dois parâmetros (valores) que deverão ser digitados. Os valores estão representados por N1 e N2 do tipo Long, porém, o resultado da função é do tipo Double, pois, caso N1=10 e N2=3, o resultado será um valor decimal. Function dgeral(n1 as Long, n2 As Long) As Double dgeral = n1 / n2 End Function

237


5.9.1. Utilizando as funções criadas •

Executando funções a partir de um código em VBA

Podemos executar a função a partir de uma macro, em que o valor dos argumentos poderá ser fixo ou estar contido em uma variável. Observe o exemplo, com a utilização da função dgeral(), em que os argumentos são passados a partir de Inputbox: Function dgeral(n1, n2 As Long) As Double dgeral = n1 / n2 End Function Sub Exemplo_Funcao() Dim valor1, valor2 As Long valor1 = InputBox("Digite o primeiro número") valor2 = InputBox("Digite o segundo número") If valor2 = 0 Then MsgBox "Valor Invalido", vbCritical Exit Sub End If MsgBox dgeral(valor1, valor2) End Sub •

Executando funções a partir da planilha

Para utilizar a função em uma célula, basta pressionar o sinal de igual (=) seguido do nome da função e dos argumentos que ela possuir. Os argumentos poderão ser valores fixos ou valores buscados de células específicas, de acordo com os exemplos: •

Argumento fixo

=dgeral(10;2)

238

Funções VBA

•

5

Argumento buscando valor contido em células da planilha

=dgeral(A20;A21)

Também é possível utilizarmos a função criada, executando-a a partir do Assistente de Função (fx). Como padrão, as funções criadas no VBA ficam na categoria Definida pelo usuário.

A função deve ser finalizada com a instrução End Function.

5.10. Criando suplementos XLAM •

Executando a função a partir de outra pasta de trabalho

Vamos imaginar que a função dgeral() foi criada em uma pasta de trabalho denominada funcao.xlsm. Nesse caso, a função poderá ser utilizada em qualquer planilha dessa pasta de trabalho, e será possível digitar em qualquer célula o seguinte exemplo: =dgeral(10;2)

No entanto, ao digitarmos a instrução acima em outra pasta de trabalho, o Excel não a reconhecerá. Nesse caso, é preciso referenciar o arquivo que contém a função. É necessário manter o arquivo funcao.xlsm aberto e, em qualquer célula do outro arquivo, digitar a instrução a seguir. Percebe-se, então, a necessidade de referenciar o arquivo que contém o código da função: =funcao.xlsm!dgeral(10;2) •

Criando um suplemento

Para que as funções criadas possam ser utilizadas nos demais arquivos, podemos criar um arquivo como suplemento do Excel (.xlam), o qual terá todas as funções. Depois disso, basta vincular esse suplemento ao aplicativo Excel.

239


Observe o procedimento a seguir: 1. Em um arquivo novo, insira dentro de um módulo de VBA todas as funções com seus respectivos códigos; 2. Salve o arquivo como Suplemento do Excel (será criado um arquivo com a extensão .xlam).

Ao salvarmos um arquivo como suplemento, na caixa de diálogo Salvar como, será sugerido que o arquivo seja salvo na pasta de Suplementos. No entanto, é permitido salvar em outro local. •

Anexando o suplemento ao Excel

1. Clique no em Arquivo / Opções; 2. Clique na opção Suplementos do Excel e, em seguida, no botão Ir:

240

Funções VBA

5

3. O nome do arquivo que criamos aparecerá na caixa de diálogo Suplementos. Marque a opção correspondente ao arquivo de suplemento que você criou que, neste exemplo, é Função, e clique em Ok:

Para desativar o suplemento, basta desmarcar o item desejado, contido na janela Suplementos.

241



•

•

•

•

•

242

O VBA oferece um grupo de funções que realizam operações matemáticas. As funções Cos, Fix e Sqr, entre outras, fazem parte desse grupo; As datas são tratadas como números inteiros pelo VBA, o que nos permite também realizar operações de adição e subtração com esse tipo de dado, obtendo o número de dias existentes em um intervalo de datas, por exemplo; As funções de tratamento de string permitem trabalhar com texto e executar operações como converter um texto para letras maiúsculas ou minúsculas; Para qualquer função de conversão, a sintaxe é Função(expression), em que expression representa os dados que serão convertidos; As funções de planilha podem ser inseridas por meio do objeto WorksheetFunction, que faz parte do objeto Application; Podemos criar funções a partir da geração de uma macro iniciada com a instrução Function, seguida pelo nome da função, seus argumentos e tipos de dados, entre parênteses.

Funções VBA

5



1. A célula B4 contém o valor -5. Para obtermos o valor positivo do número contido nessa célula, que função devemos utilizar?

☐

a) sen

☐

b) exp

☐

c) cos

☐

d) abs

☐

e) sqr

2. Sabendo-se que o valor da célula G10 é “Pedro Toledo”, qual o comando utilizado para exibir apenas o nome “Pedro”?

244

☐

a) Msgbox Right(“Pedro”,5)

☐

b) Msgbox Left(Range(“g10”),5)

☐

c) Msgbox Mid(“Pedro Toledo”,5,5)

☐

d) Msgbox Right(Range(“g10”),6)

☐

e) Msgbox Len(Range(“g10”))

Funções VBA

5

3. Para exibir, na célula selecionada, o dia da semana da data de hoje, que comando utilizamos?

☐

a) Debug.print now

☐

b) Activecell.date

☐

c) Activecell = dayweek(hoje())

☐

d) Activecell = weekdayname(weekday(date))

☐


4. Qual a finalidade da função len?

☐

a) Transformar o texto em letras maiúsculas.

☐

b) Deixar somente a primeira letra do texto maiúscula.

☐

c) Retornar a quantidade de caracteres de uma string.

☐

d) Remover espaços à esquerda de um texto.

☐


245


5. Qual das alternativas a seguir contém apenas funções de texto?

246

☐

a) LCase, String, Mid.

☐

b) CDate, Mid, Day.

☐

c) Month, NPer, Hour.

☐

d) StrReverse, Format, Now.

☐


Funções VBA Mãos à obra!

5


Laboratório 1 A – Trabalhando com funções de texto e estrutura Do While 1. Com base nas informações da planilha a seguir, crie uma macro denominada Produto para escrever nas células da coluna B somente o nome dos produtos, desprezando o código. Para padronizar o texto, deixe a primeira letra de cada produto maiúscula.

248

Funções VBA

5

Laboratório 2 A – Utilizando variável array 1. Desenvolva uma macro chamada ExibirProduto, que deverá exibir, em uma Msgbox, o nome do produto que estará em promoção, dependendo do dia da semana, sendo: •

Segunda-feira: Caneta;

•

Terça-feira: Caderno;

•

Quarta-feira: Lápis;

•

Quinta-feira: Borracha;

•

Sexta-feira: Régua;

•

Sábado / Domingo: Nenhum produto (retornar nulo).

249

Interagindo com o usuário 9 9 9 9 9

MsgBox; Função InputBox; Método InputBox; Caixas de diálogo do Excel; Eventos mais utilizados e práticos do Excel.


6.1. MsgBox A caixa de mensagem é utilizada para passar informações ou para executar um comando que depende de uma resposta do usuário. Uma caixa de mensagem pode apresentar um ou mais botões, como OK, Cancelar, Sim, Não, entre outros. Para inserir uma caixa de mensagem, devemos usar a função MsgBox. Sua sintaxe, bem como a descrição dos seus argumentos, serão mostradas a seguir: MsgBox(Prompt[,Buttons][,Title][,Helpfile][,Context]

Em que: •

•

•

•

•

Prompt: Este argumento define o texto da mensagem que será exibida; Buttons: Este argumento determina quais botões e ícones devem ser exibidos na caixa de mensagem; Title: Este argumento é o texto da barra de título da caixa. Se não definirmos nenhum texto para esse argumento, o nome Microsoft Excel aparecerá na barra de título; Helpfile: Este argumento reconhece o arquivo de ajuda da caixa, desde que este exista; Context: Se definirmos Helpfile, este argumento também deverá ser especificado. Ele representa o número do contexto da ajuda.

6.1.1. Exibindo uma caixa de mensagem Podemos usar a função MsgBox sozinha (não inserindo os argumentos entre parênteses) ou fixar a uma variável a ação que ocorre como resultado do clique em seu botão.

252

Interagindo com o usuário

6

O exemplo seguinte cria uma mensagem que não retorna resultado: Sub MsgBoxDemo() MsgBox "Clique OK para confirmar" End Sub

Considerando que a célula A2 tenha um conteúdo, o exemplo a seguir irá exibir na caixa de mensagem a informação contida na célula: Sub Exibe_Valor_Celula() MsgBox Range("A2") End Sub

Utilizamos o símbolo & para promover a união de conteúdos diferentes dentro de um MsgBox. Observe o exemplo para exibir o conteúdo de uma célula e um texto: MsgBox "O conteúdo da célula é: " & Range("A2") & "!"

253


A fim de efetuar uma quebra de linha dentro da caixa de mensagem, utilizamos o comando vbCrlf . Observe exemplo: MsgBox "O conteúdo da célula é: " & vbCrLf & Range("A2")

Agora, alteramos as propriedades de uma caixa de mensagem. Neste exemplo, foi inserido um ícone que representa uma mensagem crítica: MsgBox "Erro de execução", vbCritical, "Atenção" Title

Ícone

Prompt

6.1.2. Constantes utilizadas nas caixas de mensagem O argumento dos botões apresenta uma certa flexibilidade no manuseio e essa é uma característica que influencia diretamente na praticidade e facilidade no momento de configurar uma caixa de mensagem. É possível escolher os botões que serão exibidos, qual deles será o padrão (aquele que é acionado quando o usuário aperta a tecla ENTER do teclado), ou se surgirá algum ícone. Também podemos optar entre o uso de uma constante ou de um valor, embora a primeira seja considerada mais fácil de trabalhar.

254


6

A seguir, temos uma lista que exibe o nome, o valor (entre parênteses) e a descrição de algumas constantes que podemos usar com a função MsgBox: •

vbOkOnly (0): Exibe apenas o botão OK;

•

vbOkCancel (1): Exibe ambos 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;

•

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;

•

vbDefaultButton1 (0): Faz com que o primeiro botão seja o padrão;

•

vbDefaultButton2 (256): Faz com que o segundo botão seja o padrão;

•

vbDefaultButton3 (512): Faz com que o terceiro botão seja o padrão;

•

vbDefaultButton4 (768): Faz com que o quarto botão seja o padrão.

255


Para exibir os botões OK e Cancelar, deve-se indicar o valor 1, ou a constante VbOkCancel: MsgBox "Calculo Realizado com sucesso", vbOKCancel MsgBox "Calculo Realizado com sucesso", 1

Os exemplos a seguir exibem os botões Sim e Não, mantendo o segundo botão como padrão, além de exibir o ícone de mensagem crítica e o título Alerta: MsgBox "Deseja excluir a célula?", vbYesNo + _ vbCritical + vbDefaultButton2, "Alerta" MsgBox "Deseja excluir a célula?", 4 + 16 + 256, "Alerta"

Podemos usar como um argumento mais de uma constante. Basta conectá-las com o sinal de adição ( +).

256


6

Além das constantes já citadas na lista anterior, também temos aquelas que são usadas como valores de retorno para a função MsgBox. Na lista seguinte, são citados o nome, o valor (entre parênteses) e a descrição destas funções: •

vbOK (1): O usuário clicou em OK;

•

vbCancel (2): O usuário clicou em Cancelar;

•

vbAbort (3): O usuário clicou em Abortar;

•

vbRetry (4): O usuário clicou em Repetir;

•

vbIgnore (5): O usuário clicou em Ignorar;

•

vbYes (6): O usuário clicou em Sim;

•

vbNo (7): O usuário clicou em Não.

6.1.3. Identificando o botão clicado Quando usamos caixas de mensagem com mais de um botão para o usuário clicar, é necessário saber qual dos botões será escolhido e, de acordo com essa escolha, determinar uma ação. A função MsgBox retorna um valor representando o botão clicado pelo usuário e, então, podemos designar uma variável para esse resultado.

257


A macro a seguir mostra as duas formas de utilização do comando MsgBox: o primeiro comando Msgbox identifica o botão clicado e o segundo apenas exibe uma mensagem ao usuário. Na primeira caixa de mensagem, percebe-se a utilização da função Msgbox() de modo que, caso seja pressionado o botão Sim, a linha correspondente à célula selecionada será excluída e, caso seja pressionado o botão Não, aparecerá a segunda caixa de mensagem apenas informando que a operação foi cancelada. Sub Exclui_Linha() If MsgBox("Excluir linha?", vbYesNo) = vbYes Then ActiveCell.Rows.Delete Else MsgBox "Operação Cancelada!" End If End Sub

6.2. Função InputBox Use InputBox para exibir uma caixa de diálogo simples de forma que você possa inserir informações a serem usadas em uma macro. A caixa de diálogo tem um botão OK e um botão Cancelar. Se você escolher o botão OK, InputBox retornará o valor inserido na caixa de diálogo, se você clicar no botão Cancelar, InputBox retornará False. A sintaxe da função InputBox e a descrição de cada um dos seus elementos são as seguintes: InputBox(prompt[,title][,default][,xpos][,ypos][,helpfile], [context]

Em que: •

•

258

Prompt: Este argumento é o texto da mensagem da caixa de diálogo; Title: Este argumento é o texto da barra de título da caixa. Se não definirmos nenhum texto para esse argumento, o nome Microsoft Excel aparecerá na barra de títulos;


•

•

•

•

•

6

Default: Quando a caixa tiver sido carregada, o texto definido em Default será exibido. Também podemos omitir essa informação para que a caixa de texto fique vazia; Xpos: Este argumento define a posição da caixa a partir do lado esquerdo da tela, ou seja, horizontalmente. Essa informação deve ser dada em twips. Se omitirmos esse argumento, a posição horizontal da caixa fica definida automaticamente no centro da tela; Ypos: Este argumento define a posição da caixa a partir da parte de cima da tela, ou seja, verticalmente. Essa informação deve ser dada em twips. Se omitirmos esse argumento, a posição vertical da caixa fica definida automaticamente no centro da tela; Helpfile: Este argumento reconhece o arquivo de ajuda da caixa, desde que este exista; Context: Se definirmos Helpfile, este argumento também deverá ser especificado. Ele representa o número do contexto da ajuda.

Considere o exemplo adiante: Sub Insere_Dado() Range("A1048576").End(xlUp).Offset(1, 0).Select ActiveCell = InputBox("Digite seu Nome", "Cadastro") End Sub Title

Prompt

Default

259


Observe, agora, o exemplo de um InputBox contendo um texto padrão (Default): Range("B2") = InputBox("Estado", "Cadastro de Clientes","SP")

6.3. Método InputBox No objeto Application, temos o método InputBox, que se diferencia da função InputBox por ter um argumento a mais, Type, o último parâmetro, que define o tipo de dado a ser retornado. Vale observar que o tipo de dado padrão retornado na função InputBox é String. A tabela a seguir lista os valores que podem ser passados no argumento Tipo. Pode ser um valor ou uma soma de valores. Por exemplo, para uma caixa de entrada que possa aceitar texto e números, defina Type como 1 + 2. Valor 0 1 2 4 8 16 64

260

Descrição Uma fórmula Um número String True ou False Um objeto Range Um valor de erro Uma matriz


6

Se Type for 8, InputBox retornará um objeto Range. Você deve usar a instrução Set para atribuir o resultado a um objeto Range, como mostrado no exemplo a seguir. Sub Metodo_InputBox() Dim r As Range Set r = Application.InputBox( _ "Clique na Célula do respectivo Cliente", , , , , , , 8) MsgBox "O cliente selecionado foi o(a): " _ & r.Value & vbCrLf & "com o valor de: " & _ Format(r.Offset(0, 1), "currency") End Sub

Nesta macro, na qual não foi necessário fazer tratamento de erros, enquanto o usuário não digitar um número, a solicitação será exibida novamente. Sub M_02() Dim n as Integer n = Application.InputBox(“Digite um NÚMERO”, , , , , , , 1) MsgBox n End Sub

261


6.4. Caixas de diálogo do Excel As caixas de diálogo do Excel são muito úteis para agilizar o trabalho com programação, pois, por meio delas, os próprios usuários podem escolher diversas opções, como formatar fonte, preencher sequências, imprimir, etc. A exibição das caixas de diálogo internas do Excel pode ser ativada por meio da seguinte sintaxe: Application.Dialogs(Nome da caixa de diálogo).Show

A sintaxe citada a seguir, por exemplo, seleciona um intervalo de células e, então, exibe uma caixa de diálogo contendo opções de formatação da fonte e cor de fundo desse intervalo. As caixas de diálogo que aparecem são Fontes e Formatar Células: Sub ExibirDialogo() Range("intervalo").Select Application.Dialogs(xlDialogFormatFont).Show Application.Dialogs(xlDialogPatterns).Show End Sub

Veja a imagem das caixas de diálogo Fontes e Formatar Células, que serão exibidas, respectivamente:

262


6

Para termos acesso a uma lista contendo as constantes das caixas de diálogo, basta seguir estes passos: 1. No VBA, pressione F2 para que seja exibido o Pesquisador de objetos; 2. Selecione Excel na lista Projeto/Biblioteca, presente no canto superior esquerdo do Pesquisador de objetos; 3. Digite xlDialog na lista Texto de pesquisa, localizada abaixo da lista Projeto/ Biblioteca; 4. Clique no botão Pesquisar, identificado pelo desenho de um binóculo.

263


Não são citadas, em nenhuma parte da Ajuda do sistema, as constantes das caixas de diálogo que podemos usar, o que quer dizer que, provavelmente, muitas tentativas e erros acontecerão até que possamos descobrir as constantes que realmente satisfazem as nossas necessidades. Além disso, algumas caixas de diálogo não têm uma constante que as represente, e há, ainda, as constantes que simplesmente não funcionam. Uma das causas para o Excel mostrar mensagens de erro é tentarmos inserir caixas de diálogo num contexto em que elas não se encaixam. Se, por exemplo, usarmos a constante xlDialogAlignment, a qual exibe uma caixa de diálogo que configura o alinhamento em uma célula, quando a seleção abranger algum outro elemento que não sejam células, o Excel mostrará uma mensagem de erro.

6.5. Eventos mais utilizados e práticos do Excel Para que os controles do formulário executem ações, ou seja, tornem-se funcionais, é necessário que sejam vinculados a eventos de objetos. Os eventos ocorrem quando ações geram acontecimentos, e sua presença não está apenas em formulários, mas em todos os objetos do software. Exemplos de eventos são clicar em um botão, ativar uma janela ou selecionar uma célula. Os procedimentos de eventos executam ações em resposta a um evento. Dos tipos de eventos existentes, dois são utilizados com mais frequência: os eventos de planilha (Worksheet), que ocorrem sempre que uma planilha é ativada ou uma de suas células é alterada, e os eventos de pasta de trabalho (Workbook), que ocorrem sempre que uma pasta de trabalho, suas planilhas, tabelas dinâmicas ou suplementos são alterados. Ambos os tipos de eventos são ativados automaticamente.

264


6

A caixa Procedimento, do VBE, está dentro da janela código e ao lado da caixa Objeto. Ela exibe uma lista dos eventos para a planilha ou pasta de trabalho selecionada: Caixa procedimento

Alguns desses eventos são: •

Eventos de planilha (Worksheet) Evento Activate

BeforeDoubleClick BeforeRightClick Calculate Change Deactivate FollowHyperlink SelectionChange •

Descrição Ocorre quando a planilha é ativada. Ocorre quando o usuário clica duas vezes em uma célula da planilha. Ocorre quando o usuário clica com o botão direito do mouse em uma planilha. Ocorre sempre que a planilha é recalculada. Ocorre quando uma célula da planilha é alterada. Ocorre quando a planilha é desativada. Ocorre sempre que um hyperlink é ativado. Ocorre sempre que a seleção é alterada.

Eventos de pasta de trabalho (Workbook) Evento Activate AddinInstall AddinUninstall BeforeClose BeforePrint BeforeSave Deactivate

Descrição Ocorre quando a pasta de trabalho é ativada. Ocorre sempre que um suplemento é instalado. Ocorre quando um suplemento é desinstalado. Ocorre quando a pasta de trabalho é fechada. Ocorre quando a pasta de trabalho é impressa. Ocorre sempre que a pasta de trabalho é salva. Ocorre quando a pasta de trabalho é desativada.

265


Evento

Descrição Ocorre sempre que uma nova planilha é adicionada NewSheet à pasta de trabalho. Open Ocorre quando a pasta de trabalho é aberta. Ocorre quando uma planilha da pasta de trabalho é SheetActivate ativada. Ocorre quando o usuário clica duas vezes em uma SheetBeforeDoubleClick pasta de trabalho. Ocorre sempre que o usuário clica com o botão SheetBeforeRightClick direito do mouse sobre a pasta de trabalho. Ocorre sempre que uma pasta de trabalho é SheetCalculate recalculada. Ocorre sempre que uma célula da pasta de trabalho SheetChange é alterada. Ocorre quando uma planilha da pasta de trabalho é SheetDeactivate desativada. Ocorre quando o usuário clica em um hyperlink da SheetFollowHyperlink pasta de trabalho. Ocorre quando a seleção é alterada na pasta de SheetSelectionChange trabalho. Ocorre sempre que a janela da pasta de trabalho é WindowActivate ativada. Ocorre quando a janela da pasta de trabalho é WindowDeactivate desativada. Ocorre quando a janela da pasta de trabalho é WindowResize restaurada. •

Outros eventos Evento AfterUpdate

BeforeDragOver BeforeDropOrPaste BeforeUpdate Change

266

Descrição Ocorre depois da alteração dos dados de um controle, antes do evento Exit e depois do evento BeforeUpdate. Ocorre durante o arraste de um objeto com mouse. Ocorre antes de colar dados em um formulário. Ocorre antes da alteração do conteúdo de um controle. Ocorre sempre que o conteúdo do controle é alterado.


Evento Click DblClick DropButtonClick Enter Exit Initialize KeyDown KeyUp Layout MouseMove Resize SpinDown SpinUp Zoom

6

Descrição Ocorre quando um controle é clicado. Ocorre sempre que o controle recebe um duplo-clique. Ocorre quando a lista do controle é exibida ou fechada. Ocorre quando o controle recebe o foco que estava em outro controle do formulário. Ocorre quando o controle perde o foco porque este foi dado a outro controle do formulário. Ocorre sempre que um objeto é carregado, antes de sua exibição. Ocorre quando uma tecla é pressionada. Ocorre quando uma tecla é liberada. Ocorre quando o tamanho de um objeto é alterado. Ocorre sempre que o mouse é movido sobre o controle. Ocorre quando um formulário é redimensionado. Ocorre quando a seta à esquerda ou inferior de um controle é clicada. Ocorre quando a seta à direita ou superior de um controle é clicada. Ocorre quando o zoom do objeto é alterado.

6.5.1. Procedimento de evento Todos os controles de formulário possuem um conjunto predefinido de eventos relacionados. Ao selecionarmos um controle na lista Objeto da janela Código, a lista Procedimento exibe todos os eventos para o controle selecionado. Cada procedimento de evento é precedido pelo nome do controle ao qual está vinculado. Assim, Salvar_Click é o nome do evento Click para um controle chamado Salvar. É muito importante sabermos que o código não é atualizado automaticamente quando alteramos os nomes dos controles.

Os procedimentos de evento no VBA são sempre procedimentos Sub, e não Function.

267


Para criarmos um procedimento de evento de um formulário, devemos determinar o evento que será utilizado, abrir o VBE e utilizar os seguintes passos: 1. Clique duas vezes no objeto adequado na lista a seguir dos objetos do Excel. Para criar procedimentos de eventos de planilha, por exemplo, devemos selecionar uma das planilhas da pasta de trabalho. A janela Código será aberta; 2. Selecione o objeto e o evento para os quais o código será criado. O objeto deve ser selecionado na lista Objeto, e o evento na lista Procedimento:

3. Clique sobre o evento desejado.

1. Selecione PLAN1; 2. Indique Worksheet;

3. Escreva, na janela Código, o procedimento destinado ao gerenciamento de eventos que será executado sempre que o evento ocorrer.

268


6

No exemplo a seguir, os procedimentos do evento foram inseridos na planilha Plan1. O evento Deactivate será executado quando o usuário sair da planilha Plan1. Desta forma, quando sair da planilha em questão, será executado o comando para selecionar a mesma planilha: Private Sub Worksheet_Deactivate() 'Evento ativado quando sair da Plan1 Sheets("Plan1").Select End Sub

É importante destacar que, quando uma pasta de trabalho é aberta com as macros desativadas, os códigos que criamos (inclusive os procedimentos para eventos) não serão executados. Uma das melhores maneiras de interagir com o usuário é através dos eventos VBA. A linguagem de macros (VBA) é uma linguagem de programação visual, que utiliza o paradigma de orientação a objetos e é voltada para eventos. Para desenvolver soluções que usam o Excel, você pode interagir com os objetos dele pelas suas propriedades, seus métodos e eventos. Qualquer ação de mouse ou de teclado feita pelo usuário dispara dezenas de eventos, nos quais pode haver uma macro programada e, então, o Excel a executará. Como exemplo, podemos citar o MouseMove. Os eventos são ações realizadas pelo usuário, usando o teclado ou o mouse. Por exemplo, ao abrir uma pasta de trabalho, são disparados diversos eventos do objeto Application e do objeto Workbook. Caso exista alguma programação VBA nos eventos, elas serão executadas. Na programação VBA, é possível executar uma macro em resposta a um evento. A princípio, veremos os eventos dos objetos Workbook e Worksheet. Porém, quando estivermos trabalhando com UserForms e seus controles (button, combobox, textbox etc.) veremos que cada um deles também possui suas propriedades, seus métodos e eventos.

269


6.5.2. Eventos do WorkBook 1. Para acessar os eventos do objeto Workbook, na janela de projeto, aplique um duplo-clique em PastadeTrabalho:

2. Note que uma tela de código nova é acessada, com duas listas drop-down, à esquerda e à direita. Na lista da esquerda, selecione Workbook:

3. Na lista drop-down da direita, escolha o evento a ser programado:

270


6

6.5.2.1.Open Este evento ocorre quando a pasta de trabalho é aberta. Private Sub Workbook_Open() Application.Windowstate = xlMaximized End Sub

6.5.2.2.BeforeClose Ocorre antes de uma pasta de trabalho ser fechada. Private Sub Workbook_BeforeClose(Cancel as Boolean) Cells(1,1) = now() End

Note que os eventos Workbook não são colocados no código das planilhas (Worksheets), mas sim no código da pasta de trabalho. Pode acontecer de terem sido criadas as macros auto_open e workbook_open. Ambas serão executadas, a não ser que a pasta de trabalho tenha sido chamada via código VBA, caso em que só será executada a workbook_open. O códigoActiveWorkbook.RunAutoMacros xlAutoOpen acrescido do comando que irá chamar a macro executa a auto_open.

6.5.3. Eventos de Worksheet A seguir, veja os eventos mais comuns utilizados nas planilhas.

271


6.5.3.1.Worksheet_Activate Em uma planilha, considere que, na célula A5, tem um total referente a uma meta (que pode ser de vendas). Mas, em vez de escrever o código em um módulo padrão, selecione o evento e escreva na planilha. Private Sub Worksheet_Activate() If Range("A5").Value < 6500 Then MsgBox "Meta de vendas não atingida!!!" Else MsgBox "Meta de vendas atingida!!! Parabéns!!!" End if End Sub

6.5.3.2.Worksheet_SelectionChange Este evento ocorrerá quando o usuário mudar de célula. Private Sub Worksheet_SelectionChange(ByVal a As Range) MsgBox "Você acaba de ir para a célula " & a.Address End Sub

6.5.3.3.Worksheet_Calculate Este evento ocorrerá toda vez que algum cálculo na planilha for atualizado. Private Sub Worksheet_Calculate() If Range("A5").Value < 6500 Then MsgBox "Meta de vendas não atingida!!!" Else MsgBox "Meta de vendas atingida!!! Parabéns!!!" End If End Sub

272


6

6.5.3.4.Worksheet_Change Este exemplo altera, para azul, a cor das células alteradas. Private Sub Worksheet_Change (ByVal a as Range) a.Font.ColorIndex = 5 End Sub

6.5.4. Eventos com UserForms Com os UserForms podemos criar interfaces personalizadas, diferentes da interface padrão do Excel. Para a criação de um UserForm, seguimos os seguintes passos: 1. Insira um objeto UserForm na pasta de trabalho pelo editor do VBA; 2. Adicione controles com seus respectivos códigos em VBA.

273


É importante notar que, assim como tantos outros objetos, o UserForm, bem como cada um dos seus controles, tem suas respectivas propriedades, métodos e eventos. Podemos usar o método Show (exibir) do objeto UserForm no momento em que a Pasta de Trabalho for aberta, usando o evento Open do Workbook. Veja o exemplo: Private Sub Workbook_Open() UserForm1.Show End sub

O evento Initialize dos Userforms é muito utilizado para definirmos como o usuário verá o formulário na primeira vez de uso. Se usarmos as propriedades Height e Width, junto com o evento Initialize, podemos dimensionar o UserForm na tela do usuário, no momento que ele é chamado. Veja o exemplo: Private Sub UserForm_Initialize() Me.Height = Int(0.98 * ActiveWindow.Height) Me.Width = Int(0.98 * ActiveWindow.Width) End Sub

274


6

Depois de criado o objeto TextBox para que o usuário entre com uma informação, podemos criar o seguinte código no seu evento AfterUpdate (após atualização): Private Sub TextBox1_AfterUpdate() If Not IsNumeric(TextBox1.Value) Then MsgBox "Digite valor númerico!!!" TextBox1.Value = "" TextBox1.SetFocus Exit Sub End If If (TextBox1.Value < 1000) Or (TextBox1.Value > 15000) Then MsgBox "O valor deve estar entre 1000 e 15000!!!" TextBox1.Value = "" TextBox1.SetFocus Exit Sub End If End Sub

Note que, após o preenchimento do TextBox1, o formulário só terá continuidade se o valor preenchido for numérico e entre 1000 e 15000. Uma coisa muito interessante que deve ser enfatizada é o fato de essas propriedades poderem ser definidas tanto pela janela Propriedades quanto em tempo de execução (no meio de uma macro, por exemplo).

275



•

•

•

•

•

276

A caixa de mensagem é utilizada para passar informações ou para executar um comando que depende de uma resposta do usuário; Para exibir uma mensagem, podemos utilizar o comando Msgbox. Com a função Msgbox(), podemos definir uma caixa de mensagem com mais de um botão e identificar qual dos botões foi clicado pelo usuário para, posteriormente, indicar uma ação a ser executada, dependendo do item escolhido; Use InputBox para exibir uma caixa de diálogo simples de forma que você possa inserir informações a serem usadas em uma macro; O método InputBox do objeto Application se diferencia da função InputBox por ter um argumento a mais: type, o último parâmetro, que define o tipo de dado a ser retornado; As caixas de diálogo do Excel são muito úteis para agilizar o trabalho com programação, pois, por meio delas, os próprios usuários podem escolher diversas opções, como formatar fonte, preencher sequências, imprimir etc.; Enquanto utilizamos o Excel, a cada ação do usuário são disparados vários eventos em que se tiver programado alguma macro, esta será executada quando o evento for disparado.

6




1. Qual comando utilizamos para criar uma quebra de linha dentro da caixa de mensagem?

☐

a) Vbcrlf

☐

b) Input

☐

c) Print

☐

d) InputBox

☐

e) Enter

2. Qual opção define melhor o objetivo de usarmos a função InputBox em uma macro?

278

☐

a) Para que o usuário possa inserir informações a serem usadas.

☐

b) Exibir um conteúdo.

☐

c) Tratar um erro.

☐

d) Declarar uma variável.

☐

e) Redimensionar um array.


6

3. Qual a finalidade do argumento Default da função InputBox?

☐

a) Personalizar um título na janela.

☐

b) Definir os botões no InputBox.

☐

c) Definir a cor do InputBox.

☐

d) Exibir o texto como padrão quando a caixa for carregada.

☐

e) No topo da janela, inserir o botão de restaurar seu tamanho.

4. Em que o método InputBox do objeto Application difere da função InputBox?

☐

a) Pelo formato da caixa de diálogo exibida.

☐

b) Pelo argumento Type.

☐

c) Pela possibilidade de o usuário marcar várias opções de respostas.

☐

d) Por criar um novo objeto Application.

☐


279


5. Para qual das ações a seguir o Excel terá algum evento disparado?

280

☐

a) Ao abrir um arquivo.

☐

b) Ao fechar um arquivo.

☐

c) Ao clicar em uma célula.

☐

d) Ao inserir uma planilha.

☐

e) Todas as alternativas anteriores estão corretas.

6

Interagindo com o usuário Mãos à obra!


Laboratório 1 A – Criando uma macro que salva o arquivo quando a planilha for editada Neste laboratório, criaremos uma macro que salva o arquivo toda vez que o usuário editar a planilha, inserindo ou deletando dados em qualquer célula. Para isso, iremos programar o evento Change da Planilha1 Planilha1,, seguindo os passos adiante: 1. Clique com o botão direito do mouse na aba da planilha e escolha a opção Exibir Código: Código:

Com isso, o Editor do Visual Basic é acessado já no módulo Private Private desta planilha:

282


6

2. Escolha o objeto que você vai programar. Nesse caso, a planilha:

3. Note que o Editor “sugere” “ sugere” programar o evento SelectionChange SelectionChange.. Independente desta sugestão, para este laboratório, escolha o evento Change Change::

4. Apague o outro evento que não será programado; 5. No evento Change Change,, utilize o código ThisWorkBook.Save ThisWorkBook.Save::

6. Retorne à planilha e teste. Note que, a cada alteração (inclusão, edição ou exclusão) de qualquer conteúdo em qualquer célula, este arquivo é automaticamente salvo.

283

Objeto UserForm 9 9

Criando formulários; Controles da Caixa de ferramentas do UserForm.


7.1. Introdução O Visual Basic utiliza o objeto UserForm UserForm como como interface entre os usuários e o computador. O objeto UserForm UserForm,, também conhecido como formulário, apresenta-se para o usuário como uma caixa de diálogo que pode incluir controles diversos, como espaços para a inserção de texto, botões (como OK ou Cancelar Cancelar,, entre outros), imagens e caixas de seleção. Todos os controles podem ser associados a ações específicas.

7.2. Criando formulários Tão importante quanto criar de fato o formulário é o processo de seu planejamento. Os formulários são interfaces gráficas do usuário (ou GUI, Graphical User Interface), por isso, suas instruções, aparência e organização de seus elementos devem ser cuidadosamente planejadas para que eles possam ser utilizados de forma simples e eficiente pelos usuários da aplicação. Para criar um formulário, devemos, no Editor VBE, acessar o menu menu Inserir Inserir e, então, clicar na opção UserForm UserForm.. Vale ressaltar que um objeto UserForm UserForm,, ilustrado na imagem seguinte, contém um único formulário. Ao inserir o primeiro UseForm UseForm também também é aberta a Caixa de ferramentas, na qual dispomos de controles que podem ser adicionados aos formulários. Falaremos mais sobre ela adiante. Caixa de ferramentas

Formulários

286

Objeto UserForm

7

Em seguida, devemos utilizar os próximos passos, que serão abordados detalhadamente nos tópicos deste capítulo: 1. Adicione os controles necessários ao UserForm, como botões e caixas de texto; 2. Modifique as propriedades dos controles e do UserForm, utilizando a janela Properties; 3. Escreva procedimentos destinados ao gerenciamento de eventos para os controles, como macros que serão executadas quando botões forem clicados; 4. Escreva, em um módulo VBA, um procedimento que exiba o formulário criado para o usuário.

7.2.1. Inserindo controles em um formulário Inicialmente, todos os formulários criados são vazios, ou seja, não apresentam controles, como botões, caixas de texto ou caixas de opções. Todos os controles devem ser inseridos por meio da Caixa de ferramentas, exibida a seguir:

Grande parte dos controles da Caixa de ferramentas é semelhante aos controles da Barra de ferramentas Formulários do Excel. Para inserir um controle, devemos clicar sobre ele e arrastá-lo até o ponto desejado do formulário. O tamanho do controle pode ser alterado por meio da janela de Propriedades, arrastando-se as alças de edição (da mesma forma que redimensionamos objetos de desenho do Windows) ou por código em tempo de execução.

287


7.2.1.1.Caixa de ferramentas Conheceremos, a seguir, os controles que podem ser inseridos nos formulários a partir da Caixa de ferramentas: Controle Label (rótulo)

TextBox (caixa de texto)

ComboBox (caixa de combinação)

ListBox (caixa de listagem)

Descrição O controle Label é utilizado para exibir um texto no formulário.

O controle TextBox cria uma caixa de texto que permite a inserção de texto pelo usuário. Este controle é similar ao ListBox, com a diferença de que exibe apenas um item por vez, apresentando-se como uma caixa drop-down, e permite a inserção de um valor diferente dos que a lista apresenta.

O controle ListBox permite que o usuário selecione um ou mais itens de uma lista.

CheckBox Este controle é utilizado para obter escolhas binárias, como (caixa de seleção) verdadeiro ou falso e sim ou não.

OptionButton (botão de opção)

ToggleButton (botão de ativação)

288

O controle OptionButton apresenta ao usuário um grupo de dois ou mais itens, dentre os quais um pode ser selecionado. Os controles OptionButton podem ser agrupados por meio da propriedade GroupName ou podem ser colocados dentro de um mesmo controle Frame. Este controle apresenta-se na forma de um botão que, quando clicado, alterna entre os estados ativado (True), cuja aparência é de um botão pressionado, e desativado (False), cuja aparência é de um botão não pressionado.

Objeto UserForm

Controle

Descrição

Frame (moldura)

O controle Frame é utilizado para organizar outros controles, reunindo grupos lógicos ou apenas organizando visualmente os elementos do formulário.

CommandButton (botão de comando)

TabStrip (faixa de tabulação)

MultiPage (multipágina)

ScrollBar (barra de rolagem)

7

O controle CommandButton apresenta-se para o usuário como um botão que pode ser clicado. Este controle é utilizado com procedimentos que manipulam eventos, que são executados quando o botão é clicado. Ao clicarmos neste controle, uma macro armazenada na janela Código do UserForm é executada. O nome dessa macro deve ser composto pelo nome do botão seguido de um underline e da palavra Click, por exemplo, Abrir_Click. O controle TabStrip exibe guias no formulário. Este controle é semelhante ao controle MultiPage, porém, sua utilização é mais complexa. Este controle é utilizado para criar caixas de diálogo com guias. O padrão é que o controle apresente duas guias, mas podemos inserir outras clicando com o botão direito do mouse sobre uma guia existente e selecionando a opção New Page do menu de contexto. O controle ScrollBar é semelhante ao SpinButton, com a diferença de que permite que o usuário arraste o botão do controle para aumentar ou diminuir os valores com incrementos maiores. Este controle, normalmente, é utilizado quando existe a necessidade de o usuário especificar um valor dentro de um intervalo de valores possíveis.

SpinButton O controle SpinButton apresenta ao usuário duas setas, (botão de rotação) uma apontando para cima e outra para baixo, as quais aumentam ou diminuem um valor quando clicadas.

289


Controle

Image (imagem)

Descrição O controle Image é utilizado para exibir imagens no formulário, como fotos ou logotipos. As imagens dos controles Image são armazenadas junto com a pasta de trabalho. Por um lado, isso traz a vantagem de não ser necessário enviar arquivos adicionais quando compartilhamos a pasta de trabalho com alguém; por outro lado, o tamanho do arquivo da pasta de trabalho pode aumentar muito. É recomendável, portanto, utilizarmos imagens pequenas. Para inserir um clip-art no formulário, devemos primeiramente inseri-lo em uma planilha e, então, copiá-lo e colá-lo no formulário.

Podemos utilizar o método AddItem para adicionar itens ao controle, caso a lista de itens não esteja disponível em uma planilha.

7.2.2. Configurando as propriedades dos controles O objeto UserForm e cada um dos controles que podem ser inseridos em um formulário possuem propriedades diversas que, de acordo com o valor definido, fazem com que os controles apresentem uma aparência ou comportamento específico. Essas propriedades podem ser visualizadas e editadas por meio da janela de Propriedades do VBE, exibida na próxima imagem. É importante salientar que essa janela exibe as propriedades do controle atualmente selecionado no formulário.

290

Objeto UserForm

7

Serão exibidas as propriedades do objeto selecionado.

Caso a janela de Propriedades não esteja visível, basta pressionar a tecla F4, ou acessar o menu Exibir e clicar na opção Janela 'Propriedades'. As principais propriedades dos controles de formulários estão descritas na próxima tabela. É importante destacar que essas propriedades variam de controle para controle, ou seja, não estão presentes, obrigatoriamente, em todos eles, apesar de alguns controles possuírem propriedades comuns. Propriedade Accelerator

AutoSize

Descrição Define uma tecla que acessa o controle quando pressionada em conjunto com a tecla ALT, por exemplo, ALT + L. Quando definida como True, faz com que o controle seja automaticamente redimensionado de acordo com a largura do texto que é inserido. Quando definida como False, a largura do controle é fixa.

291


Propriedade

Descrição Quando definida como True, ativa a ocorrência da tabulação automática para o próximo controle sempre que o número AutoTab máximo de caracteres permitido para o controle for inserido. Quando definida como False, não ocorre tabulação. Quando definida como True, as palavras são consideradas unidades básicas se o usuário estender uma seleção (ao colocar o ponto de inserção no meio de uma palavra e AutoWordSelect arrastar, toda a palavra será selecionada). Quando definida como False, os caracteres são tomados como unidades básicas. BackColor Define a cor de preenchimento do controle. O valor 1 define o plano de fundo como opaco, e o valor 0 BackStyle define o plano de fundo como transparente, caso em que a propriedade BackColor será ignorada. Define a cor da borda do controle. Para a visualização da BorderColor borda, a propriedade BorderStyle deverá ser definida com o valor 1. O valor 1 define uma borda simples para o controle, e o BorderStyle valor 0 faz com que o controle não possua bordas, caso em que a propriedade BorderColor será ignorada. Quando o controle possui várias colunas, indica a coluna BoundColumn que contém os dados que serão armazenados. Quando definido como True, faz com que o controle assuma Cancel a função do botão Cancelar. Quando definido como False, o controle não assume essa função. Define o rótulo do controle, ou seja, o texto que é exibido Caption para o usuário. Esta é uma propriedade utilizada nos controles ComboBox e ListBox. Quando definida como 0, o controle não exibe ColumnCount nenhuma coluna da lista. Quando definida como -1, todas as colunas da lista são exibidas pelo controle. Especifica a célula que armazena ou define o valor do controle. A utilização da propriedade ControlSource em controles ControlSource SpinButton deve ser evitada em planilhas que levam muito tempo para serem calculadas, já que essa propriedade faz com que toda a planilha seja recalculada a cada alteração.

292

Objeto UserForm

Propriedade ControlTipText Default

Enabled Font ForeColor GroupName

Height

HideSelection

IntegralHeight

LargeChange Left

7

Descrição Define um texto exibido sempre que o mouse apontar para o controle, como um comentário. Quando definida como True, o controle passa a assumir a função de botão de comando padrão do formulário, podendo ser acessado com a tecla ENTER. Quando definida como True, o controle é habilitado, ou seja, pode receber foco. Quando definida como False, o controle é desabilitado. Define o formato da fonte para o texto do controle. Define a cor do texto do controle. Define um nome de grupo para o controle. Os controles que possuem o mesmo valor para essa propriedade são associados, formando grupos. Caso essa propriedade não seja definida, todos os controles farão parte de um só grupo. Define a largura do controle, utilizando pontos (cada ponto é igual a 0,0353 cm). Quando definida como True, faz com que o texto do controle perca o foco, ou seja, deixe de ser selecionado quando o usuário faz a seleção de outro controle. Quando definida como False, o texto mantém o foco mesmo após outro controle ser selecionado. Quando definida como True, faz com que o controle seja automaticamente redimensionado de acordo com a altura do texto, ou seja, a quantidade de linhas que são inseridas. Quando definida como False, a altura do controle é fixa. É importante ressaltar que, ao definirmos a propriedade IntegralHeight como True, o Visual Basic ajustará a altura do controle para que a última entrada possa ser visualizada por inteiro. Assim, é possível que a altura do controle exibido ao usuário seja diferente da altura que definimos. Define o valor que será incrementado ou decrementado a cada clique à esquerda ou à direita do botão de um controle ScrollBar. Define a posição do controle em relação à borda esquerda do controle que o contém.

293


Propriedade ListRows ListStyle Locked

Descrição Define quantas linhas serão exibidas pelo controle. Caso o controle possua uma quantidade de itens maior que o número definido nesta propriedade, será exibida uma barra de rolagem. Define a aparência dos itens exibidos pelo controle. Quando definida como True, o controle é bloqueado, não aceitando edições. Quando definida como False, o controle pode ser editado normalmente. Define como é feita a pesquisa enquanto o usuário digita um valor no controle: •

MatchEntry •

Max MaxLenght Min

294

Quando definida como 1, a pesquisa considera a sequência de caracteres digitados pelo usuário: ao digitar A, será exibido Acre; em seguida, se o usuário digitar L, será exibido Alagoas, e se digitar M será exibido Amapá;

Quando definida como 2, não há pesquisa; o controle exibirá apenas os caracteres digitados pelo usuário. Quando definida como True, não aceita entradas que não correspondam exatamente a algum item da lista. Quando definida como False, permite que o usuário insira qualquer sequência de caracteres, mesmo que não corresponda a nenhum item da lista. Define o valor máximo aceito pelo controle. É muito utilizado nos controles SpinButton e ScrollBar. Define o número de caracteres que podem ser inseridos no controle. Define o valor mínimo aceito pelo controle. É muito utilizado nos controles SpinButton e ScrollBar. •

MatchRequired

Quando definida como 0, serão exibidas ao usuário as primeiras palavras da lista que sejam iniciadas com cada letra digitada, uma de cada vez: em uma lista com os Estados brasileiros, por exemplo, um usuário que deseje digitar Pernambuco encontrará a seguinte situação: ao digitar P, será exibido Pará; ao digitar o E, será exibido Espírito Santo; e ao digitar o R será exibido Rio de Janeiro;

Objeto UserForm

7

Propriedade

Descrição Define um ícone personalizado para o ponteiro do mouse, exibido quando este for posicionado sobre o controle. O estilo do ponteiro pode ser indicado por meio da pasta MouseIcon que contém os ponteiros de mouse, a qual está localizada dentro da pasta Windows. Para utilizar esta propriedade, é necessário que a propriedade MousePointer esteja definida com o valor 99. Define um ícone para o ponteiro do mouse, exibido quando MousePointer este for posicionado sobre o controle. O valor desta propriedade pode ser um número de 0 a 15 ou 99. Quando definida como True, faz com que o controle exiba o MultiLine texto em várias linhas. Quando definida como False, o texto é exibido em uma única linha. Quando definida como 1 ou 2, permite que o usuário selecione mais de um item da lista exibida pelo controle. MultiSelect Vale salientar que, com isso, não é possível especificar uma propriedade ControlSource. Para definir os itens que serão selecionados, é necessário escrever uma macro. Define um nome utilizado para fazer referências ao controle nos procedimentos. É recomendável adotar um padrão para nomear os controles, para que sua identificação e a Name manutenção do código sejam mais simples. Podemos, por exemplo, utilizar prefixos que identifiquem o tipo de controle, como txtNomeCurso e imgFotoAluno. Define o caractere que será exibido no lugar de cada PasswordChar caractere digitado no controle. Esta propriedade é utilizada em campos de senha, por exemplo. Picture Define uma figura para ser exibida no controle. Define a forma como uma imagem será exibida no formulário PictureSizeMode quando os tamanhos do controle e da imagem não forem correspondentes. Define o intervalo de células onde estão armazenados os RowSource itens que são exibidos no controle. De acordo com o valor, define a presença de barras de rolagem no controle: nenhuma barra de rolagem (0), apenas ScrollBars a barra de rolagem horizontal (1), apenas a barra de rolagem vertical (2) ou ambas (3).

295


Propriedade

Descrição Define o valor do incremento do controle (utilizado nos SmallChange controles SpinButton e ScrollBar). Define o local de origem dos dados exibidos pelo controle, Source como um intervalo de células. Utiliza valores de 0 a 6 para definir como o controle será SpecialEffect apresentado. Quando definida como 2, não aceita entradas que não correspondam exatamente a algum item da lista. Quando Style definida como 0, permite que o usuário insira qualquer sequência de caracteres, mesmo que não corresponda a nenhum item da lista. Determina qual é a posição do controle na ordem de tabulação. A tabulação (através da tecla TAB) muda o foco de um controle para outro em ordem crescente, de acordo com TabIndex o número que cada controle possui para esta propriedade. Também é possível realizar o controle de tabulação utilizando a opção Ordem de Tabulação contida no menu Exibir do Editor VBE. Quando definida como True, o controle permite que o usuário insira tabulações no texto digitado. Quando definida como TabKeyBehavior False, pressionar a tecla TAB moverá o foco para o próximo controle de acordo com a ordem de tabulação. Quando definida como True, permite que o controle receba TabStop foco com a tecla TAB. Quando definida como False, o controle não recebe foco. Text Define ou retorna o texto do controle. Define o alinhamento à esquerda (1), centralizado (2) ou à TextAlign direita (3) para o texto do controle. TextColumn Define a coluna do controle que será exibida ao usuário. Define a posição do controle em relação à borda superior do Top controle que o contém.

296

Objeto UserForm

Propriedade

7

Descrição Esta propriedade varia de acordo com alguns controles: •

•

Para os controles ComboBox e ListBox, a propriedade Value define um texto para o item selecionado pelo usuário; Para o controle CheckBox, a propriedade Value pode ser definida como True, apresentando uma marca de checagem no controle, ou como False, apresentando o controle sem essa marca;

Value •

•

Para o botão OptionButton, a propriedade pode fazer com que o botão aparente estar selecionado (True), ou não (False); Para o controle MultiPage, esta propriedade define qual guia é exibida (0 exibe a primeira guia, 1 exibe a segunda, 2 exibe a terceira, e assim sucessivamente);

Para os controles ScrollBar e SpinButton, esta propriedade define o valor atual do controle. Quando definida como True, determina que o controle seja visível no formulário. Quando definida como False, o controle é oculto. Define a largura do controle, em pontos. Quando definida como True, o texto do controle apresenta quebras automáticas de linha. Quando definida como False, as quebras de linha são desativadas. •

Visible Width WordWrap

297


7.2.3. Configurando a aparência dos controles É importante criarmos formulários de fácil utilização, ou seja, que possuam um número pequeno de controles, com tamanhos adequados e alinhados perfeitamente (podemos considerar como parâmetro, por exemplo, a aparência das caixas de diálogo do Excel).

Caso seja necessário um grande número de controles, é recomendável organizá-los em controles MultiPage (multipáginas), de forma que grupos pequenos sejam exibidos em cada guia. A Barra de ferramentas UserForm, exibida a seguir, possui ferramentas úteis no trabalho de edição e organização dos controles. Para visualizá-la, devemos clicar no menu Exibir, depois em Barras de ferramentas e, em seguida, em UserForm.

Algumas dessas ferramentas são descritas na tabela a seguir: Opção

Descrição Altera o zoom da exibição da caixa de diálogo. Alinha dois ou mais controles entre si com as opçõesEsquerdas, Centros, Direitas, Superiores, Meios, Inferiores, ou um ou mais controles com a opção À grade. Os controles são alinhados em relação à posição do controle que possui as alças de edição brancas (antes de acessar esse botão, é necessário que os objetos a serem alinhados estejam selecionados). Centraliza um ou mais controles em relação ao formulário, por meio das opções Horizontalmente e Verticalmente. Define a mesma altura, a mesma largura ou o mesmo tamanho (altura e largura) para os controles selecionados, por meio das opções Altura, Largura e Ambos.

298

Objeto UserForm

7

A seleção simultânea de vários controles é muito utilizada para agilizar a edição da aparência e até mesmo das propriedades dos controles. Com vários controles selecionados, podemos arrastar as alças de edição de apenas um deles para que todos sejam redimensionados proporcionalmente de forma automática, ou alterar as propriedades de todos os controles de uma única vez. É importante notarmos que a janela de Propriedades exibirá apenas as propriedades que são comuns a todos os controles selecionados. Para selecionar simultaneamente vários controles adjacentes, basta clicarmos no primeiro controle e mantermos a tecla SHIFT pressionada enquanto clicamos no último controle, ou clicarmos e arrastarmos o mouse sobre a área que contém os controles que desejamos selecionar. Também é possível selecionarmos simultaneamente vários controles não adjacentes mantendo a tecla CTRL pressionada enquanto clicamos nos controles desejados. Muitas vezes, é necessário inserir vários controles do mesmo tipo (por exemplo: label, textbox). Em casos assim, podemos copiar o controle com a utilização dos atalhos CTRL + C e CTRL + V, ou, ainda, clicando sobre o controle de origem e, com a tecla CTRL pressionada, arrastando com o mouse até a posição de destino.

7.3. Controles da Caixa de ferramentas do UserForm Para entendermos o funcionamento de todas as etapas de criação de um formulário, criaremos um para efetuar o cadastramento de alunos. Os dados serão cadastrados no próprio formulário e inseridos na planilha denominada Alunos quando for pressionado o botão Cadastrar do formulário. Para a criação de tal formulário, devemos seguir os passos adiante: 1. Crie uma pasta de trabalho contendo as planilhas Alunos e Apoio. Na planilha Alunos, serão armazenadas as informações cadastradas. Utilizaremos a planilha Apoio para guardar as informações a serem exibidas no formulário;

299


2. Digite as informações conforme o exemplo e nomeie o intervalo de células "A2:B6" como cursos:

3. Na planilha Alunos, faça como mostrado no exemplo seguinte:

•

1ª Etapa: Criação do layout do formulário

1. Acesse o VBE e insira um novo formulário por meio da opção UserForm, contida no menu Inserir; 2. Nomeie o formulário (que até o momento tem o nome de UserForm1) por meio da janela Properties, alterando o conteúdo da propriedade Name para frmAlunos;

300

Objeto UserForm

7

3. Altere o texto contido na Barra de título do formulário, alterando o conteúdo da propriedade Caption para Cadastro de Alunos; 4. Para executar o formulário, clique uma vez sobre ele e pressionar a tecla de função F5; Conteúdo inserido na propriedade Caption.

5. Para obter os controles que serão inseridos no frmAlunos, utilize a Caixa de ferramentas, contida em Exibir; 6. Insira um rótulo, alterando a propriedade Name para IblAluno e a propriedade Caption para Nome:

7. Insira outro rótulo abaixo do primeiro. Nesse caso, podemos usar o mesmo procedimento para a criação do primeiro rótulo ou efetuar uma cópia dele. A propriedade Name deve ser alterada para lblCurso, e a propriedade Caption deve ser alterada para Curso;

301


8. Alinhe os controles mantendo a tecla SHIFT pressionada e, então, clique em todos os controles desejados a fim de selecioná-los. Em seguida, clique com o botão direito do mouse e acesse a opção desejada dentro do submenu Alinhar, como mostra o exemplo seguinte:

9. Insira o controle Caixa de Texto alterando a propriedade Name para txtAluno, a propriedade MaxLength para 40 (que é o número máximo de caracteres a serem inseridos no controle) e a propriedade Width para 195 (que é a largura do controle): Controle Caixa de Texto.

302

Objeto UserForm

7

10. Insira, na frente da palavra Curso, o controle Caixa de Combinação, o qual permitirá que o usuário escolha o curso desejado de acordo com uma lista predefinida. Este controle buscará as informações referentes aos cursos que estão digitadas na planilha Apoio, região nomeada como Cursos; 11. Altere a propriedade Name para cmbCurso, a propriedade RowSource para Cursos (nome da região indicada na planilha Apoio), a propriedade ListRows para 5 (número de itens a serem exibidos) e a propriedade Style para 2 (somente poderá ser digitado um valor contido na lista). Veja, na imagem adiante, qual é a aparência após executar:

ListRows: 5

Será possível observar que são exibidas apenas as informações contidas na primeira coluna do intervalo Cursos, porém, o controle Caixa de Combinação permite a visualização das demais colunas do intervalo, conforme o status da propriedade ColumnCount deste controle, sendo: •

0, para não exibir coluna alguma;

•

-1, para exibir todas as colunas;

•

Outro número, para exibir um número específico de colunas.

A intenção é exibir apenas os nomes dos cursos, ou seja, a primeira coluna do intervalo nomeado (ColumnCount = 1), pois, no momento em que selecionarmos um curso na Caixa de Combinação, deverá ser exibido o valor correspondente em um rótulo.

303


12. Crie mais dois rótulos, alterando, no primeiro rótulo, a propriedade Name para IblRotValor e a propriedade Caption para Valor:. No segundo rótulo, altere a propriedade Name para IblValor, a propriedade Caption para 0,00 e a propriedade TextAlign para 3fmTextAlignRight; 13. Caso necessário, selecione todos os controles desta linha e efetue o alinhamento. Para tanto, clique com o botão direito do mouse sobre a seleção, selecione Alinhar e, então, Inferiores:

14. Insira o controle Quadro alterando a propriedade Name para framTipo e a propriedade Caption para Tipo de Pagamento; 15. No controle Quadro, insira dois controles Botão de Opção alterando as seguintes propriedades: •

•

•

•

Name: optPrazo; Caption: A Prazo. Name: optVista; Caption: À Vista.

16. Insira dois controles Botão de Comando, alterando as seguintes propriedades: •

•

•

304

Name: cmdCadastrar; Caption: Cadastrar; Accelerator: C (define a tecla de atalho para o controle).

Objeto UserForm

•

•

•

7

Name: cmdFechar; Caption: Fechar; Accelerator: F (define a tecla de atalho para o controle).

Controles: Quadro / Botão de Opção

Botões de Comando

No momento em que for indicado o tipo de pagamento por meio de um dos botões de opção, será efetuada uma das condições: •

•

À Vista: Será exibido um controle indicando o valor total a ser pago, com 15% de desconto sobre o valor do curso; A Prazo: De acordo com a indicação do número de parcelas, deverá ser exibido o valor de cada uma das parcelas, sendo possível a divisão em até 12 vezes iguais.

17. Crie os seguintes controles, os quais ficarão ocultos, sendo exibidos somente no momento do clique no botão de opção A Prazo: •

Propriedades dos rótulos •

•

•

•

•

•

Name: lblRotQtde; Caption: Parcelas; Visible: False (oculta o controle). Name: lblRotlParcela; Caption: Valor / Parcela Visible: False (oculta o controle).

305


•

•

•

•

•

•

Propriedades da Caixa de Texto •

•

•

•

Name: lblValParcela; Caption: 0,00 SpecialEffect: 2 (efeito para o controle); TextAlign: 3 fmTextAlignRight (alinhamento de texto à direita); Visible: False (oculta o controle).

Name: txtQtde; Text: 2; Visible: False (oculta o controle).

Propriedades do Botão de Rotação •

•

•

•

•

Name: spiQtde; Min: 2 (valor mínimo); Max: 12 (valor máximo); SmallChange: 1 (valor incremental); Visible: False (oculta o controle).

Organize-os da seguinte maneira:

Estes controles serão exibidos somente quando indicado o tipo de pagamento A Prazo.

306

Objeto UserForm

7

18. Crie os seguintes controles a serem exibidos no momento do clique no botão de opção À Vista: •

Propriedades dos Rótulos: •

•

•

•

•

•

•

Name: lblRotVista; Caption: Total a Pagar; Visible: False (oculta o controle). Name: lblVista; Caption: 0,00; TextAlign: 3 fmTextAlignRight (alinhamento de texto à direita). SpecialEffect: 2 (efeito para o controle).

Estes controles serão exibidos somente quando indicado o tipo de pagamento À Vista.

•

2ª Etapa: Tornando o formulário funcional – eventos dos controles

Após a criação do layout do formulário é necessário automatizar os controles. Este recurso ocorre através dos eventos, ou seja, o evento é acionado quando ocorre alguma ação em um controle, por exemplo, um clique em um botão, a inserção de dados em uma caixa de texto etc. Você deve realizar os seguintes passos:

307


1. Dentro do evento, digite o código que será executado quando esse evento ocorrer para o controle. A seguir, temos descritas duas formas para editar um evento: •

•

Clique duas vezes no controle para abrir o evento automaticamente; Dentro do Editor VBE, indique o controle dentro da Caixa de seleção Objetos do Formulário e selecione o evento em Procedimento.

Procedimentos

Objetos do Formulário

2. Dentro do evento, digite o código que será executado quando esse evento ocorrer para o controle.

308

Objeto UserForm

7

Podemos citar como exemplo o controle Botão de Comando, o qual receberá um código para fechar o formulário quando ocorrer o evento Clicar nesse controle. Este é um evento padrão para esse controle, ou seja, se, no VBE, dermos um duplo-clique sobre o botão, será criada a instrução para este evento, não sendo necessário escolher evento algum na Lista de Procedimentos:

3. Clique duas vezes sobre o controle cmdFechar e digite o comando UnloadMe para fechar o formulário atual, como mostrado a seguir: Private Sub CmdFechar_Click() Unload Me End Sub

309


4. Na área de edição de código, execute o formulário por meio de uma das seguintes opções: •

•

Pressione a tecla de função F5; Na janela Project Explorer, dê um duplo-clique em frmAlunos e, em seguida, pressione F5.

Então, será necessário automatizar os demais controles no formulário. Por exemplo, ao selecionarmos um determinado curso no controle cmbCurso, deverá ser exibido o seu valor no controle lblValor, ou seja, o evento Change da caixa de combinação irá buscar na planilha Apoio o valor correspondente ao item selecionado, o qual está na mesma linha do curso e em uma coluna à direita. A seguir, temos os dados da Planilha Apoio: CURSO Windows Word Excel Power Point Access

VALOR R$ R$ R$ R$ R$

290,00 350,00 450,00 380,00 440,00

Alguns itens devem ser observados com atenção. São eles: •

•

•

•

310

Intervalo na planilha foi nomeado como Cursos; Este mesmo intervalo está sendo utilizado pelo controle cmbCurso (propriedade RowSource); A caixa de combinação exibe somente as informações da primeira coluna; Quando selecionado um item na caixa de combinação (primeira coluna), deverá ser exibido o valor que está na mesma linha e na primeira coluna à direita, a propriedade Column(1).

Objeto UserForm

7

5. Clique duas vezes no controle cmbCurso e digite o seguinte código: Private Sub CmbCurso_Change() lblValor = CmbCurso.Column(1) End Sub

Durante a execução do formulário, ao escolhermos um determinado curso, será exibido, no controle lblValor, o valor correspondente a ele. No entanto, esse valor não será apresentado com a mesma formatação numérica que se apresentava na planilha, sendo necessário realizar a formatação deste valor também dentro do formulário.

Valor sem formatação.

6. Defina a formatação do valor a ser exibido no controle Rótulo utilizando a função Format, conforme o exemplo a seguir: lblvalor = Format(cmbcurso.Column(1), "#,##0.00")

Ao clicarmos no botão de opção optVista, serão necessárias as seguintes ações: •

Garantir que os controles de opção A Prazo estejam ocultos;

•

Deixar visíveis os controles da opção À Vista;

•

Exibir o valor do curso com 15% de desconto.

311


Private Sub optVista_Click() 'ocultar controles da opção "A Prazo" lblRotQtde.Visible = False txtqtde.Visible = False spiqtde.Visible = False lblRotParcela.Visible = False lblValParcela.Visible = False 'exibir controles da opção "À Vista" lblRotVista.Visible = True lblVista.Visible = True 'Calcular o valor com 15% de desconto lblVista = Format(lblvalor - (lblvalor * 0.15), "#,##0.00") End Sub

Para o procedimento do controle optPrazo, são necessárias as seguintes ações: •

Garantir que os controles da opção À Vista estejam ocultos;

•

Deixar visíveis os controles da opção A Prazo;

•

Calcular o valor da parcela. Private Sub optPrazo_Click() 'ocultar controles da opção "À Vista" lblRotVista.Visible = False lblVista.Visible = False 'Exibir controles da opção "A Prazo" lblRotQtde.Visible = True txtqtde.Visible = True spiqtde.Visible = True lblRotParcela.Visible = True lblValParcela.Visible = True 'Calcular o valor de cada parcela lblValParcela = Format(lblvalor / txtqtde, "#,##0.00") End Sub

312

Objeto UserForm

7

7. Defina o código para o controle spiQtde para que seja exibido o seu valor automaticamente na caixa de texto txtQtde e, também, para que seja atualizado o valor das parcelas no controle lblParcela. Para tanto, clique duas vezes no Botão de Rotação e digite o seguinte código: Private Sub SpiQtde_Change() txtQtde = SpiQtde lblValParcela = format(lblValor / txtQtde,"#,##0.00") End Sub

8. Depois de automatizados os botões de opção À vista e A Prazo, clique duas vezes no controle cmbCurso e oculte todos os controles, conforme o exemplo a seguir, pois eles deverão ser exibidos somente com a ativação de um dos botões de opção: Private Sub cmbcurso_Change() 'Exibir o valor do curso no Rótulo lblvalor = Format(cmbcurso.Column(1), "#,##0.00") 'Ocultar os controles lblRotQtde.Visible = False txtqtde.Visible = False spiqtde.Visible = False lblRotParcela.Visible = False lblValParcela.Visible = False lblRotVista.Visible = False lblVista.Visible = False optVista = False optPrazo = False txtQtde = 2 End Sub •

3ª Etapa: Inserindo as informações na planilha

Utilizaremos o evento Click do botão de comando cmdCadastrar a fim de inserir os dados cadastrados no formulário para a planilha Dados.

313


Iniciaremos o código realizando as consistências nos controles, pois os dados só serão cadastrados na planilha se todas as informações tiverem sido inseridas no formulário. Caso exista alguma informação pendente no formulário, um alerta deverá ser exibido ao usuário: Private Sub cmdCadastrar_Click() 'Consistências If txtAluno = "" Then MsgBox "Digite o nome do aluno" txtAluno.SetFocus Exit Sub ElseIf IsNumeric(txtAluno) Then MsgBox "Informação Inválida" txtAluno = "" txtAluno.SetFocus Exit Sub End If If cmbCurso = "" Then MsgBox "Selecione o curso desejado" cmbCurso.DropDown Exit Sub End If If optVista = False And optPrazo = False Then MsgBox "Indique o tipo de Pagamento" End If End Sub

O método SetFocus permite posicionar o cursor dentro do controle. Já o método DropDown que foi utilizado no controle cmbCurso é responsável por expandir a caixa de combinação, permitindo ao usuário a escolha de uma opção contida na lista. Após as consistências, podemos prosseguir o procedimento com os comandos para inserir as informações na planilha, continuando dentro do evento cmdCadastrar_Click.

314

Objeto UserForm

7

Na primeira coluna da planilha (Coluna A), iremos identificar os alunos por um número de registro, que será incrementado automaticamente, iniciando a primeira linha no valor 1. O código a seguir irá posicionar o cursor na primeira célula vazia da coluna A. Se a célula da linha anterior não for numérica, significa que o código posicionou o ponteiro na célula A2 e a célula anterior é o título. Neste caso, o registro será iniciado com a inserção do valor 1. Caso contrário, será obtido o valor da célula anterior adicionando +1. Sheets("Alunos").Select Range("a65536").End(xlUp).Offset(1, 0).Select if Not IsNumeric(ActiveCell.Offset(-1, 0)) Then ActiveCell = 1 Else ActiveCell = ActiveCell.Offset(-1, 0) + 1 End If

Agora, é necessário colocar nas demais colunas da planilha as informações contidas nos controles do formulário: ActiveCell.Offset(0, 1) = ActiveCell.Offset(0, 2) = ActiveCell.Offset(0, 3) = If optVista = True Then ActiveCell.Offset(0, ActiveCell.Offset(0, ActiveCell.Offset(0, ActiveCell.Offset(0, Else ActiveCell.Offset(0, ActiveCell.Offset(0, ActiveCell.Offset(0, ActiveCell.Offset(0, End If

txtAluno cmbCurso lblvalor 4) 5) 6) 7)

= = = =

"À Vista" "15%" 1 lblVista

4) 5) 6) 7)

= = = =

"A Prazo" "-" txtQtde lblValParcela

315


Ao efetuarmos o cadastro, observamos que os valores numéricos (Parcelas / Valor por Parcela) foram inseridos na planilha como texto, inclusive, estão alinhados à esquerda da célula. Para que o valor seja inserido na célula como valor numérico, é necessário utilizar a função Cdbl() que irá realizar a conversão. Apesar de termos formatado essas informações dentro do procedimento dos controles com a função Format() após a conversão para valor numérico, será necessário também formatá-las na célula da planilha: Valores inseridos como texto

No código anterior, foram acrescentadas a função Cdbl() e CInt() para a conversão numérica e a propriedade NumberFormat para a formatação da célula: ActiveCell.Offset(0, 3) = CDbl(lblvalor) If optVista = True Then ActiveCell.Offset(0, 4) = "À Vista" ActiveCell.Offset(0, 5) = "15%" ActiveCell.Offset(0, 6) = 1 ActiveCell.Offset(0, 7) = CDbl(lblVista) Else ActiveCell.Offset(0, 4) = "A Prazo" ActiveCell.Offset(0, 5) = "-" ActiveCell.Offset(0, 6) = CInt (txtQtde) ActiveCell.Offset(0, 7) = CDbl(lblValParcela) End If ActiveCell.Offset(0, 3).NumberFormat = "#,##0.00" ActiveCell.Offset(0, 7).NumberFormat = "#,##0.00"

316

Objeto UserForm

7

Observe o exemplo após a alteração da macro e o recadastramento do registros anteriores, a partir do início:

Após a inserção das informações, vemos que o conteúdo permanece nos controles da janela de cadastro, no entanto, podemos limpar o conteúdo dos controles do formulário e posicionar o cursor no controle txtAluno para o que o usuário possa digitar um novo registro: txtAluno = "" lblvalor = "0,00" cmbcurso = "" optVista = False optPrazo = False lblRotQtde.Visible = False txtQtde.Visible = False spiQtde.Visible = False lblRotParcela.Visible = False lblValParcela.Visible = False lblRotVista.Visible = False lblVista.Visible = False txtAluno.SetFocus

317


Ao executar este procedimento, ocorrerá um erro de execução no evento Change do controle cmbCurso, isto porque, ao executar o comando cmbCurso = "", a informação vazio será procurada dentro da planilha Apoio.

A fim de resolver este problema, devemos aplicar um duplo-clique no controle cmbCurso e inserir a seguinte consistência: Private Sub cmbcurso_Change() If cmbcurso = "" Then Exit Sub End If

.... continuação dos demais comandos End Sub

Podemos centralizar as informações contidas nas células conforme o exemplo a seguir: ActiveCell.Offset(0, 4).HorizontalAlignment = xlCenter

Observe todo o conteúdo do procedimento inserido no controle cmdCadastrar: Private Sub cmdCadastrar_Click() 'Consistências If txtAluno = "" Then MsgBox "Digite o nome do aluno" txtAluno.SetFocus Exit Sub ElseIf IsNumeric(txtAluno) Then MsgBox "Informação Inválida" txtAluno = "" txtAluno.SetFocus Exit Sub

318

Objeto UserForm

7

End If If cmbCurso = "" Then MsgBox "Selecione o curso desejado" cmbCurso.DropDown Exit Sub End If If optVista = False And optPrazo = False Then MsgBox "Indique o tipo de Pagamento" End If 'inserindo dados na planilha Sheets("Alunos").Select Range("a65536").End(xlUp).Offset(1, 0).Select If Not IsNumeric(ActiveCell.Offset(-1, 0)) Then ActiveCell = 1 Else ActiveCell = ActiveCell.Offset(-1, 0) + 1 End If ActiveCell.Offset(0, 1) = txtAluno ActiveCell.Offset(0, 2) = cmbCurso ActiveCell.Offset(0, 3) = CDbl(lblValor) If optVista = True Then ActiveCell.Offset(0, 4) = "À Vista" ActiveCell.Offset(0, 5) = "15%" ActiveCell.Offset(0, 6) = 1 ActiveCell.Offset(0, 7) = CDbl(lblVista) Else ActiveCell.Offset(0, 4) = "A Prazo" ActiveCell.Offset(0, 5) = "-" ActiveCell.Offset(0, 6) = CInt(txtQtde) ActiveCell.Offset(0, 7) = CDbl(lblValParcela) End If ActiveCell.Offset(0, 3).NumberFormat = "#,##0.00" ActiveCell.Offset(0, 7).NumberFormat = "#,##0.00" 'Centralizando as informações nas células ActiveCell.Offset(0, 4).HorizontalAlignment = xlCenter ActiveCell.Offset(0, 5).HorizontalAlignment = xlCenter 'Limpando o conteúdo dos controles txtAluno = "" lblValor = "0,00" cmbCurso = "" optVista = False optPrazo = False lblRotQtde.Visible = False

319


txtQtde.Visible = False spiQtde.Visible = False lblRotParcela.Visible = False lblValParcela.Visible = False lblRotVista.Visible = False lblVista.Visible = False 'Posicionando o cursor no controle txtAluno.SetFocus End Sub Private Sub cmdFechar_Click() Unload Me End Sub Private Sub optPrazo_Click() 'ocultar controles da opção "À Vista" lblRotVista.Visible = False lblVista.Visible = False 'Exibir controles da opção "A Prazo" lblRotQtde.Visible = True txtQtde.Visible = True spiQtde.Visible = True lblRotParcela.Visible = True lblValParcela.Visible = True 'Calcular o valor de cada parcela lblValParcela = Format(lblValor / txtQtde, "#,##0.00") End Sub •

Criando um formulário para inserir um novo curso na planilha Apoio

Como o controle Caixa de Combinação está capturando as informações contidas no intervalo denominado Cursos da planilha Apoio, criaremos um formulário para inserir um novo curso na planilha, o qual pertencerá à lista de opções do cmbCurso do formulário de alunos.

320

Objeto UserForm

7

Primeiramente, criaremos um novo formulário com o nome frmCursos, conforme o exemplo adiante:

•

Propriedades dos Objetos •

•

•

•

Name: frmCurso; Caption: Inclusão de Cursos. Name: lblRotCurso; Caption: Curso.

•

Name: lblRotValor; Caption: Valor.

•

Name: txtCurso.

•

Name: txtValor.

•

•

•

Name: cmdInserir; Accelerator: I.

321


Observe, a seguir, um exemplo bem simples para definir uma máscara de formatação no controle que irá receber o valor do curso ( txtValor) e que irá formatar o valor numérico com separador de milhares e duas casas decimais. Utilizaremos a função Format() dentro do evento AfterUpdate da Caixa de combinação: Private Sub txtValor_AfterUpdate() txtValor = Format(txtValor, "#,##0.00") End Sub

O controle Botão de Comando (neste caso cmdInserir) será responsável por executar os códigos de procedimentos para cadastrar a nova informação na planilha. Faremos a consistência das informações e, com o auxílio da instrução For Each, verificaremos, na planilha Apoio, a existência do curso a ser cadastrado, não permitindo o cadastramento de dois ou mais cursos com o mesmo nome. Caso a informação digitada no controle txtCurso não exista na planilha, o cursor irá selecionar uma célula dentro do intervalo Cursos e inserir uma nova linha neste local, a qual receberá as informações digitadas nas caixas de texto. Após este processo, será feita a classificação por ordem alfabética de cursos. Em seguida, aplicamos um duplo-clique no controle cmdInserir e digitamos o seguinte procedimento: Private Sub cmdInserir_Click() Dim procura As Object 'Consistência de Dados If txtCurso = "" Then MsgBox "Digite o Curso" txtCurso.SetFocus Exit Sub End If If txtValor = "" Or Not IsNumeric(txtValor) Then MsgBox "Digite o valor" txtValor = "" txtValor.SetFocus Exit Sub End If

322

Objeto UserForm

7

Sheets("Apoio").Select 'verificar a existência do Curso For Each procura In Range("cursos") If UCase(procura.Value) = UCase(txtCurso) Then MsgBox txtCurso & vbCrLf & _ "Curso já existente! Digite outro" txtCurso = "" txtValor = "" txtCurso.SetFocus Exit Sub End If Next 'Posicionar na tabela e inserir uma linha Range("a65536").End(xlUp).Select ActiveCell.EntireRow.Insert 'Inserir informações ActiveCell = txtCurso ActiveCell.Offset(0, 1) = CDbl(txtValor) ActiveCell.Offset(0, 1).NumberFormat = "#,##0.00" 'Classificar a planilha pela coluna de cursos (Coluna A) Range("a2").Sort key1:=Range("a2"), _ order1:=xlAscending, Header:=xlYes End Sub

7.3.1. Exibindo um formulário Para fazer com que um formulário criado seja exibido ao usuário, devemos criar um procedimento com o método Show. Esse procedimento deve ser inserido em um módulo VBA, e não no código do formulário. O código de exemplo a seguir exibirá um formulário chamado FrmAlunos: Sub ShowDialog() frmAlunos.Show ' Demais instruções End Sub

323


É possível indicarmos a exibição de um formulário assim que o arquivo for aberto, através do método Auto_Open: Sub Auto_Open() frmAlunos.Show ' Demais instruções End Sub

Os procedimentos a seguir abordam a criação de códigos para a abertura de formulários, a atribuição desses códigos a botões de planilha e a criação de outros códigos envolvidos no trabalho com formulários: 1. Insira no módulo os códigos para abrir os formulários frmAlunos e frmCursos, exibidos a seguir: Sub Abrir_Alunos() frmAlunos.show End Sub Sub Abrir_Cursos() frmCursos.show End Sub

2. Insira uma nova planilha com o nome Abertura, incluindo botões que serão responsáveis pela abertura dos formulários, como ilustra a imagem a seguir:

324

Objeto UserForm

7

Para adicionar um botão diretamente na planilha, basta ir à guia Desenvolvedor e, no grupo Controles, clicar no botão Inserir. Então será exibida uma lista com diversos controles que podem ser adicionados à planilha. Ao adicionar cada botão, aparecerá uma janela, na qual vamos atribuir uma macro para eles, atribua para o primeiro botão a macro Abrir_Alunos e para o segundo a macro Abrir_Cursos:

Depois, renomeie os botões como "Alunos" e "Cursos" respectivamente, clicando com o botão direito no botão e na opção Editar Texto. 3. Insira mais uma linha de código para o botão cmdFechar do formulário frmAluno. Esse código, exibido a seguir, deve fazer com que um clique no botão, além de fechar o formulário, que era o que já fazia, também retorne para a planilha Abertura: Private Sub cmdFechar_Click() Unload Me Sheets("abertura").Select End Sub

4. Crie um código para que o evento Terminate do formulário frmCurso retorne para a planilha Abertura: Private Sub UserForm_Terminate() Sheets("abertura").Select End Sub

325


5. Dentro no módulo, insira um código para ocultar alguns objetos do Excel, insira a data do sistema operacional na barra de status e selecione a planilha Abertura: Sub auto_open() 'oculta as guias de planilha ActiveWindow.DisplayWorkbookTabs = False 'oculta cabeçalho de linha e coluna ActiveWindow.DisplayHeadings = False 'oculta as barras de rolagem vertical e horizontal ActiveWindow.DisplayHorizontalScrollBar = False ActiveWindow.DisplayVerticalScrollBar = False 'Exibe a data do sistema na barra de status Application.StatusBar = "Data: " & Date 'Seleciona a planilha Sheets("abertura").Select End Sub

Quando utilizamos o método Show, a execução da macro é pausada quando o formulário é exibido. Assim que o formulário é fechado, a macro retoma sua execução, executando quaisquer outros procedimentos que possua (na maioria das vezes não utilizamos outros procedimentos).

326

Objeto UserForm

7


•

•

•

•

Dentre os diversos modos de enviar informações para um computador, o uso de formulários é um dos principais métodos adotados pelos desenvolvedores de programas. Os formulários permitem interatividade com o usuário, são um meio seguro de colocar os dados em suas posições corretas e conferem consistência às informações; O Visual Basic utiliza o objeto UserForm, também conhecido como formulário, como interface entre os usuários e o computador; Inicialmente, todos os formulários criados são vazios, ou seja, não apresentam controles como botões, caixas de texto ou caixas de opções. Todos os controles devem ser inseridos por meio da Caixa de ferramentas; O objeto UserForm e cada um dos controles que podem ser inseridos em um formulário possuem propriedades diversas que, de acordo com o valor definido, fazem com que os controles apresentem uma aparência ou comportamento específico. Essas propriedades podem ser visualizadas e editadas por meio da janela de Propriedades; A Barra de ferramentas UserForm possui ferramentas úteis no trabalho de edição e organização dos controles. Para visualizá-la, devemos clicar no menu Exibir, depois em Barras de ferramentas e, em seguida, em UserForm;

327


•

•

328

Para que os controles do formulário executem ações, ou seja, tornemse funcionais, é necessário que sejam vinculados a eventos de objetos. Exemplos de eventos são clicar em um botão, ativar uma janela ou selecionar uma célula. Os procedimentos de eventos executam ações em resposta a um evento. Os eventos mais frequentemente utilizados são os eventos de planilha (Worksheet) e os eventos de pasta de trabalho (Workbook); Para fazer com que um formulário criado seja exibido ao usuário, devemos criar um procedimento com o método Show. Esse procedimento deve ser inserido em um módulo VBA, e não no código do formulário.

7

Objeto UserForm



1. Qual é o procedimento para criar um UserForm?

☐

a) Menu Inserir / Userform.

☐

b) Pressionar a tecla F5.

☐

c) Não é possível criar um UserForm.

☐

d) Aplicar um duplo-clique na janela de projeto.

☐


2. Qual das alternativas a seguir representa o local que contém os controles que serão inseridos no formulário?

330

☐

a) Menu Inserir / Arquivo.

☐

b) Caixa de Ferramentas

☐

c) UserForm

☐

d) Project Explorer

☐

e) As alternativas A e B estão corretas.

Objeto UserForm

7

3. Qual das alternativas a seguir é utilizada para exibir um texto dentro do formulário?

☐

a) Controle Combobox

☐

b) Userform

☐

c) Caixa de Ferramentas

☐

d) Não é possível exibir um texto dentro do formulário.

☐

e) Controle Label

4. Qual é o procedimento utilizado para alinhar controles dentro de um formulário?

☐

a) Menu Arquivo / Alinhamento.

☐

b) Selecionar todos os controles, clicar com o botão direito do mouse e acessar a opção desejada no submenu Alinhar.

☐

c) Selecionar apenas um controle e pressionar as teclas SHIFT + CTRL.

☐

d) Selecionar todos os controles e realizar o alinhamento arrastando o mouse para a direita.

☐


331


5. Para que um controle ComboBox liste informações contidas em uma região de células, qual propriedade deverá ser alterada?

332

☐

a) RowSource

☐

b) Value

☐

c) Select

☐

d) Name

☐

e) Column

7

Objeto UserForm Mãos à obra!

Excel 2016 VBA - Módulo I

Recommend Documents