24 exemplos úteis de macros do Excel para iniciantes em VBA (prontos para uso)

O uso de macros do Excel pode acelerar seu trabalho e economizar muito tempo.

Uma maneira de obter o código VBA é gravar uma macro e obter o código que ela gera.No entanto, o código do gravador de macro geralmente está cheio de código que não é realmente necessário.O gravador de macro também tem algumas limitações.

Portanto, vale a pena ter um conjunto útil de macros VBA que você pode guardar no bolso de trás e usar quando precisar.

Embora possa levar algum tempo para codificar uma macro VBA do Excel inicialmente, quando terminar, você poderá usá-la como referência e usá-la na próxima vez que precisar.

Neste artigo enorme, listarei alguns exemplos úteis de macros do Excel que muitas vezes preciso e mantenho em meu cofre particular.

Continuarei a atualizar este tutorial com mais exemplos de macros.Por favor, deixe um comentário se você acha que algo deveria estar na lista.

Você pode marcar esta página para referência futura.

Agora, antes de entrar nos exemplos de macro e fornecer o código VBA, deixe-me mostrar primeiro como usar o código de exemplo.

Usando o código do exemplo de macro do Excel

Aqui estão as etapas a seguir para usar o código de qualquer um dos exemplos:

• Abra a pasta de trabalho na qual você deseja usar a macro.
• Mantenha pressionada a tecla ALT e pressione F11.Isso vai abrireditor VB.
• Clique com o botão direito do mouse em qualquer objeto no Project Explorer.
• Vá para Inserir -> Módulos.
• Copie e cole o código na janela de código do módulo.

Se o exemplo disser que você precisa colar o código na janela de código da planilha, clique duas vezes no objeto da planilha e copie e cole o código na janela de código.

Após inserir o código na pasta de trabalho, você precisa salvá-lo com a extensão .XLSM ou .XLS.

Como executar uma macro

Após copiar o código no editor VB, as etapas para executar a macro são as seguintes:

• Vá para a guia do desenvolvedor.
• Clique em Macro.

• Na caixa de diálogo Macros, selecione a macro a ser executada.
• Clique no botão Executar.

Se você não conseguir encontrar a guia do desenvolvedor na faixa de opções, leia este tutorial para saber como obtê-la.

Se o código for colado na janela de código da planilha, você não precisa se preocupar em executar o código.Ele é executado automaticamente quando a ação especificada ocorre.

Agora, vamos entrar em exemplos de macro úteis que podem ajudá-lo a automatizar seu trabalho e economizar tempo.

