Se você está apenas começando com VBA , então você vai querer começar a estudar nosso guia VBA para iniciantes^{(VBA guide for beginners)} . Mas se você for um especialista em VBA experiente e estiver procurando por coisas mais avançadas que pode fazer com o VBA no Excel , continue lendo.

A capacidade de usar a codificação VBA no ^(VBA)Excel abre todo um mundo de automação. Você pode automatizar cálculos no Excel , botões e até mesmo enviar e-mail. Há mais possibilidades de automatizar seu trabalho diário com o VBA do que você imagina.

Guia VBA Avançado para Microsoft Excel^{(Advanced VBA Guide For Microsoft Excel)}

O principal objetivo de escrever código VBA no ^(VBA)Excel é extrair informações de uma planilha, realizar vários cálculos nela e, em seguida, gravar os resultados de volta na planilha.

A seguir estão os usos mais comuns do VBA no Excel .

• Importar^(Import) dados e realizar cálculos
• Calcular^(Calculate) resultados de um usuário pressionando um botão
• Resultados de cálculo por e- mail^(Email) para alguém

Com esses três exemplos, você poderá escrever uma variedade de seu próprio código VBA avançado do Excel .^{(Excel VBA)}

Importando dados e realizando cálculos^{(Importing Data and Performing Calculations)}

Uma das coisas mais comuns para as quais as pessoas usam o Excel é realizar cálculos em dados que existem fora do Excel . Se você não usa VBA , isso significa que você precisa importar os dados manualmente, executar os cálculos e enviar esses valores para outra planilha ou relatório.

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^(Monday) , poderá configurar seu código VBA para ser executado quando abrir sua planilha pela primeira vez na terça^(Tuesday) -feira de manhã.

O código de importação a seguir será executado e importará o arquivo CSV para sua planilha do Excel^(Excel) .

Dim ws As Worksheet, strFile As String

Set 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 Excel VBA e selecione o objeto Sheet1 . Nas caixas suspensas de objeto e método, escolha Planilha^(Worksheet) e Ativar^(Activate) . 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.

Isso define a planilha ativa como Sheet1 , limpa a planilha, conecta-se ao arquivo usando o caminho do arquivo que você definiu com a variável strFile e, em seguida, o loop With percorre 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 são importados para sua planilha em branco, em Sheet1 .

A importação é apenas o primeiro passo. Em seguida, você deseja criar um novo cabeçalho para a coluna que conterá os resultados do cálculo. Neste exemplo, digamos que você queira calcular os 5% de impostos pagos na venda de cada item.

A ordem das ações que seu código deve executar é:

1. Crie uma nova coluna de resultados chamada impostos^(taxes) .
2. Percorra a coluna de unidades vendidas^{(units sold)} e calcule o imposto sobre vendas.
3. Escreva os resultados do cálculo na linha apropriada da planilha.

O código a seguir realizará 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 em 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 abaixo do código anterior e execute o script. Você verá os resultados aparecerem na coluna E.

Agora, toda vez que você abrir sua planilha do Excel^(Excel) , ela sairá automaticamente e obterá a cópia mais recente dos dados do arquivo CSV . Em seguida, realizará os cálculos e escreverá os resultados na folha. Você não precisa mais fazer nada manualmente!

Calcular resultados ao pressionar o botão^{(Calculate Results From Button Press)}

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^(Control) são úteis se você deseja controlar quais cálculos são usados. Por exemplo, neste mesmo caso acima, e se você quiser usar uma alíquota de 5% para uma região e uma alíquota de 7% para outra?

Você pode permitir que o mesmo código de importação CSV seja executado automaticamente, mas deixe o código de cálculo de imposto em execução quando você pressionar o botão apropriado.

Usando a mesma planilha acima, selecione a guia Desenvolvedor^(Developer) e selecione Inserir^(Insert) no grupo Controles^(Controls) na faixa de opções. Selecione o botão ^{(push button)} Controle ActiveX^{(ActiveX Control)} no menu suspenso.

Desenhe o botão em qualquer parte da folha longe de onde quaisquer dados irão.

Clique com o botão direito do mouse no botão de comando e selecione Propriedades^(Properties) . Na janela Propriedades^(Properties) , altere a Legenda para o que você gostaria de exibir ao usuário. Nesse caso, pode ser Calculate 5% Tax .

Você verá este texto refletido no próprio botão. Feche a janela de propriedades^(properties) e clique duas vezes no próprio botão. Isso abrirá a janela do editor de código e seu cursor estará dentro da função que será executada quando o usuário pressionar o botão.

Cole o código de cálculo do 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 de pressão. Faça a legenda Calculate 7% Tax .

Clique duas vezes^{(Double-click)} nesse botão e cole o mesmo código, mas torne o multiplicador de impostos 0,07.

