Progress Bar

Ultima modifica: 29-05-2016

A chi lavora molto intensamente con certe applicazioni e, in modo particolare, a chi è abituato a lavorare con applicazioni "fai da te" è consigliato eseguire periodici controlli cardiologici per verificare lo stato delle proprie coronarie.

Infatti, quando lavoriamo con molti dati o quando dobbiamo eseguire complesse elaborazioni dei dati giacenti in tabelle, tabelline o quant'altro, il computer che stiamo usando sembra addormentarsi. Scendiamo al bar, ci facciamo un caffé, ritorniamo dal nostro amato e lo troviamo ancora dormiente.

Ma che succede? E' bloccato il computer, è bloccato il programma che stiamo usando, il programma che stiamo usando è entrato in un loop infinito o semplicemente sta eseguendo miliardi di istruzioni che richiedono solo la pazienza di attendere?

 

Possiamo quindi pensare di progettare qualche cosa che ci mostri l'attività che il nostro VBA sta eseguendo e ci possa in qualche modo tranquillizzare.

In questo contesto vorrei mostrarvi come costruire una Progress Bar in una UserForm usando una Label che avanzerà man mano che il nostro codice andrà avanti nelle sue elaborazioni. Farò sì che questa Label, da lunghezza 0 (zero) venga estesa, passo dopo passo, fino a raggiungere il massimo della sua lunghezza.

 

Questa soluzione ci potrebbe portare senz'altro alla soluzione del problema.

Tuttavia, se eseguiamo l'aggiornamento alla Label per mostrarne l'avanzamento ad ogni istruzione che teniamo sotto controllo, la progressione della barra risulterebbe troppo fine e, soprattutto, rallenterebbe troppo le operazioni già lunghe che stiamo compiendo. Dobbiamo quindi decidere ogni quanto eseguirne l'aggiornamento.

Armiamoci, quindi, di pazienza, prendiamo nota di ciò che ci serve e, muniti di un pallottoliere, prepariamoci ad eseguire alcuni calcoli.

 

Il progetto

In questo contesto dobbiamo conoscere:

 

  1. La lunghezza della Label che usiamo come Progress Bar
    • se ci troviamo nel modulo della UserForm è sufficiente:
      LungLabel = Label1.Width
    • se ci troviamo in un modulo diverso da quello della UserForm occorre modificare l'istruzione in:
      LungLabel = UserForm1.Label1.Width
  2. Il numero di iterazioni che andremo a svolgere:
    • se le istruzioni sono comprese in un ciclo: For A = 1 To NumRec
    • le iterazioni saranno:
      Iterazioni = NumRec
    • se le istruzioni sono comprese in cicli nidificati come:
      For A = 1 To NumRec
      For B = 1 To NumCampi
    • le iterazioni saranno il risultato del prodotto di tutti i limiti massimi dei cicli usati:
      Iterazioni = NumRec * NumCampi
    • se le istruzioni sono comprese in un procedimento di ordinazione del tipo da me adottato:
      For A = 1 To NumRec - 1
      For B = A + 1 To NumRec
    • qui le cose si potrebbero complicare. Io ho risolto con la seguente formula che, non lo nascondo, mi fa un po' paura perchè, nonostante i test a cui l'ho sottoposta, potrebbe avere dei Bug. Tuttavia, in linea di massima il risultato della formula può ritenersi attendibile e non influenza il buon funzionamento della routine.
      Conteggio= (NumRec * NumRec / 2) - (NumRec / 2)
    • se invece dei cicli For ... Next stiamo usando il ciclo For Each ... Next le iterazoni potrebbero essere calcolate come nel caso seguente:
      Set MioIntervallo = Range("A1").CurrentRegion
      Iterazioni = MioIntervallo.Cells.Count
      For Each cl In MioIntervallo
      ecc
    • se vogliamo monitorare il lavoro svolto in più routines sommiamo tra loro le iterazioni calcolate in ogni routine. Nel lavoro che sto per presentare ho risolto così:
      Iterazioni = NumRec * NumCampi * 3 + Conteggio
      dove
      1. il "*3" indica il numero di routines in cui svolgo l'azione, esclusa quella dell'ordinamento
      2. "Conteggio" è il numero di iterazioni calcolato per la routine di ordinamento
  3. Il passo da usare: è sufficiente il semplice calcolo:
    Passo = Iterazioni / LungLabel
  4. Il conteggio delle iterazioni compiute: è sufficiente incrementare, nelle routines da monitorare, il contatore "Conteggio ":
    Conteggio = Conteggio + 1
  5. La progressione del lavoro eseguito da visualizzare come percentuale del lavoro eseguito si ottiene calcolando:
    PercFatto = Conteggio / Iterazioni
    UserForm1.Caption = " " & Format(PercFatto, "0%") & " "
  6. Il valore da utilizzare per l'aggiornamento della Progress Bar
    UserForm1.Label1.Width = PercFatto * LungLabel

Sperando di non aver dimenticato nulla, possiamo passare al nostro progetto.

 

La preparazione del lavoro

