Articolo wiki
  • Questo articolo wiki è bloccato
Applicabile a 8296 visualizzazioni

Excel - ABC della programmazione ad oggetti in Excel - VBA (Update)

Livello tecnico : Base

Riepilogo

Come creare codice sicuro grazie alla programmazione ad oggetti - Conoscenza di base di Visual Basic for Applications.

Si applica a: Excel (2003/2007/2010/2013)

Dettagli

NOTA. Gli esempi ed il codice sono forniti *così come sono* e l’autore declina ogni responsabilità per un loro uso scorretto. Utilizzate gli esempi forniti o file di prova per i vostri test. La correzione delle eccezioni (errori) e il controllo del tipo di dati inserito è solo parziale. I file vogliono essere un semplice esempio che illustra come sia possibile automatizzare Excel e Word e sono forniti solo a scopo dimostrativo.

Premessa.

La programmazione ad oggetti in Excel non è da confondere con la programmazione ad oggetti che è possibile in linguaggi come C++, C#, Java, VisualBasic.Net, ecc. Nel Visual Basic di Excel, quello che possiamo fare è utilizzare riferimenti specifici a ciascuno degli oggetti che compongono il nostro file ed evitare errori banali e comuni come scrivere nella cella sbagliata del foglio sbagliato e cose di questo genere.


Una macro generica.

Questo codice, creato avviando il registratore delle macro, scrive nella cella attiva:


Sub Macro1()
'
' Macro1 Macro
'

'
    ActiveCell.FormulaR1C1 = "Ciao"

End Sub


E' un codice senza nessun tipo di controllo e che scrive *casualmente* sulla cella attiva, modificandone l'eventuale contenuto. L'utente che lancia la macro non è al corrente di dove stia scrivendo e questo non è sicuramente il massimo per ciò che riguarda la sicurezza. La macro seguente (che deve trovarsi in un modulo standard) avvisa l'utente di cosa stia accadendo e migliora un po' la sicurezza:


Public Sub Macro1()


    Dim lRisposta As Long
   
    lRisposta = MsgBox("Vuoi sovrascrivere la cella: " & _
        vbNewLine & "Foglio " & ActiveSheet.Name & _
        " - Cella " & ActiveCell.Address(False, False) & "?", _
        vbYesNo + vbQuestion, _
        "Attenzione")
    If lRisposta = vbYes Then
        ActiveCell.FormulaR1C1 = "Ciao"
        MsgBox "Ho scritto nella cella"
    Else
        MsgBox "Operazione annullata"
    End If


 End Sub


In pratica abbiamo una MsgBox che specifica foglio e cella che stiamo per modificare e chiede all'utente cosa fare:



Figura 1. La MsgBox che avvisa l'utente in quale cella, di quale foglio sia per scrivere.


Salvo rarissimi casi, questo modo di scrivere codice puntando ad ActiveCell/ActiveSheet è assolutamente da evitare.



Una macro per la generica cella A1.

La macro che segue(che deve trovarsi in un modulo standard) ha come riferimento generico il Range A1. Non punta a nessun foglio particolare, quindi scriverà nella cella A1 del foglio attivo nel momento in cui la lanciamo. Se non abbiamo la necessità di scrivere in un foglio particolare, non pone problemi.


Public Sub m()
    Range("A1").Value = "Ciao"
End Sub

Quindi, quale sia il foglio attivo, la macro scriverà nella sua cella A1. E' ancora troppo generica per essere sicura.

Una macro per la cella A1 del Foglio1.
Foglio1 per il vb di Excel può essere il riferimento a cose diverse. Dipende da come inseriamo quel Foglio1 nel nostro codice. Se date un'occhiata alle proprietà del foglio, trovate la proprietà (Name) indicata dalla freccia numero 2 e la proprietà Name indicata dalla freccia numero 3. La freccia 1 indica invece come viene visualizzato il riferimento allo stesso Foglio1 nella finestra Progetto-VBAProject, fra parentesi il nome presente sulla linguetta lato Excel:



Figura 2. Indicate dalle frecce 2 e 3 le proprietà (Name) e Name; dalla freccia 1 come si presenta il Foglio1 nella finestra Progetto-VBAProject.


La prima, la proprietà (Name) è modificabile solo in fase di progettazione (in realtà c'è un modo non documentato di modificarla anche run-time, ma è OT in questo contesto). La seconda, la proprietà Name mostra il nome che è presente sulla linguetta in basso in *Excel lato celle* (ho modificato il nome da Foglio1 a Dati nel file d'esempio) ed è modificabile da codice e appunto dalla linguetta di *Excel lato celle*. Vediamo di fare due esempi. La macro che segue( che deve trovarsi in un modulo standard) ultilizza Foglio1.Range("A1").Value, utilizza cioè la proprietà (Name) per identificare il foglio.Se cambiate il nome in *Excel lato celle* sulla linguetta del Foglio1, il riferimento rimarrà comunque al foglio che nell'insieme dei fogli ha come (Name) Foglio1. La cosa può essere utile se chi utilizza il file ha la possibilità di modificare il nome dei fogli tramite la linguetta, sollevando un errore se la vostra macro punta ad un foglio tramite la proprietà Name(che ricordo corrisponde a quanto trovo nella linguetta).


