Comment éviter D'utiliser Select dans Excel VBA

deux raisons principales pour lesquelles .Select / .Activate / Selection / Activecell / Activesheet / Activeworkbook etc... à éviter

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

comment l'éviter?

1) travailler directement avec les objets

considérez ce code

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

ce code peut aussi s'écrire comme

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

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

Dim ws as worksheet

Set ws = Sheets("Sheet1")

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

un petit point d'emphase que je vais ajouter à toutes les excellentes réponses données ci-dessus:

probablement, la plus grande chose que vous pouvez faire pour éviter D'utiliser Select est de autant que possible, utilisez les plages nommées (combinées avec des noms de variables significatives) dans votre code VBA . Ce point a été mentionné ci-dessus, mais un peu passé sous silence; toutefois, il mérite une attention particulière.

voici un couple supplémentaire raisons de faire un usage libéral des gammes nommées même si je suis sûr que je pourrais penser à plus.

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

exemple:

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 ce que les gammes nommées Months et MonthlySales contiennent, et ce que la procédure fait.

Pourquoi est-ce important? En partie parce qu'il est plus facile pour d'autres personnes à comprendre, mais même si vous sont la seule personne qui verra jamais ou utiliser votre code, vous devez toujours utiliser des gammes nommées et de bons noms de variables parce que vous oublierez ce que vous avez voulu faire avec elle un an plus tard, et vous gaspillerez 30 minutes en calculant juste ce que votre code est en train de faire.

les plages nommées s'assurent que vos macros ne se cassent pas quand (pas si!) la configuration du tableur changement.

considérer, 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 "bon sang, je pense que je vais ajouter une nouvelle colonne avec l'année dans la colonne A !", ou mettez une colonne des dépenses entre les colonnes mois et ventes, ou ajoutez un en-tête à chaque colonne. Maintenant, votre code est cassé. Et parce que vous avez utilisé des noms de variables terribles, il vous faudra un beaucoup plus de temps pour trouver comment le réparer que ça devrait prendre.

si vous aviez utilisé les plages nommées pour commencer, les colonnes Months et Sales pourraient être déplacées autour de tout ce que vous voulez, et votre code continuera à fonctionner très bien.

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

vous aurez .sélectionnez et .activez chaque fois que vous enregistrez des macros et réutilisez-les. Quand vous .sélectionnez une cellule ou une feuille, elle le rend tout simplement actif. À partir de ce point, chaque fois que vous utilisez des références non qualifiées comme Range.Value ils utilisent juste la cellule active et la feuille. Cela peut aussi être problématique si vous ne regardez pas où votre code est placé ou un utilisateur clique sur le classeur.

donc, vous pouvez éliminer ces problèmes en référençant directement vos cellules. Qui dit:

'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

'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 y a différentes combinaisons de ces méthodes, mais ce serait l'idée générale exprimée aussi rapidement que possible pour les gens impatients comme moi.

"... et je trouve que mon code serait plus réutilisable si j'étais capable d'utiliser des variables plutôt que des fonctions de sélection."

bien que je ne puisse pas penser à plus qu'une poignée isolée de situations où .Select serait un meilleur choix que le référencement direct de cellules, je me lèverais à la défense de Selection et faire remarquer qu'il ne devrait pas être jeté pour les mêmes raisons que .Select devrait être évité.

il y a des moments où avoir des macro-sous-routines courtes et économes de temps affectées à des combinaisons de touches à chaud disponibles avec le robinet d'un couple de touches permet d'économiser beaucoup de temps. Être en mesure de sélectionner un groupe de cellules pour promulguer le code opérationnel sur les merveilles de travaux lors du traitement des données de poche qui ne se conforme pas à un format de données de feuille de travail-large. De la même manière que vous pouvez sélectionner un groupe de cellules et appliquer un changement de format, en sélectionnant un groupe de cellules pour exécuter une macro spéciale code contre peut être un important gain de temps.

Exemples de Sélection de la base de sous-cadre:

Public Sub Run_on_Selected()
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Selected_Visible()
    'this is better for selected ranges on filtered data or containing hidden rows/columns
    Dim rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each rng In rSEL.SpecialCells(xlCellTypeVisible)
        Debug.Print rng.Address(0, 0)
        'cell-by-cell operational code here
    Next rng
    Set rSEL = Nothing
End Sub

Public Sub Run_on_Discontiguous_Area()
    'this is better for selected ranges of discontiguous areas
    Dim ara As Range, rng As Range, rSEL As Range
    Set rSEL = Selection    'store the current selection in case it changes
    For Each ara In rSEL.Areas
        Debug.Print ara.Address(0, 0)
        'cell group operational code here
        For Each rng In ara.Areas
            Debug.Print rng.Address(0, 0)
            'cell-by-cell operational code here
        Next rng
    Next ara
    Set rSEL = Nothing
End Sub

le code réel à traiter pourrait être n'importe quoi, d'une seule ligne à plusieurs modules. J'ai utilisé cette méthode pour initier des routines de longue durée sur une sélection irrégulière de cellules contenant les noms de fichiers de manuels externes.

