Soluzioni varie.    (aggiornabile n. 55) - pagina vista: volte

• 1) - Usare una Textbox per comporre in automatico una data senza necessità di scrivere le barre "/" separatrici (lo fa il codice, e destinazione della data in una cella del foglio di lavoro, o dove vorrete:

• 2)  -  Evidenziare celle su più cartelle (aperte) in una colonna (sempre la stessa in ogni cartella) dove sono contenute date, e lanciare un messaggio con indicato ogni cella & cartella trovata,  dove la data sia uguale ad una determinata data, (quella del giorno in corso, nell' esempio). Il colore di evidenzazione nell'esempio è il giallo:

• 3)  -  Inserimento di un dato con ricerca se il dato è già presente in un intervallo: se sarà un doppione, si avvisa con un messaggio e si esce dalla Sub, altrimenti il nuovo dato verrà aggiunto alla fine dell'intervallo. Si sfrutta un ciclo Do While..Loop che è molto veloce anche con elenchi lunghi. Nell'esempio si suppone di inserire un nominativo scritto in una TextBox su una UserForm (ShowModal=False), tramite un CommandButton; condizione necessaria: tutte le celle dell'intervallo dovranno contenere dati:

• 4)  -  Verifica di dati doppioni in un elenco. Ciò che faremo con il dato doppione ev. trovato, cambia con le varie esigenze, e si potrà eliminare l'intera riga contenente il doppione, oppure sostituire il doppione con un trattino "-" come in questo esempio. Si sfrutta un doppio ciclo veloce, che sfrutta un ciclo Do While.. Loop come ciclo esterno, che scorrerà tutte le righe di un elenco, e per ogni cella letta, innesca un ciclo For..Next che partirà dall'ultima cella occupata, scorrendo all'indietro fino alla cella sottostante la cella ora letta; se verrà trovato un doppione il dato verrà sostituito con un trattino "-" e si avvisa con un messaggio indicante il dato trovato e la cella in cui il dato era. Anche in questo caso è necessario che l'elenco da controllare non contenga celle vuote. L'elenco è nella colonna B (la 2) ed inizia con intestazione di colonna dalla riga 5:

• 5)  -  Usare la Caption di una Label per creare un collegamento ad un sito, oppure per inviare una email. Si sfrutta il metodo FollowHyperlink

• 6)  -  Se invece vorrete mantenere un'elenco di tutti i siti visitati mentre lavorate su Excel, potrete usare una ListBox posta su UserForm, per farvi aggiungere gli indirizzi visitati; si suppone che gli indirizzi siano stringhe di connessione (HyperLinks) elencate sul foglio attivo. Impostare la proprietà ShowModal della userform a False, altrimenti risulterà impossibile mantenere la userform aperta e continuare nuovi collegamenti

• 7)  -  Oppure, se vorrete tenere traccia dei siti visitati, e della data e ora in cui vi siete collegati, converrà usare un foglio della cartella dove far accodare data e indirizzo visitato. Esempio: gli indirizzi (Hyperlinks) sono sul Foglio1, e la registrazione la faremo sul Foglio2, usando le colonne A per le date e B per gli indirizzi:

• 8)  - Usare una Label posta su UserForm come un messaggio, oppure un breve help rientrabile. Si sfrutta l'altezza come condizione da verificare se si cliccherà sulla Label stessa. L'altezza standard è 12 punti, se sapremo di inserire 4 righe di testo, ad inizio routine imposteremo l'altezza a 12 x 4 = 48 punti, e quindi modifichiamo la Caption della Label con il nuovo testo; se al successivo click si riscontra che l'altezza è = a 48, si ripristina a 12 punti.

Private Sub Label1_Click() If Label1.Height = 48 Then  'se l'altezza è uguale a 48 Label1.Height = 12  'si imposta a 12 Exit Sub 'e si esce dalla sub Else  'altrimenti, se è diversa da 48 punti Label1.Height = 48  'la si imposta a 48 e si vedrà così il messaggio sotto Label1.Caption = "Leggimi" & vbCrLf _ & "Questo è un" & vbCrLf _ & "breve messaggio" & vbCrLf _ & "di saluto" End If End Sub	Label a riposo
	Label cliccata

• 9)  -  Lavorare con due Fogli di lavoro affiancati (in verticale op. orizzontale). A volte può servire consultare dati che abbiamo su un'altro foglio, mentre stiamo lavorando sul foglio attivo. In Excel non è possibile tenere due fogli aperti contemporaneamente se non si creano due diverse finestre (Window). La cosa è possibile scegliendo dal menù Finestra/Nuova finestra, e a seguire Finestra/Disponi, scegliendo il tipo di affiancamento. Il tutto è gestibile anche dal vba. In questo esempio che segue, stando sul foglio1, faremo aprire il foglio2 ed allineare nella nuova finestra, in alto a sinistra, una cella, la E5, che presumiamo sia la cella iniziale dell'area che vogliamo visualizzare; per questa operazione, dopo aver selezionato il foglio2, usiamo lo ScrollColumn e lo ScrollRow:

• 10)  -  Cambiare il colore della "griglia" (GridLines) di un foglio, in automatico. E' possibile intervenire sul colore della griglia dal menù Strumenti/Opzioni/Visualizza, scegliendo un colore nell'apposita voce "Colore griglia". Questa scelta agisce su tutti i fogli della cartella aperta. E' possibile però scegliere un colore diverso della griglia, per ogni foglio della cartella, usando una semplice istruzione vba: ActiveWindow.GridlineColorIndex = 5 , e coloriamo la griglia di blu (ognuno sceglierà il colore che vorrà). Per automatizzare questa istruzione potremo scegliere un'evento del foglio di cui vogliamo modificare il colore; possiamo scegliere l'evento Activate, e quando selezioneremo quel foglio attiveremo l'istruzione, oppure il SelectionChange, o ancora legare il colore della griglia se un certo valore compare in una determinata cella. Possiamo pure randomizzare il colore, che cambierà di volta in volta. Vediamo tre esempi:

