Se você está apenas começando com o VBA, comece a estudar nossos Guia VBA para iniciantes. Mas se você é um especialista experiente em VBA e está procurando coisas mais avançadas que possa fazer com o VBA no Excel, continue lendo.
A capacidade de usar a codificação VBA no Excel abre um mundo inteiro de automação. Você pode automatizar cálculos no Excel, botões de pressão e até enviar email. Há mais possibilidades de automatizar seu trabalho diário com o VBA do que você imagina.
Guia avançado de VBA para Microsoft Excel
O principal objetivo de escrever código VBA no Excel é para que você possa extrair informações a partir de uma planilha, execute vários cálculos e, em seguida, escreva os resultados na planilha
A seguir, são apresentados os usos mais comuns do VBA no Excel.
Com esses três exemplos, você deve seja capaz de escrever uma variedade de seu próprio código VBA avançado do Excel.
Importando dados e realizando cálculos
Uma das coisas mais comuns que as pessoas usam o Excel para está realizando cálculos com dados que existem fora do Excel. Se você não usa o VBA, isso significa que você deve importar manualmente os dados, executar os cálculos e gerar esses valores para outra planilha ou relatório.
In_content_1 all: [300x250] / dfp : [640 x 360]->Com o VBA, você pode automatizar todo o processo. Por exemplo, se você tiver um novo arquivo CSV baixado em um diretório em seu computador toda segunda-feira, poderá configurar seu código VBA para executar quando abrir sua planilha pela primeira vez na manhã de terça-feira.
O seguinte código de importação será execute e importe o arquivo CSV para sua planilha do Excel.
Dim ws As Worksheet, strFile As StringSet ws = ActiveWorkbook.Sheets("Sheet1") Cells.ClearContents strFile = “c:\temp\purchases.csv” With ws.QueryTables.Add(Connection:="TEXT;" & strFile, Destination:=ws.Range("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh End With
Abra a ferramenta de edição do Excel VBA e selecione o objeto Sheet1. Nas caixas suspensas de objeto e método, escolha Planilhae Ativar. Isso executará o código toda vez que você abrir a planilha.
Isso criará uma função Sub Worksheet_Activate (). Cole o código acima nessa função.
Define a planilha ativa como Planilha1, limpa a planilha, conecta-se ao arquivo usando o caminho do arquivo definido com a variável strFilee, em seguida, a O looppercorre todas as linhas do arquivo e coloca os dados na planilha começando na célula A1.
Se você executar esse código, verá que os dados do arquivo CSV é importado para sua planilha em branco, na Planilha1.
A importação é apenas o primeiro passo . Em seguida, você deseja criar um novo cabeçalho para a coluna que conterá seus resultados de cálculo. Neste exemplo, digamos que você deseja calcular os impostos de 5% pagos na venda de cada item.
A ordem das ações que seu código deve executar é:
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")
'Find Last Row and Column
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 encontra a última linha na sua planilha de dados e, em seguida, define o intervalo de células (a coluna com os preços de venda) de acordo com a primeira e a última linha de dados. Em seguida, o código percorre cada uma dessas células, executa o cálculo do imposto e grava os resultados em sua nova coluna (coluna 5).
Cole o código VBA acima do código anterior e execute o script. Você verá os resultados exibidos na coluna E.
Agora, toda vez que você abre sua planilha do Excel, ela sai automaticamente e obtém a cópia mais recente dos dados do arquivo CSV. Em seguida, ele realizará os cálculos e gravará os resultados na planilha. Você não precisa mais fazer nada manualmente!
Calcule resultados pressionando o botão
Se preferir ter um controle mais direto sobre quando os cálculos são executados , em vez de executar automaticamente quando a planilha é aberta, você pode usar um botão de controle.
Os botões de controle são úteis se você deseja controlar quais cálculos são usados. Por exemplo, no mesmo caso acima, e se você quiser usar uma taxa de imposto de 5% para uma região e uma taxa de imposto de 7% para outra?
Você poderia permitir o mesmo código de importação CSV para execute automaticamente, mas deixe o código de cálculo de imposto para executar quando você pressionar o botão apropriado.
Usando a mesma planilha acima, selecione a guia Desenvolvedore selecione Inserirdo grupo Controlesna faixa de opções. Selecione o botãoControle ActiveX no menu suspenso.
Coloque o botão em qualquer parte da planilha, longe de onde os dados irão.
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. Nesse caso, pode ser Calcular imposto de 5%.
Você verá este texto refletido no próprio botão. Feche a janela propriedadese clique duas vezes no próprio botão. Isso abrirá a janela do editor de código e o cursor estará dentro da função que será executada quando o usuário pressionar o botão.
Cole o código de cálculo de imposto da seção acima nesta função, mantendo o multiplicador da taxa de imposto em 0,05. Lembre-se de incluir as 2 linhas a seguir para definir a planilha ativa.
Dim ws As Worksheet, strFile As String
Set ws = ActiveWorkbook.Sheets("Sheet1")
Agora, repita o processo novamente, criando um segundo botão. Crie a legenda Calcular imposto de 7%.
Clique duas vezes nesse botão e cole o mesmo código, mas faça com que o multiplicador de impostos seja 0,07.
Agora, dependendo do botão que você pressionar, a coluna "Impostos" calculado de acordo.
Quando terminar, você terá os dois botões na folha. Cada um deles iniciará um cálculo de imposto diferente e gravará resultados diferentes na coluna de resultados.
Para enviar uma mensagem de texto, selecione o menu Desenvolvedore selecione Modo Designdo grupo Controles na faixa de opções para desativar o Modo Design. Isso ativará os botões de pressão.
Tente selecionar cada botão para ver como a coluna de resultados "impostos" muda.
Enviar os resultados do cálculo por e-mail para alguém
O que se você deseja enviar os resultados da planilha para alguém por e-mail?
Você pode criar outro botão chamado Email Sheet to Bossusando o mesmo procedimento acima. O código para esse botão envolverá o uso do objeto CDO do Excel para definir as configurações de email SMTP e enviar os resultados por email em um formato legível pelo usuário.
Para ativar esse recurso, selecione Ferramentas e Referências. Role para baixo até Microsoft CDO para Windows 2000 Library, ative-o e selecione OK.
Existem três seções principais no código que você precisa criar para enviar um email e incorporar os resultados da planilha.
A primeira é a configuração de variáveis a serem mantidas o assunto, os endereços Para e De e o corpo do email.
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
Set ws = ActiveWorkbook.Sheets("Sheet1")
strSubject = "Taxes Paid This Quarter"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "The following is the breakdown of taxes paid on sales this quarter."
Obviamente, o corpo precisa ser dinâmico, dependendo dos resultados. na planilha, aqui você precisará adicionar um loop que percorre o intervalo, extrai os dados e grava uma linha de cada vez no corpo.
Set StartCell = Range("A1")
'Find Last Row and Column
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
strBody = strBody & vbCrLf
For Each cell In rng
strBody = strBody & vbCrLf
strBody = strBody & "We sold " & Cells(rowCounter, 3).Value & " of " & Cells(rowCounter, 1).Value _
& " for " & Cells(rowCounter, 4).Value & " and paid taxes of " & Cells(rowCounter, 5).Value & "."
rowCounter = rowCounter + 1
Next cell
A próxima seção envolve a configuração das configurações SMTP, para que você possa enviar e-mail através do servidor SMTP. Se você usa o Gmail, esse é normalmente o seu endereço de e-mail, a senha do Gmail e o servidor SMTP do Gmail (smtp.gmail.com).
Set CDO_Mail = CreateObject("CDO.Message")
On Error GoTo Error_Handling
Set CDO_Config = CreateObject("CDO.Configuration")
CDO_Config.Load -1
Set SMTP_Config = CDO_Config.Fields
With 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 protected]"
.Item("http://schemas.microsoft.com/cdo/configuration/sendpassword") = "password"
.Item("http://schemas.microsoft.com/cdo/configuration/smtpserverport") = 465
.Item("http://schemas.microsoft.com/cdo/configuration/smtpusessl") = True
.Update
End With
With CDO_Mail
Set .Configuration = CDO_Config
End With
Substitua [email protected] e senha com os detalhes da sua conta.
Por fim, para iniciar o envio de e-mail, insira 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
Nota: se você encontrar um erro de transporte ao tentar executar esse código, é provável que sua conta do Google esteja impedindo a execução de "aplicativos menos seguros". Você precisará visitar os página de configurações de aplicativos menos seguros e ativar esse recurso.
Depois de ativado, seu email será enviado. É assim que a pessoa recebe o e-mail de resultados gerado automaticamente.
Como você pode ver, há muito que pode realmente automatizar com o Excel VBA. Tente brincar com os trechos de código que você aprendeu neste artigo e crie suas próprias automações VBA exclusivas.