Usar macros do Excel pode acelerar o trabalho e economizar muito tempo.
Uma maneira de obter o código VBA é gravar a macro e obter o código que ela gera. No entanto, esse código do gravador de macro costuma estar cheio de códigos que não são realmente necessários. Além disso, o gravador de macro tem algumas limitações.
Portanto, vale a pena ter uma coleção de códigos de macro VBA úteis que você pode ter no bolso de trás e usá-los quando necessário.
Embora a escrita de um código de macro VBA do Excel possa levar algum tempo inicialmente, uma vez feito, você pode mantê-lo disponível como uma referência e usá-lo sempre que precisar.
Neste artigo enorme, vou listar alguns exemplos úteis de macro do Excel que eu preciso com frequência e mantenho escondidos em meu cofre particular.
Vou continuar atualizando este tutorial com mais exemplos de macro. Se você acha que algo deveria estar na lista, deixe um comentário.
Você pode marcar esta página para referência futura.
Agora, antes de entrar no exemplo de macro e fornecer o código VBA, vou primeiro mostrar como usar esses códigos de exemplo.
Usando o código de exemplos de macro do Excel
Aqui estão as etapas que você precisa seguir para usar o código de qualquer um dos exemplos:
- Abra a pasta de trabalho na qual deseja usar a macro.
- Segure a tecla ALT e pressione F11. Isso abre o Editor VB.
- Clique com o botão direito em qualquer um dos objetos no explorador de projetos.
- Vá para Inserir -> Módulo.
- Copie e cole o código na janela de código do módulo.
Caso o exemplo diga 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.
Depois de inserir o código em uma pasta de trabalho, você precisa salvá-lo com uma extensão .XLSM ou .XLS.
Como executar a macro
Depois de copiar o código no Editor VB, aqui estão as etapas para executar a macro:
- Vá para a guia Desenvolvedor.
- Clique em Macros.
- Na caixa de diálogo Macro, selecione a macro que deseja executar.
- Clique no botão Executar.
Caso você não consiga encontrar a guia do desenvolvedor na faixa de opções, leia este tutorial para aprender como obtê-la.
Tutorial relacionado: diferentes maneiras de executar uma macro no Excel.
Caso o código seja colado na janela de código da planilha, você não precisa se preocupar em executar o código. Ele será executado automaticamente quando a ação especificada ocorrer.
Agora, vamos entrar em exemplos úteis de macro que podem ajudar a automatizar o trabalho e economizar tempo.
Nota: você encontrará muitas instâncias de um apóstrofo (‘) seguido por uma linha ou duas. Esses são comentários que são ignorados durante a execução do código e são colocados como notas para o próprio / leitor.
Caso você encontre algum erro no artigo ou no código, por favor, seja incrível e me avise.
Exemplos de macro do Excel
Os exemplos de macro abaixo são abordados neste artigo:
Reexibir todas as planilhas de uma vez
Se você estiver trabalhando em uma pasta de trabalho que contém várias planilhas ocultas, será necessário reexibir essas planilhas uma por uma. Isso pode levar algum tempo, caso haja muitas folhas ocultas.
Aqui está o código que exibirá todas as planilhas na pasta de trabalho.
'Este código exibirá todas as planilhas na pasta de trabalho Sub UnhideAllWoksheets () Dim ws As planilha para cada ws em ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
O código acima usa um loop VBA (For Each) para percorrer cada planilha na pasta de trabalho. Em seguida, ele altera a propriedade visible da planilha para visível.
Aqui está um tutorial detalhado sobre como usar vários métodos para exibir planilhas no Excel.
Ocultar todas as planilhas exceto a planilha ativa
Se você estiver trabalhando em um relatório ou painel e quiser ocultar toda a planilha, exceto aquela que contém o relatório / painel, você pode usar este código de macro.
'Esta macro ocultará toda a planilha, exceto a planilha ativa Sub HideAllExceptActiveSheet () Dim ws As Planilha Para Cada ws Em ThisWorkbook.Worksheets If ws.Name ActiveSheet.Name Then ws.Visible = xlSheetHidden Next ws End Sub
Classificar planilhas em ordem alfabética usando VBA
Se você tem uma pasta de trabalho com muitas planilhas e deseja classificá-las em ordem alfabética, este código de macro pode ser muito útil. Esse pode ser o caso se você tiver nomes de planilhas como anos ou nomes de funcionários ou nomes de produtos.
'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) .Mover before: = Sheets (i) End If Next j Next i Application.ScreenUpdating = True End Sub
Proteja todas as planilhas de uma só vez
Se você tem muitas planilhas em uma pasta de trabalho e deseja proteger todas as planilhas, pode usar este código de macro.
Ele permite que você especifique a senha dentro do código. Você precisará dessa senha para desproteger a planilha.
'Este código protegerá todas as folhas de uma vez Sub ProtectAllSheets () Dim ws As Worksheet Dim password As String password = "Test123"' substitua Test123 pela senha desejada Para Cada ws Em Worksheets ws.Protect senha: = senha Próximo ws End Sub
Desproteja todas as planilhas de uma só vez
Se você tem algumas ou todas as planilhas protegidas, você pode apenas usar uma ligeira modificação do código usado para proteger as planilhas para desprotegê-lo.
'Este código protegerá todas as planilhas de uma vez Sub ProtectAllSheets () Dim ws As Planilha Dim password As String password = "Test123"' substitua Test123 pela senha desejada Para Cada ws Em Planilhas ws.Unprotect senha: = senha Próximo ws End Sub
Observe que a senha deve ser a mesma que foi usada para bloquear as planilhas. Se não estiver, 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 muito útil se você obtiver um 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
É uma prática comum mesclar células para torná-la uma. Enquanto faz o trabalho, quando as células são mescladas, você não poderá classificar os dados.
Caso você esteja trabalhando com uma planilha com células mescladas, use o código a seguir para desfazer a mesclagem de todas as células mescladas de uma vez.
'Este código irá desfazer a mesclagem de todas as células mescladas Sub UnmergeAllCells () ActiveSheet.Cells.UnMerge End Sub
Observe que, em vez de Merge and Center, eu recomendo usar a opção Center Across Selection.
Salvar pasta de trabalho com carimbo de data / hora em seu nome
Muito tempo, você pode precisar criar versões de seu trabalho. Eles são bastante úteis em projetos longos em que você trabalha com um arquivo ao longo do tempo.
Uma boa prática é salvar o arquivo com carimbos de data / hora.
O uso de carimbos de data / hora permitirá que você volte a um determinado arquivo para ver quais alterações foram feitas ou quais dados foram usados.
Aqui está o código que salvará automaticamente a pasta de trabalho na pasta especificada e adicionará um carimbo de data / hora sempre que for salva.
'Este código salvará o arquivo com um carimbo de data / hora em seu nome Sub SaveWorkbookWithTimeStamp () Dim timestamp As String timestamp = Format (Date, "dd-mm-yyyy") & "_" & Format (Time, "hh-ss") ThisWorkbook.SaveAs "C: UsersUsernameDesktopWorkbookName" & timestamp 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 usei. Você precisa especificar o local da pasta onde deseja salvar o arquivo. Além disso, usei um nome genérico “WorkbookName” como prefixo do nome do arquivo. Você pode especificar algo relacionado ao seu projeto ou empresa.
Salvar cada planilha como um PDF separado
Se você trabalha com dados de anos, divisões ou produtos diferentes, pode ter a necessidade de salvar planilhas diferentes como arquivos PDF.
Embora possa ser um processo demorado se feito manualmente, o VBA pode realmente acelerá-lo.
Aqui está um código VBA que salvará cada planilha como um PDF separado.
'Este código salvará cada planilha como um PDF Sub SaveWorkshetAsPDF () Dim ws As planilha para cada ws nas planilhas ws.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
No código acima, especifiquei o endereço do local da pasta na qual desejo salvar os PDFs. Além disso, cada PDF receberá o mesmo nome da planilha. Você terá que modificar o local desta pasta (a menos que seu nome também seja Sumit e você o esteja salvando em uma pasta de teste na área de trabalho).
Observe que este código funciona apenas para planilhas (e não para planilhas de gráfico).
Salvar cada planilha como um PDF separado
Aqui está o código que salvará sua pasta de trabalho inteira como um PDF na pasta especificada.
'Este código salvará toda a pasta de trabalho como PDF Sub SaveWorkshetAsPDF () ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C: UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Você terá que alterar o local da pasta para usar este código.
Converter todas as fórmulas em valores
Use este código quando você tiver uma planilha que contém muitas fórmulas e quiser convertê-las em valores.
'Este código irá converter todas as fórmulas em valores Sub ConvertToValues () With ActiveSheet.UsedRange .Value = .Value End With End Sub
Este código identifica automaticamente as células usadas e as converte em valores.
Proteger / bloquear células com fórmulas
Você pode querer bloquear células com fórmulas quando tiver muitos cálculos e não quiser excluí-los ou alterá-los acidentalmente.
Aqui está o código que bloqueará todas as células que possuem fórmulas, enquanto todas as outras células não serão bloqueadas.
'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.
Proteja todas as planilhas na pasta de trabalho
Use o código a seguir para proteger todas as planilhas em uma pasta de trabalho de uma vez.
'Este código protegerá todas as planilhas na pasta de trabalho Sub ProtectAllSheets () Dim ws As Planilha Para Cada ws Em Planilhas ws.Protect Next ws End Sub
Este código irá percorrer todas as planilhas uma por uma e protegê-las.
Caso queira desproteger todas as planilhas, use ws.Unprotect em vez de ws.Protect no código.
Insira uma linha após cada outra linha na 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 Próximo i End Sub
Da mesma forma, você pode modificar este código para inserir uma coluna em branco após cada coluna no intervalo selecionado.
Inserir data e carimbo de data / hora automaticamente na célula adjacente
Um carimbo de data / hora é algo que você usa quando deseja rastrear atividades.
Por exemplo, você pode querer rastrear atividades como quando uma determinada despesa foi incorrida, a que horas a fatura de venda foi criada, quando a entrada de dados foi feita em uma célula, quando o relatório foi atualizado pela última vez, etc.
Use este código para inserir um carimbo de data e hora na célula adjacente quando uma entrada é feita ou o conteúdo existente é editado.
'Este código irá inserir um carimbo de data / hora 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-aaaa hh: mm: ss") Application.EnableEvents = True End If Handler: End Sub
Observe que você precisa inserir este código na janela de código da planilha (e 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 folha na qual deseja esta funcionalidade. Em seguida, copie e cole este código na janela de código dessa folha.
Além disso, esse código é feito para funcionar quando a entrada de dados é feita na Coluna A (observe que o código tem a linha Destino.Coluna = 1). Você pode alterar isso de acordo.
Destacar linhas alternativas na seleção
Destacar linhas alternativas pode aumentar a legibilidade de seus dados tremendamente. Isso pode ser útil quando você precisa imprimir e examinar os dados.
Aqui está um código que destacará instantaneamente 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 = Seleção para cada Myrow em Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Observe que eu especifiquei a cor como vbCyan no código. Você também pode especificar outras cores (como vbRed, vbGreen, vbBlue).
Destacar células com palavras incorretas
O Excel não tem um corretor ortográfico como no Word ou PowerPoint. Embora você possa executar a verificação ortográfica pressionando a tecla F7, não há dica visual quando há um erro de grafia.
Use este código para destacar instantaneamente todas as células que contêm um erro de grafia.
'Este código irá destacar as células que têm palavras com erros ortográficos 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 End Sub
Observe que as células realçadas são aquelas que possuem texto que o Excel considera como um erro ortográfico. Em muitos casos, ele também destacaria nomes ou termos de marca que não entende.
Atualizar todas as tabelas dinâmicas na pasta de trabalho
Se você tiver mais de uma Tabela Dinâmica na pasta de trabalho, poderá usar esse código para atualizar todas essas tabelas Dinâmicas de uma vez.
'Este código irá atualizar toda a tabela dinâmica na pasta de trabalho Sub RefreshAllPivotTables () Dim PT como tabela dinâmica para cada PT em ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub
Você pode ler mais sobre como atualizar as tabelas dinâmicas aqui.
Alterar a caixa das letras das células selecionadas para maiúsculas
Embora o Excel tenha as fórmulas para alterar as maiúsculas e minúsculas do texto, ele faz isso em outro conjunto de células.
Use este código para alterar instantaneamente a caixa das letras do texto no texto selecionado.
'Este código mudará a seleção para maiúscula Sub ChangeCase () Dim Rng As Range para cada Rng em Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase (Rng.Value) End If Next Rng End Sub
Observe que, neste caso, usei UCase para tornar o texto maiúsculo. Você pode usar LCase para minúsculas.
Destacar todas as células com comentários
Use o código abaixo para destacar todas as células que contêm 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, usei vbBlue para dar uma cor azul às células. Você pode alterar para outras cores, se desejar.
Destacar células em branco com VBA
Embora você possa destacar uma célula em branco com formatação condicional ou usando a caixa de diálogo Ir para especial, se você tiver que fazer isso com frequência, é melhor usar uma macro.
Depois de criada, você pode ter essa macro na Barra de Ferramentas de Acesso Rápido ou salvá-la em sua pasta de trabalho pessoal de macros.
Aqui está o código de macro VBA:
'Este código irá destacar todas as células em branco no conjunto de dados Sub HighlightBlankCells () Dim Dataset as Range Set Dataset = Selection Dataset.SpecialCells (xlCellTypeBlanks) .Interior.Color = vbRed End Sub
Neste código, especifiquei as células em branco a serem destacadas na cor vermelha. Você pode escolher outras cores, como azul, amarelo, ciano, etc.
Como classificar dados por coluna única
Você pode usar o código a seguir para classificar os dados pela coluna especificada.
Sub SortDataHeader () Range ("DataRange"). Sort Key1: = Range ("A1"), Order1: = xlAscending, Header: = xlYes End Sub
Observe que criei um intervalo nomeado com o nome ‘DataRange’ e o usei em vez das referências de células.
Além disso, existem três parâmetros principais que são usados aqui:
- Key1 - é aquele em que 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.
- Ordem- 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 cabeçalhos 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 conforme mostrado abaixo:
Abaixo está o código que classificará os dados com base em várias colunas:
Sub SortMultipleColumns () With ActiveSheet.Sort .SortFields.Add Key: = Range ("A1"), Order: = xlAscending .SortFields.Add Key: = Range ("B1"), Order: = xlAscending .SetRange Range ("A1 : C13 ") .Header = xlSim. Aplicar End With End Sub
Observe que aqui eu especifiquei primeiro a classificação com base na coluna A e, em seguida, com base na coluna B.
A saída seria algo como mostrado abaixo:
Como obter apenas a parte numérica de uma string no Excel
Se você deseja extrair apenas a parte numérica ou apenas a parte do texto de uma string, pode criar uma função personalizada no VBA.
Você pode então usar esta função VBA na planilha (assim como funções regulares do Excel) e ela irá extrair apenas a parte numérica ou de texto da string.
Algo conforme mostrado abaixo:
Abaixo está o código VBA que criará uma função para extrair 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, em seguida, pode usar a função = GetNumeric na planilha.
Esta função terá apenas um argumento, que é a referência da célula da qual você deseja obter a parte numérica.
Da mesma forma, a seguir está a função que 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 dos códigos de macro úteis do Excel que você pode usar no seu trabalho diário para automatizar tarefas e ser muito mais produtivo.
