Il blog di Gianni Giaccaglini

Blog su VBA e VSTO
Gianni Giaccaglini

My Links

News

NB - V. anche gli ARTICOLI (in fondo a questa barra)
Solo quesiti validi a: giannigiac@tin.it
Il mio Best seller su VBA
(v. www.hoepli.it)


Il mio ultimo libro su Open XML
(v. www.FAG.it):



La mia nipotina ELISA

Foto con dedica a ME di
Bill Gates giovanissimo
nei mitici anni 80!

Categorie Post

Categorie Articoli

Archivio

Immagini

Blog Stats

lunedì 23 gennaio 2012 #

Un Ribbon spartano ma efficace per Excel

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:

Premi qui
per favore

 

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.

?>

posted @ 16.11 | Feedback (0)

martedì 20 dicembre 2011 #

Spedire circolari Outlook da indirizzari Excel

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'>&nbsp;&nbsp;&nbsp;&nbsp;"

  Corpo = Corpo & "&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;"

  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 & "&nbsp; &nbsp; &nbsp; Elica srl<br>"

  Corpo = Corpo & "&nbsp; 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:

Matteo Evangelista

matteoevang@gmail.com

Giovanni Telegrafista

giovantelegrafist@tin.it

 

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:

Mario Zaccariai

mario.zaccaria@gmail.com

Arturo Benedetti

arturbened@libero.it

TreDenari Paolina

treden@info.com

Rossi Michele

rossi.michele@boh.eu

 

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.

 

?>

?>

?>

?>

posted @ 10.49 | Feedback (0)

giovedì 1 dicembre 2011 #

Generazione personale di numeri pseudo casuali

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.

?>

?>

?>

posted @ 17.23 | Feedback (0)

sabato 19 novembre 2011 #

La CurrentPage delle tabelle pivot richiede precisione con le date

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

?>

?>

posted @ 11.57 | Feedback (0)

lunedì 12 settembre 2011 #

Come inserire in una cella dati numerici da una TextBox

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

?>

?>

posted @ 11.07 | Feedback (0)

mercoledì 18 maggio 2011 #

Caselle di testo dinamiche inserite in un UserForm

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.

?>

posted @ 16.37 | Feedback (0)

giovedì 21 aprile 2011 #

Esplorazione di singoli grafici di una serie multipla

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.

?>

posted @ 14.46