Dati univoci prelevati da una tabella e ricerca dei dati corrispondenti

Ultima modifica: 27-05-2016

Questa volta ci dedicheremo ad un Problema leggermente complesso.

Iniziamo con un probabile scenario che potrebbe presentarsi in questa maniera: esempio

Partendo da questa tabella vogliamo eseguire queste operazioni:

Ecco come organizziamo il lavoro:

Sul foglio disegnamo dei controlli prelevati da due barre degli strumenti di excel:

Moduli (da cui preleviamo il pulsante da abbinare alla macro iniziale) (*)

Strumenti di controllo (da cui preleviamo le due combobox e la listbox)

*) Disegnando il pulsante verrà proposta una finestra di dialogo che chiede di assegnagli una macro.
In questa fase si può evitare di eseguire l'assegnazione perchè ancora non abbiamo alcuna macro da assegnargli.
Una volta scritta la macro si può tornare sul foglio, fare clic destro sul pulsante e scegliere la voce Assegna macro

Nelle immagini qui sotto vediamo le barre degli strumenti da cui abbiamo prelevato i controlli, ed i controlli posti sul foglio.

le barre degli strumenti
Nella immagine qui a fianco è da notare:

  • il pulsante Aggiorna per creare i due oggetti Collection e per popolare le due combobox
  • la prima combobox viene popolata dai nomi dei clienti (utenti) raccolti nella Collection "Elenco_cli"
  • la listbox raccoglierà le fatture emesse a nome del cliente scelto
  • la combobox sottostante raccoglierà tutte le fatture elencate nella tabella e raccolte nella Collection "Elenco_fat"
i moduli nel VBA

 

I controlli sul foglio

Dopo questa panoramica del lavoro fatto sul foglio possiamo passare nell'editor visual basic (la strada più breve è ALT + F11).

Qui c'è da fare 3 lavori in 2 moduli differenti (quelli indicati dalle frecce nell'immagine qui sopra:

Nel modulo generale Modulo1: la macro, chiamata Sub Cliente_fattura(), da abbinare al pulsante che serve per creare i due oggetti collection che andranno ad alimentare

Nel modulo Foglio1: due macro abbinate alle due combobox disegnate sul foglio1

Detto questo possiamo passare alla descrizione delle 3 macro necessarie a far funzionare il nostro programmino.

Variabili pubbliche

In questo progetto possiamo desiderare di avere delle variabili pubbliche da poter usare in più moduli ed in più routines.

Queste variabili vengono dichiarate nel Modulo1 del progetto e vengono inizializzate e valorizzate dal pulsante Aggiorna. Forse non servono tutte, ma siccome si riferiscono ad oggetti usati in quasi tutte le routines li mettiamo qui, in questo modulo alle primissime righe prima della dichiarazione di qualsiari routine.

Public Intervallo As Range, Intervallo_dest As Range
Public Elenco_cli As New Collection, Elenco_fat As New Collection

Il pulsante Aggiorna

Questo pulsante è associato alla routine Sub Cliente_fattura() visibile qui sotto e residente nel Modulo1 del progetto.

Con questa routine eseguo queste operazioni, comunque aggiunte come commenti nel codice qui sotto riportato:

**) L'Oggetto Collection accetta i dati in ingresso assegnando loro, di default, un indice che deve essere univoco. In questo caso gli elementi da inserire non sono necessariamente univoci, ma hanno assegnato un indice numerico progressivo.

Nel nostro caso voglio che i dati in esso inseriti siano univoci, quindi non ripetuti. Per fare questo, alla Collection, l'indice glielo fornisco io. Ne consegue che se cerco di assegnare alla Collection un indice già esistente il debugger si lamenta con questo messaggio: Errore di run-time 457: Questa chiave è già associata ad un elemento dell'insieme.

Per questo, per evitare l'interruzione della macro faccio uso del gestore di errori che, intercettando eventuali errori, permette alla macro continui il suo lavoro.


***) L'ordinamento basato su metodo BubbleSort è il più semplice da gestire.

