14. EXCLUÍNDO DADOS ATRAVÉS DO FORMULÁRIO

14. EXCLUINDO DADOS ATRAVÉS DO FORMULÁRIO

            Já inserimos dados, alteramos os mesmos, fizemos a validação do número do CPF e outras coisas através do nosso formulário. Eis a pergunta. E se precisarmos excluir os dados que foram inseridos? Boa pergunta, muito boa mesmo.

            Não esquenta, vamos fazer isto num piscar de olhos. Então, prestenção!

            Primeiro insira um novo botão no formulário.

            

           

O código para excluir uma linha através do VBA é super simples. A grande sacada é saber qual é a linha que deve ser excluída. Para isto, vamos usar o índice da combobox para identificarmos na planilha qual a linha que vai ser excluída.

Vamos dizer que linha que queremos é Lin = PreencheListBox.ListBox1.ListIndex + 2. Com este código, localizaremos a linha exata que queremos excluir. Você pode estar se perguntando porque “+2”. Isto ocorre porque a listbox tem começa com o índice 0 e assim, precisamos contar as linhas à partir daí. Como temos uma linha de cabeçalho, então o índice da linha que procuramos será acrescido de 2.

Agora insira este código dentro do botão excluir que criamos.

Private Sub Cb_Excluir_Click()

   

Resp = MsgBox("Deseja excluir este registro?", vbYesNo + vbQuestion, "EXCLUSÃO DE REGISTRO")  

If Resp = vbNo Then Exit Sub

Lin = PreencheListBox.ListBox1.ListIndex + 2

 ActiveSheet.Rows(Lin).EntireRow.Delete

End Sub

Perceba que não fiz comentários dentro do código por ser muito simples e facilmente interpretado por você que acompanha o nosso curso. Para fazer o teste, selecione algum registro e clique em excluir.

Uma mensagem de confirmação será exibida, dando a opção de excluir ou não o registo. Facim demais sô!

Para acessar o curso completo Clique aqui 

13. VALIDANDO DADOS

13. VALIDANDO DADOS

            Este assunto é extremamente amplo, as necessidades são muitas e você pode usar a criatividade para validar praticamente qualquer coisa.

            No nosso caso, vamos aproveitar o formulário que estamos criando e incrementá-lo para fazer a validação do CPF. Insira este novo campo.

Já fizemos este código no item 10.3 do nosso curso,  agora vamos apenas adapta-lo ao nosso formulário. Very Easy!!!

Private Sub Cb_Salvar_Click()

    Success = CPF(Replace(Replace(Txt_CPF, ".", ""), "-", ""))  'uso Sucess porque a função precisa retornar algum valor

    'o Replace é usado é usado para tirar o ponto e o hífem

    If Success = "Invalido" Then: Exit Sub          'se não for um CPF válido, sai da rotina

Salva_Reg

    Limpa_Reg

End Sub

Observe que em vermelho, fizemos uma chamada para a função CPF, que está abaixo:

            Agora, veja a função. Observe que tá tudo explicadinho nos mínimos detalhas. Mas se houver alguma dúvida, você pode consultar o item 10.3.