• 11)  -  Quando si usa un "controllo" MultiPage (lo stesso vale anche per il controllo TabStrip) posto su una UserForm, per poter selezionare e rendere attiva una delle pagine (Page, o Tab sul TabStrip) tramite vba, è necessario sfruttare la proprietà Value del MultiPage (o del TabStrip), indicando come valore un numero che identifica l'index di Page, tenendo presente che l'indice delle Pages inizia da zero (che corrisponde alla Page1). In questo esempio, sfruttiamo un commandbutton posto sulla stessa UserForm, e attiviamo la Pagina 2:

• 12)  -  Copiare Fogli di lavoro. Quando si voglia creare copie (che è diverso dall'aggiungere (Add ) nuovi fogli)di un foglio contenente dati, all'interno della stessa cartella.xls, teniamo presente che Excel rinomina la copia col nome del foglio di origine, aggiungendo al "nome" di questo foglio un numero progressivo posto tra parentesi tonde. Se il foglio di origine sarà il "Foglio1" la prima copia copia si chiamerà "Foglio1 (2)", la seconda copia si chiamerà "Foglio1 (3)", ecc. Questa routine aggiunge tanti fogli quanti ne scriveremo nell'InputBox che appare ad inizio routine, e li pone in ordine progressivo dopo il Foglio di origine. Il contatore N del ciclo, identifica al tempo stesso l'indice del foglio da usare come origine, nell'esempio il primo foglio (che ha il valore indice 1). Se dovessimo copiare il foglio indice 3, dovremmo iniziare il ciclo For Next da 3, addizionando 3 al valore ottenuto dall'inputbox.

• 13)  -  TextBox : ottenere il valore numerico più alto. Quando sia necessario reperire il valore più alto contenuto tra più TextBox poste su UserForm (ma anche su un Foglio) contenenti numeri, possiamo usare la funzione Array, sfruttando i nomi delle textbox come argomenti della matrice, ed usare un contatore inizializzato a zero che, scorrendo con un ciclo For Next la matrice e leggendo ogni valore (delle textbox), si aggiorni se il valore in quel momento letto dal ciclo è maggiore del valore del contatore. Al termine del ciclo otterremo il valore massimo tra quelli presenti nelle TextBox; esempio con tre TextBox:

• 14)  -  Usare il "nome" di una cartella per istruire istruzioni condizionali. Spesso, nel lavorare su più cartelle contemporaneamente aperte, possiamo desiderare di attivare (o limitare/escludere) condizioni operative diverse a secondo la cartella  che in quel momento sarà attiva. Possiamo usare un evento della cartella "pilota". Quando attiviamo una cartella diversa da quella attualmente aperta, attiviamo l'evento Deactivate (per contro si verifica l'evento Activate della cartella che prende lo stato attivo) relativo alla cartella che perde lo stato attivo : è in questo evento che inseriremo istruzioni che verificando il "nome" di una cartella, attivino le istruzioni che decideremo. In questo esempio, simuliamo che una UserForm (con la proprietà ShowModal impostata a False) aperta dalla cartella "miacartella.xls", si chiuda se rendiamo attiva un'altra cartella:

• 15) - Minimizzare Excel (rendere ad icona excel) all'apertura del programma, lasciando a video una UserForm, sfruttando l'evento Workbook_Open  (l'userform dovrà avere la proprietà ShowModal impostata a False):

• 16) -  Non visualizzare la "Struttura" quando si applica "Subtotali" ad un range di valori. Excel di default, visualizza la Struttura quando si eseguono Subtotali (dal menù Dati/Subtotali, oppure via codice vba). E' possibile deselezionare questa opzione dal menù Strumenti/Opzioni, e nella finestra "Visualizza", togliere il segno di spunta alla voce "Simboli di struttura". Ovviamente è possibile usare in alternativa un'istruzione vba, magari inserita dopo istruzioni vba per l'applicazioni di Subtotali. Tenere presente che questa istruzione agisce sull'opzione predetta rendendo permanente la scelta, a meno che non si ripristini l'opzione inserendola nelle istruzioni che toglieranno i subtotali. Questa l'istruzione:

• 17) -  Trovare un dato correlato al valore maggiore contenuto in un'intervallo. Esempio: trovare il nome (posto nella colonna A), relativo al valore MAX tra i valori posti nella colonna D. Si deve cercare il valore massimo  in un intervallo, memorizzando il numero di riga della cella contenente questo valore
  Si può quindi realizzare usando una variabile contatore, iniziata a zero, e creare un ciclo che scorrendo le celle con i valori , aggiorni il contatore se legge una cella con un valore maggiore presente nel contatore. Di quella cella si prende il numero riga, e alla fine si usa la sintassi Cells(riga, colonna) dove il numero riga sarà quello del numero riga corrispondente al valore maggiore trovato, e come "colonna" dovremo indicare la colonna con i nomi (la A ):

• 18) - Inserire un'immagine su un foglio. E' consigliabile prima di inserire un'immagine, selezionare una cella che verrà usata come cella iniziale per l'importazione dell'immagine, altrimenti l'immagine verrà posizionata a partire dalla cella in quel momento attiva. Per importare l'immagine dobbiamo indicare il percorso completo che mira al file immagine, e sfruttiamo il metodo Insert della proprietà Pictures dell'oggetto Worksheet. Nel primo esempio indichiamo nel codice il percorso che mira al file immagine, nel secondo invece facciamo aprire, tramite GetOpenFilename, la finestra "Apri file" e reperiamo tramite la variabile NewFile il percorso del file che selezioneremo; useremo poi questa variabile come vettore del percorso:

• 19) - Copiare una cella o un'intervallo di celle esportandola (anche in Excel stesso) come immagine bmp. Si sfrutta il metodo CopyPicture che consente appunto di "scattare" una foto ad un'area indicata nelle istruzioni, e poi incollare questa "foto" dove vorremo. In questo esempio fotografiamo e copiamo l'intervallo A6:F16 del foglio 1:

• 20) - Aggiungere caratteri (testo) a numeri. In alcuni casi, può necessitare far apparire una cifra racchiusa tra delimitatori, per esempio come una cifra su un assegno dove la cifra si "barra" per evitare possibili manomissioni. Supponiamo di avere la cella F10 dove scriveremo la cifra 2.200,00 e vogliamo che la stessa appaia posta tra "cancelletti" (i delimitatori) così : #2.200,00# . Si tratta in definitiva di una Concatenazione, ma dobbiamo tenere presente che la cifra, pur essendo in origine un valore numerico o valutario, dopo la concatenazione verrà trasformata in stringa di testo, (quindi poco adatta ad essere usata in operazioni numeriche) ma excel concatena il numero facendogli perdere la formattazione (separatore delle migliaia e due decimali) e vedremmo in realtà la cifra 2.200,00 come #2200#. per ovviare a ciò, è sufficiente "prelevare" la cifra da concatenare non come valore, ma come testo (text). Se il tutto lo vogliamo in automatico, potremo scegliere l'evento Worksheet_Change, in modo che le istruzioni avvengano dopo che quella cella  (la F10, o un'altra a vostra scelta) subisca un cambiamento. Per evitare che le istruzioni si attivino sempre per quella cella, istruiamo una condizione da verificare: se la cella è diversa da vuota e conterrà un valore numerico(una cifra) , allora attiviamo le istruzioni, negli altri casi, quindi anche dopo che avremo ottenuto il concatenamento, le istruzioni non si attiveranno.

• 21) - Usare il "Titolo" (Title) assegnato/da assegnare ad una cartella. In alcuni casi è possibile utilizzare il Titolo di una cartella, per esempio, per identificare la stessa tra più cartelle aperte. Il "Titolo" lo si può assegnare in Excel, dal menu File/Proprietà/Riepilogo/Titolo (nella stessa finestra dove appare la proprietà "Autore"). Le istruzioni sono semplici, e sfruttano la proprietà Title dell'oggetto Workbook.

• 22) - Registrare le Proprietà di una cartella di Excel. Un esercizio che sfrutta il CreateObject per accedere alle "Proprietà" di un file. Può necessitare di voler registrare, all'interno di un foglio di una cartella di excel, alcuni dati quali: il nome del file stesso, il percorso, l'autore, la data di creazione, la data
  dell'ultima modifica, ecc... dati che possono servire all'utente come controllo o verifica. Potremo lanciare la routine a mano, oppure sfruttare un'evento della cartella stessa, tipo il Workbook_Open, meglio ancora il Workbook_BeforeClose, in modo che venga registrata l'ultima modifica se avremo appunto apportato modifiche al file. Nelle istruzioni, per comodità, presumo che si voglia reperire le proprietà della cartella al momento attiva (ThisWorkbook) ed uso le celle dalla A1 alla A7 come destinazione dei dati; ognuno deciderà le celle a lui soddisfacenti:

• 23) -  Tratteggiare una cella (o più celle) . Per rendere la cella con i bordi tratteggiati simili all'effetto che si ottiene con il Copia. Si utilizza la proprietà LineStyle dell'insieme Borders: un insieme di quattro oggetti Border che rappresentano i quattro bordi di un oggetto Range o Style. (Vedi Guida in Linea).

• 24) -  Eliminare Macro - Nel caso che vengano eliminate una o più macro presenti in un modulo standard, Excel, anche le le macro sono state eliminate, alla riapertura della cartella chiederà se si voglia o meno "disabilitare le macro" (con protezione macro impostata a media). Questo perchè Excel riconosce la presenza di un modulo (modulo = macro) e pone la richiesta. Se vorrete eliminare macro e non ricevere più il messaggio, dovrete eliminare anche il modulo che conteneva le macro.

• 25) - Salvataggio In Corso... -  Una possibile soluzione per tutti coloro che vorrebbero far visualizzare una barra di  avanzamento per segnalare il salvataggio in corso del file xls quando lo stesso salvataggio (in genere per le "generose" dimensioni del file stesso) richiede diversi secondi o addirittura minuti. Non mi risulta sia possibile intercettare, tramite istruzioni vba, l'azione del salvataggio come un valore di iterazione da assegnare ad una normale ProgressBar (o almeno io non le conosco). Un'alternativa quindi potrebbe essere questo suggerimento: usare una UserForm da richiamare nell'evento Workbook_BeforeClose (ma anche da un apposito CommandButton preposto al salvataggio della cartella).

                   a - Impostare la proprietà ShowModal = False della UserForm impiegata per consentire lo svolgersi di operazioni sulla cartella.
                   b - Inserire il seguente controllo Gif89.dll (qui scaricabile in formato zip) , decomprimerlo nella cartella Sistem32 di Windows, indi dalla casella
                        degli strumenti della UserForm  , caricare questa libreria come "Componente aggiuntivo", e dopo posizionare il controllo sulla
                        UserForm, dimensionandolo opportunamente.
                        Questa libreria consente l'utilizzo di gif animate, e sarà appunto una gif animata a simulare il trascorrere del tempo.
                   c - questa è la gif animata   che potrete salvare cliccandoci sopra col destro del mouse e scegliendo "Salva immagine con
                         nome" . La Gif andrà posta nella stessa directory dove risiede il file xls che ne deve fare uso. L'effetto sarà simile a questa immagine:

          E queste le istruzioni: la prima andrà inserita nell'evento Activate della UserForm che ospita la gif animata, e fornisce il percorso che mira al file "scorri.gif", 

• 26) - Usare una cella per inserire numeri, con somma in altra cella dei numeri inseriti. Può necessitare di usare una cella come maschera di introduzione numeri, e si voglia però totalizzare i numeri inseriti. Sfruttiamo il metodo Intersect per l'imitare l'azione di somma all'introduzione dati in una sola cella: simuliamo la cella A1 come introduzione numeri (interi o decimali) e la cella B1 per ottenere la somma dei numeri immessi in A1