Nell'applicativo di esempio, prima di passare alla scrittura del codice, ho dovuto preparate il foglio e la UserForm.

  1. Sul foglio ho collocato un pulsante e, nelle celle H1 e I1 i valori da assegnare alle due dimensioni della matrice su cui voglio lavorare:
    • in H1 possiamo scrivere un valore che può arrivare a qualche migliaio che serve per il dimensionamento verticale (le righe) della matrice
    • in I1 scriviamo un numero che può arrivare a qualche decina che serve per il dimensionamento orizzontale (le colonne) della matrice
  2. per la UserForm ne ho preparata una dotata di:
    • una Label a cui assegno un colore diverso dallo sfondo
    • una Label su cui poter scrivere le azioni che stiamo compiendo
    • un pulsante che viene mantenuto nascosto durante il lavoro e mostrato solo alla fine come visibile in questa immagine

L'aggiornamento della Progress Bar avviene in tre tempi:

  1. nella routine iniziale "Sub Continua" dove inizializzo le variabili "LungLabel, Iterazioni, Passo, Conteggio"
  2. nelle routines
    • Sub riempiMatrice
    • Sub Ordinamento1
    • Sub ScritturaMatrice
  3. dove, con
    Conteggio = Conteggio + 1 incremento il numero delle iterazioni nei cicli
    e con
    If Conteggio Mod (Passo) = 0 Then verifico se "Conteggio" è divisibile per "Passo"
  4. nella routine finale "Sub Scorri" dove effettuo l'incremento della lunghezza della Label.

 

La parte finale: scrittura del codice

Il codice che vado a presentare sembra complesso, ma se si seguono attentamente i passaggi, vedrete che alla fine risulterà più semplice di quel che ci si aspetta. Non per nulla ho cercato di frammentare in varie routines, con mia gran fatica, ognuna con un compito specifico.

Innanzitutto, nel modulo standard come primissime righe, dichiaro pubbliche alcune variabili che mi serviranno in tutto il modulo standard e che serviranno alle varie routines ed attivo la UserForm

Dim Colonna
Dim Matrice()
Dim LungLabel, Iterazioni, Passo, Conteggio
Dim PercFatto, Azione

Sub Primotest()
UserForm1.Show
End Sub

Sub Continua()
Dim A, B
Dim NumRec, NumCampi
Sheets("Foglio1").Select
'cancello l'eventuale contenuto del foglio
Range("B10").CurrentRegion.ClearContents
Range("N10").CurrentRegion.ClearContents
'leggo da 2 celle le dimensioni della matrice che voglio creare
NumRec = Range("H1") 'nell'esempio ho usato 3000 per le righe della matrice
NumCampi = Range("I1") 'nell'esempio ho usato 10 per le colonne della matrice
'calcolo quante azioni compie la routine per l'ordinamento
Conteggio = (NumRec * NumRec / 2) - (NumRec / 2)
'calcolo il numero totale delle reiterazioni
'scrivo "* 3" perchè in tre routines ho gli stessi cicli
'scrivo "+ Conteggio" perchè aggiungo anche le iterazioni compiute nell'ordinamento

Iterazioni = NumRec * NumCampi * 3 + Conteggio
'leggo la lunghezza della label posta sulla UserForm1
LungLabel = UserForm1.Label1.Width
'calcolo ogni quante volte debbo aggiornare la lunghezza della label
Passo = Iterazioni / LungLabel
'inizializzo il contatore delle azioni
Conteggio = 0
'vado alla routine che riempie la matrice con numeri casuali
'l'aggiornamento della Progress Bar avviene all'interno di ciascuna delle seguenti routines

riempiMatrice
'copio la matrice appena creata sul foglio a partire dalla 2^ colonna
Colonna = 1
ScritturaMatrice
'ordino la matrice
Ordinamento1
'copio a fianco della prima la stessa matrice anche dopo l'ordinamento a partire dalla 14^ colonna
Colonna = 13
ScritturaMatrice
'finito il lavoro sono pronto a chiudere la UserForm1 e rendo visibile il pulsante per chiuderla
UserForm1.CommandButton1.Visible = True
End Sub

Sub riempiMatrice()
Dim A, B
Dim NumRec, NumCampi
NumRec = Range("H1")
NumCampi = Range("I1")
'dopo aver rilevato i valori dimensiono opportunamente la matrice
ReDim Matrice(1 To NumRec, 1 To NumCampi)
Randomize
'la riempio con numeri casuali
For A = 1 To NumRec
For B = 1 To NumCampi
Matrice(A, B) = Int(Rnd() * 999) + 1
'incremento il contatore delle iterazioni che compiono i 2 cicli
'se il contatore è un multiplo di "Passo" (Conteggio Mod (Passo) = 0)
'procedo al suo aggiornamento

Conteggio = Conteggio + 1
If Conteggio Mod (Passo) = 0 Then
Azione = "Riempimento matrice"
Scorri 
'è in questa routine che eseguo l'aggiornamento della Label
End If
Next
Next
End Sub

