Controle para Empresas Aula23 – Atualizando a Aba de DRE

Hoje em Controle para Empresas Aula23 vamos começar a atualização da aba de DRE para automatizar essa parte do nosso formulário.

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

Para receber por e-mail o(s) arquivo(s) utilizados na aula, preencha:

Essa é a vigésima terceira aula (Controle para Empresas Aula23) da nossa Série de Controle para Empresas. Nessa aula vamos dar início a construção da macro para cálculo de DRE.

Vamos fazer esses cálculos no VBA ao invés de fazer no Excel, então vamos construir essa macro do zero para esses cálculos.

Vamos utilizar a estrutura condicional (função IF) que você já sabe como funciona, vamos utilizar o MsgBox (caixa de mensagens), pois vamos precisar informar o usuário.

Uma estrutura importante é o CLng para transformar valor para long (principalmente na parte de datas), pois o VBA acaba tendo dificuldades na comparação de datas com números.

O worksheetfunction você já deve conhecer, que é para usar funções do Excel no VBA, então vamos utilizar o SumIfs (que seria o SOMASES no VBA) para poder somar os valores corresponde ao ano desejado.

E claro, tudo isso vai estar dentro de um botão, então vamos precisar utilizar os eventos no VBA para que ao clicar essa macro seja executada!

Acessando o código do formulário

Para fazer alterações no nosso formulário já criado, precisamos primeiramente abrir o ambiente de VBA, a partir do atalho do teclado Alt(+Fn)+F11. Assim poderemos continuar o nosso formulário de controle da empresa.

Mais uma vez lembrando que, para abrir o código do nosso formulário de interesse, devemos ir com o botão direito no formulário ControleDaEmpresa > exibir código.

Inserindo o código abaixo dentro dos códigos já existentes no formulário

Private Sub btCarregarDRE_Click()

Dim recBruta As Double, dedRec As Double, recLiq As Double, cmv As Double
Dim resBruto As Double, despOp As Double, resAntIR As Double
Dim impRenda As Double, resLiq As Double
Dim anoAnalise As Long
Dim abaVendas As Object, abaCaixa As Object, abaDRE As Object

If cxAnoDRE.Value = "" Then
    MsgBox "A caixa de ano está vazia. Favor preencher a caixa com o ano desejado."
    Exit Sub
End If


Set abaVendas = Sheets("Vendas")
Set abaCaixa = Sheets("Caixa")
Set abaDRE = Sheets("DRE")

anoAnalise = CLng(cxAnoDRE.Value)

recBruta = WorksheetFunction.SumIfs(abaVendas.Range("G:G"), abaVendas.Range("C:C"), ">=" & CLng(DateSerial(anoAnalise, 1, 1)), abaVendas.Range("C:C"), "<=" & CLng(DateSerial(anoAnalise, 12, 31)))
dedRec = 0
recLiq = recBruta - dedRec

cmv = WorksheetFunction.SumIfs(abaVendas.Range("I:I"), abaVendas.Range("C:C"), ">=" & CLng(DateSerial(anoAnalise, 1, 1)), abaVendas.Range("C:C"), "<=" & CLng(DateSerial(anoAnalise, 12, 31)))
resBruto = recLiq - cmv

despOp = -WorksheetFunction.SumIfs(abaCaixa.Range("G:G"), abaCaixa.Range("C:C"), ">=" & CLng(DateSerial(anoAnalise, 1, 1)), abaCaixa.Range("C:C"), "<=" & CLng(DateSerial(anoAnalise, 12, 31)), abaCaixa.Range("E:E"), "Despesa")
resAntIR = resBruto - despOp

impRenda = recBruta * 0.06
resLiq = resAntIR - impRenda

abaDRE.Cells(1, 2).Value = anoAnalise
abaDRE.Cells(2, 2).Value = recBruta
abaDRE.Cells(3, 2).Value = dedRec
abaDRE.Cells(4, 2).Value = recLiq
abaDRE.Cells(5, 2).Value = cmv
abaDRE.Cells(6, 2).Value = resBruto
abaDRE.Cells(7, 2).Value = despOp
abaDRE.Cells(8, 2).Value = resAntIR
abaDRE.Cells(9, 2).Value = impRenda
abaDRE.Cells(10, 2).Value = resLiq

abaDRE.Cells(4, 3).Value = recLiq / recBruta
abaDRE.Cells(6, 3).Value = resBruto / recBruta
abaDRE.Cells(8, 3).Value = resAntIR / recBruta
abaDRE.Cells(10, 3).Value = resLiq / recBruta

abaDRE.Range("A:C").Columns.AutoFit

Set abaVendas = Nothing
Set abaCaixa = Nothing
Set abaDRE = Nothing


End Sub

Explicação do código acima:

• Vamos dimensionar as seguintes variáveis como números do tipo Double:
  • recBruta, dedRec, recLiq, cmv
  • resBruto, despOp, resAntIR
  • impRenda, resLiq
• Vamos dimensionar anoAnalise como número do tipo Long
• Vamos dimensionar abaVendas, abaCaixa e abaDRE como objetos
• Para validar o ano de análise do DRE a ser “feito” vamos ver se a CxAnoDRE está preenchida (é diferente de vazio).
  • Caso esteja vazia, uma mensagem será exibida na tela alertando para esse fato e o código vai ser abortado através do comando Exit Sub (instrução de saída).
  • Caso ela esteja preenchida, vamos prosseguir com o código para disparar o código que dará o DRE na página específica no formulário