Nota: Você encontrará muitos apóstrofos (') seguidos por uma ou duas linhas.Esses são comentários que são ignorados ao executar o código e colocados como comentários do próprio/leitor.

Se você encontrar algum erro no artigo ou código, por favor me avise.

Exemplo de macro do Excel

Este artigo descreve os seguintes exemplos de macro:

Reexibir todas as planilhas de uma vez

Se você estiver trabalhando em uma pasta de trabalho com várias planilhas ocultas, precisará reexibir essas planilhas uma a uma.Isso pode levar algum tempo se houver muitas planilhas ocultas.

Aqui está o código para reexibir todas as planilhas na pasta de trabalho.

'Este código mostrará todas as planilhas da pasta de trabalho Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook. Worksheets ws. Visible = xlSheetVisible Next ws End Sub

O código acima usa um loop VBA (para cada) para percorrer cada planilha na pasta de trabalho.Em seguida, ele altera a propriedade visible da planilha para visible.

Aqui está um tutorial detalhado sobre como exibir uma planilha no Excel usando vários métodos.

Ocultar todas as planilhas, exceto a planilha ativa

Esse código de macro pode ser usado se você estiver trabalhando em um relatório ou painel e quiser ocultar todas as planilhas, exceto a planilha que contém o relatório/painel.

'Esta macro irá esconder toda a planilha exceto a planilha ativa Sub HideAllExceptActiveSheet() Dim ws As Worksheet For Each ws In ThisWorkbook.Worksheets If ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub

Classifique a planilha em ordem alfabética usando o VBA

Esse código de macro pode ser útil se você tiver uma pasta de trabalho com muitas planilhas e quiser classificar essas planilhas em ordem alfabética.Isso pode acontecer se você tiver o nome da planilha como o ano ou nome do funcionário ou nome do produto.

'Este código classificará as planilhas em ordem alfabética Sub SortSheetsTabName() Application.ScreenUpdating = False Dim ShCount As Integer, i As Integer, j As Integer ShCount = Sheets.Count For i = 1 To ShCount - 1 For j = i + 1 To ShCount If Sheets(j).Name < Sheets(i).Name Then Sheets(j).Move before:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub

Proteja todas as planilhas de uma só vez

Se você tiver muitas planilhas em sua pasta de trabalho e quiser proteger todas as planilhas, poderá usar esse código de macro.

Ele permite que você especifique uma senha no código.Você precisará dessa senha para desproteger a planilha.

'Este código protegerá todas as planilhas de uma vez Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'substitua Test123 pela senha que você deseja For Each ws In Worksheets ws.Protect password:=password Next ws Finalizar Sub

Desproteja todas as planilhas de uma só vez

Se você protegeu algumas ou todas as suas planilhas, você pode desprotegê-las com apenas uma pequena modificação no código usado para proteger as planilhas.

'Este código protegerá todas as planilhas de uma vez Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'substitua Test123 pela senha que você deseja For Each ws In Worksheets ws.Unprotect password:=password Next ws Finalizar Sub

Observe que a senha precisa ser a mesma senha usada para bloquear a planilha.Caso contrário, você verá um erro.

Reexibir todas as linhas e colunas

Este código de macro exibirá todas as linhas e colunas ocultas.

Isso pode ser útil se você estiver pegando o arquivo de outra pessoa e quiser ter certeza de que não há linhas/colunas ocultas.

'Este código irá mostrar todas as linhas e colunas na planilha Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub

Desfazer a mesclagem de todas as células mescladas

Mesclar células em uma é uma prática comum.Enquanto funciona, você não poderá classificar os dados quando as células forem mescladas.

Se você estiver usando uma planilha com células mescladas, use o código abaixo para desfazer a mesclagem de todas as células mescladas de uma só vez.

'Este código irá unmerge todas as células mescladas Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Observe que eu recomendo usar a opção "Center Across Selection" em vez da opção "Merge and Center".

Salvar pasta de trabalho com carimbo de data/hora no nome

Há muitas vezes em que você pode precisar criar uma versão de trabalho.Eles são ótimos para projetos de longo prazo que lidam com arquivos ao longo do tempo.

Uma boa prática é salvar o arquivo com um carimbo de data/hora.

Usar um carimbo de data/hora permitirá que você volte a um arquivo para ver quais alterações foram feitas ou quais dados foram usados.

Aqui está o código que salva automaticamente a pasta de trabalho na pasta especificada e adiciona um carimbo de data/hora ao salvar.

'Este código salvará o arquivo com um timestamp em seu nome Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" e carimbo de data/hora End Sub

Você precisa especificar o local da pasta e o nome do arquivo.

No código acima, "C:UsersUsernameDesktop é o local da pasta que eu uso.Você precisa especificar o local da pasta onde deseja salvar o arquivo.Além disso, usei o nome genérico "WorkbookName" como prefixo do nome do arquivo.Você pode especificar o conteúdo relacionado ao seu projeto ou empresa.

Salve cada planilha como um PDF separado

Se você usar dados de anos, departamentos ou produtos diferentes, talvez seja necessário salvar planilhas diferentes como arquivos PDF.

Embora isso possa ser um processo demorado se feito manualmente, o VBA acelera as coisas.

Aqui está um código VBA que salva cada planilha como um PDF separado.

'Este código salvará cada planilha como um PDF separado Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub

No código acima, especifiquei o endereço do local da pasta onde quero salvar o PDF.Além disso, cada PDF terá o mesmo nome da planilha.Você terá que modificar o local desta pasta (a menos que seu nome também seja Sumit e você a tenha salvo em uma pasta de teste em sua área de trabalho).

Observe que esse código só funciona para planilhas (não para planilhas de gráfico).

Salve cada planilha como um PDF separado

Aqui está o código para salvar a pasta de trabalho inteira como PDF na pasta especificada.

'Este código salvará a pasta de trabalho inteira como PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub

Você deve alterar o local da pasta para usar esse código.

Converter todas as fórmulas em valores

Use este código quando tiver uma planilha com muitas fórmulas e quiser converter essas fórmulas em valores.

'Este código irá converter todas as fórmulas em valores Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Esse código reconhece automaticamente a célula usada e a converte em um valor.

Proteger/bloquear células com fórmulas

Quando você tem muitos cálculos e não deseja excluí-los ou alterá-los acidentalmente, convém bloquear as células com fórmulas.

Aqui está o código que bloqueará todas as células com fórmulas e deixará todas as outras células desbloqueadas.

'Este código de macro irá bloquear todas as células com fórmulas Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub

Tutorial relacionado: Como bloquear células no Excel.

Proteger todas as planilhas em uma pasta de trabalho

Use o código a seguir para proteger todas as planilhas de uma pasta de trabalho de uma só vez.

'Este código protegerá todas as planilhas da pasta de trabalho Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws. Protect Next ws End Sub

Este código passará por todas as planilhas uma a uma e as protegerá.

Se você deseja desproteger todas as planilhas, use ws.Unprotect em vez de ws.Protect em seu código.

Inserir uma linha após cada linha da seleção

Use este código quando quiser inserir uma linha em branco após cada linha no intervalo selecionado.

'Este código irá inserir uma linha após cada linha na seleção Sub InsertAlternateRows() Dim rng As Range Dim CountRow As Integer Dim i As Integer Set rng = Selection CountRow = rng.EntireRow.Count For i = 1 To CountRow ActiveCell.EntireRow. Insira ActiveCell.Offset(2, 0). Selecione Next i End Sub

Novamente, você pode modificar esse código para inserir uma coluna em branco após cada coluna no intervalo selecionado.

Insira automaticamente a data e o carimbo de data/hora em células adjacentes

Os carimbos de data/hora são o que você usa quando deseja rastrear a atividade.

Por exemplo, você pode querer rastrear atividades como quando uma determinada despesa foi incorrida, quando uma fatura de vendas foi criada, quando os dados foram inseridos em uma célula, quando um relatório foi atualizado pela última vez e assim por diante.

Use este código para inserir um carimbo de data e hora nas células adjacentes ao digitar ou editar o conteúdo existente.

'Este código irá inserir um timestamp na célula adjacente Private Sub Worksheet_Change(ByVal Target As Range) On Error GoTo Handler If Target.Column = 1 And Target.Value <> "" Then Application.EnableEvents = False Target.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss") Application.EnableEvents = True End If Handler: End Sub

Observe que você precisa inserir esse código na janela de código da planilha (não na janela de código do módulo, como fizemos em outros exemplos de macro do Excel até agora).Para fazer isso, no editor VB, clique duas vezes no nome da planilha para a qual você deseja essa funcionalidade.Em seguida, copie e cole esse código na janela de código dessa planilha.

Além disso, esse código funciona quando a entrada de dados é feita na coluna A (observe que o código tem uma linha Target.Column = 1).Você pode alterá-lo de acordo.

Realçar linhas alternativas na seleção

Destacar linhas alternadas pode melhorar muito a legibilidade de seus dados.Isso pode ser útil quando você precisa imprimi-lo e navegar pelos dados.

Aqui está um código que irá destacar imediatamente as linhas alternativas na seleção.

'Este código destacaria linhas alternativas na seleção Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selection For Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Se Próxima Myrow End Sub

Observe que especifiquei a cor como vbCyan no código.Você também pode especificar outras cores (por exemplo, vbRed, vbGreen, vbBlue).

Destacar células com erros ortográficos

O Excel não tem a verificação ortográfica no Word ou PowerPoint.Embora você possa executar a verificação ortográfica pressionando F7, não há indicação visual para erros de ortografia.

Use este código para destacar instantaneamente todas as células com erros ortográficos.

'Este código irá realçar as células que têm palavras incorretas Sub HighlightMisspelledCells() Dim cl As Range For Each cl In ActiveSheet.UsedRange If Not Application.CheckSpelling(word:=cl.Text) Then cl.Interior.Color = vbRed End If Next cl Fim Sub

Observe que as células destacadas são as células com texto que o Excel acha que está incorreto.Em muitos casos, também destaca nomes ou termos de marca que não entende.

Atualizar todas as tabelas dinâmicas em uma pasta de trabalho

Se você tiver várias tabelas dinâmicas na pasta de trabalho, poderá usar esse código para atualizar todas elas de uma vez.

'Este código irá atualizar toda a Tabela Dinâmica na Pasta de Trabalho Sub RefreshAllPivotTables() Dim PT As PivotTable For Each PT In ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub

Você pode ler mais sobre como atualizar tabelas dinâmicas aqui.

Altere as letras maiúsculas das células selecionadas para maiúsculas

Embora o Excel tenha fórmulas para alterar as maiúsculas e minúsculas das letras do texto, ele permite que você faça isso em outro conjunto de células.

Use este código para alterar instantaneamente as letras maiúsculas do texto no texto selecionado.

'Este código mudará a Seleção para Maiúsculas Sub ChangeCase() Dim Rng As Range For Each Rng In Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub

Observe que, neste caso, uso o UCase para definir as maiúsculas do texto.Você pode usar LCase para letras minúsculas.

Realçar todas as células com comentários

Use o código a seguir para destacar todas as células com comentários.

'Este código irá destacar as células que possuem comentários` Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

Neste caso, eu uso vbBlue para dar à célula uma cor azul.Você pode alterá-lo para outra cor, se necessário.

Realce células em branco com VBA

Embora você possa usar a formatação condicional ou usar a caixa de diálogo Ir para especial para destacar células em branco, se precisar fazer isso com frequência, é melhor usar uma macro.

Uma vez criada, você pode ter essa macro na Barra de Ferramentas de Acesso Rápido ou salvá-la em sua pasta de trabalho de macro pessoal.

Aqui está o código da macro VBA:

'Este código irá destacar todas as células em branco no dataset Sub HighlightBlankCells() Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

Neste código, especifiquei células em branco destacadas em vermelho.Você pode escolher outras cores como azul, amarelo, ciano, etc.

Como classificar dados por uma única coluna

Você pode usar o código a seguir para classificar dados por uma coluna especificada.

Sub SortDataHeader() 
Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes 
End Sub

Observe que criei um intervalo nomeado chamado "DataRange" e o usei no lugar da referência de célula.

Há também três parâmetros principais usados ​​aqui:

• Key1 – Esta é a chave pela qual você deseja classificar o conjunto de dados.No código de exemplo acima, os dados serão classificados com base nos valores da coluna A.
• Order1 – Aqui você precisa especificar se deseja classificar os dados em ordem crescente ou decrescente.
• Cabeçalho – Aqui você precisa especificar se seus dados têm um cabeçalho ou não.

Leia mais sobre como classificar dados no Excel usando VBA.

Como classificar dados por várias colunas

Suponha que você tenha um conjunto de dados parecido com este:

Aqui está o código para classificar os dados com base em várias colunas:

Sub SortMultipleColumns() Com ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Header = xlYes .Apply End With End Sub

Observe que aqui eu especifiquei para classificar na coluna A primeiro e depois na coluna B.

A saída ficará assim:

Como obter apenas a parte numérica de uma string no Excel

Se você deseja apenas extrair a parte numérica ou a parte de texto da string, você pode criar uma função personalizada no VBA.

Você pode usar essa função VBA em sua planilha (assim como uma função normal do Excel) e ela extrairá apenas o número ou a parte do texto da string.

Como mostrado abaixo:

Aqui está o código VBA que irá criar uma função que extrai a parte numérica de uma string:

'Este código VBA criará uma função para obter a parte numérica de uma string Function GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Then Result = Result & Mid(CellRef, i, 1) Next i GetNumeric = Result End Function

Você precisa colocar o código em um módulo e então pode usar a função =GetNumeric na planilha.

Esta função terá apenas um parâmetro, a referência de célula da célula da qual obterá a parte numérica.

Da mesma forma, a função abaixo obterá apenas a parte do texto de uma string no Excel:

'Este código VBA criará uma função para obter a parte do texto de uma string Function GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1) Next i GetText = Result End Function

Portanto, esses são alguns códigos de macros úteis do Excel que você pode usar em seu trabalho diário para automatizar tarefas e aumentar sua produtividade.