excel-vba => Meilleures pratiques de VBA

Remarques

Nous les connaissons tous, mais ces pratiques sont beaucoup moins évidentes pour quelqu'un qui commence à programmer en VBA.

TOUJOURS utiliser "Option Explicit"

Dans la fenêtre Editeur VBA, dans le menu Outils, sélectionnez "Options":

Ensuite, dans l'onglet "Éditeur", assurez-vous que "Exiger une déclaration de variable" est coché:

La sélection de cette option place automatiquement Option Explicit en haut de chaque module VBA.

Petite remarque: Cela est vrai pour les modules, les modules de classe, etc. qui n'ont pas encore été ouverts. Donc, si vous avez déjà regardé par exemple le code de Sheet1 avant d’activer l’option "Exiger une déclaration de variable", Option Explicit ne sera pas ajoutée!

Option Explicit exige que chaque variable soit définie avant utilisation, par exemple avec une instruction Dim . Sans l' Option Explicit , le compilateur VBA supposera que tout mot non reconnu sera une nouvelle variable du type Variant , ce qui causera des bogues extrêmement difficiles à détecter liés aux erreurs typographiques. Lorsque l' Option Explicit activée, tous les mots non reconnus provoquent une erreur de compilation indiquant la ligne incriminée.

Exemple :

Si vous exécutez le code suivant:

Sub Test()
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

Vous recevrez le message suivant:

Vous avez fait une erreur en écrivant myvariable au lieu de my_variable , puis la boîte de message affiche une variable vide. Si vous utilisez Option Explicit , cette erreur n'est pas possible car vous obtiendrez un message d'erreur de compilation indiquant le problème.

Maintenant, si vous ajoutez la déclaration correcte:

Sub Test()
  Dim my_variable As Integer
  my_variable = 12
  MsgBox "My Variable is : " & myvariable
End Sub

Vous obtiendrez un message d'erreur indiquant précisément l'erreur avec myvariable :