Lavorando in una collection il lavoro è leggermente più complesso perchè occorre eseguire due scambi creando dei doppioni che poi alla fine verranno rimossi


****) Per usare un controllo Activex disegnato su un foglio da un modulo generico occorre referenziarlo:

With Worksheets("Foglio1")
.ComboBox1.Clear ' o altri metodi
End With

Questa la routine residente nel Modulo1 dove sono incluse anche le variabili pubbliche.

Option Explicit
Public Intervallo As Range, Intervallo_dest As Range
Public Elenco_cli As New Collection, Elenco_fat As New Collection

'______________________________________________________________________________________
Sub Cliente_fattura()
Dim Righe, Colonne, Riga, Col_cli, Col_fat, Col_dest_1
Dim R, R1, Swap1, Swap2
Dim Valori As Variant
'   determino l'ampiezza della tabella
Righe = Range("A1").CurrentRegion.Rows.Count
Colonne = Range("A1").CurrentRegion.Columns.Count
'   creo l'intervallo della tabella
Set Intervallo = Range("A1").CurrentRegion.Offset(1, 0).Resize(Righe - 1, Colonne)
'   determino il punto dal quale inizia l'intervallo di destinazione
Col_dest_1 = Colonne + 5
'   creo l'intervallo dove scrivere i risultati delle nostre ricerche
Set Intervallo_dest = Cells(1, Col_dest_1).CurrentRegion
Intervallo_dest.ClearContents
Intervallo_dest(1, 1) = "Clienti"
Intervallo_dest(1, 2) = "Fatt"
Intervallo_dest(1, 3) = "Descrizione"
Intervallo_dest(1, 4) = "Prezzo"
Col_cli = 2
Col_fat = 3
'   inizializzo le due collection
Set Elenco_cli = Nothing
Set Elenco_fat = Nothing
'   per compiere la prossima operazione mi servo del gestore di errori bloccandolo
On Error Resume Next
'   riempio le due collection coi dati prelevati dalla tabella di origine
For R = 1 To Righe - 1
    Elenco_cli.Add Intervallo(R, Col_cli).Value, CStr(Intervallo(R, Col_cli).Value)
    Elenco_fat.Add Intervallo(R, Col_fat).Value, CStr(Intervallo(R, Col_fat).Value)
Next
On Error GoTo 0
'   ordino la collection Elenco_cli
For R = 1 To Elenco_cli.Count - 1
    For R1 = R + 1 To Elenco_cli.Count
        If Elenco_cli(R) > Elenco_cli(R1) Then
            Swap1 = Elenco_cli(R)
            Swap2 = Elenco_cli(R1)
            Elenco_cli.Add Swap1, before:=R1
            Elenco_cli.Add Swap2, before:=R
            Elenco_cli.Remove R + 1
            Elenco_cli.Remove R1 + 1
        End If
    Next
Next
With Worksheets("Foglio1")
'   popolamento della ComboBox1 coi dati della collection Elenco_cli
.ComboBox1.Clear
For Each Valori In Elenco_cli
.ComboBox1.AddItem Valori
Next
'   popolamento della ComboBox3 coi dati della collection Elenco_fat
.ListBox1.Clear
.ComboBox3.Clear
For Each Valori In Elenco_fat
.ComboBox3.AddItem Valori
Next
End With
'   opzionalmente visualizzo i dati contenuti nelle 2 collection
ActiveCell.Activate
For R = 1 To Elenco_cli.Count
Cells(R + 1, Col_dest_1) = Elenco_cli(R)
Next
For R = 1 To Elenco_fat.Count
Cells(R + 1, Col_dest_1 + 1) = Elenco_fat(R)
Next
End Sub

Fatto questo lavoro possiamo già provare la rourine collegata al pulsante "Aggiorna" dopo averlo associato alla macro appena scritta. Premendo sul pulsante possiamo osservare come le due Combobox si popolinoo coi dati attesi.

Passiamo ora alle due Combobox

La rourine per la Combobox1

Qui inizia il primo vero lavoro: Scegliendo un nome si estraggono tutte le fatture a cui quel nome è associato.

