[vba] Stranezze velocità vba

[ramset64]

L'altro ieri utilizzavo una macro che di fatto richiama dei valori di altri file. La macro era molto lenta.
Ieri ho cambiato tutti i file che vengono richiamati, nel senso che ho modificato qualcosa al loro interno e stranamente ieri la stessa identica macro era diventata più veloce di almeno 10 volte.
Oggi, dopo aver di nuovo modificato i file di origine, è tornata di nuovo lentissima.

La macro non è mai stata cambiata, i file di origine sono diventati ogni volta più pesanti della precedente e quindi non dipende dal loro peso. Da cosa diavolo può dipendere?

Ripeto la macro non è stata cambiata, il calcolo automatico è disabilitato.

Qualcuno sa spiegarmi il perchè succede questo? Risparmierei molto tempo se riuscissi a farla tornare a funzionare come ieri.

Grazie, ciao

[Flash30005]

Quale versione di office hai?

[ramset64]

OFFICE 2007
Ciao

[Flash30005]

Office 2007 a differenza delle versioni precedenti comprime i file e carica gli oggetti quando occorrono (gli stessi file che in office2007 occupano 10 in office, versioni precedenti, occupano 30) e quindi la versione 2007 impiega maggior tempo sia nel caricamento che nell'esecuzione dei programmi/macro contenuti nei file stessi.
Ora però se usando la stessa versione di office su file diversi (cloni o nuovi) hai tempi diversi, devi verificare se tra i vecchi file e i nuovi ci sono differenze di struttura, dati e soprattutto se nel caricamento dei fogli vecchi hai un controllo di righe e/o colonne con macro del tipo:
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
oppure Private Sub Workbook_Open() etc
che nei nuovi non hai, perché questi controlli sono una "zavorra" per ogni avvio ed esecuzione delle macro.

Ciao

[ramset64]

Guarda i file di origine sono cambiati semplicemente nel fatto che hanno qualche colonna in più o in meno in un determinato foglio. Nessun codice vba è stato modificato.

[Flash30005]

A me è capitato che per una formattazione condizionale in una cella, quindi con macro disabilitate, calcoli disabilitati etc etc, una cartella si apriva in 4 secondi e la stessa con la formattazione condizionale impiegasse 10 minuti.

Ciao

[ramset64]

Be, allora è impossibile andare a cercare cosa può rallentare cosi tanto l'esecuzione della macro.

[Flash30005]

Il problema era dovuto sicuramente dal fatto che avendo una
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
'controlla se un certo range cambia selezione
end sub
e la cella sotto controllo avrebbe cambiato colore e selezione se all'apertura succedeva ciò che la formattazione condizionale prevedeva.
Penso si innescasse una specie di loop che prima che excel lo risolvesse passavano i minuti
Non ho fatto il test direttamente sui fogli che mi davano quella lentezza ma tolta quella formattazione su nuovi fogli quel problema non si è più ripresentato pur avendo le stesse macro e il famoso
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
che mi occorreva per altre esigenze

ciao

[ramset64]

Capisco, io però non ho di queste istruzioni, ho solo una Private Sub Workbook_Open() che mi disabilità il calcolo automatico, ma c'era anche nei file precedenti e non dava nessun problema.

[ramset64]

Senti, ma esiste un modo per riportare i fogli di excel all'origine ovvero togliere eventuali formattazioni in un colpo solo.? Magari cosi risolvo.

[Flash30005]

Beh, la maniera per riportare quei fogli come erano all'origine senza formattazioni c'è:
selezioni tutto il foglio lo copi apri una nuova cartella e incolli-speciale solo i valori
ma fare questo in "un colpo solo" ci sarebbe:
con una macro
ma sarebbe un controsenso

Ciao

[ramset64]

Si hai ragione, la cosa migliore e riprendere uno dei file che funzionava e rifare il tutto. Grazie, ciao

[ramset64]

Credo di aver capito qual è il problema... l'unica differenza tra i file che si leggevano più velocemente e gli altri sta nelle colonne che contengono calcoli...... evidentemente il file generale che richiama gli altri, attiva il calcolo sui file che va a leggere, sono quasi certo che il problema è questo.

Se ci fosse una istruzione da mettere nel file generale che dicesse di andare a leggere i file da richiamare senza attivare alcun calcolo, la velocità della macro migliorerebbe in maniera esponenziale.

Ma esiste?

[Flash30005]

Si, se ti decidessi a usare qualche macro in più e qualche formula in meno...

La o le formule vanno bene quando in un foglio non superano una certa quantità (il numero non lo saprei) ma sono sicuro che un file dove è la macro a mettere i dati (tanti) è molto più veloce e snello (anche come grandezza fisica in kb) che non uno dove i dati (tutti) vengono ricavati da formule,
Se io scrivo 5 (risultato della macro) in una casella ho occupato un bite (carattere) se, invece, scrivo =A1+A2 ho sei caratteri occupati per avere lo stesso risultato di 5.
Indubbiamente la formula è immediata ma in generale appesantisce il file di excel.

E' una mia considerazione che può non trovare tutti d'accordo ma io, i miei fogli, evito di "appesantirli".

Ciao

[ramset64]

Hai perfettamente ragione, ma in questo caso quasi tutto viene fatto dalle macro, tranne un foglio dove i dati cambiano di continuo ed è assolutamente necessario utilizzare i calcoli.
Comunque confermo che il problema è quello, se vi fosse il modo di dire al codice leggi quel file senza attivare calcoli sarebbe una manna... io credo proprio che ci sia, ma trovarlo sarà dura!

[Anthony47]

La tua macro contiene salvataggio dei file? Se SI, nelle versioni prima di 2007 c' era una opzione di "Ricalcola prima di salvare", nel tuo caso sarebbe da togliere.

Ciao.

[ramset64]

La tua macro contiene salvataggio dei file? Se SI, nelle versioni prima di 2007 c' era una opzione di "Ricalcola prima di salvare", nel tuo caso sarebbe da togliere.

Ciao.

La macro che richiama i file in questione non contiene salvataggio file. La lentezza è direttamente proporzionale alla quntità di colonne che contengono formule nel file richiamati. Più colonne con formule ci sono più diventa lento. Eppure questi file hanno l'opzione che in apertura disattivano il calcolo automatico. Non so se esiste altro modo per velocizzare l'apertura di questi file dalla macro in argomento. Purtroppo quelle colonne con le formule mi sono necessarie.
Ciao

[Anthony47]

Hummm...
Per prova, dopo aver fatto 2 copie di backup dei file originali:
-se hai celle unite, separale (in genere servono solo per usi estetici)
-se hai formattazioni condizionali, eliminale (F5=vai a, vai a Speciale, spunta Formati condizionali, Ok; Elimina quindi tutti i livelli di formattazione sulle celle selezionate)
-se hai "collegamenti", modificali affinche' all' avvio "non si visualizzi l' avviso e non si aggiorni automaticamente", poi salva il file e prova con la macro.
-se hai colonne o righe nascoste, scoprile
-se i file sono su un server di rete, prova con gli stessi sul disco locale
-se la macro di cui parli contiene un Calculate, usa Worksheets("NomeFoglio").Calculate per limitare il ricalcolo a un solo foglio del file attivo, avendo attivato non il file che stai importando.

Per evitare equivoci, quando dici che la tua macro "di fatto richiama dei valori di altri file" intendi che apre i file e legge dei suoi dati o che li richiami inserendo dei collegamenti nel file correntemente aperto?

Sono tentativi su situazioni che so possono rallentare clamorosamente le operazioni.

Ciao.

[ramset64]

No, Anthony ho verificato e ti garantisco che sono le colonne che contengono formule che modificano la velicità della macro. Provo a spiegare meglio il tutto:
1) Io ho dei file chiamiamoli file1, file2, file3 per comodità che contengono 9 fogli di dati, i primi 8 foglio vengono tutte scritte da macro e quindi non contengono alcuna formula, l'ultimo (il 9) contiene delle colonne con delle formule.
2) Qui ho un file che chiamiamo per comodità Generale, su questo file ho una macro che richiama dei valori (solo il dato) relativi ai vari file1, file2, file3 etc.

Quello che ho notato è che maggiori sono le colonne presenti nel foglio9 dei file1,file2, file3 etc, minore è la velocità di esecuzione della macro del file generale (che ripeto va a prendere dei dati dai vari file numerati). Infatti se elimino le formule del foglio9 dei vari file numerati l'esecuzione della macro è velocissima.

Adesso tocca capire se la velocità diminusce perchè ogni volta che un dato di un file numerato viene richiamato il file viene aperto con più lentezza per via delle formule contenute o se la macro quando apre un file poi aggiora il calcolo. Una volta capito (ma forse tu lo sai), si può pensare ad una soluzione se esiste.
Ciao

[Anthony47]

Se con l' evento Workbook_open metti Calculate in manuale il file non viene ricalcolato; lo puoi verificare facendo delle modifiche (con Calculate=Manual), salvando il file con l' opzione "Non ricalcolare prima di salvare": riaprendo il file dovresti trovare il risultato basato sulle celle modificate ancora non aggiornato.
E poi non mi e' chiaro se il tempo di apertura e' proporzionato al numero di celle con formule (basterebbe provare con 100 righe e poi con 1000), o se e' un cambiamento on/off.
Comunque tutte le situazioni citate nel post precedente creano rallentamenti, vedi se trovi il tempo di fare anche quelle prove.

Ciao