Comment éviter d'utiliser Select dans Excel VBA

Question

J'ai beaucoup entendu parler de la répugnance compréhensible d'utiliser .Select dans Excel VBA, mais je ne sais pas comment éviter de l'utiliser. Je trouve que mon code serait plus réutilisable si je pouvais utiliser des variables au lieu de Select fonctions. Cependant, je ne suis pas sûr de savoir comment faire référence à des choses (comme la fonction ActiveCell etc.) si vous n'utilisez pas Select .

J'ai trouvé cet article sur les gammes y cet exemple sur les avantages de ne pas utiliser de sélection mais je ne trouve rien sur comment .

Answer 1

Quelques exemples de la façon d'éviter de sélectionner

Utilisez Dim d variables

Dim rng as Range

Set la variable dans la plage requise. Il existe de nombreuses façons de se référer à une plage de cellules uniques :

Set rng = Range("A1")
Set rng = Cells(1, 1)
Set rng = Range("NamedRange")

Ou une gamme multi-cellules :

Set rng = Range("A1:B10")
Set rng = Range("A1", "B10")
Set rng = Range(Cells(1, 1), Cells(10, 2))
Set rng = Range("AnotherNamedRange")
Set rng = Range("A1").Resize(10, 2)

Vous peut utiliser le raccourci vers le Evaluate mais cette méthode est moins efficace et doit généralement être évitée dans le code de production.

Set rng = [A1]
Set rng = [A1:B10]

Tous les exemples ci-dessus se réfèrent à des cellules sur le feuille active . À moins que vous ne souhaitiez spécifiquement ne travailler qu'avec la feuille active, il est préférable de Dim une Worksheet variable également :

Dim ws As Worksheet
Set ws = Worksheets("Sheet1")
Set rng = ws.Cells(1, 1)
With ws
    Set rng = .Range(.Cells(1, 1), .Cells(2, 10))
End With

Si vous faire veulent travailler avec le ActiveSheet pour plus de clarté, il est préférable d'être explicite. Mais attention, car certains Worksheet permettent de changer la feuille active.

Set rng = ActiveSheet.Range("A1")

Là encore, il s'agit de la cahier d'exercices actif . À moins que vous ne souhaitiez spécifiquement travailler uniquement avec le ActiveWorkbook o ThisWorkbook il est préférable d'utiliser Dim a Workbook variable également.

Dim wb As Workbook
Set wb = Application.Workbooks("Book1")
Set rng = wb.Worksheets("Sheet1").Range("A1")

Si vous faire veulent travailler avec le ActiveWorkbook pour plus de clarté, il est préférable d'être explicite. Mais attention, car de nombreux WorkBook permettent de changer le livre actif.

Set rng = ActiveWorkbook.Worksheets("Sheet1").Range("A1")

Vous pouvez également utiliser le ThisWorkbook pour faire référence au livre contenant le code en cours d'exécution.

Set rng = ThisWorkbook.Worksheets("Sheet1").Range("A1")

Un morceau de code commun (mauvais) est d'ouvrir un livre, obtenir des données et le refermer.

C'est mauvais :

Sub foo()
    Dim v as Variant
    Workbooks("Book1.xlsx").Sheets(1).Range("A1").Clear
    Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = ActiveWorkbook.Sheets(1).Range("A1").Value
    Workbooks("SomeAlreadyOpenBook.xlsx").Activate
    ActiveWorkbook.Sheets("SomeSheet").Range("A1").Value = v
    Workbooks(2).Activate
    ActiveWorkbook.Close()
End Sub

Et ce serait mieux comme :

Sub foo()
    Dim v as Variant
    Dim wb1 as Workbook
    Dim  wb2 as Workbook
    Set wb1 = Workbooks("SomeAlreadyOpenBook.xlsx")
    Set wb2 = Workbooks.Open("C:\Path\To\SomeClosedBook.xlsx")
    v = wb2.Sheets("SomeSheet").Range("A1").Value
    wb1.Sheets("SomeOtherSheet").Range("A1").Value = v
    wb2.Close()
End Sub

