Microsoft Excel VBA

Prévia do material em texto

Microsoft Excel 
Visual Basic para Aplicativos 
 
PROJETO: Sistema de Cadastro 
 
Por Fernando de Carvalho Navarro 
Agosto/2013 
 
 
Microsoft Excel – Visual Basic para Aplicativos 
Projeto Sistema de Cadastro 3 
Projeto Sistema de Cadastro 
Este tutorial tem a finalidade de permitir que você possa criar um projeto em VBA para cadastra-
mento de alunos em cursos de informática. 
Para isto use o arquivo Sistema de Cadastro.xlsm, disponível em sua pasta de arquivos de treina-
mento. Nessa mesma pasta há diversos arquivos de imagem que também serão usados. 
Siga os passos para completar cada atividade do projeto. 
Parte 1: Formulário de Cadastro de Aluno 
1. Vá ao ambiente do VBA e insira um novo UserForm. 
2. Defina, inicialmente, as seguintes propriedades: 
Name: frmCadastroAluno 
Caption: Formulário de Cadastro de Aluno 
Font: Verdana, 10pt 
Height: 300 
Width: 400 
Definir a propriedade Font no formulário fará com que todos os próximos controles já possu-
am essas características de texto. 
3. Desenhe dois botões de comando: Cadastrar e Cancelar. Organize-os na parte inferior do 
formulário (use o menu Formatar  Organizar botões  Inferior). 
 
4. Em seguida defina suas propriedades: 
Name: cmdCadastrar Name: cmdCancelar 
Caption: Cadastrar Caption: Cancelar 
Default: True Cancel: True 
Height: 24 Height: 24 
Width: 72 Width: 75 
 Microsoft Excel – Visual Basic para Aplicativos 
4 Projeto Sistema de Cadastro 
5. Codifique os botões Cadastrar e Cancelar (dê um duplo clique no objeto para codificá-lo): 
Option Explicit 
 
Private Sub cmdCadastrar_Click() 
 Unload Me 
End Sub 
 
 
Private Sub cmdCancelar_Click() 
 End 
End Sub 
6. Teste o formulário pressionando F5 . Feche o formulário pelo botão [x]. 
7. Desenhe 4 controles de Rótulo e 4 controles de Caixa de Texto e defina as propriedades: 
 
Name: lblNome Name: lblTelefone 
Caption: Nome: Caption: Telefone: 
Accelerator: N Accelerator: f 
BackStyle: 0 - fmBackStyleTransparent BackStyle: 0 - fmBackStyleTransparent 
Height: 18 Height: 18 
Width: 54 Width: 54 
TextAlign: 3 - fmTextAlignRight TextAlign: 3 - fmTextAlignRight 
 
Name: lblMatrícula Name: lblIdade 
Caption: Matrícula: Caption: Idade: 
Accelerator: Accelerator: I 
BackStyle: 0 - fmBackStyleTransparent BackStyle: 0 - fmBackStyleTransparent 
Height: 18 Height: 18 
Width: 54 Width: 54 
TextAlign: 3 - fmTextAlignRight TextAlign: 3 - fmTextAlignRight 
 
Name: txtNome Name: txtTelefone 
AutoTab: True AutoTab: True 
TabStop: True TabStop: True 
MaxLength: 40 MaxLength: 20 
SelectionMargin: False SelectionMargin: False 
Height: 18 Height: 18 
Width: 190 Width: 96 
 
Name: txtMatrícula Name: txtIdade 
AutoTab: False AutoTab: True 
TabStop: False TabStop: True 
MaxLength: 0 MaxLength: 2 
SelectionMargin: False SelectionMargin: False 
Height: 18 Height: 18 
Width: 48 Width: 48 
Enabled: False 
Microsoft Excel – Visual Basic para Aplicativos 
Projeto Sistema de Cadastro 5 
8. Desenhe duas Caixas de Seleção e defina as propriedades: 
 
