Corso VBA

[LoryOne]

Il codice seguente è ancora meglio:

Function Max2 (Value1, Value2) 
Max2=Value1
If Value2 > Value1 Then Max2 = Value2

In pratica, impostate già un valore il quale cambia solo al verificarsi della condizione specifica.

Qualora il loop impegnasse tanto il processore da rendere il S.O. meno reattivo, aggiungete DoEvents nel ciclo for...next

For i = 1 To 1000000
...
DoEvents
Next

In ultimo, nei sistemi a 32 bit utilizzare long invece di integer risulta più performante.

[Alexsandra]

Ottima segnalazione

[Alexsandra]

Gestione Eventi di foglio di lavoro con Un Modulo Di Classe

Quando si utilizzano dei controlli in un foglio di lavoro, spesso si ha la necessità di utilizzare gli eventi per gestire le azioni che possono essere eseguite. Al tempo stesso se si usano diversi controlli, può diventare problematico far interagire i vari controlli in quanto si deve aggiungere una o più sub evento per ognuno di essi. Per semplificarne la programmazione si può usare un modulo di classe con una singola routine evento per una serie di controlli uguali

Possiamo capirne meglio il significato vedendo un esempio partendo da un foglio di lavoro in cui inseriamo due serie di pulsanti di opzione tramite il percorso dal menu Sviluppo – Inserisci e scegliamo il pulsante di Opzione dalla sezione Controlli ActiveX

Fig. 1

Una volta inseriti e allineati i vari controlli, per poter farli lavorare come due gruppi separati di pulsanti, dovremmo assegnare due GroupName diversi, rispettivamente Test1 e Test2 tramite la modalità Progettazione e la finestra delle proprietà che troviamo nella scheda Sviluppo

Fig. 2

In questo esempio faremo in modo che il pulsante di opzione selezionato diventi rosso mentre quelli non selezionati saranno di colore verde, inoltre mandiamo anche un avviso a video che riporti il nome del pulsante selezionato

Fig. 3

Iniziamo inserendo un modulo di classe, entrando nell’editor di VBA dal menu Inserisci – Modulo di classe. Una volta inserita selezioniamo la classe e nella finestra delle proprietà, cambiamo il suo nome predefinito da Class1 a clsTest

Fig. 4

Ora, nella finestra del codice digitiamo queste righe:

Option Explicit
Private WithEvents testOpt As MSForms.OptionButton

Tramite la parola chiave WithEvents possiamo specificare che una variabile dichiarata (testOpt) si riferisca a un'istanza di una classe in grado di generare eventi. Infatti dopo aver fatto questo, si sarà in grado di selezionare la variabile testOpt dall'elenco a discesa nella parte superiore sinistra della finestra del codice

Fig. 5

Una volta selezionata, per impostazione predefinita, nella finestra del codice appare l’evento click in questo modo

Private Sub testOpt_Click()

End Sub

Se ora si fa clic sul menu a discesa a destra, verranno elencati tutti gli eventi disponibili per questo tipo di controllo attraverso il modulo di classe e selezioniamo l'evento Change dal menu a discesa e rimuoviamo l'evento click

Fig. 6

E otterremo qualcosa di simile:

Option Explicit
Private WithEvents testOpt As MSForms.OptionButton

Private Sub testOpt_Change()

End Sub

Si diceva di cambiare il colore del controllo selezionato e, naturalmente, anche cambiare il colore del controllo deselezionato, inoltre si voleva anche un messaggio che avvisi quale controllo è stato selezionato. A tal proposito ho pensato di usare questo codice nel modulo di classe

Private Sub testOpt_Change()
    If testOpt.Value = 0 Then
        testOpt.Object.BackColor = RGB(0, 255, 0)
    Else
        MsgBox "Hai selezionato il pulsante " & testOpt.Caption & "   Del gruppo " & testOpt.GroupName
        testOpt.Object.BackColor = RGB(255, 0, 0)
    End If
End Sub

Quando si clicca su un pulsante di opzione per selezionarlo, sia il pulsante selezionato e i pulsanti deselezionati eseguiranno i loro eventi di modifica, quindi questa Sub Evento sarà eseguita due volte, una volta per il controllo selezionato e una volta per il controllo deselezionato, il primo avrà valore 1 e il secondo valore 0. Tutto ciò che deve essere fatto ora è quello di collegare i controlli sul foglio di lavoro al modulo di classe. Per prima cosa si deve scrivere del codice nel modulo di classe che riceverà l'oggetto che sta per "ascoltare" da una routine di inizializzazione

Public Property Set Control(obtNew As MSForms.OptionButton)
    Set testOpt = obtNew
End Property

E infine distruggiamo la classe per liberare risorse

Private Sub Class_Terminate()
    Set testOpt = Nothing
End Sub

Ora abbiamo bisogno di creare le istanze di questo modulo di classe per quanti controlli abbiamo da collegare. Inseriamo un modulo normale nel progetto e useremo una variabile Collection per contenere le istanze dei moduli di classe:

Dim testC As Collection

Poi con un ciclo scorriamo tutti gli oggetti del foglio di lavoro e agganciamo i controlli OptionButton all'evento, ecco il codice per l'intero modulo:

Option Explicit
Dim testC As Collection

Sub InitializeEvents()
    Dim ctrOpt As OLEObject
    Dim Fgl As Worksheet
    Dim clsEvents As testOpt
    Set Fgl = ThisWorkbook.Worksheets(1)
    If testC Is Nothing Then
        Set testC = New Collection
    End If
    'Ciclo attraverso tutti i controlli
    For Each ctrOpt In Fgl.OLEObjects
        If TypeName(ctrOpt.Object) = "OptionButton" Then
            'Creiamo una nuova istanza della classe
            Set clsEvents = New testOpt
            Set clsEvents.Control = ctrOpt.Object
             'Aggiungiamo l'istanza ad una collezione
             
            testC.Add clsEvents
        End If
    Next
End Sub

Sub TerminateEvents()
    'distruggiamo la classe per liberare memoria
    Set testC = Nothing
   
End Sub

Il passo finale è quello di assicurarsi che il codice della routine InitializeEvents venga eseguito all’apertura della cartella, per cui useremo l’evento Workbook_Open nel modulo ThisWorkbook inserendo questo codice

Option Explicit

Private Sub Workbook_Open()
    InitializeEvents
End Sub

Infine, quando la cartella di lavoro viene chiusa o se si vuole fermare l’esecuzione della classe di rispondere agli eventi, è necessario distruggere la classe per liberare risorse con questo codice

Private Sub Class_Terminate()
    Set testC = Nothing
End Sub

Da notare che la routine Class_Terminate verrà eseguita per ciascuna istanza della classe creata con la routine InitialiseEvents. Ovviamente questa sub deve essere eseguita quando la cartella di lavoro si chiude, quindi nel modulo ThisWorkbook, aggiungere:

Private Sub Workbook_BeforeClose(Cancel As Boolean)
    TerminateEvents
End Sub

In conclusione è possibile sostituire una moltitudine di subroutine di eventi da un singolo modulo di classe in combinazione con una routine di inizializzazione. Ciò è particolarmente utile quando si ha un gran numero di controlli su un singolo modulo o foglio di lavoro e vuole eseguire azioni simili su un evento specifico di ogni controllo.

[Alexsandra]

Classi e Oggetti: Introduzione

Il linguaggio di Microsoft Visual Basic utilizza il concetto di classe per identificare o gestire le parti di un'applicazione. Se, per esempio, consideriamo un oggetto come una casa, avrà delle caratteristiche come il tipo (residenziale, condominio, villa, etc.), il numero di camere da letto, il numero di bagni, etc. e tutte queste caratteristiche le utilizziamo per descrivere la casa a qualcuno che vuole acquistarla. In programmazione, per ottenere un tale oggetto, è necessario definire i criteri che lo descrivono. Ecco un esempio:

House
[indirizzo
tipo_di_casa
numero_di_camere
numero_di_bagni
ha_il_garage]

Queste informazioni vengono utilizzate per descrivere la casa e sulla base di questo, la casa (House) si chiama classe. In realtà per descrivere una vera e propria casa, è necessario fornire informazioni più dettagliate per ciascuna delle caratteristiche di cui sopra, come per esempio:

House: Mandela
[indirizzo: V. Libertà 123
tipo_di_casa: Monofamiliare
numero_di_camere: 4
numero_di_bagni: 3
ha_il_garage: Sì]

In questo caso, la casa di nome Mandela non è più una classe, ma è una casa vera e propria esplicitamente descritta, pertanto, Mandela è un oggetto. Sulla base di questo, possiamo definire una classe come una tecnica utilizzata per fornire i criteri per definire un oggetto, che è il risultato di una descrizione basata su una classe.


Le proprietà di un oggetto
Nel nostro esempio della casa, abbiamo usato dei termini per descriverla, come: Indirizzo, Tipo di casa, numero di camere, numero di bagni etc. in programmazione invece le caratteristiche utilizzate per descrivere un oggetto sono indicate come sue proprietà. Mentre la maggior parte degli oggetti forniscono solo le caratteristiche per descriverli, altri possono eseguire azioni, ad esempio, una casa può essere utilizzata per proteggere le persone quando fuori piove. In programmazione, invece, un'azione che un oggetto può eseguire è indicato come metodo. In precedenza, abbiamo definito una classe Casa con le sue proprietà, ma a differenza di una proprietà, un metodo deve visualizzare delle parentesi sul lato destro per distinguerlo da una proprietà. Un esempio potrebbe essere:

