Filtro automatico su campi Data? Preferire il Filtro avanzato...

Filtro automatico su campi data? Problematico

Dal Forum di Shareoffice un visitatore segnala questa stranezza:

Domanda. Nel mio modello Scadenzario la macro registrata relativa al Filtro automatico su un campo Data Scadenza occulta tutto. Eppure le operazioni manuali registrate da tale macro funzionano regolarmente. Come mai ?

Risposta iniziale. In un primo tempo ho ritenuto trattarsi di un’anomalia dovuta alla confusione tra formati data e numeri seriali, ossia i valori veri sottostanti. Per cui ne ho concluso che  il Filtro automatico sui campi Data va evitato nelle macro...

Successivamente mi è stato segnalato che una soluzione che “salva” il filtro automatico c’è, che consiste nell’adottare nella macro il formato inglese mese/giorno/anno! Avrei dovuto pensarci, perché non va mai dimenticato che IL VBA PRETENDE SEMPRE SINTASSI ANGLOSASSONE ANCHE NELLE EDIZIONI LOCALIZZATE DI OFFICE.

Dunque tutto quello che ho pubblicato è da buttare? Sì e no, per due motivi:

• Qualcosa, anche sbagliando, s’impara sempre e le note seguenti che lascio immutate, benché affette da quel tal errore, ricordano anche i pregi del Filtro avanzato (da me sempre preferito);
• Ho poi scoperto che pure la sintassi inglese delle date è affetta da una stranezza! La indico in fondo all’articolo e poi non ne parliamo più.

INIZIO PARTE IMPRECISA

Esaminiamo il modellino. Esso si compone di diversi campi, con le relative intestazioni a riga 1. Quello che ci interessa è il numero 12, che si presenta così:

Il nono campo, che non riporto, contiene codici quali “p” e “pa“ relativi allo stato di quel certo elemento. Non sto a dire che diavolo sia tale stato, non ha importanza. Sta di fatto che accendendo il Registratore di macro e lanciando il comando Dati > Filtro > Filtro automatico e poi agendo con bene note operazioni sui predetti campi il nostro amico ottiene due cose:

• filtra regolarmente sul valore pa del primo campo e sulla data 13/01/2006 del secondo; per cui sono visualizzati solo i record aventi quello stato e quella data.
• ottiene la macro seguente, anzi l’ho ottenuta io stesso, a riprova:

Sub Macro1()

'

' Macro1 Macro

' Macro registrata il 25/01/2007 da giaccaglini

'

    Selection.AutoFilter Field:=9, Criteria1:="pa"

    Selection.AutoFilter Field:=12, Criteria1:="13/01/2006"

End Sub

Purtroppo lanciando Macro1 si ottiene l’occultamento di tutto l’elenco. Procedendo passo dopo passo (col tasto F8) si nota poi che la macro funziona solo sulla PRIMA istruzione. Si direbbe un BUG e, comunque, un limite di AutoFilter.

Focalizzato il guaio, ecco un secondo tentativo, limitato al dannato campo 12:

Sub MacroBis()

  Range("A1").AutoFilter Field:=12, Criteria1:="13/01/2006"

End Sub

Stavolta ho usato Range(“A1”) anziché Selection, per cui la macro agisce “a distanza”, qualunque sia la selezione corrente, ma questa finezza (che non mi stanco di raccomandare!) non evita la fastidiosa anomalia.

Soluzione parziale

Pensa e ripensa mi sono reso conto della causa: il Registratore considera il dato scelto come stringa (“13/01/2006”) che però è un dato formattato mentre il dato vero è un numero seriale.

Basta applicare il formato Generale alle celle per scoprire che, per limitarsi alla dannata data 13/01/2006 corrisponde il numero 38455 (tanti essendo i giorni a partire dall’anno zero del Calendario Excel).

