Lavorare con le macro VBA di Excel
Le macro in Excel sono memorizzate come codice VBA e talvolta vorrai modificare direttamente questo codice. Questo tutorial illustrerà come visualizzare e modificare le macro, descrivere alcune tecniche di debug delle macro e fornire alcuni esempi di modifica comuni.
Visualizza macro
Un elenco di macro può essere visualizzato nella finestra di dialogo Macro. Per visualizzare questa finestra di dialogo, seleziona la scheda Sviluppatore sulla barra multifunzione e fai clic sul pulsante Macro.
Se sono aperte più cartelle di lavoro, nell'elenco verranno visualizzate le macro di tutte le cartelle di lavoro. Le macro nella cartella di lavoro attiva verranno visualizzate solo per nome, mentre le macro in altre cartelle di lavoro saranno precedute dal nome della cartella di lavoro e da un punto esclamativo (ad esempio "Book2!OtherMacro").
Apri una macro per la modifica
È possibile utilizzare la finestra di dialogo Macro per aprire il codice di una macro selezionando il nome della macro e facendo clic sul pulsante Modifica. Questo aprirà la macro nell'editor VB.
In alternativa, puoi aprire direttamente VB Editor facendo clic sul pulsante Visual Basic nella scheda Sviluppatore o premendo la scorciatoia da tastiera ALT+F11.
Usando questo metodo, dovrai accedere alla macro desiderata (chiamata anche "procedura"). Esamineremo il layout dell'editor VBA:
Panoramica dell'editor VB
L'editor VB ha diverse finestre; in questo tutorial tratteremo la finestra del progetto, la finestra delle proprietà e la finestra del codice.
Finestra del progetto
La finestra del progetto mostra ogni file Excel come il proprio progetto, con tutti gli oggetti in quel progetto classificati per tipo. Le macro registrate appariranno nella categoria "Moduli", generalmente nell'oggetto “Modulo1”. (Se il tuo progetto ha più moduli e non sei sicuro di dove sia archiviata la tua macro, aprila semplicemente dalla suddetta finestra di dialogo Macro.)
Finestra delle proprietà
La finestra delle proprietà mostra le proprietà e i valori associati di un oggetto: ad esempio, facendo clic su un oggetto del foglio di lavoro nella finestra del progetto verrà visualizzato un elenco di proprietà per il foglio di lavoro. I nomi delle proprietà sono a sinistra e i valori delle proprietà sono a destra.
Selezionando un modulo nella finestra Progetto mostrerà che ha solo una proprietà, "(Nome)". È possibile modificare il nome di un modulo facendo doppio clic sul valore della proprietà, digitando un nuovo nome e premendo Invio. La modifica del nome di un modulo lo rinominerà nella finestra del progetto, il che è utile se hai molti moduli.
Finestre di codice
Le finestre del codice sono speciali editor di testo in cui è possibile modificare il codice VBA della macro. Se si desidera visualizzare il codice per una macro situata in Module1, fare doppio clic su "Module1" nella finestra del progetto.
Esecuzione di macro nell'editor VB
Le macro possono essere eseguite direttamente dall'editor VB, utile per il test e il debug.
Esecuzione di una macro
- Nella finestra del progetto, fare doppio clic sul modulo contenente la macro che si desidera testare (per aprire la finestra del codice)
- Nella finestra Codice, posiziona il cursore in un punto qualsiasi del codice della macro tra "Sub" e "End Sub"
- Clicca il Correre pulsante sulla barra degli strumenti o premere la scorciatoia da tastiera F5
"Passo attraverso" una macro
Invece di eseguire la macro contemporaneamente, è possibile eseguire la macro una riga alla volta, utilizzando una scorciatoia da tastiera per "passare attraverso" il codice. La macro si fermerà su ogni riga, consentendoti di assicurarti che ogni riga di codice faccia ciò che ti aspetti in Excel. Puoi anche impedire a una macro di continuare in qualsiasi momento utilizzando questo metodo.
Per "passare attraverso" una macro:
- Nella finestra del progetto, fare doppio clic sul modulo contenente la macro che si desidera testare (per aprire la finestra del codice)
- Nella finestra Codice, posiziona il cursore in un punto qualsiasi del codice della macro
- Premi la scorciatoia da tastiera F8 per iniziare il processo "step-through"
- Premere ripetutamente F8 per far avanzare l'esecuzione del codice, indicato dall'evidenziazione gialla nella finestra del codice
- Per interrompere la continuazione di una macro, premere il tasto Ripristina pulsante
Perché modificare le macro VBA?
Anche il registratore di macro, sebbene efficace, è molto limitato. In alcuni casi produce macro lente, registra azioni che non avevi intenzione di ripetere o registra cose che non pensavi di fare. Imparare a modificare le macro li aiuterà a funzionare più velocemente, in modo più efficiente e in modo più prevedibile.
Oltre a risolvere questi problemi, otterrai anche un enorme aumento della produttività quando sfrutti tutta la potenza delle macro. Le macro non devono essere solo registrazioni di attività: le macro possono includere la logica in modo da eseguire attività solo in determinate condizioni. In un paio di minuti puoi codificare loop che ripetono un'attività centinaia o migliaia di volte in una volta sola!
Di seguito troverai alcuni suggerimenti utili per ottimizzare il tuo codice macro, nonché strumenti per far funzionare le tue macro più duramente e in modo più intelligente.
Esempi comuni di modifica delle macro
Macro velocizzate
Se disponi di una macro che richiede molto tempo per l'esecuzione, potrebbero esserci un paio di motivi per cui viene eseguita lentamente.
Per uno: quando viene eseguita una macro, Excel mostrerà tutto come accade in tempo reale, anche se potrebbe Guarda veloce per te, in realtàmostrando il lavoro è un significativo successo di prestazioni. Un modo per rendere Excel significativamente più veloce è dirgli di smetti di aggiornare lo schermo:
' Disabilita l'applicazione di aggiornamento dello schermo.ScreenUpdating = False ' Abilita l'applicazione di aggiornamento dello schermo.ScreenUpdating = True
La riga "Application.ScreenUpdating = False" significa che non vedrai la macro funzionare, ma funzionerà molto più velocemente. Nota che dovresti sempre impostare ScreenUpdating su True alla fine della tua macro, altrimenti Excel potrebbe non agire come ti aspetti in seguito!
Un altro modo per velocizzare le macro:disattiva il calcolo automatico nella macro. Se hai lavorato con fogli di calcolo complessi, saprai che piccole modifiche possono attivare migliaia di calcoli che richiedono tempo per essere completati, motivo per cui molte persone disattivano il calcolo automatico nelle opzioni di Excel. Puoi anche attivarlo con il codice VBA, quindi la tua macro funzionerà comunque rapidamente su altri computer. Questo aiuta nei casi in cui stai copiando e incollando molte celle della formula o causando l'attivazione di molti calcoli mentre incolli i dati in un intervallo:
' Disabilita l'applicazione di calcolo automatico.Calculation = xlCalculationManual ' Abilita l'applicazione di calcolo automatico.Calculation = xlCalculationAutomatic
Aggiungi loop e logica (istruzioni If)
Il registratore di macro salva tutte le tue azioni come codice in un linguaggio chiamato VBA. VBA è più di un semplice modo per registrare azioni in Excel: è un linguaggio di programmazione, il che significa che può contenere codice per prendere decisioni su quali azioni eseguire o ripetere azioni fino a quando non viene soddisfatta una condizione.
loop
Supponiamo che volessi creare una macro che preparasse un rapporto e come parte di quella macro dovevi aggiungere diciannove fogli alla cartella di lavoro, per un totale di venti. Puoi registrarti facendo ripetutamente clic sul pulsante (+), oppure puoi scrivere un ciclo che ripete l'azione per te, in questo modo:
Sub ReportPrep() Dim i As Long For i = 1 To 19 Sheets.Add Next i End Sub
In questo esempio, usiamo a Per ciclo, che è una sorta di ciclo che scorre un intervallo di elementi. Qui, il nostro intervallo è costituito dai numeri da 1 a 19, utilizzando una variabile denominata "i" in modo che il ciclo possa tenere traccia. All'interno del nostro ciclo, c'è solo un'azione ripetuta tra il per eprossimo righe (l'aggiunta del foglio), ma puoi aggiungere tutto il codice all'interno del ciclo che desideri per eseguire operazioni come formattare il foglio o copiare e incollare dati su ciascun foglio, qualunque cosa tu voglia ripetere.
Se Dichiarazioni
Un Se dichiarazione viene utilizzato per decidere se un codice viene eseguito o meno, utilizzando un test logico per prendere la decisione. Ecco un semplice esempio:
Sub ClearIfSmall() If Selection.Value < 100 Then Selection.Clear End If End Sub
Questo semplice esempio mostra come funziona l'istruzione If: si verifica una condizione che è True o False (il valore della cella selezionata è inferiore a 100?), e se il test restituisce True, viene eseguito il codice all'interno.
Un difetto di questo codice è che verifica solo una cella alla volta (e fallirebbe se si selezionassero più celle). Questo sarebbe più utile se potessi… scorrere tutte le celle selezionate e testarle ognuna…
Sub ClearIfSmall() Dim c As Range For Each c In Selection.Cells If c.Value < 100 Then c.Clear End If Next c End Sub
In questo esempio, c'è un ciclo For leggermente diverso: questo non esegue il ciclo di un intervallo di numeri, ma esegue il ciclo di tutte le celle nella selezione, utilizzando una variabile denominata "c" per tenere traccia. All'interno del ciclo, il valore di "c" viene utilizzato per determinare se la cella deve essere cancellata o meno.
I loop e le istruzioni If possono essere combinati come preferisci: puoi inserire loop all'interno di loop, o un If all'interno di un altro, o utilizzare un If per decidere se un ciclo deve essere eseguito.
<<>>
Rimuovi effetti di scorrimento
Un motivo comune per modificare il codice macro è rimuovere lo scorrimento dello schermo. Quando si registra una macro, potrebbe essere necessario raggiungere altre aree di un foglio di lavoro scorrendo, ma non è necessario che le macro scorrano per accedere ai dati.
Lo scorrimento può ingombrare il codice con centinaia o addirittura migliaia di righe di codice non necessario. Ecco un esempio del codice che viene registrato quando fai clic e trascini sulla barra di scorrimento:
Questo tipo di codice è completamente inutile e potrebbe essere eliminato senza influire su altre funzionalità. Anche se volessi mantenere lo scorrimento, questo codice potrebbe comunque essere condensato in un ciclo.
Rimuovi il codice ridondante
Le macro registrate tendono ad aggiungere molto codice ridondante che non riflette necessariamente ciò che si desidera che faccia la macro. Prendiamo ad esempio il seguente codice registrato, che registra la modifica di un nome di carattere su una cella:
Anche se è stato modificato solo il nome del carattere, sono state registrate undici (11) modifiche al carattere come la dimensione del carattere, gli effetti del testo, ecc. la macro registrata non funzionerebbe!
È possibile modificare questa macro in modo che cambi solo il nome del carattere:
Non solo questa macro funzionerà come previsto ora, ma è anche molto più facile da leggere.
Rimuovi i movimenti del cursore
Un'altra cosa che viene registrata nelle macro sono le selezioni del foglio di lavoro e delle celle. Questo è un problema perché un utente può facilmente perdere traccia di ciò su cui stava lavorando se il cursore si sposta in una posizione diversa dopo l'esecuzione di una macro.
Come con lo scorrimento, tu potrebbe essere necessario spostare il cursore e selezionare celle diverse per eseguire un'attività, ma le macro non devono utilizzare il cursore per accedere ai dati. Considera il seguente codice, che copia un intervallo e poi lo incolla in altri tre fogli:
Ci sono alcuni problemi con questo codice:
- L'utente perderà il posto precedente nella cartella di lavoro
- La macro non specifica quale foglio stiamo copiandoa partire dal - questo potrebbe essere un problema se la macro è stata eseguita sul foglio sbagliato
Inoltre, il codice è difficile da leggere e dispendioso. Questi problemi possono essere risolti abbastanza facilmente:
In questo codice, è chiaro che stiamo copiando da Sheet1 e né il foglio di lavoro attivo né l'intervallo selezionato devono essere modificati per incollare i dati. (Un cambiamento significativo è l'uso di "PasteSpecial" invece di "Paste" - Gli oggetti Range, come "Range("C4")", hanno accesso solo al comando PasteSpecial.)
Ogni volta che il codice diventa pieno di riferimenti a ".Select" e "Selection", è un indizio che c'è spazio per ottimizzare quel codice e renderlo più efficiente.