en bref, ne pas jeter Selection en raison de son association étroite avec .Select et ActiveCell . En tant que bien de la feuille de travail, il a de nombreuses autres fins.

_{(Oui, je sais que cette question portait sur .Select , pas sur Selection mais je voulais éliminer toute idée fausse que des codeurs novices de VBA pourraient déduire.)}

veuillez noter que dans la suite je compare L'approche Select (celle que L'OP veut éviter), avec l'approche Range (et c'est la réponse à la question). Alors n'arrêtez 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 souhaitez définir la valeur de la cellule active à "foo". En utilisant ActiveCell vous écririez quelque chose comme ceci:

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

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

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

en utilisant des gammes, vous pouvez écrire une macro plus générique qui peut être utilisé pour définir la valeur de n'importe quelle cellule que vous voulez à ce que vous voulez:

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

alors vous pouvez réécrire Macro2 comme:

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

et Macro1 as:

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

Hope cela aide à clarifier un peu les choses.

éviter Select et Activate est le mouvement qui vous rend un peu meilleur développeur VBA. En général, Select et Activate sont utilisés lorsqu'une macro est enregistrée, de sorte que la feuille de travail ou la plage Parent est toujours considérée comme active.

voici comment vous pouvez éviter Select et Activate dans les cas suivants:

ajouter une nouvelle feuille de travail et copier une cellule dessus:

De l' (code généré avec l'enregistreur de macro):

Sub Makro2()
    Range("B2").Select
    Sheets.Add After:=ActiveSheet
    Sheets("Tabelle1").Select
    Sheets("Tabelle1").Name = "NewName"
    ActiveCell.FormulaR1C1 = "12"
    Range("B2").Select
    Selection.Copy
    Range("B3").Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

à:

Sub TestMe()
    Dim ws As Worksheet
    Set ws = Worksheets.Add
    With ws
        .Name = "NewName"
        .Range("B2") = 12
        .Range("B2").Copy Destination:=.Range("B3")
    End With
End Sub

quand vous voulez copier gamme entre les feuilles de travail:

de:

Sheets("Source").Select
Columns("A:D").Select
Selection.Copy
Sheets("Target").Select
Columns("A:D").Select
ActiveSheet.Paste

à:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").Range("a1")

à l'Aide de fantaisie plages nommées

, Vous pouvez y accéder avec [] . Qui est vraiment belle, comparée à l'autre façon. Vérifiez vous-même:

Dim Months As Range
Dim MonthlySales As Range

Set Months = Range("Months")    
Set MonthlySales = Range("MonthlySales")

Set Months =[Months]
Set MonthlySales = [MonthlySales]

l'exemple d'en haut ressemblerait à ceci:

Worksheets("Source").Columns("A:D").Copy Destination:=Worksheets("Target").[A1]

ne copie Pas les valeurs, mais de les prendre

habituellement , si vous êtes prêt à select , très probablement vous copiez quelque chose. Si vous êtes uniquement intéressé par les valeurs, c'est une bonne option pour éviter de sélectionner:

Range("B1:B6").Value = Range("A1:A6").Value

essayez toujours de renvoyer la feuille de travail ainsi

c'est probablement l'erreur la plus courante au vba . Chaque fois que vous copiez des gammes, parfois la feuille de travail n'est pas référée et VBA considère donc la feuille de travail Activew.

'This will work only if the 2. Worksheet is selected!
Public Sub TestMe()
    Dim rng As Range
    Set rng = Worksheets(2).Range(Cells(1, 1), Cells(2, 2)).Copy
End Sub

'This works always!
Public Sub TestMe2()
    Dim rng As Range
    With Worksheets(2)
        .Range(.Cells(1, 1), .Cells(2, 2)).Copy
    End With
End Sub

puis-je vraiment Ne jamais utiliser .Select ou .Activate pour quelque chose?

le seul le temps où vous pourriez être justifié d'utiliser .Activate et .Select est quand vous voulez s'assurer, qu'une feuille de travail spécifique est sélectionnée pour des raisons visuelles. Par exemple, que votre Excel s'ouvre toujours avec la feuille de travail de couverture sélectionnée en premier, sans tenir compte de laquelle était la feuille d'activés lorsque le fichier a été fermé. Ainsi, quelque chose comme ceci est absolument ok:

Private Sub Workbook_Open()
    Worksheets("Cover").Activate
End Sub

toujours énoncer le cahier de travail, la feuille de travail et la cellule/gamme.

par exemple:

Thisworkbook.Worksheets("fred").cells(1,1)
Workbooks("bob").Worksheets("fred").cells(1,1)

parce que les utilisateurs finaux seront toujours juste cliquez sur les boutons et dès que la mise au point se déplace hors du classeur le code veut travailler avec puis les choses vont complètement mal.

et n'utilisez jamais l'index d'un classeur.

Workbooks(1).Worksheets("fred").cells(1,1)

