In questa pagina è mio intendo scrivere come una collezione di piccoli e rapidi suggerimenti e trucchi per lavorare con minor fatica con l'editor VBA. Perciò questa pagina verrà di tanto in tanto aggiornata.
Regole per le variabili
Per evitare di incorrere nell'errore frequente di scrivere in modo errato le variabili, nella zona dichiarazione delle variabili è buona norma abituarsi ad usare qualche lettera maiuscola nella loro dichiarazione: Dim MiaVariabile e quando è il momento di usarle le possiamo tranquillamente in minuscolo: miavariabile. Questo fa sì che uscendo dal rigo la variabile se è stata digitata correttamente trasforma automaticamente in maiuscole le lettere che nella dichiarazione abbiamo scritto in maiuscolo. Se ciò non accade è perchè abbiamo commesso qualche errore di digitazione (mivariabile, mia_variabile, miavariable, ecc). Anche nello scrivere le parole riservate del codice possiamo scriverle tranquillamente in minuscolo che il codice pensa a trasformarle tranquillamente con le lettere maiuscole al punto giusto.
Scrivendo delle proprietà, dei metodi o degli argomenti di alcune funzioni od oggetti del VBA ci capita di vedere l'elenco delle proprietà o degli argomenti correlati all'oggetto o alla funzione che stiamo istruendo:
Questo è un aiuto che il compilatore vuol darci. Infatti, dopo il punto o, in alcuni casi, dopo uno spazio o una parentesi vediamo comparire questi elenchi.
Ecco come utilizzarli in modo proficuo per velocizzare il nostro lavoro e, soprattutto, per evitare di commettere errori di digitazione:
Autocompletamento forzato
In un punto qualsiasi dell'Editor VBA si può ugualmente avere suggerimenti su cosa scrivere usando la combinazione di tasti Control - Barra Spaziatrice per far apparire a fianco del cursore una finestra con tutti i comandi ed oggetti disponibili con la quale è possibile comportarsi allo stesso modo di quello visto nel paragrafo precedente.
A volte può tornarci utile sapere se una data che stiamo valutando è di un anno bisestile o meno.
Questa la teoria che sostiene la formula seguente:
l'anno deve essere divisibile per 4 (Anno Mod 4 = 0)
gli anni secolari (Anno Mod 100 <> 0) debbono essere divisibili per 400 (Anno Mod 400 = 0)
MioAnno = Year(MiaData)
Bisestile = (MioAnno Mod 4 = 0 And (MioAnno Mod 100 <> 0 Or MioAnno Mod 400 = 0))
In Bisestile avremo uno dei due valori: Vero o Falso ed usando opportunamente questa variabile possiamo controllare agevolmente il risultato che vogliamo ottenere conseguentemente a tale valore.
Questi sono alcuni metodi per cercare l'ultima riga o l'ultima colonna di un intervallo.
Il più noto è quello che fa uso della Proprietà End
Riga = Range("A1").End(xlDown).Row
Partendo da una cella che contiene un qualsiasi valore restituisce il valore dell'ultima cella occupata nell'elenco che si sta analizzando.
E' poi possibile usare anche l'istruzione While...Wend in questa maniera:
URiga = 1
While Cells(URiga, 1) <> ""
URiga = URiga + 1
Wend
Partendo da una cella che contiene un qualsiasi valore restituisce il valore della prima cella libera nell'elenco che si sta analizzando.
Infine è possibile trovare l'ultima riga o l'ultima colonna in un Intervallo ben precisato semplicemente contandone le righe o le colonne per poi lavorare su questo intervallo anzichè sul foglio.
Set Intervallo = Range("A1").CurrentRegion
URiga = Intervallo.Rows.Count
UColonna = Intervallo.Columns.Count
Restituisce il valore dell'ultima cella occupata nell'elenco che si sta analizzando.
Questo suppone di dover poi lavorare con l'intervallo e non più con le celle. Dovunque si trovi l'intervallo è possibile lavorare riferendosi alle celle riferendosi alla cella che sta nell'angolo superiore sinistro dell'intervallo:
Intervallo(1, 1).Select
si riferisce alla cella che sta nell'angolo superiore sinistro dell'intervallo
Intervallo(3, 15).Select
si riferisce alla cella che sta alla terza riga, quindicesima colonna rispetto all'angolo superiore sinistro dell'intervallo
Intervallo(URiga, UColonna).Select
si riferisce all'ultima cella dell'intervallo
I dati che compongono la lista di una ComboBox o una ListBox hanno un identificatore ovvero un indice. I valori di ListIndex sono compresi tra 0 (zero) e un numero minore di un'unità rispetto al numero totale di righe di un elenco comprese nei controlli. Il valore di ListIndex:
Da questo ne consegue che il conteggio degli elementi inizia da 0.
Per leggere il valore di ListIndex si usa la sintassi:
A = ComboBox1.ListIndex
Se riusciamo a leggere la posizione di un elemento in un elenco compreso in una ComboBox o una ListBox e se gli elementi sono stati presi nell'ordine in cui appaiono nel foglio di Excel risulta agevole ricavare la posizione che l'elemento selezionato occupa tra le righe del foglio.
se la lista comincia dalla prima riga del foglio, tenendo conto che la ListIndex del controllo inizia da 0 (zero), è sufficiente la seguente semplice formula:
Riga = ComboBox1.ListIndex + 1
se la lista comincia dalla quarta riga del foglio è sufficiente la seguente:
Riga = ComboBox1.ListIndex + 4
Questo metodo è più semplice è più veloce di quella che saremmo costretti a fare ignorando ListIndex e cioè: selezionato un elemento da una ComboBox o una ListBox andare a cercarlo nel foglio di Excel per esempio con istruzioni del tipo:
Elemento = ComboBox 1.Text
For A = PrimaRiga To UltimaRiga
If Cells(A,1).Value = Elemento Then
Riga = A
Exit For
End If
Next
A volte scrivere da VBA in Excel una data potrebbe creare un problema. Esempio la data verrebbe scritta in modo inatteso o addirittura viene scritta una data inaspettata.
Senza ricorrere a strani artefici si può facilmente ricorrere alla funzione DateSerial che contrariamente alle nostre aspettative (ma non troppo se stiamo attenti ai suggerimenti che il nostro solerte editor VBA ci offre) ha una sintassi particolare.
Nel comporre la data dobbiamo invertire i termini ossia: invece di scrivere A = DateSerial(Giorno, Mese, Anno) basta scrivere
A = DateSerial(Anno, Mese, Giorno)
Alle volte tenere in ordine le Impostazioni Internazionali dal Pannello di Controllo del nostro sistema operativo ci aiuta nella programmazione di VBA o VB. Allora possiamo agire così:
Una volta fatto questo, le impostazioni in ambiente VBA e VB, nonché quelle in Excel verranno adeguate di conseguenza.
Questa nota viene da un recente controllo che ho eseguito su una funzione mandatami in visione.
Questa funziona recitava così:
Attenzione:
Non usare questa funzione prima di aver letto attentamente questo paragrafo
Function Trova(Nome As String, Intervallo As Range) As String
Dim Cella As Range
Dim Trovato As String
For Each Cella In Intervallo
If Cella.Value = Nome Then
Trovato = Cella.Address
Trova = Trovato
Exit Function
End If
Next Cella
End Function
Ovviamente dava dei malfunzionamenti.
Il motivo della confusione è dovuto al fatto che questa funzione personale ha lo stesso nome di una funzione che in Excel esiste già ed è Trova che nulla ha in comune con questa. Ma né il VBA né Excel protestavano.
Non solo, ma controllando la funzione nativa di Excel l'ho trovata corrotta e di conseguenza non la si poteva più usare.
Allora ho provato a correggerla assegnando alla funzione un nome che sicuramente non esiste nel sistema.
Function FaiMiaRicerca (Nome As String, Intervallo As Range) As String
e più in basso
FaiMiaRicerca = Trovato
Solo dopo la correzione del nome della funzione le cose sono tornate a posto, almeno per la funzione.
Ora si può usarla indifferentemente sia da Excel che da VBA in uno dei seguenti modi:
Può capitare a volte di avere la necessità di lasciare il Focus sulla casella di testo dalla quale siamo stiamo uscendo. A volte notiamo che, nonostante tutti i nostri sforzi, questo non succede.
Il Motivo?
Occorre solo trovare l'evento giusto su cui si possa agire con successo. Infatti non si può ottenere l'effetto desiderato con un evento qualsiasi.
Studiando la sequenza degli eventi che vengono generati da una casella di testo dall'ingresso all'uscita, ho rilevato questa sequenza:
| Private Sub TextBox4_Enter() | all'ingresso nella casella di testo |
| Private Sub TextBox4_KeyUp() | |
| Private Sub TextBox4_KeyDown() | alla pressione di un tasto per scrivere un carattere |
| Private Sub TextBox4_KeyPress() | |
| Private Sub TextBox4_Change() | viene visualizzato il carattere digitato |
| Private Sub TextBox4_KeyUp() | |
| Private Sub TextBox4_KeyDown() | premendo il tasto enter o tab per uscire dalla casella di testo |
| Private Sub TextBox4_BeforeUpdate() | |
| Private Sub TextBox4_AfterUpdate() | |
| Private Sub TextBox4_Exit() | |
| Private Sub TextBox5_Enter() | |
| Private Sub TextBox5_KeyUp() | |
| Private Sub TextBox5_Exit() |
Quindi occorre agire prima dell'evento Enter del controllo successivo.
Ma non tutti gli eventi riescono a bloccare il Focus. Quelli con cui sono riuscito ad ottenere successo sono quelli che come argomento hanno:
(ByVal Cancel As MSForms.ReturnBoolean)
e tra tutti gli eventi che sono andato a visitare ce ne sono due che rispondono ai requisiti:
e tra questi due io sceglierei il primo in quanto è il primo ad essere eseguito nella sequenza degli eventi.
A questo punto si potrebbe scrivere una cosa del genere:
Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
If TextBox4 = "s" Then
Cancel = True
TextBox4.SetFocus
End If
End Sub
Questa è una tabella che contiene tutti i codici Ascii del set caratteri disponibili in Windows
| cod | car | cod | car | cod | car | cod | car | cod | car | cod | car | cod | car | cod | car |
|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | · | 32 | [spazio] | 64 | @ | 96 | ` | 128 | · | 160 | [spazio] | 192 | À | 224 | à |
| 1 | · | 33 | ! | 65 | A | 97 | a | 129 | · | 161 | ¡ | 193 | Á | 225 | á |
| 2 | · | 34 | " | 66 | B | 98 | b | 130 | · | 162 | ¢ | 194 | Â | 226 | â |
| 3 | · | 35 | # | 67 | C | 99 | c | 131 | · | 163 | £ | 195 | Ã | 227 | ã |
| 4 | · | 36 | $ | 68 | D | 100 | d | 132 | · | 164 | ¤ | 196 | Ä | 228 | ä |
| 5 | · | 37 | % | 69 | E | 101 | e | 133 | · | 165 | ¥ | 197 | Å | 229 | å |
| 6 | · | 38 | & | 70 | F | 102 | f | 134 | · | 166 | ¦ | 198 | Æ | 230 | æ |
| 7 | · | 39 | ' | 71 | G | 103 | g | 135 | · | 167 | § | 199 | Ç | 231 | ç |
| 8 | ** | 40 | ( | 72 | H | 104 | h | 136 | · | 168 | ¨ | 200 | È | 232 | è |
| 9 | ** | 41 | ) | 73 | I | 105 | i | 137 | · | 169 | © | 201 | É | 233 | é |
| 10 | ** | 42 | * | 74 | J | 106 | j | 138 | · | 170 | ª | 202 | Ê | 234 | ê |
| 11 | · | 43 | + | 75 | K | 107 | k | 139 | · | 171 | « | 203 | Ë | 235 | ë |
| 12 | · | 44 | , | 76 | L | 108 | l | 140 | · | 172 | ¬ | 204 | Ì | 236 | ì |
| 13 | ** | 45 | - | 77 | M | 109 | m | 141 | · | 173 | | 205 | Í | 237 | í |
| 14 | · | 46 | . | 78 | N | 110 | n | 142 | · | 174 | ® | 206 | Î | 238 | î |
| 15 | · | 47 | / | 79 | O | 111 | o | 143 | · | 175 | ¯ | 207 | Ï | 239 | ï |
| 16 | · | 48 | 0 | 80 | P | 112 | p | 144 | · | 176 | ° | 208 | Ð | 240 | ð |
| 17 | · | 49 | 1 | 81 | Q | 113 | q | 145 | ‘ | 177 | ± | 209 | Ñ | 241 | ñ |
| 18 | · | 50 | 2 | 82 | R | 114 | r | 146 | ’ | 178 | ² | 210 | Ò | 242 | ò |
| 19 | · | 51 | 3 | 83 | S | 115 | s | 147 | · | 179 | ³ | 211 | Ó | 243 | ó |
| 20 | · | 52 | 4 | 84 | T | 116 | t | 148 | · | 180 | ´ | 212 | Ô | 244 | ô |
| 21 | · | 53 | 5 | 85 | U | 117 | u | 149 | · | 181 | µ | 213 | Õ | 245 | õ |
| 22 | · | 54 | 6 | 86 | V | 118 | v | 150 | · | 182 | ¶ | 214 | Ö | 246 | ö |
| 23 | · | 55 | 7 | 87 | W | 119 | w | 151 | · | 183 | · | 215 | × | 247 | ÷ |
| 24 | · | 56 | 8 | 88 | X | 120 | x | 152 | · | 184 | ¸ | 216 | Ø | 248 | ø |
| 25 | · | 57 | 9 | 89 | Y | 121 | y | 153 | · | 185 | ¹ | 217 | Ù | 249 | ù |
| 26 | · | 58 | : | 90 | Z | 122 | z | 154 | · | 186 | º | 218 | Ú | 250 | ú |
| 27 | · | 59 | ; | 91 | [ | 123 | { | 155 | · | 187 | » | 219 | Û | 251 | û |
| 28 | · | 60 | < | 92 | \ | 124 | | | 156 | · | 188 | ¼ | 220 | Ü | 252 | ü |
| 29 | · | 61 | = | 93 | ] | 125 | } | 157 | · | 189 | ½ | 221 | Ý | 253 | ý |
| 30 | · | 62 | > | 94 | ^ | 126 | ~ | 158 | · | 190 | ¾ | 222 | Þ | 254 | þ |
| 31 | · | 63 | ? | 95 | _ | 127 | · | 159 | · | 191 | ¿ | 223 | ß | 255 | ÿ |
Questi caratteri non sono supportati in Microsoft Windows.
**I valori 8, 9, 10 e 13 vengono convertiti rispettivamente nei caratteri BACKSPACE, tabulazione, avanzamento riga e ritorno a capo. Per questi caratteri non è disponibile una rappresentazione grafica, ma, in base al tipo di applicazione, possono avere effetto sulla visualizzazione del testo.
I valori della tabella sono i valori predefiniti di Windows. Tuttavia, i valori del set di caratteri ANSI superiori a 127 vengono determinati nella pagina del codice specifica del sistema operativo.
E' possibile conoscere i codice caratteri anche tramite una semplice routine da inserire nell'evento KeyPress di una casella di testo
Private Sub TextBox4_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
TextBox5 = KeyAscii
End Sub
Questa routine scrive in una casella di testo (TextBox5) il codice relativo al carattere digitato in un'altra casella di testo (TextBox4)
E' facile ottenere in caselle di testo il controllo di ciò che si digita.
Per fare questo occorre tuttavia conoscere i codici Ascii dei caratteria che si vogliono escludere o accettare.
Per accettare solo caratteri numerici, nell'evento KeyPress basta scrivere poche righe di codice:
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Select Case KeyAscii
Case 48 To 57
Case Else
KeyAscii = 0
End Select
End Sub
Una utile funzione che il VB ed il VBA ci mettono a disposizione è la funzione Split.
Questa funzione, data una stringa complessa e dato un carattere separatore, restituisce tutte le sottostringhe di cui la stringa originale è composta.
Una stringa "Questa è una bella giornata" in cui il carattere separatore è " " (spazio) restituisce cinque sottostringhe: "Questa", "è", "una", "bella", "giornata".
La sintassi con cui viene chiamata la funzione è la seguente:
Split(stringa[, carattere separatore[, n. elementi restituiti[, compare metod]]])
La funzione restituisce le sottostringhe trovate in una matrice ad indice 0 (zero). L'unico argomento obbligatorio è il primo che rappresenta la stringa da elaborare. Tutti gli altri argomenti sono facoltativi.
stringa: l'argomento è obbligatorio e si riferisce alla stringa che dovremo elaborare
carattere separatore: può essere un qualsiasi carattere ma se omesso, viene automaticamente utilizzato come delimitatore il carattere spazio (" ")
Può essere rappresentato da uno o più caratteri: (" "), ("abc"), (" /"). In questo caso la stringa originale viene divisa ogni volta viene incontrata questa serie di caratteri.
Se il carattere separatore è di lunghezza zero (""), verrà restituita una matrice di un solo elemento contenente l'intera stringa. Ma in questo caso non penso abbia senso far riferimento a questa funzione.
n. elementi restituiti: se omesso assume il valore di default -1 ed in questo caso restituisce tutte le sottostringhe di cui la stringa principale è composta, altrimenti restituisce il numero di sottostringhe indicato dove le prime rispettano come punto di separazione il carattere separatore indicato e l'ultima rappresenta tutta la parte residua della stringa originaria Il numero indicato specifica il numero di sottostringhe desiderato (da uno all'ultima) non l'indice della matrice (da zero all'ultima meno uno)
compare metod: è facoltativo ma può essere espresso per indicare se il confronto deve essere eseguito tenendo conto delle maiuscole e minuscole o meno (vbTextCompare esegue la ricerca non sensibile a lettere maiuscole o minuscole). Se l'argomento viene omesso la ricerca tiene conto delle lettere maiuscole o minuscole.
Questa funzione può essere usata per trovare tutti gli elementi delimitati da uno o più caratteri separatori.
La variabile che dovrà accogliere tutte le sottostringhe come matrice può essere inizializzata come una semplice variabile e non come matrice per cui basta un banale: Dim B.
Tutti gli elementi trovati ed estratti da una stringa originale vengono depositati in questa variabile come matrice monodimensionale ad indice 0 (zero). Se mettiamo in una variabile, per esempio nella variabile "A" una stringa del genere: "Questa è una bella giornata"
e poi diamo l'istruzione: B = Split(a, " ")
oppure: B = Split(a, " ", 3)
avremo in "B" una matrice ad indice 0 come indicato nei due elenchi:
| b(0) = | Questa | b(0)= | Questa | |
| b(1) = | è | b(1)= | è | |
| b(2) = | una | b(2)= | una bella giornata | |
| b(3) = | bella | |||
| b(4) = | giornata |
Così con B = Split(a)
o con B = Split(a, " ")
se abbiamo una stringa "Questa è una bella giornata" come risultato avremo:
Questa
è
una
bella
giornata
se abbiamo: "35031 VEN PD 049 Abano Terme Bagni" (con due spazi tra PD e 049) come risultato avremo:
35031
VEN
PD
-------> vuota
049
Abano
Terme
Bagni
se abbiamo: "84040 CAM SA Abatemarco" (con due spazi tra SA e Abatemarco) come risultato abbiamo:
84040
CAM
SA
-------> vuota
-------> vuota
-------> vuota
-------> vuota
-------> vuota
Abatemarco
Molte volte ci siamo chiesti come stabilire tramite codice la cella da cui siamo appena usciti perchè, per esempio abbiamo la necessità di controllare ciò che è stato inserito in una determinata cella.
Ci viene in aiuto un evento legato al foglio di calcolo di Excel: L'Evento Worksheet_Change.
L'Evento Worksheet_Change si verifica quando l'utente o il codice cambiano il contenuto di una cella.
Dal momento che una volta modificata una cella del foglio di lavoro è possibile uscirne con svariati mezzi:
questo evento ci torna utile per stabilire e riconoscere la cella modificata dalla quale si è usciti.
Questo evento ha un argomento Target di tipo Range che viene fornito automaticamente ogni volta che viene eseguito l'evento Worksheet_Change e da cui si può leggere l'indirizzo della cella di cui è stato cambiato il contenuto.
L'argomento Target è integrato nella procedura Worksheet_Change del foglio di calcolo. Questo argomento recupera, come la proprietà Activecell, le coordinate della cella e dell'intervallo i cui contenuti modificati hanno provocato l'evento. Tale argomento può essere utilizzato nella procedura come ogni altra variabile di tipo Range.
Ecco alcuni esempi di utilizzo di questo argomento, che ricordiamo, è di tipo "Range":
a = Target.Address ' che restituisce una stringa del tipo: $A$3
b = Target.Count ' che restituisce il numero di celle modificate
c = Target.Row ' che restituisce un qualsiasi valore numerico compreso tra 1 e 65536
d = Target.Column ' che restituisce un qualsiasi valore numerico compreso tra 1 e 256
e = Target ' che restituisce il valore contenuto nella cella modificata
Target.Select ' che seleziona l'ultima cella selezionata
Tuttavia a volte lavorare con taluni eventi, e questo è uno di quelli, si possono ottenere effetti indesiderati, quali la ricorsività dell'evento. Un esempio potrebbe essere dato dal seguente frammento di codice:
Private Sub Worksheet_Change(ByVal Target As Range)
a = Target
If a <> "Giuseppe" Then
Target = ""
End If
End Sub
in cui si corre il rischio di ripetere all'infinito la stessa procedura. Infatti se il contenuto inserito nella cella non rispetta alcune regole, tale contenuto viene cancellato, come nell'esempio precedente.
Ma cancellando il contenuto della cella si provoca ancora l'evento Worksheet_Change del foglio di lavoro per cui la routine viene ancora ripetuta ritornando a valutare ulteriormente la cella in oggetto.
Ovviamente, continuando a ripetere sempre lo stesso controllo la cella in esame non soddisferà mai il controllo che si va a compiere per cui entriamo in un giro che tende a non finire mai.
Qui entra in gioco il prossimo paragrafo che vi invito a leggere come proseguo di questo.
Molto spesso chi ha a che fare con la programmazione si imbatte a gestire molti eventi.
Molti di questi li usiamo così spesso che neanche ce ne rendiamo conto. Prendiamo per esempio gli eventi legati ai pulsanti (CommandButton2_Click) o quelli legati alle UserForm (UserForm_Activate). Per noi oramai è consuetudine lavorare con questi e molti altri simili.
Qualche problema tuttavia potrebbero procurarci alcuni eventi.
Tanto per continuare il discorso appena fatto nel paragrafo precedente, vediamone uno per tutti: l'evento Worksheet_Change
Questo evento ci permette di gestire tramite l'argomento "Target" la cella da cui siamo appena usciti dopo averne modificato il contenuto.
Ora succede che, se per qualsiasi motivo dobbiamo modificare da codice il contenuto della cella appena modificato manualmente, molto probabilmente innescheremo un ciclo pressoché infinito.
Ma questo si può evitare in molte maniere.
All'inizio della routine possiamo porre delle istruzioni che impediscano al codice di proseguire oltre, come negli esempi che seguono:
If Target = "" Then Exit Sub 'da mettere all'inizio della procedura
If Target.Count > 1 Then Exit Sub 'da mettere all'inizio della procedura
Con queste istruzioni infatti viene bloccata l'esecuzione delle istruzioni contenute in seguito nella procedura di evento.
Ma esiste anche una istruzione ancor più potente che inibisce completamente l'uso di qualsiasi evento. Tale istruzione é:
Application.EnableEvents = False
da porre immediatamente prima dell'istruzione che altrimenti provocherebbe l'attivazione indesiderata o non necessaria dell'evento. Questa istruzione inibisce l'evento che altrimenti verrebbe scatenato.
Finita l'operazione, tuttavia, occorre riattivare la gestione degli eventi con un'istruzione opposta:
Application.EnableEvents = True.
Nell'esempio sopra citato le istruzioni dovrebbero essere scritte con questa sequenza:
If a <> "Giuseppe" Then
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
Questo tipo di difesa può essere attivata da qualsiasi punto del progetto, quindi anche fuori del modulo che ospita la gestione dell'evento. Nel caso che nel nostro esempio stiamo esaminando (Worksheet_Change), possiamo usarlo anche fuori del modulo relativo al foglio in questione.
Se la scrittura in una determinata cella viene eseguita da una UsefForm è molto probabile che non dobbiamo effettuare alcun controllo, per cui potremmo trovare istruzioni simili anche nel modulo relativo alla UserForm che stiamo usando.
Nell'evento Click della UserForm possiamo scrivere:
Private Sub CommandButton1_Click()
Application.EnableEvents = False
ActiveCell = ComboBox1.Text
Application.EnableEvents = True
End Sub
Infatti questo comando disabilita non solo l'evento (Worksheet_Change) che solo casualmente stiamo esaminando, ma qualsiasi tipo di evento.
A volte vogliamo conoscere il codice Ascii dei caratteri di una stringa o dei caratteri da usare per una stringa.
Se non vogliamo perderci nella tabella più sopra riportata, questa è una brevissima routine che ci permette di conoscere i codici Ascii dei caratteri che andiamo a digitare in una TextBox disegnata in una UserForm.
Per fare questo usiamo l'evento "TextBox1_KeyPress" della casella di testo.
Dim Riga, colonna
Private Sub TextBox1_KeyPress(ByVal KeyAscii As MSForms.ReturnInteger)
Cells(Riga + 1, colonna + 1) = KeyAscii
Riga = Riga + 1
If KeyAscii = 13 Then
colonna = colonna + 1
Riga = 0
End If
End Sub
Questo codice provvede a visualizzare e scrivere i codici relativi ai caratteri che digitiamo nella TextBox.
Per ottenere l'incremento della riga e/o della colonna occorre che le due variabili siano dichiarate nella sezione "Generale" del modulo relativo alla UserForm.
Il KeyAscii 13, che ho usato per cambiare la colonna, si ottiene premendo contemporaneamente i tasti Maiuscolo / Invio.
Mentre si scrivono dei dati in un foglio di lavoro e per farlo occorre spostarsi continuamente spostarsi su diversi intervalli del foglio ed addirittura spostarsi tra i fogli e si vuole evitare la visualizzazione di questi movimenti è possibile inibire temporaneamente l'aggiornamento del video. Questo evita in alcuni casi un fastidioso sfarfallio del video oltre che far risparmiare tempo.
La sintassi per disabilitare l'aggiornamento del video:
Application.ScreenUpdating = False
La sintassi per riabilitare l'aggiornamento del video:
Application.ScreenUpdating = True
Quando si lavora con gli eventi si vorrebbe disabilitarli temporaneamente per evitare di entrare in loop lunghissimi, se non infiniti o addirittura di mandare in tilt l'applicazione.
Questo può succedere, per esempio, quando si lavora l'evento: Worksheet_Change.
Quello che segue ne è un esempio:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> "Giovanni" Then
Target = ""
End If
End Sub
Se in una cella del foglio di lavoro si scrive qualcosa di diverso da "Giovanni" e si va a cancellare il contenuto si entra nel loop.
Ma, cancellando il contenuto del dato errato inserito nella cella del foglio di lavoro, si provoca ancora l'evento Worksheet_Change che andando a controllare ancora il valore della cella (questa volta vuota perché il contenuto è stato cancellato) lo trova ancora diverso da "Giovanni" eseguendo ancora l'azione di cancellazione del contenuto della cella.
Questo fatto provoca continue attivazioni dell'evento Worksheet_Change.
Per evitare questo loop occorre correggere così la procedura scritta:
Private Sub Worksheet_Change(ByVal Target As Range)
If Target <> "Giovanni" Then
Application.EnableEvents = False
Target = ""
Application.EnableEvents = True
End If
End Sub
Quando non possiamo usare i mezzi che Excel ed il VBA ci offrono, dobbiamo arrangiarci come meglio possiamo. E' il caso dell'ordinamento. Più volte ho mostrato, più o meno esplicitamente, come io ho affrontato il problema.
Normalmente faccio uso del bubble short che usa un algoritmo più intuitivo anch se il più lento tra i vari algoritmi che si trovano in giro:
Sub Ordinamento2()
NumRec = Range("A1").End(xlDown).Row
NumCampi = 2
For A = 1 To NumRec - 1
For B = A + 1 To NumRec
If Cells(A, 1) < Cells(B, 1) Then
C = Cells(A, 1)
D = Cells(A, 2)
Cells(A, 1) = Cells(B, 1)
Cells(A, 2) = Cells(B, 2)
Cells(B, 1) = C
Cells(B, 2) = D
End If
Next
Next
End Sub
Questo va bene se abbiamo a che fare con dati collocati su quante righe vogliamo e su pochissime colonne (nell'esempio abbiamo i dati distribuiti su due colonne.
Se le colonne cominciano ad essere di un numero più consistente le istruzioni da scrivere nella porzione di codice evidenziato cominciano ad essere di una quantità notevole.
La questione si risolve inserendo al posto delle istruzioni evidenziate un ciclo che spazzoli tutte le colonne della riga che stiamo esaminando:
Sub Ordinamento3()
Dim A, B, C, CTemp
Dim NumRec, NumCampi
NumRec = Range("A1").End(xlDown).Row
NumCampi = Range("A1").End(xlToRight).Column
For A = 1 To NumRec - 1
For B = A + 1 To NumRec
If Cells(A, 1) < Cells(B, 1) Then
For CTemp = 1 To NumCampi
C = Cells(A, CTemp)
Cells(A, CTemp) = Cells(B, CTemp)
Cells(B, CTemp) = C
Next
End If
Next
Next
End Sub
Quando sono pochi i controlli da trattare il problema nemmeno si presenta.
Se i controlli sono nell'ordine di alcune decine è più comodo usare un ciclo per passare in rassegna i vari controlli di una UserForm.
Se in una UserForm sono disegnati 5 caselle di testo e 5 etichette questo è un esempio di utilizzo di "Controls"
Per una serie di caselle di testo
For a = 1 To 5
Controls("TextBox" & a) = a
Next
Per una serie di etichette
For a = 1 To 5
Controls("Label" & a).Caption = a
Next
Invece di: Controls("TextBox" & a) per eseguire dei controlli su TextBox è possibile usare la funzione TypeName in questa maniera:
Dim Ctrl As Control
For Each Ctrl In UserForm1.Controls
If (TypeName(Ctrl) = ("TextBox")) Then
If IsNumeric(Ctrl.Value) Then
Tot = Tot + CDbl(Ctrl.Value)
End If
End If
Next
Un utile strumento che il VBA ci mette a disposizione è La Finestra immediata.
Il suo uso è facile: è sufficiente usare Debug.Print e può essere associato ad un uso intelligente alla punteggiatura usata dopo l'istruzione:
Si rileva particolarmente utile in sostituzione delle MsgBox che di tanto in tanto usiamo per testare dei risultati intermedi. Infatti, se ci troviamo in un ciclo particolarmente lungo, può risultare particolarmente fastidioso l'uso di queste MsgBox e si rileva particolarmente utile l'uso di Debug.Print
Qui di seguito alcuni esempi pratici
Primo esempio
For A = LBound(Nomi) To UBound(Nomi)
Debug.Print Nomi(A),
Next
effetto:
Cesare Nerone Ottaviano Agrippa
Secondo esempio
For A = LBound(Nomi) To UBound(Nomi)
Debug.Print Nomi(A);
Next
effetto:
CesareNeroneOttavianoAgrippa
Terzo esempio
For A = LBound(Nomi) To UBound(Nomi)
Debug.Print Nomi(A)
Next
effetto:
Cesare
Nerone
Ottaviano
Agrippa
Quarto esempio
For A = LBound(Nomi) To UBound(Nomi)
Debug.Print Nomi(A); " - ";
Next
effetto:
Cesare - Nerone - Ottaviano - Agrippa -
E' possibile usare una normale casella di testo per immettere una Password facendo apparire una serie di asterischi invece dei caratteri digitati.
E' sufficiente digitare nella casella relativa alla Proprietà PasswordChar della finestra Proprietà relativa alla TextBox il carattere che deve essere visualizzato in luogo dei caratteri digitati nella casella di testo.
Nonostante gli asterischi presenti nella TextBox è possibile leggerne il contenuto nella maniera usuale:
MsgBox TextBox1.Text
Per impostare la proprietà PasswordChar in VBA in maniera dinamica è necessaria l'istruzione:
TextBox2.PasswordChar = "*"
A volte capita di dover visualizzare una porzione di foglio distante dalla zona visualizzata al momento.
Si potrebbe usare:
Range("AA80").Select
Ma questo non porta la cella "AA80" nell'angolo superiore sinistro del foglio ma si accontenta di visualizzarla nella finestra.
Per ottenere questo conosco due metodi:
ActiveWindow.SmallScroll Down:=66
ActiveWindow.SmallScroll ToRight:=40
Queste istruzioni effettuano uno spostamento relativamente dalla posizione corrente per il numero di righe e di colonne indicate.
Application.GoTo Reference:=Range("AO67"), Scroll:=True
Questa istruzione sposta la cella "AO67" nell'angolo in alto a sinistra del foglio.
Una costante, come si evince dal nome, è un valore che in tutta la routine conserva sempre il valore assegnato in fase di programmazione.
Le costanti possono essere di due tipi:
Le costanti letterali possono essere un qualsiasi valore (stringa, numero, data) scritto direttamente ed esplicitamente nel codice:
MsgBox "Buon giorno", Range("A1") = 5 * 7, Dim Matrice(50)
Le costanti con nome hanno un nome da noi scelto seguendo le stesse regole usate per i nomi delle variabili.
Ma diversamente dalle variabili il valore assegnato ad una costante non può essere mai cambiato all'interno di una routine.
La convenienza dell'uso di una costante con nome sta nel fatto che facilita la leggibilità e la comprensione del codice oltre che facilita la manutenzione e l'aggiornamento. E' quindi conveniente usare una costante con nome al posto di una letterale per valori che vengono usati ripetutamente o per valori difficili da ricordare (il pigreco per esempio).
Se in un listato si deve modificare una costante letteraria usata più volte occorre trovare tutte le ricorrenze, col rischio di dimenticarne qualcuna o di cambiare un'altra costante che con quella in oggetto non c'entra nhulla.
Per una costante con nome è sufficiente cambiarne il valore nella sua dichiarazione.
Questo è un esempio di dichiarazione di una costante con nome:
Const Pi = 3,1415926535897932.
Per la creazione di una costante e per la sua portata valgono le stesse regole usate per una variabile, facendone magari l'assegnazione nella zona dichiarazione delle variabili in modo che sia accessibile a tutte le procedure del modulo, con la differenza che l'assegnazione di un valore deve avvenire usando esclusivamente una costante letterale o un'espressione su costanti letterali o un'altra costante precedentemente creata, non mai una variabile:
Const Raggio = 5
Const Pi = 3,1415926535897932
Const Circonf = 2 * Pi * Raggio (sarà vero?...)
Per impedire la scrittura o la cancellazione di caselle di testo abbiamo a disposizione due proprietà delle caselle di testo, che si possono settare nella finestra proprietà.
Ad una primo esame della figura osserviamo che le prime due caselle di testo sembrano uguali, mentre riconosciamo subito la terza casella di testo settata con Enabled = False.
In realtà la seconda casella di testo settata con Locked = True è più chiara da leggere ed accetta persino, contrariamente alla casella disabilitata col solito Enabled = False, il cursore ed eventuali colori, ma al suo interno non sarà comunque possibile scrivere alcunché.