Gianni Giaccaglini

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

Dati numeri e formule Excel segretati con crittografia differenziata

Dati, numeri e formule Excel segretati

Gianni Giaccaglini

Antefatto. Questo post fa seguito al precedente su questo stesso blog in cui ho esposto un sistema per occultare le (sole) formule di un foglio di lavoro, indicando di ciascuna i riferimenti e la formula stessa in due colonne di un secondo foglio nascosto. Meglio ancora, aggiungo ora, assegnandogli la proprietà Visible pari a xlSheetVeryHiddeen. Ho poi penato per estendere il metodo a più fogli, ma alla fine mi sono detto: perché non ricorrere alla crittografia? In tal modo si può estendere la protezione ad altri tipi di dato e l’applicabilità a qualsiasi foglio di lavoro o a tutti quanti è più semplice.

Ed ecco il frutto di questo secondo approccio.

Il mio modello si può scaricare da:
www.giannigiaccaglini.it/download/DatiFormuleSegretate.zip

Ad ogni buon conto per dare un'idea a quanti vorranno farne a meno sulla scorta delle abbondanti delucidazioni che tra poco fornirò, ecco come si presenta il mio modellino, una previsione di vendite trimestrali:

                increm 1,20%    
               
    Gennaio Febbraio Marzo

TotTrim

   
  Bulloni 160 161,92 163,86 485,78  

Cripta

  Viti 134 135,61 137,24 406,84    
  Dadi 192 194,3 196,64 582,94    
  Rondelle 119 120,43 121,87 361,3    
  Coppiglie 194 196,33 198,68 589,01    
               
  TOTALI 799 808,59 818,29 2.425,88    
               

In alto (cella B1) si ha una scritta Vendite primo trimestre 2014 frutto di questa formulaccia:
="Vendite primo trimestre  "&ANNO(OGGI()) 

Sulla destra la dicitura Cripta raffigura rozzamente un pulsante classico (non ActiveX) incollato nel foglio di lavoro, che inoltre comprende un grafico incorporato, sopra omesso. Attivando Cripta, connesso all'omonima macro si ottiene il guazzabuglio seguente:

        gŒƒ‹ 445,60%    
               
    eƒŒŒ‡ dƒ€€‡ k˜ r’r‡‹ƒ‘’ƒ    
  `“ŠŠŒ‡     504,00  e©¡®Ÿª…gmdfp   e©¡®Ÿª…gmdfp€   pgp€gp     
  t‡’‡     578,00  e©¡®Ÿª…gmdfq   e©¡®Ÿª…gmdfq€   qgq€gq     
  b‚‡     536,00  e©¡®Ÿª…gmdfr   e©¡®Ÿª…gmdfr€   rgr€gr    Decripta
  pŒ‚ƒŠŠƒ     553,00  e©¡®Ÿª…gmdfs   e©¡®Ÿª…gmdfs€   sgs€gs     
  aŽŽ‡…Š‡ƒ     538,00  e©¡®Ÿª…gmdft   e©¡®Ÿª…gmdft€   tgt€gt     
               
  rmr_jg  etvpd‰‘   et€vp€d‰‘   etvpd‰‘   lmglm€glm     
               

Faccio solo notare che a) i numeri, modificati, sono rimasti tali con l'Increm percentuale divenuto abnorme (ma senza possibilità di indovinare cos'era prima); b) le formule, stravolte, hanno un contrassegno di due simboli speciali (v. più avanti); c) il pulsante Cripta è sparito e ne è comparso uno Decripta, collegato all'omonica macro restauratrice.

Crittografare, che passione!

La crittografia è un mezzo per rendere segreti documenti di vario genere. Nel caso Excel, sono partito dalla constatazione che in un foglio di lavoro vi sono tre distinte tipologie di oggetti documentari:

- testi non numerici
- valori costanti numerici
- formule

Dopo di che ho pensato di applicare una strategia diversificata per “mascherare” i tre casi. In particolare, già diversi anni fa, avevo escogitato un metodo che crittografasse con chiavi distinte testi e numeri, in modo – si badi bene – che le cifre venissero tradotte in altre cifre. Con tale metamorfosi distinta qualora l’intruso umano o digitale riuscisse a scoprire la chiave applicata ai testi utilizzandola per recuperare gli originali otterrebbe però solo strane combinazioni di caratteri alfanumerici. La qual cosa farebbe ammattire i meno scaltri, mentre anche i più smaliziati potrebbero ipotizzare l’esistenza di una chiave ad hoc per i numeri tuttavia senza poterli scoprire, perché i numeri, salvo casi specialissimi, non hanno valenza semantica.

Citazione dotta. Non tutti sanno che Champollion poté decifrare i geroglifici egizi solo perché conosceva l’antica lingua copta, affine all’egiziano. Così i più sofisticati algoritmi frequenziali non riuscirebbero a recuperare un testo scritto in una lingua ignota (come quella dei messaggeri indiani nella seconda guerra mondiale).

Per fare un esempio spicciolo, si abbiano i due dati “ciao” e 123. Una banale translitterazione di quattro posti per i testi e di tre per i numeri produce “gmes” e 456. Con la chiave 4 si riotterrebbe “ciao, ma applicandola al numero darebbe “-./”. Infatti Asc(“1”) – 4 è uguale a 45 e Chr(45) restituisce il trattino. Analogamente Chr(Asc(“2”) - 4) = “.” E Chr(Asc(“3”) – 4) = “/”.

Il modello utilizzato per prova

Per sperimentare il procedimento anticipato ho adottato sul primo foglio di lavoro una classica situazione di vendite trimestrali di certi articoli, con i valori dei mesi di febbraio e marzo che crescono secondo un valore Incr (percentuale) applicando una formula del tipo =B1*(1 + Incr). Altre sono pedestri addizioni tipo =B1 + C1 + D1 né mancano più comuni funzioni =SOMMA nella riga dei totali. Ho inoltre previsto quest’altra formulaccia, nella prima riga: ="Vendite primo trimestre "&ANNO(OGGI()). Sullo stesso foglio è poi incorporato un grafico.

Il lettore comunque può crearsi una situazione analoga di suo gusto purché caratterizzata da una buona varietà di dati costanti e formule.

Repetita iuvant. Le macro fondamentali, che, abbiate pazienza, mostrerò fra poco, nel mio modellino sperimentale hanno nome Cripta e Decripta e sono assegnate a due pulsanti “classici” (non ActiveX) di nome Pulsante 1 e Pulsante 2 ed etichettati guardacaso Cripta e Decripta, incollati sul foglio. Cliccando sul primo si ottiene uno stravolgimento in cui l’azione criptante ha modificato i valori alfanumerici (etichette, nel gergo spreadsheet) con strani simboli tipo `“ŠŠŒ‡ o pŒ‚ƒŠŠƒ e analoghe metamorfosi hanno subito le formule ma tutte precedute dai caratteri di numero Ascii 14 e 15. In gergo Visual Basic si tratta dei codici Chr(14) e Chr(15) non riprodotti in Word (sono comandi di cambio pagina e altro) ma che nelle celle Excel vengono visualizzati (il primo è una coppia di note musicali). Hanno sostituito il segno = e servono a marcare la presenza di formule in origine, permettendo così la decrittazione.

Anche i numeri sono stati alterati ma rimanendo tali, pertanto Il grafico continua a rappresentare solo tali valori mentre quelli legati a formule scompaiono. Al posto del pulsante Cripta compare un pulsante Decripta, in precedenza occultato.

Quando si clicca su Decripta si riceve domanda idiota e fasulla, cui invece occorre rispondere digitando una certa password. Solo così ricompare fedelmente la situazione corretta, altrimenti non succede nulla (ho infatti evitato un messaggio tipo “Password scorretta” o che, peggio, sbeffeggi l’utente).

Importante! Va subito sottolineato che è fondamentale l’utilizzo di una password (unica, ma solo a beneficio degli smemorati) non solo per lanciare Decripta ma per occultare il codice VBA. Infatti altrimenti i malintenzionati che avessero accesso al modello crittografato potrebbero scoprire facilmente gli altarini!

Tutto il codice VBA

Riportato qui di seguito le funzioni e le routine, intercalate da essenziali commenti del caso.

Sul Modulo1

Function strCripDecr(Str As String, Correz As Integer, Cript As Boolean) As String

   Dim Car As String

   Correz = IIf(Cript, Correz, -Correz)

   For i = 1 To Len(Str)

      Car = Mid(Str, i, 1)

      Mid(Str, i, 1) = Chr(Asc(Car) + Correz)

    Next

    strCripDecr = Str

End Function

Sub ProvastrCriptDecript()

  MsgBox strCripDecr("", 3, True) ' Cripta

  MsgBox strCripDecr("ambarabà", 2, True)

  Dim strCript As String

  strCript = strCripDecr("ciccicoccò", 2, True)

  strCript = strCripDecr(strCript, 2, False)

  MsgBox strCripDecr(strCripDecr("Ciao mondo!", 2, True), 2, False)

End Sub

 

La strcripDecr è una funzione ambidestra che, applicata a una certa Str, restituisce con Cript = True una stringa crittata e ripristina l’originale con Cript =False. Il procedimento si limita ad aggiungere o togliere, nei due casi, una certa Correz all’i-esimo carattere della stringa trattata ed è facilmente verificabile con la susseguente routine di prova (in particolare se ne noti l’ultima istruzione che segnala con MsgBox un immutato “Ciao mondo” dopo averlo crittato e decrittato.

Nota. A nessuno sfuggirà la rozzezza di questo algoritmo, dovuto alla pigrizia e alla natura sostanzialmente didattica di questo articolo.

Ma passiamo alla routine crittografica, fortemente caratterizzata dal distinto trattamento di costanti alfabetiche, numeriche e formule.

Sub Cripta()

  Dim Cella As Range

  Dim Form As String, Lung As Integer

  Application.Calculation = xlCalculationManual ' IMP! Senza, formule criptate => ERRORE

  For Each Cella In ActiveSheet.UsedRange.Cells

    If Cella.HasFormula Then

      Form = Cella.Formula

      Lung = Len(Form)

      Form = Right(Form, Lung - 1)

      Form = strInversa(Form) ' Inverti la stringa Form

      Cella.Value = Chr(14) & Chr(15) & strCripDecr(Form, 10, True)

    Else

    If WorksheetFunction.IsNumber(Cella) Then

      Cella.Value = NumCrip(Cella.Value, 4) ' Diversifica i casi numerici

    Else

      Cella = strCripDecr(Cella.Value, 5, True)

    End If

    End If

  Next

  Application.Calculation = xlCalculationAutomatic

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

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

End Sub

 

Tale routine viene scatenata pigiando il pulsante omonimo incollato sul foglio di lavoro. Si tratta di un oggetto Shape battezzato come “Pulsante 1” all’atto della creazione, così come “Pulsante 2” è quello etichettato Decripta. Le due ultime istruzioni palesemente provvedono al termine della routine ad occultare il primo pulsante facendo riapparire il secondo. Le analoghe e opposte istruzioni in fondo alla Sub Decripta sono scontate.

Il nucleo di Cripta è il ciclo For Each Cella In ActiveSheet.UsedRange.Cells ... Next che spazzola tutte e sole le celle dell’intervallo utilizzato anziché le miriadi, vuote, del foglio corrente. La proprietà UsedRange non a tutti nota è importantissima in casi del genere e va tenuta presente a futura memoria.

Il mestiere svolto nei tre casi distinti, ossia Cella.HasFormula, che caratterizza celle contenenti formule, celle numeriche individuate dalla funzione Excel WorksheetFunction.IsNumber e le altre celle, alfanumeriche è abbastanza chiaro nel secondo e terzo caso, ove le distinzione sta nel fatto che alle etichette viene applicata la funzione ambidestra strCripDecr, mentre i numeri vengono assoggettati a una più particolare
NumCrip(Cella.Value, 4) per maggiormente confondere le acque a chi volesse recuperare l’originale. La funzione NumCrip e la sua opposta NumDecr saranno mostrate più avanti.

Il procedimento applicato alle formule è più elaborato. Va premesso che Excel VBA in una cella contenente una formula distingue il valore evidenziato diciamo così in superficie (Value) dalla formula “in cantina”, sempre così per-dicendo. I gioco consiste nel prelevare la Formula nella variabile Form, togliere a questa l’iniziale segno =, invertirla poi con la funzione strInversa che vedremo (e che, comunque anticipiamo, trasforma “Roma” in “amoR”) e, infine, riversare nel Value la Form assoggettata alla funzione ambidestra già un paio di volte vista, con tanto di anteposizione dei due predetti caratteri Chr(14) e Chr(15). Per comodità ripeto questa cruciale istruzione:

Cella.Value = Chr(14) & Chr(15) & strCripDecr(Form, 10, True)

 

A questo punto la funzione Decripta non dovrebbe avere sorprese, pertanto la lascio all’esegesi autonoma, incluse le prime istruzioni relative alla password.

Sub Decripta()

   Dim Cella As Range

   Dim Lung As Integer, Form As String

   Dim Pswrd As String, Dom As String, Titolo As String

   Pswrd = "33trentini"

   Dom = "Quanto fa 2 + 2 ?": Titolo = "Sembra facile..."

   If InputBox(Dom, Titolo) <> Pswrd Then Exit Sub ' Uscita brutale senza altre storie

   For Each Cella In ActiveSheet.UsedRange.Cells

     If Left(Cella, 2) = Chr(14) & Chr(15) Then

       Lung = Len(Cella)

       Form = strInversa(strCripDecr(Right(Cella, Lung - 2), 10, False))

       Cella.Formula = "=" & Form

     Else

     If WorksheetFunction.IsNumber(Cella) Then

       Cella.Value = NumDecr(Cella.Value, 4)

     Else

       Cella = strCripDecr(Cella.Value, 5, False)

     End If

     End If

   Next

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

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

End Sub

Sub DecriptaFormula()

  'PROVATA SOLO SU UNA FORMULA ATTIVA CRIPTATA!

  Dim Lung As Integer

  Lung = Len(ActiveCell)

  ActiveCell = strCripDecr(ActiveCell, False)

  ActiveCell.Formula = "=" & Right(ActiveCell, Lung - 2)

End Sub

 

Sul Modulo2

Su tale modulo sono presenti funzioni evocate da Cripta e Decripta, il cui ruolo avevo solo anticipato. Le riporto senza commento alcuno, considerando facili da capire e testare.

Function strInversa(Str As String) As String

  Dim i As Integer, Car As String, StrInv As String

  For i = Len(Str) To 1 Step -1

    StrInv = StrInv & Mid(Str, i, 1)

  Next

  strInversa = StrInv

End Function

Sub ProvastrInversa()

  Dim miaStr As String

  miaStr = "Pio"

  MsgBox strInversa(miaStr)

End Sub

 

Function NumCrip(Num As String, Corr As Integer)

  If Num = "" Then Exit Function

  For i = 1 To Len(Num)

    Car = Mid(Num, i, 1)

    If Asc(Car) >= 48 And Asc(Car) <= 57 Then

      Car = 0 + Car + Corr

      If Car > 9 Then Car = Car - 10

    End If

    Mid(Num, i, 1) = Car

  Next

  NumCrip = 0 + Num

End Function

Sub ProvaNumCrip()

  ActiveCell.Offset(0, 1) = NumCrip(ActiveCell, 4)

End Sub

 

Function NumDecr(Num As String, Corr As Integer)

  If Num = "" Then Exit Function

  For i = 1 To Len(Num)

    Car = Mid(Num, i, 1)

    If Asc(Car) >= 48 And Asc(Car) <= 57 Then

      Car = 0 + Car - Corr

      If Car < 0 Then Car = Car + 10

    End If

    Mid(Num, i, 1) = Car

  Next

  NumDecr = 0 + Num

End Function

Sub ProvaNumDecr()

  ActiveCell.Offset(0, 1) = NumDecr(ActiveCell, 4)

End Sub

 

Sono di facile accesso al comprendonio dei più, ergo non le commenterò salvo far notare che stavolta ho rinunciato a ricorrere a una funzione ambidestra. Questa, chi la gradisse, potrebbe estrinsecarsi nella funzione seguente:

Function NumCripDecr(Num As String, Corr As Integer, Crip As Boolean)

 If Num = "" Then Exit Function

  For i = 1 To Len(Num)

    Car = Mid(Num, i, 1)

    If Asc(Car) >= 48 And Asc(Car) <= 57 Then

      If Crip Then

        Car = 0 + Car + Corr

        If Car > 9 Then Car = Car - 10

      Else

        Car = 0 + Car - Corr

        If Car < 0 Then Car = Car + 10

      End If

    End If

    Mid(Num, i, 1) = Car

  Next

  NumCripDecr = 0 + Num

End Function

 

Sub ProvaNumCripDecr()

  'MsgBox NumCripDecr(Range("A2"), 4, True)

  ' MsgBox NumCripDecr(Range("B2"), 4, False)

  A = NumCripDecr(Range("A2"), 4, True)

  B = NumCripDecr("" & A, 4, False) ' Senza "" & A si ha errore

End Sub

Conclusioni

Il procedimento descritto non è certo privo di pecche, cui tuttavia si potrebbe in parte porre rimedio mediante algoritmi meno elementari. In particolare quello di troppo semplice translitterazione applicato alle etichette, mentre rimane valido quando si applica ai numeri. Quanto al trattamento delle formule va precisato che degli strani caratteri anteposti di codice ASCII 14 e 15 contrassegnano in modo univoco l’origine delle stringhe criptate in quanto il parametro Correz sposta in avanti i caratteri, rendendo precisa la decrittazione. Tali segnali potrebbero insospettire gli estranei desiderosi di scoprire l’arcano, ma è un compito davvero arduo a causa anche dell’inversione ad opera della funzione strInversa. Chi proprio ci tiene potrebbe posporre i due caratteri strani.

E gli altri fogli di lavoro?

Fin qui abbiamo descritto l’operatività del codice VBA sul primo foglio del modello. Ma lo si può utilizzare con gli altri, in una cartella di lavoro a fogli multipli?. La risposta è positiva: basta selezionare un qualsiasi altro foglio e lanciare Cripta e Decripta per vederne il regolare funzionamento. Com’è da attendersi, se il foglio è vuoto non succede nulla, inoltre le istruzioni che celano e fanno riapparire i due pulsanti continuano ad agire, senza altro disturbo.

A questo punto sorge spontaneo il desiderio di implementare una versione di Cripta e Decripta in grado di operare su tutti i fogli della cartella, mantenendone l’associazione ai due fatidici pulsanti omonimi. Con riferimento, per semplicità alla prima delle due routine, occorre anzitutto modificarla così:

Sub Cripta(Foglio As Worksheet)

  Dim Cella As Range

  Dim Form As String, Lung As Integer

  Application.Calculation = xlCalculationManual ' IMP! IMP! Senza le formule criptate => ERRORE

  For Each Cella In Foglio.UsedRange.Cells

. . .  eccetera . . .

End Sub

 

' Routine di prova. Semplicissima:

Sub ProvaCripta

  Cripta Foglio3 ' Oppure Cripta Sheets(2)

End Sub

 

La routine che Cripta tutti i fogli, da associare al primo pulsante, è subito vista:

Sub CriptaTuttiFogli

  Dim mioFoglio As Worksheet

  For Each mioFoglio In ThisWorkbook.Sheets

    On Erros GoTo 1 ' Salta eventuali fogli-Grafico

    Cripta mioFoglio

1: ' Prosegui

  Next

End Sub

 

Quasi del tutto analoga è la contrapposta Sub DecriptaTuttiFogli, partendo da una nuova Decripta (Foglio As Worksheet) avendo solo cura di spostare il codice che reclama la password dalla seconda alla prima per evitare continue richieste persino con fogli nullatenenti.

Un’ultima trovata: export-import di moduli VBA

Ecco da ultimo (per davvero!) un suggerimento del genere cui non si pensa. Consiste nei passaggi seguenti.

1. Nell’Editor VBA selezionare e salvare tutti i Modulo1, Modulo2 e simili, nonché UserForm varie.

2. Eliminare tali moduli e salvare la cartella Excel, magari in formato xlsx di Office 13.

3. Alla riapertura di tale file .xlsx (o vecchio .xls), sempre nell’Editor VBA importare i moduli esportati al passo 1.

In tal modo soltanto noi (e i nostri partner “fidati”) possiamo sfruttare le funzionalità critto e decritto-grafiche senza necessità di applicare una password al Progetto VBA.

N.B. Il punto è che un file MiaCartella.xlsx una volta aperto accoglie nell’Editor nuove macro e le esegue senza fiatare. Tuttavia rifiuta di salvare tale archivio con le nuove macro, invitando semmai a farlo come MiaCartella.xlsm.

 

Se si insiste col formato .xlsx i moduli estemporanei vengono perduti, idem l’eventuale protezione con password del Progetto VBA. Comunque viene tranquillamente eseguita l’importazione di moduli preregistrati, che sono subito operanti.

Funzioni personalizzate. Si tratta dell’applicazione forse più interessante. Una funzione personalizzata agisce soltanto in presenza di uno specifico modulo VBA. In sua assenza la cella che la contiene dà errore ma se lo si importa la funzione risuscita (e se in origine puntava a un determinato intervallo si presta ad altri impieghi modificando i riferimenti).

Il sistema descritto vi sembra un po’ macchinoso? Sì, ma è grazioso.

 

Print | posted on lunedì 5 gennaio 2015 11:10 |

Feedback

No comments posted yet.

Post Comment

Title  
Name  
Email
Url
Comment   
Please add 5 and 6 and type the answer here:

Powered by:
Powered By Subtext Powered By ASP.NET