Indentare le formule Excel con una macro

Simone P

Utente junior
24 Giugno 2020
60
18
Milano
Office 365
9
Ciao a tutti,
in più occasioni per analizzare meglio le formule più complicate, sia che si trattasse di formule trovate in giro sia che fossero opera mia, ho optato per un "indentazione" fatta a mano all'interno della cella della formula.
Per andare a capo nella cella usavo la combinazione ALT+Invio e per far "rientrare" una riga aggiungevo a mano un numero adeguato di spazi.
Fare un esempio semplice, la formula =SE(E([@Condizione1];[@Condizione2]);[@Risultato1];[@Risultato2]) l'avrei riscritta in questo modo
=SE(
E(
[@Condizione1];
[@Condizione2]
);
[@Risultato1];
[@Risultato2]
)

In questo modo le formule diventano più leggibili sia che vi serva studiare qualcosa scritto da altri, sia che vogliate controllare che quello che state scrivendo voi sia coerente con quello che volete ottenere e/o che tutte le funzioni abbiano i giusti parametri.

La cosa scomoda è che fare questa operazione a mano per formule lunghe è noioso ed è facile dimenticarsi un "a capo" dopo una parentesi o dopo un punto e virgola complicando la lettura anziché semplificarla.

Oggi mi sono finalmente deciso a scrivere un po' di codice VBA per fare questa operazione in automatico, e già che c'ero dell'altro codice per riportare tutto alla situazione di partenza.

Nel file allegato trovate due macro:
Inserisci_Indentazione_Formule per ottenere che una formula venga indentata in modo automatico
Rimuovi_Indentazione_Formule per riportare tutto alla situazione ordinaria
Personalmente le ho aggiunte entrambe alla cartella macro personali mettendo anche due pulsanti in una barra multifunzione personalizzata.
Funzionano sia selezionando una singola cella sia selezionando un blocco di celle.

I criteri che ho usato per l'indentazione sono i seguenti
  • Dopo ciascuna parentesi aperta: a capo con spostamento di 8 caratteri a destra
  • Subito prima di ciascuna parentesi chiusa: a capo con spostamento di 8 caratteri a sinistra
  • Dopo ciascun punto e virgola (o ciascuna virgola se con Excel in inglese): a capo restando con lo stesso allineamento
  • Se all'interno di virgolette " .... " : nessuna azione
  • Se all'interno di parentesi graffe { ... } : nessuna azione
Punti di attenzione:
  • È necessario stare attenti e selezionare un numero limitato di celle perché su intervalli grandi i tempi diventano molto lunghi.
  • All'interno delle tabella fare attenzione ad applicare a una sola riga per volta perché altrimenti la macro viene applicata una volta per ciascuna riga selezionata e siccome le tabelle riportano automaticamente le formule verso il bassi ci si ritrova con una formula scritta in modo curioso.
  • Da verificare cosa succede per formule davvero molto lunghe che si avvicinino al limite massimo li lunghezza all'interno di una cella.
  • Da verificare il comportamento con formule matriciali, dalle mie prove (con Excel 365) dopo aver applicato una delle due macro a una cella con formula matriciale è necessario rientrare nella cella e confermare con CTRL+Shift+Invio.
  • Ho l'impressione che dietro le quinte la macro lavori sulla versione in inglese delle formule, finora la cosa non mi ha dato problemi ma è il motivo per il quale ho dovuto aggiungere la condizione di a capo sia dopo il punto e virgola che dopo la virgola semplice.
Spero che sia utile ad altri, fatemi sapere se avete suggerimenti per migliorare in qualche modo le due macro.
 

Allegati

Simone P

Utente junior
24 Giugno 2020
60
18
Milano
Office 365
9
Una nota:
l'esempio che ho riportato doveva venire formattato in modo diverso, con l'indentazione appunto, mi sono accorto della visualizzazione errata troppo tardi per modificare il messaggio originale.
Ci riprovo.
Applicando la mia macro la formula =SE(E([@Condizione1];[@Condizione2]);[@Risultato1];[@Risultato2]) viene riscritta in questo modo:
=SE(
E(
[@Condizione1];
[@Condizione2]
);
[@Risultato1];
[@Risultato2]
)

Nel frattempo ho anche preparato una versione .xlam con creazione automatica di una barra multifunzione "Indentazione formule" contenente due pulsanti "Applica indentazione" e "Rimuovi indentazione". La allego per chi fosse interessato a installarla direttamente come componente aggiuntivo.
 

Allegati

  • Like
Reactions: zio_tom and Rubik72

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
bellissimo lavoro Simone P @Simone P ... complimenti
io aggiungerei le possibilità di applicare all'intero foglio ed all'intero file
un cosa simile sapevo farlo, ma sono arruginito
spero ch qualcuno meno arruginito di me faccia questa implementazione
penso sarebbe molto utile e velocizzerebbe
 

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
ho provato con questo codice..... aggiunto nel file di Simone P @Simone P
nel file ho aggiunto 2 cartelle con alcune formule per foglio
ma non finisce mai... dato che dovrebbe scandire circa un milione di righe x 16000 colonne
qualche soluzione più intelligente
ricordo che si può individuare l'ultima riga occupata e l'ultima colonna
però senza "legarsi" ad una colonna o riga di riferimento, dato che dovrebbe essere il più generale possibile
e quindi al Cell.Select
si potrebbe sostituire ad esempio il Range("A1:D20").Select