House
[indirizzo
tipo_di_casa
numero_di_camere
numero_di_bagni
ha_il_garage
Proteggi_pioggia ()]

Quando un oggetto ha un metodo, per accedere a tale metodo, si deve immettere il nome dell'oggetto, seguito da un punto, e dal nome del metodo con le parentesi. Ad esempio, se si dispone di un oggetto casa di nome Mandela e volete chiedere di proteggere dalla pioggia, si deve digitare:

Mandela.Proteggi_pioggia ()

Questo è indicato anche come richiamare un metodo.

Quando è stato chiesto di eseguire un'azione, un metodo può avere bisogno di uno o più valori con cui lavorare, se un metodo necessita di un valore, tale valore è chiamato argomento. Mentre un certo metodo può avere bisogno di un argomento, un altro metodo potrebbe averne bisogno più di uno e il numero di argomenti di un metodo dipendono dalla sua portata e sono visualizzati tra parentesi.
Supponiamo di avere un oggetto casa e si vuole proteggere il suo contenuto, per svariate ragioni, per cui l'interno deve essere protetto per:

• La pioggia
• La polvere
• Il vento
• Il sole, etc.

Sulla base di questo, potrebbe essere necessario fornire informazioni aggiuntive per indicare perché o come la parte interna deve essere protetta. Per questo motivo, quando tale metodo viene chiamato, queste ulteriori informazioni devono essere fornite, tra le parentesi del metodo. Ecco un esempio:

House
[indirizzo
tipo_di_casa
numero_di_camere
numero_di_bagni
ha_il_garage
Proteggi_pioggia (ragione)]

Come accennato in precedenza, un metodo può essere creato per prendere più di un argomento. In questo caso, gli argomenti sono separati da virgole. Ecco un esempio:

House
[indirizzo
tipo_di_casa
numero_di_camere
numero_di_bagni
ha_il_garage
Proteggi_pioggia (ragione, quando)]

Gli argomenti vengono utilizzati per aiutare l'oggetto ad eseguire l'azione prevista e una volta creato il metodo, può essere utilizzato varie volte e se un metodo necessita di un argomento, quando si richiama, è necessario fornire un valore, altrimenti il metodo non funzionerebbe. Per richiamare un metodo che richiede un argomento, si deve digitare il nome del metodo seguito dalla parentesi aperta "(" e seguito dal valore che sarà l'argomento e seguito da una parentesi chiusa ")". L'argomento che si passa può essere un valore costante, regolare o può essere il nome di un altro oggetto e se il metodo richiede più di un argomento, per richiamarlo, si devono digitare i valori per gli argomenti, nell'ordine esatto indicato, separati l'uno dall'altro da una virgola.

Abbiamo detto che, quando si richiama un metodo che richiede un argomento, è necessario fornire un valore per l'argomento, ma c'è un'eccezione. A seconda di come è stato creato il metodo, può essere configurato per utilizzare un valore di default se non si fornisce un valore, ma non tutti i metodi seguono questa regola.
Se un metodo che accetta un argomento ha un valore di default per questo, allora non c'è bisogno di fornire un valore quando si chiama questo metodo e tale argomento è considerato facoltativo, inoltre gli argomenti che hanno valori predefiniti possono essere utilizzati e non c'è bisogno di fornirli.


Tecniche di Accesso ad un oggetto: L'enunciato Me
Finora abbiamo visto che un oggetto dispone di proprietà e metodi e abbiamo anche visto come accedere a una proprietà di un oggetto. Per esempio, immaginate di avere una classe House definita come segue:

House
[indirizzo
tipo_di_casa
numero_di_camere
numero_di_bagni
ha_il_garage
Proteggi_pioggia ()]

Se si dispone di un oggetto denominato Gino e che è di tipo House, per accedere ad alcune delle sue proprietà, è necessario utilizzare il codice come segue:

Gino.indirizzo
Gino.tipodicasa

Se si sta lavorando all'interno di un metodo della classe, per esempio, se si lavora nel corpo del metodo Proteggi_pioggia, è anche possibile accedere alle proprietà nello stesso modo, questa volta senza il nome dell'oggetto. Ciò potrebbe essere fatto nel modo seguente:

Proteggi_pioggia ()
indirizzo
tipo_di_casa
numero_di_camere
numero_di_bagni
End

Quando si accede ad un membro di una classe all'interno di uno dei suoi metodi, è possibile precedere il membro con l'oggetto Me preceduto dall'operatore . (punto). Ecco un esempio:

Proteggi_pioggia ()
Me. indirizzo
Me. Tipo_di_casa
Me. Numero_di_camere
Me. Numero_di_bagni
End

Ricordate che l’oggetto Me viene utilizzato per accedere ai membri di un oggetto mentre si è all'interno di un altro membro dell'oggetto.


Il ciclo With

Abbiamo visto che è possibile utilizzare il nome di un oggetto per accedere ai suoi membri. Ecco un esempio:

Gino. indirizzo
Gino. tipo_di_casa
Gino. numero_di_camere
Gino. Numero_di_bagni

Invece di utilizzare il nome dell'oggetto ogni volta, è possibile avviare una sezione con la parola chiave With seguita dal nome dell'oggetto con espressione:

With Gino
    
End With

Tra le parole chiave With e End With si inseriscono le linee di codice per accedere al membro della classe preceduto da un punto seguito dal membro desiderato. Ciò dovrebbe essere fatto nel modo seguente:

With Gino
.indirizzo
.tipo_di_casa
.numero_di_camere
.numero_di_bagni
.ha_il_garage 
End With

[Alexsandra]

Oggetti e Collezioni nei moduli di classe

Una raccolta è una serie di elementi in cui ogni elemento ha le stesse caratteristiche, in altre parole, tutti gli elementi possono essere descritte nello stesso modo. In programmazione, una collezione è una serie di elementi in cui tutti gli elementi condividono le stesse proprietà e metodi, se presenti. Ad esempio, una raccolta può essere fatta dai dipendenti di una società in cui ogni dipendente può essere descritto con le stesse caratteristiche ad esempio il nome.


Creazione di una collezione
Per supportare le collezioni, il linguaggio Visual Basic è dotato di una classe denominata Collection, in realtà, la Collezione di classe che ci accingiamo a studiare è quella definita in VBA e può essere usata per creare una collezione. Per fare questo, si deve dichiarare una variabile di tipo Collection. Ecco un esempio:

Sub Test()
    Dim dipendenti As Collection
End Sub

Dopo aver dichiarato la variabile, si deve allocare lo spazio in memoria per la stessa, utilizzando la parola chiave Set per assegnare una nuova istanza alla variabile. Ecco un esempio:

Sub Test()
    Dim dipendenti As Collection
    Set dipendenti = New Collection
End Sub

Invece di creare sempre una nuova collezione, a meno che non si debba, VBA è dotato di molte collezioni standard integrate, in modo da evitare di creare sempre la propria collezione e sono indicate come le collezioni incorporate.
Le classi Collection incorporate sono derivati dalla classe Collection di Visual Basic e hanno tutte le loro funzionalità primarie ereditate dalla collezione di classe. Questo significa anche che tutto ciò che menzioneremo per la raccolta classe si applica a qualsiasi raccolta incorporata. Per utilizzare una collezione precostituita, è possibile dichiarare una variabile. Ecco un esempio:

Sub Test()
    Dim CurrentSheets As Worksheets
End Sub

In realtà, quando Microsoft Excel viene avviato, la maggior parte (se non tutte) delle classi sono già disponibili in modo che non c'è bisogno di dichiarare la loro variabile prima di utilizzarle.


Caratteristiche, e operazioni su, una raccolta
L'operazione primaria da effettuare su una collezione consiste nell'aggiungere elementi, a sostegno di questa, la Collezione classe è dotata del metodo Add . La sua sintassi è:

Public Sub Add( _
   ByVal Item As Object, _
   Optional ByVal Key As String, _
   Optional ByVal { Before | After } As Object = Nothing _
)

Questo metodo richiede tre argomenti, ma solo il primo è necessario e la voce "argomento" specifica l'oggetto da aggiungere alla collezione. Ecco un esempio:

Sub Test()
    Dim dipendenti As Collection
    Set dipendenti = New Collection
    dipendenti.Add "Patrizia"
End Sub

Allo stesso modo, è possibile aggiungere il numero di elementi che si desidera:

Sub Test()
    Dim dipendenti As Collection
    Set dipendenti = New Collection
dipendenti.Add ”Patrizia”
dipendenti.Add ”Giacomo”
dipendenti.Add ”Alice”
dipendenti.Add ”Franco”
End Sub

Ricordate che se si sta utilizzando una delle classi standard precostituite, non è necessario dichiarare una variabile, si può solo richiamare il metodo Add per aggiungere un elemento. Ecco un esempio:

Sub Test() 
    Worksheets.Add
End Sub

Accesso a un elemento in una raccolta
Le voci di una raccolta sono organizzate in una sequenza in cui ogni elemento contiene un indice specifico. Il primo elemento dell'insieme possiede un indice di 1, il secondo elemento contiene un indice di 2, e così via. Per accedere alle voci di una raccolta, la collezione di classe è dotata di una proprietà denominata Item. Ci sono due modi per utilizzare questa proprietà, uno utilizzando formalmente il valore della proprietà, digitando il nome dell'oggetto collezione, seguito dall'operatore periodo e seguito da un articolo e le parentesi opzionali. Dopo la proprietà Item o all'interno delle sue parentesi, si deve digitare l'indice della voce desiderata. Ecco un esempio:

Sub Test()
    Dim dipendenti As Collection
    Set dipendenti = New Collection
dipendenti.Add ”Patrizia”
dipendenti.Add ”Giacomo”
dipendenti.Add ”Alice”
dipendenti.Add ”Franco”

dipendenti.Item 2
End Sub

Oppure si possono usare anche le parentesi:

Sub Test()
    Dim dipendenti As Collection
    Set dipendenti = New Collection
dipendenti.Add ”Patrizia”
dipendenti.Add ”Giacomo”
dipendenti.Add ”Alice”
dipendenti.Add ”Franco”

dipendenti.Item (2)
End Sub

Invece di utilizzare la proprietà Item, è possibile applicare l'indice direttamente all'oggetto collezione. Ecco alcuni esempi:

Sub Test()
    Dim dipendenti As Collection
    Set dipendenti = New Collection
dipendenti.Add ”Patrizia”
dipendenti.Add ”Giacomo”
dipendenti.Add ”Alice”
dipendenti.Add ”Franco”

 dipendenti.Item 2
 dipendenti.Item (2)
 dipendenti 2
 dipendenti (2)
End Sub

Tutte queste quattro tecniche (quelle in rosso) consentono di accedere alla voce di cui abbiamo specificato il suo indice


Rimozione di un elemento da una raccolta
In contrasto con l'aggiunta di un nuovo elemento, è possibile eliminare uno. A sostegno di questa operazione, la classe Collection è dotato di un nome metodo Remove. La sua sintassi è:

Public Sub Remove(Index As Integer)

Questo metodo accetta un argomento e quando si richiama, si deve passare l'indice della voce che si desidera eliminare. Ecco un esempio:

Sub Test()
    Dim dipendenti As Collection
    Set dipendenti = New Collection
dipendenti.Add ”Patrizia”
dipendenti.Add ”Giacomo”
dipendenti.Add ”Alice”
dipendenti.Add ”Franco”

 dipendenti.Remove 2
End Sub

Questo codice elimina il secondo elemento della collezione.


Numero di elementi in una Collezione
Quando si inizia una nuova collezione, ovviamente è vuota e il suo numero di elementi è 0. Per tenere traccia del numero di elementi in una collezione, la collezione di classe è dotata di una proprietà denominata Count il cui tipo è un numero intero. Si deve ricordare che tutte le classi di Collection incorporate ereditano il loro comportamento dalla classe Collection, ciò significa che le classi di raccolta incorporate sono dotate di una proprietà denominata Count per contenere il numero di elementi.

Abbiamo visto come è possibile aggiungere nuovi elementi a un insieme e ogni volta che si aggiunge un nuovo elemento alla raccolta, la proprietà Count aumenta di 1. Sappiamo anche come rimuovere un elemento da una raccolta e ogni volta che un elemento esistente viene eliminato, il valore della proprietà Count è diminuito di 1.
In qualsiasi momento, possiamo conoscere il numero di elementi di una collezione, ottenendo il valore della sua proprietà Count

[Alexsandra]

Introduzione alle collezioni in VBA

Le Collection, (o Collezioni) sono una serie di elementi in cui ogni elemento ha le stesse caratteristiche, in altre parole, tutti gli elementi possono essere descritti nello stesso modo. In programmazione, una collezione è una serie di elementi in cui tutti gli elementi condividono le stesse proprietà e metodi, se presenti. VBA fornisce un oggetto Collection che è possibile utilizzare per memorizzare oggetti e dati e dispone di quattro proprietà:

• Add
• Count
• Item
• Remove

Non ci sono restrizioni sul tipo di dati che possono essere memorizzati in un oggetto Collection, e oggetti con tipi di dati diversi possono essere memorizzati nello stesso oggetto Collection. Excel dispone di molte collezioni incorporate, per esempio una cartella di lavoro ha una collezione di fogli, un foglio di lavoro ha un insieme di celle e così via. Possiamo fare riferimento a un foglio di lavoro con il suo indice nella collezione. Per esempio:

ActiveWorkbook.Worksheets (1).Visible

Oppure se il foglio è stato nominato, è possibile anche fare riferimento col nome.

ActiveWorkbook.Worksheets ("Dati"). Visible

Un altro aspetto delle Collection è che sono simili agli array, ma molto spesso viene preferita la collezione rispetto alla matrice. La ragione per cui si desidera lavorare con le collezioni piuttosto che con gli array è che si vuole evitare di dover ridimensionare le matrici ogni volta. Gli array sono fondamentalmente utile solo a patto che si sa, in anticipo, le dimensioni della nostra raccolta dei dati.

Le collezioni e gli array sono entrambi utilizzati con delle variabili di gruppo ed entrambi memorizzano una serie di oggetti simili, ad esempio un elenco di nomi o di paesi, ed è possibile manipolare facilmente e rapidamente un gran numero di elementi. Vediamo ora molto brevemente la differenza tra una variabile normale e un array. Se dobbiamo memorizzare i dati di uno studente si può facilmente farlo con una singola variabile in questo modo

Dim stud As Long
Stud = sheets(“Foglio1”).Range(“A1”)

Tuttavia si avrà da affrontare la manipolazione di dati per più studenti, pertanto immaginate di voler archiviare i dati di 100 studenti, se non è stata utilizzata una collezione o un array si avrebbe bisogno di creare un centinaio di variabili, una variabile per ogni studente. Inoltre un altro problema è che si devono utilizzare queste variabili singolarmente, cioè se si desidera memorizzare 100 valori allora abbiamo bisogno di una riga di codice ogni volta che si desidera memorizzare un valore in una variabile.

Dim stud1 As Long
Dim stud2 As Long
Dim stud3 As Long
stud1 = sheets(“Foglio1”).Range (“A1”)
stud2 = sheets(“Foglio1”).Range (“A2”)
stud3 = sheets(“Foglio1”).Range (“A3”)

Come si può vedere nell'esempio sopra, la scrittura di codice come questo significherebbe dover scrivere centinaia di righe di codice ripetitivo, mentre se si utilizza una raccolta o un array è solo necessario dichiarare una variabile e utilizzare un ciclo per scorrerne tutti gli elementi, per cui con una raccolta è sufficiente una riga per poter leggere ulteriori valori. Se riscriviamo l'esempio precedente utilizzando una collezione, abbiamo solo bisogno di un paio di righe di codice

'crea la collezione
Dim Cstud As New Collection

'Leggi 100 valori della raccolta
Dim Col1 As Range
For Each Col1 In Foglio1.Range("A1:A100")
Cstud.Add Col1.Value
Next Col1

Con questo esempio abbiamo visto quello che le collezioni e gli array hanno in comune, allora, qual è la differenza e perché usare uno al posto dell'altro? La differenza principale è che con una serie normalmente si impostano le dimensioni una sola volta, questo significa che si conosce la dimensione prima di iniziare ad aggiungere elementi. Mi spiego con un esempio. Immaginate di avere un foglio di lavoro con un elenco di studenti e uno studente per riga

Fig. 1

Ora se si desidera memorizzare le informazioni di ogni studente, in questo esempio si può facilmente contare il numero di righe per ottenere il numero di studenti, in altre parole si conosce il numero di elementi in anticipo.

'trova l'ultima riga
Dim stud1 As Long
stud1 = Sheets("Foglio1").Range("A" & Rows.Count).End(xlUp).Row
'Crea un array di dimensione corretta
Dim arr() As Long
ReDim arr(1 To stud1)

Nel codice di esempio, si può vedere che si ottiene il numero di studenti contando le righe, per cui possiamo quindi utilizzare questo sistema per creare un array di dimensioni corrette. Vediamo ora in un secondo esempio in cui non conosciamo il numero di elementi in anticipo e vogliamo estrarre solo gli studenti con un dato criterio. Ad esempio solo gli studenti provenienti dalla Spagna o dall’Italia che studiano la matematica o la storia, in altre parole non sarà come selezionare uno studente e leggere i loro dati dal foglio di lavoro.

Immaginate anche che gli studenti possono essere aggiunti o rimossi dalla lista, quindi il numero di studenti non è fisso e varia, e non si conosce il numero di studenti in anticipo, quindi non sappiamo che dimensione assegnare alla matrice per crearla. Si potrebbe creare un array di grandi dimensioni, ma si avrebbero un sacco di slot vuoti, oppure leggere 50 studenti da un massimo di 1.000, ma allora si avrebbe 950 slot di matrice non utilizzati. Si potrebbe anche ridimensionare la matrice per ogni elemento come viene aggiunto, ma questo metodo è molto inefficiente e piuttosto disordinato, quindi, possiamo utilizzare una Collection in questo modo.

Dim coll As New Collection
coll.Add “Gino”
coll.Add “Mauro”
coll.Remove 1

Quando si aggiunge o si rimuove un elemento in una collezione, VBA fa tutto il ridimensionamento da solo, non è necessario specificare le dimensioni o allocare nuovi spazi, tutto quello che si deve fare è aggiungere un elemento o rimuoverlo. Le raccolte sono molto più facili da usare rispetto agli array soprattutto per chi non ha molta pratica nella programmazione, il più delle volte si fanno tre cose con le collezioni: Si creano, si aggiungono elementi e si leggono le voci

