Alcuni trucchi del mestiere ... Suggerimenti

Ultima modifica: 28-05-2016

In questa pagina verranno inseriti alcuni trucchi e suggerimenti

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.

I temi affrontati

Anno Bisestile Impostazioni Internazionali e formato Valuta
Attenzione ai nomi di variabili e funzioni Input Controllato in TextBox
Autocompletamento Lavorare con gli eventi
Casella di testo per inserire Password Le costanti in VBA
Cercare Ultima riga o ultima colonna Le variabili
Codici Ascii L'Evento Worksheet_Change
Controllare il Focus in TextBox ListIndex  
Controls e TypeName per passare in rassegna i controlli Ordinamento dati
Definire la cella da visualizzare in alto a destra del foglio Proprietà Enabled o Proprietà Locked per TextBox
EnableEvents ScreenUpdating
Finestra Immediata e l'istruzione print Sulle date
Funzione Split Trovare il codice Ascii di un carattere
Conoscere il numero di riga e colonna di una cella  

 

 

venire qui

Le variabili

Regole per le variabili

  1. Iniziano obbligatoriamente con un carattere alfabetico e possono continuare con caratteri alfanumerici (m2z3fgrt)
  2. Devono essere univoci all'interno di una stessa area di validità
  3. Non possono essere composti da più di 255 caratteri
  4. Non possono contenere un punto o un carattere di dichiarazione del tipo

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.

 

(Torna all'Indice)

 

 

Autocompletamento delle istruzioni VBA

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:

  1. se la parola non è visibile nell'elenco digitiamo le prime lettere fintanto che non la vediamo
  2. muoviamoci eventualmente con le frecce SU - GIÙ per raggiungerla e selezionarla
  3. una volta selezionata premiamo il tasto TAB per inserirla

 

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.

 

(Torna all'Indice)

 

 

Anno Bisestile

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.

 

(Torna all'Indice)

 

 

Cercare Ultima riga o ultima colonna

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

 

 

(Torna all'Indice)

 

 

Ricavare le coordinate di riga dall'indice di una ComboBox o una ListBox

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:

  1. sarà uguale a -1 se non è selezionato alcun elemento o se la lista è vuota
  2. sarà uguale a 0 se è selezionato il primo elemento
  3. sarà uguale a 1 se è selezionato il secondo elemento
  4. ecc.

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

 

(Torna all'Indice)

 

 

Scrivere una data in Excel

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)

 

(Torna all'Indice)

 

 

Formato valuta ed impostazioni internazionali

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.

 

(Torna all'Indice)

 

 

Attenzione ai nomi delle Variabili, Routines e Funzioni

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:

 

(Torna all'Indice)

 

 

Controllare il Focus in TextBox

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:

  1. Private Sub TextBox4_BeforeUpdate(ByVal Cancel As MSForms.ReturnBoolean)
  2. Private Sub TextBox4_Exit(ByVal Cancel As MSForms.ReturnBoolean)

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

 

(Torna all'Indice)

 

 

I codici ascii dei caratteri

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)

 

(Torna all'Indice)

 

 

Input controllato in TextBox

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

 

(Torna all'Indice)

 

 

La funzione Split

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

 

 

 

(Torna all'Indice)

 

 

L'Evento Worksheet_Change

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:

  1. il tasto Invio
  2. i tasti Freccia
  3. il tasto Tab
  4. il Mouse

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.

 

(Torna all'Indice)

 

 

 

Lavorare con gli eventi (disabilitare e riabilitare gli eventi)

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.

 

(Torna all'Indice)

 

 

Conoscere il codice Ascii di un carattere o di una serie di caratteri

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.

 

(Torna all'Indice)

 

 

 

ScreenUpdating

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

 

(Torna all'Indice)

 

 

 

EnableEvents

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

 

(Torna all'Indice)

 

 

 

Ordinamento dati

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

 

(Torna all'Indice)

 

 

 

Passare in rassegna sequenzialmente i controlli di una UserForm

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

 

(Torna all'Indice)

 

 

 

Uso della Finestra Immediata

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:

  1. virgola (,): la successiva istruzione Debug.Print pone il testo sulla stessa riga di quello precedentemente scritto usando una tabulazione predefinita
  2. punto e virgola (;): la successiva istruzione Debug.Print pone il testo sulla stessa riga ed immediatamente dopo quello precedentemente scritto
  3. senza alcuna punteggiatura: la successiva istruzione Debug.Print pone il testo in una nuova riga
  4. dopo la virgola o il punto e virgola è possibile aggiungere altre costanti o variabili scritte consecutivamente a quelle precedentemente scritte

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 -

 

(Torna all'Indice)

 

 

 

Casella di testo per inserire Password

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 = "*"

 

 

(Torna all'Indice)

 

 

 

Definire la cella da visualizzare in alto a destra del foglio

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.

 

(Torna all'Indice)

 

 

 

Le costanti in VBA

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?...)

 

(Torna all'Indice)

 

 

 

Usare le proprietà Enabled o Locked per le caselle di testo (TextBox)

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é.

 

(Torna all'Indice)

 

 

 

Conoscere il numero di riga e di colonna di una cella

Per conoscere il numero di riga e colonna di una cella sono sufficienti queste poche righe di codice:

Sub coordinate()
Dim Riga, Colonna
Riga = ActiveCell.Row
Colonna = ActiveCell.Column
MsgBox (Riga & ", " & Colonna)
Riga = Range("J9").Row
Colonna = Range("J9").Column
MsgBox (Riga & ", " & Colonna)
Range("J9").Select
End Sub

 

(Torna all'Indice)