10 Modi per accelerare le tue macro - dummies

Man mano che le macro di Excel diventano sempre più robuste e complesse, è possibile che perdano prestazioni. Quando si parla di macro, la parola prestazioni è in genere sinonimo di velocità . La velocità è la velocità con cui le procedure VBA eseguono i compiti previsti. Di seguito sono riportati dieci metodi per mantenere le macro di Excel in esecuzione a un livello di prestazioni ottimale.

Calcoli del foglio di arresto

Lo sapevate che ogni volta che una cella che influenza qualsiasi formula nel vostro foglio di calcolo viene modificata o manipolata, Excel ricalcola l'intero foglio di lavoro? Nei fogli di lavoro che hanno una grande quantità di formule, questo comportamento può rallentare drasticamente i tuoi macronutrienti.

È possibile utilizzare l'applicazione. Proprietà di calcolo per indicare a Excel di passare alla modalità di calcolo manuale. Quando una cartella di lavoro è in modalità di calcolo manuale, la cartella di lavoro non ricalcola fino a quando non si attiva esplicitamente un calcolo premendo il tasto F9.

Posiziona Excel in modalità di calcolo manuale, esegui il tuo codice e poi torna alla modalità di calcolo automatico.

Applicazione Sub Macro1 (). Calcolo = xlCalculationManual 'Inserisci il tuo codice macro qui Applicazione. Calcolo = xlCalculationAutomatic End Sub

L'impostazione della modalità di calcolo su xlCalculationAutomatic attiverà automaticamente un ricalcolo del foglio di lavoro, quindi non è necessario premere il tasto F9 dopo aver eseguito la macro.

Disabilitazione dell'aggiornamento della schermata del foglio

Potresti notare che quando si eseguono i tuoi macro, lo schermo emette una certa quantità di sfarfallio. Questo sfarfallio è un tentativo di Excel di ridisegnare lo schermo per mostrare lo stato corrente del foglio di lavoro. Sfortunatamente, ogni volta che Excel ridisegna lo schermo, occupa risorse di memoria.

È possibile utilizzare l'applicazione. Proprietà ScreenUpdating per disabilitare gli aggiornamenti dello schermo fino al completamento della macro. La disattivazione dell'aggiornamento dello schermo consente di risparmiare tempo e risorse, consentendo alla macro di funzionare un po 'più velocemente. Al termine dell'esecuzione del codice macro, puoi riattivare l'aggiornamento dello schermo.

Applicazione Sub Macro1 (). Calcolo = Applicazione manuale xlCalculation. ScreenUpdating = False 'Inserisci il tuo codice macro qui Applicazione. Calcolo = xlCalcoloApplicazione automatica. ScreenUpdating = True End Sub

Dopo aver reimpostato la proprietà ScreenUpdating su True, Excel attiverà automaticamente un ridisegno dello schermo.

Disattivazione degli aggiornamenti della barra di stato

La barra di stato di Excel, visualizzata nella parte inferiore della finestra di Excel, visualizza normalmente l'avanzamento di determinate azioni in Excel.Se la tua macro funziona con molti dati, la barra di stato occuperà alcune risorse.

È importante notare che disattivare l'aggiornamento dello schermo è separato dallo spegnimento del display della barra di stato. La barra di stato continuerà ad essere aggiornata anche se si disabilita l'aggiornamento dello schermo. Puoi usare l'applicazione. Proprietà DisplayStatusBar per disabilitare temporaneamente tutti gli aggiornamenti della barra di stato, migliorando ulteriormente le prestazioni della macro:

Applicazione Sub Macro1 (). Calcolo = Applicazione manuale xlCalculation. ScreenUpdating = False Application. DisplayStatusBar = False 'Inserisci il tuo codice macro qui Applicazione. Calcolo = xlCalcoloApplicazione automatica. ScreenUpdating = True Application. DisplayStatusBar = True End Sub

Dire ad Excel di ignorare gli eventi

È possibile implementare macro come procedure di eventi, dicendo ad Excel di eseguire determinati codici quando un foglio di lavoro o una cartella di lavoro cambia.

A volte, le macro standard apportano modifiche che attivano una procedura di evento. Ad esempio, se si dispone di una macro standard che manipola diverse celle su Sheet1, ogni volta che viene modificata una cella su quel foglio, la macro deve sospendere mentre viene eseguito l'evento Worksheet_Change.

È possibile aggiungere un altro livello di aumento delle prestazioni utilizzando la proprietà EnableEvents per indicare a Excel di ignorare gli eventi mentre la macro viene eseguita.

Impostare la proprietà EnableEvents su False prima di eseguire la macro. Al termine dell'esecuzione del codice macro, è possibile impostare la proprietà EnableEvents su True.

Applicazione Sub Macro1 (). Calcolo = Applicazione manuale xlCalculation. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False 'Inserisci il tuo codice macro qui Applicazione. Calcolo = xlCalcoloApplicazione automatica. ScreenUpdating = True Application. DisplayStatusBar = Vera applicazione. EnableEvents = True End Sub

Nascondere le interruzioni di pagina

Ogni volta che la macro modifica il numero di righe, modifica il numero di colonne o modifica l'impostazione di pagina di un foglio di lavoro, Excel è costretto a impiegare del tempo per ricalcolare le interruzioni di pagina mostrate sul foglio.

È possibile evitare questo comportamento nascondendo semplicemente le interruzioni di pagina prima di avviare la macro.

Imposta la proprietà del foglio DisplayPageBreaks su False per nascondere le interruzioni di pagina. Se si desidera continuare a mostrare interruzioni di pagina dopo l'esecuzione della macro, impostare la proprietà del foglio DisplayPageBreak su True.