Si deve però tener presente nella scelta del metodo da adottare che le Collezioni hanno anche uno svantaggio, infatti sono procedure in sola lettura, pertanto si può aggiungere o rimuovere un elemento, ma non è possibile modificarne il valore. Se si ha la necessità di cambiare i valori in un gruppo di voci, allora è meglio utilizzare un array. Ora che sappiamo quando e perché utilizzare una collezione diamo un'occhiata a come usarle. È possibile dichiarare e creare in una sola riga del codice una collezione in questo modo

Dim coll As New Collection

Come si può vedere non è necessario specificare le dimensioni e una volta che la collezione è stata creata è possibile aggiungere facilmente elementi, inoltre è possibile dichiarare e quindi creare la collezione, quando se ne ha bisogno.

‘Dichiarazione
Dim coll As Collection
‘crea la raccolta
Set coll = New Collection

La differenza tra questi metodi è che nel primo la raccolta viene sempre creata, mentre nel secondo metodo viene creata solo quando si raggiunge la linea Set, così si potrebbe impostare il codice per creare solo la raccolta se una certa condizione viene soddisfatta

‘Dichiarazione
Dim coll As Collection
‘crea la raccolta se viene trovato un file
If filefound = True Then
Set coll = New Collection
End If

Il vantaggio di questo metodo è minimo, l'allocazione di memoria era un parametro importante tanti anni fa, quando la memoria del computer era limitata, a meno che non si stia creando un enorme numero di collezioni su un PC lento non si noterà alcun beneficio. Utilizzando la parola chiave Set, la raccolta si comporterà in modo diverso rispetto a quando si imposta la raccolta a Nothing, questa istruzione viene usata per rimuovere tutti gli elementi

Set coll = Nothing

Un punto importante da capire è che ciò che fa una collezione dipende da come è stata creata, come abbiamo visto è possibile creare una collezione dichiarandola utilizzando New o utilizzando Set e New. Diamo ora un'occhiata a entrambi i tipi


Creare una raccolta utilizzando New
Quando si aggiunge un nuovo elemento VBA imposta automaticamente la variabile Collection a una raccolta valida, in altre parole se si imposta la raccolta a Nothing vengono svuotati tutti gli elementi e se poi si aggiunge un elemento si avrà una collezione con una sola voce. Ciò rende più semplice per svuotare una raccolta, il codice seguente illustra questo aspetto

Sub Test ()
‘crea la raccolta e aggiungi degli elementi
Dim coll As New Collection
Set coll = Nothing
Coll.Add “Pippo”
End Sub

Creare una raccolta utilizzando Set e New
Quando si utilizza la parola chiave Set per creare una collezione è necessario creare nuovamente la raccolta, se successivamente viene impostata a Nothing, nel codice che segue, dopo aver impostato la collezione a Nothing, si deve poi impostarla di nuovo utilizzando la parola chiave New, se non si esegue questa operazione si ottiene l'errore: "Variabile o Oggetto non impostato".

Sub Test ()
‘crea la collezione
Dim coll As Collection
Set coll = New Collection
Coll.Add “Pippo”

Set coll = Nothing
Set coll = New Collection
Coll.Add “Franco”
End Sub

Rimuovere tutta la Collezione - un metodo alternativo
Il seguente metodo rimuove tutti gli elementi di una collezione, ma è molto lento, il vantaggio è che funziona indipendentemente dal modo in cui si crea la collezione.

Sub elimina (ByRef coll As Collection)
Dim k As Long
For k = coll.Count To 1 Step -1
Coll.Remove K
Next k
End Sub

Aggiunta di elementi a una raccolta
Per aggiungere elementi a una Collezione è possibile farlo utilizzando la proprietà Add seguita dal valore che si desidera aggiungere.

coll.Add “Pera”
coll.Add “Mela”

Quando si aggiungono elementi in questo modo vengono aggiunti al successivo indice disponibile, nell'esempio sopra riportato, Pera si aggiunge alla posizione 1 e Mela alla posizione 2.


I Parametri Before e After
È possibile utilizzare i parametri Before e After per specificare dove si desidera posizionare l'elemento della collezione.

coll.Add “Pera”
coll.Add “Mela”
‘Aggiungere Limone come prima voce
coll.Add “Limone”, Before: =1

Dopo questo codice la collezione è nell'ordine

1. Limone
2. Pera
3. Mela

coll.Add “Pera”
coll.Add “Mela”
‘Aggiungere Limone dopo la prima voce
coll.Add “Limone”, After: =1

Dopo questo codice la collezione è nell'ordine
1. Pera
2. Limone
3. Mela


Accesso agli elementi di una collezione
Per accedere alle voci di una raccolta è sufficiente utilizzare l'indice, che come abbiamo già visto, è la posizione della voce nella raccolta in base all'ordine che sono stati aggiunti. L'ordine può anche essere impostato utilizzando il parametro Before o After.

Sub Test ()
Dim coll As New Collection
coll.Add “Pera”
coll.Add “Mela”

‘ stampare Pera nella finestra Immediata
Debug.Print coll (1)
‘ Aggiungere fragola come prima voce
coll.Add “Fragola”, Before: = 1
‘ stampare Fragola nella finestra Immediata
Debug.Print coll (1)
‘ stampare Pera nella finestra Immediata che ora è nella seconda posizione
Debug.Print coll (2)
End Sub

È inoltre possibile utilizzare la proprietà Item per accedere a un elemento della collezione, che è il metodo predefinito della raccolta in modo che le linee di codice siano equivalenti

Debug.Print coll (1)
Debug.Print coll.Item (1)

Abbiamo detto che non è possibile modificare il valore di un elemento in una collezione, perché quando si accede a un elemento di una raccolta è di sola lettura, per cui se si tenta di scrivere una voce della raccolta si ottiene un errore. Il seguente codice produce un errore "Necessario oggetto"

Sub Test ()
Dim coll As New Collection
Coll.Add “Mela”
‘ questa riga genera un errore
Coll (1) = “Pera”
End Sub

Aggiungere tipi di dati diversi
È inoltre possibile aggiungere diversi tipi di oggetti alla collezione.

coll.Add “Mela”
coll.Add 45
coll.Add N° 12/12/2015 #

Il codice seguente visualizza il tipo e il nome di tutti i fogli della cartella di lavoro corrente.

Nota: Per accedere a tipi di dati diversi è necessario che la variabile sia dichiarata come Variant o avrete un errore.

Si utilizza un ciclo For Each...Next quando si desidera ripetere un set di istruzioni per ciascun elemento di una raccolta o di una matrice.

Sub Test ()
Dim sh As Variant
For Each sh In ThisWorkbook.Sheets 
‘ stampa nella finestra immediate il nome del foglio e il tipo di foglio
Debug.Print TypeName (sh), sh.Name
Next sh
End Sub

Aggiunta di elementi utilizzando una chiave (Key)
È inoltre possibile aggiungere elementi utilizzando una chiave come l'esempio sotto riportato

Coll.Add Item:=45, Key:=”Gino”
Debug.Print "Hai inserito: ",coll("Bill")

Ho incluso i nomi dei parametri per rendere l'esempio più chiaro, tuttavia non è necessario, basta ricordare la chiave, che è il secondo parametro, e deve essere una stringa univoca. Il codice seguente mostra un secondo esempio di utilizzo di chiavi

Sub Test()
Dim coll1 As New Collection
 
    coll1.Add 45, "Gino"
    coll1.Add 67, "Franco"
    coll1.Add 12, "Laura"
    coll1.Add 89, "Bruno"

    Debug.Print coll1("Franco")
    Debug.Print coll1("Bruno")
End Sub

Utilizzando la chiave si hanno tre vantaggi:

• Se l'ordine cambia il codice può accedere lo stesso alla voce corretta
• È possibile accedere direttamente alla voce senza leggere l'intera collezione
• Il codice è più leggibile

Nella collezione VBA delle cartelle di lavoro è molto meglio accedere alla cartella di lavoro con la chiave (il nome) in quanto l'indice è poco affidabile perché dipende da quando sono stati aperti e quindi è molto casuale.


Quando utilizzare le chiavi
Un esempio di quando usare le chiavi è il seguente: Immaginate di avere una collezione di nomi di 10.000 studenti con i loro ID (indici), si potrebbe aggiungere i 10.000 studenti ad una collezione utilizzando il loro ID studente come chiave. Quando si legge un ID dal foglio di lavoro è possibile accedere direttamente al nome dello studente.


Problemi ad usare le chiavi nelle collezioni
Ci sono tre problemi con l'utilizzo delle chiavi nelle collezioni

• Non è possibile controllare se la chiave esiste
• Non è possibile modificare la chiave
• Non è possibile recuperare la chiave

VBA contiene una classe simile alla Collezione chiamata dizionario, che permette di utilizzare sempre le chiavi per aggiungere un elemento. Il dizionario fornisce ulteriori funzionalità per lavorare con le chiavi e se avete bisogno di più funzionalità con le chiavi, si potrebbe trovare il Dizionario molto utile. Tornando alle Collezioni, se si ha bisogno di accedere direttamente a un singolo elemento possono essere molto utili, in caso contrario, non è conveniente usarle.


Accedere a tutti gli elementi di una collezione
Per accedere a tutti gli elementi di una collezione è possibile utilizzare un ciclo For o un For Each. Diamo un'occhiata a questi singolarmente.


Utilizzo del ciclo For
Con un normale ciclo For, si utilizza l'indice per accedere a ciascuna voce. L'esempio seguente stampa il nome di tutte le cartelle di lavoro aperte

