Gianni Giaccaglini

Tricks & mini applics on WPF
posts - 46, comments - 0, trackbacks - 0

Formule del foglio Excel segretate

  

Formule del foglio di lavoro segretate

Può capitare che un nostro file Excel Miomodello.xls rechi contenuti sensibili nel primo foglio di lavoro. Per tenerli lontani da occhi indiscreti ho pensato dapprima di adempiere a un suggerimento di Diego Cattaruzza, ossia trasferire valori e formule in un parallelo file MioModello.csv, da recuperare successivamente (previa cancellazione dei contenuti, ma conservando i formati). L’operazione in partenza si presentava macchinosa e ardua da automatizzare. Peggio ancora, dopo inenarrabili tormenti ho scoperto che il recupero del file .csv con comandi Excel (la via che ritengo più consona) è capriccioso, perché a volte presenta i dati tutti sulla colonna A, separati da virgole.

Così ho smesso di arrovellarmi, ma in seguito m’è venuta in mente una soluzione più semplice: trasferire in un foglio aggiuntivo segreto solo le formule abbinate alle coordinate di ciascuna. La ricetta funziona nei molti casi in cui le formule siano essenziali per ottenere i risultati di elaborazioni abbastanza complesse e non appesantisce troppo il Foglio2 (e l’intera cartella di lavoro).

NOTA. Anche il fatto che il metodo che mi accingo a illustrare si limiti al primo foglio (ma, volendo, generalizzabile, v. in fondo all’articolo) può restare valido qualora anche gli altri fogli dipendano fortemente dalle formule del primo.

Per studiare il marchingegno propongo un rudimentale modelluccio Il cui Foglio1 presenta grossomodo il layout seguente:

 

 

A

B

C

D

E

F

G

H

I

1

   

 

           

2

   

         Vendite

primo trimestre

2014

       

3

   

Gennaio

Febbraio

Marzo

 

Formule su Foglio2

 

4

 

Bulloni

144,00

155,00

183,00

   

5

 

Viti

183,00

122,00

175,00

       

6

 

Dadi

152,00

162,00

149,00

       

7

 

Rondelle

194,00

169,00

163,00

 

Ripristina Formule

 

8

 

Coppiglie

144,00

139,00

123,00

   

9

                 

10

 

TOTALI

817,00

747,00

793,00

       

11

                 

12

                 

 

Le formule nell’intervallo C4:E8 sono tutte =INT(CASUALE()*100+100) per cui restituiscono interi pseudo-casuali compresi tra 100 e 200, mentre a riga 10 si hanno scontate funzioni =SOMMA. La scritta in alto “Vendite primo trimestre 2014” deriva da un’unica formula in C2:
"Vendite primo trimestre "&ANNO(OGGI())

Va poi detto che in basso si ha un grafico non mostrato sopra, che raffigura le vendite di Bulloni, Viti, Dadi ecc. nei tre mesi fatidici. Infine ai due pulsanti “classici” a destra (della classe Modulo, non ActiveX) sono associate altrettante macro che svolgono il mestiere in ciascuno indicato. In realtà non sono visibili entrambi perché, come vedremo, l’attivazione di una macro rende invisibile l’uno facendo sparire l’altro.

La macro che porta le formule sul Foglio2...

Eccola, senza indugi.

Sub FormuleSuFoglio2()

  Dim miaCella As Range, i As Integer

  Dim miaForm As String, LungForm As Integer

  Sheets("Foglio2").Columns("A:B").ClearContents

  For Each miaCella In Foglio1.UsedRange

    If miaCella.HasFormula Then

      i = i + 1

      Foglio2.Cells(i, 1) = miaCella.Address(False, False)

       miaForm = miaCella.Formula

      LungForm = Len(miaForm)

      Foglio2.Cells(i, 2) = Right(miaForm, LungForm - 1)

      miaCella = ""

    End If

  Next

  Foglio1.Shapes("Pulsante 1").Visible = False

  Foglio1.Shapes("Pulsante 2").Visible = True

End Sub

COMMENTI. Questa routine esordisce ripulendo (ClearContens) le colonne A e B. Segue un ciclo che spazzola dalla prima all’ultima cella utilizzata (intervallo UsedRange) del Foglio1, B2:E10 nel nostro esempio verificando se contiene una formula (HasFormula), nel qual caso l’indirizzo (Address) viene posto in Foglio2.Cells(i, 1), ossia l’i-esima cella di colonna A, poi viene posta nella variabile stringa miaForm la formula (proprietà appunto Formula in assenza della quale viene comunque assunto il valore). Infine in Foglio2.Cells(i, 2), ovvero l’i-esima cella di colonna B, viene riportata la parte destra della formula meno un carattere.

