Um guia vBA avançado para o MS Excel

Se você está apenas começando com o VBA, então você deve começar a estudar nosso guia VBA para iniciantes. Mas se você é um especialista em VBA experiente 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 um mundo inteiro de automação. Você pode automatizar cálculos no Excel, PushButtons e até enviar e -mail. Existem mais possibilidades para automatizar seu trabalho diário com VBA do que você pode imaginar.

Isso define a planilha ativa para Folha1, Limpa a folha, se conecta ao arquivo usando o caminho do arquivo definido com o strfile variável, e então o Com O loop percorre todas as linhas do arquivo e coloca os dados na folha 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.

Importar é 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ê queira calcular os impostos de 5% pagos sobre a venda de cada item.

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

1. Crie uma nova coluna de resultados chamada impostos.
2. Loop através do unidades vendidas coluna e calcule o imposto sobre vendas.
3. Escreva os resultados de calcular para a linha apropriada na folha.

O código a seguir realizará todas essas etapas.

Dim lastrow por tanto tempo
Dim StartCell como alcance
Dim Rowcounter como inteiro
Dim RNG como alcance, célula como alcance
Dim flttax como o dobro
Defina startCell = range ("A1")
'Encontre a última fila e coluna
Lastrow = ws.Células (WS.Linhas.Conde, StartCell.Coluna).Fim (xlup).Linha
Definir rng = ws.Intervalo (ws.Células (2, 4), WS.Células (Lastrow, 4))
linecounter = 2
Células (1, 5) = "impostos"
Para cada célula em RNG
flttax = célula.Valor * 0.05
Células (rowcounter, 5) = FLTTAX
linecounter = linecounter + 1
Próxima célula

Este código encontra a última linha em sua folha 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 de impostos e escreve 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 aparecer na coluna E.

Agora, toda vez que você abre sua planilha do Excel, ela sairá automaticamente e obtém a cópia mais nova dos dados do arquivo CSV. Em seguida, ele executará os cálculos e escreverá os resultados para a folha. Você não precisa mais fazer nada manualmente!

Calcule os resultados da pressão de botão

Se você preferir ter um controle mais direto sobre quando os cálculos são executados, em vez de funcionar automaticamente quando a folha é aberta, você pode usar um botão de controle em vez.

Os botões de controle são úteis se você deseja controlar quais cálculos são usados. Por exemplo, nesse 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 outro?

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

Usando a mesma planilha acima, selecione o Desenvolvedor guia e selecione Inserir de Controles grupo na fita. Selecione os botão de apertar Controle ActiveX do menu suspenso.

Desenhe o botão para qualquer parte da folha para longe de onde vão os dados.

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

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 de impostos da seção acima nessa função, mantendo o multiplicador de taxa de imposto em 0.05. Lembre -se de incluir as 2 linhas a seguir para definir a folha ativa.

Dim ws como planilha, strfile como string
Definir WS = Activeworkbook.Folhas ("Sheet1")

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

Clique duas vezes nesse botão e colar o mesmo código, mas faça o multiplicador de impostos 0.07.

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

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

Para enviar uma mensagem, selecione o Desenvolvedor menu e selecione Modo de design formar o grupo de controles na fita para desativar Modo de design. Isso vai ativar os botões de push.

Tente selecionar cada botão de pressão para ver como as mudanças na coluna de resultado "impostos".

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

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

Você pode criar outro botão chamado Folha de e -mail para chefe usando o mesmo procedimento acima. O código para este botão envolverá o uso do objeto CDO do Excel para definir configurações de email SMTP e enviar os resultados em um formato legível pelo usuário.

Para ativar esse recurso, você precisa selecionar Ferramentas e referências. Role para baixo até Microsoft CDO para biblioteca Windows 2000, Ative -o e selecione OK.

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

O primeiro é configurar variáveis ​​para manter o assunto, para e para os endereços, e o corpo de e -mail.

Dim cdo_mail como objeto
Dim cdo_config como objeto
Dim smtp_config como variante
Strsubject escum como string
Dim strfrom como string
Dim strto como string
Dim STRCC como string
Dim STRBCC como string
Dim strbody como corda
Dim lastrow por tanto tempo
Dim StartCell como alcance
Dim Rowcounter como inteiro
Dim RNG como alcance, célula como alcance
Dim flttax como o dobro

Definir WS = Activeworkbook.Folhas ("Sheet1")
strsubject = "Impostos pagos neste trimestre"
strfrom = "[email protected] "
strto = "correio de [email protected] "
strcc = ""
strbcc = ""
strbody = "O seguinte é o detalhamento dos impostos pagos nas vendas neste trimestre."

Obviamente, o corpo precisa ser dinâmico, dependendo dos resultados na folha, então aqui você precisará adicionar um loop que passa pelo intervalo, extrai os dados e grava uma linha de cada vez no corpo.

Defina startCell = range ("A1") 'Encontre a última linha e coluna lastrow = ws.Células (WS.Linhas.Conde, StartCell.Coluna).Fim (xlup).Define rng = ws.Intervalo (ws.Células (2, 4), WS.Células (lastrow, 4)) linecounter = 2 strbody = strbody & vbcrlf para cada célula em rng strbody = strbody & vbcrlf strbody = strbody & "vendemos" e células (growcounter, 3).Valor e "de" e células (giro, 1).Valor _ & "para" & Cells (Rowcounter, 4).Valor e "e impostos pagos de" e células (rowcounter, 5).Valor & "."RowCounter = RowCounter + 1 Próxima célula

A próxima seção envolve a configuração das configurações do SMTP para que você possa enviar e -mail através do seu servidor SMTP. Se você usa o Gmail, este é tipicamente o seu endereço de e -mail do Gmail, sua senha do Gmail e o servidor SMTP do Gmail (SMTP.Gmail.com).

Definir cdo_mail = createObject ("cdo.Mensagem ") no erro goto error_handling set cdo_config = createObject (" cdo.Configuração ") cdo_config.Carregar -1 set smtp_config = cdo_config.Campos com smtp_config .Item ("http: // esquemas.Microsoft.com/cdo/configuração/sendusing ") = 2 .Item ("http: // esquemas.Microsoft.com/cdo/configuração/smtpServer ") =" smtp.Gmail.com " .Item ("http: // esquemas.Microsoft.com/cdo/configuração/smtpauthenticate ") = 1 .Item ("http: // esquemas.Microsoft.com/cdo/configuration/sendUserName ") =" [email protected] " .Item ("http: // esquemas.Microsoft.com/cdo/configuração/sendpassword ") =" senha " .Item ("http: // esquemas.Microsoft.com/cdo/configuração/smtpServerport ") = 465 .Item ("http: // esquemas.Microsoft.com/cdo/configuração/smtpusessl ") = true .Atualizar termina com o conjunto de cdo_mail .Configuração = CDO_CONFIG END COM

Substitua o [email protected] e senha com os detalhes da sua própria conta.

Finalmente, para iniciar o e -mail enviando, insira o seguinte código.

Cdo_mail.Assunto = StrSubject
Cdo_mail.De = strfrom
Cdo_mail.Para = strto
Cdo_mail.Textbody = strbody
Cdo_mail.CC = STRCC
Cdo_mail.BCC = STRBCC
Cdo_mail.Enviar
Manipulação de erros:
Se err.Descrição "" então msgbox err err.Descrição

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

Depois disso está ativado, seu e -mail será enviado. É assim que parece para a pessoa que recebe seus resultados gerados automaticamente email.

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.