Agora, dependendo de qual botão você pressionar, a coluna de impostos será calculada de acordo.

Quando terminar, você terá os dois botões na sua folha. Cada um deles iniciará um cálculo de imposto diferente e escreverá resultados diferentes na coluna de resultados. 

Para enviar um texto, selecione o menu Desenvolvedor^(Developer) e selecione Modo de Design^{(Design Mode)} no grupo Controles^(Controls) na faixa de opções para desabilitar o Modo de Design^{(Design Mode)} . Isso ativará os botões de pressão. 

Tente selecionar cada botão para ver como a coluna de resultados de “impostos” muda.

Enviar resultados de cálculo por e-mail para alguém^{(Email Calculation Results to Someone)}

E se você quiser enviar os resultados da planilha para alguém por e-mail?

Você pode criar outro botão chamado Email Sheet to Boss usando o mesmo procedimento acima. O código para este botão envolverá o uso do objeto CDO do Excel para definir as configurações de email ^{(Excel CDO)}SMTP e enviar os resultados por email em um formato legível pelo usuário.

Para habilitar esse recurso, você precisa selecionar Ferramentas e Referências^{(Tools and References)} . Role para baixo até a Biblioteca do Microsoft CDO para Windows 2000^{(Microsoft CDO for Windows 2000 Library)} , habilite-a e selecione OK .

Existem três seções principais no código que você precisa criar para enviar um e-mail e incorporar os resultados da planilha.

A primeira é configurar variáveis ​​para conter o assunto, os endereços de e para e o corpo do e-mail .^(From)

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."