Passez les gammes à votre Sub et Function comme variables de portée :

Sub ClearRange(r as Range)
    r.ClearContents
    '....
End Sub

Sub MyMacro()
    Dim rng as Range
    Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:B10")
    ClearRange rng
End Sub

Vous devez également appliquer des méthodes (telles que Find y Copy ) aux variables :

Dim rng1 As Range
Dim rng2 As Range
Set rng1 = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10")
Set rng2 = ThisWorkbook.Worksheets("SomeSheet").Range("B1:B10")
rng1.Copy rng2

Si vous bouclez sur une plage de cellules, il est souvent préférable (plus rapide) de copier d'abord les valeurs de la plage dans un tableau de variantes et de boucler sur celui-ci :

Dim dat As Variant
Dim rng As Range
Dim i As Long

Set rng = ThisWorkbook.Worksheets("SomeSheet").Range("A1:A10000")
dat = rng.Value  ' dat is now array (1 to 10000, 1 to 1)
for i = LBound(dat, 1) to UBound(dat, 1)
    dat(i,1) = dat(i, 1) * 10 ' Or whatever operation you need to perform
next
rng.Value = dat ' put new values back on sheet

C'est un petit avant-goût de ce qui est possible.

Answer 2

Deux raisons principales .Select , .Activate , Selection , Activecell , Activesheet , Activeworkbook etc. doivent être évités

1. Cela ralentit votre code.
2. C'est généralement la principale cause des erreurs d'exécution.

Comment l'éviter ?

1) Travailler directement avec les objets concernés

Considérons ce code

Sheets("Sheet1").Activate
Range("A1").Select
Selection.Value = "Blah"
Selection.NumberFormat = "@"

Ce code peut également être écrit comme suit

With Sheets("Sheet1").Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

2) Si nécessaire, déclarez vos variables. Le même code ci-dessus peut être écrit comme

Dim ws as worksheet

Set ws = Sheets("Sheet1")

With ws.Range("A1")
    .Value = "Blah"
    .NumberFormat = "@"
End With

C'est une bonne réponse, mais ce qui me manque dans ce sujet, c'est de savoir quand nous avons réellement besoin d'Activer. Tout le monde dit que c'est mauvais, mais personne n'explique les cas où il est judicieux de l'utiliser.

Situation dans laquelle vous ne peut éviter en utilisant .Activate/.Select . (J'ajouterai d'autres liens au fur et à mesure que je les trouverai).

1. Lorsque vous souhaitez présenter une feuille de calcul à un utilisateur afin que celui-ci puisse la voir.
2. Des scénarios comme La macro de travail renvoie une erreur lorsqu'elle est exécutée à partir d'un contrôle de formulaire où vous êtes obligé d'utiliser .Activate
3. Lorsque méthodes habituelles de Text To Columns / .Formula = .Formula ne fonctionne pas, vous devrez peut-être avoir recours à .Select

Answer 3

J'ajouterai un petit point à toutes les excellentes réponses données précédemment :

La meilleure chose que vous puissiez faire pour éviter d'utiliser Select est probablement de dans la mesure du possible, utilisez des plages nommées (combinées à des noms de variables significatifs) dans votre code VBA. . Ce point a été mentionné plus haut, mais il a été un peu escamoté ; il mérite cependant une attention particulière.

Voici quelques raisons supplémentaires pour faire un usage libéral des plages nommées, mais je suis sûr que je pourrais en trouver d'autres.

Les plages nommées rendent votre code plus facile à lire et à comprendre.

Ejemplo:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")
' E.g, "Months" might be a named range referring to A1:A12

Set MonthlySales = Range("MonthlySales")
' E.g, "Monthly Sales" might be a named range referring to B1:B12

Dim Month As Range
For Each Month in Months
    Debug.Print MonthlySales(Month.Row)
Next Month

Il est assez évident que les gammes nommées Months y MonthlySales contiennent, et ce que la procédure fait.

Pourquoi est-ce important ? En partie parce qu'il est plus facile pour les autres personnes de le comprendre, mais même si vous êtes la seule personne qui verra ou utilisera votre code, vous devez toujours utiliser des plages nommées et de bons noms de variables car vous oublierez ce que vous vouliez en faire un an plus tard, et vous allez gaspiller 30 minutes juste pour comprendre ce que fait votre code.

