24 utili esempi di macro di Excel per principianti VBA (pronto per l'uso)

L'uso delle macro di Excel può velocizzare il tuo lavoro e risparmiare molto tempo.

Un modo per ottenere il codice VBA è registrare una macro e ottenere il codice che genera.Tuttavia, il codice del registratore di macro è spesso pieno di codice che non è realmente necessario.Anche il registratore di macro presenta alcune limitazioni.

Quindi vale la pena avere un utile set di macro VBA che puoi tenere nella tasca posteriore e utilizzare quando ne hai bisogno.

Anche se inizialmente potrebbe essere necessario del tempo per codificare una macro VBA di Excel, una volta terminato, puoi usarlo come riferimento e utilizzarlo la prossima volta che ne hai bisogno.

In questo enorme articolo, elencherò alcuni utili esempi di macro di Excel di cui ho spesso bisogno e che conservo nel mio caveau privato.

Continuerò ad aggiornare questo tutorial con altri esempi di macro.Per favore lascia un commento se pensi che qualcosa dovrebbe essere nell'elenco.

Puoi aggiungere questa pagina ai segnalibri per riferimenti futuri.

Ora, prima di entrare negli esempi di macro e darti il ​​codice VBA, lascia che ti mostri prima come utilizzare il codice di esempio.

Utilizzo del codice dell'esempio di macro di Excel

Di seguito sono riportati i passaggi da seguire per utilizzare il codice di uno qualsiasi degli esempi:

• Aprire la cartella di lavoro in cui si desidera utilizzare la macro.
• Tenere premuto il tasto ALT e premere F11.Questo si apriràEditore VB.
• Fare clic con il pulsante destro del mouse su qualsiasi oggetto in Project Explorer.
• Vai a Inserisci -> Moduli.
• Copia e incolla il codice nella finestra del codice del modulo.

Se l'esempio dice 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.

Dopo aver inserito il codice nella cartella di lavoro, è necessario salvarlo con l'estensione .XLSM o .XLS.

Come eseguire una macro

Dopo aver copiato il codice nell'editor VB, i passaggi per eseguire la macro sono i seguenti:

• Vai alla scheda sviluppatore.
• Fare clic su Macro.

• Nella finestra di dialogo Macro, selezionare la macro da eseguire.
• Fare clic sul pulsante Esegui.

Se non riesci a trovare la scheda sviluppatore nella barra multifunzione, leggi questo tutorial per sapere come ottenerla.

Se il codice viene incollato nella finestra del codice del foglio di lavoro, non devi preoccuparti di eseguire il codice.Viene eseguito automaticamente quando si verifica l'azione specificata.

Ora, entriamo in utili esempi di macro che possono aiutarti ad automatizzare il tuo lavoro e risparmiare tempo.

Nota: troverai molti apostrofi (') seguiti da una o due righe.Questi sono commenti che vengono ignorati durante l'esecuzione del codice e inseriti come commenti di auto/lettore.

Se trovi errori nell'articolo o nel codice, faccelo sapere.

Esempio di macro di Excel

In questo articolo vengono descritti i seguenti esempi di macro:

Scopri tutti i fogli di lavoro contemporaneamente

Se stai lavorando su una cartella di lavoro con più fogli nascosti, devi mostrare questi fogli uno per uno.L'operazione potrebbe richiedere del tempo se sono presenti molti fogli nascosti.

Ecco il codice per mostrare tutti i fogli 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 precedente usa un ciclo VBA (For Each) per scorrere ogni foglio di lavoro nella cartella di lavoro.Quindi cambia la proprietà visibile del foglio in visibile.

Ecco un tutorial dettagliato su come mostrare un foglio di lavoro in Excel utilizzando vari metodi.

Nascondi tutti i fogli di lavoro tranne il foglio di lavoro attivo

Questo codice macro può essere utilizzato se si lavora su un report o dashboard e si desidera nascondere tutti i fogli tranne il foglio che contiene il report/dashboard.

'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 il foglio di lavoro in ordine alfabetico usando VBA

Questo codice macro può tornare utile se si dispone di una cartella di lavoro con molti fogli e si desidera ordinare quei fogli in ordine alfabetico.Questo può accadere se hai il nome del foglio come anno o nome del dipendente o nome del prodotto.

'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 Se Fogli(j).Nome < Fogli(i).Nome Poi Fogli(j).Sposta prima:=Fogli(i) Fine se Avanti j Avanti i Application.ScreenUpdating = True End Sub

Proteggi tutti i fogli di lavoro contemporaneamente

Se nella cartella di lavoro sono presenti molti fogli e si desidera proteggere tutti i fogli, è possibile utilizzare questo codice macro.

Consente di specificare una password nel codice.Avrai bisogno di questa password per rimuovere la protezione del foglio.

'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 Each ws In Worksheets ws.Protect password:=password Next ws Fine Sub

Annulla la protezione di tutti i fogli di lavoro contemporaneamente

Se hai protetto alcuni o tutti i tuoi fogli di lavoro, puoi rimuoverli dalla protezione apportando solo una leggera modifica al codice utilizzato per proteggere i fogli 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 Each ws In Worksheets ws.Unprotect password:=password Next ws Fine Sub

Si noti che la password deve essere la stessa utilizzata per bloccare il foglio.In caso contrario, vedrai un errore.

Mostra tutte le righe e le colonne

Questo codice macro mostrerà tutte le righe e le colonne nascoste.

Questo può essere utile se stai prendendo il file da qualcun altro e vuoi assicurarti 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

Separa tutte le celle unite

Unire le celle in una è una pratica comune.Mentre funziona, non sarai in grado di ordinare i dati quando le celle vengono unite.

Se stai utilizzando un foglio di lavoro con celle unite, usa il codice seguente per separare tutte le celle unite contemporaneamente.

'Questo codice separerà tutte le celle unite Sub UnmergeAllCells() ActiveSheet.Cells.UnMerge End Sub

Nota che ti consiglio di utilizzare l'opzione "Centra nella selezione" invece dell'opzione "Unisci e centra".

Salva la cartella di lavoro con il timestamp nel nome

Ci sono molte volte in cui potrebbe essere necessario creare una versione funzionante.Questi sono ottimi per progetti a lungo termine che si occupano di file nel tempo.

Una buona pratica è salvare il file con un timestamp.

L'utilizzo di un timestamp ti consentirà di tornare a un file per vedere quali modifiche sono state apportate o quali dati sono stati utilizzati.

Ecco il codice che salva automaticamente la cartella di lavoro nella cartella specificata e aggiunge un timestamp durante il salvataggio.

'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 il percorso della cartella e il nome del file.

Nel codice sopra, "C:UsersUsernameDesktop è la posizione della cartella che uso.È necessario specificare la posizione della cartella in cui si desidera salvare il file.Inoltre, ho usato il nome generico "WorkbookName" come prefisso del nome file.Puoi specificare i contenuti relativi al tuo progetto o alla tua azienda.

Salva ogni foglio di lavoro come PDF separato

Se utilizzi dati di anni o dipartimenti o prodotti diversi, potrebbe essere necessario salvare diversi fogli di lavoro come file PDF.

Sebbene questo possa essere un processo che richiede tempo se eseguito manualmente, VBA velocizza le cose.

Ecco un codice VBA che salva ogni foglio di lavoro come PDF separato.

'Questo codice salverà ogni foglio di lavoro come un PDF separato 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 cartella dove voglio salvare il PDF.Inoltre, ogni PDF riceverà lo stesso nome del foglio.Dovrai modificare la posizione di questa cartella (a meno che il tuo nome non sia anche Sumit e l'hai salvato in una cartella di prova sul desktop).

Nota che questo codice funziona solo per i fogli di lavoro (non per i fogli grafici).

Salva ogni foglio di lavoro come PDF separato

Ecco il codice per salvare 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

È necessario modificare il percorso della cartella per utilizzare questo codice.

Converti tutte le formule in valori

Utilizzare questo codice quando si dispone di un foglio di lavoro con molte formule e si desidera convertire tali formule in valori.

'Questo codice convertirà tutte le formule in valori Sub ConvertToValues() With ActiveSheet.UsedRange .Value = .Value End With End Sub

Questo codice riconosce automaticamente la cella utilizzata e la converte in un valore.

Proteggi/blocca le celle con le formule

Quando hai molti calcoli e non vuoi eliminarli o modificarli accidentalmente, potresti voler bloccare le celle con le formule.

Ecco il codice che bloccherà tutte le celle con le formule e lascerà sbloccate tutte le altre celle.

'Questo codice macro bloccherà tutte le celle con formule Sub LockCellsWithFormulas() con ActiveSheet .Unprotect .Cells.Locked = False .Cells.SpecialCells(xlCellTypeFormulas).Locked = True .Protect AllowDeletingRows:=True End with End Sub

Tutorial correlato: come bloccare le celle in Excel.

Proteggi tutti i fogli in una cartella di lavoro

Utilizzare il codice seguente per proteggere contemporaneamente tutti i fogli di una cartella di lavoro.

'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 uno per uno e li proteggerà.

Se vuoi annullare la protezione di tutti i fogli, usa ws.Unprotect invece di ws.Protect nel tuo codice.

Inserisci una riga dopo ogni riga della 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

Ancora una volta, puoi modificare questo codice per inserire una colonna vuota dopo ogni colonna nell'intervallo selezionato.

Inserisci automaticamente data e timestamp nelle celle adiacenti

I timestamp sono ciò che usi quando desideri monitorare l'attività.

Ad esempio, potresti voler tenere traccia di attività come quando è stata sostenuta una spesa particolare, quando è stata creata una fattura di vendita, quando i dati sono stati inseriti in una cella, quando è stato aggiornato l'ultima volta un report e così via.

Utilizzare questo codice per inserire una data e un'ora nelle celle adiacenti durante la digitazione o la modifica del contenuto esistente.

'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

Nota che devi inserire questo codice nella finestra del codice del foglio di lavoro (non nella finestra del codice del modulo come abbiamo fatto finora in altri esempi di macro di Excel).A tale scopo, nell'editor VB, fare doppio clic sul nome del foglio per il quale 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 una riga Target.Column = 1).Puoi cambiarlo di conseguenza.

Evidenzia le righe alternative nella selezione

L'evidenziazione di righe alternate può migliorare notevolmente la leggibilità dei dati.Questo può essere utile quando è necessario stamparlo e sfogliare i dati.

Ecco un codice che evidenzierà immediatamente le righe alternative nella selezione.

'Questo codice evidenzierebbe righe alternative nella selezione Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Selezione per ogni Myrow in Myrange.Rows If Myrow.Row Mod 2 = 1 Then Myrow.Interior.Color = vbCyan End Se Next Myrow End Sub

Nota che ho specificato il colore come vbCyan nel codice.Puoi anche specificare altri colori (es. vbRed, vbGreen, vbBlue).

Evidenzia le celle con errori di ortografia

Excel non ha il controllo ortografico in Word o PowerPoint.Sebbene sia possibile eseguire il controllo ortografico premendo F7, non è presente alcun segnale visivo per gli errori di ortografia.

Usa questo codice per evidenziare istantaneamente tutte le celle errate.

'Questo codice evidenzierà le celle che hanno parole errate Sub HighlightMisspeldCells() 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 Fine Sub

Si noti che le celle evidenziate sono le celle con il testo che Excel ritiene sia errato.In molti casi, evidenzia anche nomi o termini di marca che non comprende.

Aggiorna tutte le tabelle pivot in una cartella di lavoro

Se nella cartella di lavoro sono presenti più tabelle pivot, puoi usare questo codice per aggiornarle tutte 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 PT successivo End Sub

Puoi leggere di più sull'aggiornamento delle tabelle pivot qui.

Cambia la lettera maiuscola delle celle selezionate in maiuscolo

Sebbene Excel abbia formule per cambiare il caso delle lettere di testo, ti consente di farlo in un altro insieme di celle.

Usa questo codice per cambiare istantaneamente la lettera maiuscola del testo nel testo selezionato.

'Questo codice cambierà la selezione in maiuscolo 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

Nota che in questo caso utilizzo UCase per impostare il testo su maiuscolo.Puoi usare LCase per minuscole.

Evidenzia tutte le celle con i commenti

Utilizzare il codice seguente per evidenziare tutte le celle con commenti al loro interno.

'Questo codice evidenzierà le celle che contengono commenti' Sub HighlightCellsWithComments() ActiveSheet.Cells.SpecialCells(xlCellTypeComments).Interior.Color = vbBlue End Sub

In questo caso, uso vbBlue per dare alla cella un colore blu.Puoi cambiarlo in un altro colore se necessario.

Evidenzia le celle vuote con VBA

Mentre puoi usare la formattazione condizionale o usare la finestra di dialogo Vai a speciale per evidenziare le celle vuote, se devi farlo 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 as Range Set Dataset = Selection Dataset.SpecialCells(xlCellTypeBlanks).Interior.Color = vbRed End Sub

In questo codice, ho specificato le celle vuote evidenziate in rosso.Puoi scegliere altri colori come blu, giallo, ciano, ecc.

Come ordinare i dati in base a una singola colonna

È possibile utilizzare il codice seguente per ordinare i dati in base a una colonna specificata.

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

Nota che ho creato un intervallo denominato "DataRange" e l'ho usato al posto del riferimento di cella.

Ci sono anche tre parametri chiave utilizzati qui:

• Key1 – Questa è la chiave in base alla quale si desidera ordinare il set di dati.Nel codice di esempio sopra, i dati verranno ordinati in base ai valori nella colonna A.
• Order1 – Qui è necessario specificare se si desidera ordinare i dati in ordine crescente o decrescente.
• Intestazione: qui devi specificare se i tuoi dati hanno un'intestazione o meno.

Ulteriori informazioni su come ordinare i dati in Excel utilizzando VBA.

Come ordinare i dati per più colonne

Supponiamo di avere un set di dati simile a questo:

Ecco il codice per ordinare i dati in base a più colonne:

Sub SortMultipleColumns() con ActiveSheet.Sort .SortFields.Add Key:=Range("A1"), Order:=xlAscending .SortFields.Add Key:=Range("B1"), Order:=xlAscending .SetRange Range("A1") :C13") .Header = xlSì .Applica Fine con End Sub

Nota che qui ho specificato di ordinare prima sulla colonna A e poi sulla colonna B.

L'output sarà simile a questo:

Come ottenere solo la parte numerica da una stringa in Excel

Se vuoi solo estrarre la parte numerica o la parte di testo dalla stringa, puoi creare una funzione personalizzata in VBA.

È quindi possibile utilizzare questa funzione VBA nel foglio di lavoro (proprio come una normale funzione di Excel) ed estrarrà solo il numero o la parte di testo dalla stringa.

Come mostrato di seguito:

Ecco il codice VBA che creerà una funzione che estrae la parte numerica da una stringa:

'Questo codice VBA creerà una funzione per ottenere la parte numerica da una stringa Function 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 & Mid(CellRef, i, 1) Avanti i GetNumeric = Risultato Funzione di fine

È necessario inserire il codice in un modulo e quindi utilizzare la funzione =GetNumeric nel foglio di lavoro.

Questa funzione prenderà un solo parametro, il riferimento di cella della cella da cui ottenere la parte numerica.

Allo stesso modo, la funzione seguente 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 Function 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 & Mid(CellRef, i, 1) Avanti i GetText = Risultato Funzione di fine

Quindi, questi sono alcuni utili codici macro di Excel che puoi utilizzare nel tuo lavoro quotidiano per automatizzare le attività e aumentare la tua produttività.