• Vamos configurar a variável abaVendas para representar um objeto inteiro, que será nesse caso a aba do nosso arquivo em Excel “Vendas”
• Vamos configurar a variável abaCaixa para representar um objeto inteiro, que será nesse caso a aba do nosso arquivo em Excel “Caixa”
• Vamos configurar a variável abaDRE para representar um objeto inteiro, que será nesse caso a aba do nosso arquivo em Excel “DRE”
• Vamos armazenar na variável “anoAnalise” o número do ano (dado pela função de conversão CLng) do valor que estiver escrito na CxAnoDRE
• Armazenar na variável “recBruta” o resultado da função SOMASES no VBA, que vai somar os valores da coluna G da aba Vendas, considerando o que na coluna C da aba Vendas for equivalente a uma data maior ou igual ao 1º dia do 1º mês do anoAnalise, e menor ou igual ao 31º dia do 12º mês do anoAnalise
• Armazenar na variável “dedRec” valor zero (0)
• Armazenar na variável “recLiq” o resultado da subtração entre “recBruta” e “dedRec”
• Armazenar na variável “cmv” o resultado da função SOMASES no VBA, que vai somar os valores da coluna I da aba Vendas, considerando o que na coluna C da aba Vendas for equivalente a uma data maior ou igual ao 1º dia do 1º mês do anoAnalise, e menor ou igual ao 31º dia do 12º mês do anoAnalise
• Armazenar na variável “resBruto” o resultado da subtração entre “recLiq” e “cmv”
• Armazenar na variável “despOp” o resultado da função SOMASES no VBA, que vai somar os valores da coluna G da aba Caixa, considerando o que na coluna C da aba Caixa for equivalente a uma data maior ou igual ao 1º dia do 1º mês do anoAnalise, e menor ou igual ao 31º dia do 12º mês do anoAnalise. Multiplicaremos esse resultado por -1 para representar uma despesa (sinal negativo)
• Armazenar na variável “resAntIR” o resultado da subtração entre “resBruto” e “despOP”
• Armazenar na variável “impRenda” valor resultante da multiplicação entre a variável “recBruta” e 6% (0,06)
• Armazenar na variável “resAntIR” o resultado da subtração entre “resAntIR” e “impRenda”
• Agora vamos passar os valores das variáveis para as células da 2ª coluna (coluna B) da aba DRE:
  • Na 1ª linha vamos colocar o que tiver na variável anoAnalise
  • Na 2ª linha vamos colocar o que tiver na variável recBruta
  • Na 3ª linha vamos colocar o que tiver na variável dedRec
  • Na 4ª linha vamos colocar o que tiver na variável recLiq
  • Na 5ª linha vamos colocar o que tiver na variável cmv
  • Na 6ª linha vamos colocar o que tiver na variável resBruto
  • Na 7ª linha vamos colocar o que tiver na variável despOp
  • Na 8ª linha vamos colocar o que tiver na variável resAntIR
  • Na 9ª linha vamos colocar o que tiver na variável impRenda
  • Na 10ª linha vamos colocar o que tiver na variável resLiq
• Agora vamos passar os valores dos percentuais para as células da 3ª coluna (coluna C) da aba DRE:
  • Na 4ª linha vamos colocar o resultado da razão entre recLiq e recBruta
  • Na 6ª linha vamos colocar o resultado da razão entre resBruto e recBruta
  • Na 8ª linha vamos colocar o resultado da razão entre resAntIR e recBruta
  • Na 10ª linha vamos colocar o resultado da razão entre resLiq e recBruta
• Vamos fazer o autoajuste do tamanho das colunas A até C da abaDRE
• Vamos apagar o conteúdo (Nothing) da variável abaVendas, abaCaixa e abaDRE, que antes armazenava um objeto
• Se encerra o código

Veja um exemplo pronto depois de rodar o DRE para o ano de 2021, mostrado em aula:

OBS: Para mais detalhes dos tipos de variáveis no VBA visite essa página.

OBS2: Explicamos a estrutura If em outro post do nosso blog também de forma detalhada, caso queira conferir.

OBS3: Para conferir mais detalhes sobre usar as funções do Excel no VBA (WorksheetFunction) você pode dar uma olhada nesse outro post em que abordamos justamente isso.

Até agora, na parte 1, conseguimos:

• Validar Caixa Ano
• Fazer cálculos da DRE
• Preencher a Aba DRE

Nas próximas aulas vamos fechar o código dessa aba.

Conclusão do Controle para Empresas Aula23

Nessa aula (Controle para Empresas Aula23) você resumidamente lidou com:

• Como descobrir a última linha no VBA
• Macro para cálculo de DRE
• WorksheetFunction no VBA
• Estrutura Condicional If
• Msgbox VBA

Até mais, pessoal! Fique ligado no nosso canal do Youtube para mais conteúdo de Excel, VBA e Power BI toda semana! Um abraço!

Hashtag Treinamentos

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

Quer aprender mais sobre VBA com um Minicurso Gratuito?