Visual Basic:
Sub Indenta_Scansione_Fogli()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Cells.Select
        Call Inserisci_Indentazione_Formule
        Range("A1").Select
    Next ws
End Sub

Sub Rimuovi_Indentazioni_Scansione_Fogli()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        Cells.Select
        Call Rimuovi_Indentazione_Formule
        Range("A1").Select
    Next ws
End Sub
 

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
x Simone P @Simone P
ho notato che usando il file del post #1
se seleziono l'area interessata e lancio la rimuovi ... tutto OK
se riseleziono e lancio inserisci
nelle caselle al di fuori della tabella funziona
mentre dentro la tabella non mette tutta la formula indentata
ma solo la prima riga, e non il rimanente
=CASUALE.TRA(
capita solo a me (ho la 2019)???
 

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
correggo quanto detto sopra
il problema è che invece degli spazi mette dei caporiga, questo dentro la tabella
su caselle normali è OK
 

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
il problema sta dentro alle tabelle (almeno nel mio 2019)
se seleziono solo la prima casella della tabella
funziona su TUTTA la colonna (quindi fa una sola scansione)
se seleziono le prime 3 caselle della colonna
esegue 3 volte e quindi mette 3 ritorni a capo
trovato il problema.... rimane come risolverlo!!!!! non ne ho la minima idea!!!
a meno non ci sia un flag che disattiva l'aggiornamento (il copiare la modifica su tutta la colonna)
un maledetto automatismo!!!

oppure vista la filosofia delle tabelle
quando si fa la scansione, identificare se siamo in una tabella e modificare solamente la prima riga
ignorando la selezione fatta
e non scansionare le seguenti
 

Simone P

Utente junior
24 Giugno 2020
60
18
Milano
Office 365
9
zio_tom @zio_tom sì, ho notato anche io il comportamento se si selezionano più celle della stessa colonna in una tabella. Al momento non saprei come risolverlo perché dipende proprio dal fatto che, come hai intuito, le tabelle riportano automaticamente la formula su tutte le altre righe.
Tra l'altro a breve invierò una nuova versione perché Bruno @Bruno mi ha fatto notare un'anomalia nella gestione delle formule matriciali dinamiche con Excel 365 versione insider quindi ho dovuto modificare leggermente per gestire quel caso.

Ho la tentazione di cercare di capire come limitare l'applicazione della macro a una sola cella per volta, disabilitando se si selezionano più celle. Da un lato mi dispiacerebbe perché trovo comodo poter applicare a più celle di una riga in un colpo solo, dall'altro al momento la possibilità di applicare a un intervallo mi sembra portare più svantaggi che vantaggi: per esempio se per sbaglio si prova ad applicare avendo selezionato un intero foglio la macro prova a girare su milioni di celle bloccando tutto, poi l'anomalia che hai notato sulle tabelle.
 

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
sarebbe un peccato usarla in una sola cella!!!!
come accennavo sopra
se fosse possibile, in sequenza:
1) identificare se sono presenti tabelle
__ a) se si, memorizzare aree delle tabelle
__ b) applicare la procedura solo su prima riga delle tabelle
2) identificare la zona compilata (per ridurre il lavoro)
3) scasionare tale area, escludendo le zone pertinenti alle tabelle

al momento non come leggere se ci sono tabelle, il nome, l'area corrispondente...

PS: x Simone P @Simone P limitare l'area di scansione alla sola area compilata è abbastanza semplice
qui sul forum avevo trovato questa procedura (mi sembra di Zer0Kelvin @Zer0Kelvin ) anche se non riesco ad applicarla....
Visual Basic:
Public Function LastCell(Foglio As String) As Range
Dim Riga As Long, Colonna As Long, uRiga As Long, uColonna As Long
    With Sheets(Foglio).UsedRange
        For Colonna = .Columns.Count To 1 Step -1
            If WorksheetFunction.CountBlank(.Columns(Colonna)) <> .Columns(Colonna).Cells.Count Then
                uColonna = .Columns(Colonna).Column
                Exit For
            End If
        Next Colonna
        For Riga = .Rows.Count To 1 Step -1
            If WorksheetFunction.CountBlank(.Rows(Riga)) <> .Rows(Riga).Cells.Count Then
                uRiga = .Rows(Riga).Row
                Exit For
            End If
        Next Riga
    End With
    If uRiga > 0 And uColonna > 0 Then
       Set LastCell = Sheets(Foglio).Cells(uRiga, uColonna)
    Else
       Set LastCell = Sheets(Foglio).Cells(1, 1)
    End If
End Function
 
Ultima modifica:
  • Like
Reactions: Simone P

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
ho risolto in modo un po' casereccio il fatto di saltare le caselle già processate dentro tabelle
controllo se nella formula c'è la tabulazione pari a Basic_Tab_Size spazi
If InStr(Orig_Formula, String(Basic_Tab_Size, " ")) > 1 Then GoTo salta
e mi sembra funzionare!!!!
 

