Excel 2016 VBA Speed ​​Tips - dummies

VBA pour Excel 2016 est rapide, mais pas toujours rapide. (Les programmes informatiques ne sont jamais assez rapides.) Continuez à lire pour découvrir quelques exemples de programmation que vous pouvez utiliser pour accélérer vos macros.

Désactivation de la mise à jour de l'écran

Lors de l'exécution d'une macro, vous pouvez vous asseoir et observer toutes les actions à l'écran qui se produisent dans la macro. Bien que cela puisse être instructif, une fois que la macro fonctionne correctement, c'est souvent ennuyeux et cela peut considérablement ralentir les performances de votre macro. Heureusement, vous pouvez désactiver la mise à jour de l'écran qui se produit normalement lorsque vous exécutez une macro. Pour désactiver la mise à jour de l'écran, utilisez l'instruction suivante:

Application. ScreenUpdating = False

Si vous souhaitez que l'utilisateur puisse voir ce qui se passe à un moment quelconque de la macro, utilisez l'instruction suivante pour réactiver la mise à jour de l'écran:

Application. ScreenUpdating = True

Pour montrer la différence de vitesse, exécutez cette macro simple, qui remplit une plage avec des nombres:

Sub FillRange () Dim r comme Long, c As Long Dim Nombre comme Long Number = 0 For r = 1 à 50 Pour c = 1 à 50 Nombre = Nombre + 1 Cellules (r, c). Sélectionnez Cellules (r, c). Value = Number Next c Next r End Sub

Vous voyez chaque cellule sélectionnée et la valeur saisie dans les cellules. Maintenant insérez l'instruction suivante au début de la procédure et exécutez-la à nouveau:

Application. ScreenUpdating = False

La plage est remplie beaucoup plus rapidement, et vous ne voyez pas le résultat tant que la macro n'est pas terminée et que la mise à jour de l'écran est (automatiquement) définie sur True.

Lorsque vous déboguez du code, l'exécution du programme se termine parfois quelque part au milieu sans que vous ayez réactivé la mise à jour de l'écran. Cela provoque parfois la fenêtre d'application Excel pour devenir totalement ne répond pas. La sortie de cet état figé est simple: Revenez au VBE et exécutez l'instruction suivante dans la fenêtre Exécution:

Application. ScreenUpdating = True

Désactiver le calcul automatique

Si vous avez une feuille de calcul contenant de nombreuses formules complexes, vous pouvez constater que vous pouvez accélérer considérablement les choses en définissant le mode de calcul sur manual pendant l'exécution de votre macro. Lorsque la macro se termine, redéfinissez le mode de calcul sur Automatique.

L'instruction suivante définit le mode de calcul Excel sur manual:

Application. Calculation = xlCalculationManual

Exécute l'instruction suivante pour mettre le mode de calcul à automatique:

Application. Calcul = xlCalculationAutomatic

Si votre code utilise des cellules avec des résultats de formule, désactiver le calcul signifie que les cellules ne seront pas recalculées à moins que vous n'ayez explicitement dit à Excel de le faire!

Éliminer ces messages d'alerte agaçants

Comme vous le savez, une macro peut automatiquement effectuer une série d'actions. Dans la plupart des cas, vous pouvez démarrer une macro, puis passer du temps dans la salle de pause pendant qu'Excel fait son travail. Certaines opérations Excel, cependant, affichent des messages qui nécessitent une réponse humaine. Ces types de messages signifient que vous ne pouvez pas laisser Excel sans surveillance pendant qu'il exécute votre macro, sauf si vous connaissez l'astuce secrète.

Vous pouvez demander à Excel de ne pas afficher ces types d'alertes lors de l'exécution d'une macro.

L'astuce secrète pour éviter ces messages d'alerte consiste à insérer l'instruction VBA suivante dans votre macro:

Application. DisplayAlerts = False

