neste artigo, vou mostrar-lhe alguns dos mais incríveis VBA do Excel códigos que você pode usar para otimizar o seu trabalho. VBA é uma linguagem de programação, que pode ser usada para estender as capacidades do MS Excel e outras aplicações do MS Office. É extremamente útil para os usuários do MS Excel, porque ele pode ser usado para automatizar o seu trabalho e melhorar significativamente a sua eficiência. Este artigo irá apresentá-lo ao VBA e mostrar-lhe alguns dos mais úteis, prontos para usar os códigos VBA lá fora. Você pode usar estes exemplos de macro para criar seus próprios scripts que se encaixem em suas próprias necessidades.

Você não precisa de experiência de programação para tirar proveito da informação neste artigo, mas espera-se que tenha conhecimento básico do Excel. Se você é um usuário iniciante, eu recomendo que você leia as fórmulas do artigo 20 Excel que você deve começar a usar agora para saber mais sobre as funcionalidades centrais do Excel.

preparei para você uma série de exemplos de Macro do VBA Excel prontos para usar com grande funcionalidade que você pode usar para otimizar seu trabalho. Para usá-los, você precisa “instalá-los” em seu arquivo Excel. O parágrafo seguinte trata da instalação de macro Excel. Pule esta parte se você já estiver familiarizado com isso.

como instalar uma macro

