lunedì 23 gennaio 2012
#
Un Ribbon spartano ma efficace per Excel (e chi si accontenta)
Con l’avvento della barra multifunzione, Ribbon in inglese, vecchi utenti di Excel continuano a rimpiangere i pur vetusti (e obsoleti? No comment) menu e soprattutto la possibilità di creare barre personalizzate, adatte a questo o quel modello. Queste in verità restato in vita su modelli creati in precedenza, purtroppo confinate in cartelle chiuse (perdendo una delle loro virtù, il floating e le macro associate sono garantite.
Nota. Con qualche spiacevole incompatibilità, perché non pochi metodi e proprietà sono stati occultati e, addirittura, annullati. Il caso più imbarazzante è FileSearch. Digitando “Application.” Il magico intellisense non lo fornisce, ma si forza Application.Filesearch poi tale mago ce ne dà tutti i vari figli, come LookIn, FindFiles, Execute ecc. Un’illusione che dura poco, perché tali verbi danno errore a run-time.
Intendiamoci, si può sempre creare un Ribbon personalizzato e anch’io ne ho parlato su questo blog ma l’operazione, da compiere con un (ostico) testo XML, è macchinosa. E in parte delude, ma non è di questo che qui voglio parlare.
Far sparire il Ribbon
Con Excel VBA si può. Accontento impazienti & curiosi mostrando il piccolo segreto con una macro altalenante:
Public CelaSvela As Boolean ' Definita a livello modulo (Dichiarazioni)
Sub CelaSvelaRibbon()
If Not CelaSvela Then
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",False)"
Else
Application.ExecuteExcel4Macro "SHOW.TOOLBAR(""Ribbon"",True)"
End If
CelaSvela = Not CelaSvela
End Sub
Le istruzioni segrete sono in grassetto e volendo si può inserire la prima nella routine dell’evento Open e, la seconda, in quella di BeforClose per occultare il ribbon all’utente e, rispettivamente, ripristinarglielo.
La semplice ricetta, inizialmente ingenua...
La scomparsa del nastrone si può giustificare in un modello più o meno chiuso per l’utilizzatore ma ovviamente abbiamo l’obbligo morale di offrire alla sua vista oggetti su cui cliccare per lanciare operazioni compiute da codice VBA. A tale scopo, l’ideuzza consiste nell’utilizzare la riga 1 opportunamente larga come ricettacolo, quantomeno, di pulsanti ivi incorporati. Con due mosse:
1. Selezionare una cella a riga2;
2. Dalla tab Visualizza scegliere Blocca riquadri > Blocca riga superiore.
In tal modo la prima riga resta fissa e visibile mentre si fa lo scrolling verso il basso e può ospitare titoli e oggetti vari cui associare macro secondo modalità che do per arcinote (altrimenti che ci state a fare qua? Tornate quando la sapete un po’ più lunga...).
Il più delle volte ci si accontenta di un’associazione uno a uno. Ma se volessi una specie di menu con opzioni multiple? Si può, si può. Ed ecco la ricetta finale, che sfrutta una ComboBox rispetto alla quale i più bravi faranno spallucce (ce lo sapevo, ce lo sapevo!) mentre le masse credo che ammetteranno di non averci mai pensato. Infatti tutto il mondo o quasi (me compreso) usano tali caselle per attingere valori da un elenco.
Ma ecco il da farsi, in dettaglio:
1. Scheda Sviluppo, poi Modalità progettazione ;
2. Tab Inserisci > Controlli ActiveX > Casella combinata;
3. Selezionare la ComboBox, posizionarla e dimensionarla sulla riga 1 e assegnarle, almeno, la proprietà Caption in modo consono a quel tal caso applicativo;
4. Inserire in una zona di celle, diciamo H2:H5, meglio se denominata “Opzioni” (*), i testi “Questo”, “Codesto”, “Quello” e “Nulla” , di deprecabile umorismo (ma è solo per fare un esempio a casaccio);
5. Fissare la proprietà ListFillRange a Opzioni, lasciando indefinita la LinkedCell che qui non serve e disattivare la Modalità progettazione. Ovviamente ora cliccando sul controllo si accede alle buffe scelte predette.
Nota (*) In tal modo si può spostare l’intervallo senza che la ComboBox cambi le sue scelte. Un altro modo per toglierlo alla vista dell’utente è assegnare il colore bianco ai caratteri delle celle Opzioni, mentre il formato nascosto ;;; non va bene (la ComboBox mostrerebbe tutte opzioni blank.
6. Il discorso si chiude con il possibile codice macro qui sotto riportato (ma attenzione!, esso presenta un malfunzionamento imprevisto...).
Dim ComboAttivato As Boolean ' Variabile a livello Dichiarazioni
Private Sub ComboBox1_Click()
ComboAttivato = Not ComboAttivato ' Toggle da True a False e viceversa
If ComboAttivato Then
Scelta = ComboBox1.Text ' Va bene anche ComboBox1.Value
Select Case Scelta
Case "Questo"
MsgBox "Prima scelta" ' O, meglio, codice ad hoc
Case "Codesto"
MsgBox "Seconda scelta" ' O, meglio, codice ad hoc
Case "Quello"
MsgBox "Terza scelta" ' O, meglio, codice ad hoc
Case "nulla"
MsgBox "Quarta scelta" ' O, meglio, codice ad hoc
End Select
' ComboBox1.Enabled = True 'EVITARE!
' (Valido solo per NON usare più il combo)
ComboBox1.ListIndex = 0 ' Pone la prima scelta
ActiveCell.Select
' Torna alla cella attiva (disattivando di fatto il controllo)
End If
End Sub
A questo punto, per pigrizia più che per sadismo, affermo che i commenti incorporati dovrebbero bastare. Dico solo che il booleano ComboAttivato evita un comportamento, all’inizio, inatteso: senza di esso e in assenza di If ComboAttivato... End If l’istruzione ComboBox1.ListIndex = 0, in verità non indispensabile ma che serve a rimettere in vista la prima scelta (contro sconcerti dell’utente), scatena a sorpresa un evento che lancia comunque MsgBox “Prima scelta”.
Di questo incredibile dictu non è facile darsi piena ragione. Ma chi se ne impipa? La pezza di ComboAttivato dovrebbe funzionare. Ahimè NON è così, inoltre si scopre un altro inghippo che la macro d’evento mette in luce. Pensa e ripensa la causa di questi guai è presto trovata e la corretta soluzione pure.
La riporto dopo una serie di righe intenzionalmente vuote, invitando a non leggerla, ossia sforzandosi di trovarla da soli.
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
RIGA VUOTA
La ricetta semplice, e funzionante
La causa dei malfunzionamenti di cui sopra è presto detta: l’evento Click del ComboBox equivale all’evento Change e come questo si scatena se e solo se viene scelta un’opzione diversa da quella corrente.
Il rimedio è presto trovato. Non basta un ComboBox, occorre aggiungervi un pulsante che lanci il codice relativo alle varie opzioni, dopo aver selezionato quella voluta agendo sulla frecciotta a destra del Combo. La situazione è rozzamente raffigurata qui sotto:
|
Scelte Varie |
|
|
Questo |
V |
|
SCEGLI |
|
Etichetta (rettangolo arrot. )
ComboBox1
CommandButton4
E il codice VBA? È quasi identico a quello dell’evento Click del ComboBox, ma stavolta si scatena cliccando sul CommandButton4 (è il nome che ho io, ma ciascuno avrà il suo):
Private Sub CommandButton4_Click()
' Scelta dell'opzione del ComboBox1
Dim Scelta As String
Scelta = ComboBox1.Text ' Va bene anche ComboBox1.Value
Select Case Scelta
Case "Questo"
MsgBox "Prima scelta" ' O, meglio, codice ad hoc
Case "Codesto"
MsgBox "Seconda scelta" ' O, meglio, codice ad hoc
Case "Quello"
MsgBox "Terza scelta" ' O, meglio, codice ad hoc
Case "Nulla"
MsgBox "Quarta scelta" ' O, meglio, codice ad hoc
End Select
ComboBox1.ListIndex = 0 ' Pone la prima scelta
ActiveCell.Select
End Sub
Miscellanea di inghippi e piccoli segreti
Fuori programma, ma per analogia con le particolarità citate sopra, ne aggiungo altre due un po’ alla rinfusa.
Che fine ha fatto il Modulo Dati?
Sto parlando del comodo comando Dati > Modulo... per navigare su un elenco presente nel foglio di lavoro. Nella Barra multifunzione non ce n’è traccia. Qualche veterano magari se la cava con l’equivalente codice VBA (rimasto vivo e vegeto, contrariamente al dannato FileSearch):
ActiveSheet.ShowDataForm
Se ne ottiene una finestra di dialogo bell’e pronta, essenziale ma abbastanza completa – navigazione, modifica, filtraggio – anche se limitata a 32 campi.
Comunque non occorre scomodare il VBA. Anche con file .xlsx (privi di macro in Office 2010) tale maschera di record si può inserire nella barra di accesso rapido. Occorre un po’ di pazienza, andando nelle Opzioni di Excel, poi cliccare Barra di accesso rapido, quindi – attenzione! – attivare Scegli comandi da: > Comandi non presenti sulla barra multifunzione, trovare nel susseguente elenco l’icona Modulo...e, infine, trascinarla sul pannello di destra con il pulsante Aggiungi >>. Superfluo dire che tali manovre possono ternare utili con altri oggetti mancannti sull’ingombrante Ribbon.
Caption su 2 righe in un pulsante ActiveX
A prima vista, ovvero nel progetto manuale, non sembra esserci verso di impostare la Caption su due (o più) righe. Esempio:
Ma tranne improbabili segreti, un modo c’è. Consiste nel lanciare una tantum un’istruzione VBA come questa:
CommandButton1.Caption = "Premi qui" & Vblf & "per favore"
Il risultato è permanente, se qualcuno ne dubitasse.
Il pannello segreto GodMode di Windows 7
Premesso che le misteriose operazioni che sto per descrivere NON valgono per Windows XP, eccone i passi qui di seguito.
1. Creare sul desktop una nuova cartella (di file) denominandola esattamente così:
GodMode.{ED7BA470-8E54-465E-825C-99712043E01C};
2. Premere Invio, creando così il pannello GodMode;
3. D’ora in poi un doppioclic su di esso fa apparire una lista di ben 283 voci che danno adito ad altrettante funzioni più o meno speciali. Divertitevi.
?>
martedì 20 dicembre 2011
#
Spedire circolari Outlook da indirizzari Excel
La tecnologia OLE Automation , detta anche sinteticamente Automation, permette proficui dialoghi fra applicativi. Si tratta di un sistema Client/Server dove il primo (Client) richiama la libreria del secondo (Server) utilizzandone proprietà e metodi.
Il codice macro che propongo deriva da una soluzione più completa dovuta a un mio corrispondente conosciuto tramite ShareOffice. Trattasi – onore al merito! – di Ettore Caliendo che tra l’altro sfrutta una caratteristica non a tutti ben nota: il formato HTML per il messaggio Outlook. Di conseguenza si può inglobare nel messaggio un’immagine, il logo della ditta nella fattispecie, mediante il tag (evidenziato in giallo, insieme ad altre particolarità, nella macro qui sotto riportata).
Il predetto Ettore all’inizio mi aveva palesato una perplessità, dovuta al fatto che tale logo non perveniva ai suoi corrispondenti. Per non sapere né leggere né scrivere (in tema di messaggi Outlook in formato HTML, lo confesso...) tuttavia gli ho subito detto: dovresti inserirla su un sito web, altrimenti come potrebbero gli estranei vedere la figura che risiede sul TUO PC?.
Compresa l’antifona, il rimedio è stato provato e andato in porto, con reciproca soddisfazione. Ed ecco, a beneficio delle masse, una Sub mutuata dal sullodato Ettore e un po' semplificata che va incorporata in un opportuno archivio Excel, tipo MessOutlook.xls, o MessOutlook.xlsm con Office 2010. Ho pensato di corredarla di due argomenti, il Cliente e l’IndirEmail di chiara semantica:
------------------
Sub MessHTML(Cliente As String, IndirEmail As String)
Dim Corpo As String
Dim Outlk As New Outlook.Application ' Early binding
Dim MioMess As Outlook.MailItem
Set MioMess = Outlk.CreateItem(olMailItem)
Corpo = "<html><body><p>"
Corpo = Corpo & "<img border='0' src='http://www.elicasrl.eu/loghi/logo_Elica.bmp'> "
Corpo = Corpo & " "
Corpo = Corpo & "<img border='0' src='http://www.elicasrl.eu/loghi/logo_Crif.bmp' ></p>"
Corpo = Corpo & "<p><font face='Tahoma' size='4'>Gentilissimo/a " & Cliente & ",<br>"
Corpo = Corpo & "la presente per richiederLe quanto segue: <br><br><br>"
Corpo = Corpo & "<br><br>"
Corpo = Corpo & "RingraziandoLa anticipatamente, Le porgiamo distinti saluti.<br><br>"
Corpo = Corpo & " Elica srl<br>"
Corpo = Corpo & " Rossi Asdrubale <br><br><br>"
Corpo = Corpo & "-------------------------------------------------------------------------<br>"
Corpo = Corpo & "TRAFFICI VARI srl<br>"
Corpo = Corpo & "Via Sette bellezze n. 103<br>"
Corpo = Corpo & "00123 Roma (RM)<br>"
Corpo = Corpo & "e-mail: settebell@gmail.it<br>"
Corpo = Corpo & "Tel. ******<br>"
Corpo = Corpo & "Fax ******<br>"
Corpo = Corpo & "web: www.settebell.com<br>"
Corpo = Corpo & "</font></p>"
Corpo = Corpo & "</body>"
With MioMess
.Subject = "Richiesta chiarimenti"
.Bcc = "pallino.pinco@lib.it"
.To = IndirEmail
.HTMLBody = Corpo
End With
MioMess.Send ' Invia il messaggio
' Libera le variabili “pesanti”:
Set MioMess = Nothing
Set Outlk = Nothing
End Sub
------------------
A ignari & immemori comincio col ricordare che per funzionare Automation chiede che nell'Editor VBA si fissino i riferimenti opportuni, mediante il comando Strumenti > Riferimenti... scegliendo poi Microsoft Outlook 12 Object Library nel nostro caso (diventa Outlook 14 con Office 2010).
Qui vale la pena di fare un paio di osservazioni. La prima è l’utilizzo della sintassi As New Outlook.Application, che al pregio della sinteticità unisce quello di fornire il cosiddetto Early Binding (associazione immediata) contrapposto al Late Binding (associazione tardiva) offerto invece dalla classica istruzione utilizzata dal nostro amico:
Outlk = CreateObject(“Outlook.Application”)
Nel qual caso la variabile è del tipo generale Object, con due conseguenze: maggior lentezza e segnalazione di errori solo a run-time, unita alla mancanza dell’intellisense. Infatti solo con l’early binding accanto al punto (.) della variabile Outlk spuntano, comodamente, cose come .Subject (l’oggetto del messaggio), .Bcc (il corrispondente in “carbon copy”, che può convenire assumere come il proprio email, per un riscontro, ovviamente al posto del comico e fantomatico pallino.pinco@lib.it!), .To IndirEmail (l’email del destinatario) e .HTMLBody = Corpo (il “corpo” del messaggio in formato HTML).
La seconda osservazione è il fatto che il buon Ettore ha impostato i riferimenti non a Microsoft Outlook bensì a Microsoft Office 12 (o 14 nell’edizione 2010), con un certo mio stupore! A caldo penso che questa sia più generale, ergo più “pesante” di quella che userebbe il sottoscritto. Ma non ne sono sicuro.
Nota. Posso però testimoniare che, in un’altra circostanza – Automation di Word – la libreria MS Office NON presentava una certa proprietà... Misteri dell’OLE Automation...
Tornando al testo del messaggio, a parte la sua genericità, che travalica il più squallido umorismo, esso dovrebbe essere chiaro a chiunque mastica il linguaggio HTML, faccio solo notare che con il concatenamento Gentilissimo & Cliente si ottiene l’inserimento del nome del destinatario nel corpo del messaggio stesso.
A questo punto, fatte le debite personalizzazioni, gl’interessati possono testare la predetta routine con un’istruzione del tipo seguente:
MessHTML "Michele Topo", topolino@disney.com
Più vantaggiosamente si può pensare a una serie di nominativi sul foglio Excel affiancati dai rispettivi indirizzi di posta elettronica:
Eccetera.
Immaginando, per estrema semplicità che i due dati siano collocati nelle colonne A e B a partire da A1, si potrebbe ricorrere alla procedura dell’evento BeforeDoubleClick:
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
If Target.Column <> 1 Or Target.Value = "" Then
Cancel = True ' Annulla il minieditor della cella
Exit Sub
End If
MessHTML Target.Value, Target.Offset(0, 1).Value
Cancel = True ' Annulla il minieditor della cella
End Sub
Che sul doppio click in una cella non vuota in colonna A lancia il massaggio Outlook a quel dato soggetto.
Nota ultimissima. In altri mondo OLE Automation si ha la proprietà Visible, che va impostata a True se si vuole che Excel o Word siano visualizzati, altrimenti agiscono nell’ombra. Nel caso Outlook Visible non esiste proprio. Poco male, perché l’applicativo può, indifferentemente, agire in background o allo scoperto, qualora fosse già aperto sulla nostra scrivania.
Mi fermo qui suggerendo agl’interessati altri esperimenti, in particolare un’applicazione di circolari più o meno standard, nel qual caso un opportuno ciclo del tipo For Each Nome In Indirizzario... Next sarà al centro del lavoretto o lavorone che ciascuno saprà implementare.
Ultim’ora: un’altra possibilità, anzi due
Questa variante mi è venuta in mente nel metrò (giuro). Consiste nell’uso della sintassi ; ; ... della casella A: (oppure Cc: o Ccn) che ha il vantaggio di registrare in Outlook un’unica missiva a più corrispondenti. Ed ecco la ricetta, che comprende due varianti. La prima consiste nel limitarsi al solo argomento IndirEmail della routine in questione:
(questo perché ora non è possibile specificare i singoli nominativi? ma più avanti vedremo che si può)
La seconda variante è un suggerimento, ovvero usare il metodo Save in luogo di Send. In tal modo si rimanda l’invio a tempi successivi, di tipo manuale ma con possibilità di adattamenti e/o ri-usi. Ed ecco il risultato:
Sub MessHTML(IndirEmail As String)
. . . idem c. s. . . .
With MioMess
.Subject = "Richiesta chiarimenti"
.Bcc = "pallinopinco@lib.it"
.To = IndirEmail
.HTMLBody = Corpo
.Save
End With
Set MioMess = Nothing
Set Outlk = Nothing
End Sub
Ciò premesso, ecco una possibile macro:
Sub Circolare()
Dim Indirizzi As String, PrimaCellaIndir As Range, i As Integer
Set PrimaCellaIndir = Range("PrimaCellaIndir")
Indirizzi = PrimaCellaIndir.Value
i = 2
While PrimaCellaIndir(i).Value <> ""
Indirizzi = Indirizzi & ";" & PrimaCellaIndir(i)
i = i + 1
Wend
MsgBox Indirizzi ' Da usare solo in fase di debug
MessHTML Indirizzi
End Sub
Ritengo che la ricetta parli da sola, dico solo che “PrimaCellaIndir” è il nome affibbiato alla prima cella dell’indirizzario, che sarà un campo in Colonna B o altrove.
E la seconda possibilità appena preannunciata? Dapprima non ci avevo pensato, ma è semplice. Consiste nell’utilizzare entrambi gli argomenti dell’iniziale routine MessHTML, passandole come secondo argomento una stringa di nominativi concatenati, tutti terminanti con
. Bando alle chiacchiere, fornisco la soluzione completa, che parte da una tabella Nomi/indirizzi come già vista:
La routine, ripetuta per comodità del lettore (ma la parte evidenziata è l’unica modifica):
Sub MessHTML(Cliente, IndirEmail As String)
Dim Corpo As String
Dim Outlk As New Outlook.Application
Dim MioMess As Outlook.MailItem
Set MioMess = Outlk.CreateItem(olMailItem)
Corpo = "
"
Corpo = Corpo & "
"
Corpo = Corpo & " "
Corpo = Corpo & "
"
Corpo = Corpo & "
Gentilissimi " & "
" & Cliente & "
"
Corpo = Corpo & "la presente per richiederLe quanto segue:
"
Corpo = Corpo & "
"
Corpo = Corpo & "RingraziandoLa anticipatamente, Le porgiamo distinti saluti.
"
Corpo = Corpo & " Elica srl
"
Corpo = Corpo & " Rossi Asdrubale
"
Corpo = Corpo & "-------------------------------------------------------------------------
"
Corpo = Corpo & "TRAFFICI VARI srl
"
Corpo = Corpo & "Via Sette bellezze n. 123
"
Corpo = Corpo & "00123 Roma (RM)
"
Corpo = Corpo & "e-mail: settebell@gmail.it
"
Corpo = Corpo & "Tel. ******
"
Corpo = Corpo & "Fax ******
"
Corpo = Corpo & "web: www.settebell.com
"
Corpo = Corpo & "
"
Corpo = Corpo & ""
With MioMess
.Subject = "Richiesta chiarimenti"
.Bcc = "giannigiac@tin.it"
.To = IndirEmail
.HTMLBody = Corpo
MioMess.Save
End With
' MioMess.Send ' Alternativa possibile
Set MioMess = Nothing
Set Outlk = Nothing
End Sub
La macro connessa a un opportuno pulsante Activex:
Private Sub CommandButton1_Click()
Dim ZonaNomi As Range, Iniz As Range
Set Iniz = Range("InizNomi")
With Iniz
Set ZonaNomi = Range(.Cells(1), .End(xlDown))
End With
Dim Nomi As String, Indirizzi As String, i As Integer
Nomi = Iniz.Value & "
"
Indirizzi = Iniz.Offset(0, 1).Value
For i = 2 To ZonaNomi.Count
Nomi = Nomi & ZonaNomi(i).Value & "
"
Indirizzi = Indirizzi & ";" & ZonaNomi(i).Offset(0, 1).Value
Next
MessHTML Cliente:=Nomi, IndirEmail:=Indirizzi
End Sub
Meditate il ciclo in giallo, gente e vedrete che funziona.
?>
?>
?>
?>
giovedì 1 dicembre 2011
#
Generazione personale di numeri pseudo casuali
Nell’ultimo mio intervento nella sezione Articoli, ovvero:
http://blog.shareoffice.it/giannigiaccaglini/articles/11052.aspx
Ho esposto un procedimento che ritengo originale (e comunque ingegnoso, se permettete) in grado di “Scombussolare un documento Word” disponendo in modo random delle sue parti tri-letterali.
L’esperimento illustrato comunque curioso e istruttivo si è in un primo tempo presentato deludente per quanto riguarda il recupero del testo originario. Ne ho dedotto, erroneamente, che i casuali generati da Rnd senza ricorrere a Randomize sono inizializzati in modo sempre differente ad ogni riapertura del documento stravolto.
In realtà tale sequenza è identica e i primi numeri sono sempre 0,7055475; 0,533424; 5795186; 2895625; 301948; 7747401;... e via di seguito, in qualunque nuova sessione e sia con le versioni 2007 che 1010. Con le quali, dunque il recupero di documenti scombussolati e salvati può funzionare regolarmente.
Nota Permettendo così a se stessi o a un eventuale corrispondente amico di recuperare l’originale. Qui va anche evidenziato che, come si rendono conto quanti sperimentano ques’idea, che vengono comunque perdute le formattazioni. Qualcuno sa dire come porvi rimedio?
Comunque rimane il timore non tanto di future versioni VBA che alterino la funzione Rnd rendendola più aleatoria, ma piuttosto il fatto che questa eccessiva Regolarità si presenti fragile, magari nell’ipotesi che questo procedimento diventi noto in giro.
Il modo migliore per ovviare all’inconveniente, è dato da una sequenza molto ampia di numeri casuali ma che resta identica se se ne fissa il primo valore (il “seme”, in gergo).
Mi dicono che la cosa è possibile in Visual Studio con un complesso generatore Random, ma non nel convento VB6 su cui si fiondano le macro di MS Office. Per fortuna ci si può arrangiare con un algoritmo del cosiddetto metodo della congruenza lineare. Con un po’ di pazienza si può trovare descritto sul Web.
Illustrazione pratica del metodo con Excel
La formula che fornisce il successore di un siffatto casuale è abbastanza semplice:
xi+1= (a* xi + c) Mod m
ove Mod in VBA è proprio l’operatore che, per fare un esempio banale, con l’istruzione Resto = 12 Mod 5 ci dà il resto della divisione intera fra i due numeri interi, ossia 2 nella fattispecie.
Per comprendere più da presso la faccenda propongo di ricorrere a Excel. La figura seguente illustra un caso particolare dovuto alla scelta x0 = 1, a = 12, c=5, m=11. Le intestazioni di riga e colonna sono omesse ma l’intervallo è “appeso” in A1.
|
X0 |
1 |
1 |
|
a |
12 |
6 |
|
c |
5 |
0 |
|
m |
11 |
5 |
|
|
|
10 |
|
|
|
4 |
|
|
|
9 |
|
|
|
3 |
|
|
|
8 |
|
|
|
2 |
|
|
|
7 |
|
|
|
1 |
|
|
|
6 |
|
|
|
0 |
|
|
|
5 |
Le formule nelle celle di colonna C sono subito viste:
C1=B1
C2:C15=RESTO($B$2*C1+$B$3;$B$4)
Ove la seconda formula si ottiene al volo in tre mosse: 1. Selezione di C1:C15; 2. Digitazione della formula in C2; 3. Consolidamento della stessa con Ctrl+Invio.
Si constaterà una sequenza di numeri ciclica di lunghezza 11: 1, 6, 0, 5, 10, 4, 9, 3, 8, 2, 7... Chiaramente troppo breve. Addirittura con certi valori la lunghezza è 1, ossia del tipo 3, 3, 3, 3,...
L’algoritmo proposto
La teoria chiarisce i criteri da adottare per ottenere sequenze di massima lunghezza. In base ad essi diversi studiosi, tra cui il famoso Knuth, hanno escogitato opportuni valori dei parametri suddetti. Suggerisco quelli dovuti a Goodman e Miller: m=231 -1; a = 75; c=0
Il risultato è del tipo seguente:
|
X0 |
2310882914 |
2310882914 |
|
a |
16807 |
1767379603 |
|
c |
0 |
355182317 |
|
m |
2147483647 |
1692146806 |
|
|
|
785431221 |
|
|
|
160553238 |
|
|
|
1178810434 |
|
|
|
1730320663 |
|
|
|
275835367 |
|
|
|
1695302943 |
|
|
|
143534605 |
|
|
|
761970654 |
|
|
|
995794717 |
|
|
|
981747548 |
|
|
|
1114179335 |
|
|
|
2102165152 |
Stavolta, per maggior generalità ho assegnato all’intervallino dei parametri i nomi X0, a ed m, tralasciando c, che essendo nullo si può omettere nella formula base. Ed ecco infine le formule del caso:
B1=INT(CASUALE()*10000000000)
B2=7^5
B4=2^31-1
C1=X0
C2=RESTO(a*C1;m)
C3=RESTO(a*C2;m)
E via di seguito nelle celle sottostanti, a piacere. Premendo il tasto RICALCOLA, alias F9 si assisterà alla riproduzione di serie caratterizzate da un seme X0 variabile da zero a 10 alla decima.
Nota. I sullodati Goodman e Miller ne garantiscono una siffatta lunghezza, che poi corrisponde al range massimo di 10 cifre distinte supportate da Excel e, credo, anche da VB6.
Soluzione del nostro problema? problematica
Per lo scopo di cui all’inizio, sconvolgere un documento, salvarlo e recuperarne l’originale la soluzione è presto detta: usare un generatore come quello appena illustrato adottando come chiave un seme di massimo 10 cifre.
Chi legge attentamente anche l’articolo citato in apertura, se interessato e buon intenditore, può cavarsela anche da solo. Prima di chiudere tuttavia è doveroso precisare che nel frattempo ho scoperto un altro inghippo:
Il metodo sopra illustrato, tradotto in VBA purtroppo dà luogo a blocchi per “overflow”, a causa del limitato range del tipo Long.
Nota.Il tipo Long in VBA spazia da -2.147.483.648 a 2.147.483.647, mentre in Visual Studio il range è di 18 cifre, inoltre c’è pure un tipo ancor più ampio, Ulong). La cosa buffa è che Excel, essendo un foglio di calcolo accetta interi fino a 10^11 – 1, e pertanto sullo spreadsheet il metodo funziona.
Il guaio si può constatare, con raccapriccio, lanciando questo macrocodice:
Function NextRand(PrecRand) '
Dim a, m
a = CDec(7 ^ 5): m = CDec(2 ^ 31 - 1)
Dim NextRnd As Double
NextRnd = a * PrecRand Mod m
NextRand = NextRnd
End Function
Sub Prova()
x = 12345
For i = 1 To 1000
MsgBox x
x = NextRand(x)
Next
End Sub
Altrettanto male vanno le cose se ci si illude di sfruttare NextRand come funzione personale sul foglio di lavoro...
Ma ci si può accontentare
Provando e riprovando un po’ a casaccio ho trovato questi parametri: a = 16807, c = 78125, m = 2047, che danno una sequenza di casuali di lunghezza 93. Questa sarebbe troppo breve se si adottasse il metodo per una crittografia, “classica”, ovvero di cifratura. Ma Nel nostro sistema scombussolante le cose vanno decisamente meglio.
Si lanci il seguente macrocodice:
Sub Scombussola()
strOrig = "abcdefghijklmnopqrstuvwxyz"
strRand = "793152468"
NOrig = Len(strOrig): NRand = Len(strRand)
ReDim VettRand(NOrig)
j = 1
For i = 1 To NOrig
VettRand(i) = Mid(strRand, j, 1)
j = IIf(j = NRand, 1, j + 1)
Next
' Bubble sort
For i = 1 To NOrig - 1
For j = i To NOrig
If VettRand(j) < VettRand(i) Then
dep = VettRand(i)
VettRand(i) = VettRand(j)
VettRand(j) = dep
dep = Mid(strOrig, i, 1)
Mid(strOrig, i, 1) = Mid(strOrig, j, 1)
Mid(strOrig, j, 1) = dep
End If
Next j
Next
For i = 1 To NOrig
Debug.Print VettRand(i), Mid(strOrig, i, 1)
Next
End Sub
La routine crea un banale VettRand di interi casuali di lunghezza 9, che si succedono come nella strRand, quindi si procede al bubble sort sia di VettRand che della stringa originaria qui rappresentata dai caratteri dell’alfabeto. Ed ecco il risultato nella finestra di Debug:
1 d
1 m
1 v
2 o
2 f
2 x
3 u
3 c
3 l
4 p
4 g
4 y
5 w
5 e
5 n
Eccetera...
In prima battuta si evince che l’ordinamento di VettRand ha creato ripetizioni, la qual cosa potrebbe far sorgere qualche timore, che però viene (ragionevolmente) fugato dalla constatazione che ciò nonostante un buon grado di disordine nella stringa, stravolta come “dmvofxuclpgywen....”.
Mi fermo qui, confidando in commenti e suggerimenti da parte dei più esperti.
?>
?>
?>
sabato 19 novembre 2011
#
La CurrentPage delle tabelle pivot, con le date può far ammattire...
A volte qualcuno mi sottopone quesiti particolari, inattesi. Ecco l’ultimo, relativo alla proprietà CurrentPage di un oggetto PivotField di una tabella pivot:
“Come mai l’istruzione seguente che operava correttamente in Excel 2003:
ActiveSheet.PivotTables(NomeTabPivo).PivotFields(DaAna).CurrentPage = DomDaCer
Con la versione 2007 dà errore a run-time?
La DomDaCerc è una variabile di tipo Date e la pagina corrente è relativa a un campo DaAna anch’esso di tipo data.
Non dispongo più di Excel 2003, pertanto non sono in grado di avvalorare o smentire il dubbio del nostro amico, che nel frattempo mi ha comunicato che, in Excel 2007, ha corretto il (presunto) bug traducendo tutte le date in stringhe, alias etichette nel gergo Excel.
In un successivo ritaglio di tempo ho fatto delle prove su questo database posto su un foglio MioDb comprendente un campo Data con date del tipo 5/12/2011 (formato data):
|
Prodotto |
Area |
Data |
Fatturato |
|
Bulloni |
Centro |
11/05/2011 |
€ 5.079,00 |
|
Bulloni |
Centro |
03/10/2011 |
€ 7.400,00 |
|
Bulloni |
Nord |
11/05/2011 |
€ 5.145,00 |
|
Bulloni |
Centro |
11/05/2011 |
€ 6.283,00 |
|
Bulloni |
Sud |
15/08/2011 |
€ 6.409,00 |
|
Bulloni |
Sud |
15/08/2011 |
€ 6.959,00 |
|
Bulloni |
Centro |
03/10/2011 |
€ 3.013,00 |
|
Dadi |
Nord |
11/05/2011 |
€ 5.079,00 |
|
Dadi |
Centro |
11/05/2011 |
€ 2.636,00 |
|
Dadi |
Centro |
15/08/2011 |
€ 6.283,00 |
|
Dadi |
Centro |
03/10/2011 |
€ 6.409,00 |
|
Dadi |
Centro |
03/10/2011 |
€ 6.959,00 |
|
Dadi |
Nord |
15/08/2011 |
€ 3.013,00 |
|
Rondelle |
Nord |
15/08/2011 |
€ 6.929,00 |
|
Rondelle |
Sud |
03/10/2011 |
€ 4.827,00 |
|
Rondelle |
Sud |
11/05/2011 |
€ 6.283,00 |
|
Rondelle |
Sud |
15/08/2011 |
€ 6.409,00 |
|
Rondelle |
Sud |
15/08/2011 |
€ 6.959,00 |
|
Viti |
Nord |
15/08/2011 |
€ 3.013,00 |
|
Viti |
Centro |
15/08/2011 |
€ 4.864,00 |
|
Viti |
Nord |
15/08/2011 |
€ 4.192,00 |
|
|
|
|
(ho omesso intestazioni di riga e colonna, visto che l’elenco parte dalla cella A1)
A MioDb ho appioppato una tabella Pivot Multidim1, posta su altro foglio FoglioPivot:
Nota. La figura qui sotto mostra la situazione in cui il campo Data della tabella pivot viene filtrato secondo la data 11/05/2011 e di conseguenza tutti gli altri valori sono inerenti a tale data). Cliccando sulla casella a discesa si scoprono tutti gli elementi mediante l’opzione (Tutto), ovvero: 11/05/2011; 15/08/2011; 03/10/2011; (Tutto). Rivedere la figura precedente e notare che a (Tutto) corrisponde in VBA (inglese!) la sgtringa “(All)”.
|
|
A |
B |
C |
D |
E |
F |
G |
|
1 |
Data |
11/05/2011 |
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
3 |
|
|
Prodotto |
|
|
|
|
|
4 |
Area |
Dati |
Bulloni |
Dadi |
Rondelle |
Totale complessivo |
|
|
5 |
Centro |
Somma di Fatturato |
11362 |
2636 |
|
13998 |
|
|
6 |
|
Somma di fatturato2 |
11362 |
2636 |
|
13998 |
|
|
7 |
Nord |
Somma di Fatturato |
5145 |
5079 |
|
10224 |
|
|
8 |
|
Somma di Fatturato2 |
5145 |
5079 |
|
10224 |
|
|
9 |
Sud |
Somma di Fatturato |
|
|
6283 |
6283 |
|
|
10 |
|
Somma di Fatturato2 |
|
|
6283 |
6283 |
|
|
11 |
Somma di Fatturato totale |
|
16507 |
7715 |
6283 |
30505 |
|
|
12 |
Somma di Fatturato2 totale |
|
16507 |
7715 |
6283 |
30505 |
|
|
13 |
|
|
|
|
|
|
|
Per comodità del visitatore riporto poi quel che dice la Guida VBA a proposito di CurrentPage:
|
PivotField.CurrentPage, proprietà |
Restituisce o imposta la pagina corrente visualizzata per il campo pagina. È valida solo per i campi pagina. Proprietà di tipo PivotItem di lettura/scrittura.
Sintassi
espressione.CurrentPage
espressione Variabile che rappresenta un oggetto PivotField.
Esempio
In questo esempio viene restituito il nome della pagina corrente per il rapporto di tabella pivot di Sheet1 all'interno della variabile di tipo string strPgName.
|
Visual Basic, Application Edition |
|
Set pvtTable = Worksheets("Sheet1").Range("A3").PivotTable
strPgName = pvtTable.PivotFields("Country").CurrentPage.Name |
Precisazione importante. A quanto sopra aggiungo che Pagina è un particolare campo della tabella pivot, in pratica è posto nella sua parte in alto. Il campo Data nel nostro caso. A tale proposito riporto un’istruzione che aggiunge appunto un campo del genere (il codice in grassetto dovrebbe chiarire il concetto):
With ActiveSheet
.PivotTables("Multidim1").AddFields RowFields:= _
"Area", ColumnFields:="Prodotto", PageFields:="Data"
.PivotTables("Multidim1").PivotFields("Fatturato"). _
Orientation = xlDataField
.Name = "AnalisiDim"
End With
Tornando all’assillo relativo al campo Data, ecco il codice adottato da chi scrive, che funziona regolarmente in Excel 2007:
Dim mioPivot As PivotTable
Set mioPivot = Sheets(“FoglioPivot”).PivotTables("Multidim1")
mioPivot.PivotCache.Refresh ' Aggiorna tabella pivot
Dim MiaData As Date
MiaData = #5/11/2011#
mioPivot.PivotFields("Data").CurrentPage = MiaData
Nota Si badi bene che per aggiornare il pivot occorre un’istruzione del tipo .PivotCache.Refresh da non confondere con .Update, metodo da usare quando il db originario, specie se esterno, ha subito modifiche.
Insomma non c’è da modificare le date in stringhe, occorre solo che il formato-data sia coerente con quello usato nel MioDB in quanto la tabella pivot mostra “quel che si vede” – a livello formato intendo sottolineare. Faccio notare che in ambiente VBA il formato INGLESE mese/giorno/anno viene regolarmente tradotto in italiano ossia 5/12/2011 ma, attenzione!, se digito il valore assegnato a MiaData come #May 12 20011# esso viene accettato ma automaticamente e inesorabilmente tradotto in #5/12/2011#. E se la formattazione adottata nel database è differente si ottiene un rigetto.
Per la cronaca anche quest’altro tentativo, che utilizza una cella di nome MiaData va a buon fine, ma sempre a condizione di avere lo stesso formato data dei campo presenbte sul database:
mioPivot.PivotFields("DataOrdine").CurrentPage = Range(“MiaData”).Value ' NB - Senza .Value si ha errore
Infine volendo ripristinare i valori globali del pivot si usa l’istruzione:
mioPivot.PivotFields("Data").CurrentPage = "(All)"
Repetita iuvant: non va dimenticato il metodo Refresh del pivot – o, più esattamente, della sua PivotCache - fra un’impostazione e l’altra della CurrentPage.
L’intera routine per creazione automatica della tabella pivot
La riporto qui sotto, affidandone l’esegesi a chi ha sufficiente padronanza del VBA:
Dim SwPivot As Boolean ' Definito a livello Dichiarazioni
Sub CreaPivot()
If SwPivot Then Exit Sub
With Sheets("AnalisiVendite")
.PivotTableWizard SourceType:=xlDatabase, _
SourceData:=.Cells(1, 1).CurrentRegion, TableDestination:="", _
TableName:="Multidim1", HasAutoFormat:=True, _
SaveData:=True
End With
With ActiveSheet
.PivotTables("Multidim1").AddFields RowFields:= _
"Area", ColumnFields:="Prodotto", PageFields:="Data"
.PivotTables("Multidim1").PivotFields("Fatturato"). _
Orientation = xlDataField
.Name = "AnalisiDim"
End With
SwPivot = True
End Sub
Per togliere di mezzo il pivot potremmo ricorrere a quest’altra Sub:
Sub EliminaPivot()
If SwPivot = False Then Exit Sub
Application.DisplayAlerts = False
Sheets("AnalisiDim").Delete
Sheets("AnalisiVendite").Activate
SwPivot = False 'Ridà la possibilità di creare il pivot
End Sub
?>
?>
lunedì 12 settembre 2011
#
Da diverse parti mi giunge la segnalazione che una formula come la seguente è rigettata per errore a run-time:
ActiveCell = CDbl(TextBox1)
ove TextBox1 è una casella di testo di una UserForm. Ovviamente al posto di ActiveCell si può avere qualunque codice relativo a una cella (tipo Cells(3, 4) e simili). In verità a chi scrive l'inconvienente lamentato non si presenta...
Sia come sia, tali formule possono funzionare anche senza aggiungere .Text alla TextBox e senza premettere CDbl, ma in tal caso nella cella viene scaricata una literal (etichetta, in gergo Excel), preceduta da un apice ('). Dopo di che l'utente può tradurla in numero con l'opzione specifica suggerita (*). Scomodo no?
Ma se proprio si desidera imporre a Excel la nostra volontà, occorre e basta usare la proprietà Formula o FormulaR1C1 o persino Value. Con riferimento alla cella attiva e a una generica TextBox1:
ActiveCell.Formula = CDbl(TextBox1.Text)
Si ottiene anche la massima chiarezza.
(*) Il fatto èn che con ActiveCell = TextBox1 VBA assume (sottintende) la proprietà Value sulla cella e Text per la casella di testo. Insomma tale sintassi equivale ad ActiveCell.Value = TextBox1.Text.
A beneficio dei principianti concludo con una mini soluzione che obbliga l'utente a inserire "qualcosa" nella casella TextBox1 nonché un dato convertibile in un numero di tipo Double:
Private Sub CommandButton1_Click()
Dim Num As Double
On Error GoTo Errore
If TextBox1.Text = "" Then
TextBox1.SetFocus
Exit Sub
End If
Num = CDbl(TextBox1.Text) ' Qui può scatenarsi l'errore
ActiveCell.Formula = Num ' Limitandosi alla cella attiva, per semplicità
Unload Me
Exit Sub
Errore:
MsgBox "Occorre un dato numerico!", vbCritical, "Errore"
With TextBox1
.Text = ""
.SetFocus
End With
End Sub
?>
?>
mercoledì 18 maggio 2011
#
Caselle di testo dinamiche inserite in un UserForm
Recentemente mi è stata posta questa domanda: “Si può inserire in una UserForm un numero variabile, fissato a run-time (magari tramite opportuna macro VBA) di caselle di testo?” La richiesta è inusuale, visto che il più delle volte una UserForm viene definita al tempo di progetto. Con un po’ di pazienza, frugando nella Guida dell’Editor VBA, ho trovato la risposta, di cui riporto un tipico esempio in fondo a questo post. Adattandolo al caso che tormentava il nostro amico ho escogitato una prima macro di test, sull’evento Click dell’unico pulsante di una UserForm1, per il resto vuota di altri controlli:
Private Sub CommandButton1_Click()
Dim miaTxtBox As Control
Set miaTxtBox = Controls.Add("Forms.TextBox.1", "miaCasella", True)
With miaTxtBox
.Left = 18
.Top = 150
.Width = 175
.Height = 20
End With
Me.Controls("miaCasella").Value = InputBox("Scrivi...")
End Sub
L’istruzione basilare, grassettata, ha la seguente sintassi:
Controls.Add(“<Tipo ctrontrollo>”, “Nome”,
E ha l’effetto di aggiungere (metodo Add) all’insieme dei Controls una TextBox definita come “Forms.Textbox.1” – mentre analoghe sintassi valgono per altri controlli come il "MSForms.CommandButton.1” esemplificato dalla Guida e altri di pari eloquenza. Il terzo argomento va prudentemente posto a True assicurando la visibilità della neonata casella impostata nella MiaTxtBoz (di tipo Control, ovviamente). Dopo di che a MiaTxtBox sono assegnati, dinamicamente, proprietà relative al posizionamento e alle dimensioni e, infine, vi viene inserito il valore stabilito dall’utente in risposta a una InputBox.
Come i più svegli subito notano nell’ultima istruzione si ricorre, in alternativa a miaTextBox, al nome “miaCasella”, secondo nome facoltativo di Controls.Add.
Se poi si vuole lanciare la UserForm1 non resta che lanciare una macro bene nota come questa:
Sub UserFormDinamica()
Load UserForm1
UserForm1.Show
End Sub
Una variante appena un po’ più elaborata è la seguente, che crea ben due (“e regali..”, dicono a Livorno) casellineficcandovi due scemenze:
Dim CaselleAggiunte As Boolean
Private Sub CommandButton1_Click()
If Not CaselleAggiunte Then
Me.Controls.Add "Forms.TextBox.1", "Casella1", True
Me.Controls.Add "Forms.TextBox.1", "Casella2", True
With Me.Controls("Casella2")
.Left = 50
.Top = 50
End With
CaselleAggiunte = True
End If
Me.Controls("Casella1") = "ambarabà"
Me.Controls("Casella2") = "Ciccì coccò"
End Sub
Il punto più rilevante è dato dalle istruzioni “creative” la cui sintassi è nata dalla scoperta che non occorre nessuna variabile per registrare il nuovo controllo, basta invocare il medoto Add dell’insieme Controls dell’UserForm corrente, ovvero Me.Controls.
Creazione dinamica di N casella all’apertura dell’UserForm
Dopo i precedenti esperimenti, torniamo all’esigenza iniziale ovvero, ricordate?, aggiungere un numero di TexBox definite di volta in volta. In tale prospettiva, si compiano le mosse seguenti:
1. Creare un UserForm2
2. Aggiungervi come prima un solo CommandButton1, magari dotato di Caption “Caselle dinamiche” in luogo del default “UserForm2”;
3. Digitare la macro dell’evento Initialize:
Private Sub UserForm_Initialize()
Dim i As Integer, N As Integer
Dim NomeCasella As String
Dim Cima As Integer
N = Range("NumCaselle")
Cima = 10
Me.Height = N * 25 + 75
CommandButton1.Top = Me.Height - 50
' If N > 10 Then N = 10 ' Limitazione num. caselle? Non serve col combo...
For i = 1 To N
NomeCasella = "Casella" & i
Me.Controls.Add "Forms.TextBox.1", NomeCasella, True
With Me.Controls(NomeCasella)
.Left = 50
.Height = 20
.Width = 150
.Top = Cima
End With
Cima = Cima + 25
Next
End Sub
Commenti tacitiani. Il valore N è attinto da una cella del foglio di lavoro preliminarmente battezzata “NumCaselle”, ove l’utente dovrà stabilire appunto il desiato numero caselle. Il clou sta nel ciclo For i = 1 To N in seno al quale vengono via via fissato un NomeCasella pari a “ Casella” & i vale a dire “Casella1”, “Casella2” e così via. Il dimensionamento Me.Heigth dell’altezza del Form in modo da adeguarsi alle N caselle (ciascuna di altezza, Height, pari a 20) + 5 spazi tra le stesse è lasciato all’esegesi autogestita. Idem il posizionamento del Top del CommandButton1, sottostante alla N caselline ma senza sparire.
E la Sub dell’evento Click di quest’ultimo? Eccone una ipotetica, che segnala (MsgBox) i nomi delle N caselle create inserendovi man mano numeri casuali. Tanto per fare qualcosa, nei casi personali ognuno personalizzerà questa faccenda.
Private Sub CommandButton1_Click()
Dim Ctrl As Control
For Each Ctrl In Me.Controls
If Left(Ctrl.Name, 7) <> "Command" Then
Randomize
Ctrl = Int(Rnd * 100000 + 100)
MsgBox Ctrl.Name
End If
Next
End Sub
Concludo suggerendo, in luogo del controllo del numero massimo N = 10, un combo box sul foglio di lavoro associato alla predetta cella di nome”NumCaselle”.
Ultimissima variante
Consiste nell’omissione del secondo argomento di Controls.Add, nel qual caso la caselle di testo via via create assumono i nomi default o standard che dir si voglia “TextBox1” ,” TextBox2”, eccetera.
Private Sub UserForm_Initialize()
Dim i As Integer, N As Integer
Dim Cima As Integer
N = Range("NumCaselle")
Cima = 10
Me.Height = N * 25 + 75
CommandButton1.Top = Me.Height - 60
For i = 1 To N
Me.Controls.Add "Forms.TextBox.1", , True
With Me.Controls(i)
.Left = 50
.Height = 20
.Width = 150
.Top = Cima
End With
Cima = Cima + 25
Next
End Sub
L’esempio della Guida
Esempio dell'evento AddControl e del metodo Add
In questo esempio il metodo Add viene utilizzato per aggiungere un controllo a un form in fase di esecuzione. L'evento AddControl viene utilizzato per verificare se il controllo è stato aggiunto.
Per eseguire l'esempio, copiare il codice riportato di seguito nella sezione Dichiarazioni di un form. Verificare che il form contenga:
· Un controllo CommandButton (CommandButton1).
· Un controllo Label (Label1).
Dim Mycmd as Control
Private Sub CommandButton1_Click()
Set Mycmd = Controls.Add("Forms.CommandButton.1") ', CommandButton2, Visible)
Mycmd.Left = 18
Mycmd.Top = 150
Mycmd.Width = 175
Mycmd.Height = 20
Mycmd.Caption = "This is fun." & Mycmd.Name
End Sub
Private Sub UserForm_AddControl(ByVal Control As _
MSForms.Control)
Label1.Caption = "Control was Added."
End Sub
Nota. Qui osservo, solamente, che a) definire a livello Dichiarazione la variabile Mycmd (o la mia Casella nel caso di una TextBox) non è indispensabile, come ho mostrato nei miei esempietti; b) il secondo pulsante di comando Mycmd creato di fatto non serve quasi a nulla, non potendo associarvi routine d’evento (possibilità speciale ammessa in VB .NET). Lo stesso vale per le nostre caselle di cui però si può modificare il valore.
?>
giovedì 21 aprile 2011
#
Esplorare uno alla volta i grafici di una serie multipla
Si abbia una serie di dati storici disposti su righe successive, che formano un intervallo denominato “ZonaDati”:
|
|
A |
B |
C |
D |
E |
. . . |
J |
K |
L |
M |
N |
|
1 |
|
|
|
|
|
|
|
|
|
|
|
|
2 |
|
|
|
|
|
|
|
|
|
|
|
|
3 |
1 |
2 |
3 |
4 |
5 |
6 |
10 |
11 |
12 |
|
Previsione |
|
4 |
|
|
|
|
|
|
|
|
|
|
|
|
5 |
1500 |
8000 |
5000 |
2001 |
1000 |
. . . |
1000 |
6550 |
777 |
|
21791 |
|
6 |
3210 |
6500 |
1000 |
8880 |
1000 |
. . . |
1000 |
4111 |
699 |
|
20429 |
|
7 |
5661 |
4000 |
966 |
6661 |
1000 |
. . . |
1000 |
21145 |
5000 |
|
10004 |
|
8 |
25569 |
2553 |
7441 |
4440 |
1000 |
. . . |
1000 |
3369 |
4000 |
|
43500 |
|
9 |
7000 |
6998 |
2000 |
2589 |
5994 |
. . . |
1000 |
9974 |
4889 |
|
6243,3 |
|
10 |
5894 |
4588 |
8999 |
1147 |
2444 |
. . . |
1000 |
1440 |
6001 |
|
9682,8 |
|
11 |
2001 |
30014 |
3500 |
3005 |
30459 |
. . . |
1000 |
30014 |
10025 |
|
20384 |
|
12 |
69974 |
5555 |
1000 |
97774 |
10002 |
. . . |
1000 |
2000 |
40025 |
|
2031,2 |
Volendo graficizzarli, per esempio con un tipo XY (“a dispersione”) il procedimento normale conduce a grafici multipli sovrapposti, tra l’altro con qualche problema qualora i singoli valori massimi e minimi siano troppo differenti. Le macro che propongo servono ad evidenziare il singolo grafico di ciascuna serie.
Prima soluzione, con doppio clic
Prima di procedere occorre creare manualmente il grafico, incollato sul foglio di lavoro, relativo alla prima serie. Dopo di che la prima soluzione sfrutta l’evento doppio clic. La fornisco come ricetta (che i più esperti sapranno comunque ben meditare), ricordando solo che la si ottiene selezionando il modulo Foglio1 quindi scegliendo Worksheet e BeforeDoubleClick nelle due caselline a discesa in alto (etichettate coi tip “Oggetto” e “Routine”):
Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
Dim ZonaDati As Range
Set ZonaDati = Range("ZonaDati")
If Intersect(Target, ZonaDati) Is Nothing Then
Exit Sub ' Esci se la cella è fuori zona dati
Else
Dim Nc As Integer ' Numero colonne
Nc = ZonaDati.Columns.Count
Dim RigaIniz As Integer
RigaIniz = ZonaDati.Row
Dim RigaDati As Range
Set RigaDati = ZonaDati.Rows(Target.Row - RigaIniz + 1)
RigaDati.Select
CambiaSerie RigaDati.Address
End If
End Sub
Ma cosa diavolo è CambiaSerie? Si tratta di una Sub, stavolta, inserita in un nomale Modulo1 in testa al quale si ha una variabile comunitaria (area Dichiarazioni) NumeroSerie:
Dim NumeroSerie As Integer
Sub CambiaSerie(RigaDati As String)
Dim QuestoGraf As ChartObject
Set QuestoGraf = ActiveSheet.ChartObjects("Grafico 1")
QuestoGraf.Chart.SeriesCollection.Add _
Source:=Worksheets("Foglio1").Range(RigaDati)
QuestoGraf.Chart.SeriesCollection(1).Delete
End Sub
Sub ProvaCambiaSerie()'Routine di prova
CambiaSerie "A7:L7"
End Sub
Premesso che “Grafico 1” è il nome default assegnato al grafico creato a mano (in caso contrario modificare a dovere “Grafico 1”) il procedimento inizialmente aggiunge un grafico assumendo come origine (Source) l’intervallo RigaDati passato come argomento, dopo di che viene eliminato (Delete) il grafico precedente, ovvero il membro numero 1 dell’insieme SeriesCollection.
Seconda soluzione, con pulsante incorporato
Si tratta, più semplicemente, di una comune routine presente anch’essa sul Modulo1 da abbinare a un pulsante “classico” di Excel (o magari a una shape, personalmente uso la forma “Telaio”). Eccola:
Sub EsploraGrafici()
swSfondo = Not swSfondo
Dim ZonaDati As Range, Previsioni As Range
Set ZonaDati = Range("ZonaDati")
Set Previsioni = Range("Previsioni")
NumeroSerie = _
IIf(NumeroSerie = ZonaDati.Rows.Count, 1, NumeroSerie + 1)
ZonaDati.Interior.ColorIndex = 17
Previsioni.Interior.ColorIndex = 17
Dim RigaDati As Range, CellaPrevis As Range
Set RigaDati = ZonaDati.Rows(NumeroSerie)
Set CellaPrevis = Previsioni(NumeroSerie)
RigaDati.Select
RigaDati.Interior.ColorIndex = 6
CellaPrevis.Interior.ColorIndex = 6
CambiaSerie RigaDati.Address
End Sub
In questo caso ho pensato di trattare anche l’intervallo N5:N12 posto sulla destra della zona dati (rivedere la figura iniziale) e battezzato “Previsioni”, le cui celle contengono formule statistiche di cui riporto solo la prima, posta in N5:
=PREVISIONE(13;A12:L12;$A$3:$L$3)
Nota. I dati forniti sono alquanto aleatori, idem pertanto i risultati di ciascuna previsione... Ma era solo un esercizio sulle macro.
Per evitare confusioni si considerino solo le istruzioni segnate in neretto, che sono quelle essenziali relative alle serie di dati. Ad ogni clic sul predetto pulsante la macro incrementa il NumeroSerie ciclicamente (ovvero riparte con 1 se si supera il numero di righe della zona dati (ZonaDati.Rows.Count) e successivamente viene fissata la nuova serie in RigaDati =ZonaDati.Rows(NumeroSerie) poi selezionata con RigaDati.Select e infine ne viene passato il riferimento (Address) alla già vista Sub CambiaSerie.
Le istruzioni intercalate (prive di grassetto) compiono un analogo mestiere sull’intervallo “Previsioni”, inoltre evidenziano in giallo la riga selezionata e la corrispondente cella di previsione.
?>