• 27) - Routine ContaParole - Una interessante soluzione per ovviare alla mancanza della funzione "Conta Parole" presente in MS Word, ma assente in MS Excel, trovata sul sito www.cpearson.com/excel.htm . Le istruzioni eseguono un ciclo su tutte le celle di un foglio che contengono dati (il foglio attivo, nell'esempio) (ActiveSheet.UsedRange) e per ogni cella, contano la lunghezza (Len) del contenuto, letto come stringa (Text). Poichè in una cella possono esserci più parole, e le parole sono divide da spazi vuoti, viene usato il metodo Replace per eliminare gli spazi (" ") dalla stringa, quindi si misura la lunghezza di questa, sottraendo il valore dalla lunghezza totale, aggiungendo 1; in questo modo si ottiene il numero di parole presenti nella stringa stessa. Un contatore provvede ad incrementare il totale di ogni cella.

• 28) -  Funzione Array - In molte occasioni, quando dobbiamo cercare o confrontare dati con un elenco di dati fissi, sempre gli stessi, come ad esempio un elenco di mesi, o di giorni, oppure una combinazione di lettere alfabetiche, o ancora una serie di codici numerici, o ancora di date, o di orari, ecc., può convenire di creare una "matrice" contenente l'elenco, ed assegnare, tramite la Funzione Array, detta matrice, ad una variabile. Sarà  così possibile scorrere gli "indici" di  matrice, alla ricerca o confronto di un dato presente in matrice, tramite un Ciclo For...Next. Facciamo un esempio per creare una routine che leggendo un codice fiscale, ci restituisca il nome del mese corrispondente alla nona lettera del codice fiscale (lettera appunto che identifica il mese di nascita). Impostiamo due matrici, una contenente l'elenco delle 12 lettere usate nella creazione di un codice fiscale (A, B, C, D, E, H, L, M, P, R, S, T) per identificare il mese, l'altra contenente i nomi dei 12 mesi dell'anno. Usiamo la funzione Mid per ottenere la nona lettera di un codice fiscale assegnata alla variabile "a", indi scorriamo con un ciclo (contatore "N") la matrice contenente le lettere corrispondenti ai mesi (LM) e confrontiamo la lettera letta con LM(N) con la lettera contenuta in "a"; trovata una corrispondenza, si esce dal ciclo (Exit For) e si avvisa con un messaggio. Cosa otteniamo: se si controllasse l'ipotetico codice fiscale di Ludovico Sforza (detto il Moro, nato a Vigevano il 27/07/1452) "SRZLVC14L27L872O", abbiamo che la nona lettera è la "L" , quindi il ciclo cerca la corrispondenza tra il contenuto della matrice LM e la "a", e si interrompe quando il contatore "N" è alla settima posizione di matrice (infatti in 7 posizione troviamo la "L"); quindi "N" è uguale a 7 (e potremmo fermarci qui se cercavamo il numero del mese corrispondente) oppure ottenere anche la settima posizione nella matrice "MS" dove con MS(N) otteniamo "LUG".

• 29) - Funzione "Mezzanotte" (MZT) - Tra le varie soluzioni possibili per svolgere calcoli su orari, ho preparato questa Funzione MZT() che restituisce la differenza tra due orari quando si accavalla la mezzanotte. Utile quindi in quei casi dove si voglia ottenere il totale delle ore lavorate in turni notturni. Il concetto è semplice: Excel considera un giorno composto da 24 ore, anzi: 23.59.59 visto che le 24 non esistono in quanto superate le 23.59.59 scattano le ore 00.00.00 che sono l'inizio di un nuovo giorno. La funzione quindi separa il primo orario (di ingresso turno) dalle 23.59.59, poi conteggia l'orario di uscita aggiungendolo alle ore 00.00.00, infine somma i due orari ottenuti restituendo il numero totale di ore intercorse tra ingresso/uscita. Per evitare risultati inesatti le celle che ospitano gli orari dovranno essere formattati come formato cella, a Ore. Se in A1 scriveremo l'orario di inizio turno 20.30.00 e in B1 quello di fine turno 4.00.00, in C1 metteremo la funzione = MZT(A1;B1) ed otterremo 7.30.00. Questa la funzione:

• 30) - Funzione FINEANNO - Ancora una funzione "fai da te" (cioè una Funzione Utente). Utile quando si voglia ottenere la data di Fine Anno relativa ad una data immessa in una cella o in una TextBox. Presento due funzioni: la funzione FINEANNO(data) che richiede un solo argomento (la data su cui calcolare il fine anno e che restituirà la data di fine anno dello stesso anno di "data", e la funzione FINEANNI(data;anni) che richiede due argomenti: la data da cui iniziare il calcolo di fine anno, e il numero di anni a scadere, utile per calcoli di fine anno futuri; questa seconda funzione può essere comunque usata al posto della prima semplicemente fornendo 0 (zero) come secondo argomento. Le funzioni dovranno essere poste in un modulo standard, per essere visibili sia sul foglio di lavoro, sia su UserForm o procedure vba. Con le celle: se in A1 avremo la data 22/03/05 e in B1 inseriamo la funzione =FINEANNI(A1;0), in B1 otterremo 31/12/05. Con le TextBox : se nella TextBox1 scriviamo 31/03/05 e destiniamo la TextBox2 come risultato della funzione, l'istruzione sarà: TextBox2 = FINEANNO(TextBox1) . Vediamo le due funzioni:

• 31) -  Elenchi personalizzati - rimando alla sezione "Primi passi/Elenchi personalizzati" per una breve trattazione dell'argomento. Qui vediamo le istruzioni vba per la realizzazione di Elenchi Personalizzati. Sfruttiamo il metodo AddCustomList che richiede come argomento la ListArray alla quale va assegnato o un'intervallo di celle, se l'elenco deve essere preso da celle di un foglio di lavoro, oppure da una matrice (Array) nella quale scriveremo i valori, racchiusi tra doppi apici e separati da una virgola (sono gli elementi della matrice). Una volta creato l'elenco, potremo usarlo sfruttando il metodo AutoFill