Remarque sur les options explicites et tableaux ( déclaration d'un tableau dynamique ):
Vous pouvez utiliser l'instruction ReDim pour déclarer un tableau implicitement dans une procédure.
Veillez à ne pas mal orthographier le nom du tableau lorsque vous utilisez l'instruction ReDim
Même si l'instruction Option Explicit est incluse dans le module, un nouveau tableau sera créé
Dim arr() as Long

ReDim ar() 'creates new array "ar" - "ReDim ar()" acts like "Dim ar()"

Travailler avec des tableaux, pas avec des gammes

Blog Office - Meilleures pratiques pour le codage des performances VBA Excel

Souvent, les meilleures performances sont obtenues en évitant autant que possible l'utilisation de Range . Dans cet exemple, nous lisons un objet Range entier dans un tableau, mettons en carré chaque nombre du tableau, puis nous renvoyons le tableau à la Range . Cela n'accède à Range que deux fois, alors qu'une boucle y accède 20 fois pour les lectures / écritures.

Option Explicit
Sub WorkWithArrayExample()
    
Dim DataRange As Variant
Dim Irow As Long
Dim Icol As Integer
DataRange = ActiveSheet.Range("A1:A10").Value ' read all the values at once from the Excel grid, put into an array

For Irow = LBound(DataRange,1) To UBound(DataRange, 1) ' Get the number of rows.
  For Icol = LBound(DataRange,2) To UBound(DataRange, 2) ' Get the number of columns.
    DataRange(Irow, Icol) = DataRange(Irow, Icol) * DataRange(Irow, Icol) ' cell.value^2
  Next Icol
Next Irow
ActiveSheet.Range("A1:A10").Value = DataRange ' writes all the results back to the range at once
    
End Sub

Vous trouverez plus de conseils et d’informations avec des exemples temporels dans les FDU de Writing Williams VBA (Part 1) de Charles Williams et d’ autres articles de la série .

Utiliser les constantes VB lorsqu'elles sont disponibles

If MsgBox("Click OK") = vbOK Then

peut être utilisé à la place de

If MsgBox("Click OK") = 1 Then

afin d'améliorer la lisibilité.

Utilisez le navigateur d'objets pour trouver les constantes VB disponibles. Afficher → Navigateur d'objets ou F2 de l'éditeur VB.

Entrez le cours pour rechercher

Afficher les membres disponibles

Utiliser une dénomination de variable descriptive

Les noms descriptifs et la structure dans votre code aident à rendre les commentaires inutiles

Dim ductWidth  As Double
Dim ductHeight As Double
Dim ductArea   As Double

ductArea = ductWidth * ductHeight

est mieux que

Dim a, w, h

a = w * h

Cela est particulièrement utile lorsque vous copiez des données d'un endroit à un autre, qu'il s'agisse d'une cellule, d'une plage, d'une feuille de calcul ou d'un classeur. Aidez-vous en utilisant des noms tels que ceux-ci:

Dim myWB As Workbook
Dim srcWS As Worksheet
Dim destWS As Worksheet
Dim srcData As Range
Dim destData As Range

Set myWB = ActiveWorkbook
Set srcWS = myWB.Sheets("Sheet1")
Set destWS = myWB.Sheets("Sheet2")
Set srcData = srcWS.Range("A1:A10")
Set destData = destWS.Range("B11:B20")
destData = srcData

Si vous déclarez plusieurs variables dans une ligne, assurez-vous de spécifier un type pour chaque variable comme:

Dim ductWidth As Double, ductHeight As Double, ductArea As Double

Ce qui suit ne déclarera que la dernière variable et les premières resteront Variant :

Dim ductWidth, ductHeight, ductArea As Double

La gestion des erreurs

Une bonne gestion des erreurs empêche les utilisateurs finaux de voir les erreurs d'exécution de VBA et aide le développeur à diagnostiquer et à corriger facilement les erreurs.

Il existe trois méthodes principales de gestion des erreurs dans VBA, dont deux doivent être évitées pour les programmes distribués, sauf si cela est spécifiquement requis dans le code.

On Error GoTo 0 'Avoid using

On Error Resume Next 'Avoid using

Préférez utiliser:

On Error GoTo <line> 'Prefer using

En cas d'erreur GoTo 0

Si aucune gestion des erreurs n'est définie dans votre code, On Error GoTo 0 est le gestionnaire d'erreurs par défaut. Dans ce mode, toute erreur d'exécution lancera le message d'erreur VBA standard, vous permettant de mettre fin au code ou d'entrer en mode debug , en identifiant la source. Lors de l'écriture de code, cette méthode est la plus simple et la plus utile, mais elle devrait toujours être évitée pour le code distribué aux utilisateurs finaux, car cette méthode est très difficile à comprendre pour les utilisateurs finaux.

On Error Resume Next

On Error Resume Next fera en sorte que VBA ignore toutes les erreurs générées à l'exécution pour toutes les lignes après l'appel d'erreur jusqu'à ce que le gestionnaire d'erreurs ait été modifié. Dans des cas très spécifiques, cette ligne peut être utile, mais elle doit être évitée en dehors de ces cas. Par exemple, lors du lancement d'un programme distinct à partir d'une macro Excel, l'appel de On Error Resume Next peut être utile si vous ne savez pas si le programme est déjà ouvert ou non:

'In this example, we open an instance of Powerpoint using the On Error Resume Next call
Dim PPApp As PowerPoint.Application
Dim PPPres As PowerPoint.Presentation
Dim PPSlide As PowerPoint.Slide

'Open PPT if not running, otherwise select active instance
On Error Resume Next
Set PPApp = GetObject(, "PowerPoint.Application")
On Error GoTo ErrHandler
If PPApp Is Nothing Then
    'Open PowerPoint
    Set PPApp = CreateObject("PowerPoint.Application")
    PPApp.Visible = True
End If

Si nous n'avions pas utilisé l'appel de reprise On Error Resume Next et que l'application Powerpoint n'était pas déjà ouverte, la méthode GetObject émettrait une erreur. Ainsi, On Error Resume Next était nécessaire pour éviter de créer deux instances de l'application.

Remarque: Il est également recommandé de réinitialiser immédiatement le gestionnaire d’erreurs dès que vous n’avez plus besoin de l’appel de la fonction de On Error Resume Next

En cas d'erreur GoTo <line>

Cette méthode de gestion des erreurs est recommandée pour tout le code distribué aux autres utilisateurs. Cela permet au programmeur de contrôler exactement comment VBA traite une erreur en envoyant le code à la ligne spécifiée. La balise peut être remplie avec n'importe quelle chaîne (y compris les chaînes numériques) et enverra le code à la chaîne correspondante suivie de deux points. Plusieurs blocs de gestion des erreurs peuvent être utilisés en effectuant différents appels de On Error GoTo <line> . La sous-routine ci-dessous illustre la syntaxe d'un appel On Error GoTo <line> .

Remarque: Il est essentiel que la ligne de Exit Sub soit placée au-dessus du premier gestionnaire d’erreur et avant chaque gestionnaire d’erreur suivant pour empêcher le code de progresser naturellement dans le bloc sans qu’une erreur soit appelée. Par conséquent, il est recommandé de placer les gestionnaires d'erreurs à la fin d'un bloc de code dans les fonctions et la lisibilité.

Sub YourMethodName()
    On Error GoTo errorHandler
    ' Insert code here
    On Error GoTo secondErrorHandler

    Exit Sub 'The exit sub line is essential, as the code will otherwise
             'continue running into the error handling block, likely causing an error

errorHandler:
    MsgBox "Error " & Err.Number & ": " & Err.Description & " in " & _
        VBE.ActiveCodePane.CodeModule, vbOKOnly, "Error"
    Exit Sub

secondErrorHandler:
    If Err.Number = 424 Then 'Object not found error (purely for illustration)
        Application.ScreenUpdating = True
        Application.EnableEvents = True
        Exit Sub
    Else
        MsgBox "Error " & Err.Number & ": " & Err.Desctription
        Application.ScreenUpdating = True
        Application.EnableEvents = True   
        Exit Sub
    End If      
    Exit Sub

End Sub

Si vous quittez votre méthode avec votre code de gestion des erreurs, assurez-vous de nettoyer:

Annule tout ce qui est partiellement terminé
Fermer les fichiers
Réinitialiser la mise à jour de l'écran
Réinitialiser le mode de calcul
Réinitialiser les événements
Réinitialiser le pointeur de la souris
Appelez la méthode de déchargement sur les instances d’objets qui persistent après la End Sub
Réinitialiser la barre d'état

Documentez votre travail

Il est recommandé de documenter votre travail pour une utilisation ultérieure, en particulier si vous codez pour une charge de travail dynamique. Les bons commentaires devraient expliquer pourquoi le code fait quelque chose, pas ce que le code fait.

Function Bonus(EmployeeTitle as String) as Double
    If EmployeeTitle = "Sales" Then
        Bonus = 0    'Sales representatives receive commission instead of a bonus
    Else
        Bonus = .10
    End If
End Function

Si votre code est tellement obscur qu'il nécessite des commentaires pour expliquer ce qu'il fait, envisagez de le réécrire pour qu'il soit plus clair au lieu de l'expliquer par des commentaires. Par exemple, au lieu de:

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    
    'Boolean values can be evaluated as an integer, -1 for True, 0 for False.
    'This is used here to adjust the range from 5 to 7 rows if including weekends.
    Range("A1:A" & 5 - (IncludeWeekends * 2)).Copy
    Range("B1").PasteSpecial
End Sub

Clarifier le code pour être plus facile à suivre, tel que:

Sub CopySalesNumbers
    Dim IncludeWeekends as Boolean
    Dim DaysinWeek as Integer
    
    If IncludeWeekends Then
        DaysinWeek = 7
    Else
        DaysinWeek = 5
    End If
    Range("A1:A" & DaysinWeek).Copy
    Range("B1").PasteSpecial
End Sub

Désactiver les propriétés lors de l'exécution de la macro

Il est recommandé dans tout langage de programmation d' éviter toute optimisation prématurée. Cependant, si les tests révèlent que votre code s'exécute trop lentement, vous pouvez gagner en rapidité en désactivant certaines propriétés de l'application pendant son exécution. Ajoutez ce code à un module standard:

Public Sub SpeedUp( _
    SpeedUpOn As Boolean, _
    Optional xlCalc as XlCalculation = xlCalculationAutomatic _
)
    With Application
        If SpeedUpOn Then
            .ScreenUpdating = False
            .Calculation = xlCalculationManual
            .EnableEvents = False
            .DisplayStatusBar = False 'in case you are not showing any messages
            ActiveSheet.DisplayPageBreaks = False 'note this is a sheet-level setting
        Else
            .ScreenUpdating = True
            .Calculation = xlCalc
            .EnableEvents = True
            .DisplayStatusBar = True
            ActiveSheet.DisplayPageBreaks = True
        End If
    End With
End Sub

Plus d'informations sur le blog Office - Meilleures pratiques pour le codage des performances VBA Excel

Et juste l'appeler au début et à la fin des macros:

Public Sub SomeMacro
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    SpeedUp True

    'code here ...

    'by giving the second argument the initial "calculation" state is restored
    'otherwise it is set to 'xlCalculationAutomatic'
    SpeedUp False, xlCalc
End Sub

Bien que ceux - ci peuvent en grande partie être considérés comme des « améliorations » pour régulières Public Sub procédures, événement invalidantes manipulation avec Application.EnableEvents = False devrait être considérée comme obligatoire pour Worksheet_Change et Workbook_SheetChange macros d'événement privé qui modifient les valeurs sur une ou plusieurs feuilles de calcul. Si vous ne désactivez pas les déclencheurs d'événement, la macro d'événement s'exécute de manière récursive sur elle-même lorsqu'une valeur change et peut conduire à un classeur "gelé". N'oubliez pas de réactiver les événements avant de quitter la macro d'événements, éventuellement via un gestionnaire d'erreurs "exit sécurisé".

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    If Not Intersect(Target, Range("A:A")) Is Nothing Then
        On Error GoTo bm_Safe_Exit
        Application.EnableEvents = False
        
        'code that may change a value on the worksheet goes here
        
    End If
bm_Safe_Exit:
    Application.EnableEvents = True
End Sub

Une mise en garde: bien que la désactivation de ces paramètres améliore le temps d'exécution, ils peuvent rendre le débogage de votre application beaucoup plus difficile. Si votre code ne fonctionne pas correctement, SpeedUp True commentaire l'appel SpeedUp True jusqu'à ce que vous trouviez le problème.

Cela est particulièrement important si vous écrivez dans des cellules d'une feuille de calcul et que vous lisez ensuite les résultats calculés à partir des fonctions de la feuille de calcul, car xlCalculationManual empêche le calcul du classeur. Pour contourner ce SpeedUp sans désactiver SpeedUp , vous pouvez inclure Application.Calculate pour exécuter un calcul sur des points spécifiques.

REMARQUE: Comme ce sont des propriétés de l' Application elle-même, vous devez vous assurer qu'elles sont à nouveau activées avant la fermeture de votre macro. Cela rend particulièrement important d'utiliser des gestionnaires d'erreurs et d'éviter plusieurs points de sortie (par exemple, End ou Unload Me ).

Avec gestion des erreurs:

Public Sub SomeMacro()
    'store the initial "calculation" state
    Dim xlCalc As XlCalculation
    xlCalc = Application.Calculation

    On Error GoTo Handler
    SpeedUp True
    
    'code here ...
    i = 1 / 0
CleanExit:
    SpeedUp False, xlCalc
    Exit Sub
Handler:
    'handle error
    Resume CleanExit
End Sub

Évitez d'utiliser ActiveCell ou ActiveSheet dans Excel

Utiliser ActiveCell ou ActiveSheet peut être source d’erreurs si (pour une raison quelconque) le code est exécuté au mauvais endroit.

ActiveCell.Value = "Hello" 
'will place "Hello" in the cell that is currently selected
Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the currently selected sheet

ActiveSheet.Cells(1, 1).Value = "Hello" 
'will place "Hello" in A1 of the currently selected sheet
Sheets("MySheetName").Cells(1, 1).Value = "Hello" 
'will always place "Hello" in A1 of the sheet named "MySheetName"

L'utilisation d' Active* peut créer des problèmes lors de longues macros si votre utilisateur s'ennuie et clique sur une autre feuille de calcul ou ouvre un autre classeur.
Cela peut créer des problèmes si votre code ouvre ou crée un autre classeur.
Cela peut créer des problèmes si votre code utilise des Sheets("MyOtherSheet").Select et vous avez oublié quelle feuille vous étiez avant de commencer à lire ou à écrire.

Ne jamais assumer la feuille de travail

Même lorsque tout votre travail est dirigé vers une seule feuille de calcul, il est toujours recommandé de spécifier explicitement la feuille de calcul dans votre code. Cette habitude facilite considérablement l’élargissement de votre code ultérieurement ou la levée de certaines parties (ou de toutes) d’une Sub - Function ou d’une Function à réutiliser ailleurs. De nombreux développeurs prennent l’habitude de réutiliser le même nom de variable locale pour une feuille de calcul dans leur code, ce qui rend la réutilisation de ce code encore plus simple.

Par exemple, le code suivant est ambigu - mais fonctionne! - tant que le développeur n’active pas ou ne change pas de feuille de calcul:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Cells(1, 1).Value = Now()  ' don't refer to Cells without a sheet reference!
End Sub

Si la Sheet1 est active, la cellule A1 de la feuille Sheet1 sera remplie avec la date et l'heure actuelles. Mais si l'utilisateur modifie les feuilles de calcul pour une raison quelconque, le code sera mis à jour quelle que soit la feuille de calcul active. La feuille de calcul de la destination est ambiguë.

La meilleure pratique consiste à toujours identifier la feuille de calcul à laquelle votre code fait référence:

Option Explicit
Sub ShowTheTime()
    '--- displays the current time and date in cell A1 on the worksheet
    Dim myWB As Workbook
    Set myWB = ThisWorkbook
    Dim timestampSH As Worksheet
    Set timestampSH = myWB.Sheets("Sheet1")
    timestampSH.Cells(1, 1).Value = Now()
End Sub

Le code ci-dessus identifie clairement le classeur et la feuille de calcul. Bien que cela puisse sembler exagéré, créer une bonne habitude concernant les références cibles vous évitera de futurs problèmes.

Évitez d'utiliser SELECT ou ACTIVATE

Il est très rare que vous souhaitiez utiliser Select ou Activate dans votre code, mais certaines méthodes Excel nécessitent l'activation d'une feuille de calcul ou d'un classeur avant de pouvoir fonctionner comme prévu.

Si vous commencez juste à apprendre VBA, vous serez souvent invité à enregistrer vos actions à l'aide de l'enregistreur de macros, puis à regarder le code. Par exemple, j'ai enregistré les actions entreprises pour entrer une valeur dans la cellule D3 de la feuille Sheet2 et le code de la macro ressemble à ceci:

Option Explicit
Sub Macro1()
'
' Macro1 Macro
'

'
    Sheets("Sheet2").Select
    Range("D3").Select
    ActiveCell.FormulaR1C1 = "3.1415"   '(see **note below)
    Range("D4").Select
End Sub

Rappelez-vous cependant que l'enregistreur de macros crée une ligne de code pour chacune de vos actions (utilisateur). Cela inclut de cliquer sur l'onglet de la feuille de calcul pour sélectionner Sheet2 ( Sheets("Sheet2").Select ), en cliquant sur la cellule D3 avant d'entrer la valeur ( Range("D3").Select et en utilisant la touche Entrée en sélectionnant "la cellule sous la cellule actuellement sélectionnée: Range("D4").Select ).

Il existe plusieurs problèmes d'utilisation de .Select ici:

La feuille de calcul n'est pas toujours spécifiée. Cela se produit si vous ne changez pas de feuille de calcul pendant l'enregistrement et que le code produira des résultats différents pour les différentes feuilles de calcul actives.
.Select() est lent. Même si Application.ScreenUpdating est défini sur False , il s'agit d'une opération inutile à traiter.
.Select() est indiscipliné. Si Application.ScreenUpdating est laissé à True , Excel sélectionnera les cellules, la feuille de calcul, le formulaire, etc. C'est stressant pour les yeux et vraiment désagréable à regarder.
.Select() déclenchera les écouteurs. C'est déjà un peu avancé, mais à moins d'avoir fonctionné, des fonctions comme Worksheet_SelectionChange() seront déclenchées.

Lorsque vous codez en VBA, toutes les actions de "typage" (c.-à-d Select instructions Select ) ne sont plus nécessaires. Votre code peut être réduit à une seule instruction pour mettre la valeur dans la cellule:

'--- GOOD
ActiveWorkbook.Sheets("Sheet2").Range("D3").Value = 3.1415

'--- BETTER
Dim myWB      As Workbook
Dim myWS      As Worksheet
Dim myCell    As Range

Set myWB = ThisWorkbook             '*** see NOTE2
Set myWS = myWB.Sheets("Sheet2")
Set myCell = myWS.Range("D3")

myCell.Value = 3.1415

(Le meilleur exemple ci-dessus montre l'utilisation de variables intermédiaires pour séparer différentes parties de la référence de cellule. L'exemple GOOD fonctionnera toujours correctement, mais peut être très lourd dans des modules de code beaucoup plus longs et plus difficile à déboguer. )

** REMARQUE: l'enregistreur de macros émet de nombreuses hypothèses sur le type de données que vous entrez, en entrant dans ce cas une valeur de chaîne en tant que formule pour créer la valeur. Votre code n'a pas à le faire et peut simplement attribuer une valeur numérique directement à la cellule, comme indiqué ci-dessus.

** NOTE2: la pratique recommandée est de définir votre variable de classeur local sur ThisWorkbook au lieu d' ActiveWorkbook (sauf si vous en avez explicitement besoin). La raison en est que votre macro a généralement besoin / utilise des ressources dans le classeur dont provient le code VBA et ne regardera PAS en dehors de ce classeur, à moins que vous ne demandiez explicitement à votre code de travailler avec un autre classeur. Lorsque plusieurs classeurs sont ouverts dans Excel, ActiveWorkbook est celui qui peut être différent du classeur affiché dans votre éditeur VBA . Donc, vous pensez que vous exécutez dans un seul classeur lorsque vous faites vraiment référence à un autre. ThisWorkbook fait référence au classeur contenant le code en cours d'exécution.

Toujours définir et définir des références à tous les classeurs et feuilles

Lorsque vous travaillez avec plusieurs classeurs ouverts, chacun pouvant comporter plusieurs feuilles, il est plus sûr de définir et de définir une référence à tous les classeurs et feuilles.

Ne vous fiez pas à ActiveWorkbook ou ActiveSheet car ils peuvent être modifiés par l'utilisateur.

L'exemple de code suivant montre comment copier une plage de feuille « raw_data » dans le classeur « Data.xlsx » à feuille « Refined_Data » dans le classeur « Results.xlsx ».

La procédure montre également comment copier et coller sans utiliser la méthode Select .

Option Explicit

Sub CopyRanges_BetweenShts()

    
    Dim wbSrc                           As Workbook
    Dim wbDest                          As Workbook
    Dim shtCopy                         As Worksheet
    Dim shtPaste                        As Worksheet
    
    ' set reference to all workbooks by name, don't rely on ActiveWorkbook
    Set wbSrc = Workbooks("Data.xlsx")
    Set wbDest = Workbooks("Results.xlsx")
    
    ' set reference to all sheets by name, don't rely on ActiveSheet
    Set shtCopy = wbSrc.Sheet1 '// "Raw_Data" sheet
    Set shtPaste = wbDest.Sheet2 '// "Refined_Data") sheet
    
    ' copy range from "Data" workbook to "Results" workbook without using Select
    shtCopy.Range("A1:C10").Copy _
    Destination:=shtPaste.Range("A1")

End Sub

L'objet WorksheetFunction s'exécute plus rapidement qu'un équivalent UDF

VBA est compilé au moment de l'exécution, ce qui a un impact négatif énorme sur ses performances, tout ce qui est intégré sera plus rapide, essayez de les utiliser.

Par exemple, je compare les fonctions SUM et COUNTIF, mais vous pouvez utiliser si vous pouvez résoudre avec WorkSheetFunctions.

Une première tentative serait de parcourir la plage et de la traiter cellule par cellule (en utilisant une plage):

Sub UseRange()
    Dim rng as Range
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    Total = 0
    CountLessThan01 = 0
    For Each rng in Sheets(1).Range("A1:A100")
        Total = Total + rng.Value2
        If rng.Value < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next rng
    Debug.Print Total & ", " & CountLessThan01
End Sub

Une amélioration peut être de stocker les valeurs de plage dans un tableau et de traiter les éléments suivants:

Sub UseArray()
    Dim DataToSummarize As Variant
    Dim i As Long
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    DataToSummarize = Sheets(1).Range("A1:A100").Value2 'faster than .Value
    Total = 0
    CountLessThan01 = 0
    For i = 1 To 100
        Total = Total + DataToSummarize(i, 1)
        If DataToSummarize(i, 1) < 0.1 Then
            CountLessThan01 = CountLessThan01 + 1
        End If
    Next i
    Debug.Print Total & ", " & CountLessThan01
End Sub

Mais au lieu d'écrire une boucle, vous pouvez utiliser Application.Worksheetfunction qui est très pratique pour exécuter des formules simples:

Sub UseWorksheetFunction()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application.WorksheetFunction
        Total = .Sum(Sheets(1).Range("A1:A100"))
        CountLessThan01 = .CountIf(Sheets(1).Range("A1:A100"), "<0.1")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

Ou, pour des calculs plus complexes, vous pouvez même utiliser Application.Evaluate :

Sub UseEvaluate()
    Dim Total As Double
    Dim CountLessThan01 As Long
    
    With Application
        Total = .Evaluate("SUM(" & Sheet1.Range("A1:A100").Address( _
            external:=True) & ")")
        CountLessThan01 = .Evaluate("COUNTIF('Sheet1'!A1:A100,""<0.1"")")
    End With
    
    Debug.Print Total & ", " & CountLessThan01
End Sub

Et enfin, en cours d'exécution au-dessus de 25 000 fois chacun, voici la moyenne (5 tests) en millisecondes (bien sûr, ce sera différent sur chaque PC, mais les uns par rapport aux autres, ils se comporteront de la même manière):

UseWorksheetFunction: 2156 ms
UseArray: 2219 ms (+ 3%)
UseEvaluate: 4693 ms (+ 118%)
UseRange: 6530 ms (+ 203%)

Évitez de réutiliser les noms de propriétés ou de méthodes comme variables

Il n'est généralement pas considéré comme «meilleure pratique» de réutiliser les noms réservés de propriétés ou de méthodes en tant que noms de vos propres procédures et variables.

Mauvaise forme - Bien que ce qui suit soit (à proprement parler) légal, le code de travail, la réutilisation de la méthode Find ainsi que les propriétés Row , Column et Address peuvent provoquer des problèmes / conflits avec une ambiguïté de nom.

Option Explicit

Sub find()
    Dim row As Long, column As Long
    Dim find As String, address As Range
    
    find = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set address = .SpecialCells(xlCellTypeLastCell)
        row = .find(what:=find, after:=address).row        '< note .row not capitalized
        column = .find(what:=find, after:=address).column  '< note .column not capitalized
        
        Debug.Print "The first 'something' is in " & .Cells(row, column).address(0, 0)
    End With
End Sub

Bonne forme - Tous les mots réservés étant renommés en approximations proches mais uniques des originaux, tous les conflits de noms potentiels ont été évités.

Option Explicit

Sub myFind()
    Dim rw As Long, col As Long
    Dim wht As String, lastCell As Range
    
    wht = "something"
    
    With ThisWorkbook.Worksheets("Sheet1").Cells
        Set lastCell = .SpecialCells(xlCellTypeLastCell)
        rw = .Find(What:=wht, After:=lastCell).Row         '◄ note .Find and .Row
        col = .Find(What:=wht, After:=lastCell).Column     '◄ .Find and .Column
        
        Debug.Print "The first 'something' is in " & .Cells(rw, col).Address(0, 0)
    End With
End Sub

Bien qu'il puisse arriver que vous souhaitiez réécrire intentionnellement une méthode ou une propriété standard selon vos propres spécifications, ces situations sont rares. Dans la plupart des cas, évitez de réutiliser des noms réservés pour vos propres constructions.

Modified text is an extract of the original Stack Overflow Documentation

Sous licence CC BY-SA 3.0

Non affilié à Stack Overflow

excel-vba
Meilleures pratiques de VBA

Recherche…