Public Sub m()
    Foglio1.Range("A1").Value = "Ciao"
End Sub

In questo caso ci viene in aiuto l'intellisense dell'editor del VB: quando digitate il . (il punto) dopo Foglio1, vengono mostrati metodi, proprietà, eventi relativi all'oggetto Foglio1.

Figura 3. L'intellisense ci viene in aiuto mostrando metodi, ptoprietà ed eventi del Foglio1.


Possiamo invece fare riferimento alla proprietà Name del foglio. La macro che segue (che deve trovarsi in un modulo standard) fa appunto riferimento al foglio Dati tramite la proprietà Name. Quindi nell'insieme Worksheets, punterò al foglio con quel Name.


Public Sub m_3()
    Worksheets("Dati").Range("A1").Value = "Ciao"
End Sub

Altro modo per scrivere la macro qui sopra è quello di utilizzare la programmazione ad oggetti; creo una variabile di tipo Worksheet e metto un riferimento che punta al Foglio1. Ecco la macro (che deve trovarsi in un modulo standard):


Public Sub m
    'dichiaro la variabile oggetto di tipo Worksheet
    Dim sh As Worksheet
    'metto il riferimento al Dati
    Set sh = Worksheets("Dati")
    sh.Range("A1").Value = "Ciao"
    'Set a Nothing della variabile oggetto
    Set sh = Nothing
End Sub

Se facciamo invece riferimento al (Name) fare quanto sopra non ha alcun senso. Foglio1 è già una variabile che punta all'oggetto foglio.


Con l'istruzione With miglioro ulteriormente il codice (guida del vb di Excel per Istruzione With). La macro che segue (che deve trovarsi in un modulo standard) mostra l'utilizzo di With:


Public Sub m
    Dim sh As Worksheet
    Set sh = Worksheets("Dati")
    With sh
         .Range("A1").Value = "Ciao"
    End With
    Set sh = Nothing
End Sub

In tutti i casi mostrati in questa sezione, il nostro codice punta ad una specifica cella di uno specifico foglio.


Cella A1 del Foglio1, ma di quale Workbook?
L'ultima macro della sezione precedente è perfetta se abbiamo un unico file aperto nella sessione di Excel, ma se apriamo un nuovo file di Excel o se abbiamo più files di Excel aperti nella stessa sessione, comincia ad avere problemi. Potremmo avere più file aperti con presente un foglio Dati e vorremmo trasferire valori dall'uno all'altro. La macro precedente non è più sicura in quanto scriverebbe sul foglio Dati del file attivo e non è detto che sia la cosa giusta. Come rimediare a questa cosa? Ancora grazie alla programmazione ad oggetti. La macro che segue(che deve trovarsi in un modulo standard), corregge il problema:


Public Sub m()

    'dichiaro le variabili oggetto di tipo Worksheet e Workbook
    Dim sh As Worksheet
    Dim wk As Workbook
    
    'metto un riferimento al Workbook che contiene la macro
    Set wk = ThisWorkbook
    'metto il riferimento al foglio Dati di questo Workbook
    With wk
        Set sh = .Worksheets("Dati")
    End With
    
    With sh
         .Range("A1").Value = "Ciao"
    End With
    
    'Set a Nothing delle variabili oggetto
    Set sh = Nothing
    Set wk = Nothing
    
End Sub

A questo punto siamo sicuri che il nostro riferimento è alla cella A1 del foglio Dati del Workbook che contiene la macro.


Gruppi di celle.