Name: chkPNE Name: chkBolsa 
Caption: Portador de Necessidades Especiais Caption: Bolsa de Estudos 
Accelerator: P Accelerator: B 
BackStyle: 0 - fmBackStyleTransparent BackStyle: 0 - fmBackStyleTransparent 
Height: 18 Height: 18 
Width: 200 Width: 104 
9. Desenhe um controle de Moldura e defina as propriedades: 
 
Name: fraCurso 
Caption: Curso 
Height: 120 
Width: 366 
 
 Microsoft Excel – Visual Basic para Aplicativos 
6 Projeto Sistema de Cadastro 
10. Dentro da moldura desenhe 4 Botões de Opção e defina as propriedades: 
Name: optManhã Name: optTarde 
Accelerator: M Accelerator: T 
Caption: Manhã Caption: Tarde 
Height: 18 Height: 18 
Width: 60 Width: 60 
 
Name: optNoite Name: optSábado 
Accelerator: O Accelerator: S 
Caption: Noite Caption: Sábado 
Height: 18 Height: 18 
Width: 60 Width: 60 
 
11. Certifique-se de desenhar os 4 botões inteiramente dentro da moldura para que façam par-
te do mesmo grupo. Use as ferramentas de tamanho e alinhamento caso necessário. 
12. Desenhe dentro da moldura uma Caixa de Listagem e um controle de Imagem, com as se-
guintes propriedades: 
Name: lstCursos Name: imgCurso 
Font: Verdana, 8 pt BackStyle: 0 - fmBackStyleTransparent 
BoundColumn: 1 BorderStyle: 0 - fmBorderStyleNone 
ColumnCount: 2 PictureAlignment: 2 - fmPictureAlignmentCenter 
ColumnHeads: True PictureSizemode: 1 - fmPictureSizeModeStretch 
ColumnWidths: 124pt Height: 60 
RowSource: BD_Cursos Width: 60 
Height: 90,75 
Width: 240,75 
 
Microsoft Excel – Visual Basic para Aplicativos 
Projeto Sistema de Cadastro 7 
13. Defina a Ordem de Tabulação numa sequência lógica (clique no formulário com o botão di-
reito do mouse e escolha Ordem de Tabulação): 
 
14. Teste novamente o formulário. Use a tecla  Tab para navegar entre os campos e teste 
também o tamanho máximo de caracteres em cada caixa de texto. Clique Cancelar para fe-
char o formulário. 
15. Insira um novo módulo e crie a macro que irá exibir o formulário: 
Option Explicit 
 
Sub Exibir_Form_Cadastro_Aluno() 
 frmCadastroAluno.Show 
End Sub 
16. Na planilha Início, atribua a macro ao botão Cadastrar Aluno: 
 
17. De volta ao ambiente do VBA, você criará agora uma lógica para o funcionamento das caixas 
de seleção Portador de Necessidades Especiais e Bolsa de Estudos: 
a. Quando a caixa Portador de Necessidades Especiais estiver selecionada, a caixa 
Bolsa de Estudos deverá ser selecionada e depois desabilitada. 
b. Quando Portador de Necessidades Especiais for desligada, ela deverá habilitar no-
vamente a caixa Bolsa de Estudos e deixá-la desmarcada. Dê um duplo clique no 
controle chkPNE para abrir o módulo para codificá-lo: 
 Microsoft Excel – Visual Basic para Aplicativos 
8 Projeto Sistema de Cadastro 
Private Sub chkPNE_Click() 
 If chkPNE Then 
 chkBolsa.Value = True 
 chkBolsa.Enabled = False 
 Else 
 chkBolsa.Value = False 
 chkBolsa.Enabled = True 
 End If 
End Sub 
18. Quando o formulário iniciar, o nº de matrícula para o novo aluno que está sendo cadastrado 
deverá aparecer dentro da caixa de texto txtMatrícula (definida originalmente com a pro-
priedade Enabled = False). Dê um duplo clique numa área vazia do formulário para codificar 
o UserForm. Inicialmente o procedimento de evento UserForm_Click() aparecerá na janela 
do código, porém mude o evento na lista de eventos (parte superior direita da janela de 
módulo) para Initialize e escreva o código correspondente: 
Private Sub UserForm_Initialize() 
 txtMatrícula = WorksheetFunction.Max(Range("Alunos!A:A")) + 1 