Sub Test()
Dim k As Long
For k = 1 To Workbooks.Count
Debug.Print Workbooks(k).Name
Next k
End Sub

Come si può vedere nel codice abbiamo usato come indice 1 con Workbooks.Count, in quanto il primo elemento è sempre in posizione 1 e l'ultimo elemento è sempre nella posizione specificata dalla proprietà Count dell'insieme. Il prossimo esempio stampa tutti gli elementi di una collezione creati dagli utenti.

Sub Test()
'Dichiarare e creare la raccolta
    Dim coll2 As New Collection
 
    ' aggiungere gli articoli
    coll2.Add "Mela"
    coll2.Add "Pera"
    coll2.Add "Fragola"
 
    ' stampare tutte le voci
    Dim i As Long
    For i = 1 To coll2.Count
        Debug.Print coll2(i)
    Next i
End Sub

Utilizzo del ciclo For Each
Il ciclo For Each non usa l'indice ed il formato è mostrato nel seguente esempio

Sub Test()
Dim sh As Variant
    For Each sh In Workbooks
        Debug.Print sh.Name
    Next
End Sub

Il formato del ciclo For è:
For i = 1 To coll2.Count
Next i

dove i è una variabile Long e coll2 è una collezione.
Il formato del ciclo For Each è:

For Each var In coll2
Next

dove var è una variabile Variant e coll2 è una raccolta.

È importante comprendere la differenza tra i due cicli, il ciclo For Each:

• E’ più veloce
• E’ più ordinato da scrivere
• Ha un solo ordine, dal più basso al più alto

Mentre invece Il Ciclo For

• E’ più lento
• E’ meno ordinato da scrivere
• Si può accedervi con un ordine diverso

Se mettiamo a confronto i due cicli il For Each è considerato più veloce rispetto al ciclo For ed è più ordinato da scrivere, soprattutto se si utilizzano cicli annidati e si ha meno probabilità di avere errori. L'ordine del ciclo For Each è sempre dall’ indice più basso al più alto e se si vuole ottenere un ordine diverso, allora si deve utilizzare il ciclo For, dove l'ordine può essere modificato. Inoltre è possibile leggere le voci in senso inverso, leggere una sezione degli articoli o è possibile leggere ogni due fogli.

Sub Test()
'scorrere i fogli da destra a sinistra
Dim i As Long
For i = ThisWorkbook.Worksheets.Count To 1 Step -1
Debug.Print ThisWorkbook.Worksheets(i).Name
Next i
 
'scorrere i primi 3 fogli
For i = 1 To 3
Debug.Print ThisWorkbook.Worksheets(i).Name
Next i
 
'scorrere ogni due fogli
For i = 1 To ThisWorkbook.Worksheets.Count Step 2
Debug.Print ThisWorkbook.Worksheets(i).Name
Next i
End Sub

Le collezioni sono una parte molto utile di VBA, sono più facili da usare rispetto agli Array e sono molto utili quando il numero di elementi cambia. Hanno solo quattro proprietà: Add, Remove, Count e Item e questo li rende molto facile da padroneggiare.

[Alexsandra]

Lavorare con le Date in VBA

Microsoft Excel tramite Il linguaggio Visual Basic, offre una vasta gamma di funzioni per poter creare e manipolare le date ed è possibile inserire una data in qualsiasi formato leggibile, ad esempio usando lo stile americano (mese-giorno-anno) o il formato europeo (giorno-mese-anno), dipende dalle impostazioni internazionali del vostro sistema

