[Excel VBA] Ricerche e Azioni Multiple su WorkBook

Descrizione :
Un mio esempio pratico su come impostare Ricerche e Azioni conseguenti su tutti i Fogli di un WorkBook.

+ Articolo :

In questo Articolo ho cercato di riunire alcune tecniche che fossero comuni più o meno a tutte le versioni di Excel e relativo VBA. Lo scopo è di eseguire una o più ricerche su tutti i Fogli di un WorkBook, utilizzando il più possibile Oggetti e Metodi base. Una ricerca Cella-per-Cella che sia in grado di eseguire più criteri contemporaneamente, e quindi più azioni da intraprendere, a seconda dei casi e degli esiti delle ricerche stesse.
Il tutto con il massimo controllo possibile, che spesso invece, utilizzando solo funzioni automatiche di Excel, viene perso.

L'esempio si basa sull'utilizzo di UserForm e di Funzioni parametrizzate definite a livello di Modulo.
Questa tecnica garantisce che il codice "di comando" sia eseguito ad un livello superiore rispetto ai moduli VBA dei singoli Fogli, e quindi senza rischio di conflitti o errori, anche in caso di selezioni.

Il WorkBook di esempio è semplice. Basta che contenga un certo numero di Fogli :

Foglio1
Foglio2
Foglio3
...
FoglioN

Su Foglio1 si inserisce un CommandButton ( del tipo ActiveX ) "cmd_formricerca".
In questo caso Foglio1 è escluso dalla ricerca perchè si presume che Foglio1 abbia il ruolo di "Foglio di Comando", e non contenga dati.

--> Codice cmd_formricerca :

Private Sub cmd_formricerca_Click()

    UserForm1.Show

End Sub

I rimanenti Fogli ( Foglio2 --> FoglioN ) conterranno dati organizzati in qualsivoglia struttura, come tabelle, righe o colonne separate da spazi, ecc...

Elemento principale dell'esempio è la UserForm1, da creare e disegnare all'interno dell'Editor VBA.

UserForm1 permetterà di :

1. Decidere se restringere o meno il campo di ricerca su ogni Foglio analizzato, ad un Range che sia effettivamente popolato da dati.

2. Decidere se la determinazione di tale Range-Dati vada fatta automaticamente o abbia dimensioni fisse predefinite.

3. Scegliere quali Fogli siano da escludere/includere nella ricerca.

4. Impostare una keyword e avviare la ricerca.

Struttura e funzionamento possono essere descritti con la seguente figura :

--> UserForm1 :
I controlli utilizzati sono di base e comuni a tutte le versioni di Excel VBA :
--> CheckBox : chk_autour
--> TextBox : txt_ricerca
--> CommandButton : cmd_selt
--> CommandButton : cmd_seln
--> CommandButton : cmd_cerca
--> ListBox : lst_fogli

--> Codice UserForm1 :

Private Const defaultStartC As Long = 1
Private Const defaultStartR As Long = 1
Private Const defaultEndC As Long = 50
Private Const defaultEndR As Long = 100

Private startC As Long
Private startR As Long
Private endC As Long
Private endR As Long

Private Sub cmd_selt_Click()

    Dim i As Integer
    For i = 0 To lst_fogli.ListCount - 1
        lst_fogli.Selected(i) = True
    Next i

End Sub

Private Sub cmd_seln_Click()

    Dim i As Integer
    For i = 0 To lst_fogli.ListCount - 1
        lst_fogli.Selected(i) = False
    Next i

End Sub

Private Sub UserForm_Initialize()

    Dim i As Integer
    With lst_fogli
        .ListStyle = fmListStyleOption
        .MultiSelect = fmMultiSelectMulti
        
        For i = 2 To ThisWorkbook.Worksheets.Count
            .AddItem (ThisWorkbook.Worksheets(i).Name)
            .Selected(.ListCount - 1) = True
        Next i
    End With

End Sub