É claro que o corpo precisa ser dinâmico dependendo de quais resultados estão na planilha, então aqui você precisará adicionar um loop que percorra o intervalo, extraia os dados e grave uma linha por 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 de SMTP para que você possa enviar e-mails por meio do servidor SMTP . Se você usa o Gmail , geralmente é seu endereço de e-mail do Gmail , sua senha do Gmail e o servidor ^(Gmail)SMTP^{(Gmail 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 pelos detalhes da sua própria 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

Observação^(Note) : se você vir um erro de transporte ao tentar executar esse código, é provável que sua conta do Google esteja bloqueando a execução de "aplicativos menos seguros". Você precisará visitar a página de configurações de aplicativos menos seguros^{(less secure apps settings page)} e ativar esse recurso.

Após habilitar, seu e-mail será enviado. Isso é o que parece para a pessoa que recebe seu e-mail de resultados gerados automaticamente.

Como você pode ver, há muito que você 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.^(VBA)

An Advanced VBA Guide For MS Excel

If you are just getting started with VBA, then уou’ll want tо start out studyіng our VBA guide for beginners. But if you’re a seasoned VBA expert and you’re looking for more advanced things you can do with VBA in Excel, then keep reading.

The ability to use VBA coding in Excel opens up a whole world of automation. You can automate calculations in Excel, pushbuttons, and even send email. There are more possibilities to automate your daily work with VBA than you may realize.

Advanced VBA Guide For Microsoft Excel

The main goal of writing VBA code in Excel is so that you can extract information from a spreadsheet, perform a variety of calculations on it, and then write the results back to the spreadsheet

The following are the most common uses of VBA in Excel.

• Import data and perform calculations
• Calculate results from a user pressing a button
• Email calculation results to someone

With these three examples, you should be able to write a variety of your own advanced Excel VBA code.

Importing Data and Performing Calculations

One of the most common things people use Excel for is performing calculations on data that exists outside of Excel. If you don’t use VBA, that means you have to manually import the data, run the calculations and output those values to another sheet or report.

With VBA, you can automate the entire process. For example, if you have a new CSV file downloaded into a directory on your computer every Monday, you can configure your VBA code to run when you first open your spreadsheet on Tuesday morning.

The following import code will run and import the CSV file into your Excel spreadsheet.

Dim ws As Worksheet, strFile As String

Set 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

Open the Excel VBA editing tool and select the Sheet1 object. From the object and method dropdown boxes, choose Worksheet and Activate. This will run the code every time you open the spreadsheet.

This will create a Sub Worksheet_Activate() function. Paste the code above into that function.

This sets the active worksheet to Sheet1, clears the sheet, connects to the file using the file path you defined with the strFile variable, and then the With loop cycles through every line in the file and places the data into the sheet starting at cell A1.

If you run this code, you’ll see that the CSV file data is imported into your blank spreadsheet, in Sheet1.

Importing is only the first step. Next, you want to create a new header for the column that will contain your calculation results. In this example, let’s say you want to calculate the 5% taxes paid on the sale of each item.

The order of actions your code should take is:

1. Create new results column called taxes.
2. Loop through the units sold column and calculate the sales tax.
3. Write the calculate results to the appropriate row in the sheet.

The following code will accomplish all of these steps.

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

This code finds the last row in your sheet of data, and then sets the range of cells (the column with the sales prices) according to the first and last row of data. Then the code loops through each of those cells, performs the tax calculation and writes the results into your new column (column 5).

Paste the above VBA code below the previous code, and run the script. You will see the results show up in column E.

Now, every time you open your Excel worksheet, it’ll automatically go out and get the freshest copy of data from the CSV file. Then, it will perform the calculations and write the results to the sheet. You don’t have to do anything manually anymore!

Calculate Results From Button Press

If you’d rather have more direct control over when calculations run, rather than running automatically when the sheet opens, you can use a control button instead.

Control buttons are useful if you want to control which calculations are used. For example, in this same case as above, what if you want to use a 5% tax rate for one region, and a 7% tax rate for another?

You could allow the same CSV import code to run automatically, but leave the tax calculation code to run when you press the appropriate button.

Using the same spreadsheet as above, select the Developer tab, and select Insert from the Controls group in the ribbon. Select the push button ActiveX Control from the dropdown menu.

Draw the pushbutton onto any part of the sheet away from where any data will go.

Right-click the push button, and select Properties. In the Properties window, change the Caption to what you’d like to display to the user. In this case it might be Calculate 5% Tax.

You’ll see this text reflected on the push button itself. Close the properties window, and double-click the pushbutton itself. This will open the code editor window, and your cursor will be inside the function that will run when the user presses the pushbutton.

Paste the tax calculation code from the section above into this function, keeping the tax rate multiplier at 0.05. Remember to include the following 2 lines to define the active sheet.

Dim ws As Worksheet, strFile As String

Set ws = ActiveWorkbook.Sheets("Sheet1")

Now, repeat the process again, creating a second push button. Make the caption Calculate 7% Tax.

Double-click that button and paste the same code, but make the tax multiplier 0.07.

Now, depending which button you press, the taxes column will be calculated accordingly.

Once you’re done, you’ll have both push buttons on your sheet. Each of them will initiate a different tax calculation and will write different results into the result column. 

To text this, select the Developer menu, and select Design Mode form the Controls group in the ribbon to disable Design Mode. This will activate the push buttons. 

Try selecting each push button to see how the “taxes” result column changes.

Email Calculation Results to Someone

What if you want to send the results on the spreadsheet to someone via email?

You could create another button called Email Sheet to Boss using the same procedure above. The code for this button will involve using the Excel CDO object to configure SMTP email settings, and emailing the results in a user-readable format.

To enable this feature, you need to select Tools and References. Scroll down to Microsoft CDO for Windows 2000 Library, enable it, and select OK.

There are three main sections to the code you need to create to send out an email and embed spreadsheet results.

The first is setting up variables to hold the subject, To and From addresses, and the email body.

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."

Of course, the body needs to be dynamic depending on what results are in the sheet, so here you’ll need to add a loop that goes through the range, extracts the data, and writes a line at a time to the body.

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

The next section involves setting up the SMTP settings so that you can send email through your SMTP server. If you use Gmail, this is typically your Gmail email address, your Gmail password, and the Gmail SMTP server (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

Replace [email protected] and password with your own account details.

Finally, to initiate the email send, insert the following code.

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

Note: If you see a transport error when trying to run this code, it’s likely because your Google account is blocking “less secure apps” from running. You’ll need to visit the less secure apps settings page and turn this feature ON.

After that’s enabled, your email will be sent. This is what it looks like to the person who receives your automatically generated results email.

As you can see there is a lot you can actually automate with Excel VBA. Try playing around with the code snippets you’ve learned about in this article and create your own unique VBA automations.

Related posts

• O melhor guia VBA (para iniciantes) que você precisará

• Como criar uma macro ou script VBA no Excel

• Um guia para todas as extensões de arquivo do Excel e o que elas significam

• Como excluir linhas em branco no Excel

• Corrigir a mensagem de erro "Não é possível verificar a licença" do MS Office

• 4 maneiras de converter Excel para Planilhas Google

• Planilhas Google vs Microsoft Excel – Quais são as diferenças?

• Como criar uma tabela dinâmica simples no Excel

• Centralize seus dados de planilha no Excel para impressão

• Como inserir rapidamente várias linhas no Excel

• Como calcular o Z-Score no Excel

• Por que você deve usar intervalos nomeados no Excel

• Usando a ferramenta de busca de meta de análise de variações hipotéticas do Excel

• Como mesclar células, colunas e linhas no Excel

• Como criar várias listas suspensas vinculadas no Excel

• Verifique todos os hiperlinks em um documento do MS Word em segundos

• Agrupar linhas e colunas em uma planilha do Excel

• Como usar instruções if e if aninhadas no Excel

• Soletrar rapidamente números no Word e Excel

• 2 maneiras de usar a função de transposição do Excel