Possiamo applicare la programmazione ad oggetti a gruppi di celle. Il codice della macro che segue (che deve trovarsi in un modulo standard) crea un riferimento alle celle del Range A1:A5 del foglio Dati, quindi nel primo ciclo For Each andrà a mettere un valore in ciascuna cella, mentre il secondo ciclo scriverà in una TextBox il riferimento a ciascuna  cella ed il suo contenuto. Il codice è commentato passo passo:


 Public Sub m()
   
    'dichiaro le variabili oggetto
    Dim wk As Workbook
    Dim sh As Worksheet
    Dim rng As Range
    Dim c As Range

    'dichiaro una variabile per incrementare
    'il valore nelle celle
    Dim lCont As Long
    'dichiaro la variabile s nella quale
    'andrò a mettere i valori ciclati nel Range
    'per riportarli nella MsgBox
    Dim s As String
   
    'inizializzo a 1 la variabile lCont
    lCont = 1
   
    'metto i riferimenti ai vari oggetti;
    'al workbook che contiene il codice
    Set wk = ThisWorkbook
   
    'al foglio dati
    Set sh = wk.Worksheets("Dati")
   
    'al Range A1:A5 del foglio Dati
    Set rng = sh.Range("A1:A5")
   
    'primo ciclo For Each che andrà a
    'scrivere nelle celle
    For Each c In rng
        c.Value = "Dato " & lCont
        'aumento di 1 il valore di lCont
        lCont = lCont + 1
    Next
   
    'secondo ciclo For Each che mette nella
    'variabile s riferimento e valore delle celle ciclate
    For Each c In rng
        s = s & "Foglio: " & sh.Name & " - " & _
            "Rif.Cella: " & c.Address & ": " & c.Value & _
            vbNewLine
    Next
   
    'richiamo la MsgBox e mostro il risultato
    MsgBox s
   
    'Set a Nothing delle variabili oggetto
    Set c = Nothing
    Set rng = Nothing
    Set sh = Nothing
    Set wk = Nothing

   
End Sub




Figura 4. Il risultato della macro.


Come potete notare, ho dichiarato una variabile c di tipo Range. Questa variabile viene utilizzata in For Each per ciclare il Range rng. Semplificando, in questo ciclo For Each la variabile c ha lo stesso impiego della variabile numerica che utilizziamo nei normali cicli For.


Una nota sul Set a Nothing delle variabili oggetto, che altro non è che un'istruzione che serve a liberare la memoria impegnata dagli oggetti. In Excel non è sempre necessario fare il Set a Nothing delle variabili oggetto. Le varie Sub vengono eseguite in un contesto di memoria riservato e all'uscita della Sub (End Sub) tutta la memoria utilizzata viene rilasciata automaticamente. In alcuni casi questo però non avviene e possono rimanere in memoria riferimenti/oggetti/file/altro. E' buona norma fare sempre il Set a Nothing degli oggetti quando escono dallo scopo, cioè tralasciando termini tecnici, non servono più.


Attenzione! L'utilizzo della programmazione ad oggetti in questo articolo Wiki è solo introduttivo. Questo modo di programmare aiuta a creare un contesto pienamente gestito ma può portare a nuovi errori se non si tengono presenti alcune cose. Proprio sull'utilizzo di Set a Nothing degli oggetti e i problemi che possono presentarsi, leggere questo articolo:


Foglio e gruppi di fogli.

Abbiamo già visto come sia possibile determinare su quale foglio eseguire delle operazioni creando un riferimento:


'.....

Dim sh As Worksheet

'.....

Set sh = ThisWorkbook.Worksheets("Dati")

'.....


Adesso tre esempi che riguardano foglio/fogli. Come sia possibile:

  • ciclare l'insieme Worksheets dei fogli di un Workbook
  • ciclareun nostro insieme di fogli
  • l'esclusione di un foglio dell'insieme Worksheets dal risultato del ciclo


Prima macro: cicliamo tutti i fogli dell'insieme Worksheets del Workbook:


Public Sub m()

    Dim sh As Worksheet
    Dim s As String
   
    For Each sh In ThisWorkbook.Worksheets
        s = s & sh.Name & " - "
    Next
   
    MsgBox s
   
    Set sh = Nothing
   
End Sub



Figura 5. Il risultato del ciclo per tutti i fogli dell'insieme Worksheets.


Come abbiamo visto in precedenza nel ciclo For Each che riguardava un Range, dichiaro una variabile del tipo dell'oggetto contenuto nell'insieme che vado a ciclare: in questo caso Dim sh As Worksheet.



Seconda macro. Creiamo un nostro insieme Sheets da ciclare, andando a specificare quali sono i fogli che faranno parte del nuovo insieme. Da qui in poi ho aggiunto una semplice correzione delle eccezioni (o errori: qui un articolo introduttivo):


Public Sub m()
   
    Dim shs As Sheets
    Dim sh As Worksheet
    Dim s As String
 
On Error GoTo RigaErrore
  
    Set shs = ThisWorkbook.Worksheets(Array("Dati", "Foglio2", "Foglio6"))
   
    For Each sh In shs
           s = s & sh.Name & " - "
    Next
   
    MsgBox s


RigaChiusura:

    Set sh = Nothing
    Set shs = Nothing
    Exit Sub
   
RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura
 
End Sub



Figura 6. Il risultato della macro.


Da notare come non utilizzi l'insieme Worksheets ma Sheets. Sheets, a differenza di Worksheets, contiene non solo i fogli di lavoro, ma anche i fogli con i grafici, i fogli con le macro Excel4.0 e i fogli di dialogo Excel 5.0. Non è possibile questa sintassi: Dim shs As Worksheets.


