Registra Modifica Esegui macro Excel - Suggerimenti per Excel

Questo è il diciannovesimo suggerimento settimanale su Excel su.com. Molti dei suggerimenti di Excel implicano una sorta di trucco macro. Questa settimana, per gli utenti di Excel che non hanno mai scritto una macro, offro un primer su come registrare e poi personalizzare un'utile macro di Excel.

Dati indirizzo di esempio

Supponiamo che tu abbia 400 righe di dati di indirizzo come quello mostrato nella figura in alto a sinistra. Il campo del nome si trova nella colonna A, l'indirizzo stradale nella colonna B e la città nella colonna C.

Il tuo obiettivo è convertire i dati in una singola colonna come quella mostrata nella seconda figura.

Questo semplice problema verrà utilizzato per illustrare come registrare, modificare e quindi eseguire una semplice macro.

Per gli utenti di Excel 95: dopo aver registrato la macro, Excel inserirà la macro in un foglio chiamato Modulo1 nella cartella di lavoro. Puoi semplicemente fare clic sul foglio per accedere alla macro.

Sebbene ci siano 400 record in questo foglio di lavoro, voglio registrare una piccola parte della macro che si occupa solo del primo indirizzo. La macro presupporrà che il cellpointer si trovi sul nome. Inserirà tre righe vuote. Copierà la cella a destra della cella originale nella cella sotto la cella originale. Copierà la cella della città nella cella 2 righe sotto la cella originale. Dovrebbe quindi spostare il puntatore della cella verso il basso in modo che si trovi sul nome successivo.

La chiave è pensare a questo processo prima di registrarlo. Non vuoi fare molti errori durante la registrazione della macro.

Quindi, metti il puntatore della cella nella cella A1. Vai al menu e seleziona Strumenti> Macro> Registra nuova macro. La finestra di dialogo Registra macro suggerisce un nome di Macro1. Va bene, quindi premi OK.

Il registratore di macro di Excel ha un'impostazione predefinita molto stupida che devi assolutamente modificare per far funzionare questa macro. In Excel 95, vai su Strumenti> Macro> Usa riferimenti relativi In Excel 97-2003, fai clic sulla seconda icona sulla barra degli strumenti Interrompi registrazione. L'icona sembra un piccolo foglio di lavoro. Un globulo rosso in C3 punta a un altro globulo rosso in A3. L'icona si chiama Riferimento relativo. Quando questa icona è "attiva", è presente un colore attorno all'icona. L'icona ricorda l'ultima impostazione della sessione di Excel corrente, quindi potrebbe essere necessario fare clic su di essa un paio di volte per capire quale metodo è attivo o meno. In Excel 2007, utilizzare Visualizza - Macro - Usa riferimenti relativi.

OK, siamo pronti per partire. Segui questi passi:

Premi una volta la freccia in basso per passare alla cella B1.
Tieni premuto il tasto Maiusc e premi due volte la freccia giù per selezionare le righe 2, 3 e 4
Dal menu, seleziona Inserisci, quindi seleziona Righe per inserire tre righe vuote.
Premi la freccia su e poi la freccia destra per passare alla cella B2.
Premi Ctrl X per tagliare la cella B2.
Premi la freccia giù, la freccia sinistra, quindi Ctrl V per incollare nella cella A2.
Premi freccia su, freccia destra, freccia destra, Ctrl X, freccia sinistra, freccia sinistra, freccia giù, freccia giù, Ctrl V per spostare C1 in A3.
Premi la freccia giù due volte in modo che il puntatore della cella si trovi ora sul nome successivo nella riga A5.
Fare clic sull'icona "Interrompi registrazione" sulla barra degli strumenti per interrompere la registrazione della macro.

Bene, hai registrato la tua prima macro. Diamo un'occhiata. Vai a Strumenti> Macro> Macro. Dalla lista, evidenzia Macro1 e premi il pulsante Modifica. Dovresti vedere qualcosa che assomiglia a questo.

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select End Sub

Ehi, se non sei un programmatore, probabilmente sembra piuttosto intimidatorio. Non lasciare che accada. Se c'è qualcosa che non capisci, c'è un ottimo aiuto. Fare clic con il cursore da qualche parte nella parola chiave Offset e premere F1. A condizione che tu abbia installato il file della guida VBA, vedrai l'argomento della guida per la parola chiave Offset. L'aiuto ti dice la sintassi dell'istruzione. Dice che è Offset (RowOffset, ColumnOffset). Non è ancora molto chiaro? Cerca la parola sottolineata in verde "esempio" nella parte superiore della guida. Gli esempi VBA di Excel ti permetteranno di imparare cosa sta succedendo. Nell'esempio di Offset, dice che per attivare la cella due righe sotto e tre righe a destra della cella corrente, useresti:

ActiveCell.Offset(3, 2).Activate

OK, quindi questo è un indizio. La funzione offset è un modo per spostarsi nel foglio di calcolo di Excel. Data questa informazione, puoi vedere cosa sta facendo la macro. Il primo offset (1, 0) è dove abbiamo spostato il cellpointer in basso su A2. Il prossimo offset è dove ci siamo spostati su una riga (-1 righe) e su 1 colonna. Potresti non capire nient'altro nella macro, ma è comunque utile.

Torna al foglio di lavoro di Excel. Metti il puntatore della cella nella cella A5. Scegli Strumenti> Macro> Macro> Macro1> Esegui. La macro viene eseguita e il secondo indirizzo viene formattato.

Potresti dire che selezionare questa lunga e grande stringa di comandi è più difficile della semplice formattazione a mano. OK, quindi fai Strumenti> Macro> Macro> Opzioni. Nella casella di scelta rapida, dire che Ctrl + w è il tasto di scelta rapida per questa macro. Fare clic su OK, quindi chiudere la finestra di dialogo Macro con Annulla. Ora, quando premi Ctrl w, la macro verrà eseguita. È possibile formattare un indirizzo con una singola sequenza di tasti.

Sei pronto per il grande momento? Quanti indirizzi hai lasciato? Ho premuto Ctrl wa alcune volte, quindi mi rimangono 395. Torna alla tua macro. Metteremo l'intero codice della macro in un ciclo. Inserire una nuova riga che dice "Do Until activecell.value =" "" prima della prima riga del codice della macro. Inserire una riga che dice "Loop" prima della riga End Sub. Il ciclo Do eseguirà tutto ciò che si trova tra la riga Do e la riga Loop fino a quando non si trova in una riga vuota. La macro ora ha questo aspetto:

Sub Macro1() ' ' Macro1 Macro ' Macro recorded 4/18/99 by Reader ' ' Do Until ActiveCell.Value = "" ActiveCell.Offset(1, 0).Range("A1:A3").Select Selection.EntireRow.Insert ActiveCell.Offset(-1, 1).Range("A1").Select Selection.Cut ActiveCell.Offset(1, -1).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(-1, 2).Range("A1").Select Selection.Cut ActiveCell.Offset(2, -2).Range("A1").Select ActiveSheet.Paste ActiveCell.Offset(2, 0).Range("A1").Select Loop End Sub

Torna al tuo foglio Excel. Posiziona il puntatore della cella sul nome successivo. Premi Ctrl w e la macro formatterà tutti i tuoi record in pochi secondi.

Gli autori dei libri di Excel dicono che non puoi fare nulla di utile registrando una macro. Non vero! Per la persona che avrebbe dovuto tagliare e incollare 800 volte, questa macro è molto utile. Ci sono voluti alcuni minuti per registrare e personalizzare. Sì, i programmatori professionisti faranno notare che il codice è orribilmente inefficiente. Excel inserisce un sacco di cose che non è necessario inserire lì. Sì, se sapessi cosa stai facendo, puoi eseguire la stessa operazione con metà delle linee che verranno eseguite in 1,2 secondi invece di 3 secondi. E ALLORA? 3 secondi è molto più veloce dei 30 minuti che l'attività avrebbe richiesto.

Altri suggerimenti per iniziare a registrare macro:

L'apostrofo è usato per indicare un commento. Qualsiasi cosa dopo l'apostrofo viene ignorato da VBA
Questa è la programmazione orientata agli oggetti. La sintassi di base è object.action. Se un compilatore orientato agli oggetti stesse giocando a calcio, direbbe "ball.kick" per calciare il pallone. Quindi "Selection.Cut" dice di fare un "edit> cut" sulla selezione corrente.
Nell'esempio precedente, i modificatori dell'intervallo sono relativi alla cella attiva. Se la cella attiva è in B2 e dici "ActiveCell.Range (" A1: C3 "). Seleziona", seleziona l'area di 3 righe per 3 colonne a partire dalla cella B2. In altre parole, selezioni B2: D4. Dire "ActiveCell.Range (" A1 ")" dice di selezionare l'intervallo di celle 1 x 1 a partire dalla cella attiva. Questo è incredibilmente ridondante. È equivalente a dire "ActiveCell.Select".
Salvare la cartella di lavoro prima di eseguire una macro per la prima volta. In questo modo, se ha un errore e fa qualcosa di inaspettato, puoi chiudere senza salvare e tornare alla versione salvata.

Si spera che questo semplice esempio ti dia il coraggio di registrare una semplice macro la prossima volta che hai un'attività ricorrente da eseguire in Excel.