Um guia VBA avançado para MS Excel

Se você está apenas começando com o VBA, convém começar a estudar nosso Guia VBA para iniciantes. Mas se você é um especialista experiente em VBA e está procurando coisas mais avançadas que pode fazer com o VBA no Excel, continue lendo.

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

Guia VBA avançado para Microsoft Excel

O principal objetivo de escrever código VBA no Excel é que você possa extrair informações de uma planilha, realizar uma variedade de 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 dados e realizar cálculos
• Calcule os resultados de um usuário pressionando um botão
• Resultados de cálculo de e-mail para alguém

Com esses três exemplos, você deve ser capaz de escrever uma variedade de seu próprio código VBA avançado do Excel.

Importação de dados e execução de cálculos

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 o VBA, isso significa que você tem que importar manualmente os dados, 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ê tem um novo arquivo CSV baixado em um diretório em seu computador toda segunda-feira, pode configurar seu código VBA para ser executado quando você abrir sua planilha pela primeira vez na manhã de terça-feira.

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

Dim ws As Worksheet, strFile As String Set ws = ActiveWorkbook. Folhas ("Folha1") Células. ClearContents strFile = “c: \ temp \ transactions.csv” Com ws. QueryTables. Adicionar (Conexão: = "TEXTO;" & strArquivo, Destino: = ws. Range ("A1")) .TextFileParseType = xlDelimited .TextFileCommaDelimiter = True .Refresh. Terminar com

Abra a ferramenta de edição do Excel VBA e selecione o objeto Plan1. Nas caixas suspensas de objeto e método, escolha Planilha e Ativar. Isso executará o código toda vez que você abrir a planilha.

Isso criará um Sub planilha_Activate () função. Cole o código acima nessa função.

Isso define a planilha ativa para Folha1, limpa a planilha, se conecta ao arquivo usando o caminho de arquivo que você definiu com o strFile variável, e então o Com o loop percorre todas as linhas do arquivo e coloca os dados na planilha, começando na célula A1.

Se você executar este código, verá que os dados do arquivo CSV são importados para sua planilha em branco, em Folha1.

A importação é apenas a primeira etapa. Em seguida, você deseja criar um novo cabeçalho para a coluna que conterá os resultados do 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 realizar é:

1. Crie uma nova coluna de resultados chamada impostos.
2. Faça um loop através do unidades vendidas coluna e calcular 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 Enquanto Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng como intervalo, célula como intervalo
Dim fltTax As Double
Definir StartCell = Range ("A1")
'Encontrar Última Linha e Coluna
LastRow = ws. Células (ws. Rows. Conte, StartCell. Coluna) .End (xlUp) .Row
Defina rng = ws. Intervalo (ws. Células (2, 4), ws. Células (LastRow, 4))
rowCounter = 2
Células (1, 5) = "impostos"
Para cada célula em rng
fltTax = célula. Valor * 0,05
Células (rowCounter, 5) = fltTax
rowCounter = rowCounter + 1
Próxima célula

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 mostrados 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 fará os cálculos e escreverá os resultados na planilha. Você não precisa mais fazer nada manualmente!

Calcular os resultados ao pressionar o botão

Se você preferir ter mais controle 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ê pode permitir que o mesmo código de importação CSV seja executado automaticamente, mas deixe o código de cálculo de imposto ser executado quando você pressiona o botão apropriado.

Usando a mesma planilha acima, selecione o Desenvolvedor guia e selecione Inserir de Controles grupo na faixa de opções. Selecione os botão de apertar Controle ActiveX no menu suspenso.

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

Clique com o botão direito no botão e selecione Propriedades. Na janela Propriedades, altere a legenda para o que você gostaria de exibir para o usuário. Neste caso, pode ser Calcular 5% de imposto.

Você verá este texto refletido no próprio botão. Feche o propriedades janela 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 Planilha, strFile As String
Defina ws = ActiveWorkbook. Folhas ("Folha1")

Agora, repita o processo novamente, criando um segundo botão. Faça a legenda Calcular 7% de imposto.

Clique duas vezes nesse botão e cole o mesmo código, mas faça o multiplicador de imposto de 0,07.

