Blog

Postado em em 30 de abril de 2021

Preenchimento das Taxas com VBA 2 de 3 – Bolsa de Valores

Preenchimento das Taxas com VBA 2 de 3

Nesta aula vamos continuar o preenchimento das taxas com VBA 2 de 3. Essa é a segunda aula dessa parte de taxas para organizá-las!

Caso prefira esse conteúdo no formato de vídeo-aula, assista ao vídeo abaixo!

Para receber a planilha que usamos na aula no seu e-mail, preencha:

Essa é a vigésima segunda aula da nossa Série Controles para Bolsa de Valores!

Nela dar prosseguimento a série, e como fazer o preenchimento das taxas de forma automática que começamos na aula anterior. Essa aula vai ser a segunda parte, de três aulas, para fazer esse código.

Nesse post você vai aprender a lidar com:

  • Array no VBA
  • WorksheetFunction
  • SumIf e SumIfs
  • CountIf e CountIfs
  • IF VBA
  • Estrutura de repetição For

Para fazer alterações na nossa planilha já criada e criar nossos códigos propriamente ditos, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11.

Assim que o ambiente do VBA se abrir, iremos clicar em AbaNotas, nos módulos, como mostrado no vídeo.  Daremos seguimento ao preenchimento das taxas para que na próxima aula possamos finalizar essa parte das taxas. Observe a sub abaixo:

Sub preenchendo_taxas()

Dim m_valor_taxas(1 To 11)

m_taxas = Array("I.R.R.F.", "Taxa Liquidação", "Taxa Registro", "Taxa Termo/Opções", "Taxa A.N.A.", "Emolumentos", "Taxa Operacional", "Taxa Execução", "Taxa Custódia", "Impostos", "Taxa Outros")

cont = 1
For Each taxa In m_taxas

m_valor_taxas(cont) = InputBox("Digite o valor da taxa: " & taxa)
cont = cont + 1

Next

nota = InputBox("Digite o número da Nota de Corretagem") + 0


n_ativos = WorksheetFunction.CountIf(Sheets("Notas").Range("C:C"), nota)
n_ativos_v = WorksheetFunction.CountIfs(Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("G:G"), "V")

total_operacao = WorksheetFunction.SumIf(Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("L:L"))
total_operacao_v = WorksheetFunction.SumIfs(Sheets("Notas").Range("L:L"), Sheets("Notas").Range("C:C"), nota, Sheets("Notas").Range("G:G"), "V")

linha = Sheets("Notas").Range("A1048576").End(xlUp).Row

For i = 2 To linha

    If Sheets("Notas").Cells(i, 3) = nota Then

       For j = 1 To 11      

            If j = 1 Then 'Se o j for 1, significa que é o I.R.R.F.        

                If Sheets("Notas").Cells(i, 7) = "V" Then

                   Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao_v, 2)
               Else
                   Sheets("Notas").Cells(i, 4 + j) = 0

                End If
         

            Else 'Significa que estamos nas outras taxas
              
                Sheets("Notas").Cells(i, 14 + j) = Round(Sheets("Notas").Cells(i, 12) * m_valor_taxas(j) / total_operacao, 2)
             

            End If
     
       Next       

    End If

Next

End Sub


