Ciclo FOR che si rallenta ad ogni cella che popola

[FulvioPartecipante Fulvio]

Ciao a tutti,

ho un piccolo problemino ho un progettino che sto modificando da tanti anni e adesso volevo aggiungere una modifica che però lo ha rallentato in modo esagerato parliamo di ore per modificare 14 colonne per 17 righe.

vi descrivo lo scenario e poi vi allego il file.

ho un foglio PIPPO con dentro una tabella con N righe le righe di questo foglio sono raggruppate per ogni riga di tipo GRUPPO che ha un ID univoco (colonna B) e le sotto righe hanno a loro volta un ID univoco che inizia con il valore della riga padre

Es. Gruppo 1 ID 01 e sotto riga ID 01.01

per ogni riga ho anche altri tre campi che sono Mandatory (colonna F) e Priority (colonna G) e Test rusult (colonna H) che possono avere dei valori ben precisi impostati con il convalida dati e servono per fare dei filtri nel foglio TR.

Poi ho il foglio TR dove ho una tabella che riporta dei conteggi per ogni singolo gruppo, ed ogni singolo stato presente in test rusult (colonna H del foglio pippo)

le celle contengono delle formule relativamente complesse

premendo il pulsante "FILTRA" in alto nella pagina il sistema riscrive tutte le formule delle celle da D11 a D27 (dove D27 è dinamico)

ecco il mio problema è che il sistema si rallenta sempre di più ad ogni cella che popola con la formula ricalcolata.

io non so se è un problema di formule che sono troppo pesanti per excel o se ho sbagliato io qualche cosa nel codice.

PS. per il momento nel ciclo FOR ho limitato il calcolo alle sole righe 11 e 12 per non impallare il PC

nel codice troverete un sacco di righe inutili e commentate, ignoratele pure, sono frutto di modifiche che non cancello mai fino a quando non sono sicuro del risultato.

il codice lo trovate tutto in modulo1, ci sono due funzioni, quella interessata è solo la prima, l'altra mi serve solo per nascondere o mostrare determinate righe del report.

Allegati:

You must be logged in to view attached files.

[Luca73Partecipante Luca73 Karma: 58 pts]

Ciao A prescindere da alcune semplificazioni

ad esempio vedo che riporti in due posizioni la stessa condizioni che potresti accorpare

If mandatori <> "TOT" Then
    StringaMandatori_OLD = ";INDIRETTO(B" & i & "&""!F:F"");$D$4"
End If
If priorita <> "Tutte" Then
    StringaPriorita_OLD = ";INDIRETTO(B" & i & "&""!G:G"");$D$6"
End If
If mandatori <> "TOT" Then
    StringaMandatori = "*--(INDIRETTO(B"
    StringaMandatori_fine = "&""!F:F"")=$D$4)"
End If
If priorita <> "Tutte" Then
    StringaPriorita = "*--(INDIRETTO(B"
    StringaPriorita_fine = "&""!G:G"")=$D$6)"
End If

Che diventa

If mandatori <> "TOT" Then
    StringaMandatori_OLD = ";INDIRETTO(B" & i & "&""!F:F"");$D$4"
    StringaMandatori = "*--(INDIRETTO(B"
    StringaMandatori_fine = "&""!F:F"")=$D$4)"
End If
If priorita <> "Tutte" Then
    StringaPriorita_OLD = ";INDIRETTO(B" & i & "&""!G:G"");$D$6"
    StringaPriorita = "*--(INDIRETTO(B"
    StringaPriorita_fine = "&""!G:G"")=$D$6)"
End If

e magari alleggerire il numero di righe 

stringafiltro = stringafiltro1 & """PRONTO"")"
stringafiltro = stringafiltro & StringaGruppo
stringafiltro = stringafiltro & StringaMandatori
stringafiltro = stringafiltro & i
stringafiltro = stringafiltro & StringaMandatori_fine
stringafiltro = stringafiltro & StringaPriorita
stringafiltro = stringafiltro & i
stringafiltro = stringafiltro & StringaPriorita_fine
stringafiltro = stringafiltro & ")"

come

stringafiltro = stringafiltro1 & """PRONTO"")" & StringaGruppo & StringaMandatori & i & StringaMandatori_fine & StringaPriorita & i & StringaPriorita_fine & ")"

IO proverei a mettere all'inizio del tuo programma

le seguenti righe:

Application.ScreenUpdating = False
Application.Calculation = xlCalculationmanual
Application.EnableEvents = True

e alla fine prima del End le seguenti

Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
Application.EnableEvents = True
End Sub

In modo che ad ogni cambio di cella Excel non esegua tutti i calcoli e non visulaizzi le modifiche durante la macro

 

Fammi sapere

Ciao

Luca

[FulvioPartecipante Fulvio]

Ciao Luca,

per la duplicazione, concordo con te, ma la parte OLD sparirà era li perché stavo cambiando paino piano le varie parti quindi è un problema relativo.

per l'alleggerire il numero di righe anche quello era in previsione, se vedi alcune erano già state accorpate in una sola riga.

mentre per le parti da aggiungere quello mi sembra interessante e lo provo subito, vi faccio sapere.

Grazie mille

[FulvioPartecipante Fulvio]

Ho ripulito il codice, da tutte quelle righe "morte" e dai commenti superflui, ho accorpato le righe come ci dicevamo sopra.

poi ho inserito la modifica all'inizio della funzione e quella alla fine e devo dire che le cose sono nettamente migliorate, siamo passati da ORE di elaborazione a poco più di un minuto.

quindi la tua modifica ha funzionato alla grande.

adesso non mi rimane altro da fare che cercare di snellire ancora il codice, ho già visto un paio di cose che possono essere migliorate.

Grazie ancora

[Luca73Partecipante Luca73 Karma: 58 pts]

Ciao Quando inserisci una formula il foglio excel ricalcola tutto.

Nel tuo caso il programma continua ad inserire formule e quindi continua a ricalcolare.

Facendolo ricalcolare in manuale Application.Calculation = xlCalculationmanual ad ogni inserimento di formula non avviene il ricalcolo e questo favorisce di molto la velocità.

inoltre con l'istruzione Application.ScreenUpdating = False ad ogni comando che modifica la visualizzazione non viene modificata ma tutto viene accorpato alla fine.

Ciao

Luca

[FulvioPartecipante Fulvio]

Ciao, lascio solo una nota per i posteri, la soluzione sopra funziona perfettamente, ma mi sono accorto che tutte quelle formule così complesse nel foglio rallentano il file, non è un problema della funzione usata, ma è proprio legato al fatto che nel foglio alla fine della funzione saranno presenti troppe formule. 

ho ovviato al problema rinunciando all'aggiornamento in real time e facendo fare al codice vba tutte le formule ed inserendo nel foglio solo i risultati finali e non le formule.

[Luca73Partecipante Luca73 Karma: 58 pts]

Ciao Basterebbe anche inibire il ricalcolo manuale.

IO in passato per tabelle molto grandi piene di calcoli con fomule che si potevano ottenere per copia incolla della prima riga avevo optato per scrivere due macro. la prima copiava le formule in tutto il folgio, la seconda copiava dalla seconda riga in fondo e poi invollava speciali valori

Ciao

Luca

[Autore]

Articoli