Descrizione
Il registratore di macro.
Obiettivi della lezione
Saper registrare ed eseguire una macro e capirne la
funzionalità.
Indice lezione
Lezione 7 - Foglio Elettronico
7.2
Operazioni e vari
tipi di macro;
7.3
La registrazione
di una macro;
7.4
Esempio di
registrazione macro per impostazione foglio di lavoro;
7.5
Esercizio.
Dati sulla lezione
|
Versione: |
v1.0 |
|
Tempi: |
1 1/2 ora / 90
minuti |
|
Data svolgimento lezione: |
12 Marzo 2003 |
|
Autore dei contenuti: |
Vastapane Marco Attilio – Giuliano Luca |
|
Autore materiali didattici: |
Togni Francesca |
La Teoria
7.1 - Il registratore di macro
Nel senso più generale una macro è una serie di istruzioni che vanno ad automatizzare alcuni aspetti dell’applicativo, in questo caso di Excel, in modo da poter lavorare sfruttandone al pieno le potenzialità.
Vengono considerate normalmente come funzioni avanzate di Excel. Una volta creata, e dopo aver eseguito il necessario debug per verificarne il corretto funzionamento, è possibile utilizzarla per svolgere operazioni complesse.
Una macro quindi è un programma scritto o registrato in cui è memorizzata una serie di comandi di Microsoft Excel utilizzabile successivamente come un singolo comando. Le macro consentono di automatizzare attività complesse e ripetitive. Con il linguaggio di programmazione Microsoft Visual Basic è possibile compilare le varie istruzioni utilizzando direttamente Visual Basic Editor, ed il risultato che si andrà ad ottenere sarà sicuramente di maggior pregio rispetto al codice generato dal registratore, ma è anche possibile scriverle attraverso l’apposito editor. Infatti, il risultato che si può ottenere utilizzando direttamente l’editor Visual Basic, consente all’applicazione di assumere un aspetto più professionale e funzionale.
7.2 - Operazioni e vari tipi di macro
Tra le varie
operazioni che si possono eseguire con le macro ed il VBA, possono rientrare:
q L’automatizzazione di una procedura che si deve eseguire di frequente
q Il compimento di un’operazione ripetitiva da effettuarsi su un discreto numero di fogli.
q La creazione di comandi personalizzati da inserire sul foglio o su un forum su di esso caricato.
Tra i vari
tipi di macro possiamo trovare:
q
Le subroutine
È un tipo di macro che può essere eseguito dall’utente o da un’altra macro. Si può avere un numero qualsiasi di subroutine all’interno della cartella di lavoro.
q
Le funzioni
Restituiscono sempre un singolo valore, alla stregua delle funzioni del foglio elettronico. Può essere eseguita da altre procedure VBA.
7.3 - La registrazione di una macro
Nel nostro
esempio, proviamo a costruire una semplice macro con il Registratore di
macro.
Prima di registrare o scrivere una macro, definire i vari passaggi e i comandi che si desidera che la macro esegua. Se si commette un errore durante la registrazione viene memorizzato nella procedura con la relativa correzione. Ogni macro verrà memorizzata da Visual Basic in un nuovo modulo associato alla cartella di lavoro.
q
Per attivare il registratore di macro, al menu Strumenti,
cliccare Macro, quindi Registra nuova macro.
Alcune informazioni necessarie:
q Il nome macro : è quello che viene assegnato. Troviamo nomi predefiniti come Macro1, Macro2 e così via.
q Scelta rapida da tastiera : è possibile specificare alcuni tasti di scelta rapida che vadano ad eseguire la macro. È anche possibile , in abbinamento al tasto CTRL, associare il tasto SHIFT o MAIUSCOLE. Quindi CTRL+A sarà diverso da CTRL+MAIUSCOLE+A.
q Memorizza macro in : è la posizione destinata per la macro
q Descrizione: una sorta di appunti dell’autore della macro.
Nel nostro esempio vogliamo far scrivere dalla macro nella cella A1 la frase “Ciao a tutti”.
La macro verrà lanciata tutte le
volte che da tastiera andremo a premere una combinazione di tasti, ad esempio CTRL+m
Qualunque operazione ora andremo a
compiere,verrà registrata, errori compresi. Quindi andiamo nella cella A1 e
scriviamo la frase Ciao a tutti.
Quando abbiamo terminato, dopo aver
disattivato la cella in cui si è scritto, basta premere il tasto per fermare la
registrazione, il quadrato blu. 
Il registratore di macro ha generato del codice VBA, che é possibile andare a leggere premendo il tasto Modifica della finestra dedicata alle macro, evocata dal menu Strumenti – macro.
Le informazioni relative alla macro , salvate nel modulo associato alla cartella di lavoro di excel, saranno capaci di evocare la macro, ogni volta che si andrà a premere la combinazione di tasti scelta precedentemente nel modulo associato alla cartella di lavoro.
La registrazione delle operazioni dà sempre una nuova procedura subroutine. Purtroppo per creare delle funzioni, non è possibile utilizzare il registratore di macro, ma occorre scriverle attraverso l’editor Visual Basic.
Il modulo è memorizzato nella cartella di lavoro di Excel e può essere visualizzare solo nella finestra VBE, cioè Visual Basic Editor.
La finestra di progetto presenta un elenco di tutte le cartelle di lavoro aperte e di tutti i vari componenti aggiuntivi.
Il codice memorizzato in precedenza si trova nel Modulo1 nella cartella di lavoro corrente.
Nel nostro esempio analizzando il codice:
la macro registrata è una subroutine, iniziando con l’istruzione Sub.
La parte in verde non è che un semplice commento aggiunto da Excel.
Ovviamente, dopo aver registrato la macro, sarà possibile andarla a modificare, semplicemente modificando parte del codice generato.
Ad esempio, se volessimo modificare la cella di destinazione da A1 a B1, sarà sufficiente sostituire la prima riga effettiva in :
Range (“B1”).Select
7.4 - Esempio di registrazione macro per
impostazione foglio di lavoro
Utilizzare una macro per andare ad impostare in un foglio di
lavoro, una formattazione delle celle B3:F10 in modo che il formato celle sia
impostato come nella foto sottostante.
Apriamo il Registratore di macro,e scegliamo come combinazione di tasti CTRL+p.
Ora,tutte le operazioni che andremo a compiere,errori compresi, saranno registrate del modulo associato al file di Visual Basic.
L’obiettivo è preparare una tabella di calcolo per effettuare, lanciando la macro,un’impostazione del foglio elettronico già predisposto all’occorrenza.
Selezioniamo le celle prescelte, come nell’immagine a fianco:nella Categoria la Valuta, in Posizione decimali 2, e in Simbolo l’Euro.
Diamo anche un colore azzurro,ed impostiamo la bordatura delle celle in esame.
Selezioniamo ora le celle B3:B10 e dal menu Formato impostiamo nella Categoria , Numero in Posizione decimali 0. Questo dato servirà per la quantità dei prodotti da inserire.
Ora inseriamo un
intestazione con la descrizione dei vari campi , come mostrato nella figura
sottostante;la cella F11 sarà quella in cui sarà visualizzato il totale.
Ora dovremo inserire e formule nelle celle D3:F3
D3
: Posto che l’iva è al 20%, calcoliamo l’iva del singolo pezzo = C3*0.2
E3 : Il costo del singolo pezzo ivato =C3+D3
F3 : Il totale riguardo ai pezzi dello stesso tipo =E3*B3.E’ possibile anche inserire al posto della semplice moltiplicazione, la formula = SE(B4=0;"";E4*B4) , per eliminare gli 0 nel risultato.
F11 : inseriamo la funzione somma per avere il totale, e formattiamo quella cella in modo che visualizzi il risultato sotto forma di valuta: dal menu Formato impostiamo nella Categoria la Valuta, in Posizione decimali 2, e in Simbolo l’Euro.
Ora, i tasti CTRL+p, la macro eseguirà le operazioni desiderate.
Ecco il codice prodotto:
Sub Macro6()
'
' Macro6
Macro
'
Macro registrata il 04/03/2003 da vasta
'
'
Scelta rapida da tastiera: CTRL+p
'
Range("B3:F10").Select
Selection.Font.ColorIndex = 34
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With
Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Interior
.ColorIndex = 8
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 0
Range("A2:F2").Select
With
Selection.Interior
.ColorIndex = 5
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 2
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With
Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlEdgeTop)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
Selection.Font.Bold = False
Selection.Font.Bold = True
Range("A2").Select
ActiveCell.FormulaR1C1 = "Descr"
Range("B2").Select
ActiveCell.FormulaR1C1 =
"Quantità"
Range("C2").Select
ActiveCell.FormulaR1C1 = "Costo"
Range("D2").Select
ActiveCell.FormulaR1C1 = "IVA"
Range("E2").Select
ActiveCell.FormulaR1C1 = "Pr.Ivato"
Range("F2").Select
ActiveCell.FormulaR1C1 = "Totale"
Range("F11").Select
With
Selection.Interior
.ColorIndex = 6
.Pattern = xlSolid
End With
Selection.Font.ColorIndex = 3
ActiveCell.FormulaR1C1 = "=SUM(R[-8]C:R[-1]C)"
Range("B3:F10").Select
Selection.NumberFormat = "[$€-2] #,##0.00"
Range("B3:B10").Select
Selection.NumberFormat = "#,##0"
Range("D3").Select
ActiveCell.FormulaR1C1 = "=RC[-1]*0.2"
Range("E3").Select
ActiveCell.FormulaR1C1 = "=RC[-2]+RC[-1]"
Range("F3").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-4]=0,"""",RC[-1]*RC[-4])"
Range("B3:F3").Select
Selection.AutoFill Destination:=Range("B3:F10"),
Type:=xlFillDefault
Range("B3:F10").Select
Range("F16").Select
End Sub
La Pratica
Sulla base di quanto abbiamo visto nella lezione sul registratore di macro, proviamo a fare un’esercitazione capace di evocare alla pressione dei tasti CTRL+a il seguente foglio di lavoro.
q Apriamo Excel
q Dal menu strumenti selezioniamo Macro , quindi Registra nuova macro…
q Selezioniamo la combinazione di tasti CTRL+a e premiamo OK.Tutte le operazioni che ora andremo a compire, errori compresi, saranno registrati nel modulo Visual Basic associato alla cartella di lavoro.
q Selezioniamo le celle A1-H2
q Uniamole con il comando Unisci e centra
q Con lo strumento Colore riempimento , colorare la cella così ottenuta di azzurro
q Scriviamo l’intestazione della nostra Azienda, ad esempio azienda Pinco Pallo e formattiamo il testo in Arial 24, grassetto colore nero.
q Ora formattiamo le celle B4:G16 ; Formato celle : valuta euro a 2 decimali, come in figura.
L’area che abbiamo selezionato, per agevolare il lavoro di inserimento,potrà essere colorata di giallo,con una bordatura semplice nelle celle, mentre nelle celle B4:B16 potremo lasciare il colore bianco.
q Andiamo ad allargare la colonna A, in modo da agevolare l’inserimento degli articoli di magazzino.
q Nella cella A3 , fino alla cella G3 andremo ad inserire le parole Articolo, Qnt, Costo, %, Iva Pr.Ivato, Totale e formattiamoli attribuendo al testo il Grassetto con allineamento centrato.
q Occorre ancora formattare le celle B4:B16 in modo che il valore in esse memorizzate siano di tipo numerico, mentre per le celle D4:D16 sarà necessario impostare la percentuale. Formato celle : percentuale a o decimali.
Ora prepariamo le formule.
q Nella cella E4 inseriamo la formula che dovrà calcolare la percentuale del costo.La formula sarà ovviamente =D4*C4
q Se vogliamo, come al solito, non visualizzare il risultato se la cella C4, che racchiude il costo è uguale a 0, dovremo utilizzare la funzione SE
q Il prezzo ivato, nella cella F4 sarà ottenuto sommando l’iva al prezzo.
q Il totale, nella cella G4 sarà dato dal prodotto del Prezzo ivato moltiplicato per la quantità.
q Ora non resta che estendere le formule alle altre celle, sfruttando i riferimenti relativi.
q Nella cella G17 inseriremo la somma, utilizzando l’apposita funzione .
A questo punto,tutte le informazioni necessarie al funzionamento della
macro sono state inserite.Occorre deselezionare la cella che calcola il totale
e interrompere la registrazione.
Ecco il codice prodotto
Range("A1:H2").Select
With
Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Selection.Merge
With
Selection.Interior
.ColorIndex = 33
.Pattern = xlSolid
End With
Range("A1:H2").Select
ActiveCell.FormulaR1C1 = "Azienda Pinco
Pallo"
With ActiveCell.Characters(Start:=1,
Length:=19).Font
.Name =
"Arial"
.FontStyle = "Grassetto"
.Size =
24
.Strikethrough = False
.Superscript = False
.Subscript = False
.OutlineFont = False
.Shadow
= False
.Underline = xlUnderlineStyleNone
.ColorIndex = xlAutomatic
End With
Range("A3").Select
Columns("A:A").ColumnWidth = 17.71
Range("B4:G16").Select
Selection.NumberFormat = "#,##0.00 [$€-1]"
Range("B4:B16").Select
Selection.NumberFormat = "#,##0"
Range("D4:D16").Select
Selection.NumberFormat = "0%"
Range("B4:G16").Select
Selection.Borders(xlDiagonalDown).LineStyle = xlNone
Selection.Borders(xlDiagonalUp).LineStyle = xlNone
With
Selection.Borders(xlEdgeLeft)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlEdgeTop)
.LineStyle
= xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlEdgeBottom)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlEdgeRight)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlInsideVertical)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
With
Selection.Borders(xlInsideHorizontal)
.LineStyle = xlContinuous
.Weight
= xlThin
.ColorIndex = xlAutomatic
End With
Range("C4:G16").Select
With
Selection.Interior
.ColorIndex = 36
.Pattern = xlSolid
End With
Range("A3").Select
ActiveCell.FormulaR1C1 = "Articolo"
Range("B3").Select
ActiveCell.FormulaR1C1 = "Qnt"
Range("C3").Select
ActiveCell.FormulaR1C1 = "Costo"
Range("D3").Select
ActiveCell.FormulaR1C1
= "%"
Range("E3").Select
ActiveCell.FormulaR1C1 = "Iva"
Range("F3").Select
ActiveCell.FormulaR1C1 = "Pr.Ivato"
Range("G3").Select
ActiveCell.FormulaR1C1 = "Totale"
Range("A3:H3").Select
Range("H3").Activate
Selection.Font.Bold = True
With
Selection
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlBottom
.WrapText = False
.Orientation = 0
.AddIndent = False
.ShrinkToFit = False
.MergeCells = False
End With
Range("E4").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-2]=0,"""",RC[-2]*RC[-1])"
Range("F4").Select
ActiveCell.FormulaR1C1 =
"=IF(RC[-3]=0,"""",RC[-3]+RC[-1])"
Range("G4").Select
ActiveCell.FormulaR1C1 = "=IF(RC[-4]=0,"""",RC[-1]*RC[-5])"
Range("B4:G4").Select
Selection.AutoFill Destination:=Range("B4:G16"),
Type:=xlFillDefault
Range("B4:G16").Select
Range("G17").Select
ActiveCell.FormulaR1C1 = "=SUM(R[-13]C:R[-1]C)"
Range("G17").Select
With
Selection.Interior
.ColorIndex = 3
.Pattern = xlSolid
End With
Range("H18").Select
End Sub
Inserire l’esercizio
nello zaino.
Feedback
Per
chiarimenti, si prega di lasciare la domanda direttamente sul Forum della
piattaforma Maestra.