End Sub 
A linha de código preenche a caixa de texto da matrícula com o próximo número livre, defini-
do como sendo o máximo da coluna A da planilha Alunos, mais um. 
19. Exclua o procedimento UserForm_Click() que não chegou a ser usado. 
20. Como um “toque extra”, faça com que o Excel fique oculto quando o formulário for iniciali-
zado e apareça novamente quando o usuário clicar no botão Cancelar . No código anterior 
acrescente uma linha que altera a propriedade Visible do objeto Application: 
Private Sub UserForm_Initialize() 
 txtMatrícula = WorksheetFunction.Max(Range("Alunos!A:A")) + 1 
 Application.Visible = False 
End Sub 
21. Para que o formulário seja exibido novamente ao término do cadastramento, altere o códi-
go do botão Cancelar : 
Private Sub cmdCancelar_Click() 
 Application.Visible = True 
 End 
End Sub 
22. Volte à planilha Início. Teste o carregamento do formulário para verificar o nº de matrícula 
na caixa de texto correspondente e se o Excel ficará oculto. O efeito será visualizado melhor 
se você minimizar todas as janelas, menos a do Excel. 
OBS: No Windows 7 e Windows 8, quando você segura a janela do aplicativo pela barra de tí-
tulo e “chacoalha” o ponteiro do mouse, todas as demais ficam minimizadas, menos a que 
foi sacudida.Para que retornem ao normal, chacoalhe novamente a janela que está sendo 
visualizada. 
Microsoft Excel – Visual Basic para Aplicativos 
Projeto Sistema de Cadastro 9 
23. O próximo controle a ser codificado será a caixa de listagem lstCursos: 
Private Sub lstCursos_Click() 
 Dim Arq_Imagem As String 
 ' Procura o curso selecionado no intervalo BD_Cursos e traz o valor da 3ª coluna 
 Arq_Imagem = WorksheetFunction.VLookup(lstCursos.Text, Range("BD_Cursos"), 3, 0) 
 ' Concatena o caminho completo desta pasta de trabalho ao nome do arquivo 
 Arq_Imagem = ThisWorkbook.Path & "\" & Arq_Imagem 
 ' Carrega a imagem definida pelo nome do arquivo à propriedade Picture 
 imgCurso.Picture = LoadPicture(Arq_Imagem) 
End Sub 
24. Finalmente o cadastramento poderá ser feito. Codifique o botão Cadastrar . 
Private Sub cmdCadastrar_Click() 
 ' Seleciona a planilha "Alunos" (banco de dados) e acha a 1ª célula livre, abaixo 
 Sheets("Alunos").Select 
 Range("A4").End(xlDown).Offset(1, 0).Select 
 
 ' Preenche as células do novo registro com as informações do formulário 
 ActiveCell(1, 1) = CLng(txtMatrícula) 
 ActiveCell(1, 2) = txtNome 
 ActiveCell(1, 3) = txtTelefone 
 ActiveCell(1, 4) = CLng(txtIdade) 
 ActiveCell(1, 5) = chkPNE 
 ActiveCell(1, 6) = chkBolsa 
 ActiveCell(1, 7) = lstCursos 
 
 ' Verifica qual o período escolhido para armazenar na 8ª coluna da base de dados 
 If optManhã Then 
 ActiveCell(1, 8) = "Manhã" 
 ElseIf optTarde Then 
 ActiveCell(1, 8) = "Tarde" 
 ElseIf optNoite Then 
 ActiveCell(1, 8) = "Noite" 
 ElseIf optSábado Then 
 ActiveCell(1, 8) = "Sábado" 
 Else 
 ActiveCell(1, 8) = "Não Especificado" 
 End If 
 
 ' Finaliza: seleciona a célula A1 e volta à planilha "início" 
 Range("A1").Select 
 Sheets("Início").Select 
 Application.Visible = True 
 Unload Me 