A questo punto, ho anche riflettuto sul fatto che il nostro amico aveva adottato una UserForm con una casella di testo ove l’utente deve digitare una scadenza da lui scelta. Palesemente, un oggetto TextBox  contiene sempre una stringa e il problema si ripropone. Di qui il duplice suggerimento: a) adottare tre caselline, per il Giorno, Mese e Anno (facilitando il lavoro dell’utente); b) adottare opportune funzioni data per tradurre questii tre dati in un valore da dare in pasto al Criteria1.

Per brevità e pigrizia, ho sperimentato tre celle sul foglio di lavoro stesso, come le seguenti:

  Ed ecco le due macro escogitate, limitate al filtraggio su data scadenza:

Sub FiltraPerData()

  'ActiveSheet.ShowAllData

  Dim d As Date

  d = DateSerial(Range("aa"), Range("mm"), Range("gg"))

  Range("InizElenco").AutoFilter Field:=12, Criteria1:=d

 'la costante sarebbe del tipo #1/13/2006#

End Sub

Sub TogliFiltro()

  Range("InizElenco").AutoFilter

End Sub

Mi limito alla prima macro e tralascio i commenti in verde, dico solo che “aa”, “mm” e “gg” sono in nomi pre-assegnati alle celle del giorno, mese e anno, mentre “InizElenco” soddisfa la mia maniacalità coi nomi di zona (così il tutto funziona anche se l’elenco non ha intestazioni a riga 1).

Tutto va ben, madama la Marchesa? Non al 100%

Sì se il nostro amico si accontenta: la macro FiltraPerData lavora regolarmente, almeno coi dati e coi formati data da lui adottati. Ma basta modificare questi formati perché si torni alla strana scomparsa dei record!

Per rendersene conto basta 1. selezionare le celle in questione; 2. scegliere Formato > Celle poi nella scheda Numero impostare il formato Data 14/3/01 per vedere di nuovo il fallimento della sudata FiltraPerData. Idem, con disappunto dei puristi!, se si imposta il formato Generale.

Ed Excel 2007 ? Registratore OK, problema generale no

 Con la neonata versione il Registratore fornisce una macro del genere seguente (ottenuta su un diverso minielenco di test (con un campo data in colonna 2 anche qui col formato data del nostro visitatore):

Sub FiltraSuData

   Selection.Autofilter

   Range("A1:F10").AutoFilter Field:=2, _

   Operator:=xlFilterValues, _

   Criteria1:=Array(0, "13/01/2006")

End Sub

La sintassi ora adottata è diversa e più articolata.  Attenzione, però, che non è nuova: le chiavi Operator e Array esistono anche nel VBA 2003 (v. Guida). La buona notizia è che ora la macro registrata funziona come nell’uso manuale. Quella cattiva è che il malfunzionamento rispetto al cambiamento di formato rimane tale e quale.

Il pasticcio avrà sicuramente le sue motivazioni (tipologiche, direi) ma, a mio modesto avviso, resta tale.

La soluzione rigorosa: il Filtro avanzato

Il Filtro Avanzato è dato per noto, ricordo solo che esso richiede un intervallo dei criteri, separato dall’elenco, contenente le condizioni necessarie. Inoltre se si lancia almeno una volta il Filtro avanzato, tale intervallo assume automaticamente il nome standard “Criteri”, che da quel momento conserva.

Personalmente poi prediligo una zona “Criteri” formata semplicemente da due celle:

1)     la cella superiore, di intestazione, contenente un’etichetta qualsiasi che in questo caso deve essere diversa da quelle che intestano i campi dell’elenco;

2)     la cella sottostante, contenente una condizione sotto forma di formula booleana (comprendente, se occorre, le funzioni logiche E() e/o O()).

Esempio banale:

L’ipotetico elenco ha un campo intestato NOME, comprendente per l’appunto vari nominativi. Volendo filtrare tutti i “Pippo”, la zona criteri “normale” sarebbe intestata NOME e conterrebbe la stringa “Pippo” nella cella sotto. La zona criteri che preferisco, testé esaminata restituisce VERO o FALSO nella formula sopra indicata, a seconda che la cella A2 contiene o meno il fatidico “Pippo”. In sostanza il Filtro funziona così: esamina tutte le celle da A2 in giù e visualizza solo quelle per le quali la predetta formula dà VERO, occultando le altre.

