Sommario:
- Disattivazione dell'aggiornamento schermo
- Disattivazione del calcolo automatico
- Eliminazione di quei messaggi di avviso fastidiosi
- Semplificazione dei riferimenti agli oggetti
- Dichiarazione dei tipi di variabili
- Utilizzo della struttura With End con
Video: 6 Ways to Speed up your Excel Macros 2024
VBA per Excel 2016 è veloce, ma non sempre è abbastanza veloce. (I programmi per computer non sono mai abbastanza veloci.) Continua a leggere per scoprire alcuni esempi di programmazione che puoi utilizzare per velocizzare i tuoi macronutrienti.
Disattivazione dell'aggiornamento schermo
Quando si esegue una macro, è possibile sedersi e guardare tutte le azioni su schermo che si verificano nella macro. Anche se farlo può essere istruttivo, dopo aver fatto funzionare correttamente la macro, è spesso fastidioso e può rallentare notevolmente le prestazioni della tua macro. Fortunatamente, è possibile disabilitare l'aggiornamento dello schermo che normalmente si verifica quando si esegue una macro. Per disattivare l'aggiornamento dello schermo, utilizzare la seguente dichiarazione:
Applicazione. ScreenUpdating = False
Se vuoi che l'utente veda cosa sta succedendo in qualsiasi momento durante la macro, usa la seguente dichiarazione per riattivare l'aggiornamento dello schermo:
Applicazione. ScreenUpdating = True
Per dimostrare la differenza di velocità, esegui questa semplice macro, che riempie un intervallo con i numeri:
Sub FillRange () Dim r as Long, c As Long Dim Number as Long Number = 0 Per r = Da 1 a 50 Per c = da 1 a 50 Numero = Numero + 1 celle (r, c). Seleziona celle (r, c). Valore = Numero Avanti c Successivo r Fine Sott.
Si vede ogni cella selezionata e il valore inserito nelle celle. Ora inserisci la seguente dichiarazione all'inizio della procedura ed eseguila di nuovo:
Applicazione. ScreenUpdating = False
L'intervallo viene riempito molto più velocemente e non viene visualizzato il risultato fino a quando la macro non è terminata e l'aggiornamento dello schermo è (automaticamente) impostato su True.
Quando esegui il debug del codice, l'esecuzione del programma a volte finisce nel mezzo senza che l'Aggiornamento dello schermo venga riattivato. Ciò a volte fa sì che la finestra dell'applicazione di Excel non risponda completamente. L'uscita da questo stato bloccato è semplice: tornare al VBE ed eseguire la seguente istruzione nella finestra Immediata:
Applicazione. ScreenUpdating = True
Disattivazione del calcolo automatico
Se si dispone di un foglio di lavoro con molte formule complesse, è possibile che sia possibile accelerare notevolmente le cose impostando la modalità di calcolo su manuale mentre la macro è in esecuzione. Al termine della macro, impostare la modalità di calcolo su automatico.
La seguente istruzione imposta la modalità di calcolo Excel su manuale:
Applicazione. Calcolo = xlCalculationManual
Esegue l'istruzione successiva per impostare la modalità di calcolo su automatico:
Applicazione. Calcolo = xlCalculationAutomatic
Se il tuo codice utilizza celle con risultati di formula, disattivando il calcolo significa che le celle non verranno ricalcolate a meno che tu non dica esplicitamente a Excel di farlo!
Eliminazione di quei messaggi di avviso fastidiosi
Come sai, una macro può eseguire automaticamente una serie di azioni. In molti casi, è possibile avviare una macro e quindi uscire nella sala riunioni mentre Excel fa il suo dovere. Alcune operazioni di Excel, tuttavia, visualizzano messaggi che richiedono una risposta umana. Questi tipi di messaggi significano che non puoi lasciare Excel incustodito mentre esegue la tua macro, a meno che tu non conosca il trucco segreto.
Il trucco segreto per evitare questi messaggi di avviso è l'inserimento della seguente istruzione VBA nella macro:
Applicazione. DisplayAlerts = Falso
Excel esegue l'operazione predefinita per questi tipi di messaggi. In caso di cancellazione di un foglio, l'operazione predefinita è Cancella. Se non sei sicuro di quale sia l'operazione predefinita, esegui un test per vedere cosa succede.
Al termine della procedura, Excel reimposta automaticamente la proprietà DisplayAlerts su True. Se è necessario riattivare gli avvisi prima che la procedura termini, utilizzare questa istruzione:
Applicazione. DisplayAlerts = True
Semplificazione dei riferimenti agli oggetti
Come probabilmente già sapete, i riferimenti agli oggetti possono diventare molto lunghi. Ad esempio, un riferimento completo a un oggetto Range può avere il seguente aspetto:
Cartelle di lavoro ("MyBook. Xlsx"). Fogli di lavoro ("Foglio1") _. Intervallo ("Tasso di interesse")
Se la macro utilizza frequentemente questo intervallo, è possibile creare una variabile oggetto utilizzando il comando Imposta. Ad esempio, la seguente istruzione assegna questo oggetto Range a una variabile oggetto denominata Rate:
Imposta frequenza = cartelle di lavoro ("MyBook. Xlsx") _. Fogli di lavoro (“Sheet1”). Range ("InterestRate")
Dopo aver definito questa variabile oggetto, è possibile utilizzare la variabile Rate piuttosto che il riferimento lungo. Ad esempio, è possibile modificare il valore della cella denominata Tasso di interesse:
Tasso. Valore =. 085
Questo è molto più semplice da scrivere rispetto alla seguente dichiarazione:
Cartelle di lavoro ("MyBook. Xlsx"). Fogli di lavoro (“Sheet1”). _ Range ("InterestRate") =. 085
Oltre a semplificare la codifica, l'uso delle variabili oggetto accelera notevolmente i tuoi macronutrienti.
Dichiarazione dei tipi di variabili
Di solito non devi preoccuparti del tipo di dati che assegni a una variabile. Excel gestisce tutti i dettagli per te dietro le quinte. Ad esempio, se si dispone di una variabile denominata MyVar, è possibile assegnare un numero di qualsiasi tipo a tale variabile. È anche possibile assegnare una stringa di testo ad esso più avanti nella procedura.
Se vuoi che le tue procedure siano eseguite il più velocemente possibile, comunica a Excel quale tipo di dati saranno assegnati a ciascuna delle tue variabili. Questo è noto come che dichiara il tipo di una variabile.
In generale, è necessario utilizzare il tipo di dati che richiede il numero più piccolo di byte, ma può ancora gestire tutti i dati ad esso assegnati. Quando VBA funziona con i dati, la velocità di esecuzione dipende dal numero di byte che VBA ha a sua disposizione. In altre parole, vengono utilizzati meno byte, più velocemente VBA può accedere e manipolare i dati.Un'eccezione a questo è il tipo di dati Integer. Se la velocità è critica, utilizzare invece il tipo di dati lungo.
Se si utilizza una variabile oggetto, è possibile dichiarare la variabile come un particolare tipo di oggetto. Ecco un esempio:
Dim Rate come Range Set Rate = Cartelle di lavoro ("MyBook. Xlsx") _. Fogli di lavoro (“Sheet1”). Intervallo ("Tasso di interesse")
Utilizzo della struttura With End con
È necessario impostare un numero di proprietà per un oggetto? Il tuo codice funziona più velocemente se usi la struttura With-End With. Un ulteriore vantaggio è che il tuo codice potrebbe essere più facile da leggere.
Il seguente codice non usa With-End With:
Selection. HorizontalAlignment = xlCenter Selection. VerticalAlignment = xlCenter Selection. WrapText = True Selection. Orientamento = 0 Selezione. ShrinkToFit = False Selection. MergeCells = False
Ecco lo stesso codice, riscritto per utilizzare With-End With:
With Selection. HorizontalAlignment = xlCenter. VerticalAlignment = xlCenter. WrapText = True. Orientamento = 0. ShrinkToFit = False. MergeCells = False End With
Quando usi With-End With, assicurati che ogni istruzione inizi con un punto.