Function CPF(NUMEROCPF As String) As String 'EXCELECIA.BLOGSPOT.COM

    'Declaramos as variáveis S1 e S2 para receberem a soma. A variável QD, ficará com o número de digitos do CPF. A variável I será o contador.

    'A variável D, receberá o DV calculado. As variáveis R1 e R2, receberão o resto das divisões. Finalmente, DV1 e DV2, receberão os dígitos calculados.

    Dim S1, S2, QD, I, D As Integer

    Dim R1, R2 As Integer

    Dim DV1, DV2 As Integer '

    SOMA1 = 0 'Inicializamos as somas como zero.

    SOMA2 = 0

    QD = 11 'A quantidade de digitos de um CPF é 11.

    'Criamos um LOOP que será executado 11 vezes

    For I = 1 To 11 Step 1

        'D absorverá cada digito do CPF. A cada giro do loop, ele será um número diferente, sendo um de cada vez

        D = Val(Mid$(NUMEROCPF, I, 1))

        'Para calcularmos o 1º DV, precisamos vazer o calculo como explicado no capítulo. Repare que as duas somas são realizadas de uma só vez.

        If I <= 9 Then

            S1 = S1 + D * (QD - 1)

        End If

        If I <= 10 Then

            S2 = S2 + D * QD

        End If

        'Precisamos decrementar esta variável para que os números possam decrescer.

        QD = QD - 1

    Next

    'Agora é só descobrir o resto da divisão para usarmos a regra e encontrar os DVs.

    R1 = S1 Mod 11

    If (R1 <= 1) Then

        DV1 = 0

    Else

        DV1 = 11 - R1

    End If

        R2 = S2 Mod 11

    If (R2 <= 1) Then

        DV2 = 0

    Else

        DV2 = 11 - R2

    End If

    'Se tanto o DV1, com o DV2 forem diferentes do digito 10 e 11 respectivamente do CPF analisado,o mesmo será invalido, do contrário, válido! Pronto,está feito.

    If (DV1 <> Val(Mid$(NUMEROCPF, 10, 1))) Or (DV2 <> Val(Mid$(NUMEROCPF, 11, 1))) Then

        CPF = "Invalido"

    Else

        CPF = "Valido"

    End If

   

    If CPF = "Invalido" Then

        MsgBox "Este não é um CPF válido", vbOKOnly + vbCritical, "CPF INVÁLIDO"

        'Txt_CPF = ""

        Txt_CPF.SetFocus

       

    End If

End Function

Após o usuário digitar o número do CPF e clicar em salvar, a função verificará se trata-se de um CPF válido. Caso positivo, a informação será salva, do contrário, uma mensagem será exibida, exigindo a digitação de um número válido. Fácil demais.

Até o próximo capítulo.

Para acessar o curso completo Clique aqui

12.2. Salvando e alterando dados através do formulário

12.2. Salvando e alterando dados através do formulário

            No capítulo 8, vimos como inserir dados em uma planilha através do formulário de forma bem simples. Porém, naquela oportunidade, vimos apenas como inserir registros linha a linha, sem a possibilidade de alterá-los.

            Pensa direitinho my friend. E precisarmos alterar as informações que já cadastramos?

            Vamos aprender então, além de salvar, alterar os dados que já foram inseridos na planilha.

            Para facilitar a compreensão, daremos sequência ao formulário que estamos construindo, acrescentando um botão, que chamaremos Cb_Salvar.

 

            Criaremos as rotinas chamadas Salva_Reg e Limpa_Reg respectivamente. Esta segunda, será para limpar os controles e permitir uma nova digitação.

 

Function Salva_Reg()

    Dim ShtFunc                                     'declara a variável da planilha

      

    Set ShtFunc = Sheets("Plan4")                   'seta a planilha que será utilizada

    'captura a última linha usada da plna e incrementa mais uma para colar o registro na imediatamente vazia

    If ComboBox1.ListIndex = -1 Then                'se não houver item selecionado

        UltLin = ShtFunc.Cells(Rows.Count, 1).End(xlUp).Row + 1 'defina a linha da planilha onde o registro será inserido

    Else                                            'se já houver um item selecionado, precisamos apenas atualizar o registro

        UltLin = ComboBox1.ListIndex + 2

    End If

    With ShtFunc                                    'insere dos dados na planilhas

        ShtFunc.Cells(UltLin, 1) = Txt_Nome

        ShtFunc.Cells(UltLin, 2) = Txt_Sal

        ShtFunc.Cells(UltLin, 3) = Txt_Cargo

    End With
 

End Function

Após inserirmos os dados, vamos automatizar uma rotina para limpar os campos para que possamos inserir novos dados. Veja a rotina abaixo(Moleza!).

Function Limpa_Reg()

    With PreencheListBox                            'considerando os controles do nosso formulário

        Txt_Nome = ""                               'vamos limpa-los

        Txt_Sal = ""

        Txt_Cargo = ""

        Txt_Nome.SetFocus                           'e trazer o foco para o 1º campo a ser digitado

    End With

End Function

            Finalmente, vamos chamar as rotinas através do botão Salvar

