Se você está começando a usar VBA, precisará começar estudando. Um guia para iniciantes em VBAMas se você é um especialista em VBA experiente e está procurando por recursos mais avançados que podem ser utilizados com 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. Nas listas suspensas Objeto e Método, escolha Planilha و AtivaçãoIsso fará com que o código seja executado sempre que você abrir a planilha.
Isso criará uma função Sub Worksheet_Activate()Cole o código acima nesta função.
Isso define a planilha ativa para Sheet1Digitalize a planilha e conecte-se ao arquivo usando o caminho do arquivo especificado na variável. strFileEntão o loop Com Percorrendo cada linha do arquivo e inserindo os dados na planilha a partir da célula A1.
Ao executar este código, você verá que os dados do arquivo CSV serão importados para sua planilha vazia. Sheet1.
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.
Utilizando a mesma tabela de dados mencionada acima, selecione a aba. Developer, E selecione inserção De um grupo Controles Na faixa de opções, selecione um controle. botão de apertar Selecione 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 ComparativasNa janela Propriedades, altere a legenda para o que você deseja exibir para o usuário. Nesse caso, poderia ser Calcule o imposto de 5%..
Você verá esse texto refletido no próprio botão. Fechar janela PropriedadesClique duas vezes no mesmo botão. Isso abrirá uma janela do editor de código e o cursor ficará 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ê um nome a ele. Cálculo do 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 isso, selecione uma lista. O desenvolvedor, E selecione Modo de design No grupo de controles da faixa de opções, desative-os. Modo de designIsso 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 Folha de envio de e-mail para o chefe Utilizando o mesmo procedimento descrito acima, o código para este botão envolverá o uso de um objeto CDO do Excel para configurar as definições de email SMTP e enviar os resultados por email num formato amigável para o utilizador.
Para ativar esse recurso, você precisa selecionar Ferramentas e Referências. Role para baixo até Biblioteca Microsoft CDO para Windows 2000Ative-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
substituir e-mail@site.com A senha contém os dados da sua 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
NotaSe você vir um erro de transferência ao tentar executar este código, é provável que sua conta do Google esteja bloqueando a execução de "aplicativos menos seguros". Você precisará visitar [link/referência]. Página de configurações de aplicativos menos seguros E habilite esta 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.