Calcoli tradotti al volo in valori (ricetta originale!)

QUESTA "RICETTA" NASCE DALLA RICHIESTA FATTAMI DA UNO SVILUPPATORE DI MODELLI EXCEL CON VBA, SPECIALIZZATO IN PROBLEMI DI STUDI DENTISTICI:

Paolo Bortolini

Sito: www.studiobortolini.com

Blog: http://managementodontoiatria.wordpress.com/

A onor del vero, dopo avergliela escogitata e proposta, mi ha de visu mostrato che nel frattempo anch'egli era suppergiù pervenuto ad un'idea stostanzialmente consimile... (succede, non s'inventa nulla a questo Excel-mondo).

Calcoli tradotti al volo in valori (ricetta originale!)

Si abbia una tabella del tipo seguente - con dati fasulli, colonne e righe omesse –sulla cui parte destra (da colonna H in avanti) si vogliono riportare formule anche complesse copiate in basso su tutti i record.

Il problema che subito si presenta deriva dal fatto che le migliaia di righe in ballo sono troppe, con due conseguenze poco simpatiche: le formule appesantiscono il file e, peggio ancora, rallentano paurosamente l’esecuzione.

Prima soluzione (inadeguata)

Partendo dal normale intervallo della bozza affidatami dal committente, ovvero non da una tabella “canonica” (ListObject in VBA), ho dapprima pensato a una macro che, partendo da una prima riga contenente essa soltanto le formulacce da colonna H in poi, le ricopiasse in basso per tutte le righe sottostanti da 2 a 12500 (o giù di lì). Peggio che andar di notte, ovviamente: si registravano tempi biblici di ricalcolo.

Poi mi sono detto che salvare i risultati in termini di VALORI poteva comunque essere utile, in vista di ulteriori elaborazioni con altri programmi come Access o applicazioni sviluppate in Visual Studio. Ed ecco allora le macro VBA con le quali ho tentato di concretizzare l’idea. Si esamini anzitutto la seguente, seguita dalla sua brava Sub di prova.

Sub IncollaSpeciale(Scarto As Integer)

  Dim RigaFrm As Range, RigaDest As Range

  Set RigaFrm = Range("RigaFormule")

  Set RigaDest = RigaFrm.Offset(Scarto)

  RigaFrm.Copy RigaDest

  RigaDest.Copy

  RigaDest.PasteSpecial Paste:=xlPasteValues

  Application.CutCopyMode = False

End Sub

Sub ProvaIncollaSpeciale()

   IncollaSpeciale 3

End Sub

La IncollaSpeciale applica appunto all’intervallo preliminarmente denominato “RigaFormule” (nella figura precedente potrebbe avere coordinate quali H1:M1 o simili) contenenti le fatidiche formule a riga 2 da H in poi) il comando omonimo Copia seguito da Incolla Speciale con opzione Valori. Tali operazioni si traducono in VBA in queste successive istruzioni: a) proprietà Copy applicato a RigaForm che registra il Range(“RigaFormule”) assegnandole come destinazione una RigaDest shiftata secondo l’argomento Scarto; b) a tale RigaDest è di nuovo applicato Copy; c) quindi, sempre RigaDest, è assoggettato a PasteSpecial con opzione Paste:=xlPasteValue che, come qui si desidera,incolla i valori e non le formule; d) chiude un (inevitabile) Application.CutCopyMode = False che elimina la dannata casella ruotante che altrimenti continuerebbe a girare attorno all’origine (o alla destinazione? Verificate voi stessi...).

Ed ecco la macro centrale che utilizza la copia in basso dei valori, lasciando in vita la riga 2 di formule, secondo un certo numero di record NumRec come argomento.

Sub CopiaDati(NumeroRec As Integer)

  Dim Inizio As Date

  Inizio = Now()

  SvuotaRigheMenoUna

  Application.ScreenUpdating = False

  Dim i As Integer

  For i = 1 To NumeroRec

    IncollaSpeciale i

  Next

  MsgBox "Inizio: " & Inizio & vbLf & "Fine: " & Now()

End Sub

A questo punto non voglio tediare gli esperti cui mi rivolgo con commenti che ora ritengo superflui. Costoro potranno divertirsi a sfruttare CopiaDati passandole un NumRec pari alle righe meno una del nostro elenco, nonché a creare una macro SvuotaRigheMenoUna che, appunto, ripulisce tutte le celle che stanno sotto la riga 2 a partire da colonna H (o quel che l’è).

La pensata precedente può essere interessante per situazioni intermedie, diciamo con non più di 200-300 righe, ma purtroppo delude in gran parte le speranze. Infatti se garantisce un minor appesantimento di formule l’esecuzione del codice presenta riduzioni di tempo piuttosto scarse, come indicano - provare per credere – le istruzioni iniziale Inizio = Now e finale MsgBox ecc. che segnalano circa 10 minuti già con un migliaio di righe.

Altro tentativo fallito

Successivamente ho sperimentato la variante consistente nel tradurre il fatidico intervallo in una Tabella , mediante l’apposito comando (in Excel 2007 posto nella scheda omonima) che automaticamente affibbia il nome default Tabella1. La cosa è in ogni caso interessante per due motivi:

1)      offre gratuitamente filtri automatici sui vari campi e funzionalità di filtro avanzato nonché l’automatico arruolamento come nuovo record di tale Tabella1 ogni volta che si aggiunge qualcosa sotto l’ultima riga precedente;

2)      riporta, sempre automaticamente, le formule inserite (da H in poi, sempre per stare al nostro caso) in tutti i record vecchi o nuovi.

Ma purtroppo anche in questo caso i tempi di ricalcolo si rivelano altresì solo leggermente più contenuti: confidavo in un qualche sistema di ottimizzazione offerto da Excel con le tabelle, alias ListObject. Aspettativa ingenua (miracoli non se ne fanno, neanche in casa Microsoft), almeno con le molte e complesse formule della nostra riga da H in poi e i tempi gli appesantimenti del file restano inaccettabili.

Eureka! Ovvero un uovo di Colombo

Per giunta il mio incontentabile committente pretendeva che venisse esportato su un diverso foglio l’intervallo di righe relative a un certo criterio (principalmente quelle compresi fra due date fissate dall’utente finale). Di qui il secondo uovo di Colombo, più complicato perché richiede codice VBA, ma comunque brillante.

L’idea appena detta si concretizza con un record ausiliario posto su colonne lontane da quelle del database. Si tratta di una cosa del genere, che ripropone a parte un tipico record della nostra Tabella1:

Si fa presente che le prime 7 celle costituiscono campi di input, mentre quelle accanto, da colonna AF in avanti contengono le dannate formule. Orbene, prima di esaminare la prima basilare macro si sappia che agli intervalli delle une e delle altre sono stati affibbiati i nomi QuestoRecord e, rispettivamente, QuesteFormule. Eloquenti, direi.

Ed ecco la macro in parola, seguita da una SvuotaRighe a sua volta richiamata dalla prima con evidente finalità:

Sub AggiornaValori()

  If ActiveCell.Column <> 8 Then

    MsgBox "Cella attiva DEVE essere in colonna H!", vbCritical

    Exit Sub

  End If

  Dim SetteCelle As Range

  With ActiveCell

    Set SetteCelle = Range(.Offset(0, -1), .Offset(0, -7))

  End With

  SetteCelle.Copy Range("QuestoRecord")

  Range("QuesteFormule").Copy

  ActiveCell.PasteSpecial Paste:=xlPasteValues

  Application.CutCopyMode = False

End Sub

Sub SvuotaRighe()

  Dim PrimaCella As Range, UltimaCella As Range

  With Range("RigaFormule")

    Set PrimaCella = .Cells(1, 1)

    Set UltimaCella = .Cells(.Count)(1, 1)

  End With

  If IsEmpty(PrimaCella) Then Exit Sub

  If IsEmpty(PrimaCella.Offset(1, 0)) Then

    Range(PrimaCella, UltimaCella).Clear

  Else

    Range(PrimaCella.End(xlDown), UltimaCella).Clear

  End If

End Sub

Commenti. Tacitiani e limitati alla Sub AggiornaValori (la seconda è affidata all’esegesi autogestita). La variabile SetteCelle serve a registrare l’intervallo delle prime celle dei campi di input (il valore 7 deriva dal caso trattato e va allineato a situazioni differenti). La macro s’incarica poi di copiare il Range SetteCelle nel Range QuestoRecord , con conseguente calcolo delle specifiche formule nella zona QuesteFormule, che a loro volta vengono incollate col PasteSpecial di soli valori nella cella attiva.

Qualcuno obietterà che l’utente potrebbe dimenticare di lanciare AggiornaValori. Eccolo accontentato con una macro che li aggiorna tutti, circostanza nella quale il distrattone potrebbe andarsi a prendere un caffè, una tantum si spera!

Sub AggiornaTuttiValori()

  Dim Inizio As Date

  Inizio = Now()

  Load UserForm1

  UserForm1.Show

  Dim CampoCosti As Range, i As Integer

  Dim SetteCelle As Range

  Set CampoCosti = _

  Foglio1.ListObjects("Tabella1").ListColumns("COSTI GENERALI").Range

    On Error Resume Next ' Patch dovuta a causa dell'orribile caso #DIV/0!

    For i = 2 To CampoCosti.Cells.Count

    If CampoCosti.Cells(i) = "" Then

      With CampoCosti.Cells(i)

        Set SetteCelle = Range(.Offset(0, -1), .Offset(0, -7))

      End With

      SetteCelle.Copy Range("QuestoRecord")

      Range("QuesteFormule").Copy

      CampoCosti.Cells(i).PasteSpecial Paste:=xlPasteValues

      Application.CutCopyMode = False

    End If

  Next

  UserForm1.Hide

  Unload UserForm1

  Range("A3").Select

  MsgBox "Inizio: " & Inizio & vbLf & "Fine: " & Now()

End Sub

Anche qui mi astengo da commenti, in modo pilatesco quanto pigro. Preciso anzi che ho preparato una versione che, per limitare il predetto coffee break, esclude i record già sistemati. La lascio per esercizio. Piuttosto si esamini quest’altra macro, relativa all’evento DoubleClick del Worksheet ove risiede la tabella:

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

    If Target.Column = 8 Then AggiornaValori

    Cancel = True

End Sub

Come a tutti è chiaro, essa agisce ogni volta che si dà una doppia cliccata su una cella di colonna H (quella del campo contenente la prima formulaccia).

L’evento risolutore. Alla fine, riflettendo, ci si rende conto che le macro precedenti meno AggiornaValori e la sua ancella SvuotaRighe sono pleonastiche o quasi. Questo perché l’evento che sistema tutto o quasi (*) è l’evento Change sempre del foglio della Tabella1:

Private Sub Worksheet_Change(ByVal Target As Range)

  If Intersect(Target, Range("Tabella1")) Is Nothing _

    Or Target.Column = 8 Then Exit Sub

  Range("H" & Target.Row).Select

  AggiornaValori

End Sub

Qui si fanno solo due osservazioni: a) una tabella canonica ha la virtù che se si digita qualcosa in una riga subito sotto questa viene automaticamente inserita come nuovo record; b) può essere importante che i valori da colonna H in poi vengano aggiornati (come avverrebbe con formule) qualora l’utente modifichi in seguito qualche dato di input. A buon intenditor...

posted on giovedì 18 marzo 2010 11.38