A mesura que les vostres macros d'Excel es tornen cada cop més robustes i complexes, és possible que perdin rendiment. Quan es parla de macros, la paraula rendiment sol ser sinònim de velocitat . La velocitat és la rapidesa amb què els vostres procediments VBA realitzen les tasques previstes. A continuació es mostren deu maneres d'ajudar a mantenir les macros d'Excel en funcionament al seu nivell de rendiment òptim.
Sabíeu que cada vegada que es canvia o manipula una cel·la que afecta qualsevol fórmula del vostre full de càlcul, Excel torna a calcular tot el full de treball? En fulls de treball que tenen una gran quantitat de fórmules, aquest comportament pot alentir dràsticament les vostres macros.
Podeu utilitzar la propietat Application.Calculation per dir a Excel que canviï al mode de càlcul manual. Quan un llibre de treball està en mode de càlcul manual, el llibre de treball no es tornarà a calcular fins que no activeu explícitament un càlcul prement la tecla F9.
Col·loqueu Excel al mode de càlcul manual, executeu el vostre codi i, a continuació, torneu al mode de càlcul automàtic.
Sub macro1() Application.Calculation = xlCalculationManual 'Coloqueu el vostre codi de macro aquí Application.Calculation = xlCalculationAutomatic End Sub
Tornar a establir el mode de càlcul a xlCalculationAutomatic activarà automàticament un recàlcul del full de treball, de manera que no cal que premeu la tecla F9 després de l'execució de la macro.
És possible que noteu que quan s'executen les vostres macros, la pantalla fa una bona quantitat de parpelleig. Aquest parpelleig és que Excel intenta tornar a dibuixar la pantalla per mostrar l'estat actual del full de treball. Malauradament, cada vegada que Excel torna a dibuixar la pantalla, ocupa recursos de memòria.
Podeu utilitzar la propietat Application.ScreenUpdating per desactivar les actualitzacions de pantalla fins que s'hagi completat la macro. Desactivar l'actualització de la pantalla estalvia temps i recursos, permetent que la macro s'executi una mica més ràpid. Un cop s'hagi acabat d'executar el codi de macro, podeu tornar a activar l'actualització de la pantalla.
Sub macro1() Application.Calculation = xlCalculationManual Application.ScreenUpdating = Fals 'Coloqueu el vostre codi de macro aquí Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = Veritable End Sub
Després de tornar a establir la propietat ScreenUpdating a True, Excel activarà automàticament un redibuix de la pantalla.
La barra d'estat d'Excel, que apareix a la part inferior de la finestra d'Excel, normalment mostra el progrés de determinades accions a Excel. Si la vostra macro funciona amb moltes dades, la barra d'estat ocuparà alguns recursos.
És important tenir en compte que desactivar l'actualització de la pantalla és independent de desactivar la visualització de la barra d'estat. La barra d'estat es continuarà actualitzant encara que desactiveu l'actualització de la pantalla. Podeu utilitzar la propietat Application.DisplayStatusBar per desactivar temporalment les actualitzacions de la barra d'estat, millorant encara més el rendiment de la vostra macro:
Sub macro1() Application.Calculation = xlCalculationManual Application.ScreenUpdating = Fals Application.DisplayStatusBar = Fals 'Coloqueu el vostre codi de macro aquí Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = Veritable Application.DisplayStatusBar = True End Sub
Podeu implementar macros com a procediments d'esdeveniment, dient a Excel que executi cert codi quan canvia un full de treball o un llibre de treball.
De vegades, les macros estàndard fan canvis que desencadenen un procediment d'esdeveniment. Per exemple, si teniu una macro estàndard que manipula diverses cel·les a Sheet1, cada vegada que es canvia una cel·la d'aquest full, la vostra macro s'ha d'aturar mentre s'executa l'esdeveniment Worksheet_Change.
Podeu afegir un altre nivell d'augment del rendiment mitjançant la propietat EnableEvents per dir a Excel que ignori els esdeveniments mentre s'executa la macro.
Estableix la propietat EnableEvents a False abans d'executar la macro. Un cop s'hagi acabat d'executar el codi de macro, podeu tornar a establir la propietat EnableEvents a True.
Sub macro1() Application.Calculation = xlCalculationManual Application.ScreenUpdating = Fals Application.DisplayStatusBar = Fals Application.EnableEvents = Fals 'Coloqueu el vostre codi de macro aquí Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = Veritable Application.DisplayStatusBar = True Application.EnableEvents = True End Sub
Cada vegada que la vostra macro modifica el nombre de files, modifica el nombre de columnes o altera la configuració de la pàgina d'un full de treball, Excel es veu obligat a prendre temps per tornar a calcular els salts de pàgina que es mostren al full.
Podeu evitar aquest comportament només amagant els salts de pàgina abans d'iniciar la macro.
Estableix la propietat del full DisplayPageBreaks a False per amagar els salts de pàgina. Si voleu continuar mostrant els salts de pàgina després de l'execució de la macro, torneu a establir la propietat del full DisplayPageBreaks a True.
Sub macro1() Application.Calculation = xlCalculationManual Application.ScreenUpdating = Fals Application.DisplayStatusBar = Fals Application.EnableEvents = Fals Activesheet.DisplayPageBreaks = Fals 'Coloqueu el vostre codi de macro aquí Application.Calculation = xlCalculationAutomatic Application.ScreenUpdating = Veritable Application.DisplayStatusBar = True Application.EnableEvents = True Activesheet.DisplayPageBreaks = Veritable End Sub
Si la vostra macro manipula taules dinàmiques que contenen fonts de dades grans, és possible que tingueu un rendiment baix quan feu coses com ara afegir o moure camps dinàmics de manera dinàmica.
Podeu millorar el rendiment de la vostra macro suspendint el recàlcul de la taula dinàmica fins que s'hagin fet tots els canvis del camp dinàmic. Simplement establiu la propietat PivotTable.ManualUpdate a True per ajornar el recàlcul, executeu el vostre codi de macro i, a continuació, torneu a establir la propietat PivotTable.ManualUpdate a False per activar el recàlcul.
Sub macro1()
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=True
'Coloqueu el vostre codi de macro aquí
ActiveSheet.PivotTables("PivotTable1").ManualUpdate=False
End Sub
És important recordar que, tot i que Macro Recorder estalvia temps escrivint codi VBA per a tu, no sempre escriu el codi més eficient. Un bon exemple és com Macro Recorder captura qualsevol acció de copiar i enganxar que feu durant la gravació.
Podeu donar un lleuger impuls a les vostres macros eliminant l'intermediari i fent una còpia directa d'una cel·la a una cel·la de destinació. Aquest codi alternatiu utilitza l'argument Destinació per ometre el porta-retalls i copiar el contingut de la cel·la A1 directament a la cel·la B1.
Interval ("A1"). Destinació de la còpia:=Interval ("B1")
Si necessiteu copiar només valors (no formatació ni fórmules), podeu millorar encara més el rendiment evitant el mètode de còpia tots junts. Simplement establiu el valor de la cel·la de destinació al mateix valor que es troba a la cel·la d'origen. Aquest mètode és aproximadament 25 vegades més ràpid que utilitzar el mètode de còpia:
Interval("B1").Valor = Interval("A1").Valor
Si només necessiteu copiar fórmules d'una cel·la a una altra (no valors ni format), podeu establir la fórmula de la cel·la de destinació amb la mateixa fórmula continguda a la cel·la d'origen:
Interval("B1").Fórmula = Interval("A1").Fórmula
Quan enregistreu macros, sovint manipulareu el mateix objecte més d'una vegada. Podeu estalviar temps i millorar el rendiment utilitzant la instrucció With per realitzar diverses accions sobre un objecte determinat d'una sola vegada.
La instrucció With utilitzada a l'exemple següent indica a Excel que aplique tots els canvis de format alhora:
Amb Range("A1"). Font
.Negreta = cert
.Itàlica = Vertader
.Underline = xlUnderlineStyleSingle
Acaba amb
Adquirir l'hàbit de dividir les accions a les declaracions With no només permetrà que les vostres macros funcionin més ràpidament, sinó que també us facilitarà la lectura del vostre codi de macros.
A Macro Recorder li agrada utilitzar el mètode Select per seleccionar objectes de manera explícita abans de fer-hi accions. En general, no cal seleccionar objectes abans de treballar-hi. De fet, podeu millorar dràsticament el rendiment de les macros si no feu servir el mètode Select.
Després d'enregistrar les vostres macros, feu un hàbit alterar el codi generat per eliminar els mètodes Select. En aquest cas, el codi optimitzat seria el següent:
Fulls ("Full1"). Interval ("A1"). FórmulaR1C1 = "1000"
Fulls("Full2").Range("A1").FormulaR1C1 = "1000"
Fulls("Full3").Range("A1").FormulaR1C1 = "1000"
Tingueu en compte que no s'està seleccionant res. El codi simplement utilitza la jerarquia d'objectes per aplicar les accions necessàries.
Una altra manera d'accelerar les macros és limitar la quantitat de vegades que feu referència a les dades del full de treball al vostre codi. Sempre és menys eficient agafar dades del full de treball que de la memòria. És a dir, les vostres macros s'executaran molt més ràpid si no han d'interaccionar repetidament amb el full de treball.
Per exemple, el codi senzill següent obliga a VBA a tornar contínuament a Sheets ("Full1").Range ("A1") per obtenir el número necessari per a la comparació que es realitza a la instrucció If:
Per ReportMonth = 1 a 12
If Range("A1").Value = ReportMonth Aleshores
MsgBox 1000000 / ReportMonth
Acaba si
Pròxim InformeMes
Un mètode molt més eficient és desar el valor a Sheets(“Full1”).Range(“A1”) en una variable anomenada El meu mes. D'aquesta manera, el codi fa referència a la variable MyMonth en lloc del full de treball:
Atenua MyMonth com a nombre enter
El meu mes = Interval("A1").Valor
Per ReportMonth = 1 a 12
Si MyMonth = ReportMonth Aleshores
MsgBox 1000000 / ReportMonth
Acaba si
Pròxim InformeMes
Penseu en aprofitar variables per treballar amb dades a la memòria en lloc de fer referència directament a fulls de treball.
Quan es crida a un mètode o propietat d'un objecte, cal passar per la interfície IDispatch del component OLE. Les trucades a aquests components OLE triguen temps, de manera que reduir el nombre de referències als components OLE pot millorar la velocitat del codi de macro.
Per a la invocació de propietats o mètodes d'objectes, generalment s'utilitza el mètode de representació de Object.Method , és a dir, el "." El símbol s'utilitza per invocar propietats i mètodes.
Per tant, el nombre de trucades de mètode o propietat es pot jutjar segons el nombre de símbols ".". Com menys "." símbol, més ràpid s'executa el codi.
Per exemple, la següent afirmació inclou 3 símbols ".".
ThisWorkbook.Sheet1.Range("A1").Value = 100La següent afirmació només té un símbol ".".
Activewindow.Top = 100Aquí teniu alguns trucs per reduir el nombre de símbols "." per córrer més ràpid.
En primer lloc, quan necessiteu fer referència al mateix objecte repetidament, podeu definir l'objecte en una variable per reduir el nombre de trucades. Per exemple, el codi següent requereix dues trucades per línia.
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = 100
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = 200
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = 300Com que l' objecte Sheets("Sheet1") s'ha de fer referència repetidament, primer es pot establir en una variable sht , de manera que cada codi només s'ha de cridar una vegada.
Set sht = ThisWorkbook.Sheets("Sheet1")
sht.Cells(1, 1) = 100
sht.Cells(2, 1) = 200
sht.Cells(3, 1) = 300En segon lloc, si no voleu declarar una variable temporal sht, també podeu utilitzar la instrucció With esmentada anteriorment. Com es mostra a l'exemple següent:
With ThisWorkbook.Sheets("Sheet1")
.Cells(1, 1) = 100
.Cells(2, 1) = 200
.Cells(3, 1) = 300
End WithEn tercer lloc, quan hi ha molts bucles, intenteu mantenir les propietats i els mètodes fora del bucle. Quan reutilitzeu un valor de propietat del mateix objecte en un bucle, primer podeu assignar el valor de la propietat a una variable especificada fora del bucle i després utilitzar la variable al bucle, que pot aconseguir una velocitat més ràpida. Com es mostra a l'exemple següent:
For i = 1 To 1000
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = Cells(1, 2).Value
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = Cells(1, 2).Value
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = Cells(1, 2).Value
Next iCada bucle d'aquest exemple obté la propietat Value de la cel·la Cells(1,2). Si assigneu la propietat Value de Cells(1.2) a una variable abans que comenci el bucle, obtindreu una execució més ràpida. Com es mostra a l'exemple següent:
tmp = Cells(1, 2).Value
For i = 1 To 1000
ThisWorkbook.Sheets("Sheet1").Cells(1, 1) = tmp
ThisWorkbook.Sheets("Sheet1").Cells(2, 1) = tmp
ThisWorkbook.Sheets("Sheet1").Cells(3, 1) = tmp
Next iEl codi anterior crida a ThisWorkbook.Sheets("Sheet1") cada vegada que fa un bucle. Podeu fer-ho més ràpidament utilitzant la instrucció With per moure la trucada a ThisWorkbook.Sheets("Sheet1") fora del bucle. Com es mostra a l'exemple següent:
tmp = Cells(1, 2).Value
With ThisWorkbook.Sheets("Sheet1")
For i = 1 To 1000
.Cells(1, 1) = tmp
.Cells(2, 1) = tmp
.Cells(3, 1) = tmp
Next i
End With
Els principiants normalment prefereixen utilitzar variables de tipus Variant, la qual cosa té l'avantatge de ser menys complicada perquè es pot utilitzar qualsevol tipus de dades sense el problema de desbordament de memòria si les dades són massa grans per als tipus de dades Integer o Long. Tanmateix, les dades de tipus Varienmt requereixen més espai de memòria addicional que els altres tipus especificats (2 bytes per a dades enteres, 4 bytes per a dades llargues i 16 bytes per a dades de variant), VBA requereix més temps per processar dades de tipus Variant que altres tipus especificats. de dades. Com mostra l'exemple següent.
Sub VariantTest()
Dim i As Long
Dim ix As Integer, iy As Integer, iz As Integer
Dim vx As Variant, vy As Variant, vz As Variant
Dim tm As Date
vx = 100: vy = 50
tm = Timer
For i = 1 To 1000000
vz = vx * vy
vz = vx + vy
vz = vx - vy
vz = vx / vy
Next i
Debug.Print "Variant types take " & Format((Timer - tm), "0.00000") & " seconds"
ix = 100: iy = 50
tm = Timer
For i = 1 To 1000000
iz = ix * iy
iz = ix + iy
iz = ix - iy
iz = ix / iy
Next i
Debug.Print "Integer types take " & Format((Timer - tm), "0.00000") & " seconds"
End SubEn el codi anterior, les línies 8 a 13 fan 1 milió d'operacions de suma, resta, multiplicació i divisió de variables Variant, i les línies 17 a 22 fan 1 milió d'operacions de suma, resta, multiplicació i divisió de variables senceres. Al meu ordinador, el funcionament de la variable Variant va trigar uns 0,09375 segons, mentre que el funcionament de la variable Integer va trigar uns 0,03125 segons. Els resultats poden variar d'un ordinador a un altre, però les variables Variant són significativament més lentes que les variables Integer .
Per aquest motiu, es recomana evitar l'ús de variables Variant quan es pot utilitzar explícitament el tipus de dades especificat .
Com bloquejar Microsoft Word d'obrir fitxers en mode de només lectura a Windows Microsoft Word obre fitxers en mode de només lectura, cosa que fa que sigui impossible editar-los? No us preocupeu, els mètodes es mostren a continuació
Com corregir errors en imprimir documents incorrectes de Microsoft Word Els errors en imprimir documents de Word amb tipus de lletra canviats, paràgrafs desordenats, falta de text o contingut perdut són força habituals. No obstant això, no
Si heu utilitzat el llapis o el marcador per dibuixar a les vostres diapositives de PowerPoint durant una presentació, podeu desar els dibuixos per a la següent presentació o esborrar-los de manera que la propera vegada que el mostreu, començareu amb diapositives de PowerPoint netes. Seguiu aquestes instruccions per esborrar dibuixos a llapis i ressaltadors: Esborrant línies una a […]
La biblioteca d'estils conté fitxers CSS, fitxers de llenguatge de full d'estil extensible (XSL) i imatges utilitzades per pàgines mestres predefinides, dissenys de pàgina i controls a SharePoint 2010. Per localitzar fitxers CSS a la biblioteca d'estils d'un lloc de publicació: Trieu Accions del lloc→Visualitza Tot el contingut del lloc. Apareix el contingut del lloc. La biblioteca d'estil es troba a […]
No desbordeu el vostre públic amb números gigantesques. A Microsoft Excel, podeu millorar la llegibilitat dels vostres taulers i informes formatant els vostres números perquè apareguin en milers o milions.
Apreneu a utilitzar les eines de xarxes socials de SharePoints que permeten a persones i grups comunicar-se, col·laborar, compartir i connectar-se.
Les dates julianes s'utilitzen sovint en entorns de fabricació com a marca de temps i referència ràpida per a un número de lot. Aquest tipus de codificació de dates permet als minoristes, consumidors i agents de serveis identificar quan es va fabricar un producte i, per tant, l'antiguitat del producte. Les dates julianes també s'utilitzen en programació, militars i astronomia. Diferents […]
Podeu crear una aplicació web a Access 2016. Què és, de totes maneres, una aplicació web? Bé, web vol dir que està en línia i l'aplicació és només una abreviatura de "aplicació". Una aplicació web personalitzada és una aplicació de base de dades en línia a la qual s'accedeix des del núvol mitjançant un navegador. Creeu i manteniu l'aplicació web a la versió d'escriptori […]
La majoria de les pàgines del SharePoint 2010 mostren una llista d'enllaços de navegació a la barra d'inici ràpid al costat esquerre de la pàgina. La barra d'inici ràpid mostra enllaços al contingut del lloc destacat, com ara llistes, biblioteques, llocs i pàgines de publicació. La barra d'inici ràpid inclou dos enllaços molt importants: Enllaç de tot el contingut del lloc: el […]
Per a problemes senzills, Solver a Excel sol trobar ràpidament els valors òptims de la variable Solver per a la funció objectiu. Però, en alguns casos, Solver té problemes per trobar els valors de la variable Solver que optimitzen la funció objectiu. En aquests casos, Solver normalment mostra un missatge o un missatge d'error que descriu o discuteix el problema que […]