• 32) - Filtro: colore linguetta - Quando applichiamo un filtro ad una tabella, spuntano fuori i menù contestuali, (quei pulsantini con un triangolino nero) uno per ogni campo che forma la tabella. Se selezioniamo un menù di un campo per eseguire una filtrazione, il colore della linguetta (cioè del triangolino sul menù) diventa di colore blu. La funzione del cambio di colore dovrebbe facilitarci nell'individuazione di quale campo è stato scelto per la filtrazione, ma come molti segnalano (me compreso) solo con una lente di ingrandimento si riesce a notare il cambiamento di colore. Purtroppo non mi risulta sia possibile modificare l'opzione colore del triangolo del menù del filtro selezionato, impostandola ad esempio ad un bel rosso, ben distinguibile rispetto al nero. Ho pensato ad una soluzione (che propongo e che il lettore potrà modificare sia nel colore, che nell'indirizzo cella da colorare) che, riconoscendo a quale campo è stato applicata la filtrazione, colori la prima cella (quella di intestazione di campo) di rosso; risulterà chiaramente identificabile la colonna su cui si è scelta la filtrazione. Unica necessità: definire un automatismo che scatti al momento della filtrazione per lanciare le istruzioni; la selezione di un campo del filtro non viene rilevata da nessun evento (oppure io non ne conosco), ma possiamo sfruttare una condizione che risulta abbastanza naturale dopo aver applicato la filtrazione: quella di selezionare una qualsiasi cella del foglio di lavoro. Per questo sfrutteremo il Worksheet_SelectionChange dove inserire le istruzioni. Le istruzioni sfruttano la proprietà On dell'oggetto Filters che restituisce il valore True se un filtro specifico è attivo; creiamo quindi un ciclo che rilevi la posizione di quale filtro è attivo, ne prenda il valore index, che ci servirà per determinare il numero di colonna nella sintassi Cells; il numero di riga lo conosciamo a priori ( se la tabella non inizia dalla colonna 1, al valore reperito con la variabile "n" dovremo aggiungere il numero di tante colonne quante sono tra inizio colonna (1) e inizio tabella). Una volta che toglieremo il filtro selezioniamo di nuovo una cella per rilanciare l'istruzione che in questo caso, riportando le celle a neutre, non troverà nessun filtro attivo:

Private Sub Worksheet_SelectionChange(ByVal Target AsRange) Set w = Worksheets("Foglio1")  'foglio ipotetico con tabella da filtrare w.Cells.Interior.ColorIndex = xlNone  'riportiamo il colore di tutte le celle a neutro If w.AutoFilterMode Then   'se il filtro risulta inserito x = w.AutoFilter.Filters.Count  con x contiamo da quanti campi è formato For n = 1 To x  'si inizia il ciclo che controllerà quale indice di filtro è attivato If w.AutoFilter.Filters(n).On Then  'la variabile "n" restituisce l'indice del filtro attivo Cells(1, n).Interior.ColorIndex = 3 'quindi si colora di rosso la cella 1, colonna "n" End If Next End If End Sub	il triangolino è blu???
	con l'effetto della macro

• 33) - Numero Civico: estrarlo da un indirizzo. Può capitare la necessità di ottenere il solo numero civico contenuto in un campo "Indirizzo"; spesso nelle tabelle dati di nominativi, clienti, fornitori, amici, ecc. usiamo una colonna dove scriviamo l'indirizzo relativo ad un nome, e, almeno da noi, usiamo scrivere il numero civico alla fine dell'indirizzo, esempio "Via Giolitti, 13" o ancora "Via Verdi 87", o ancora "L.go Colombo nr. 8", ecc. ecc. In tutti i casi, separiamo con uno spazio vuoto il numero dal resto del testo. Questo tipo di sintassi non consente di usare la funzione Val(stringa) che restituisce il numero presente in una stringa di testo (l'indirizzo, appunto) come avviene con la sintassi anglosassone che usa mettere il numero all'inizio dell'indirizzo (1088 Park Lane), infatti è necessario che il valore numerico si trovi all'inizio della stringa perchè la funzione Val restituisca il numero.  Questa sotto è una piccola macro che sfruttando la funzione InStrRev(dovecercare, cosacercare) ci restituisce il valore numerico relativo alla posizione dell'occorrenza cercata all'interno della stringa dove cercare, a partire dalla fine della stringa. Noi cercheremo quindi il primo spazio vuoto a partire da destra, e la posizione di questo spazio vuoto ci servirà per estrarre tramite la funzione Mid tutto ciò che e oltre questa posizione, verso destra, quindi il numero civico. Se l'elenco è lungo, useremo un ciclo che scorra tutta la colonna "Indirizzi" e metta, in un'altra colonna, i numeri civici estratti. La macro la lanceremo dal foglio con gli indirizzi: (non utilizzando il dimensionamento delle variabili (Ultimo, n,y,a,c), le stesse sono assimilate come di tipo Var (quindi stringa o numero)).

• 34) - Inibire una cella - Se vogliamo impedire che si possa scrivere in una cella (contenente una formula, o un valore che deve restare fisso), possiamo sfruttare l'evento Worksheet_SelectionChange : dato che per scrivere in una cella è necessario selezionarla, sfruttiamo l'evento , e attraverso Intersect, limitiamo l'azione alla sola cella Target (quella ce ci interessa proteggere) inibendo (impediamo) l'accesso alla cella, spostando la selezione su un'altra cella:

• 35) - Stampare .pdf (Acrobat) da Excel - un lettore (Antonello Manca), adattando suggerimenti reperiti sul web, invia queste istruzioni per la stampa, da Excel, di documenti realizzati con Acrobat ®. Le istruzioni sfruttano il metodo Run applicato alla Shell di sistema, tramite il CreateObject. ( vedi articolo ) e necessitano di una versione recente del programma Acrobat Reader (versione libera scaricabile da internet) installato sul computer. Le istruzioni sono state testate con la versione 6.0.1 di Acrobat Reader. Poichè il metodo Run richiede di dichiarare il nome esatto dell'eseguibile da lanciare, ognuno dovrà verificare sul proprio computer l'esatto nome dell'eseguibile relativo alla versione Acrobat Reader installata: il percorso è: C:\Programmi\Adobe\Acrobat 6.0\Reader\nome eseguibile, che nella versione 6 è AcroRd32.exe, seguito dal nome, completo di percorso, del file .pdf da inviare in stampa; è possibile inviare una sequenza di nomi di file .pdf, in modo da stampare uno dopo l'altro, tutti i file. Vediamo due esempi:

• 36) - Menù contestuale : lanciare macro -  quando clicchiamo col destro del mouse su una cella di un foglio di lavoro, appare un menù con una serie di opzioni (voci) da poter selezionare: quella finestrina è il "menù contestuale". In vba di excel quella "finestrina" è una "CommandBars", ed il suo nome è "Cell", e come in ogni CommandBars che si rispetti, è possibile aggiungere "Voci" (Controls) per associarle a macro da lanciare. Per aggiungere una voce (Control) sfruttiamo il metodo Add, e visto che desideriamo aggiungere voci di menù ad ogni apertura della cartella, sfruttiamo l'evento Workbook_Open per inserire le nostre istruzioni; ma, poichè excel memorizzerebbe le voci aggiunte, riaggiungendole nuovamente ad ogni apertura cartella, inseriamo l'istruzione di resettare le voci di default del menù contestuale "Cell". Inoltre, per evitare che excel presenti il menù contestuale modificato anche su altre cartelle, sfrutteremo anche la chiusura della cartella per resettarlo

• 37) - Uso di tasti per lanciare macro - Usare tasti per attivare istruzioni (lanciare macro) non è una novità (vedi articolo "Il Metodo OnKey" sull'altro sito) ma qui vediamo un esempio impostato su una condizione : usare il tasto F2 (o quello che preferite) per lanciare una macro, SOLO se siamo su un determinato Foglio. La soluzione è semplice: sfruttiamo l'evento Workbook_SheetActivate (NON WorkSheet_Activate) e inseriamo le istruzioni che sfruttano il metodo OnKey; solo quando avremo selezionato il foglio il cui "nome" è inserito, il tasto F2 lancerà la macro associata:

• 38) - Quando sia necessario identificare un'area che abbiamo selezionato, magari per scorrere le celle dell'area con un ciclo For Each Next, possiamo impostare (tramite l'istruzione Set) una variabile come identificativo dell'area selezionata, e quindi agire sulla variabile. Questo ci consentirebbe di poter variare di volta in volta l'area su cui agire in funzione dell'area selezionata. Oppure potremmo selezionare un'area solo per impostare una formattazione specifica, come un "Formato Cella", un tipo di font, o ancora un colore, ecc. ecc.

• 39) - Selezionare una stampante - Quando si voglia poter decidere quale stampante (tra le disponibili nel sistema) utilizzare per l'invio del documento in stampa, conviene utilizzare una DialogBox (vedi articolo "Finestre di dialogo-costanti"). Le istruzioni sono semplici, e possono essere inserite all'interno delle nostre routine per stampare; le sottostanti istruzioni fanno aprire la classica finestra di selezione stampante, dove scegliendo la stampante e premendo OK, si invierà la stampa alla stampante scelta:

istruzioni:	effetto:
..... With Application.Dialogs(xlDialogPrint) .Show End With .....

• 40) - ColumnHeads delle ListBox o di ComboBox - Credo esista un pò di confusione su come usarle. Intanto si deve impostare la proprietà ColumnHeads del controllo a True, quindi sarà sufficiente tenere presente che la ListBox/ComboBox caricherà come "intestazioni di colonna" i valori che si trovano nella riga immediatamente superiore alla prima riga dell'intervallo che forma il RowSource. In questo esempio sotto, vediamo una tabella che inizia dalla riga 3, dove esistono le "intestazioni di campo", mentre i dati veri e propri iniziano dalla riga 4; al RowSource verrà assegnato quindi l'intervallo A4:E10 mentre i valori nella riga 3 formeranno le "intestazioni di colonna" nella ListBox. Sembra quindi che se i dati verranno caricati non tramite l'assegnazione del RowSource, ma tramite il metodo Additem, non sarà possibile ottenere intestazioni di colonna in quanto il controllo non riesce a identificare l'indice di riga di inizio dati.

• 41) - Funzione SCRIVI(arg) - Sappiamo che quando inseriamo il segno di uguale (=) iniziale in una cella excel lo interpreta come inserimento di una formula, ma se volessimo "scrivere" la composizione di una formula per visualizzarla solo come testo compreso il segno di uguale, dovremmo anteporre un singolo apice ( ' ) al segno di uguale; in alcuni casi però vorremmo poter disporre di una Funzione che provveda da sola alla conversione formula:testo . Questa sotto soddisfa lo scopo: è una "funzione fai da te" molto semplice che restituisce il valore in formato Testo: viene richiesto come argomento il riferimento della cella contenente la formula (Origine), per cui sul foglio si userà (se la formula da visualizzare è in C1):   =SCRIVI(C1)

• 42) - Doppi Apici ( "" ) in una stringa. - Quando si voglia evidenziare una parola all'interno di una stringa di testo, che è gia necessariamente compresa tra due doppi apici (tutta la stringa), la parola (o frase) da evidenziare va posta tra 4 doppi apici. Tipica applicazione una finestra di messaggio dove vogliamo far risaltare una parola; esempio: nella stringa "Se vedete questo messaggio "NON" premete "AVANTI"", dove vogliamo che le parole NON e AVANTI compaiano tra doppi apici, dovremo compilare l'istruzione così:

• MsgBox "Se vedete questo messaggio ""NON"" premete ""AVANTI"""

