Pour quelle raison ma macro Excel est-elle lente lorsque think-cell est activé ?

L'utilisation de la fonction .Select est un problème courant qui peut entraîner des problèmes de performances dans les macros VBA. Chaque fois qu'une cellule est sélectionnée dans Excel, chaque complément Excel (y compris think-cell) est averti de ce changement de sélection, ce qui ralentit fortement la macro. Les macros créées à l'aide de l'enregistreur de macros sont particulièrement sujettes à ce type de problème.

• Blog Office : "Quelle est la manière la plus rapide de parcourir une grande plage dans Excel?"
• MSDN : Improving Performance in Excel 2007 : "Reference Excel objects such as Range objects directly, without selecting or activating them" (MSDN, Améliorer la performance d'Excel 2007 : "Référencer des objets Excel objects tels que des objets de plages directement, sans les sélectionner ou les activer". Disponible en anglais uniquement dans la section "Faster VBA Macros").

Exemple : Comment éviter d'utiliser la déclaration .Select

Analysons la simple macro AutoFillTable suivante :

Sub AutoFillTable()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim nCount As Integer
    nCount = iRange.Cells.Count
    For i = 1 To nCount
        Selection.Copy
        If iRange.Cells.Item(i).Value = "" Then
            iRange.Cells.Item(i).Range("A1").Select
            ActiveSheet.Paste
        Else
            iRange.Cells.Item(i).Range("A1").Select
        End If
    Next
End Sub

Cette fonction ouvre une zone de saisie qui demande à l'utilisateur de spécifier une plage de cellules. Cette fonction passe en revue toutes les cellules de cette plage. Si elle détecte une cellule qui n'est pas vide, elle en copie le contenu dans le Presse-papiers. Elle colle ensuite le contenu du Presse-papiers dans chaque cellule vide suivante.

AutoFillTable utilise le Presse-papiers pour copier le contenu des cellules. Aussi, la fonction doit sélectionner chaque cellule qui est manipulée afin qu'Excel sache à partir de quelle cellule copier et vers quelle cellule coller. La solution recommandée est illustrée dans la fonction suivante AutoFillTable2 :

Sub AutoFillTable2()
    Dim iRange As Excel.Range
    Set iRange = Application.InputBox(prompt:="Enter range", Type:=8)
    Dim iCellMaster As Excel.Range
    For Each iCell In iRange.Cells
        If iCell.Value = "" Then
            If Not iCellMaster Is Nothing Then
                iCellMaster.Copy (iCell)
            End If
        Else
            Set iCellMaster = iCell
        End If
    Next iCell
End Sub

AutoFillTable2 présente deux différences majeures par rapport à son prédécesseur :

1. Tout particulièrement, elle n'implique plus la sélection d'une cellule. lorsqu'une cellule non vide est détectée, cette cellule est stockée dans la variable iCellMaster. Par la suite, dès qu'une cellule non vide est détectée, tout le contenu de iCellMaster est copié dans iCell.
2. La fonction de langage Visual Basic For Each … Next est utilisée afin de vérifier chaque cellule de la plage. Il s'agit là d'une première amélioration en termes de lisibilité.