Sub Ordinamento1()
'come si noterà dall'avanzamento della prograss bar, questa è la procedura più lunga
Dim A, B, c, CTemp
Dim NumRec, NumCampi
NumRec = UBound(Matrice, 1)
NumCampi = UBound(Matrice, 2)
For A = 1 To NumRec - 1
For B = A + 1 To NumRec
'incremento il contatore delle iterazioni che compiono i 2 cicli
'se il contatore è un multiplo di "Passo" che rappresenta ogni quante
'volte debbo aggiornare la lunghezza della label procedo al suo aggiornamento

Conteggio = Conteggio + 1
If Conteggio Mod (Passo) = 0 Then
Azione = "Ordinamento della matrice"
Scorri 'è in questa routine che eseguo l'aggiornamento della Label
End If
If Matrice(A, 1) < Matrice(B, 1) Then
'siamo di fronte ad una matrice a due dimensioni ma con molte colonne: se non vogliamo
'allungare la routine con istruzioni ripetute usiamo un ulteriore ciclo per effettuare gli
'scambi spazzolando le colonne della riga su cui ci troviamo

For CTemp = 1 To NumCampi
c = Matrice(A, CTemp)
Matrice(A, CTemp) = Matrice(B, CTemp)
Matrice(B, CTemp) = c
Next
End If
Next
Next
End Sub

Sub ScritturaMatrice()
Dim A, B
For A = 1 To UBound(Matrice, 1)
For B = 1 To UBound(Matrice, 2)
Conteggio = Conteggio + 1
If Conteggio Mod (Passo) = 0 Then
Azione = "Scrittura sul foglio del contenuto della matrice"
Scorri 'è in questa routine che eseguo l'aggiornamento della Label
End If
Cells(A + 9, B + Colonna) = Matrice(A, B)
Next
Next
End Sub

Sub Scorri()
PercFatto = Conteggio / Iterazioni
'siamo fuori dal modulo della UserForm1 quindi è bene usare
'il costrutto "With ... End With"

With UserForm1
'dimensioniamo la lunghezza della label
.Label1.Width = PercFatto * LungLabel
'aggiorniamo l'intestazione della UserForm1
.Caption = " " & Format(PercFatto, "0%") & " "
'scriviamo in una seconda label il tipo di lavoro che stiamo compiendo
.Label2.Caption = Azione
'per questa istruzione si può leggere la guida in linea
DoEvents
End With
End Sub

La routine "Continua": in questa routine inizializzo alcune variabili e chiamo a lavorare le altre routines.

La routine "riempiMatrice" riempie con numeri casuali la matrice che andremo a torturare.

La routine "Ordinamento1" esegue l'ordinamento nella matrice

Anche se uso il mio oramai abituale metodo (bubble-sort), avendo la necessità di dover effettuare lo scambio di numerosi campi, non uso la solita tecnica che vi ho sempre mostrato per effettuare gli scambi, ma faccio uso di un ulteriore ciclo per spazzolare tutte le colonne durante la fase dello scambio. Nell'ultimo aggiornamento all'articolo "Suggerimenti (aggiornabile)" ho segnalato gli articoli che trattano l'ordinamento dei dati.

La routine "ScritturaMatrice" per stampare il contenuto della matrice: viene chiamata dalla routine N° 3 "Sub Continua" due volte: una prima volta prima dell'ordinamento della matrice, una seconda volta dopo il suo ordinamento. Non merita commenti.

L'ultima routine, la "Scorri" è quella che incrementa la lunghezza della Label. Come potete vedere le istruzioni sono pochissime ed useranno questi valori:

  1. "Iterazioni" calcolate sono 4.588.500
  2. "Passo" calcolato è pari a 16.387,5
  3. "Conteggio" sarà: 16388, 32776, 49164, ecc
  4. "PercFatto = Conteggio / Iterazioni" che restituisce la percentuale di lavoro eseguito sino a questo momento da scrivere come intestazione della UserForm: 0,01 / 0,01 / 0,02 / ecc
  5. "PercFatto * LungLabel" che indica la lunghezza della Label che stiamo usando come Progress Bar: 1, 2, 3, ecc

dopo aver usato questi parametri occorre utilizzare "DoEvents" per consentire all'applicazione di ridisegnare lo schermo e l'UserForm mentre l'elaborazione continua

 

Nel modulo relativo alla UserForm vengono scritti due semplici routines:

  1. una nell'evento UserForm_Activate per nascondere il CommandButton e per chiamare la routine che ho chiamato "Continua" per iniziare i lavori e da qui si vanno ad eseguire tutte le altre routines scritte tutte nel Modulo standard del progetto VBA
  2. una nell'evento CommandButton1_Click per chiudere la UserForm
Private Sub UserForm_Activate()
CommandButton1.Visible = False
Continua
End Sub

Private Sub CommandButton1_Click()
Unload UserForm1
End Sub

 

Conclusioni

Spero di essere stato chiaro e, soprattutto, che questa che modestamente potrei definirla una piccola utility possa essere facilmente integrata nei nostri applicativi. A questo scopo, soprattutto, mi sono impegnato nello smembrare quel che diversamente e più comodamente poteva essere scritto in una unica routine.

 

Buon lavoro