Excel exécute l'opération par défaut pour ces types de messages. Dans le cas de la suppression d'une feuille, l'opération par défaut est Supprimer. Si vous n'êtes pas sûr de l'opération par défaut, effectuez un test pour voir ce qui se passe.

Une fois la procédure terminée, Excel réinitialise automatiquement la propriété DisplayAlerts sur True. Si vous devez réactiver les alertes avant la fin de la procédure, utilisez cette instruction:

Application. DisplayAlerts = True

Simplifier les références d'objet

Comme vous le savez probablement déjà, les références aux objets peuvent devenir très longues. Par exemple, une référence entièrement qualifiée à un objet Range peut ressembler à ceci:

Workbooks ("MyBook.xlsx"). Feuilles de travail ("Sheet1") _. Range ("InterestRate")

Si votre macro utilise fréquemment cette plage, vous pouvez créer une variable d'objet à l'aide de la commande Set. Par exemple, l'instruction suivante affecte cet objet Range à une variable d'objet nommée Rate:

Set Rate = Workbooks ("MyBook.xlsx") _. Feuilles de travail ("Sheet1"). Range ("InterestRate")

Après avoir défini cette variable d'objet, vous pouvez utiliser la variable Rate plutôt que la référence longue. Par exemple, vous pouvez modifier la valeur de la cellule nommée InterestRate:

Rate. Valeur =. 085

Ceci est beaucoup plus facile à taper que la déclaration suivante:

Workbooks ("MyBook.xlsx"). Feuilles de travail ("Sheet1"). _ Range ("InterestRate") =. 085

En plus de simplifier votre codage, l'utilisation de variables d'objet accélère considérablement vos macros.

Déclaration des types de variables

Vous n'avez généralement pas à vous soucier du type de données que vous affectez à une variable. Excel gère tous les détails pour vous dans les coulisses. Par exemple, si vous avez une variable nommée MyVar, vous pouvez assigner un nombre de n'importe quel type à cette variable. Vous pouvez même lui affecter une chaîne de texte plus tard dans la procédure.

Si vous voulez que vos procédures s'exécutent le plus rapidement possible, dites à Excel quel type de données sera affecté à chacune de vos variables. Ceci est connu comme déclarant le type d'une variable.

En général, vous devez utiliser le type de données qui nécessite le plus petit nombre d'octets tout en pouvant gérer toutes les données qui lui sont affectées. Lorsque VBA travaille avec des données, la vitesse d'exécution dépend du nombre d'octets que VBA a à sa disposition. En d'autres termes, plus la quantité de données utilisées est faible, plus la VBA peut accéder et manipuler les données plus rapidement.Une exception à ceci est le type de données Integer. Si la vitesse est critique, utilisez plutôt le type de données Long.

Si vous utilisez une variable d'objet, vous pouvez déclarer la variable comme un type d'objet particulier. Voici un exemple:

Dim Rate as Range Set Rate = Classeurs ("MyBook.xlsx") _. Feuilles de travail ("Sheet1"). Range ("InterestRate")

Utilisation de With-End With structure

Avez-vous besoin de définir un certain nombre de propriétés pour un objet? Votre code s'exécute plus rapidement si vous utilisez la structure With-End With. Un avantage supplémentaire est que votre code peut être plus facile à lire.

Le code suivant n'utilise pas With-End With:

Selection. HorizontalAlignment = Sélection de xlCenter. VerticalAlignment = Sélection de xlCenter. WrapText = Sélection vraie. Orientation = 0 Sélection. ShrinkToFit = Fausse sélection. MergeCells = False

Voici le même code, réécrit pour utiliser With-End With:

With Selection. HorizontalAlignment = xlCenter. VerticalAlignment = xlCenter. WrapText = Vrai. Orientation = 0. ShrinkToFit = Faux. MergeCells = False End With

Lorsque vous utilisez With-End With, assurez-vous que chaque instruction commence par un point.