Utilizzare le macro di Excel può accelerare il lavoro e risparmiare molto tempo.
Un modo per ottenere il codice VBA è registrare la macro e prendere il codice che genera. Tuttavia, quel codice dal registratore di macro è spesso pieno di codice che non è veramente necessario. Anche il registratore di macro ha alcune limitazioni.
Perciò vale la pena avere una collezione di codici di macro VBA utili che potete avere nella vostra tasca posteriore e usarli quando serve.
Mentre scrivere un codice di macro Excel VBA può richiedere un po’ di tempo inizialmente, una volta fatto, potete tenerlo disponibile come riferimento e usarlo ogni volta che ne avete bisogno.
In questo massiccio articolo, ho intenzione di elencare alcuni utili esempi di macro Excel che mi servono spesso e che tengo nascosti nella mia cassaforte privata. Se pensi che qualcosa dovrebbe essere nella lista, lascia un commento.
Puoi mettere questa pagina tra i preferiti per riferimenti futuri.
Ora, prima di entrare nell’esempio della macro e darti il codice VBA, lascia che ti mostri come usare questi codici di esempio.
Usare il codice degli esempi di macro di Excel
Questi sono i passi da seguire per usare il codice di qualsiasi esempio:
- Apri la cartella di lavoro in cui vuoi usare la macro.
- Tieni il tasto ALT e premi F11. Questo apre l’editor VB.
- Clicca con il tasto destro del mouse su uno 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 dica che è necessario incollare il codice nella finestra del codice del foglio di lavoro, fate doppio clic sull’oggetto foglio di lavoro e copiate e incollate 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
Una volta copiato il codice nell’editor VB, ecco i passi per eseguire la macro:
- Vai alla scheda Developer.
- Clicca su Macros.
- Nella finestra di dialogo Macro, selezionare la macro che si desidera eseguire.
- Clicca sul pulsante Esegui.
Nel caso non riuscissi a trovare la scheda sviluppatore nella barra multifunzione, leggi questo tutorial per imparare come ottenerla.
Tutorial correlato: Diversi modi per eseguire una macro in Excel.
Nel caso in cui il codice sia incollato nella finestra del codice del foglio di lavoro, non dovete preoccuparvi di eseguire il codice. Verrà eseguito automaticamente quando si verifica l’azione specificata.
Ora, entriamo negli utili esempi di macro che possono aiutarvi ad automatizzare il lavoro e risparmiare tempo.
Nota: troverete molte istanze di un apostrofo (‘) seguito da una o due righe. Questi sono commenti che vengono ignorati durante l’esecuzione del codice e sono posti come note per se stessi/lettori.
Nel caso in cui troviate qualche errore nell’articolo o nel codice, siate fantastici e fatemelo sapere.
Esempi di macro Excel
I seguenti esempi di macro sono trattati in questo articolo:
Questo tutorial copre:
Deselezionare tutti i fogli di lavoro in una volta sola
Se stai lavorando in una cartella di lavoro che ha più fogli nascosti, è necessario deselezionare questi fogli uno per uno. Questo potrebbe richiedere un po’ di tempo nel caso in cui ci siano molti fogli nascosti.
Qui c’è il codice che disvelerà tutti i fogli di lavoro nella cartella di lavoro.
'This code will unhide all sheets in the workbookSub UnhideAllWoksheets()Dim ws As WorksheetFor Each ws In ActiveWorkbook.Worksheetsws.Visible = xlSheetVisibleNext wsEnd Sub
Il codice qui sopra usa un ciclo VBA (For Each) per passare attraverso ogni foglio di lavoro nella cartella di lavoro. Quindi cambia la proprietà visible del foglio di lavoro in visible.
Qui c’è un tutorial dettagliato su come usare vari metodi per nascondere i fogli in Excel.
Nascondi tutti i fogli di lavoro tranne il foglio attivo
Se stai lavorando ad un report o ad un dashboard e vuoi nascondere tutti i fogli di lavoro tranne quello che ha il report/dashboard, puoi usare questo codice macro.
'This macro will hide all the worksheet except the active sheetSub HideAllExceptActiveSheet()Dim ws As WorksheetFor Each ws In ThisWorkbook.WorksheetsIf ws.Name <> ActiveSheet.Name Then ws.Visible = xlSheetHiddenNext wsEnd Sub
Ordinare i fogli di lavoro in ordine alfabetico usando VBA
Se avete una cartella di lavoro con molti fogli di lavoro e volete ordinarli in ordine alfabetico, questo codice macro può essere molto utile. Questo potrebbe essere il caso se avete nomi di fogli come anni o nomi di dipendenti o nomi di prodotti.
'This code will sort the worksheets alphabeticallySub SortSheetsTabName()Application.ScreenUpdating = FalseDim ShCount As Integer, i As Integer, j As IntegerShCount = Sheets.CountFor i = 1 To ShCount - 1For j = i + 1 To ShCountIf Sheets(j).Name < Sheets(i).Name ThenSheets(j).Move before:=Sheets(i)End IfNext jNext iApplication.ScreenUpdating = TrueEnd Sub
Proteggete tutti i fogli di lavoro in una volta sola
Se avete molti fogli di lavoro in una cartella e volete proteggere tutti i fogli, potete usare questo codice macro.
Vi permette di specificare la password all’interno del codice. Avrete bisogno di questa password per sproteggere il foglio di lavoro.
'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword = "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheets ws.Protect password:=passwordNext wsEnd Sub
Sproteggere tutti i fogli di lavoro in una volta sola
Se avete alcuni o tutti i fogli di lavoro protetti, potete semplicemente usare una leggera modifica del codice usato per proteggere i fogli per sproteggerli.
'This code will protect all the sheets at one goSub ProtectAllSheets()Dim ws As WorksheetDim password As Stringpassword = "Test123" 'replace Test123 with the password you wantFor Each ws In Worksheetsws.Unprotect password:=passwordNext wsEnd Sub
Nota che la password deve essere la stessa che è stata usata per bloccare i fogli di lavoro. Se non lo è, vedrete un errore.
Disfare tutte le righe e le colonne
Questo codice macro disfarà tutte le righe e le colonne nascoste.
Questo potrebbe essere molto utile se si riceve un file da qualcun altro e si vuole essere sicuri che non ci siano righe/colonne nascoste.
'This code will unhide all the rows and columns in the WorksheetSub UnhideRowsColumns()Columns.EntireColumn.Hidden = FalseRows.EntireRow.Hidden = FalseEnd Sub
Unmerge All Merged Cells
È una pratica comune unire le celle per farne una sola. Mentre fa il lavoro, quando le celle sono fuse non sarete in grado di ordinare i dati.
Nel caso stiate lavorando con un foglio di lavoro con celle fuse, usate il codice qui sotto per unmerge tutte le celle fuse in una volta sola.
'This code will unmerge all the merged cellsSub UnmergeAllCells()ActiveSheet.Cells.UnMergeEnd Sub
Nota che invece di unire e centrare, ti consiglio di usare l’opzione Centro su tutta la selezione.
Salva cartella di lavoro con TimeStamp nel suo nome
Spesso, potresti aver bisogno di creare versioni del tuo lavoro. Queste sono abbastanza utili in progetti lunghi dove si lavora con un file nel tempo.
Una buona pratica è quella di salvare il file con timestamp.
L’utilizzo dei timestamp vi permetterà di tornare indietro ad un certo file per vedere quali modifiche sono state fatte 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 salvata.
'This code will Save the File With a Timestamp in its nameSub SaveWorkbookWithTimeStamp()Dim timestamp As Stringtimestamp = Format(Date, "dd-mm-yyyy") & "_" & Format(Time, "hh-ss")ThisWorkbook.SaveAs "C:UsersUsernameDesktopWorkbookName" & timestampEnd 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 alla tua azienda.
Salva ogni foglio di lavoro come PDF separato
Se lavori con dati per diversi anni o divisioni o prodotti, potresti avere la necessità di salvare diversi fogli di lavoro come file PDF.
Sebbene possa essere un processo che richiede molto tempo se fatto manualmente, VBA può davvero velocizzarlo.
Ecco un codice VBA che salverà ogni foglio di lavoro come PDF separato.
'This code will save each worsheet as a separate PDFSub SaveWorkshetAsPDF()Dim ws As WorksheetFor Each ws In Worksheetsws.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ws.Name & ".pdf"Next wsEnd Sub
Nel codice sopra, ho specificato l’indirizzo della cartella in cui voglio salvare i PDF. Inoltre, ogni PDF avrà lo stesso nome del foglio di lavoro. Dovrai modificare questo percorso della cartella (a meno che il tuo nome non sia Sumit e tu lo stia salvando in una cartella di prova sul desktop).
Nota che questo codice funziona solo per i fogli di lavoro (e non per i fogli del grafico).
Salva ogni foglio di lavoro come PDF separato
Qui c’è il codice che salverà l’intera cartella di lavoro come PDF nella cartella specificata.
'This code will save the entire workbook as PDFSub SaveWorkshetAsPDF()ThisWorkbook.ExportAsFixedFormat xlTypePDF, "C:UsersSumitDesktopTest" & ThisWorkbook.Name & ".pdf"End Sub
Dovrai cambiare la posizione della cartella per usare questo codice.
Converti tutte le formule in valori
Usa questo codice quando hai un foglio di lavoro che contiene molte formule e vuoi convertirle in valori.
'This code will convert all formulas into valuesSub ConvertToValues()With ActiveSheet.UsedRange.Value = .ValueEnd WithEnd Sub
Questo codice identifica automaticamente le celle utilizzate e le converte in valori.
Proteggi/Blocca le celle con formule
Potresti voler bloccare le celle con formule quando hai molti calcoli e non vuoi cancellarli o cambiarli accidentalmente.
Qui c’è il codice che blocca tutte le celle che hanno formule, mentre tutte le altre celle non sono bloccate.
'This macro code will lock all the cells with formulasSub LockCellsWithFormulas()With ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=TrueEnd WithEnd Sub
Tutorial correlato: Come bloccare le celle in Excel.
Proteggere tutti i fogli di lavoro nella cartella di lavoro
Utilizzare il codice seguente per proteggere tutti i fogli di lavoro in una cartella di lavoro in una sola volta.
'This code will protect all sheets in the workbookSub ProtectAllSheets()Dim ws As WorksheetFor Each ws In Worksheetsws.ProtectNext wsEnd Sub
Questo codice passerà in rassegna tutti i fogli di lavoro uno per uno e li proteggerà.
Nel caso in cui vogliate sproteggere tutti i fogli di lavoro, usate ws.Unprotect invece di ws.Protect nel codice.
Insert A Row After Every Other Row in the Selection
Utilizzate questo codice quando volete inserire una riga vuota dopo ogni riga nell’intervallo selezionato.
'This code will insert a row after every row in the selectionSub InsertAlternateRows()Dim rng As RangeDim CountRow As IntegerDim i As IntegerSet rng = SelectionCountRow = rng.EntireRow.CountFor i = 1 To CountRowActiveCell.EntireRow.InsertActiveCell.Offset(2, 0).SelectNext iEnd Sub
Similmente, potete modificare questo codice per inserire una colonna vuota dopo ogni colonna nell’intervallo selezionato.
Inserire automaticamente la data & timestamp nella cella adiacente
Un timestamp è qualcosa che si usa quando si vogliono tracciare le attività.
Per esempio, si può voler tracciare attività come quando è stata sostenuta una particolare spesa, a che ora è stata creata la fattura di vendita, quando è stato fatto l’inserimento dei dati in una cella, quando è stato aggiornato l’ultimo rapporto, ecc.
Utilizzate questo codice per inserire una data e un’ora nella cella adiacente quando viene inserito un dato o viene modificato il contenuto esistente.
'This code will insert a timestamp in the adjacent cellPrivate Sub Worksheet_Change(ByVal Target As Range)On Error GoTo HandlerIf Target.Column = 1 And Target.Value <> "" ThenApplication.EnableEvents = FalseTarget.Offset(0, 1) = Format(Now(), "dd-mm-yyyy hh:mm:ss")Application.EnableEvents = TrueEnd IfHandler:End Sub
Nota che devi inserire questo codice nella finestra del codice del foglio di lavoro (e non nella finestra del codice del modulo come abbiamo fatto in altri esempi di macro Excel finora). Per fare questo, nell’editor VB, fate doppio clic sul nome del foglio in cui volete questa funzionalità. Poi copiate e incollate questo codice nella finestra del codice di quel foglio.
Inoltre, questo codice è fatto per funzionare quando l’inserimento dei dati viene fatto nella colonna A (notate che il codice ha la linea Target.Column = 1). Puoi cambiarlo di conseguenza.
Evidenzia le righe alternative nella selezione
Evidenziare le righe alternative può aumentare enormemente la leggibilità dei tuoi dati. Questo può essere utile quando avete bisogno di prendere una stampa e passare attraverso i dati.
Ecco un codice che evidenzierà istantaneamente le righe alternative nella selezione.
'This code would highlight alternate rows in the selectionSub HighlightAlternateRows()Dim Myrange As RangeDim Myrow As RangeSet Myrange = SelectionFor Each Myrow In Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End IfNext MyrowEnd Sub
Nota che ho specificato il colore come vbCyan nel codice. Potete specificare anche altri colori (come vbRed, vbGreen, vbBlue).
Evidenzia le celle con parole sbagliate
Excel non ha un controllo ortografico come in Word o PowerPoint. Mentre è possibile eseguire il controllo ortografico premendo il tasto F7, non c’è alcun segnale visivo quando c’è un errore di ortografia.
Utilizzate questo codice per evidenziare istantaneamente tutte le celle che hanno un errore di ortografia.
'This code will highlight the cells that have misspelled wordsSub HighlightMisspelledCells()Dim cl As RangeFor Each cl In ActiveSheet.UsedRangeIf Not Application.CheckSpelling(word:=cl.Text) Thencl.Interior.Color = vbRedEnd IfNext clEnd Sub
Nota che le celle che vengono evidenziate sono quelle che hanno un testo che Excel considera un errore di ortografia. In molti casi, evidenzierebbe anche nomi o termini di marca che non capisce.
Aggiorna tutte le tabelle pivot nella cartella di lavoro
Se avete più di una tabella pivot nella cartella di lavoro, potete usare questo codice per aggiornare tutte queste tabelle pivot in una volta sola.
'This code will refresh all the Pivot Table in the WorkbookSub RefreshAllPivotTables()Dim PT As PivotTableFor Each PT In ActiveSheet.PivotTablesPT.RefreshTableNext PTEnd Sub
Potete leggere di più sul rinfresco delle tabelle pivot qui.
Cambiare le lettere maiuscole delle celle selezionate in maiuscole
Mentre Excel ha le formule per cambiare le lettere maiuscole del testo, te lo fa fare in un altro gruppo di celle.
Utilizzate questo codice per cambiare istantaneamente le lettere maiuscole del testo nel testo selezionato.
'This code will change the Selection to Upper CaseSub ChangeCase()Dim Rng As RangeFor Each Rng In Selection.CellsIf Rng.HasFormula = False ThenRng.Value = UCase(Rng.Value)End IfNext RngEnd Sub
Nota che in questo caso, ho usato UCase per rendere il testo maiuscolo. Potete usare LCase per le minuscole.
Evidenzia tutte le celle con commenti
Utilizzate il codice sottostante per evidenziare tutte le celle che hanno dei commenti.
'This code will highlight cells that have comments`Sub HighlightCellsWithComments()ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlueEnd Sub
In questo caso, ho usato vbBlue per dare un colore blu alle celle. Potete cambiarlo con altri colori se volete.
Evidenzia le celle vuote con VBA
Mentre potete evidenziare le celle vuote con la formattazione condizionale o usando la finestra di dialogo Vai a speciale, se dovete farlo abbastanza spesso, è meglio usare una macro.
Una volta creata, potete avere questa macro nella barra degli strumenti di accesso rapido o salvarla nella vostra cartella di lavoro macro personale.
Ecco il codice della macro VBA:
'This code will highlight all the blank cells in the datasetSub HighlightBlankCells()Dim Dataset as RangeSet Dataset = SelectionDataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRedEnd Sub
In questo codice, ho specificato che le celle vuote devono essere evidenziate nel colore rosso. Potete scegliere altri colori come blu, giallo, ciano, ecc.
Come ordinare i dati per colonna singola
Potete usare il codice seguente per ordinare i dati per la colonna specificata.
Sub SortDataHeader()Range("DataRange").Sort Key1:=Range("A1"), Order1:=xlAscending, Header:=xlYesEnd Sub
Nota che ho creato un intervallo con nome ‘DataRange’ e l’ho usato al posto dei riferimenti alle celle.
Inoltre ci sono tre parametri chiave che vengono usati qui:
- Key1 – Questo è il criterio su cui vuoi ordinare l’insieme dei dati. Nel codice dell’esempio precedente, i dati saranno ordinati in base ai valori della colonna A.
- Order- Qui devi specificare se vuoi ordinare i dati in ordine crescente o decrescente.
- Header – Qui devi specificare se i tuoi dati hanno o meno delle intestazioni.
Leggi di più su come ordinare i dati in Excel usando VBA.
Come ordinare i dati per colonne multiple
Supponiamo di avere un set di dati come mostrato di seguito:
Di seguito il codice che ordinerà i dati sulla base di colonne multiple:
Sub SortMultipleColumns()With ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1:C13") .Header = xlYes .ApplyEnd WithEnd 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 qui sotto:
Come ottenere solo la parte numerica da una stringa in Excel
Se volete estrarre solo la parte numerica o solo la parte di testo da una stringa, potete creare una funzione personalizzata in VBA.
Potete poi usare questa funzione VBA nel foglio di lavoro (proprio come le normali funzioni di Excel) ed essa estrarrà solo la parte numerica o di testo dalla stringa.
Come mostrato di seguito:
Di seguito il codice VBA che crea una funzione per estrarre la parte numerica da una stringa:
'This VBA code will create a function to get the numeric part from a stringFunction GetNumeric(CellRef As String)Dim StringLength As IntegerStringLength = Len(CellRef)For i = 1 To StringLengthIf IsNumeric(Mid(CellRef, i, 1)) Then Result = Result & Mid(CellRef, i, 1)Next iGetNumeric = ResultEnd Function
È necessario inserire il codice in un modulo, e poi è possibile utilizzare la funzione =GetNumeric nel foglio di lavoro.
Questa funzione prenderà solo un argomento, che è il riferimento della cella da cui volete ottenere la parte numerica.
Similmente, qui sotto c’è la funzione che vi farà ottenere solo la parte di testo da una stringa in Excel:
'This VBA code will create a function to get the text part from a stringFunction GetText(CellRef As String)Dim StringLength As IntegerStringLength = Len(CellRef)For i = 1 To StringLengthIf Not (IsNumeric(Mid(CellRef, i, 1))) Then Result = Result & Mid(CellRef, i, 1)Next iGetText = ResultEnd Function
Quindi questi sono alcuni degli utili codici macro di Excel che potete usare nel vostro lavoro quotidiano per automatizzare i compiti ed essere molto più produttivi.
Altri tutorial di Excel che potrebbero piacerti:
- Come cancellare le macro in Excel