• 43) - Sintassi per i riferimenti variabili quando si usa lo "Stile A1"  e per chiarire tipo: Range("C15") dove con la lettera C si intende la colonna mentre con il numero ci riferiamo alla riga, o anche con gli intervalli (sempre in Stile A1), esempio Range("A1:D10");  come si nota i riferimenti alle celle o agli intervalli sono compresi tra doppi apici e visti quindi dal vba, come stringhe di testo; se vogliamo rendere variabile uno dei due elementi di un riferimento (la colonna o la riga) diventa necessario quindi usare delle variabili che dovranno essere "legate" con il segno di concatenazione ( & ) come si fa appunto con l'unione di testo con testo o testo con numeri; vediamo alcuni esempi: attenzione a come si posizionano gli apici, gli spazi, il segno di concatenazione (il segreto è tutto li):

• 44) - Conoscere gli anni bisestili - Una veloce routine per chi ha necessità di conoscere in anticipo quali anni sono o saranno bisestili; si sfrutta l'operatore Mod che restituisce il quoziente di una divisione; se quindi la divisione di un anno per 4 restituirà zero, ci troveremo in presenza di un anno bisestile; per comprendere anche gli anni di centenario dovremo dividere anche per 400 anzichè solo per 4. Usiamo quindi una InputBox per scrivere il periodo in numero di anni che vogliamo verificare, e inseriamo due controllini per uscire se non scriveremo nulla e/o se non scriveremo un numero. Il conteggio inizia dall'anno in corso. Se poi si volessero controllare a ritroso quali sono stati gli anni bisestili, basta sostituire il segno di addizione (+) con quello di sottrazione (-), oppure inserire una seconda InputBox per chiedere la direzione di ricerca (in avanti o indietro) ed usare quindi la seconda routine, dove si usa il Select Case per individuare se avremo scritto + oppure - :

• 45) - Che numero di cella sei ? - sappiamo che un foglio di lavoro di excel è come un'enorme scacchiera contenente 16.777.616 celle (256 colonne x 65536 righe) e che è possibile identificare, puntare, mirare ad una cella in particolare facendo riferimento al suo numero (Indice) all'interno delle oltre 16 milioni di celle. Se noi scriviamo Activesheet.Cells(1027).Select oppure Cells(2311) = QuelCheTiPare avremmo identificato la Cella C5 nel primo caso e la cella G10 nel secondo caso. Excel infatti riconosce il numero della cella iniziando a contare da 1 dalla cella A1, proseguendo verso destra fino alla fine della riga, quindi passando all'inizio della seconda riga e scorrendola fino alla fine, e così via fino alla fine del foglio. In pratica sfrutta la proprietà Item dell'oggetto Range; proprietà che richiede di indicare il numero di indice della cella alla quale si desidera accedere. Ma se invece che accedere ad una cella indicando il suo Item(Index), lo volessimo ricavare per nostro diletto o piacere (detto "Index") ? Personalmente sono incappato in una "guerra tra i mondi" tra me e il debugger nel tentativo di usare "oggetti", "proprietà", sintassi, e mi scoppiavano "Errori" in ogni direzione, cercando risalire al "numero" di una qualsiasi cella selezionata; le ho tentate di tutte, anche una subdola manovra di accerchiamento, in puro stile "marines", ma sono stato individuato, debuggerato e sconfitto; al che rimando al "Buon Pellegrino"  il compito di "illuminarmi" (che sarà "benemerito"). Nel contempo propongo una semplice routine che restituisce, senza lamentarsi, il benedetto numero indice di una cella selezionata, ma ovviamente non era quello che cercavo: per la prova ho scelto l'evento SelectionChange del foglio, così ad ogni selezione di cella ne vediamo il suo "indice". La cosa è sfruttabile anche in esecuzione ricavando la "x" e la "y" dagli indici riga e colonna di una ev. Cells(x, y).

Private Sub Worksheet_SelectionChange(ByVal Target As Range) x = ActiveCell.Row y = ActiveCell.Column ciccio = (256 * (x - 1)) + y MsgBox Format(ciccio, "#,###") End Sub

• 46) - Convertire un immagine .Jpeg in .Bmp tramite il vba - Sfruttiamo un'istruzione tipica del VB e non conosciuta, ma eseguibile, in VBA; è l'istruzione SavePicture che consente ad un una immagine caricata in un controllo (ActiveX) Image o PictureBox, di essere salvata in un nuovo file che avrà l'estensione BMP. Se un'immagine grafica viene caricata da un file e assegnata alla proprietà Picture di uno dei due oggetti, in fase di progettazione o in fase di esecuzione, e si tratta di una bitmap, un'icona, un metafile o un enhanced metafile, verrà salvata con lo stesso formato del file originale. Se si tratta di un file GIF o JPEG, verrà salvato come un file bitmap.
  
  Le immagini grafiche create dalla proprietà Image verranno sempre salvate come file bitmap (.bmp) a prescindere dal formato originario. Useremo quindi un controllo Image che posizioneremo su una UserForm, e sfrutteremo l'evento Activate della UserForm per caricare un'immagine; affideremo ad un Command button posto sulla stessa userform il compito di convertire il formato. Per caricare e poi salvare l'immagine dovremo fornire il percorso dell'immagine Jpeg e indicare percorso e nome dove salvare l'immagine convertita in bmp. Non è difficile, si usano istruzioni già utilizzate in diversi esempi su questi siti, e comunque:

• 47) - Usare l'evento "Calculate" - Spesso usiamo o consigliamo, per lanciare istruzioni vba, l'evento "Change" di un Worksheet, che si verifica appunto quando in una qualsiasi cella del foglio "cambiamo" il contenuto, inserendo o modificando valori (numeri, testo, date/orari). Ma quando cambiano valori nelle celle NON per nostra azione diretta, ma come risultato di una formula, dovremo usare l'evento Worksheet_Calculate (infatti se in una cella contenente una formula, cambia il risultato che la formula svolge, non cambia la formula stessa e non si verifica l'evento Change). (Vedi anche articolo "Sfruttare gli eventi")

• 48) - Passare nell'Editor di Visual Basic (VBE) - Quando sia necessario passare nel VBE per raggiungere o visualizzare codice vba provenendo da un foglio di lavoro o da una UserForm, possiamo sfruttare il comando da tastiera ALT + F11, lanciato sfruttando il SendKeys; se lanciato da Userform, poichè non risulta eseguibile il comando stante la UserForm attiva, neppure con la sua proprietà ShowModal impostata a False, aggiriamo l'ostacolo chiudendo la Userform e lanciando a seguire l'istruzione; usiamo per l'occasione un CommandButton posto sulla UserForm, e questa l'istruzione:

• 49) - Eliminare righe e/o colonne nascoste - Una Riga o Colonna è nascosta (Hidden in inglese) se ha la sua proprietà Hidden = True . Intervenire quindi su queste righe o colonne diventa quindi facile: basta verificare con l'istruzione If...Then se sono nascoste e quindi applicare l'azione desiderata. Risulta quindi possibile anche "scoprirle", non solo eliminarle. Per intercettare le righe (o colonne) nascoste in un Foglio di lavoro, potremo usare un ciclo For...Next, che scorrendo un certo numero di righe (o di colonne), controlli se le righe sono Hidden = True, trovate le righe (o colonne) nascoste le elimineremo (o le scopriremo). Alcuni esempi:

• 50) - Protezione/Sprotezione Foglio con le macro - Di default tutte le celle di ogni foglio di lavoro sono protette da scrittura, protezione che si attiva SOLO se si sceglie di proteggere TUTTO il foglio di lavoro (con o senza password). Di fatto questa protezione impedisce all'utente di poter scrivere o comunque modificare le celle protette (nel caso si vogliano celle non protette basta selezionare le celle che interessano e dal menù "Formato celle/Protezione", togliere il segno di spunta alla voce "Bloccata"). A volte comunque si desidera lasciare come celle protette anche quelle celle dove si voglia intervenire tramite istruzioni vba, ma se le celle sono "bloccate" le istruzioni vba non possono intervenire e si genera un errore di run-time. La soluzione è semplice: tramite la macro che dovrà agire su quelle celle, inseriamo ad inizio routine, una istruzione che toglie la protezione al foglio e quindi lascia le celle "bloccate" libere di poter essere modificate, a seguire vengono le istruzioni della macro, ed in chiusura le istruzioni per riproteggere il foglio; vediamo un esempio:

• 51) - Numerazione pagine in fase di stampa - Può necessitare di voler stampare alcuni lavori con le pagine numerate, es: pag. 1 di 9. In Excel, senza istruzioni vba, si può seguire questa semplice procedura: prima di stampare, dal menù File scegliere "anteprima di stampa", nel menù di quella pagina, scegliere "Imposta...", nella finestrina che si apre, scegliere: "Intestaz/Piè di pag.",  in questa pagina troviamo un menù a tendina sotto la voce"Intestazione", cliccare sul triangolino nero del menu a tendina, e scegliere la voce "Pagina 1 di ? " e quindi premi ok, quindi il pulsante "stampa" sul menù dell'anteprima. Per coloro che desiderano invece adottare istruzioni vba, possono leggere il paragrafo "Area di stampa - PageSetup" dell'articolo "Stampa pagine di un foglio" http://ennius.altervista.org/vba/vba44.php dove trovate istruzioni su come procedere e i simboli usati per alcuni tipi di queste formattazioni.

• 52) - Le modalità di protezione messe a disposizione da Excel sono facilmente superabili oppure sono molto affidabili? Dipende dalla preparazione di chi cerca di forzare la protezione, ma comunque secondo me NON esiste nessuna protezione degna di tale nome; esistono molti programmi nati proprio per scoprire e rivelare, annullandole, ev. password usate dagli utenti sia con la cartella, sia per i fogli, sia per il progetto vba (più protette sono le componenti originali Microsoft usate, come ad esempio atpvbaen.xla o funcres.xla, ecc.).
  Alcuni di questi programmi sono facilmente reperibili. (basta cercare con google: "protezione excel" e ad esempio trovate  anche questo:
  http://italian.eazel.com/lv/group/view/kl35681/Accent_Excel_Password_Recovery.htm  che è shareware, in prova gratuita.)
  Sotto questo aspetto (protezione) Excel è molto poco affidabile.

• 53) - Conoscere la data in cui cade la Pasqua Cristiana - Routine per conoscere la scadenza della Pasqua Cristiana secondo il calendario Gregoriano (il nostro) . Potete leggere tutte le spiegazioni alla seguente pagina : "http://it.wikipedia.org/wiki/Calcolo_della_Pasqua" e seguendo il Metodo aritmetico di Gauss; tra le varie versioni circolanti in internet, presento questa mia soluzione nella quale si sfrutta una InputBox per scrivere l'anno di cui si vuol conoscere la data in cui cadrà la Pasqua, ottenendo la risposta tramite una messagebox; chiunque potrà modificare le istruzioni decidendo che anzichè un messaggio ottenga il risultato in una cella:

• 54) - Variare lunghezza di una stringa (testo) in una cella. - Un esercizio già descritto in alcuni articoli, ma ripreso qui esemplificando al massimo; quando per necessità diverse, si voglia disporre di stringhe di testo che abbiano tutte la stessa lunghezza, per ogni cella presente in un intervallo di celle, possiamo aggiungere tanti spazi vuoti " " (oppure caratteri come trattini "-" ad esempio) quanti ne mancano tra la lunghezza reale di una stringa e la lunghezza voluta. Sotto un esempio impostato per una specifica cella, ma sarà possibile utilizzare un ciclo che scorra tutte le celle di un'intervallo, e per ogni cella applicare l'istruzione:

• 55) - Convertire al volo Euro in Lire - E' possibile avere l'effetto tooltips, cioè cliccando sulla cella contenente cifra in euro, avere la finestrella che appare e indica il valore contenuto espresso in lire?  Risposta: si ; possiamo ottenere come risposta un messaggio che riguarda la cella "Target", cioè quella su cui  eseguiremo un doppio click (quindi doppio click di sinistro) o potremo sfruttare anche il semplice click del pulsante destro del mouse; vediamo entrambe le procedure,  dove cambiano gli eventi del foglio di lavoro da sfruttare:

• 56) -

prelevato sul sito www.ennius.altervista.org