In tutta la faccenda è fondamentale l’uso di riferimenti relativi per i termini che cambiano, usando riferimenti assoluti per quelli fissi. Così in luogo della stringa “Pippo” si potrebbe avere nella formula condizionale di cui parlo il riferimento a una ben precisa cella, diciamo F4, contenente “Pippo” o, in seguito, “Ada”, “Gino” e quant’altro. In tal caso la formula sarebbe la seguente (notare i dollari!):

=A2=$F$4

Ciò premesso ecco come si applicano questi principi all’esempio concreto che tormentava il nostro amico. L’intervallo “Criteri” ha l’aspetto seguente:

Ove L2 è la prima cella del dannato campo della data scadenza e aa, mm, gg sono i nomi assegnati all’anno mese e giorno cercato, come visto sopra. Ripeto che, ovviamente, la formula indicata restituisce VERO o FALSO, inoltre faccio notare che i nomi di cella sono, normalmente, riferimenti assoluti.

Ma anche i Criteri “normali” vanno bene

In tal caso – più “tranquillo” per i comuni mortal-utenti – l’intervallo Criteri va intestato come nel database,, mentre la cella della condizione potrebbe contenere la sola formula che attinge alle celle  aa, mm, gg:

In entrambi i casi il discorso si conclude con le due macro seguenti:

Sub FiltraAvanzPerData()

  Range("InizElenco").CurrentRegion.AdvancedFilter _

   Action:=xlFilterInPlace, CriteriaRange:= _

   Range("Criteri"), Unique:=False

End Sub

Sub TogliFiltroAvanz()

  On Error Resume Next 'Annullamento brutale dell'errore

  'che si scatena quando il Filtro non è impostato

  ActiveSheet.ShowAllData

End Sub

I buoni intenditori cui mi rivolgo a questo punto, non hanno che da esaminarle e… meditare, ricordo solo che CurrentRegion individua l’intervallo dell’elenco, a partire dalla sua cella iniziale, anche al mutare delle sue dimensioni (alla condizione – attenzione! – che sia “compatto” e, soprattutto non comprenda righe vuote). Essi constateranno che questo filtraggio lavora coi numeri seriali ergo funziona correttamente con qualunque formato data nonché col formato Generale.

La stranezza del formato data inglese

Anticipata all’inizio. Quel che segue deriva da un carteggio con un esperto di Filtro Automatico, certo Riccardo Ufilugelli, che mi aveva spedito la seguente routinetta, a correzione di una mia Filtra_data errata:

Sub Filtra_dataBIS()

   Range("A1").AutoFilter Field:=2, Criteria1:=">=01/11/2007", Operator:= _

   xlAnd, Criteria2:="<=01/11/2007"

' Adesso funziona ma il formato deve essere "mm/gg/aaaa" (*)

End Sub

Come tutti comprendono, il campo 2 (Fiels:=2) contiene date e la precedente Sub pretende di filtrare i record dell’11 gennaio 2007.

Ed ecco la mia replica a Riccardo:

Grazie e interessante. Ma il mistero s’infittisce. Per prima cosa, è strano e a mio avviso criticabile, che in un filtro per “gente comune” occorra fissare DUE criteri in AND.

E tuttavia – ATTENZIONE! - prova questa variante, sulla carta più “umana” (e logica: che minchia c’entra l’AND in questo banale caso?), ma che fallisce!:

Sub Filtra_dataTER()

   Range("A1").AutoFilter Field:=2, Criteria1:="=01/11/2007"

' Adesso NON funziona. Idem con Criteria1:=” 01/11/2007”  (*)

End Sub

(*) Il Registratore segnala Criteria1:= ” 01/11/2007”

Insomma, come al solito, una ragione di queste stranezze ci sarà ma c’è da ammattire. Ed ecco uno dei motivi per cui preferisco il Filtro Avanzato. Che per giunta è molto più potente…

FINE DELL’INCUBO SINTATTICO.

posted on mercoledì 31 gennaio 2007 8.01