End Sub 
 Microsoft Excel – Visual Basic para Aplicativos 
10 Projeto Sistema de Cadastro 
Parte 2: Formulário de Cadastro de Curso 
25. Insira um novo UserForm, desenhe os controles e modifique as propriedades conforme 
mostrado a seguir: 
 
Name: frmInclusãoCurso 
Caption: Formulário de Inclusão de Curso 
Font: Verdana, 10pt 
Height: 180 
Width: 340 
 
Name: lblNome Name: txtNome 
Caption: Nome do Curso: AutoTab: False 
Accelerator: N TabStop: True 
BackStyle: 0 - fmBackStyleTransparent MaxLength: 30 
Height: 18 SelectionMargin: False 
Width: 96 Height: 18 
TextAlign: 3 - fmTextAlignRight Width: 210 
 
Name: lblPreço Name: txtPreço 
Caption: Preço do Curso: AutoTab: False 
Accelerator: P TabStop: True 
BackStyle: 0 - fmBackStyleTransparent MaxLength: 10 
Height: 18 SelectionMargin: False 
Width: 96 Height: 18 
TextAlign: 3 - fmTextAlignRight Width: 72 
 
Name: lblImagem Name: txtImagem 
Caption: Imagem do Curso: AutoTab: False 
Accelerator: I TabStop: True 
BackStyle: 0 - fmBackStyleTransparent MaxLength: 0 
Height: 18 SelectionMargin: False 
Width: 96 Height: 18 
TextAlign: 3 - fmTextAlignRight Width: 180 
 
 Name: cmdProcurar 
 Caption: … 
 Accelerator: . 
 Font: Consolas, 8 pt 
 Height: 18 
 Width: 24 
 
Microsoft Excel – Visual Basic para Aplicativos 
Projeto Sistema de Cadastro 11 
Name: cmdIncluir Name: cmdFechar 
Caption: Incluir Caption: Fechar 
Default: True Cancel: True 
Height: 24 Height: 24 
Width: 72 Width: 72 
26. Verifique a Ordem de Tabulação: 
 
27. Codifique o botão Fechar : 
Option Explicit 
 
Private Sub cmdFechar_Click() 
 Sheets("Início").Select 
 End 
End Sub 
28. Teste o formulário pressionando F5 . Clique Fechar . 
29. Codifique o botão Incluir : 
Private Sub cmdIncluir_Click() 
 Sheets("Cursos").Select 
 Range("A4").End(xlDown).Offset(1, 0).Select 
 ActiveCell = txtNome 
 ActiveCell.Offset(0, 1) = CSng(txtPreço) 
 ActiveCell.Offset(0, 2) = txtImagem 