Per far partire la routine usiamo l'evento Change della ComboBox1: Private Sub ComboBox1_Change().

*) In VBA di Excel è possibile chiamare quasi tutte le funzioni del foglio di lavoro direttamente tramite l'oggetto Application.

L'oggetto WorksheetFunction rende la maggior parte delle funzioni del foglio di lavoro di Microsoft Excel integrate ed accessibili come metodi anche in VBA. E' possibile accedere all'oggetto WorksheetFunction tramite l'oggetto Application utilizzando la proprietà WorksheetFunction accedendo così ai relativi metodi direttamente. Data la facilità di utilizzo dell'oggetto WorksheetFunction nel codice VBA è possibile utilizzare le funzioni del foglio di lavoro di Excel direttamente dal codice in esecuzione risparmiando codice e lavoro.

Questo il codice abbinato all'evento Change della Combobox1.

Private Sub ComboBox1_Change()
'   clienti
Dim Nome, Col_1, Col_2, Righe, Dest_righe, Colonne, Col_dest_1, R, R1, Somma, Val_min, Val_max
Dim Colonna_Somma As Range
Dim Fat_elenco As New Collection
Col_1 = 2   '   la colonna del cliente
Col_2 = 3   '   la colonna del numero fattura
Righe = Intervallo.Rows.Count   '   dimensioni dell'intervallo principale
Colonne = Intervallo.Columns.Count
Col_dest_1 = Colonne + 5    '   inizio del secondo intervallo: Intervallo_dest
Set Intervallo_dest = Cells(1, Col_dest_1).CurrentRegion
If ComboBox1.Value <> "" Then
    Nome = ComboBox1.Value
    Set Fat_elenco = Nothing    '   creazione della collection delle fatture
    '   vemgono scelte le sole fatture relative al nome scelto
    On Error Resume Next
    For R = 1 To Righe
        If Intervallo(R, Col_1) = Nome Then
            Fat_elenco.Add Intervallo(R, Col_2).Value, CStr(Intervallo(R, Col_2).Value)
        End If
    Next
    On Error GoTo 0
    ListBox1.Clear      '   visualizzazione delle fatture relative al nome scelto
    For R = 1 To Fat_elenco.Count
        ListBox1.AddItem Fat_elenco(R)
    Next
    '   visualizzazione delle voci principali delle fatture relative al nome scelto
    Intervallo_dest.ClearContents
    Intervallo_dest(1, 1) = "Clienti"
    Intervallo_dest(1, 2) = "Fatt"
    Intervallo_dest(1, 3) = "Descrizione"
    Intervallo_dest(1, 4) = "Prezzo"
    R1 = 2
    For R = 1 To Righe
        If Intervallo(R, Col_1) = Nome Then
            Intervallo_dest(R1, 1) = Intervallo(R, Col_1)
            Intervallo_dest(R1, 2) = Intervallo(R, Col_2)
            Intervallo_dest(R1, 3) = Intervallo(R, Col_2 + 3)
            Intervallo_dest(R1, 4) = Val(Intervallo(R, Col_2 + 4))
            R1 = R1 + 1
        End If
    Next
    Set Intervallo_dest = Cells(1, Col_dest_1).CurrentRegion
    Dest_righe = Intervallo_dest.Rows.Count
    Set Colonna_Somma = Intervallo_dest.Offset(1, 3).Resize(Dest_righe - 1, 1)
    '   qui di seguito vengono eseguiti alcuni calcoli
    Somma = Application.WorksheetFunction.Sum(Colonna_Somma)
    Val_min = Application.WorksheetFunction.Min(Colonna_Somma)
    Val_max = Application.WorksheetFunction.Max(Colonna_Somma)
    Colonna_Somma(Colonna_Somma.Rows.Count + 1) = "Riepilogo"
    Colonna_Somma(Colonna_Somma.Rows.Count + 2) = Somma
    Colonna_Somma(Colonna_Somma.Rows.Count + 2, 2) = "Somma"
    Colonna_Somma(Colonna_Somma.Rows.Count + 3) = Val_min
    Colonna_Somma(Colonna_Somma.Rows.Count + 3, 2) = "min"
    Colonna_Somma(Colonna_Somma.Rows.Count + 4) = Val_max
    Colonna_Somma(Colonna_Somma.Rows.Count + 4, 2) = "max"