Posso ottenere lo stesso risultato con questa macro:


Public Sub m()
   
    Dim sh As Worksheet
    Dim s As String
 
On Error GoTo RigaErrore
    
    For Each sh In ThisWorkbook.Worksheets(Array("Dati", "Foglio2", "Foglio6"))
        s = s & sh.Name & " - "
    Next
   
    MsgBox s


RigaChiusura:
    Set sh = Nothing
    Exit Sub
   
RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura
 
End Sub


In questo caso ciclo un array (di Worksheet) creato al momento e al quale non ho messo nessun riferimento. Posso anche utilizzare qui e nelle macro precedenti, non il nome del foglio ma la sua posizione nell'array Worksheets, posizione che corrisponde a quella dei fogli lato celle:


'.....

    For Each sh In ThisWorkbook.Worksheets(Array(1, 2, 6))
        s = s & sh.Name & " - "
    Next

'.....



Terza macro. Un modo per escludere tre fogli (Dati, Foglio3 e Foglio5) dal risultato di un ciclo:


Public Sub m()
   
    Dim sh As Worksheet
    Dim s As String
 
On Error GoTo RigaErrore
    
    For Each sh In ThisWorkbook.Worksheets
        Select Case sh.Name
            Case "Dati", "Foglio3", "Foglio5"
                'non fare nulla
            Case Else
                s = s & sh.Name & " - "

        End Select
    Next
   
    MsgBox s


RigaChiusura:
    Set sh = Nothing
    Exit Sub
   
RigaErrore:
    MsgBox Err.Number & vbNewLine & Err.Description
    Resume RigaChiusura
 
End Sub




Figura 7. Alcuni fogli sono stati esclusi dal risultato del ciclo.



Workbook e Workbooks.

La programmazione ad oggetti può (dovrebbe) essere estesa anche agli oggetti Workbook. Fino ad ora abbiamo utilizzato ThisWorkbook nel codice, vediamo di *oggettivare* anche il workbook:


Public Sub m()

    Dim wk As Workbook
    Dim sh As Worksheet

    Set wk = ThisWorkbook
    Set sh = wk.Worksheets("Foglio1")

    With sh
        .Range("A1").Value = "Ciao"
    End With

    Set sh = Nothing
    Set wk = Nothing

End Sub


Faccio notare l'utilizzo di With End With in questo caso.


Mettere riferimenti ad oggetti Workbook (e Worksheet) diventa indispensabile quando si lavora con più file. Nell'esempio seguente, apro e metto riferimento ad un file differente da quello che ospita il codice ed eseguo una semplice operazione di copia dati da celle e fogli con nomi identici. Avendo messo i riferimenti a ciascun oggetto, saprò sempre con quale Workbook, Worksheet e Range avrò a che fare:


Public Sub m()


    Dim wkMe As Workbook
    Dim wkAltro As Workbook

    Dim shMe As Worksheet
    Dim shAltro As Worksheet


    Set wkMe = ThisWorkbook
    Set wkAltro = Workbooks.Open(wkMe.Path & _
        "mioFile.xlsx")

    Set shMe = wkMe.Worksheets("Foglio1")
    Set shAltro = wkAltro.Worksheets("Foglio1")


    shMe.Range("A1").Value = shAltro.Range("A1").Value


    Set shAltro = Nothing
    Set shMe = Nothing
    Set wkAltro = Nothing
    Set wkMe = Nothing


End Sub


Per maggior chiarezza ho omesso l'utilizzo di With End With.

Riferimenti.

Articolo Wiki che illustra come e dove inserire il codice: Visual Basic:

Conclusioni.

Questo è solo un assaggio di cosa si può fare con la programmazione ad oggetti in Excel. Seguiranno, tempo permettendo, altri articoli nei quali vedremo come approfondire ed applicare quanto visto in questo articolo in casi più vicini alla realtà.


Commenti.

Sono graditi i tuoi commenti a questo articolo Wiki e, se lo ritieni interessante, per favore spunta la voce : E' stato utile all'inizio della pagina.

Grazie.

NOTA. Excel non dispone nativamente di procedure assolutamente sicure per impedire la visualizzazione e la modifica del codice vb e/o per la protezione dei fogli. E’ una cosa da tenere sempre presente quando si distribuiscono file con contenuti ritenuti delicati. Ricordate anche che è possibile lanciare il file di Excel senza che vengano eseguite le macro.


Risorse.


 Avvio Pulito di Windows
(courtesy of Microsoft MVP Franco Leuzzi)


Computer infettato da malware (courtesy of Microsoft MVP Vincenzo Di Russo)

Commenti (1)

avanzamento