Le due istruzioni finali, come anticipato, agiscono sulle forme (Shapes) di nome “Pulsante 1” e “Pulsante 2” occultando la prima e facendo riapparire la seconda.

Insomma, con riferimento al nostro modellino, al termine la situazione nel Foglio2 è la seguente:

C1

"Vendite primo trimestre  "&YEAR(TODAY())

C4

INT(RAND()*100+100)

D4

INT(RAND()*100+100)

 

... eccetera ...

 

... eccetera ...

E8

INT(RAND()*100+100)

C10

SUM(C4:C8)

D10

SUM(D4:D8)

E10

SUM(E4:E8)

Per accrescere la segretezza in un primo tempo ho preso due misure ingenue: occultamento del Foglio2 e colore bianco ai caratteri delle colonne A e B.

In realtà la misura davvero efficace consiste nel proteggere il foglio con tanto di password mediante Revisione > Proteggi foglio. All’apparire della finestra specifica oltre che inserire in alto la password è necessario togliere la spunta anche alle prime caselle di controllo Seleziona celle bloccate e Seleziona celle sbloccate. Ne consegue che l’utente non può attivare nessuna cella, visualizzando al più il contenuto di quella attiva nella barra della formula. A livello VBA il codice in questione è del tipo seguente:
Foglio2.Protect Password:="xxyyzz", Contents:=True

Mentre per eliminare la protezione occorre:
Foglio2.Unprotect

Cui l’utente deve reagire digitando l’ipotetico “xxyyzz” nella casella di testo che appare.

NOTA. Inutile dire che il codice di sprotezione e quello di protezione andrebbero inseriti a monte e, rispettivamente, a valle della routine FormuleSuFoglio2, esercizio che per pigrizia affido a chi legge.

Ma non basta, perché un utente scafato potrebbe sfruculiare nell’ambiente VBA e scoprire la famosa password proprio esaminando l’istruzione predetta Foglio2.Protect Password:=... con quel che segue. Il rimedio, noioso, consiste in questi passaggi: Alt+F11; Strumenti > scheda Protezione > clic sulla casella di controllo Proteggi dalla visualizzazione > indicare la password nelle due sottostanti caselle di testo.

... e la macro che le ripristina

Eccola subito:

Sub RipristinaFormule()

  Dim Riga As Range

  Foglio1.Activate

  If Foglio2.Range("A1") = "" Then

    Exit Sub

  End If

  ' Soluzione brutale ma efficace

  ' che spazzola le righe delle colonne A e B

  For Each Riga In Foglio2.Columns("A:B").Rows

    If Riga.Cells(1, 1) = "" Then Exit For

    Range(Riga.Cells(1, 1)) = "=" & Riga.Cells(1, 2)

  Next

  Foglio1.Shapes("Pulsante 2").Visible = False

  Foglio1.Shapes("Pulsante 1").Visible = True

End Sub

Dopo l’esauriente spiegazione della prima Sub sono tentato di affidare il codice della seconda all’esegesi autogestita. Comunque sottolineo che per ogni Riga della coppia A:B di Foglio2 il codice
"=" & Riga.Cells(1, 2

aggiunge il segno uguale alla formula, infilandola poi in
Range(Riga.Cells(1, 1))

Su quest’ultima istruzione faccio solo notare che l’attivazione preliminare del Foglio1 (Foglio1.Activate) fa sì che Range sottintenda il Foglio1. E con questo mi fermo qui.

Un possibile sviluppo: estensione del metodo a più fogli

Chi non si accontentasse di segretare solo le formule del primo foglio di lavoro potrebbe estendere il metodo a quelli successivi. Il procedimento, cui i più avveduti avranno già pensato, consisterebbe nell’assegnare mano a mano le coppia di colonne C:D a secondo foglio, E:F al terzo e così via.

Va da sé che ora il foglio extra non può essere Foglio2. Il resto? È silenzio, se un bel tacer, a volte, si può scrivere.

Print | posted on venerdì 5 dicembre 2014 16:13 |

Feedback

No comments posted yet.

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 8 and 1 and type the answer here:

Powered by:
Powered By Subtext Powered By ASP.NET