Per memorizzare una data o un orario, e necessario definire una variabile di tipo Date e assegnare un valore alla variabile, con una sintassi particolare che prevede di inserire la data tra due caratteri "cancelletto" (#): per esempio, il 10 agosto 2015, può essere scritto:

Sub Prova()
     Dim Mydata As Date
    Mydata = #10/8/2015#
    MsgBox ("La data corrente è : " & Mydata)
End Sub

Ciò produrrebbe:

Fig. 1

Come si può notare nel codice la data è inserita nel formato americano, cioè viene espressa nella forma Mese/Giorno/Anno, perchè, le istruzioni in VBA sono in inglese, ma verrà convertita nel formato inserito nel sistema per cui nel box appare la data scritta all'italiana :10/08/2015.

Nota: La visualizzazione del formato della data dipende dalle impostazioni di settaggio contenute nel Pannello di Controllo seguendo il percorso: Pannello di controllo/Impostazioni Internazionali/Data oppure per Windows 7 o superiori dal percorso: Pannello di controllo/Paese e lingua

Per ottenere la data corrente del computer, è possibile richiamare la funzione Date in questo modo:

Sub Prova()
    MsgBox Date
End Sub

Quando si compone un valore data, è necessario seguire alcune regole che dipendono dalla lingua che si sta utilizzando. Vediamo quelle Italiane, dove un mese è riconosciuto da un indice in un intervallo da 1 a 12 e ha anche un nome che può essere rappresentato in due formati: completo o breve.

Fig. 2

Inoltre una settimana è una combinazione di 7 giorni consecutivi di un mese, per cui ogni giorno può essere riconosciuto da un indice da 1 a 7 e il giorno di ciascun indice è riconosciuto da un nome, dove il primo giorno ha un indice di 1 e si chiama Domenica, mentre l'ultimo giorno ha un indice di 7 si chiama Sabato. Come i mesi di un anno, i giorni di una settimana hanno nomi completi e brevi.

Fig. 3

Abbiamo visto che, durante la creazione di una data, è possibile includere il suo valore tra i segni #. Un'alternativa è quella di fornire una data come una stringa. A sostegno di questa, il linguaggio Visual Basic fornisce una funzione chiamata DateValue la cui sintassi è:

Function DateValue(ByVal StringDate As String) As Variant

Quando si chiama questa funzione, si deve fornire una data valida come argomento. La validità dipende dalla lingua del sistema operativo. Ecco un esempio:

Sub Prova()
    Dim data1 As Date
    data1 = DateValue("22-Ago-2010")
    MsgBox ("La Data impostata è : " & data1)
End Sub

Ciò produrrebbe:

Fig. 4

Un'alternativa a inizializzare una variabile data è quella di utilizzare una funzione denominata DateSerial, la sua sintassi è:

Function DateSerial(ByVal [Year] As Integer, ByVal [Month] As Integer, ByVal [Day] As Integer) As Variant

Come potete vedere, questa funzione consente di specificare l'anno, il mese, il giorno di un valore di data, ovviamente senza i segni #. Quando è stata chiamata, questa funzione restituisce un valore di tipo Variant, che può essere convertita in una data . Ecco un esempio:

Sub Prova()
    Dim data1 As Date
    data1 = DateSerial(2010, 2, 8)
    MsgBox ("La Data impostata è : " & data1)
    End Sub

Ciò produrrebbe:

Fig. 5

Quando si passa il valore di questa funzione, è necessario limitare ogni componente per l'intervallo consentito di valori. È possibile passare l'anno con due cifre da 0 a 99. Ecco un esempio:

Sub Prova()
    Dim data1 As Date
    data1 = DateSerial(3, 2, 8)
    MsgBox ("La Data impostata è : " & data1)
End Sub

Se si passa l'anno come un valore compreso tra 0 e 99, l'interprete rimanda all'orologio del computer per ottenere il secolo. Al momento in cui scriviamo, il secolo sarebbe il 20 e l'anno specificato sarebbe aggiunto, che producono 2003. Per essere più precisi e ridurre la confusione, si dovrebbe sempre passare l'anno con 4 cifre.

Il mese deve essere un valore compreso tra 1 e 12, e se si passa un valore superiore a 12, l'interprete dovrebbe calcolare il resto di quel numero da 12 (il numero MOD 12 =?). Il risultato della divisione intera sarebbe stato usato come il numero di anni e aggiunto al primo argomento. Il resto sarebbe stato usato come il mese del valore data. Ad esempio, se si passa il mese come 18, la divisione intera produrrebbe 1, quindi 1 anno sarebbe stato aggiunto al primo argomento. Il resto è 6 (18 MOD 12 = 6); così il mese sarebbe stato usato come 6 (Giugno). Ecco un esempio:

Sub Prova()
    Dim data1 As Date
    data1 = DateSerial(2003, 18, 8)
    MsgBox ("La Data impostata è : " & data1)
End Sub

Ciò produrrebbe:

Fig. 6

Per fare un altro esempio, se si passa il mese come 226, la divisione intera (226 / 12) produce 18 e il numero sarebbe stato aggiunto al primo argomento (2003 + 18 = 2021). Il resto di 226 a 12 è 10 (226 MOD 12 = 10) e sarebbe stato utilizzato come il mese. Ecco un esempio:

Sub Prova()
    Dim data1 As Date
    data1 = DateSerial(2003, 226, 8)
    MsgBox ("La Data impostata è : " & data1)
End Sub

Ciò produrrebbe:

Fig. 7

Se il mese è passato come 0, si considera 12 (dicembre), mentre se è passato come -1, è considerato 11 (novembre) dell'anno precedente e così via. Se il mese è passato come un numero inferiore a -11, l'interprete avrebbe calcolare la divisione intera a 12, aggiungendo 1 a questo risultato e utilizzando tale numero come l'anno, calcolando il resto a 12, e usandolo come mese.

A seconda del mese, il valore dell'argomento giorno può essere passato come un numero compreso tra 1 e 28, tra 1 e 29, tra 1 e 30, o tra 1 e 31. Se l'argomento giorno viene passato come un numero inferiore a 1 o superiore a 31, l'interprete utilizza il primo giorno del mese passato come secondo argomento. Se il giorno è passato come -1, il giorno è considerato l'ultimo giorno del mese precedente del mese in discussione. Ad esempio, se l’argomento mese è passato come 4 (aprile) e l’argomento giorno è passato come -1, l'interprete avrebbe usato 31 come giorno perché l'ultimo giorno del mese di marzo è 31.

Se l’argomento mese è passato come 3 (marzo) e l’argomento giorno è passato come -1, l'interprete si riferirebbe all’argomento Anno per determinare se l'anno è bisestile o meno. Ciò consentirebbe all'interprete di utilizzare sia 28 o 29 per il valore del giorno. L'interprete utilizza questo algoritmo per qualsiasi valore giorno passato come terzo parametro quando il numero è inferiore a 1.

Se il Giorno argomento è passato con un valore superiore a 28, 29, 30, o 31, l'interprete usa questo stesso algoritmo in senso inverso per determinare il mese e il giorno.


Conversione di un valore in Data
Se si dispone di un valore come quello fornito come una stringa e si desidera convertirlo in una data, è possibile richiamare la funzione CDate la cui sintassi è:

Function CDate(Value As Object) As Date

Questa funzione accetta qualsiasi tipo di valore, ma il valore deve essere convertibile in una data valida. Se la funzione riesce nella conversione, produce un valore Date. Se la conversione non riesce, si verifica un errore.
Come abbiamo visto finora, una data è un valore composto da tre parti: l'anno, il mese e il giorno. L'ordine di questi componenti e il modo in cui sono messi insieme per costituire una data riconoscibile dipendono dalla lingua e vengono definiti nel linguaggio e nelle Impostazioni internazionali nel Pannello di controllo.


L'Anno di una data
Il linguaggio Visual Basic supporta l'anno di una data che va da 1 a 9999. Ciò significa che questa è il Range che si può considerare quando si tratta di date nei fogli di lavoro. Nella maggior parte delle operazioni, durante la creazione di una data, se si specifica un valore compreso tra 1 e 99, l'interprete avrebbe usato il secolo in corso per la sinistra due cifre, ciò significa che, un anno come 4 o 04 indicherebbe l'anno 2004. Nella maggior parte dei casi, per essere più precisi, di solito dovrete specificare sempre l'anno con 4 cifre.
Se si dispone di un valore di data e si vuole scoprire l’anno, è possibile richiamare la funzione Year, la cui sintassi è:

Public Function Year(ByVal DateValue As Variant) As Integer

Come potete vedere, questa funzione assume un valore di data come argomento e dovrebbe contenere una data valida. In caso affermativo, la funzione restituisce l'anno in forma numerica di una data. Ecco un esempio:

Sub Prova()
  Dim data1 As Date
  data1 = #2/8/2004#
  MsgBox ("Sono nata nell'anno " & Year(data1))
End Sub

Ciò produrrebbe:

Fig. 8


Il mese di un anno
La parte del mese di una data è un valore numerico che va da 1 a 12 e quando si crea una data, è possibile specificarlo con 1 o 2 cifre. Se il mese è compreso tra 1 e 9, è possibile precederlo con uno 0. Se si dispone di un valore data e vuole ottenere il suo mese, è possibile chiamare la funzione Month, la cui sintassi è:

Function Month(ByVal DateValue As Variant) As Integer

Questa funzione richiede un oggetto Data come argomento e se la data è valida, la funzione restituisce un numero compreso tra 1 e 12 per il mese. Ecco un esempio:

Sub Prova()
    Dim data1 As Date
    data1 = #2/8/2004#
    MsgBox ("Il mese scelto è  " & Month(data1))
End Sub

Ciò produrrebbe:

Fig. 9

Come già menzionato, la funzione Month produce un valore numerico che rappresenta il mese di una data, se invece si vuole ottenere il nome del mese, è possibile chiamare la funzione MonthName, la cui sintassi è:

Function MonthName(ByVal Month As Integer, Optional ByVal Abbreviate As Boolean = False) As String

Questa funzione richiede due argomenti di cui uno opzionale. L'argomento richiesto deve rappresentare il valore di un mese e se è un valore valido, questa funzione restituisce il nome corrispondente. Ecco un esempio:

Sub Prova()
    Dim data1 As Date
    data1 = #2/8/2004#
    MsgBox ("Il mese scelto è  " & MonthName(Month(data1)))
End Sub

Ciò produrrebbe:

Fig. 10

Il secondo argomento consente di specificare se si desidera ottenere il nome breve o completo del mese. Il valore predefinito è il nome completo e in questo caso il valore predefinito dell'argomento è False, mentre se si desidera ottenere il nome breve, si deve passare il secondo argomento come True . Ecco un esempio:

Sub Prova()
Dim data1 As Date
    data1 = #2/8/2004#
    MsgBox ("Il mese scelto è  " & MonthName(Month(data1), True))
End Sub

Ciò produrrebbe:

Fig. 11


Il giorno di un mese
Il giorno è un valore numerico in un mese e a seconda del mese (e dell'anno), il suo valore può variare da 1 a 29 (febbraio bisestile), da 1 a 28 (Febbraio in un anno non bisestile), da 1 a 31 (gennaio, marzo, maggio, luglio, agosto, ottobre e dicembre), o da 1 a 30 (aprile, giugno, settembre e novembre).
Se si dispone di un valore di data e volete sapere il giorno in un anno, è possibile richiamare la funzione Day, la cui sintassi è:

Function Day(ByVal DateValue As Variant) As Integer

Questa funzione richiede una data come argomento e se la data è valida, la funzione restituisce il giorno numerico del mese dell'argomento data. Ecco un esempio:

Sub Prova()
    Dim data1 As Date
    data1 = #2/8/2004#
    MsgBox ("Il mese scelto è  " & Day(data1))
End Sub

Ciò produrrebbe:

Fif. 12


Il giorno della settimana
Per ottenere il nome del giorno della settimana, è possibile usare una funzione denominata WeekdayName, la cui sintassi è:

Function WeekdayName( ByVal Weekday As Integer, Optional ByVal Abbreviate As Boolean = False, Optional ByVal FirstDayOfWeekValue As Integer = 0) As String

Questa funzione richiede un argomento obbligatorio e due opzionali. L'argomento obbligatorio deve essere, o rappresentare un valore compreso tra 0 e 7. Se si passa come 0, l'interprete si riferisce alla lingua del sistema operativo per determinare il primo giorno della settimana, che in inglese americano è la Domenica. In caso contrario, la funzione riporterebbe il nome corrispondente del giorno. Ecco un esempio:

Sub Prova()
    MsgBox ("Il Giorno scelto è : " & WeekdayName(4))
End Sub

Ciò produrrebbe:

Fig. 13

Se si passa un valore negativo o un valore superiore a 7, si riceverà un errore. Il secondo argomento consente di specificare se si desidera ottenere il nome breve o completo. Il valore di default di questo argomento è False, che produce un nome completo, se si desidera il nome breve, si deve passare il secondo argomento come True. Ecco un esempio:

Sub Prova()
    MsgBox ("Il Giorno scelto è : " & WeekdayName(4, True))
End Sub

Per supportare più opzioni, il linguaggio Visual Basic fornisce la funzione Format () la cui sintassi è:

Function Format( ByVal Expression As Object, Optional ByVal Style As String = "" ) As String

Il primo argomento è la data in cui deve essere formattata e il secondo argomento è una stringa che contiene la formattazione da applicare. Per crearla, si utilizza una combinazione del mese, giorno e anno. Ecco un esempio:

Sub Prova1()
    Dim data1 As Date
    data1 = #12/28/2014#
    MsgBox ("La data è  " & Format(data1, "dd MMMM, yyyy"))
End Sub

Ciò produrrebbe:

Fig. 14

[Alexsandra]

Routine e Function in VBA

Le Subroutine
Una Sub è un componente essenziale del linguaggio VBA ed è definita come un insieme di codici che permettono di eseguire un'azione, ciò significa che VBA esegue le loro istruzioni. Una Sub inizia con la dichiarazione della parola chiave Sub, seguita da un nome e poi seguita da una serie di parentesi e si conclude con una dichiarazione End Sub che può essere digitato, ma appare automaticamente dopo aver digitato il nome della procedura seguito dalla pressione del tasto Invio per andare alla riga successiva. Il codice VBA o le dichiarazioni sono inseriti in mezzo tra le due dichiarazioni

Sub Anagrafica()
  altro Codice ………..
  End Sub

Quando viene assegnato il nome della Sub si deve tenere presente quanto segue:

• Il nome deve iniziare con una lettera e non può contenere spazi.
• Nonostante quanto sopra, gli altri caratteri possono essere lettere, numeri o determinati (ma non tutti) caratteri di punteggiatura. Ad esempio, non è possibile utilizzare i seguenti caratteri: #, $,%, &, @, ^, * e!.
• Periodi (.) e spazi () non sono ammessi.
• Il nome deve essere univoco nella cartella di lavoro corrente.
• Il nome non può essere una parola riservata in VBA.
• Non c'è alcuna distinzione tra lettere maiuscole e minuscole. In altre parole, "A" è uguale a "a".
• Il numero massimo di caratteri che un nome può avere è 255.

Si consiglia inoltre di assegnare un nome descrittivo alla Sub che rispecchi quello che fa il codice in esso contenuto

Quando si crea una procedura Sub, si ha la possibilità di determinare quali altre procedure sono in grado di richiamarla. Questo viene fatto attraverso l'uso delle parole chiave Pubblic o Private che sono uno degli elementi facoltativi delle procedure Sub. Tuttavia, diamo un'occhiata più approfondita a quale sia il significato di queste procedure e come è possibile determinare se una particolare procedura è pubblica o privata.
I termini VBA Private e Pubblic dichiarano l'accesso consentito al termine a cui è associato:

Private Sub: ha impostati i permessi in modo che le Sub nei moduli esterni non possano chiamare questa particolare subroutine, ciò significa che una Sub nel modulo 1 non può utilizzare richiamare una Sub private nel modulo 2. (Nota: se si avvia a livello di applicazione, è possibile utilizzare Esegui per sovrascrivere questa regola e accedere a una Sub private).

Public Sub: questa Sub può essere richiamata o attivata da altre Sub in moduli diversi ed è l'impostazione predefinita per tutte le Sub quindi non è necessario aggiungerlo prima della parola "sub", tuttavia, fornisce ulteriore chiarezza ad altri che potrebbero leggere il codice. Come preferenza personale non scrivo Public Sub a meno che non stia creando un programma complesso che contiene un certo numero di subroutine con vari ambiti (es. Ho un mix di Sub pubblic e private).

Come abbiamo detto le procedure sono un gruppo di istruzioni eseguite nel loro insieme, che istruiscono Excel su come eseguire un compito specifico e l'attività eseguita può essere molto semplice o molto complicata. Tuttavia, è una buona pratica spezzare le procedure complicate in procedure più piccole.


I due principali tipi di procedure sono Sub e Function e la principale differenza tra queste due procedure è la seguente:

• Le procedure Sub eseguono un'azione con Excel, in altre parole, quando si esegue una procedura Sub, Excel esegue qualcosa. Ciò che accade in Excel dipende da ciò che dice il particolare codice VBA.
• Le procedure di funzione eseguono calcoli e restituiscono un valore che può essere un singolo valore o un array.

Una funzione è molto simile a una sub, la principale differenza è che una funzione può restituire un valore, mentre una sub non può. Ci sono altre differenze che vedremo ma questa è la principale. Normalmente si crea una funzione quando si desidera restituire un valore.


Come eseguire una routine
Ci sono molti modi per eseguire una macro, possiamo utilizzare la finestra di dialogo macro, o il tasto di scelta rapida, o in VBE o assegnando la macro a un oggetto. La finestra di dialogo macro è raggiungibile dalla scheda Visualizza della barra multifunzione, cliccando su macro e poi su Visualizza macro che aprirà la finestra di dialogo macro, in cui si deve selezionare il nome della macro e cliccare sul pulsante Esegui per eseguire la Sub/macro.

Fig. 1

Fig. 2

È inoltre possibile aprire la finestra di dialogo macro facendo clic su Macro nel gruppo Codice della scheda Sviluppo sulla barra multifunzione e scegliere Visual Basic Editor

Fig. 3

• In Visual Basic Editor, si deve fare clic all'interno della sub e premere F5 (o fare clic su Esegui nella barra dei menu e quindi fare clic su Esegui Sub/UserForm .
• Oppure, sempre in Visual Basic Editor, si deve fare clic su Strumenti nella barra dei menu, quindi fare clic su macro che apre la finestra di dialogo Macro. Nella finestra di dialogo Macro, selezionare il nome della macro e fare clic su Esegui per eseguire la macro.

Fig. 4

Un buon modo per eseguire una routine (o macro) potrebbe essere quella di fare clic su un pulsante accompagnato da un testo esplicativo posto sul foglio di lavoro, per questo è necessario assegnare la macro a un oggetto, forma, grafico o controllo. È possibile assegnare una macro a qualsiasi controllo agendo dalla scheda Sviluppo sulla barra multifunzione, cliccando su Inserisci nel gruppo Controlli, selezionare e fare clic sul pulsante nei controlli modulo e quindi fare clic sul foglio di lavoro in cui si desidera posizionarlo nell'angolo superiore sinistro del pulsante (è possibile spostare e ridimensionare in seguito).

Fig. 5

Successivamente cliccando col destro del mouse sul pulsante e dal menu a discesa che compare selezionare Assegna macro e si apre la finestra di dialogo Assegna macro dal quale è possibile selezionare e assegnare una macro all'oggetto disegnato.

Fig. 6

E’ possibile assegnare macro a qualsiasi oggetto, forma, immagine, grafico etc. inserito nel foglio di lavoro. Nella scheda Inserisci della barra multifunzione, è possibile inserire un oggetto, forma, immagine, elemento grafico, Tabella, Casella di testo, WordArt, etc. nel foglio di lavoro.


Commento del codice in VBA
All'interno di una procedura, durante la scrittura di codice è possibile contemporaneamente fornire commenti per spiegare lo scopo e ciò che il codice sta facendo. Per differenziare i commenti con il codice, dovete inserire un singolo carattere di apostrofo (') o con la dicitura Rem seguito da uno spazio. I commenti verranno ignorati da VBA in modo che un errore di sintassi nel commento non viene restituito. Si consiglia di utilizzare i commenti nel codice che saranno di grande aiuto a un altro utente nella comprensione, o se si deve effettuare una modifica in un secondo momento. Una volta che si aggiunge un carattere di commento (') all'inizio di una riga è possibile utilizzare la sequenza di continuazione (_) per continuare il commento alla riga successiva e quando si preme il tasto Invio dopo aver digitato un commento, il suo colore diventa verde, come da impostazione predefinita di VBA.

Questa impostazione predefinita del colore del commento può essere modificato andando in VBE, e seguendo il percorso Strumenti – Opzioni

Fig. 7

Fig. 8

e nella finestra visualizzata selezionare la scheda Formato, selezionare il commento del testo nella casella di riepilogo Colori e quindi scegliere il nuovo colore di primo piano. Si noti che il testo di commento non deve necessariamente iniziare all'inizio di una riga, può essere inserita sul lato destro in una riga di codice, lasciando pochi spazi dopo il codice e quindi digitando un singolo apostrofo seguito da commento.



Proseguimento linea del codice VBA
Molte volte durante la scrittura di codice VBA, una sola riga di codice potrebbe diventare molto lunga e nella finestra del codice sarà necessario scorrere verso il lato destro per leggerlo. Per dividere una singola riga in più righe in VBA, è possibile utilizzare la linea come carattere di continuazione che è la combinazione di uno spazio seguito da un underscore (_), usando questo carattere come una interruzione di linea, vi permetterà di passare alla riga successiva e così via, e questo sarà il trattamento di tutte le linee continue come una singola riga nel codice VBA.

Si noti che si può avere un massimo di 25 linee fisicamente, unite con il carattere di continuazione di riga (cioè un massimo di 24 caratteri di continuazione della riga), e di essere trattate come una singola riga logica di codice. Una linea fisica può avere al massimo 1023 caratteri mentre una linea logica può avere un massimo di 10.230 caratteri, in modo che un massimo di 25 linee fisiche possono essere unite pari ad un massimo di 10.230 caratteri.

[Alexsandra]

Prefazione Editor VBA

Di solito si utilizza Microsoft Excel per creare documenti normali utilizzando le impostazioni predefinite dell'applicazione, ma se vogliamo applicare delle funzioni avanzate ad un foglio di calcolo, possiamo utilizzare Microsoft Visual Basic, un’applicazione che si installa automaticamente con Microsoft Excel.

Per creare un foglio di calcolo con funzionalità avanzate, si deve scrivere del codice nell’ambiente di programmazione Visual Basic Applications Edition (VBA). Per sfruttare le funzionalità dell’ambiente VBA, ci sono molte regole da seguire per interagire con il linguaggio


Visual Basic Editor (VBE)
VBE è contenuto nella cartella di lavoro di Microsoft Excel, ed è un ambiente usato per scrivere, modificare ed eseguire il debug di codice VBA che si può avviare in Excel 2007 dai seguenti percorsi:

• Dalla scheda Sviluppo - Visual Basic
• Dalla scheda Sviluppo - Visualizza codice
• Fare clic col destro del mouse sulla scheda del nome foglio in basso, quindi fare clic su Visualizza codice
• Premere la combinazione di tasti Alt + F11

I componenti di Visual Basic Editor si riferiscono alla Finestra del Codice, la Finestra Gestione progetti, la finestra Proprietà e l'area di lavoro di programmazione (es. menu e barre degli strumenti, oggetti, la finestra Immediata e la finestra di controllo). La Finestra del codice è dove scrivere e modificare il codice e le procedure, e anche dove le macro vengono registrate, mentre esplora progetti visualizza l'elenco di tutti i progetti esistenti e fornisce una vista ad albero in cui è possibile comprimere, per nascondere, o espandere, per visualizzare, gli oggetti, i Form e i moduli contenuti in un progetto. Ogni progetto contiene una cartella oggetti, che è il Microsoft Excel Objects, una cartella Forms che contiene i Form, una cartella Moduli che contiene i moduli e la cartella moduli di classe che contiene le classi.

La cartella Objects è sempre presente e contiene un oggetto foglio per ogni foglio di lavoro esistente, e un oggetto il ThisWorkbook e ogni oggetto foglio ha un primo nome che appare prima delle due parentesi, che è il nome del foglio e un secondo nome che compare tra le parentesi che è il nome della scheda del foglio che appare nel foglio di lavoro di Excel. Per visualizzare la finestra di Esplora progetti, si deve cliccare su Visualizza sulla barra dei menu VBE e quindi selezionare Esplora progetti, o premere CTRL + R in VBE.


Moduli in Excel VBE
Le macro VBA (cioè i codici o le procedure) devono risiedere nei loro appositi moduli o Excel non sarà in grado di trovare ed eseguirli, l’oggetto Modulo viene utilizzato per le procedure di evento incorporate in Excel e per creare i propri eventi che sono: Modulo, ThisWorkbook, moduli Sheet (fogli di lavoro e fogli grafici), moduli Form e moduli di classe, in generale il codice VBA nei confronti degli eventi non associati a un particolare oggetto (come la cartella di lavoro o un foglio) vengono inseriti in un modulo di codice standard, una pratica generalmente accettata è quella di piazzare le routine di evento nel modulo ThisWorkbook, nei moduli di fogli e UserForm, pur ponendo altro codice VBA in moduli standard. I moduli di codice standard sono anche indicati come moduli di codice o moduli, e ci possono essere vari moduli (Modulo1, Modulo2 etc.) in un progetto VBA, in cui ciascun modulo può essere utilizzato per coprire un certo aspetto del progetto. Per inserire un modulo si deve cliccare su Inserisci sulla barra dei menu VBE e quindi selezionare Modulo.


Proseguimento linea del codice VBA
Molte volte durante la scrittura di codice VBA, una sola riga di codice potrebbe diventare molto lunga e nella finestra del codice sarà necessario scorrere verso il lato destro per leggerlo. Per dividere una singola riga in più righe in VBA, è possibile utilizzare la linea come carattere di continuazione che è la combinazione di uno spazio seguito da un underscore (_), usando questo carattere come una interruzione di linea, vi permetterà di passare alla riga successiva e così via, e questo sarà il trattamento di tutte le linee continue come una singola riga nel codice VBA.

Si noti che si può avere un massimo di 25 linee fisicamente, unite con il carattere di continuazione di riga (cioè un massimo di 24 caratteri di continuazione della riga), e di essere trattate come una singola riga logica di codice. Una linea fisica può avere al massimo 1023 caratteri mentre una linea logica può avere un massimo di 10.230 caratteri, in modo che un massimo di 25 linee fisiche possono essere unite pari ad un massimo di 10.230 caratteri.


Controllo Automatico Sintassi
Durante la scrittura di codice VBA, il codice che ha un errore di sintassi diventa rosso non appena il cursore si sposta sulla linea se il controllo automatico di sintassi è selezionato (impostazione di default), un controllo della sintassi viene eseguito ogni volta che si sposta il cursore su una nuova linea, e in caso di errore di sintassi una finestra pop-up avverte con un messaggio "errore di compilazione". Se il controllo automatico di sintassi è deselezionato, non sarà più possibile ottenere questi box di avviso, anche se l’errore farà diventare di colore rosso la riga del codice. È possibile deselezionare il controllo automatico di sintassi andando in VBE, seguendo il percorso Strumenti – Opzioni e nella finestra visualizzata, selezionare la scheda Editor, e quindi deselezionare la casella di controllo.

È inoltre possibile modificare il colore rosso di default dell’errore di sintassi andando in VBE, cliccate su Strumenti – Opzioni e nella finestra visualizzata, selezionare la scheda Formato, selezionare sintassi del testo di errore nella casella di riepilogo codice colori, e poi scegliere il nuovo colore di primo piano.


Commento del codice in VBA
All'interno di una procedura, durante la scrittura di codice è possibile contemporaneamente fornire commenti per spiegare lo scopo e ciò che il codice sta facendo. Per differenziare i commenti con il codice, dovete inserire un singolo carattere di apostrofo (') o con la dicitura Rem seguito da uno spazio. I commenti verranno ignorati da VBA in modo che un errore di sintassi nel commento non viene restituito. Si consiglia di utilizzare i commenti nel codice che saranno di grande aiuto a un altro utente nella comprensione, o se si deve effettuare una modifica in un secondo momento. Una volta che si aggiunge un carattere di commento (') all'inizio di una riga è possibile utilizzare la sequenza di continuazione (_) per continuare il commento alla riga successiva e quando si preme il tasto Invio dopo aver digitato un commento, il suo colore diventa verde, come da impostazione predefinita di VBA.

Questa impostazione predefinita del colore del commento può essere modificato andando in VBE, e seguendo il percorso Strumenti – Opzioni

Fig. 1

Fig. 2


Indentare il codice VBA
La procedura di VBA può essere composto da più righe di codice con una serie di dichiarazioni e quando il codice diventa più lungo e complesso, la formattazione del codice con un rientro contribuirà a rendere più facile da leggere, e renderà il debug più semplice. Gli sviluppatori utilizzano in genere un rientro per il codice all'interno delle linee di inizio e fine dei Loop in cui una serie di righe di codice o in un ciclo For Next si usa rientrare per distinguerli come appartenenti ad un particolare procedimento. L'indentazione è molto utile nel codice nidificato in modo che ogni blocco di codice è visivamente separato, e le linee di inizio e fine di ogni blocco sono allineate.

L’Indentazione di solito è fatta premendo il tasto Tab una o più volte, prima di digitare il codice. Si noti che per impostazione predefinita in VBA, premendo il tasto Tab si sposta il cursore di quattro spazi o caratteri a destra. Questa impostazione del valore della scheda può essere modificata andando a VBE, e seguendo il percorso Strumenti – Opzioni e nella finestra visualizzata selezionare la scheda Editor e inserire il nuovo valore nella casella "Larghezza linguetta". Nella scheda Editor della finestra di dialogo Opzioni, è possibile anche selezionare "Rientro tabulazione", che ripeterà il rientro della riga corrente premendo Invio.


Parole chiave di VBA
Le parole chiave sono parole riservate che VBA usa come parte del suo linguaggio di programmazione e sono parole o comandi che sono riconosciuti da VBA e possono essere utilizzati nel codice solo come parte del linguaggio VBA (come in una dichiarazione, il nome della funzione, o l'operatore) e non altrimenti (come i nomi delle sub-routine o variabile). Esempi di parole chiave sono: Sub, End, Dim, If, Next, And, Or, Loop, Do, Len, Close, data, Else, Select, e così via. Per ottenere aiuto su una determinata parola chiave, inserire il cursore del mouse all'interno della parola (nel codice VBA in VBE) e premere F1.

[Alexsandra]

Inserire un Modulo nell’Editor VBA

Per inserire una macro si deve accedere all’editor VBA cliccando sull'icona di Visual Basic nella scheda Sviluppo della barra multifunzione, oppure è possibile premere i tasti ALT + F11 sulla tastiera

Fig. 1

Le macro sono memorizzate in moduli, guardate nella finestra del progetto VBA e se la finestra non fosse visibile si deve seguire il percorso "Visualizza – Gestione Progetti" dal menu. Selezionare poi la cartella di lavoro in cui si desidera inserire il codice, che si possono identificare dal nome della cartella tra parentesi accanto alla dicitura "VBAProject". In questo modo ogni cartella di lavoro aperta avrà una voce nella finestra del progetto VBA

Fig. 2

A questo punto si deve fare clic col pulsante destro del mouse sulla cartella di lavoro in cui si desidera inserire il codice e selezionare "Inserisci" e poi "Modulo" dal menu che appare

Fig. 3

E verrà creato un nuovo modulo:

Fig. 4

Quando si fa doppio clic su un modulo nella finestra di progetto VBA il codice di tale modulo viene visualizzato nella finestra del codice principale, anche se un nuovo modulo normalmente non contiene alcun codice (può contenere "Option Explicit" se si dispone di questa opzione attivata). E’ possibile modificare il nome del modulo cliccando sul nome stesso nella finestra delle proprietà, evidenziata dalla freccia blu in Fig. 4


Creare una macro
Per creare una macro si deve cliccare sul modulo appena creato e nella finestra del codice digitare il nome assegnato alla macro (prova) preceduto dalla notazione Sub e premere. Si dovrebbe ottenere una cosa del genere

Fig. 5

A questo punto alla macro si deve solo aggiungere il codice per svolgere le azioni che vogliamo. Per esempio possiamo far apparire un box a video con un saluto, in questo caso aggiungiamo il codice:
MsgBox “Salve”

Fig. 6

A questo punto dobbiamo solo eseguire la macro che possiamo fare in questo modo:

• Cliccare in qualsiasi punto all'interno della macro
• Selezionare Esegui - Esegui Sub/UserForm

Oppure, sempre posizionando il cursore in un punto qualsiasi della macro, cliccare sull’icona verde segnalata dalla freccia rossa di Fig. 6

Nota: Se il cursore non si trova all'interno di una macro, allora verrà visualizzata una finestra con l’elenco delle macro disponibile viene chiesto di selezionarne una da mandare in esecuzione.

Mandando in esecuzione la macro si ottiene un risultato del genere:

Fig. 7

[A.Maurizio]

Ciao Alessandra stò leggendo con attenzione questo tuo articolo su come scrivere e inserire i propri dati da TXT a Foglio di Excel
Però Provando questo tuo progetto Nel costrutto del (SriviFile) ho potuto constatare che dove c'è Le Parentesi con la virgola mi da errore

CellD = CellD + Trim(ActiveCell(I, j).Value) + “, “

Perche e come posso rimediare a questo errore.
Attendo tue istruzioni Grazie Saluti Sinceri da A.Maurizio