Registrazione di una macro in Excel - Articoli TechTV

Questi sono gli appunti della mia apparizione sull'episodio 33 di Call for Help su TechTV Canada.

Registratore di macro VBA

Ogni copia di Excel venduta dal 1995 include l'incredibilmente potente Visual Basic for Applications (VBA) che si nasconde dietro le celle. Se hai Excel, hai VBA.

VBA ti consente di automatizzare tutto ciò che puoi fare in Excel, oltre a fare più cose che non sono generalmente possibili in Excel.

Fortunatamente per noi, Microsoft ha incluso un registratore di macro con Excel. Ciò ti consente di scrivere codice VBA senza essere un programmatore. Sfortunatamente, il registratore di macro è difettoso. Non produrrà codice che funzionerà ogni volta. Il registratore di macro ti avvicinerà, ma in molti casi è necessario correggere un po 'il codice per farlo funzionare in modo affidabile. In altri casi, è necessario sapere quando utilizzare la registrazione relativa per far funzionare la macro.

Tracy Syrstad e io abbiamo scritto VBA e Macro Microsoft Excel 2016 per aiutarti a comprendere i limiti del codice registrato e per insegnarti come correggere il codice registrato in qualcosa che funzionerà ogni volta.

Nello show, ho dimostrato il processo di registrazione di una macro che funzionerà perfettamente se sai come utilizzare il pulsante Riferimento relativo. Qualcuno mi aveva fornito un foglio di lavoro Excel con centinaia di nomi e indirizzi. Volevano etichette postali. Se hai mai utilizzato la funzionalità di stampa unione di Microsoft Word, sai che hai bisogno di ogni campo in una nuova colonna del foglio di lavoro. Invece, questo particolare foglio di lavoro aveva i dati che scendevano nella colonna A.

Il processo per correggere una singola etichetta è piuttosto noioso.

Inizia con il puntatore della cella sul nome nella colonna A.
Premi la freccia in basso per spostarti all'indirizzo
Ctrl + x per tagliare
Freccia su, freccia destra per passare alla colonna B accanto al nome
Ctrl + v per incollare
Freccia giù due volte, freccia sinistra una volta per spostarti in città
Ctrl + x per tagliare
Freccia su due volte, freccia destra tre volte
Ctrl + v per incollare
Freccia sinistra due volte, freccia giù una volta per passare alla riga ora vuota.
Tieni premuto il tasto Maiusc mentre premi due volte la freccia giù
Alt + edr per eliminare le righe vuote
Freccia su e freccia giù per selezionare di nuovo solo il nome.

Ecco l'animazione che mostra questi passaggi:

Configurazione di Excel per consentire le macro

Sebbene ogni copia di Excel contenga VBA, per impostazione predefinita, Microsoft disattiva la possibilità di eseguire macro. È necessario eseguire una regolazione una tantum per consentire l'esecuzione delle macro. Apri Excel. Dal menu, seleziona Strumenti> Macro> Sicurezza. Se il livello di protezione della macro è attualmente impostato su alto, modificalo in Medio.

Preparazione alla registrazione della macro

Pensa ai passaggi necessari per portare a termine il compito. Vuoi assicurarti di iniziare con il puntatore di cella su un nome e di terminare con esso sul nome successivo. Ciò consentirà di eseguire ripetutamente la macro molte volte. Quando hai i passaggi pronti, accendi il registratore di macro. Dal menu, seleziona Strumenti> Macro> Registra nuova macro.

Se eseguirai questa macro per molti giorni, dovresti dare alla macro un nome utile. Se si tratta di una macro una tantum per automatizzare un'attività una tantum, è probabile che lasciare il nome della macro come Macro1 va bene. Il campo importante qui è il campo del tasto di scelta rapida. Se stai creando una macro per un utilizzo occasionale, assegna la macro a un tasto di scelta rapida come Ctrl + a: è abbastanza facile premere ripetutamente Ctrl + a poiché i tasti sono vicini l'uno all'altro. Se hai intenzione di creare una macro che utilizzerai ogni giorno, allora vuoi assegnare la macro a un tasto che non è già un'importante combinazione di tasti di scelta rapida. Ctrl + j o Ctrl + k sono buone scelte.

Le macro monouso devono essere archiviate in ThisWorkbook. Se è necessario utilizzare ripetutamente la macro su molte cartelle di lavoro diverse, è possibile memorizzare la macro nella cartella di lavoro Macro personale.

Ora ti viene presentata la barra degli strumenti più piccola di tutto Excel; la barra degli strumenti Interrompi registrazione. Ha solo due icone e ha questo aspetto:

Se questa barra degli strumenti si intromette, non premere la X per chiuderla. Invece, prendi la barra blu e trascina la barra degli strumenti in una nuova posizione. L'atto di spostare la barra degli strumenti non viene registrato nella macro. Se chiudi accidentalmente la barra degli strumenti, recuperala con Visualizza> Barre degli strumenti> Interrompi registrazione. Questa azione, tuttavia, verrà registrata.

Il primo pulsante sulla barra degli strumenti è il pulsante "Interrompi registrazione". Questo è autoesplicativo. Al termine della registrazione della macro, premere la barra degli strumenti di interruzione della registrazione.

Il pulsante più importante (e raramente compreso) è il pulsante "Riferimento relativo".

Nel nostro esempio attuale, devi premere il pulsante Riferimento relativo prima di iniziare. Se registri una macro con i riferimenti relativi attivati, Excel registrerà passaggi come questo:

Sposta una cella in basso
Taglia la cella corrente
Sposta una cella in alto
Sposta una cella a destra
Incolla
eccetera.

Se invece dovessi registrare la macro senza riferimenti relativi, la macro registrerebbe questi passaggi:

Passa alla cella A4
Taglia cella A4
Passa alla cella A3
Passa alla cella B3
Incolla nella cella B3
eccetera.

Questo sarebbe terribilmente sbagliato: abbiamo bisogno che la macro funzioni su celle che sono 1 e 2 celle dal punto di partenza della macro. Man mano che si esegue la macro più e più volte, il punto di partenza sarà la riga 4, la riga 5, la riga 6, ecc. La registrazione della macro senza i riferimenti relativi attivati farà sì che la macro venga eseguita sempre sulla riga 3 come punto di partenza.

Segui questi passi:

Scegli il pulsante Riferimento relativo sulla barra degli strumenti Interrompi registrazione
Il puntatore di cella dovrebbe essere già su un nome nella colonna A.
Premi la freccia in basso per spostarti all'indirizzo
Ctrl + x per tagliare
Freccia su, freccia destra per passare alla colonna B accanto al nome
Ctrl + v per incollare
Freccia giù due volte, freccia sinistra una volta per spostarti in città
Ctrl + x per tagliare
Freccia su due volte, freccia destra tre volte
Ctrl + v per incollare
Freccia sinistra due volte, freccia giù una volta per passare alla riga ora vuota.
Tieni premuto il tasto Maiusc mentre premi due volte la freccia giù
Alt + edr per eliminare le righe vuote
Freccia su e freccia giù per selezionare il nome successivo nell'elenco.
Premi la barra degli strumenti Interrompi registrazione
Salva la cartella di lavoro
Prova la macro premendo il tasto di scelta rapida assegnato sopra. Dovrebbe correggere perfettamente il nome successivo nell'elenco

Una volta che vedi che la macro funziona come desiderato, puoi tenere premuto Ctrl + a per correggere rapidamente alcuni nomi al secondo. L'intero elenco di 500 nomi può essere corretto in uno o due minuti.

Suggerimento bonus: non nello show

Puoi facilmente avvolgere la macro in un semplice ciclo per far sì che aggiusti tutti i 500 nomi senza dover premere Ctrl + diverse centinaia di volte.

Premi il tasto Alt + F11 per aprire l'editor delle macro.

Se non vedi il pannello Progetto - VBAProject, premi Ctrl + r.

Fare clic con il pulsante destro del mouse su Module1 e scegliere Visualizza codice. Vedrai un codice simile a questo:

Ora, non devi capire cosa fa questo codice, ma sai che vogliamo eseguire tutto in quella macro una volta per ogni nome che abbiamo. Se sai che ci sono 462 nomi, puoi aggiungere 2 righe per eseguire il codice 462 volte. Nella parte superiore della macro, inserisci una nuova riga con le parole " For i = 1 to 462". Nella parte inferiore della macro, inserisci una riga che dice " Next i". Questo dice a VBA di eseguire il codice all'interno di 462 volte.

Conclusione

Dopo questa semplice macro, ho mostrato un esempio nello show di una macro molto complessa. Questa macro ha creato tabelle e grafici pivot per 46 dipartimenti di un'azienda. Questo rapporto richiedeva 40 ore ogni mese. La macro VBA ora viene eseguita e crea tutti i 46 report in meno di 2 minuti.

Il libro VBA e Macro Microsoft Excel 2016 ti guiderà lungo la curva di apprendimento in modo da poter passare dalla registrazione di semplici macro come questa all'esecuzione di rapporti molto complessi che possono farti risparmiare centinaia di ore all'anno. Ovviamente, se non vuoi imparare VBA, assumi un consulente Excel per progettare un rapporto per te.