Explicação do código destacado acima (continuação do anterior):

  • Vamos pegar o número da nota, através da inputbox, e adicionando 0, tornamos esse valor um número (porque a inputbox em si nos retorna um texto), e vamos armazenar na variável “nota”
  • Em seguida, faremos a contagem de quantas vezes essa nota aparece na (coluna C da) nossa planilha, através da fórmula CountIf (equivalente a Cont.SE no VBA), e armazenar na variável “n_ativos”
  • Também vamos fazer a contagem de quantas vezes essa nota aparece na (coluna C da) nossa planilha, através da fórmula CountIfs (equivalente a Cont.SES no VBA), mas dessa vez para vendas, observando quantas vezes “V” aparece na coluna G, e armazenar na variável “n_ativos_v”
  • Em seguida iremos fazer a soma de valores que representa a soma dos valores das operações (coluna L), linha a linha, de onde essas notas aparecem (coluna C). Isso será feito através da fórmula SumIf (equivalente a SOMASE no VBA), e armazenado na variável “total_operacao”
  • Também iremos fazer a soma de valores que representa a soma dos valores das operações (coluna L), linha a linha, de onde essas notas aparecem (coluna C), mas só das situações de venda (“V” na coluna G). Isso será feito através da fórmula SumIfs (equivalente a SOMASES no VBA), e armazenado na variável “total_operacao_v”
  • Depois disso tudo feito, vamos buscar a última linha preenchida na tabela, e armazenar na variável “linha”. Mostramos em outras aulas como achar a última linha de uma tabela no Excel, como nessa.
  • Vamos então entrar no nosso loop mais externo (primeiro For): vamos percorrer as linhas da planilha, da linha 2 até a linha que é representada pela última linha preenchida da tabela (dada pela variável “linha”), fazendo as seguintes ações:
  • Se a célula que está na linha analisada, na coluna de notas (3), for igual ao digitado e armazenado na variável “nota”, queremos que o nosso código faça um outro loop, auxiliar, de 1 até a 11, que vá interpretar qual taxa estamos usando, e qual cálculo faremos para ela.
  • Se j = 1, significa que nós estamos na taxa IRRF. Então, queremos verificar se é uma Venda. Se for Venda, armazenaremos nessa mesma linha, na coluna representada por (14+j) da nossa planilha, que será correspondente à taxa, o valor da conta:
  • (VALOR DA OPERAÇÃO * VALOR DA TAXA(j) ) / (TOTAL DE OPERAÇÃO DE VENDA), arredondada para duas casas decimais
  • Se não for Venda, armazenaremos nessa mesma linha, na coluna representada por (14+j) da nossa planilha, que será correspondente à taxa, o valor “0”.
  • Se j for diferente de 1 (ou seja, quando a taxa não for IRRF), vamos fazer outra conta a fazer para aquela taxa em questão. Armazenaremos nessa mesma linha, na coluna representada por (14+j) da nossa planilha, que será correspondente à taxa, o valor da conta:
  • Sheets(“Notas”).Cells(i, 14 + j) = Round(VALOR DA OPERAÇÃO * VALOR DA TAXA (j) / TOTAL DE OPERAÇÃO DESSA NOTA), arredondada para duas casas decimais.
  • Continuando no looping, preencheremos e faremos as contas para todas as taxas/colunas dessa linha em questão, em que temos a nota de interesse.
  • Então, passaremos para a próxima linha, para verificar se ela corresponde a nossa variável “nota”, e para que possam ser registradas nessa linha, nas colunas das taxas, as taxas correspondentes a essa nota.
  • Se encerra o código

Aqui estão alguns links de apoio para esse post:

Conclusão do Preenchimento das Taxas com VBA 2 de 3

Nessa aula você lidou com matriz no VBA (array no VBA), assim como na anterior, podendo utilizar novamente a estrutura For que é muito importante, além da estrutura InputBox.

Nessa aula especificamente utilizamos das funções Cont.se no VBA, SOMASE e SOMASES no VBA com a utilização do método WorksheetFunction, que permite ao usuário a utilização das funções do Excel dentro do VBA.

Com isso vamos poder fazer o preenchimento automático de todas as taxas para as notas inseridas dentro da macro.

Dessa forma quando o usuário fizer o preenchimento das informações o código vai rodar para preencher todas as taxas das notas que foram inseridas.

Até o próximo post, que será o que fecharemos o código dessa aula e da anterior! Fique ligado no nosso canal do Youtube para mais conteúdo de Excel e VBA! Um abraço!

Hashtag Treinamentos

Para acessar outras publicações de VBA, clique aqui!


Quer aprender mais sobre VBA com um Minicurso Gratuito?


Vai começar o maior evento de Power BI da América Latina! Quer participar? Preencha seu e-mail abaixo: