Questo tutorial discuterà come velocizzare le macro VBA e altre best practice VBA.
Impostazioni per velocizzare il codice VBA
Di seguito troverai diversi suggerimenti per velocizzare il tuo codice VBA. I suggerimenti sono organizzati in modo approssimativo per importanza.
Il modo più semplice per migliorare la velocità del tuo codice VBA è disabilitare ScreenUpdating e disabilitare Calcoli automatici. Queste impostazioni dovrebbero essere disabilitate in tutte le procedure di grandi dimensioni.
Disattiva aggiornamento schermo
Per impostazione predefinita, Excel visualizzerà le modifiche alle cartelle di lavoro in tempo reale durante l'esecuzione del codice VBA. Ciò causa un enorme rallentamento della velocità di elaborazione poiché la maggior parte di Excel interpreta e visualizza le modifiche per ogni riga di codice.
Per disattivare l'aggiornamento dello schermo:
| 1 | Application.ScreenUpdating = False |
Alla fine della tua macro, dovresti riattivare l'aggiornamento dello schermo:
| 1 | Application.ScreenUpdating = True |
Mentre il codice è in esecuzione, potrebbe essere necessario "aggiornare" lo schermo. Non esiste un comando "aggiorna". Invece, dovrai riattivare l'aggiornamento dello schermo e disabilitarlo di nuovo.
Imposta i calcoli su Manuale
Ogni volta che viene modificato il valore di una cella, Excel deve seguire l'"albero di calcolo" per ricalcolare tutte le celle dipendenti. Inoltre, ogni volta che viene modificata una formula, Excel dovrà aggiornare l'"albero di calcolo" oltre a ricalcolare tutte le celle dipendenti. A seconda delle dimensioni della cartella di lavoro, questi ricalcoli possono causare un'esecuzione irragionevolmente lenta delle macro.
Per impostare Calcoli su Manuale:
| 1 | Applicazione.Calcolo = xlManuale |
Per ricalcolare manualmente l'intera cartella di lavoro:
| 1 | Calcolare |
Nota che puoi anche calcolare solo un foglio, un intervallo o una singola cella, se necessario per migliorare la velocità.
Per ripristinare i calcoli automatici (al termine della procedura):
| 1 | Applicazione.Calcolo = xlAutomatico |
Importante! Questa è un'impostazione di Excel. Se non reimposti i calcoli in automatico, la cartella di lavoro non verrà ricalcolata finché non glielo dirai.
Vedrai i maggiori miglioramenti dalle impostazioni di cui sopra, ma ci sono molte altre impostazioni che possono fare la differenza:
Disattiva eventi
Gli eventi sono "inneschi" che causano speciali procedure dell'evento correre. Gli esempi includono: quando una qualsiasi cella di un foglio di lavoro cambia, quando viene attivato un foglio di lavoro, quando viene aperta una cartella di lavoro, prima che venga salvata una cartella di lavoro, ecc.
La disabilitazione degli eventi può causare lievi miglioramenti della velocità quando vengono eseguite macro, ma il miglioramento della velocità può essere molto maggiore se la cartella di lavoro utilizza gli eventi. E in alcuni casi è necessario disabilitare gli eventi per evitare di creare loop infiniti.
Per disabilitare gli eventi:
| 1 | Application.EnableEvents = False |
Per riattivare gli eventi:
| 1 | Application.EnableEvents = True |
Disabilita interruzioni di pagina
La disabilitazione di PageBreaks può essere d'aiuto in determinate situazioni:
- In precedenza hai impostato una proprietà PageSetup per il foglio di lavoro pertinente e la tua procedura VBA modifica le proprietà di molte righe o colonne
- O La tua procedura VBA forza Excel a calcolare le interruzioni di pagina (visualizzando l'anteprima di stampa o modificando qualsiasi proprietà di PageSetup).
Per disabilitare i PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = False |
Per riattivare PageBreaks:
| 1 | ActiveSheet.DisplayPageBreaks = True |
Best practice per migliorare la velocità VBA
Evita di attivare e selezionare
Quando registri una Macro, vedrai molti metodi Attiva e Seleziona:
| 12345678 | Sub Slow_Example()Fogli("Foglio2").SelezionaIntervallo("D9").SelezionaActiveCell.FormulaR1C1 = "esempio"Intervallo("D12").SelezionaActiveCell.FormulaR1C1 = "demo"Intervallo("D13").SelezionaFine sottotitolo |
L'attivazione e la selezione degli oggetti di solito non sono necessarie, aggiungono confusione al codice e richiedono molto tempo. Dovresti evitare questi metodi quando possibile.
Esempio migliorato:
| 1234 | Sub Fast_Example()Fogli("Foglio2").Intervallo("D9").FormulaR1C1 = "esempio"Fogli("Foglio2").Intervallo("D12").FormulaR1C1 = "demo"Fine sottotitolo |
Evita di copiare e incollare
La copia richiede una memoria significativa. Sfortunatamente, non puoi dire a VBA di cancellare la memoria interna. Invece Excel cancellerà la sua memoria interna a intervalli (apparentemente) specifici. Quindi, se esegui molte operazioni di copia e incolla, corri il rischio di occupare troppa memoria, il che può rallentare drasticamente il tuo codice o persino mandare in crash Excel.
Invece di copiare e incollare, considera l'impostazione delle proprietà del valore delle celle.
| 123456789 | Sub CopyPaste()'Più lentamenteIntervallo("a1:a1000"). Copia Intervallo("b1:b1000")'Più veloceIntervallo("b1:b1000").Valore = Intervallo("a1:a1000").ValoreFine sottotitolo |
Usa i cicli For Each anziché i cicli For
Quando si esegue il ciclo attraverso gli oggetti, il ciclo For Each è più veloce del ciclo For. Esempio:
Questo per il ciclo:
| 123456 | Sottociclo1()dim i come RangePer i = 1 a 100Celle(i, 1).Valore = 1Avanti ioFine sottotitolo |
| 123456 | Sottociclo2()Cella debole come intervalloPer ogni cella nell'intervallo ("a1: a100")cella.Valore = 1Cella successivaFine sottotitolo |
Dichiara variabili/Usa opzione esplicita
VBA non richiede la dichiarazione delle variabili, a meno che non si aggiunga Option Explicit all'inizio del modulo:| 1 | Opzione esplicita |
| 1234 | SottoOpzioneExplicit()var1 = 10MsgBox varlFine sottotitolo |
Usa con - Termina con istruzioni
Se fai riferimento agli stessi oggetti più volte (ad es. Intervalli, Fogli di lavoro, Cartelle di lavoro), considera l'utilizzo dell'istruzione With. È più veloce da elaborare, può rendere il tuo codice più facile da leggere e semplifica il tuo codice.Con esempio di dichiarazione:| 12345678 | Sub Faster_Example()Con fogli("Foglio2").Range("D9").FormulaR1C1 = "esempio".Range("D12").FormulaR1C1 = "demo".Range("D9").Font.Bold = True.Range("D12").Font.Bold = TrueTermina conFine sottotitolo |
| 123456 | Sub Slow_Example()Fogli("Foglio2").Intervallo("D9").FormulaR1C1 = "esempio"Fogli("Foglio2").Intervallo("D12").FormulaR1C1 = "demo"Fogli("Foglio2").Intervallo("D9").Font.Bold = TrueFogli("Foglio2").Intervallo("D12").Font.Bold = TrueFine sottotitolo |
Suggerimenti avanzati sulle migliori pratiche
Proteggi solo interfaccia utente
È buona norma proteggere i fogli di lavoro dalla modifica di celle non protette per evitare che l'utente finale (o tu!) Danneggi accidentalmente la cartella di lavoro. Tuttavia, ciò proteggerà anche i fogli di lavoro dal consentire a VBA di apportare modifiche. Quindi è necessario rimuovere la protezione e ri-proteggere i fogli di lavoro, il che richiede molto tempo se eseguito su molti fogli.
| 12345 | Sub UnProtectSheet()Fogli(“foglio1”).Rimuovi protezione ”password”"Modifica foglio1"Fogli(“foglio1”).Proteggi ”password”Fine sottotitolo |
Invece, puoi proteggere i fogli impostando UserInterfaceOnly:=True. Ciò consente a VBA di apportare modifiche ai fogli, proteggendoli comunque dall'utente.
| 1 | Fogli("foglio1").Proteggi password:="password", UserInterFaceOnly:=True |
Importante! UserInterFaceOnly si reimposta su False ogni volta che si apre la cartella di lavoro. Quindi, per utilizzare questa fantastica funzionalità, dovrai utilizzare gli eventi Workbook_Open o Auto_Open per impostare l'impostazione ogni volta che viene aperta la cartella di lavoro.
Inserisci questo codice nel modulo Thisworkbook:
| 123456 | Cartella di lavoro secondaria privata_Apri()Dim ws come foglio di lavoroPer ogni ws nei fogli di lavorows.Protect Password:="password", UserInterFaceOnly:=TrueAvanti wFine sottotitolo |
o questo codice in qualsiasi modulo regolare:
| 123456 | Sottotitoli privati Auto_Apri()Dim ws come foglio di lavoroPer ogni ws nei fogli di lavorows.Protect Password:="password", UserInterFaceOnly:=TrueAvanti wFine sottotitolo |
Usa gli array per modificare grandi intervalli
Può richiedere molto tempo manipolare grandi intervalli di celle (es. 100.000+). Invece di scorrere gli intervalli di celle, manipolando ogni cella, puoi caricare le celle in un array, elaborare ogni elemento nell'array e quindi restituire l'array nelle loro celle originali. Il caricamento delle celle negli array per la manipolazione può essere molto più veloce.
| 1234567891011121314151617181920212223242526272829303132 | Sub LoopRange()Cella debole come intervalloDim tStart As DoubletStart = TimerPer ogni cella nell'intervallo ("A1: A100000")cella.Valore = cella.Valore * 100Cella successivaDebug.Print (Timer - tStart) & "secondi"Fine sottotitoloSub LoopArray()Dim arr come varianteOggetto scuro come varianteDim tStart As DoubletStart = Timerarr = Intervallo ("A1: A100000"). ValorePer ogni articolo in ararticolo = articolo * 100Articolo successivoIntervallo ("A1: A100000"). Valore = arrDebug.Print (Timer - tStart) & "secondi"Fine sottotitolo |