Simone P

Utente junior
24 Giugno 2020
60
18
Milano
Office 365
9
Ho lavorato un altro po' sulle macro e qui vi allego la nuova versione (sia in formato xlsm per chi vuol far girare a mano le macro che in formato xlam per chi preferisse un componente aggiuntivo con tanto di barra multifunzione e pulsanti pronti).

Novità di questa versione:
  • Risolta l'anomalia che poteva presentarsi agli utenti Excel 365 insider con formule matriciali dinamiche (grazie a Bruno @Bruno per la segnalazione e a Marius44 @Marius44 per il check di funzionamento su Excel 2010)
  • Aggiunte le due macro Adatta_Barra_Formula e Riduci_Barra_Formula che automatizzano l'espansione della barra della formula per mostrare più righe possibili in una volta sola e in seguito per riportare tutto alle dimensioni standard (grazie a Bruno @Bruno per l'idea e per la prima versione di questa parte della macro)
  • Riscritta la parte di individuazione delle celle selezionate in modo da ridurre le operazioni solo a quelle che contengono davvero una formula (grazie zio_tom @zio_tom per lo stimolo a non limitarmi a mettere un blocco per applicare la macro solo a una cella per volta) adesso se per errore si seleziona un intero foglio la macro non proverà a girare su milioni di celle (a meno che non abbiate davvero formule in milioni di celle) consiglio comunque di usare su un numero limitato per volta.
  • Cambiato il comportamento nel caso si tenti di applicare l'indentazione più volte alla stessa cella, adesso prima di applicare l'indentazione la macro rimuove quella eventualmente già presente per garantire che ne venga applicata sempre e solo una alla volta
  • Il punto sopra aggira anche i problemi con l'applicazione a più righe di una tabella. Nel caso delle tabelle conviene comunque applicare a una sola riga per volta perché si occupa già Excel a replicare sulle righe successive.
  • Aggiunti due pulsanti per le macro relative alla barra della formula nella versione xlam per chi fosse interessato a usarla
  • Riorganizzata leggermente la struttura del codice e i nomi delle variabili per permettere ai più curiosi di seguire meglio i passaggi del codice VBA.
Per l'applicazione automatica a più fogli devo studiarci un po' di più con calma, seguirò gli sviluppi del post di zio_tom @zio_tom in merito per capire in che direzione muovermi.

In allegato trovate la nuova versione dei due file.
 

Allegati

Simone P

Utente junior
24 Giugno 2020
60
18
Milano
Office 365
9
In allegato la nuova versione dei due file (spero che per un po' resti quella definitiva).

Novità di questa versione:
  • Se si lancia la macro dopo aver selezionato più fogli (anche non adiacenti) e quindi aver selezionato un intervallo di celle la macro viene applicata su tutti i fogli selezionati. Nella versioni precedenti veniva applicata solo al foglio visibile (grazie a zio_tom @zio_tom per il suggerimento della funzione)
  • Disabilitazione del calcolo automatico e di alcune opzioni di visualizzazione all'inizio della macro e ripristino delle stesse opzioni (solo se inizialmente era presenti) alla fine. In questo modo la macro gira più rapidamente.
Grazie a tutti per i suggerimenti SmileFace
 

Allegati

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
x Simone P @Simone P
ti siggerisco di inserire nel nome del file un numero progressivo della versione tipo
Indentazione_Formule_3.0.xlsm
spesso servono le versioni precedenti, e tornare indietro
così si ha tutta la cronologia, e mettere la descrizione di cosa fa la sub/function nell'intestazione e la descrizione delle variabili che vengono passate

a suo tempo ho fatto una mia app che indentava il codice VBA, nei vari file caricati
proverò ad implementare pure questa tua funzione
cioè non sul file corrente ma su altro file caricato
probabilmente xlam fa questo.... (sbagliando) lo carico ma non vedo niente
Non mi ricordo più come attivare e gestire xlam....

mi permetto di allegare il tuo file con esempio
 

Allegati

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
per non aprire un altro 3D..
volevo condividere e definire una variabile Public
ma ho provato, come alcuni mi hanno suggerito, di mettere in "Questa cartella di lavoro"
il seguente codice
Visual Basic:
Public Dimensione_Rientro_Base As Integer

'##########################################################################################
Private Sub Workbook_Open()
'##########################################################################################
    Dimensione_Rientro_Base = 5
End Sub
ma quando lancio la procedura che io ho aggiunto "Sub Indenta_Scansione_Fogli()"
la variabile
Dimensione_Rientro_Base
risulta vuota

ovviamente ho tolto la dichiarazione public che avevo nel modulo "suFogli"
nel file che ho allegato nel post #14

dove sbaglio???
 

zio_tom

Utente assiduo
21 Ottobre 2015
1.296
50
Veneto
2016 - Win10
25
ho provato a mantenere la dichiarazione Public nel modulo e togliendola da "Questa cartella di lavoro"
ma non cambia
 

Sostieni ForumExcel

Aiutaci a sostenere le spese e a mantenere online la community attraverso una libera donazione!