End Sub 
30. Na folha normal de Módulo existente, crie uma função que seja capaz de extrair o nome de 
um arquivo do final do texto que contém o seu caminho completo: 
' Esta função extrai o nome do arquivo do final do caminho completo 
Function ExtrairArquivo(Caminho As String) As String 
 ExtrairArquivo = Right(Caminho, Len(Caminho) - InStrRev(Caminho, "\")) 
End Function 
Esta função criada será usada quando o usuário escolher o arquivo de imagem por uma cai-
xa de diálogo. No final da escolha um texto será retornado com o caminho completo do ar-
quivo, incluindo disco, pastas e arquivo. A função deverá extrair tudo o que vier após a últi-
ma ocorrência do caractere “\”. 
 Microsoft Excel – Visual Basic para Aplicativos 
12 Projeto Sistema de Cadastro 
31. No mesmo módulo crie o procedimento que exibirá o formulário de inclusão de curso: 
Sub Incluir_Curso() 
 frmInclusãoCurso.Show 
End Sub 
32. Na planilha Início, atribua o procedimento anterior ao botão Incluir Curso. 
33. Teste o formulário clicando no botão Incluir Curso. Clique Fechar . 
34. Codifique o botão ... (Procurar): 
Private Sub cmdProcurar_Click() 
 Dim Filtro As String 
 ' Define o disco e pasta padrão para acesso aos arquivos 
 ChDrive Left(ThisWorkbook.Path, 1) 
 ChDir ThisWorkbook.Path 
 ' Monta o filtro de arquivos e solicita a escolha do arquivo de imagem 
 Filtro = "Arquivos JPEG (*.jpg), (*.jpg), Arquivos Bitmap (*.bmp), (*.bmp)" 
 txtImagem = ExtrairArquivo(Application.GetOpenFilename(FileFilter:=Filtro, _ 
 FilterIndex:=1, Title:="Importar imagem", MultiSelect:=False)) 
End Sub 
35. Teste novamente o formulário, agora selecionando uma das imagens para o curso que está 
sendo cadastrado. Insira um ou dois cursos novos. 
Parte 3: Formulário de Exclusão de curso 
36. Insira outro UserForm, desenhe os controles e modifique as propriedades conforme mos-
trado a seguir: 
 
Name: frmExclusãoCurso Name: lstCursos 
Caption: Exclusão de Curso Font: Verdana, 8 pt 
Font: Verdana, 10pt BoundColumn: 1 
Height: 180 ColumnCount: 2 
Width: 270 ColumnHeads: True 
 ColumnWidths: 124pt 
 RowSource: BD_Cursos 
 Height: 90,75 
 Width: 240,75 
 
Microsoft Excel – Visual Basic para Aplicativos 
Projeto Sistema de Cadastro 13 
Name: cmdExcluir Name: cmdCancelar 
Caption: Excluir Caption: Cancelar 
Default: True Cancel: True 
Height: 24 Height: 24 
Width: 72 Width: 72 
37. Codifique o botão Cancelar : 
Option Explicit 
 
Private Sub cmdCancelar_Click() 
 Sheets("Início").Select 
 End 
End Sub 
38. Codifique o botão Excluir : 
Private Sub cmdExcluir_Click() 
 Sheets("Cursos").Select 
 ' Remove da base o item cujo nº corresponde ao curso clicado 
 Range("A4").Offset(lstCursos.ListIndex + 1, 0).EntireRow.Delete 
 ' Lê novamente (atualiza) a lista do intervalo "BD_Cursos" 
 lstCursos.RowSource = "BD_Cursos" 
End Sub 
39. No módulo principal, crie o procedimento que fará a chamada ao formulário de Exclusão: 
Sub Exibir_Form_Exclusão_Curso() 
 frmExclusãoCurso.Show 
End Sub 
40. Teste o formulário para excluir um ou dois cursos. 
 
O projeto principal está terminado. Com o tempo e experiência, procure aplicar as seguintes me-
lhorias: 
 No formulário de cadastramento de aluno, quando o usuário clicar no botão Cadastrar , o 
procedimento já existente deverá validar os dados digitados e todos os campos deverão es-
tar preenchidos, a menos das duas caixas de seleção, que são opcionais. 
 Crie um novo formulário frmExclusãoAluno que exiba uma caixa de listagem que mostra 
duas colunas do banco de dados: Matrícula e Nome. O usuário deverá escolher um aluno 
para que seja excluído da base de dados. 
 Nos formulários de exclusão, colocar uma rotina de tratamento de erro quando o usuário 
clicar no botão Excluir sem ter selecionado nenhum item. 
 Crie um novo formulário frmMenu que exibirá botões correspondentes às opções que atu-
almente estão na planilha, mais a opção de exclusão de aluno. Cada botão acionará a macro 
correspondente. O carregamentodeste formulário deverá ser feito automaticamente quan-
do a pasta de trabalho for aberta. O procedimento também ocultará o Excel. Crie também o 
botão Sair que emitirá uma caixa de mensagem perguntando se o usuário realmente dese-
ja sair do sistema. Na confirmação, a pasta de trabalho será fechada, o arquivo salvo e o Ex-
cel visível novamente.