Les plages nommées garantissent que vos macros ne se cassent pas lorsque (pas si !) la configuration de la feuille de calcul change.

Considérez, si l'exemple ci-dessus avait été écrit comme ceci :

Dim rng1 As Range
Dim rng2 As Range

Set rng1 = Range("A1:A12")
Set rng2 = Range("B1:B12")

Dim rng3 As Range
For Each rng3 in rng1
    Debug.Print rng2(rng3.Row)
Next rng3

Ce code fonctionnera très bien au début - jusqu'à ce que vous ou un futur utilisateur décide "gee wiz, je pense que je vais ajouter une nouvelle colonne avec l'année en colonne". A !", ou mettre une colonne de dépenses entre les colonnes de mois et de ventes, ou ajouter un en-tête à chaque colonne. Maintenant, votre code est cassé. Et comme vous avez utilisé de terribles noms de variables, il vous faudra beaucoup plus de temps que prévu pour trouver comment le réparer.

Si vous aviez utilisé des plages nommées pour commencer, l'option Months y Sales Les colonnes peuvent être déplacées autant que vous le souhaitez, et votre code continuera à fonctionner parfaitement.

Answer 4

Je vais donner la réponse courte puisque tout le monde a donné la réponse longue.

Vous obtiendrez .select et .activate chaque fois que vous enregistrerez des macros et les réutiliserez. Lorsque vous sélectionnez une cellule ou une feuille, cela la rend simplement active. À partir de ce moment-là, chaque fois que vous utilisez des références non qualifiées comme Range.Value ils utilisent simplement la cellule et la feuille actives. Cela peut également poser problème si vous ne faites pas attention à l'endroit où votre code est placé ou si un utilisateur clique sur le classeur.

Vous pouvez donc éliminer ces problèmes en référençant directement vos cellules. Ce qui va :

'create and set a range
Dim Rng As Excel.Range
Set Rng = Workbooks("Book1").Worksheets("Sheet1").Range("A1")
'OR
Set Rng = Workbooks(1).Worksheets(1).Cells(1, 1)

Ou vous pourriez

'Just deal with the cell directly rather than creating a range
'I want to put the string "Hello" in Range A1 of sheet 1
Workbooks("Book1").Worksheets("Sheet1").Range("A1").value = "Hello"
'OR
Workbooks(1).Worksheets(1).Cells(1, 1).value = "Hello"

Il existe diverses combinaisons de ces méthodes, mais c'est l'idée générale exprimée le plus brièvement possible pour les personnes impatientes comme moi.

Answer 5

Veuillez noter que dans ce qui suit, je compare l'approche Select (celle que le PO veut éviter), avec l'approche Range (et c'est la réponse à la question). N'arrêtez donc pas de lire lorsque vous voyez le premier Select.

Cela dépend vraiment de ce que vous essayez de faire. Quoi qu'il en soit, un exemple simple pourrait être utile. Supposons que vous souhaitiez définir la valeur de la cellule active sur "foo". En utilisant ActiveCell, vous écrirez quelque chose comme ceci :

Sub Macro1()
    ActiveCell.Value = "foo"
End Sub

Si vous voulez l'utiliser pour une cellule qui n'est pas la cellule active, par exemple pour "B2", vous devez d'abord la sélectionner, comme ceci :

Sub Macro2()
    Range("B2").Select
    Macro1
End Sub

L'utilisation de Ranges vous permet d'écrire une macro plus générique qui peut être utilisée pour définir la valeur de n'importe quelle cellule comme vous le souhaitez :

Sub SetValue(cellAddress As String, aVal As Variant)
    Range(cellAddress).Value = aVal
End Sub

Alors vous pouvez réécrire la macro 2 comme suit :

Sub Macro2()
    SetCellValue "B2", "foo"
End Sub

Et Macro1 comme :

Sub Macro1()
    SetValue ActiveCell.Address, "foo"
End Sub