VBA per a Excel 2016 és ràpid, però no sempre és prou ràpid. (Els programes d'ordinador mai són prou ràpids.) Continueu llegint per descobrir alguns exemples de programació que podeu utilitzar per accelerar les vostres macros.
S'està desactivant l'actualització de la pantalla
Quan executeu una macro, podeu seure i veure tota l'acció en pantalla que es produeix a la macro. Tot i que fer-ho pot ser instructiu, després que la macro funcioni correctament, sovint és molest i pot retardar considerablement el rendiment de la macro. Afortunadament, podeu desactivar l'actualització de la pantalla que normalment es produeix quan executeu una macro. Per desactivar l'actualització de la pantalla, utilitzeu la següent declaració:
Application.ScreenUpdating = Fals
Si voleu que l'usuari vegi què passa en qualsevol moment de la macro, utilitzeu la següent instrucció per tornar a activar l'actualització de la pantalla:
Application.ScreenUpdating = Veritable
Per demostrar la diferència de velocitat, executeu aquesta macro senzilla, que omple un rang amb números:
Sub FillRange()
Dim r com a llarg, c com a llarg
Número atenuat com a llarg
Nombre = 0
Per a r = 1 a 50
Per c = 1 a 50
Nombre = Nombre + 1
Cel·les(r, c).Seleccioneu
Cel·les(r, c).Valor = Nombre
Següent c
Següent r
End Sub
Veu que cada cel·la es selecciona i el valor que s'introdueix a les cel·les. Ara inseriu la següent instrucció al començament del procediment i torneu-la a executar:
Application.ScreenUpdating = Fals
L'interval s'omple molt més ràpidament i no veieu el resultat fins que la macro s'acaba d'executar i l'actualització de la pantalla s'estableix (automàticament) en True.
Quan esteu depurant codi, l'execució del programa de vegades acaba en algun lloc al mig sense que hàgiu tornat a activar l'actualització de la pantalla. Això de vegades fa que la finestra de l'aplicació d'Excel no respongui totalment. La sortida d'aquest estat congelat és senzilla: torneu al VBE i executeu la instrucció següent a la finestra Immediata:
Application.ScreenUpdating = Veritable
Desactivació del càlcul automàtic
Si teniu un full de treball amb moltes fórmules complexes, podeu trobar que podeu accelerar considerablement les coses configurant el mode de càlcul a manual mentre s'executa la macro. Quan acabi la macro, torneu a configurar el mode de càlcul a automàtic.
La declaració següent estableix el mode de càlcul d'Excel en manual:
Application.Calculation = xlCalculationManual
Executeu la següent instrucció per establir el mode de càlcul en automàtic:
Application.Calculation = xlCalculationAutomatic
Si el vostre codi utilitza cel·les amb resultats de fórmules, desactivar el càlcul significa que les cel·les no es tornaran a calcular tret que digueu explícitament a Excel que ho faci!
Eliminant aquests molestos missatges d'alerta
Com sabeu, una macro pot realitzar automàticament una sèrie d'accions. En molts casos, podeu iniciar una macro i després passar l'estona a la sala de descans mentre Excel fa les seves coses. Algunes operacions d'Excel, però, mostren missatges que requereixen una resposta humana. Aquest tipus de missatges significa que no podeu deixar Excel sense vigilància mentre executa la vostra macro, tret que conegueu el truc secret.
Podeu indicar a Excel que no mostri aquest tipus d'alertes mentre executa una macro.
El truc secret per evitar aquests missatges d'alerta és inserir la següent instrucció VBA a la vostra macro:
Application.DisplayAlerts = Fals
Excel executa l'operació per defecte per a aquest tipus de missatges. En el cas d'esborrar un full, l'operació predeterminada és Suprimeix. Si no esteu segur de quina és l'operació predeterminada, feu una prova per veure què passa.
Quan finalitza el procediment, Excel restableix automàticament la propietat DisplayAlerts a True. Si necessiteu tornar a activar les alertes abans que finalitzi el procediment, utilitzeu aquesta declaració:
Application.DisplayAlerts = Veritable
Simplificació de les referències d'objectes
Com probablement ja sabeu, les referències a objectes poden arribar a ser molt llargues. Per exemple, una referència plenament qualificada a un objecte Range pot tenir aquest aspecte:
Llibres de treball ("MyBook.xlsx"). Fulls de treball ("Full1") _
.Range ("Taxa d'interès")
Si la vostra macro utilitza aquest interval amb freqüència, és possible que vulgueu crear una variable d'objecte mitjançant l'ordre Establir. Per exemple, la instrucció següent assigna aquest objecte Range a una variable d'objecte anomenada Rate:
Establir tarifa = Llibres de treball ("MyBook.xlsx") _
.Fulls de treball(“Full1”). Interval(“Taxa d'interès”)
Després de definir aquesta variable d'objecte, podeu utilitzar la variable Rate en lloc de la referència llarga. Per exemple, podeu canviar el valor de la cel·la anomenada Taxa d'interès:
Taxa.Valor = ,085
Això és molt més fàcil d'escriure que la següent declaració:
Llibres de treball ("MyBook.xlsx"). Fulls de treball ("Full1"). _
Interval ("Taxa d'interès") = ,085
A més de simplificar la vostra codificació, l'ús de variables d'objecte accelera considerablement les vostres macros.
Declaració de tipus de variables
Normalment no us haureu de preocupar pel tipus de dades que assigneu a una variable. Excel gestiona tots els detalls entre bastidors. Per exemple, si teniu una variable anomenada MyVar, podeu assignar un número de qualsevol tipus a aquesta variable. Fins i tot podeu assignar-li una cadena de text més endavant en el procediment.
Si voleu que els vostres procediments s'executin el més ràpid possible, indiqueu a Excel quin tipus de dades s'assignarà a cadascuna de les vostres variables. Això es coneix com a declarar el tipus d'una variable.
En general, hauríeu d'utilitzar el tipus de dades que requereix el nombre més petit de bytes però encara podeu gestionar totes les dades assignades. Quan VBA treballa amb dades, la velocitat d'execució depèn del nombre de bytes que VBA té a la seva disposició. En altres paraules, com menys bytes utilitzen les dades, més ràpid VBA pot accedir i manipular les dades. Una excepció a això és el tipus de dades Integer. Si la velocitat és crítica, utilitzeu el tipus de dades llarg.
Si utilitzeu una variable d'objecte, podeu declarar-la com un tipus d'objecte concret. Aquí teniu un exemple:
Redueix la taxa com a rang
Establir tarifa = Llibres de treball ("MyBook.xlsx") _
.Fulls de treball(“Full1”). Interval(“Taxa d'interès”)
Ús de l'estructura With-End With
Necessites establir una sèrie de propietats per a un objecte? El vostre codi s'executa més ràpid si feu servir l'estructura With-End With. Un avantatge addicional és que el vostre codi pot ser més fàcil de llegir.
El codi següent no utilitza With-End With:
Selection.HorizontalAlignment = xlCenter
Selection.VerticalAlignment = xlCenter
Selection.WrapText = Veritable
Selecció.Orientació = 0
Selection.ShrinkToFit = Fals
Selection.MergeCells = Fals
Aquí teniu el mateix codi, reescrit per utilitzar With-End With:
Amb Selecció
.HorizontalAlignment = xlCenter
.VerticalAlignment = xlCenter
.WrapText = Veritable
.Orientació = 0
.ShrinkToFit = Fals
.MergeCells = Fals
Acaba amb
Quan utilitzeu With-End With, assegureu-vos que cada declaració comenci amb un punt.