L'utilizzo delle macro di Excel può velocizzare il lavoro e farti risparmiare molto tempo.
Un modo per ottenere il codice VBA è registrare la macro e prendere il codice che genera. Tuttavia, quel codice del registratore di macro è spesso pieno di codice che non è realmente necessario. Anche il registratore di macro ha alcune limitazioni.
Quindi vale la pena avere una raccolta di utili codici macro VBA che puoi avere nella tasca posteriore e usarla quando necessario.
Mentre la scrittura di un codice macro VBA di Excel potrebbe richiedere del tempo inizialmente, una volta terminato, puoi tenerlo disponibile come riferimento e utilizzarlo ogni volta che ne avrai bisogno.
In questo enorme articolo, elencherò alcuni utili esempi di macro di Excel di cui ho bisogno spesso e che tengo nascosti nel mio caveau privato.
Continuerò ad aggiornare questo tutorial con altri esempi di macro. Se pensi che qualcosa dovrebbe essere nella lista, lascia un commento.
Puoi aggiungere questa pagina ai segnalibri per riferimento futuro.
Ora, prima di entrare nell'esempio macro e fornirti il codice VBA, lascia che ti mostri prima come utilizzare questi codici di esempio.
Utilizzo del codice da esempi di macro di Excel
Ecco i passaggi che devi seguire per utilizzare il codice di uno qualsiasi degli esempi:
- Apri la cartella di lavoro in cui desideri utilizzare la macro.
- Tieni premuto il tasto ALT e premi F11. Questo apre l'editor VB.
- Fare clic con il pulsante destro del mouse su uno qualsiasi degli oggetti nell'esploratore del progetto.
- Vai su Inserisci -> Modulo.
- Copia e incolla il codice nella finestra del codice del modulo.
Nel caso in cui l'esempio indichi che è necessario incollare il codice nella finestra del codice del foglio di lavoro, fare doppio clic sull'oggetto del foglio di lavoro e copiare e incollare il codice nella finestra del codice.
Una volta inserito il codice in una cartella di lavoro, è necessario salvarlo con estensione .XLSM o .XLS.
Come eseguire la macro
Dopo aver copiato il codice nell'editor VB, ecco i passaggi per eseguire la macro:
- Vai alla scheda Sviluppatore.
- Fare clic su Macro.
- Nella finestra di dialogo Macro selezionare la macro che si desidera eseguire.
- Fare clic sul pulsante Esegui.
Nel caso in cui non riesci a trovare la scheda sviluppatore nella barra multifunzione, leggi questo tutorial per sapere come ottenerlo.
Esercitazione correlata: diversi modi per eseguire una macro in Excel.
Nel caso in cui il codice venga incollato nella finestra del codice del foglio di lavoro, non devi preoccuparti di eseguire il codice. Verrà eseguito automaticamente quando si verifica l'azione specificata.
Ora, entriamo negli utili esempi di macro che possono aiutarti ad automatizzare il lavoro e risparmiare tempo.
Nota: troverai molte istanze di un apostrofo (') seguito da una o due righe. Questi sono commenti che vengono ignorati durante l'esecuzione del codice e vengono inseriti come note per l'auto/lettore.
Nel caso in cui trovi qualche errore nell'articolo o nel codice, per favore sii fantastico e fammi sapere.
Esempi di macro di Excel
Di seguito gli esempi di macro sono trattati in questo articolo:
Scopri tutti i fogli di lavoro in una volta sola
Se stai lavorando in una cartella di lavoro che ha più fogli nascosti, devi mostrare questi fogli uno per uno. Questo potrebbe richiedere del tempo nel caso in cui ci siano molti fogli nascosti.
Ecco il codice che mostrerà tutti i fogli di lavoro nella cartella di lavoro.
'Questo codice mostrerà tutti i fogli nella cartella di lavoro Sub UnhideAllWoksheets() Dim ws As Worksheet For Each ws In ActiveWorkbook.Worksheets ws.Visible = xlSheetVisible Next ws End Sub
Il codice sopra utilizza un ciclo VBA (For Each) per esaminare ciascun foglio di lavoro nella cartella di lavoro. Quindi modifica la proprietà visibile del foglio di lavoro in visibile.
Ecco un tutorial dettagliato su come utilizzare vari metodi per mostrare i fogli in Excel.
Nascondi tutti i fogli di lavoro tranne il foglio attivo
Se stai lavorando su un report o un dashboard e vuoi nascondere tutto il foglio di lavoro tranne quello che ha il report/dashboard, puoi utilizzare questo codice macro.
'Questa macro nasconderà tutto il foglio di lavoro tranne il foglio attivo 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
Ordina i fogli di lavoro in ordine alfabetico utilizzando VBA
Se hai una cartella di lavoro con molti fogli di lavoro e vuoi ordinarli in ordine alfabetico, questo codice macro può tornare molto utile. Questo potrebbe essere il caso se hai nomi di fogli come anni o nomi di dipendenti o nomi di prodotti.
'Questo codice ordinerà i fogli di lavoro in ordine alfabetico 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).Sposta prima:=Sheets(i) End If Next j Next i Application.ScreenUpdating = True End Sub
Proteggi tutti i fogli di lavoro in una volta sola
Se hai molti fogli di lavoro in una cartella di lavoro e vuoi proteggere tutti i fogli, puoi usare questo codice macro.
Consente di specificare la password all'interno del codice. Avrai bisogno di questa password per rimuovere la protezione del foglio di lavoro.
'Questo codice proteggerà tutti i fogli in una volta sola Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'sostituisci Test123 con la password che desideri Per ogni ws In Worksheets ws.Protect password:=password Next ws Fine sotto
Rimuovere la protezione di tutti i fogli di lavoro in una volta sola
Se hai protetto alcuni o tutti i fogli di lavoro, puoi semplicemente utilizzare una leggera modifica del codice utilizzato per proteggere i fogli per rimuoverne la protezione.
'Questo codice proteggerà tutti i fogli in una volta sola Sub ProtectAllSheets() Dim ws As Worksheet Dim password As String password = "Test123" 'sostituisci Test123 con la password che desideri Per ogni ws In Worksheets ws.Unprotect password:=password Next ws Fine sottotitolo
Si noti che la password deve essere la stessa utilizzata per bloccare i fogli di lavoro. In caso contrario, vedrai un errore.
Scopri tutte le righe e le colonne
Questo codice macro mostrerà tutte le righe e le colonne nascoste.
Questo potrebbe essere davvero utile se ricevi un file da qualcun altro e vuoi essere sicuro che non ci siano righe/colonne nascoste.
'Questo codice mostrerà tutte le righe e le colonne nel foglio di lavoro Sub UnhideRowsColumns() Columns.EntireColumn.Hidden = False Rows.EntireRow.Hidden = False End Sub
Dividi tutte le celle unite
È una pratica comune unire le celle per farne una. Mentre fa il lavoro, quando le celle vengono unite non sarai in grado di ordinare i dati.
Se stai lavorando con un foglio di lavoro con celle unite, usa il codice seguente per separare tutte le celle unite in una volta sola.
'Questo codice separerà tutte le celle unite Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub
Si noti che invece di Unisci e centra, consiglio di utilizzare l'opzione Centra sulla selezione.
Salva cartella di lavoro con TimeStamp nel suo nome
Molto tempo, potrebbe essere necessario creare versioni del tuo lavoro. Questi sono molto utili in progetti lunghi in cui lavori con un file nel tempo.
Una buona pratica è salvare il file con i timestamp.
L'utilizzo dei timestamp ti consentirà di tornare a un determinato file per vedere quali modifiche sono state apportate o quali dati sono stati utilizzati.
Ecco il codice che salverà automaticamente la cartella di lavoro nella cartella specificata e aggiungerà un timestamp ogni volta che viene salvato.
'Questo codice salverà il file con un timestamp nel suo nome Sub SaveWorkbookWithTimeStamp() Dim timestamp As String timestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss") ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestamp End Sub
È necessario specificare la posizione della cartella e il nome del file.
Nel codice sopra, "C: UsersUsernameDesktop è la posizione della cartella che ho usato. È necessario specificare la posizione della cartella in cui si desidera salvare il file. Inoltre, ho usato un nome generico "WorkbookName" come prefisso del nome del file. Puoi specificare qualcosa relativo al tuo progetto o azienda.
Salva ogni foglio di lavoro come PDF separato
Se lavori con dati per anni, divisioni o prodotti diversi, potresti dover salvare fogli di lavoro diversi come file PDF.
Anche se potrebbe essere un processo che richiede tempo se eseguito manualmente, VBA può davvero velocizzarlo.
Ecco un codice VBA che salverà ogni foglio di lavoro come PDF separato.
'Questo codice salverà ogni foglio di lavoro come separato PDF Sub SaveWorkshetAsPDF() Dim ws As Worksheet For Each ws In Worksheets ws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf" Next ws End Sub
Nel codice sopra, ho specificato l'indirizzo della posizione della cartella in cui voglio salvare i PDF. Inoltre, ogni PDF avrà lo stesso nome del foglio di lavoro. Dovrai modificare questa posizione della cartella (a meno che il tuo nome non sia anche Sumit e lo stai salvando in una cartella di prova sul desktop).
Nota che questo codice funziona solo per i fogli di lavoro (e non per i fogli grafici).
Salva ogni foglio di lavoro come PDF separato
Ecco il codice che salverà l'intera cartella di lavoro come PDF nella cartella specificata.
'Questo codice salverà l'intera cartella di lavoro come PDF Sub SaveWorkshetAsPDF() ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf" End Sub
Dovrai cambiare la posizione della cartella per utilizzare questo codice.
Converti tutte le formule in valori
Usa questo codice quando hai un foglio di lavoro che contiene molte formule e vuoi convertire queste formule in valori.
'Questo codice convertirà tutte le formule in valori Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub
Questo codice identifica automaticamente le celle utilizzate e le converte in valori.
Proteggi/blocca le celle con le formule
Potresti voler bloccare le celle con le formule quando hai molti calcoli e non vuoi eliminarlo o modificarlo accidentalmente.
Ecco il codice che bloccherà tutte le celle che hanno formule, mentre tutte le altre celle non sono bloccate.
'Questo codice macro bloccherà tutte le celle con le formule Sub LockCellsWithFormulas() With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End With End Sub
Esercitazione correlata: Come bloccare le celle in Excel.
Proteggi tutti i fogli di lavoro nella cartella di lavoro
Usa il codice seguente per proteggere tutti i fogli di lavoro in una cartella di lavoro in una volta sola.
'Questo codice proteggerà tutti i fogli nella cartella di lavoro Sub ProtectAllSheets() Dim ws As Worksheet For Each ws In Worksheets ws.Protect Next ws End Sub
Questo codice esaminerà tutti i fogli di lavoro uno per uno e lo proteggerà.
Nel caso in cui desideri rimuovere la protezione di tutti i fogli di lavoro, usa ws.Unprotect invece di ws.Protect nel codice.
Inserisci una riga dopo ogni altra riga nella selezione
Utilizzare questo codice quando si desidera inserire una riga vuota dopo ogni riga nell'intervallo selezionato.
'Questo codice inserirà una riga dopo ogni riga nella selezione 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. Inserisci ActiveCell.Offset(2, 0).Seleziona Next i End Sub
Allo stesso modo, puoi modificare questo codice per inserire una colonna vuota dopo ogni colonna nell'intervallo selezionato.
Inserisci automaticamente data e timestamp nella cella adiacente
Un timestamp è qualcosa che usi quando vuoi tenere traccia delle attività.
Ad esempio, potresti voler tenere traccia di attività come quando è stata sostenuta una determinata spesa, a che ora è stata creata la fattura di vendita, quando è stata eseguita l'immissione dei dati in una cella, quando è stato aggiornato l'ultimo report, ecc.
Utilizzare questo codice per inserire una data e un'ora nella cella adiacente quando viene effettuata una voce o vengono modificati i contenuti esistenti.
'Questo codice inserirà un timestamp nella cella adiacente 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
Si noti che è necessario inserire questo codice nella finestra del codice del foglio di lavoro (e non nella finestra del codice del modulo come abbiamo fatto finora in altri esempi di macro di Excel). Per fare ciò, nell'editor VB, fare doppio clic sul nome del foglio su cui si desidera questa funzionalità. Quindi copia e incolla questo codice nella finestra del codice di quel foglio.
Inoltre, questo codice funziona quando l'immissione dei dati viene eseguita nella colonna A (si noti che il codice ha la riga Target.Column = 1). Puoi cambiarlo di conseguenza.
Evidenzia righe alternative nella selezione
L'evidenziazione di righe alternative può aumentare enormemente la leggibilità dei dati. Questo può essere utile quando è necessario stampare e esaminare i dati.
Ecco un codice che evidenzierà immediatamente le righe alternate nella selezione.
'Questo codice evidenzierebbe le righe alternate nella selezione 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 If Next Myrow End Sub
Nota che ho specificato il colore come vbCyan nel codice. È possibile specificare anche altri colori (come vbRed, vbGreen, vbBlue).
Evidenzia le celle con parole errate
Excel non ha un controllo ortografico come in Word o PowerPoint. Sebbene sia possibile eseguire il controllo ortografico premendo il tasto F7, non è presente alcun segnale visivo quando si verifica un errore di ortografia.
Usa questo codice per evidenziare immediatamente tutte le celle che contengono un errore di ortografia.
'Questo codice evidenzierà le celle con parole errate 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
Si noti che le celle evidenziate sono quelle che contengono testo che Excel considera come un errore di ortografia. In molti casi, evidenzierebbe anche nomi o termini di marca che non comprende.
Aggiorna tutte le tabelle pivot nella cartella di lavoro
Se hai più di una tabella pivot nella cartella di lavoro, puoi utilizzare questo codice per aggiornare tutte queste tabelle pivot contemporaneamente.
'Questo codice aggiornerà tutta la tabella pivot nella cartella di lavoro Sub RefreshAllPivotTables() Dim PT come tabella pivot per ogni PT in ActiveSheet.PivotTables PT.RefreshTable Next PT End Sub
Puoi leggere di più sull'aggiornamento delle tabelle pivot qui.
Cambia la lettera maiuscola delle celle selezionate in maiuscolo
Mentre Excel ha le formule per cambiare la lettera maiuscola del testo, ti fa farlo in un altro set di celle.
Usa questo codice per cambiare istantaneamente la maiuscola del testo nel testo selezionato.
'Questo codice cambierà la selezione in maiuscolo Sub ChangeCase() Dim Rng As Range per ogni Rng in Selection.Cells If Rng.HasFormula = False Then Rng.Value = UCase(Rng.Value) End If Next Rng End Sub
Nota che in questo caso, ho usato UCase per creare il caso di testo Upper. Puoi usare LCase per le minuscole.
Evidenzia tutte le celle con commenti
Usa il codice seguente per evidenziare tutte le celle che contengono commenti.
'Questo codice evidenzierà le celle con commenti' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub
In questo caso, ho usato vbBlue per dare un colore blu alle celle. Puoi cambiarlo in altri colori se vuoi.
Evidenzia le celle vuote con VBA
Mentre puoi evidenziare la cella vuota con la formattazione condizionale o utilizzando la finestra di dialogo Vai a speciale, se devi farlo abbastanza spesso, è meglio usare una macro.
Una volta creata, puoi avere questa macro nella barra di accesso rapido o salvarla nella tua cartella di lavoro macro personale.
Ecco il codice della macro VBA:
'Questo codice evidenzierà tutte le celle vuote nel set di dati Sub HighlightBlankCells() Dim Dataset come Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub
In questo codice, ho specificato le celle vuote da evidenziare nel colore rosso. Puoi scegliere altri colori come blu, giallo, ciano, ecc.
Come ordinare i dati per singola colonna
È possibile utilizzare il codice seguente per ordinare i dati in base alla colonna specificata.
Sub SortDataHeader() Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYes End Sub
Nota che ho creato un intervallo denominato con il nome "DataRange" e l'ho usato al posto dei riferimenti di cella.
Inoltre ci sono tre parametri chiave che vengono utilizzati qui:
- Key1 - Questo è il su cui si desidera ordinare il set di dati. Nel codice di esempio sopra, i dati verranno ordinati in base ai valori nella colonna A.
- Ordine: qui è necessario specificare se si desidera ordinare i dati in ordine crescente o decrescente.
- Intestazione - Qui devi specificare se i tuoi dati hanno o meno intestazioni.
Maggiori informazioni su come ordinare i dati in Excel utilizzando VBA.
Come ordinare i dati per più colonne
Supponiamo di avere un set di dati come mostrato di seguito:
Di seguito è riportato il codice che ordinerà i dati in base a più colonne:
Sub SortMultipleColumns() With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1 :C13") .Intestazione = xlSì .Applica Fine con End Sub
Nota che qui ho specificato di ordinare prima in base alla colonna A e poi in base alla colonna B.
L'output sarebbe qualcosa come mostrato di seguito:
Come ottenere solo la parte numerica da una stringa in Excel
Se vuoi estrarre solo la parte numerica o solo la parte di testo da una stringa, puoi creare una funzione personalizzata in VBA.
È quindi possibile utilizzare questa funzione VBA nel foglio di lavoro (proprio come le normali funzioni di Excel) ed estrarrà solo la parte numerica o di testo dalla stringa.
Qualcosa come mostrato di seguito:
Di seguito è riportato il codice VBA che creerà una funzione per estrarre la parte numerica da una stringa:
'Questo codice VBA creerà una funzione per ottenere la parte numerica da una stringa Funzione GetNumeric(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If IsNumeric(Mid(CellRef, i, 1) ) Quindi Risultato = Risultato e Metà (RifCell, i, 1) Avanti i Ottieni Numerico = Funzione Fine Risultato
È necessario inserire il codice in un modulo, quindi è possibile utilizzare la funzione =GetNumeric nel foglio di lavoro.
Questa funzione prenderà un solo argomento, che è il riferimento di cella della cella da cui vuoi ottenere la parte numerica.
Allo stesso modo, di seguito è la funzione che otterrà solo la parte di testo da una stringa in Excel:
'Questo codice VBA creerà una funzione per ottenere la parte di testo da una stringa Funzione GetText(CellRef As String) Dim StringLength As Integer StringLength = Len(CellRef) For i = 1 To StringLength If Not (IsNumeric(Mid(CellRef, i, 1))) Quindi Risultato = Risultato e Metà (RifCell, i, 1) Avanti i GetText = Funzione Fine Risultato
Quindi questi sono alcuni degli utili codici macro di Excel che puoi utilizzare nel tuo lavoro quotidiano per automatizzare le attività ed essere molto più produttivo.