Private Sub Cb_Salvar_Click()

    Salva_Reg

    Limpa_Reg

End Sub

 

            É só fazer os testes, perceba que se você selecionar algum nome na ListBox ou ComboBox, ele será alterado quando o botão Salvar for clicado, do contrário, será inserido um novo registro.

Para acessar o curso completo Clique aqui

ATUALIZAÇÃO DA PLANILHA DE EXEMPLOS DO CURSO DE PROGRAMAÇÃO VBA

Amigos, boa noite a todos!

Estou disponibilizando uma Atualização da planilha de exemplos do curso de programação. Clique aqui!

Não deixe de acompanhar as novas atualizações do Blog. Teremos muitas novidades!!!

Para acessar o curso completo Clique aqui

12. ATUALIZANDO DADOS ATRAVÉS DE UM FORMULÁRIO.

12. ATUALIZANDO DADOS ATRAVÉS DE UM FORMULÁRIO.

Convenhamos, buscar informações em uma planilha do Excel pode ser uma verdadeira Odisseia se houverem muitos registros. Então meu caro, que tal usarmos as belezocas da ListBox e Combobox? Assim é só buscar o nome e trazer as demais informações no formulário.

12.1. Retornando os dados no formulário

            A melhor forma de exemplificar o que estou querendo dizer é criarmos um pequeno sistema de cadastro. Para começarmos, vamos utilizar o formulário do capítulo anterior e inserirmos mais alguns controles (3 Comboboxs, sendo “Txt_Nome”, “Txt_Sal”, “Txt_Cargo”. Então mãos à obra.

            Após inserido os controles, o formulário ficará desta forma.

            Agora vamos programar o formulário para retornar os dados para os controles, quando forem selecionados dados na Combobox ou Listbox.

            Vamos criar um procedimento simples para retornar os dados, veja abaixo.

Sub RetornaDados()

    Dim ShtFunc                                     'declara a variável da planilha

   

    Set ShtFunc = Sheets("Plan4")                   'seta a planilha que será utilizada

   

    Lin = PreencheListBox.ListBox1.ListIndex + 2    'defina a linha da planilha onde os dados serão capturados

    With ShtFunc                                    'retorna os dados para o formulário

        Txt_Nome = ShtFunc.Cells(Lin, 1)

        Txt_Sal = ShtFunc.Cells(Lin, 2)

        Txt_Cargo = ShtFunc.Cells(Lin, 3)

    End With

End Sub

            É só inserir a chamada do procedimento no evento Change da Combobox. Não será necessário fazer o mesmo com a Listbox, já que igualamos o índice das duas no tópico anterior.

            Veja o resultado final.

           

Para acessar o curso completo Clique aqui

11.4. Interação entre ComboBox e ListBox

11.4. Interação entre ComboBox e ListBox

            Trabalhar somente com uma Combobox ou Listbox em um formulário pode ser algo não muito prático e dinâmico. Que tal inserirmos as duas e criarmos uma interação dinâmica entre elas para facilitar nossas buscas?

            Para tanto, vamos usar o formulário da lição anterior e incrementá-lo, adicionando uma Combobox e alterando o código desta forma.

Private Sub UserForm_Initialize()

Dim Planilha As Worksheet

Dim Linha As Integer

'Você pode usar qualquer planilha. Neste caso, vou usar uma planilha que já usei neste curso.

'O resto é semelhante ao do item 11.1

Set Planilha = Worksheets("plan4")

Linha = 2

With Planilha

    Do While .Cells(Linha, 1).Value > ""

    '*********************************************

    Me.ComboBox1.AddItem .Cells(Linha, 1).Value         '*

    '*********************************************

    Me.ListBox1.AddItem .Cells(Linha, 1).Value

    Linha = Linha + 1

    Loop

   

End With

End Sub

            Perceba que acrescentamos apenas a linha de código em vermelho para preenchermos a Combobox, simultaneamente à ListBox. Muito rápido e fácil.

            Agora o formulário vai ficar com esta cara.

            Agora, no editor do VBA, dê 2 clicks na Combobox para acessar o evento Change da mesma. Vamos igualar o índice da ListBox com o da Combobox. Veja.

            O mesmo vamos fazer com ListBox, alterando o seu evento Change.

            Pronto, agora se você buscar algum nome na Combobox, o mesmo será selecionado na ListBox e vice versa. Faça o teste.

Para acessar o curso completo Clique aqui

11.3 Preenchendo Uma ListBox com dados de uma planilha

11.3 Preenchendo Uma ListBox com dados de uma planilha

             
            Assim como as Comboboxs, as ListBoxs também podem ser preenchidas com as informações que nós interessam. Vamos ver como é isto.

          Crie um formulário como este:

            Agora, digite o código dentro do formulário, como mostra a figura abaixo.

               Prontinho, agora é só executar o código, que a listbox será preenchido, com os dados da plan4.

Para acessar o curso completo Clique aqui

11.2. Utilizando a COMBOBOX para manipular planilhas

11.2. Utilizando a COMBOBOX para manipular planilhas

Vamos usar uma COMBOBOX para navegarmos de uma planilha à outra. Crie um formulário igualzinho ao que criamos no item anterior. Desta vez, vamos trabalhar com o evento INITIALIZE do formulário e com o CLICK da COMBOBOX.

Faça o teste. Quando você escolher uma certa planilha na COMBOBOX, ela será ativada na pasta de trabalho.

Para acessar o curso completo Clique aqui

11. TRABALHANDO COM OBJETOS

11. TRABALHANDO COM OBJETOS

Vamos começar aprender a trabalhar com os objetos do Excel e usar os conhecimentos que adquirimos até o momento para manipulá-los.

Estes conhecimentos são muito importantes, pois o tempo todo trabalharemos com as COMBOBOX, LISTBOX, CHECBOX, OPTIONBUTTON, COMMANDBUTTON entre outros.

11.1. Criando um LOOP para preencher uma COMBOBOX

As COMBOBOXS são muito utilizadas nos sistemas criados em programação VBA. Precisamos aprender a utilizar bem esta ferramenta, se quisermos criar bons programas.

Muitas vezes, precisamos preencher uma COMBOBOX com dados que estão em uma planilha. Veja o exemplo abaixo.

Crie um formulário com esta carinha.

Dê dois clicks nele, perceba que o evento padrão do formulário será o click, porém, vamos construir nosso algoritmo dentro do INITIALIZE, que é o evento que é ativado assim que o formulário é aberto.

Agora click em F5. Veja que a COMBOBOX foi preenchida com os dados que estão na planilha.


Para acessar o curso completo Clique aqui

10.3. Verificando a autenticidade do CPF

10.3. Verificando a autenticidade do CPF

Vamos criar uma função capaz de verificar se um dado número de CPF é valido ou não. Para tanto, precisamos entender a lógica do cálculo. É importante saber, que o raciocínio de calculo do DV(Digito Verificador) do CPF é de domínio público e todos os programadores, de qualquer linguagem o utilizam para validar CPF em seus sistemas.

Entenda a lógica da coisa! Imagine que lhe foi dado número do CPF, 590.465.719-90. Como saber se este número é valido ou não?

Para se calcular o DV, precisamos considerar que o cálculo é feito inicialmente, com os 9 primeiros números do CPF. Para facilitar, veja a tabela abaixo.

Colocamos os números do CPF e identificamos suas posições. Vamos fazer os primeiros cálculos.

Iremos multiplicar os valores de cada dígito do CPF, da esquerda para direita, começando com 10 e progredindo de forma decrescente. Finalmente, achamos o resultado que usaremos para verificar a validade do CPF.

Somamos os valores encontrados e teremos 269.

O CPF é composto por 11 números(9 + 2(DV)). Então, vamos pegar o valor que encontramos e dividir por 11, sendo 269/11 = 24,45, resto 5.

O raciocínio do cálculo é simples. Seguiremos a seguinte regra: se o resto for 0 ou 1, então o DV será igual a 0. Caso contrário, o número do DV será 11 menos o resto encontrado.

No nosso caso, o primeiro número do DV será 11 – 5 = 6.

Vamos agora, encontrar o segundo DV. Para isto, vamos reformular a tabela anterior, acrescentando o número que encontramos.

Perceba, já que encontramos o primeiro dígito, que multiplicamos o 1º número por 11 progredindo decrescentemente como na tabela anterior. Veja que na 10ª posição, colocamos o número que encontramos para o primeiro DV.

Fazendo a soma, teremos 327/11 = 29,72, resto 8. Da mesma forma, 11-8 = 3.

Pronto, o DV deste CPF deveria ser 63 e não 90. O correto é 590.465.719-63

Veja como ficou o código:

Vamos fazer os testes. Digite a fórmula do CPF na célula B1 como mostra a figura. Na célula A1, digite o CPF incorreto, que a função retornará INVALIDO. Nas células de baixo digite o número correto e veja o resultado.

Depois de ver o funcionamento básico das funções, vamos aprender uma pouco mais sobre os objetos do Excel.

Para acessar o curso completo Clique aqui

10.2. Criando uma função para descobrir o valor que originou o ICMS

10.2. Criando uma função para descobrir o valor que originou o ICMS

Algumas pessoas me perguntaram como descobrir o valor principal que originou o ICMS nas notas fiscais. Simples, porém, não se engane. Se multiplicarmos o valor total por 0,18 encontraremos apenas 18% do mesmo. Então, o que fazer? Por exemplo! Se o valor da venda for R$ 300,00 sem ICMS, para acrescentarmos o mesmo, precisamos fazer 300 x 1,18, sendo o valor total de R$ 354,00. Então, se quisermos saber o valor original, é só fazer o processo inverso, ou seja, 354/1,18, retornando o valor de R$ 300,00.

Moral da história! Uma nota com valor final de R$ 354,00, tem o valor do ICMS de R$ 54,00, que é 18% de R$ 300,00 e não R$ 63,72, que é 18% de R$ 354,00. Sabendo isto, você não corre o risco de pagar ou fazer retenções indevidas.

Vamos criar uma função que faça este cálculo automaticamente. Você pode usar a mesma planilha do exemplo anterior, é só mudar o título da célula D1 para VALOR ORIGINAL. Lembre-se, usaremos os mesmos números, porém, estamos falando de uma coisa completamente diferente, estaremos supondo que os valores da coluna 2 já estão cheios e calcularemos o valor inicial na coluna 4. Então vamos lá.

Veja o código. Muito simples! Não há muito que dizer.

Agora é só fazer o teste como no exemplo anterior.


Para acessar o curso completo Clique aqui

9.7. Excluindo dados da planilha

Pessoal! Cometi um erro no decorrer do nosso curso. Havia prometido ensinar o LOOP para excluir linhas, porém, não postei nada.

Para quem esperou por este capítulo, segue fresquinho!

9.7. Excluindo dados da planilha

Algo muito importante que devemos aprender a fazer é excluir automaticamente as linhas da planilha quando for necessário.

Faça uma planilha como esta. Use números que quiser.

Suponha que você deseja criar um LOOP que percorra a planilha excluindo as linhas cujo número seja menor que 10.

Antes de continuarmos, click com o botão direito na barra superior do Excel e ative a CAIXA DE FERRAMENTAS DE CONTROLE.

Feito isto, vamos inserir um botão na planilha.

Dê dois clicks no botão que acabamos de criar para acessar o seu evento click e digite o código abaixo

Vamos voltar à planilha. Na CAIXA DE FERRAMENTAS DE CONTROLE, click no botão MODO DE DESIGN para sairmos do ambiente de desenvolvimento e podermos acionar a ação do botão quando for clicado.

Agora é só clicar no botão. Verá que todas as linhas que possuírem números menores que 10 serão excluídas.

É isso pessoal! Fizemos muitos exemplos utilizando os diversos tipos de LOOPS. Acredito que você conseguiu compreender a utilização dos códigos e sua aplicação nas mais diversas situações do dia-a-dia. Vamos agora, aprender um pouco sobre a construção de funções no Excel, com o uso do VBA. Até logo!!!

Para acessar o curso completo Clique aqui