Applicazione Sub Macro1 (). Calcolo = Applicazione manuale xlCalculation. ScreenUpdating = False Application. DisplayStatusBar = False Application. EnableEvents = False Activesheet. DisplayPageBreaks = False 'Inserisci il tuo codice macro qui Applicazione. Calcolo = xlCalcoloApplicazione automatica. ScreenUpdating = True Application. DisplayStatusBar = Vera applicazione. EnableEvents = True Activesheet. DisplayPageBreaks = True End Sub

Sospensione degli aggiornamenti della tabella pivot

Se la macro manipola tabelle pivot contenenti origini dati di grandi dimensioni, si potrebbero verificare scarse prestazioni quando si eseguono operazioni come l'aggiunta dinamica o lo spostamento di campi pivot.

È possibile migliorare le prestazioni della macro sospendendo il ricalcolo della tabella pivot fino a quando non sono state apportate tutte le modifiche del campo pivot. Basta impostare la tabella pivot. Proprietà di ManualUpdate su True per rinviare il ricalcolo, eseguire il codice macro e quindi impostare la tabella pivot. La proprietà ManualUpdate torna su False per attivare il ricalcolo.

Sub Macro1 () ActiveSheet. Tabelle pivot ("PivotTable1"). ManualUpdate = True 'Inserisci il tuo codice macro qui ActiveSheet. Tabelle pivot ("PivotTable1"). ManualUpdate = False End Sub

Sterilizzo di copia e incolla

È importante ricordare che sebbene Macro Recorder risparmi tempo scrivendo codice VBA per te, non scrive sempre il codice più efficiente. Un primo esempio è come Macro Recorder cattura qualsiasi azione di copia e incolla eseguita durante la registrazione.

Puoi dare una leggera spinta alle tue macro tagliando l'intermediario e eseguendo una copia diretta da una cella a una cella di destinazione. Questo codice alternativo utilizza l'argomento Destination per ignorare gli appunti e copiare i contenuti della cella A1 direttamente nella cella B1.

Range ("A1"). Destinazione copia: = Intervallo ("B1")

Se è necessario copiare solo i valori (non la formattazione o le formule), è possibile migliorare ulteriormente le prestazioni evitando il metodo Copia tutti insieme. Basta impostare il valore della cella di destinazione sullo stesso valore trovato nella cella di origine. Questo metodo è circa 25 volte più veloce rispetto all'utilizzo del metodo Copia:

Intervallo ("B1"). Valore = Intervallo ("A1"). Valore

Se è necessario copiare solo le formule da una cella a un'altra (non i valori o la formattazione), è possibile impostare la formula della cella di destinazione sulla stessa formula contenuta nella cella di origine:

Intervallo ("B1"). Formula = Range ("A1"). Formula

Uso dell'istruzione With

Quando si registrano macro, si manipola spesso lo stesso oggetto più di una volta. È possibile risparmiare tempo e migliorare le prestazioni utilizzando l'istruzione With per eseguire più azioni su un dato oggetto in un'unica operazione.

L'istruzione With utilizzata nell'esempio seguente indica a Excel di applicare tutte le modifiche di formattazione contemporaneamente:

Con intervallo ("A1"). Font. Grassetto = Vero. Corsivo = True. Sottolineato = xlUnderlineStyleSingle End Con

Prendere l'abitudine di eseguire azioni di chunking in Con istruzioni non solo manterrà le macro in esecuzione più veloce, ma renderà anche più semplice la lettura del codice macro.

Evitare il metodo Select

Il Macro Recorder ama utilizzare il metodo Select per selezionare esplicitamente gli oggetti prima di eseguire azioni su di essi. Generalmente non è necessario selezionare gli oggetti prima di utilizzarli. In effetti, puoi migliorare notevolmente le prestazioni macro non usando il metodo Select.

Dopo aver registrato i tuoi macro, prendi l'abitudine di alterare il codice generato per rimuovere i metodi Seleziona. In questo caso, il codice ottimizzato sarà il seguente:

Fogli ("Foglio1"). Range ("A1"). FormulaR1C1 = "1000" Fogli ("Foglio2"). Range ("A1"). FormulaR1C1 = "1000" Fogli ("Foglio3"). Range ("A1"). FormulaR1C1 = "1000"

Si noti che non viene selezionato nulla.Il codice utilizza semplicemente la gerarchia degli oggetti per applicare le azioni necessarie.

Limitazione dei viaggi al foglio di lavoro

Un altro modo per accelerare i tuoi macro è limitare la quantità di volte in cui fai riferimento ai dati del foglio di lavoro nel tuo codice. È sempre meno efficiente acquisire i dati dal foglio di lavoro che dalla memoria. Vale a dire, le tue macro funzioneranno molto più velocemente se non devono interagire ripetutamente con il foglio di lavoro.

Ad esempio, il seguente codice semplice obbliga VBA a tornare continuamente a Fogli ("Foglio1"). Intervallo ("A1") per ottenere il numero necessario per il confronto eseguito nell'istruzione If:

For ReportMonth = 1 To 12 If Range ("A1"). Valore = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth

Un metodo molto più efficiente è quello di salvare il valore in Fogli ("Sheet1"). Intervallo ("A1") a una variabile denominata MyMonth. In questo modo, il codice fa riferimento alla variabile MyMonth anziché al foglio di lavoro:

Dim MyMonth as Integer MyMonth = Range ("A1"). Valore per ReportMonth = 1 A 12 Se MyMonth = ReportMonth Then MsgBox 1000000 / ReportMonth End If Next ReportMonth

Considerare l'utilizzo di variabili per lavorare con i dati in memoria anziché i fogli di lavoro di riferimento diretto.