Agora, dependendo do botão pressionado, a coluna de impostos será calculada de acordo.

Quando terminar, você terá os dois botões em sua planilha. 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 Desenvolvedor menu e selecione Modo Design formar o grupo de controles na faixa de opções para desativar Modo Design. Isso ativará os botões.

Experimente selecionar cada botão de pressão para ver como a coluna de resultado “impostos” muda.

Resultados do cálculo de e-mail para alguém

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

Você pode criar outro botão chamado Folha de e-mail para o chefe 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 e-mail SMTP e enviar os resultados por e-mail em um formato legível pelo usuário.

Para habilitar este recurso, você precisa selecionar Ferramentas e referências. Role para baixo até Microsoft CDO para Windows 2000 Library, habilite-o e selecione OK.

Existem três seções principais do 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 Para e De e o corpo do e-mail.

Dim CDO_Mail As Object
Dim CDO_Config As Object
Dim SMTP_Config como variante
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 Enquanto Long
Dim StartCell As Range
Dim rowCounter As Integer
Dim rng como intervalo, célula como intervalo
Dim fltTax As Double

Defina ws = ActiveWorkbook. Folhas ("Folha1")
strSubject = "Impostos pagos neste trimestre"
strFrom = "[email protected]"
strTo = "[email protected]"
strCc = ""
strBcc = ""
strBody = "A seguir está a composição dos impostos pagos sobre as vendas neste trimestre."

Claro, o corpo precisa ser dinâmico dependendo de quais resultados estão na folha, então aqui você vai precisa adicionar um loop que atravessa o intervalo, extrai os dados e grava uma linha de cada vez no corpo.

Defina StartCell = Range ("A1") 'Find Last Row and Column. LastRow = ws. Células (ws. Rows. Conte, StartCell. Coluna) .End (xlUp) .Row. Defina rng = ws. Intervalo (ws. Células (2, 4), ws. Células (LastRow, 4)) rowCounter = 2. strBody = strBody & vbCrLf Para cada célula em rng strBody = strBody & vbCrLf strBody = strBody & "Nós vendemos" & Cells (rowCounter, 3) .Value & "of" & Cells (rowCounter, 1) .Value _ & "for" & Cells (rowCounter, 4) .Value & "e impostos pagos de" & Cells (rowCounter, 5) .Value & "." rowCounter = rowCounter + 1. Próxima célula

A próxima seção envolve definir as configurações de SMTP para que você possa enviar e-mail por meio de seu servidor SMTP. Se você usa o Gmail, normalmente é o seu endereço de e-mail do Gmail, sua senha do Gmail e o servidor SMTP do Gmail (smtp.gmail.com).

Set CDO_Mail = CreateObject ("CDO.Message") On Error GoTo Error_Handling. Definir CDO_Config = CreateObject ("CDO.Configuration") CDO_Config. Carregar -1. Defina SMTP_Config = CDO_Config. Campos 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 protected]" .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") = Verdadeiro. .Atualizar. Terminar com com CDO_Mail definido .Configuration = CDO_Config. Terminar com

Substituir [email protected] e senha com seus próprios detalhes de conta.

Por fim, para iniciar o envio do e-mail, insira o seguinte código.

CDO_Mail. Assunto = strSubject
CDO_Mail. De = strFrom
CDO_Mail. To = strTo
CDO_Mail. TextBody = strBody
CDO_Mail. CC = strCc
CDO_Mail. BCC = strBcc
CDO_Mail. Mandar
Manipulação de erros:
If Err. Descrição <> "" Then MsgBox Err. Descrição

Observação: Se você vir um erro de transporte ao tentar executar este código, provavelmente é porque sua conta do Google está bloqueando a execução de "aplicativos menos seguros". Você precisará visitar o página de configurações de aplicativos menos seguros e ativar esse recurso.

Depois de habilitado, seu e-mail será enviado. Isso é o que parece para a pessoa que recebe o e-mail com os resultados gerados automaticamente.

Como você pode ver, há muito que você pode realmente automatizar com o Excel VBA. Experimente brincar com os snippets de código que você aprendeu neste artigo e crie suas próprias automações VBA exclusivas.