Private Sub cmd_cerca_Click()

    If txt_ricerca.Text = "" Then
        MsgBox "La Text Ricerca non può essere vuota.", vbExclamation, "Errore"
        Exit Sub
    End If
    Dim strRicerca As String
    strRicerca = txt_ricerca.Text
    
    Dim c As Long
    Dim r As Long
    Dim msgr As VbMsgBoxResult
    
    Dim WS As Worksheet
    For i = 0 To lst_fogli.ListCount - 1
    
        If lst_fogli.Selected(i) = True Then
            Set WS = ThisWorkbook.Worksheets(lst_fogli.List(i))
           
            If chk_autour.Value = Checked Then
            
                '--------------------------------------------------
                'Qui il metodo di determinazione del Range Dati ...
                
                With WS.UsedRange
                    startC = .Column
                    endC = startC + .Columns.Count - 1
                    startR = .Row
                    endR = startR + .Rows.Count - 1
                End With
                
                '--------------------------------------------------
                
            Else
                startC = defaultStartC
                startR = defaultStartR
                endC = defaultEndC
                endR = defaultEndR
            End If
    
            For r = startR To endR
            
                For c = startC To endC
                
                    '----------------------------------------------------------
                    'Qui il blocco Condizioni / Azioni della cella corrente ...
                
                    'CondizioneAzione1
                    If CondizioneAzione1(WS, r, c, strRicerca) = True Then Exit Sub
                    
                    'CondizioneAzione2
                    CondizioneAzione2 WS, r, c, strRicerca
                    
                    '...
                    
                    'CondizioneAzioneN
                    
                    '...
                    
                    '----------------------------------------------------------
                
                Next c
                
            Next r
        End If
        
    Next i
   
    MsgBox "Ricerca terminata"

End Sub

I due "blocchi" di codice evidenziati e delimitati da commenti sono :
- Il blocco in cui inserire il metodo con cui il Range Dati viene definito : nell'esempio viene usato uno dei più immediati, che è .UsedRange dell'Oggetto WorkSheet, ma non è detto sia l'unico possibile.
- Il blocco in cui inserire le Condizioni da verificare e le Azioni conseguenti. Ogni Condizione/Azione corrisponde ad una Function o Sub parametrizzata e definita in un Modulo.

--> Codice Modulo :

Public Function CondizioneAzione1(ByVal WS As Worksheet, ByVal indiceR As Long, ByVal indiceC As Long, ByVal arg As String) As Boolean

    Dim msgr As VbMsgBoxResult
    If InStr(1, WS.Cells(indiceR, indiceC).Text, arg) > 0 Then
        WS.Select
        WS.Cells(indiceR, indiceC).Select
        msgr = MsgBox("Continuare la ricerca ?", vbYesNo, "Domanda")
        If msgr = vbNo Then
            MsgBox "Ricerca interrotta"
            CondizioneAzione1 = True
        Else
            CondizioneAzione1 = False
        End If
    End If

End Function

Public Sub CondizioneAzione2(ByVal WS As Worksheet, ByVal indiceR As Long, ByVal indiceC As Long, ByVal arg As String)

    Dim words() As String
    Dim word As Variant
    Dim FileNumber As Integer
    If InStr(1, WS.Cells(indiceR, indiceC).Text, arg) > 0 Then
        words = Split(WS.Cells(indiceR, indiceC).Text, " ")
        For Each word In words
            If IsNumeric(word) Then
                FileNumber = FreeFile
                Open ThisWorkbook.Path & "\report.txt" For Append As #FileNumber
                Print #FileNumber, Now & " - " & WS.Cells(indiceR, indiceC).Text
                Close #FileNumber
                Exit For
            End If
        Next word
    End If

End Sub

- Il primo è un esempio con una Function : la keyword viene cercata all'interno della cella ( Instr() ), perciò se la cella contiene la keyword anche come sottostringa, la ricerca da esito positivo.
In questo caso il valore Boolean restituito viene usato dal codice principale per decidere se continuare o meno con la ricerca.

- Il secondo esempio è una Sub : la keyword viene cercata allo stesso modo, ma se la ricerca da esito positivo viene eseguito un secondo test su tutte le parole della cella. Se una di esse è di tipo numerico, il risultato viene accodato in un file di testo.

Risulta abbastanza evidente come sia possibile estendere l'esempio a molti altri casi, con ricerche e operazioni difficilmente ( o per nulla ) ottenibili utilizzando le consuete procedure di ricerca...

+ Fine Articolo.

---

Un Click su "Mi Piace" è il modo migliore per ringraziare l'autore di questo articolo.

---

Posted in: Excel VBA