End If
ActiveCell.Activate
End Sub

La rourine per la Combobox2

Questa è la routine conclusiva per questo progetto.

Per questa il lavoro è più semplice. Ad ogni fattura è abbinato un solo cliente, quindi basta solo scorrere la colonna dei numeri di fattura e trascrivere i dati che sono nelle righe dove viene trovato il numero scelto.

Il lavoro è simile a quello visto nel paragrafo superiore.

Qui di seguito il codice anche per questa Combobox

Private Sub ComboBox3_Change()
Dim Col_1, Col_2, Righe, R, R1, Colonne, Col_dest_1
Dim Dest_righe, Somma, Val_min, Val_max
Dim Colonna_Somma As Range
Dim Num_fatt
Col_1 = 2   '   la colonna del cliente
Col_2 = 3   '   la colonna del numero fattura
Righe = Intervallo.Rows.Count   '   dimensioni dell'intervallo principale
Colonne = Intervallo.Columns.Count
Col_dest_1 = Colonne + 5    '   inizio del secondo intervallo: Intervallo_dest
If ComboBox3.Value <> "" Then
    Set Intervallo_dest = Cells(1, Col_dest_1).CurrentRegion
    Intervallo_dest.ClearContents
    Num_fatt = Val(ComboBox3.Value)
    Intervallo_dest(1, 1) = "Clienti"
    Intervallo_dest(1, 2) = "Fatt"
    Intervallo_dest(1, 3) = "Descrizione"
    Intervallo_dest(1, 4) = "Prezzo"
    R1 = 2
    For R = 1 To Righe
        If Intervallo(R, Col_2) = Num_fatt Then
            Intervallo_dest(R1, 1) = Intervallo(R, Col_1)
            Intervallo_dest(R1, 2) = Intervallo(R, Col_2)
            Intervallo_dest(R1, 3) = Intervallo(R, Col_2 + 3)
            Intervallo_dest(R1, 4) = Val(Intervallo(R, Col_2 + 4))
            R1 = R1 + 1
        End If
    Next
    Set Intervallo_dest = Cells(1, Col_dest_1).CurrentRegion
    Dest_righe = Intervallo_dest.Rows.Count
    Set Colonna_Somma = Intervallo_dest.Offset(1, 3).Resize(Dest_righe - 1, 1)
    Somma = Application.WorksheetFunction.Sum(Colonna_Somma)
    Val_min = Application.WorksheetFunction.Min(Colonna_Somma)
    Val_max = Application.WorksheetFunction.Max(Colonna_Somma)
    Colonna_Somma(Colonna_Somma.Rows.Count + 1) = "Riepilogo"
    Colonna_Somma(Colonna_Somma.Rows.Count + 2) = Somma
    Colonna_Somma(Colonna_Somma.Rows.Count + 2, 2) = "Somma"
    Colonna_Somma(Colonna_Somma.Rows.Count + 3) = Val_min
    Colonna_Somma(Colonna_Somma.Rows.Count + 3, 2) = "min"
    Colonna_Somma(Colonna_Somma.Rows.Count + 4) = Val_max
    Colonna_Somma(Colonna_Somma.Rows.Count + 4, 2) = "max"
End If
End Sub

Come spostare il focus dal controllo appena usato r spostarlo nuovamente sul foglio di lavoro.

Una istruzione usata in tutte le routine e di cui non si è parlato è:

ActiveCell.Activate

Da notare è l'istruzione conclusiva: ActiveCell.Activate che toglie il focus dal comando usato e lo riporta alla cella rpecedentemente selezionata.

Questa istruzione serve per riportare il focus su una cella precedentemente selezionata uscendo dal controllo usato.

Senza questa istruzione il focus rimane sull'oggetto usato e potrebbe essere difficoltoso eseguire altri lavori sul foglio di excel

 

Questo è tutto