Se você está começando agora com VBA, recomendamos que comece estudando o Guia para Iniciantes em VBA . Mas se você já é um usuário experiente de VBA e busca recursos mais avançados para usar o VBA no Excel, continue lendo.
A possibilidade de usar a linguagem de marcação VBA no Excel abre um mundo de possibilidades de automação. Você pode automatizar cálculos, botões e até mesmo o envio de e-mails no Excel. As possibilidades de automatizar seu trabalho diário com VBA são muito maiores do que você imagina.
Guia avançado de VBA para Microsoft Excel
O principal objetivo de escrever código VBA no Excel é permitir que você extraia informações de uma planilha, realize diversos cálculos com ela e, em seguida, grave os resultados de volta na planilha.
A seguir, apresentamos os usos mais comuns do VBA no Excel.
- Importar dados e realizar cálculos
- Cálculo de resultados com base nos cliques do usuário nos botões.
- Enviar os resultados dos cálculos por e-mail para alguém.
Usando esses três exemplos, você deverá ser capaz de escrever diversos códigos VBA avançados para Excel.
Importar dados e realizar cálculos
Uma das tarefas mais comuns que as pessoas realizam no Excel é a execução de cálculos com dados externos ao programa. Se você não estiver usando VBA, isso significa que terá que importar os dados manualmente, executar os cálculos e exportar esses valores para outra planilha ou relatório.
Usando VBA, você pode automatizar todo o processo. Por exemplo, se você tiver um novo arquivo CSV baixado para um diretório no seu computador toda segunda-feira, você pode configurar seu código VBA para ser executado quando você abrir sua planilha pela primeira vez na manhã de terça-feira.
O seguinte código de importação será executado e o arquivo CSV será importado para sua planilha do Excel.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = "c:temppurchases.csv" With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")).TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Abra o editor VBA do Excel e selecione o objeto Sheet1. Nos menus suspensos Objeto e Método, escolha Planilha e Ativar . Isso executará o código sempre que você abrir a planilha.
Isso criará uma função Sub Worksheet_Activate() . Cole o código acima nessa função.
Isso define a planilha ativa como Planilha1 , examina a planilha, conecta-se ao arquivo usando o caminho do arquivo especificado com a variável strFile e, em seguida, o loop With itera por cada linha do arquivo e coloca os dados na planilha a partir da célula A1.
Se você executar este código, verá que os dados do arquivo CSV serão importados para sua planilha vazia, na Planilha1.
A importação é apenas o primeiro passo. Em seguida, você precisará criar um novo cabeçalho de coluna que conterá os resultados dos seus cálculos. Neste exemplo, digamos que você queira calcular o imposto de 5% pago sobre a venda de cada item.
A ordem das ações que seu código deve executar é:
- Crie uma nova coluna de resultados chamada impostos.
- Repita ao longo de uma coluna unidades vendidas E calcule o imposto sobre vendas.
- Escreva os resultados dos cálculos na linha apropriada do papel.
O código a seguir executará todas essas etapas.
Dim LastRow As Long Dim StartCell As Range Dim rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double Set StartCell = Range("A1") 'Encontra a última linha e coluna LastRow = ws.Cells(ws.Rows.Count, StartCell.Column).End(xlUp).Row Set rng = ws.Range(ws.Cells(2, 4), ws.Cells(LastRow, 4)) rowCounter = 2 Cells(1, 5) = "taxes" For Each cell In rng fltTax = cell.Value * 0.05 Cells(rowCounter, 5) = fltTax rowCounter = rowCounter + 1 Next cell
Este código localiza a última linha da sua planilha de dados e, em seguida, define o intervalo de células (a coluna que contém os preços de venda) de acordo com a primeira e a última linha de dados. O código então percorre cada uma dessas células, calcula o imposto e grava os resultados na sua nova coluna (coluna 5).
Cole o código VBA acima abaixo do código anterior e execute o script. Você verá os resultados aparecerem na coluna E.
Agora, sempre que você abrir sua planilha do Excel, o programa buscará automaticamente a versão mais recente dos dados do arquivo CSV. Em seguida, realizará os cálculos e gravará os resultados na planilha. Você não precisará mais fazer nada manualmente!
Calcule os resultados pressionando o botão.
Se preferir ter controle direto sobre quando os cálculos são executados, em vez de executá-los automaticamente quando a planilha é aberta, você pode usar um botão de controle.
Os botões de controle são úteis se você quiser controlar os cálculos utilizados. Por exemplo, no mesmo cenário mencionado acima, e se você quisesse usar uma taxa de imposto de 5% para uma região e uma taxa de imposto de 7% para outra?
Você pode permitir que o mesmo código de importação de CSV seja executado automaticamente, mas deixar o código de cálculo de impostos para ser executado quando o botão apropriado for pressionado.
Usando a mesma planilha mencionada acima, selecione a guia Desenvolvedor e, em seguida, selecione Inserir no grupo Controles da faixa de opções. Selecione o Controle de Botão ActiveX no menu suspenso.
Desenhe o botão em qualquer parte do papel, longe de onde os dados seriam armazenados.
Clique com o botão direito do mouse no botão e selecione Propriedades . Na janela Propriedades, altere a legenda para o que você deseja exibir para o usuário. Neste caso, poderia ser Calcular Imposto de 5%.
Você verá esse texto refletido no próprio botão. Feche a janela Propriedades e clique duas vezes no botão. Isso abrirá uma janela do editor de código e o cursor estará dentro da função que será executada quando o usuário clicar no botão.
Cole o código de cálculo de impostos da seção anterior nesta função, mantendo o multiplicador da taxa de imposto em 0.05. Lembre-se de incluir as duas linhas seguintes para identificar a planilha ativa.
Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook.Sheets("Sheet1")
Agora, repita o processo e crie um segundo botão. Dê a ele o nome de " Cálculo de Imposto de 7%".
Clique duas vezes neste botão e cole o mesmo código, mas defina o multiplicador de impostos como 0.07.
Agora, dependendo do botão que você pressionar, a coluna de impostos será calculada de acordo.
Ao concluir, você terá os dois botões na sua planilha. Cada um iniciará um cálculo de imposto diferente e exibirá resultados diferentes na coluna de resultados.
Para testar, selecione o menu Desenvolvedor e, em seguida, selecione Modo de Design no grupo de controles da faixa de opções para desativar o Modo de Design . Isso ativará os botões.
Experimente selecionar cada botão para ver como a coluna de resultados "Impostos" se altera.
Enviar os resultados da conta por e-mail para alguém.
E se você quisesse enviar os resultados da planilha para alguém por e-mail?
Você pode criar outro botão chamado " Enviar Planilha para o Chefe" usando o mesmo procedimento descrito acima. O código para este botão usará um objeto CDO do Excel para configurar as definições de e-mail SMTP e enviar os resultados por e-mail em um formato amigável ao usuário.
Para ativar esse recurso, selecione Ferramentas e Referências . Role para baixo até Biblioteca do Microsoft CDO para Windows 2000 , ative-o e selecione OK.
Existem três seções principais de código que você precisa criar para enviar um e-mail e incluir os resultados da planilha.
A primeira etapa é configurar variáveis para armazenar o assunto, os endereços "para" e "de" e o texto do e-mail.
Dim CDO_Mail As Object Dim CDO_Config As Object Dim SMTP_Config As Variant Dim strSubject As String Dim strFrom As String Dim strTo As String Dim strCc As String Dim strBcc As String Dim strBody As String Dim LastRow As Long Dim StartCell As Range Dim rowCounter As Integer Dim rng As Range, cell As Range Dim fltTax As Double
Defina ws = ActiveWorkbook.Sheets("Sheet1") strSubject = "Impostos Pagos Neste Trimestre" strFrom = "MeuEmail@gmail.com" strTo = "EmailDoChefe@gmail.com" strCc = "" strBcc = "" strBody = "A seguir, o detalhamento dos impostos pagos sobre as vendas neste trimestre."
Obviamente, o texto precisa ser dinâmico, dependendo dos resultados apresentados no artigo. Portanto, você precisará adicionar um loop que percorra o intervalo, extraia os dados e escreva uma linha no texto a cada iteração.
Defina StartCell = Range("A1"). vbCrLf Para cada célula em rng strBody = strBody & vbCrLf strBody = strBody & "Vendemos " & Cells(rowCounter, 3).Value & " de " & Cells(rowCounter, 1).Value & " por " & Cells(rowCounter, 4).Value & " e pagamos impostos de " & Cells(rowCounter, 5).Value & "." rowCounter = rowCounter + 1 Próxima célula
A próxima seção envolve a configuração do SMTP para que você possa enviar e-mails através do seu servidor SMTP. Se você usa o Gmail, geralmente são o seu endereço de e-mail do Gmail, a senha do Gmail e o servidor SMTP do Gmail (smtp.gmail.com).
Defina CDO_Mail = CreateObject("CDO.Message") On Error GoTo Error_Handling Defina CDO_Config = CreateObject("CDO.Configuration") CDO_Config.Load -1 Defina SMTP_Config = CDO_Config.Fields Com SMTP_Config .Item("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2 .Item("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "smtp.gmail.com" .Item("http://schemas.microsoft.com/cdo/configuration/smtpauthenticate") = 1 .Item("http://schemas.microsoft.com/cdo/configuration/sendusername") = "email@website.com" .Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "senha" .Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465 .Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True Set .Configuration = CDO_Config End With
Substitua email@website.com e password pelos seus dados de conta.
Por fim, para começar a enviar o e-mail, digite o seguinte código.
CDO_Mail.Subject = strSubject CDO_Mail.From = strFrom CDO_Mail.To = strTo CDO_Mail.TextBody = strBody CDO_Mail.CC = strCc CDO_Mail.BCC = strBcc CDO_Mail.Send Error_Handling: If Err.Description <> "" Then MsgBox Err.Description
Observação : Se você vir um erro de transferência ao tentar executar este código, é provável que sua conta do Google esteja bloqueando a opção "Aplicativos menos seguros". Você precisará acessar a página de configurações de Aplicativos menos seguros e ativar essa opção.
Após ativar esta opção, seu e-mail será enviado. Veja como ele aparecerá para a pessoa que receber o e-mail gerado automaticamente.
Como você pode ver, há muita coisa que pode ser automatizada usando VBA no Excel. Experimente os trechos de código que você aprendeu neste artigo e crie suas próprias automações VBA exclusivas.
Comentários estão fechados.