no Excel, pressione a combinação de teclas alt + F11. Isto irá levá-lo ao editor VBA no MS Excel. Em seguida, clique com o botão direito na pasta de objetos do Microsoft Excel à esquerda e selecione Inserir => Módulo. Este é o lugar onde as macros são armazenadas. Para fazer uso da macro, você precisa salvar o documento Excel Como macro-habilitado. A partir do arquivo tab = > gravar como, escolha Gravar como um livro de trabalho com macro-ativado (o .agora, é hora de escrever sua primeira macro!

1. Copiar dados de um arquivo para outro.

macro muito útil, pois mostra como copiar uma série de dados de dentro da vba e como criar e nomear um novo livro de trabalho. Você pode facilmente atualizá-lo para atender às suas próprias necessidades:

 Sub CopyFiletoAnotherWorkbook() 'Copy the data Sheets("Example 1").Alcance ("B4: C15").Copiar 'criar um novo livro de trabalho.Adicionar 'colar a' ActiveSheet ' de dados.Colar ' desligar a aplicação de alertas de Aplicação.DisplayAlerts = False ' grava o ficheiro Novo. Muda o nome do directório. ActiveWorkbook.SaveAs Nome Do Ficheiro:="C:\Temp\MyNewBook.XLSX "' Turn application alertas de volta na aplicação.DisplayAlerts = TrueEnd Sub

2. Mostrar as linhas escondidas

ocasionalmente, grandes arquivos do Excel contêm linhas escondidas para maior clareza. Aqui está uma macro que irá desligar todas as linhas de uma folha de trabalho activa:

Sub colunas ShowHiddenRows ().Totalmente decoluna.Escondido = Linhas Falsas.EntireRow.Hidden = FalseEnd Sub

3. Apagar linhas e colunas vazias

linhas em branco no Excel são um problema com o processamento de dados. Aqui está como se livrar deles:

Sub DeleteEmptyRowsAndColumns() 'Declare suas variáveis. Dim MyRange como intervalo Dim iCounter desde o tamanho 'Define o intervalo alvo. Set MyRange = ActiveSheet.UsedRange ' iniciar looping reverso através do intervalo de linhas. Para iCounter = MyRange.Linhas.Conta até 1 Passo-1 ' Se a linha inteira estiver vazia, então apaga-a. Se Aplicação.CountA(linhas (iCounter).EntireRow) = 0 então linhas (iCounter).Eliminar 'Remover o comentário para Ver quais são as linhas vazias 'MsgBox "linha" & iCounter & "está vazio" End If 'Incrementar o contador de lado o iCounter" Passo 6: Comece a inverter o ciclo através do intervalo de Colunas. Para iCounter = MyRange.Coluna.Conta até 1 Passo-1 ' Passo 7: Se a coluna inteira estiver vazia, apaga-a. Se Aplicação.CountA (colunas (iCounter).EntireColumn) = 0 então colunas (iCounter).Apagar Fim Se " Passo 8: Aumente o contador para baixo Sub

4. Find a blank cell

 Sub FindEmptyCell() ActiveCell.Deslocamento (1, 0).Seleccione A Opção Fazer Enquanto Não Está Vazia(ActiveCell) ActiveCell.Deslocamento (1, 0).Seleccione LoopEnd Sub

Sub-documento de trabalho privado _beforedoubleclick (alvo ByVal como intervalo, Cancelar como booleano) 'declarar as suas variáveis Dim LastRow como longo' encontrar a última linha LastRow = células (linhas.Count, 1).Fim (xlUp).Linha ' Ordenar ascendente em linhas de coluna com duplo clique ("6:"& LastRow) .Sort _ Key1: = Cells (6, ActiveCell.Coluna), _ Order1 : = Xlascendingend Sub

Sub-TrimTheSpaces() 'Declare as suas variáveis Dim MyRange como intervalo 'Dim MyCell como intervalo' Salve o Livro de trabalho antes de mudar as células Seleccione o caso MsgBox("não pode desfazer esta acção. "& _ " Save Workbook First?", Vbyesnocancel) Case Is = vbies this workbook.Gravar a capitalização é = Vbcancel sair Sub-End seleccionar ' definir o intervalo de destino. Definir MyRange = Selection ' iniciar o looping através do intervalo. Para cada Micélula em MyRange, aparar os espaços. Se não for IsEmpty (MyCell) então MyCell = Trim(MyCell) termina se 'obter a célula seguinte no intervalo Sub

Sub HighlightDuplicates() 'Declare suas variáveis Dim MyRange como Range Dim MyCell como Range 'Defina o intervalo alvo. Definir MyRange = Selection ' iniciar o looping através do intervalo. Para cada Micélula em MyRange 'certifique-se de que a célula tem formatação de texto. Se Funcionar.CountIf(MyRange, MyCell.Value) > 1 Then MyCell.Interior.ColorIndex = 36 final se ' obter a célula seguinte no intervalo Sub

este código irá realçar os dez melhores valores a partir de uma selecção de células:

 Sub TopTen() selecção.Condições format.Selecção De AddTop10.Condições Format(Selecção.Condições format.Contar).Definir Primeiro Prioridade Com A Selecção.FormatConditions (1) .TopBottom = xlTop10Top ' muda o rank aqui para destacar um número diferente de valores .Rank = 10 .Percentagem = Fim Falso Com Selecção.FormatConditions (1).Fonte .Color = -16752384 .TintAndShade = 0 Terminar Com A Selecção.FormatConditions (1).Interior .PatternColorIndex = xlAutomatic .Cor = 13561798 .TintAndShade = 0 Terminar Com A Selecção.FormatConditions (1).StopIfTrue = FalseEnd Sub

pode ajustar facilmente o código para realçar diferentes números de valores.

10. Realce maior que os valores

quando executar este código, uma janela irá perguntar. Irá perguntar-lhe o valor que deseja comparar as células que seleccionou.

Sub HighlightGreaterThanValues() Dim i As Integer i = InputBox("Enter Greater Than Value", "Enter Value") Selection.Condições format.Apagar ' mudar o operador para xlLower para realçar a selecção de valores inferiores aos valores.Condições format.Add Type: = xlCellValue, Operator:=xlGreater, Formula1: = I Selection.Condições Format(Selecção.Condições format.Contar).Definir Primeiro Prioridade Com A Selecção.FormatConditions (1) .Fonte.Cor = RGB (0, 0, 0) .Interior.Color = RGB (31, 218, 154) End WithEnd Sub

pode ajustar este código para realçar valores mais baixos também.

uma macro simples que destaca todas as células que contêm comentários:

Sub HighlightCommentCells() selecção.Especialcells (especificações xlcellt).Seleccione A Selecção.Style = "Note" End Sub 

12. Realçar as células com palavras mal escritas

isto é extremamente útil quando você trabalha com funções que tomam strings, no entanto, alguém entrou na string com um erro e as suas fórmulas não estão a funcionar. Aqui está como corrigir esta questão:

 Sub ColorMispelledCells() para cada cl Na ActiveSheet.UsedRange Se Não Aplicação.CheckSpelling(Palavra:= cl.= = Ligações externas = = Interior.ColorIndex = 28 Próximo clEnd Sub

13. Criar uma tabela pivot

Aqui está como criar uma tabela pivot do MS Excel (versão 2007). Especialmente útil, quando você está fazendo um relatório personalizado todos os dias. Você pode otimizar a criação da tabela pivot da seguinte forma:

Sub Pivotableforexcel2007() Dim SourceRange As Range Set SourceRange = Sheets("Sheet1").Range ("A3: N86") ActiveWorkbook."PivotCaches".Create (_SourceType:=xlDatabase, _ SourceData:=SourceRange, _ Version:=xlPivotTableVersion12).CreatePivotTable _ TableDestination:="", _ nome de tabela:="", _ DefaultVersion: = xlPivotTableVersion12End Sub

14. Anexar o livro de trabalho activo num E-mail

o meu código VBA favorito. Permite-lhe anexar e enviar o ficheiro em que está a trabalhar com um endereço de E-mail predefinido, o título da mensagem e o corpo da mensagem! Você primeiro precisa definir referência ao Microsoft Outlook (em seu editior VBA, clique em ferramentas => referências e escolha Microsoft Outlook).

Sub SendFIleAsAttachment() 'Declare as suas variáveis 'Set reference to Microsoft Outlook Object library Dim OLApp As Outlook.Aplicação Dim OLMail como Objecto ' abrir o Outlook iniciar um novo conjunto de itens de correio OLApp = novo Outlook.Conjunto De Aplicações OLMail = OLApp.CreateItem (0) OLApp.Sessao.Registar ' compilar o seu item de correio e enviar com o OLMail .To = "[email protected]; [email protected]". CC ="".BCC ="".Assunto = "esta é a linha de assunto".Body = "Hi there" .Anexo.Adicionar ActiveWorkbook.Nome completo .Mostrar ' mudar para .Enviar para enviar sem revisão final com ' conjunto de limpeza de memória OLMail = nada conjunto OLApp = Sub

15. Enviar todos os gráficos do Excel para uma apresentação do PowerPoint

Uma aplicação muito útil macro que permite que você adicione os seus gráficos do Excel na sua apresentação do Powerpoint apenas com um único clique:

Sub SendExcelFiguresToPowerPoint() 'Set referência a Biblioteca de objetos Microsoft Powerpoint 'Declarar as variáveis Dim PP Como o PowerPoint.Aplicação Dim PPPres Como PowerPoint.Apresentação Dim PPSlide Como PowerPoint.Slide Dim i como número inteiro 'verificar para gráficos; sair se não existirem Folhas de gráficos ("Slide Data").Seleccione Se ActiveSheet.ChartObjects.Count < 1 Then MsgBox "No charts existing the active sheet" Exit Sub End If 'Open PowerPoint and create new presentation Set PP = New PowerPoint.Application Set PPPres = PP.Apresentacao.Adicionar PP.Visible = True ' iniciar o loop baseado na contagem de gráficos para i = 1 Para Ativesheet.ChartObjects.Count ' Copie o gráfico como uma imagem ActiveSheet.ChartObjects (i).Grafico.CopyPicture _ Tamanho:=xlScreen, formato: = Xlpicture Application.Espere (Agora + TimeValue ("0: 00:1") 'contar os' slides 'e adicionar um 'slide' novo como próximo número de 'slides' disponível = PPPres.Slide.Count Set PPSlide = PPPres.Slide.Add (SlideCount + 1, ppLayoutBlank) PPSlide.Seleccione ' colar a imagem e ajustar a sua posição; vá para a página seguinte do gráfico.Forma.Colar.Seleccionar PP.ActiveWindow.Seleccao.ShapeRange.Alinhar msoAlignCenters, True PP.ActiveWindow.Seleccao.ShapeRange.Align msoAlignMiddles, True Next I ' Memory Cleanup Set PPSlide = Nothing Set PPPres = Nothing Set PP = NothingEnd Sub 

16. Envie a tabela Excel em MS Word

as tabelas Excel são geralmente colocadas dentro de documentos de texto. Aqui está uma forma automatizada de exportar a sua tabela Excel para MS Word:

 Sub ExcelTableInWord() 'Set reference to Microsoft Word Object library 'Declare your variables Dim MyRange As Excel.Alcance Dim wd como palavra.Aplicação Dim wdDoc como palavra.Documentar O Dim WdRange Como Palavra.Gama " copiar as folhas de séries definidas ("quadro de receitas").Alcance ("B4: F10").Cop ' abrir o documento de palavras alvo definir wd = nova palavra.Conjunto de aplicações wdDoc = wd.Documento.Abrir _ (Este Livro De Trabalho.Caminho && "PasteTable.docx") wd.Visível = True ' definir o foco no marcador de destino definido WdRange = wdDoc.Bookmarks ("DataTableHere").O intervalo ' remove a tabela antiga e cola a nova no caso de erro, continue a próxima WdRange.Tables(1).Apagar WdRange.Colar o 'colar na tabela' ajustar as larguras de colunas do WdRange.Tables(1).Coluna.SetWidth _ (MyRange.Largura / Mirange.Coluna.Count), Wdadjustsamewidth ' Reinsert the bookmark wdDoc.Favorito.Add "DataTableHere", Wdrange ' Memory cleanup Set wd = Nothing Set wdDoc = Nothing Set WdRange = NothingEnd Sub

17. Extrair uma palavra específica de uma célula

podemos usar fórmulas se quisermos extrair um certo número de símbolos. Mas e se quisermos extrair apenas a segunda palavra de uma frase ou de uma série de palavras numa cela? Para isso, podemos criar uma função Excel personalizada com VBA. Esta é uma das funcionalidades VBA mais iportant, porque permite que você crie suas próprias funções que não existem no MS Excel. Vamos continuar e criar duas funções: findword() e findwordrev(). Aqui está o código vba para isto.:

Function FindWord(Source As String, Position As Integer) As String On Error Resume Next FindWord = Split(WorksheetFunction.Trim (Source),"") (Position - 1) On Error GoTo GoTo 0End FunctionFunction FindWordRev(Source As String, Position As Integer) As String Dim Arr () As String Arr = VBA.Split (Função Do Documento De Trabalho.Trim(Source), " ") On Error Resume Next FindWordRev = Arr(UBound (Arr) - Position + 1) On Error GoTo 0end Function

Very nice, we have created two cstom Excel functions. Agora, tente usá-los no Excel. A função = FindWordRev (A1,1) recebe a última palavra da célula A1. A função = FindWord (A1,3) leva a terceira palavra da célula A1, etc.

18. Proteja seu livro de trabalho

às vezes queremos proteger os dados em nosso arquivo de modo que só nós podemos alterá-lo. Aqui está como fazer isso com VBA:

Sub ProtectSheets() 'Declare suas variáveis Dim ws como planilha 'inicie looping através de todas as planilhas para cada ws no ActiveWorkbook.As folhas de cálculo 'proteger e circular para a próxima folha de cálculo ws.Proteger a senha: = "1234" próximo Sub

Parabéns! Uma vez que você ainda está lendo isso, você está realmente interessado em aprender VBA. Como você já viu por si mesmo, a linguagem de programação VBA é extremamente útil e pode nos poupar muito tempo. Espero que você tenha achado essa informação útil e usá-la para se tornar um mestre em MS Excel, VBA e software de computador em geral.