vous ne savez pas quels autres manuels seront ouverts lorsque l'utilisateur exécute votre code.

L'utilisation IMHO de .select vient de gens, qui comme moi ont commencé à apprendre VBA par nécessité par l'enregistrement des macros, puis la modification du code sans se rendre compte que .select et suivant selection est juste un intermédiaire inutile.

.select peuvent être évités, comme beaucoup posté déjà, en travaillant directement avec les objets déjà existants, ce qui permet diverses références indirectes comme calculer i et j d'une manière complexe, puis éditer de la cellule(i,j), etc.

sinon, il n'y a rien implicitement de mal avec .select lui-même et vous pouvez trouver des utilisations pour cela facilement, par exemple, j'ai une feuille de calcul que je remplis avec la date, j'active macro qui fait quelque magie avec elle et l'exporte dans un format acceptable sur une feuille séparée, qui, cependant, nécessite quelques entrées manuelles finales (imprévisibles) dans une cellule adjacente. Alors voici le moment pour .select qui m'épargne ce mouvement de souris supplémentaire et clic.

Réponse Rapide:

pour éviter d'utiliser la méthode .Select , vous pouvez définir une variable égale à la propriété que vous voulez.

► par exemple, si vous voulez la valeur dans Cell A1 vous pouvez définir une variable égale à la propriété valeur de cette cellule.

• exemple valOne = Range("A1").Value

► par exemple, si vous voulez le nom de code de 'Sheet3` vous pouvez définir une variable égale à la propriété codename de cette feuille de travail.

• exemple valTwo = Sheets("Sheet3").Codename

j'espère que ça aidera. Laissez-moi savoir si vous avez des questions.

ces méthodes sont plutôt stigmatisées, donc prendre la tête de @Vityata et @Jeeped pour le plaisir de tracer une ligne dans le sable:

Pourquoi ne pas appeler .Activate , .Select , Selection , ActiveSomething méthodes/propriétés

essentiellement parce qu'ils sont appelés principalement pour traiter l'entrée de l'utilisateur par L'application UI. Comme ce sont les méthodes appelées lorsque l'utilisateur manipule des objets à travers L'interface utilisateur, ce sont elles qui sont enregistrées par l'interface utilisateur. macro-enregistreur, et c'est pourquoi les appeler est soit fragile ou redondant pour la plupart des situations: vous n'avez pas à sélectionner un objet pour effectuer une action avec Selection immédiatement après.

Toutefois, cette définition règle des situations sur lesquelles ils sont appelés à se prononcer:

Quand appeler .Activate , .Select , .Selection , .ActiveSomething méthodes/propriétés

en gros, quand on s'attend à la finale utilisateur à jouer un rôle dans l'exécution.

si vous développez et vous attendez de l'utilisateur qu'il choisisse les instances d'objet à traiter pour votre code, alors .Selection ou .ActiveObject sont appropriés.

d'autre part, .Select et .Activate sont utiles lorsque vous pouvez inférer la prochaine action de l'utilisateur et vous voulez votre code pour guider l'utilisateur, ce qui peut lui faire gagner du temps et des clics de souris. Par exemple, si votre code vient de créer un marque nouvelle instance d'un graphique ou d'une mise à jour, l'utilisateur pourrait vouloir vérifier, et vous pourriez l'appeler .Activate sur elle ou sa feuille pour économiser de l'utilisateur le temps de recherche d'; ou si vous connaissez l'utilisateur devra mettre à jour certaines valeurs de la plage, vous pouvez par programmation sélectionnez cette plage.

il s'agit d'un exemple qui va effacer le contenu de la cellule" A1 " (ou plus si le type de sélection est xllastcell, etc). Tout cela sans avoir à sélectionner les cellules.

Application.GoTo Reference:=Workbook(WorkbookName).Worksheets(WorksheetName).Range("A1")
Range(Selection,selection(selectiontype)).clearcontents 

j'espère que cela aidera quelqu'un.

j'ai remarqué qu'aucune de ces réponses mentionner le .Offset Property . Cela peut également être utilisé pour éviter d'utiliser l'action Select lors de la manipulation de certaines cellules, en particulier en référence à une cellule sélectionnée (comme l'OP mentionne avec ActiveCell ).

Voici quelques exemples.

je supposerai aussi que le" ActiveCell "est J4 .

ActiveCell.Offset(2, 0).Value = 12

• cela changera la cellule J6 pour être une valeur de 12
• a moins -2 aurait fait référence à J2

ActiveCell.Offset(0,1).Copy ActiveCell.Offset(,2)

• cela copiera la cellule dans k4 à L4 .
• noter que "0" n'est pas nécessaire dans le paramètre offset si non nécessaire (,2)
• similaire à l'exemple précédent a moins 1 serait i4

ActiveCell.Offset(, -1).EntireColumn.ClearContents

• les valeurs apparaîtront dans toutes les cases de la colonne K.

il ne s'agit pas de dire qu'elles sont "meilleures" que les options ci-dessus, mais simplement d'énumérer des alternatives.