Lorsque vous travaillez avec Excel, la plupart de votre temps est consacré à la zone de la feuille de calcul - à traiter les cellules et les plages.
Et si vous souhaitez automatiser votre travail dans Excel à l'aide de VBA, vous devez savoir comment travailler avec des cellules et des plages à l'aide de VBA.
Il y a beaucoup de choses différentes que vous pouvez faire avec les plages dans VBA (telles que sélectionner, copier, déplacer, éditer, etc.).
Donc, pour couvrir ce sujet, je vais diviser ce didacticiel en sections et vous montrer comment travailler avec des cellules et des plages dans Excel VBA à l'aide d'exemples.
Commençons.
Tous les codes que je mentionne dans ce tutoriel doivent être placés dans l'éditeur VB. Rendez-vous dans la section « Où mettre le code VBA » pour savoir comment cela fonctionne.Si vous souhaitez apprendre VBA en toute simplicité, consultez mon Formation en ligne Excel VBA.
Sélection d'une cellule / plage dans Excel à l'aide de VBA
Pour travailler avec des cellules et des plages dans Excel à l'aide de VBA, vous n'avez pas besoin de le sélectionner.
Dans la plupart des cas, il vaut mieux ne pas sélectionner de cellules ou de plages (comme nous le verrons).
Malgré cela, il est important que vous parcouriez cette section et compreniez son fonctionnement. Cela sera crucial dans votre apprentissage VBA et de nombreux concepts abordés ici seront utilisés tout au long de ce didacticiel.
Commençons donc par un exemple très simple.
Sélection d'une seule cellule à l'aide de VBA
Si vous souhaitez sélectionner une seule cellule dans la feuille active (disons A1), vous pouvez utiliser le code ci-dessous :
Sub SelectCell() Range("A1").Select End Sub
Le code ci-dessus comporte les parties obligatoires « Sub » et « End Sub » et une ligne de code qui sélectionne la cellule A1.
Range ("A1") indique à VBA l'adresse de la cellule à laquelle nous voulons nous référer.
Sélectionner est une méthode de l'objet Range et sélectionne les cellules/plage spécifiées dans l'objet Range. Les références de cellules doivent être entourées de guillemets doubles.
Ce code afficherait une erreur dans le cas où une feuille de graphique est une feuille active. Une feuille de graphique contient des graphiques et n'est pas largement utilisée. Comme il ne contient pas de cellules/plages, le code ci-dessus ne peut pas le sélectionner et finirait par afficher une erreur.
Notez que puisque vous souhaitez sélectionner la cellule dans la feuille active, il vous suffit de spécifier l'adresse de la cellule.
Mais si vous souhaitez sélectionner la cellule dans une autre feuille (disons Sheet2), vous devez d'abord activer Sheet2, puis sélectionner la cellule qu'elle contient.
Sub SelectCell() Worksheets ("Sheet2").Activate Range ("A1").Select End Sub
De même, vous pouvez également activer un classeur, puis y activer une feuille de calcul spécifique, puis sélectionner une cellule.
Sub SelectCell() Workbooks("Book2.xlsx").Worksheets("Sheet2").Activate Range ("A1").Select End Sub
Notez que lorsque vous faites référence à des classeurs, vous devez utiliser le nom complet avec l'extension de fichier (.xlsx dans le code ci-dessus). Si le classeur n'a jamais été enregistré, vous n'avez pas besoin d'utiliser l'extension de fichier.
Maintenant, ces exemples ne sont pas très utiles, mais vous verrez plus loin dans ce tutoriel comment nous pouvons utiliser les mêmes concepts pour copier et coller des cellules dans Excel (à l'aide de VBA).
Tout comme nous sélectionnons une cellule, nous pouvons également sélectionner une plage.
Dans le cas d'une plage, il peut s'agir d'une plage de taille fixe ou d'une plage de taille variable.
Dans une plage de taille fixe, vous sauriez quelle est la taille de la plage et vous pouvez utiliser la taille exacte dans votre code VBA. Mais avec une plage de taille variable, vous n'avez aucune idée de la taille de la plage et vous devez utiliser un peu de magie VBA.
Voyons comment faire cela.
Sélection d'une plage de taille fixe
Voici le code qui sélectionnera la plage A1:D20.
Sub SelectRange() Range("A1:D20").Select End Sub
Une autre façon de procéder consiste à utiliser le code ci-dessous :
Sub SelectRange() Range ("A1", "D20").Select End Sub
Le code ci-dessus prend l'adresse de cellule en haut à gauche (A1) et l'adresse de cellule en bas à droite (D20) et sélectionne toute la plage. Cette technique devient utile lorsque vous travaillez avec des plages de taille variable (comme nous le verrons lorsque la propriété End sera abordée plus loin dans ce didacticiel).
Si vous souhaitez que la sélection se produise dans un autre classeur ou une autre feuille de calcul, vous devez indiquer à VBA les noms exacts de ces objets.
Par exemple, le code ci-dessous sélectionnerait la plage A1:D20 dans la feuille de calcul Sheet2 dans le classeur Book2.
Sub SelectRange() Workbooks("Book2.xlsx").Worksheets("Sheet1").Activate Range ("A1:D20").Select End Sub
Maintenant, que faire si vous ne savez pas combien de lignes il y a. Que se passe-t-il si vous souhaitez sélectionner toutes les cellules contenant une valeur.
Dans ces cas, vous devez utiliser les méthodes indiquées dans la section suivante (sur la sélection d'une plage de taille variable).
Sélection d'une plage de taille variable
Il existe différentes manières de sélectionner une plage de cellules. La méthode que vous choisirez dépendra de la façon dont les données sont structurées.
Dans cette section, je vais couvrir quelques techniques utiles qui sont vraiment utiles lorsque vous travaillez avec des plages en VBA.
Sélectionnez à l'aide de la propriété CurrentRange
Dans les cas où vous ne savez pas combien de lignes/colonnes contiennent les données, vous pouvez utiliser la propriété CurrentRange de l'objet Range.
La propriété CurrentRange couvre toutes les cellules remplies contiguës dans une plage de données.
Vous trouverez ci-dessous le code qui sélectionnera la région actuelle contenant la cellule A1.
Sub SelectCurrentRegion() Range("A1").CurrentRegion.Select End Sub
La méthode ci-dessus est bonne lorsque vous avez toutes les données sous forme de tableau sans aucune ligne/colonne vide.
Mais si vous avez des lignes/colonnes vides dans vos données, il ne sélectionnera pas celles après les lignes/colonnes vides. Dans l'image ci-dessous, le code CurrentRegion sélectionne les données jusqu'à la ligne 10 car la ligne 11 est vide.
Dans de tels cas, vous souhaiterez peut-être utiliser la propriété UsedRange de l'objet Worksheet.
Sélectionnez à l'aide de la propriété UsedRange
UsedRange vous permet de faire référence à toutes les cellules qui ont été modifiées.
Ainsi, le code ci-dessous sélectionnerait toutes les cellules utilisées dans la feuille active.
Sub SelectUsedRegion() ActiveSheet.UsedRange.Select End Sub
Notez que si vous avez une cellule éloignée qui a été utilisée, elle serait prise en compte par le code ci-dessus et toutes les cellules jusqu'à cette cellule utilisée seraient sélectionnées.
Sélectionnez à l'aide de la propriété de fin
Maintenant, cette partie est vraiment utile.
La propriété End vous permet de sélectionner la dernière cellule remplie. Cela vous permet d'imiter l'effet de la touche fléchée Contrôle Bas/Haut ou des touches Contrôle Droite/Gauche.
Essayons de comprendre cela à l'aide d'un exemple.
Supposons que vous ayez un ensemble de données comme indiqué ci-dessous et que vous souhaitiez sélectionner rapidement les dernières cellules remplies dans la colonne A.
Le problème ici est que les données peuvent changer et vous ne savez pas combien de cellules sont remplies. Si vous devez le faire à l'aide du clavier, vous pouvez sélectionner la cellule A1, puis utiliser la touche Ctrl + flèche vers le bas, et cela sélectionnera la dernière cellule remplie de la colonne.
Voyons maintenant comment faire cela en utilisant VBA. Cette technique est pratique lorsque vous souhaitez accéder rapidement à la dernière cellule remplie dans une colonne de taille variable
Sub GoToLastFilledCell() Range("A1").End(xlDown).Select End Sub
Le code ci-dessus sauterait à la dernière cellule remplie de la colonne A.
De même, vous pouvez utiliser End(xlToRight) pour accéder à la dernière cellule remplie d'une ligne.
Sub GoToLastFilledCell() Range("A1").End(xlToRight).Select End Sub
Maintenant, que se passe-t-il si vous souhaitez sélectionner la colonne entière au lieu de sauter à la dernière cellule remplie.
Vous pouvez le faire en utilisant le code ci-dessous :
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown)).Select End Sub
Dans le code ci-dessus, nous avons utilisé la première et la dernière référence de la cellule que nous devons sélectionner. Peu importe le nombre de cellules remplies, le code ci-dessus les sélectionnera toutes.
Rappelez-vous l'exemple ci-dessus où nous avons sélectionné la plage A1:D20 en utilisant la ligne de code suivante :
Plage ("A1″,"D20")
Ici, A1 était la cellule en haut à gauche et D20 était la cellule en bas à droite de la plage. Nous pouvons utiliser la même logique pour sélectionner des plages de taille variable. Mais comme nous ne connaissons pas l'adresse exacte de la cellule en bas à droite, nous avons utilisé la propriété End pour l'obtenir.
Dans Range ("A1", Range ("A1").End(xlDown)), "A1" fait référence à la première cellule et Range ("A1").End(xlDown) fait référence à la dernière cellule. Puisque nous avons fourni les deux références, la méthode Select sélectionne toutes les cellules entre ces deux références.
De même, vous pouvez également sélectionner un ensemble de données complet comportant plusieurs lignes et colonnes.
Le code ci-dessous sélectionnerait toutes les lignes/colonnes remplies à partir de la cellule A1.
Sub SelectFilledCells() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Select End Sub
Dans le code ci-dessus, nous avons utilisé Range("A1").End(xlDown).End(xlToRight) pour obtenir la référence de la cellule remplie en bas à droite de l'ensemble de données.
Différence entre l'utilisation de CurrentRegion et End
Si vous vous demandez pourquoi utiliser la propriété End pour sélectionner la plage remplie lorsque nous avons la propriété CurrentRegion, laissez-moi vous dire la différence.
Avec la propriété End, vous pouvez spécifier la cellule de départ. Par exemple, si vous avez vos données dans A1:D20, mais que la première ligne contient des en-têtes, vous pouvez utiliser la propriété End pour sélectionner les données sans les en-têtes (à l'aide du code ci-dessous).
Sub SelectFilledCells() Range("A2", Range("A2").End(xlDown).End(xlToRight)).Select End Sub
Mais CurrentRegion sélectionnerait automatiquement l'ensemble de données complet, y compris les en-têtes.
Jusqu'à présent dans ce didacticiel, nous avons vu comment faire référence à une plage de cellules de différentes manières.
Voyons maintenant quelques façons dont nous pouvons réellement utiliser ces techniques pour faire du travail.
Copier des cellules / plages à l'aide de VBA
Comme je l'ai mentionné au début de ce tutoriel, il n'est pas nécessaire de sélectionner une cellule pour effectuer des actions dessus. Vous verrez dans cette section comment copier des cellules et des plages sans même les sélectionner.
Commençons par un exemple simple.
Copie d'une seule cellule
Si vous souhaitez copier la cellule A1 et la coller dans la cellule D1, le code ci-dessous le ferait.
Sub CopyCell() Range("A1").Copy Range("D1") End Sub
Notez que la méthode de copie de l'objet range copie la cellule (tout comme Control +C) et la colle dans la destination spécifiée.
Dans l'exemple de code ci-dessus, la destination est spécifiée dans la même ligne où vous utilisez la méthode Copy. Si vous souhaitez rendre votre code encore plus lisible, vous pouvez utiliser le code ci-dessous :
Sub CopyCell() Range("A1").Copy Destination:=Range("D1") End Sub
Les codes ci-dessus copieront et colleront la valeur ainsi que le formatage/les formules qu'il contient.
Comme vous l'avez peut-être déjà remarqué, le code ci-dessus copie la cellule sans la sélectionner. Peu importe où vous vous trouvez sur la feuille de calcul, le code copiera la cellule A1 et la collera sur D1.
Notez également que le code ci-dessus écraserait tout code existant dans la cellule D2. Si vous souhaitez qu'Excel vous indique s'il y a déjà quelque chose dans la cellule D1 sans l'écraser, vous pouvez utiliser le code ci-dessous.
Sub CopyCell() If Range("D1") "" Then Response = MsgBox("Voulez-vous écraser les données existantes", vbYesNo) End If If Response = vbYes Then Range("A1").Copy Range("D1 ") Fin si fin sous
Copie d'une plage de taille fixe
Si vous souhaitez copier A1:D20 dans J1:M20, vous pouvez utiliser le code ci-dessous :
Sub CopyRange() Range("A1:D20").Copy Range("J1") End Sub
Dans la cellule de destination, il vous suffit de spécifier l'adresse de la cellule en haut à gauche. Le code copierait automatiquement la plage copiée exacte dans la destination.
Vous pouvez utiliser la même construction pour copier des données d'une feuille à l'autre.
Le code ci-dessous copierait A1:D20 de la feuille active vers Sheet2.
Sub CopyRange() Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
Ce qui précède copie les données de la feuille active. Assurez-vous donc que la feuille contenant les données est la feuille active avant d'exécuter le code. Pour plus de sécurité, vous pouvez également spécifier le nom de la feuille de calcul lors de la copie des données.
Sub CopyRange() Worksheets("Sheet1").Range("A1:D20").Copy Worksheets("Sheet2").Range("A1") End Sub
La bonne chose à propos du code ci-dessus est que quelle que soit la feuille active, elle copiera toujours les données de Sheet1 et les collera dans Sheet2.
Vous pouvez également copier une plage nommée en utilisant son nom au lieu de la référence.
Par exemple, si vous avez une plage nommée appelée "SalesData", vous pouvez utiliser le code ci-dessous pour copier ces données dans Sheet2.
Sub CopyRange() Range("SalesData").Copy Worksheets("Sheet2").Range("A1") End Sub
Si la portée de la plage nommée correspond à l'intégralité du classeur, vous n'avez pas besoin d'être sur la feuille contenant la plage nommée pour exécuter ce code. Étant donné que la plage nommée est limitée au classeur, vous pouvez y accéder à partir de n'importe quelle feuille à l'aide de ce code.
Si vous avez une table avec le nom Table1, vous pouvez utiliser le code ci-dessous pour la copier dans Sheet2.
Sub CopyTable() Range("Table1[#All]").Copy Worksheets("Sheet2").Range("A1") End Sub
Vous pouvez également copier une plage dans un autre classeur.
Dans l'exemple suivant, je copie le tableau Excel (Table1) dans le classeur Book2.
Sub CopyCurrentRegion() Range("Table1[#All]").Copy Workbooks("Book2.xlsx").Worksheets("Sheet1").Range("A1") End Sub
Ce code ne fonctionnerait que si le classeur est déjà ouvert.
Copie d'une plage de taille variable
Une façon de copier des plages de taille variable consiste à les convertir en plages nommées ou en tableau Excel et à utiliser les codes comme indiqué dans la section précédente.
Mais si vous ne pouvez pas le faire, vous pouvez utiliser la propriété CurrentRegion ou End de l'objet range.
Le code ci-dessous copierait la région actuelle dans la feuille active et la collerait dans Sheet2.
Sub CopyCurrentRegion() Range("A1").CurrentRegion.Copy Worksheets("Sheet2").Range("A1") End Sub
Si vous souhaitez copier la première colonne de votre ensemble de données jusqu'à la dernière cellule remplie et la coller dans Sheet2, vous pouvez utiliser le code ci-dessous :
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown)).Copy Worksheets("Sheet2").Range("A1") End Sub
Si vous souhaitez copier les lignes ainsi que les colonnes, vous pouvez utiliser le code ci-dessous :
Sub CopyCurrentRegion() Range("A1", Range("A1").End(xlDown).End(xlToRight)).Copy Worksheets("Sheet2").Range("A1") End Sub
Notez que tous ces codes ne sélectionnent pas les cellules lors de leur exécution. En général, vous ne trouverez qu'une poignée de cas où vous devez réellement sélectionner une cellule/plage avant de travailler dessus.
Affectation de plages aux variables d'objet
Jusqu'à présent, nous avons utilisé l'adresse complète des cellules (telles que Workbooks ("Book2.xlsx").Worksheets ("Sheet1").Range ("A1")).
Pour rendre votre code plus gérable, vous pouvez affecter ces plages à des variables d'objet, puis utiliser ces variables.
Par exemple, dans le code ci-dessous, j'ai attribué la plage source et la plage de destination à des variables d'objet, puis j'ai utilisé ces variables pour copier des données d'une plage à l'autre.
Sub CopyRange() Dim SourceRange As Range Dim DestinationRange As Range Set SourceRange = Worksheets("Sheet1").Range("A1:D20") Set DestinationRange = Worksheets("Sheet2").Range("A1") SourceRange.Copy DestinationRange Fin du sous-marin
Nous commençons par déclarer les variables en tant qu'objets Range. Ensuite, nous affectons la plage à ces variables à l'aide de l'instruction Set. Une fois que la plage a été affectée à la variable, vous pouvez simplement utiliser la variable.
Entrez des données dans la cellule vide suivante (à l'aide de la zone de saisie)
Vous pouvez utiliser les zones de saisie pour permettre à l'utilisateur de saisir les données.
Par exemple, supposons que vous ayez l'ensemble de données ci-dessous et que vous souhaitiez saisir l'enregistrement des ventes, vous pouvez utiliser la zone de saisie dans VBA. À l'aide d'un code, nous pouvons nous assurer qu'il remplit les données de la prochaine ligne vide.
Sub EnterData() Dim RefRange As Range Set RefRange = Range("A1").End(xlDown).Offset(1, 0) Set ProductCategory = RefRange.Offset(0, 1) Set Quantity = RefRange.Offset(0, 2 ) Set Amount = RefRange.Offset(0, 3) RefRange.Value = RefRange.Offset(-1, 0).Value + 1 ProductCategory.Value = InputBox("Product Category") Quantity.Value = InputBox("Quantity") Amount.Value = InputBox("Amount") End Sub
Le code ci-dessus utilise la zone d'entrée VBA pour obtenir les entrées de l'utilisateur, puis entre les entrées dans les cellules spécifiées.
Notez que nous n'avons pas utilisé de références de cellules exactes. Au lieu de cela, nous avons utilisé la propriété End and Offset pour trouver la dernière cellule vide et remplir les données qu'elle contient.
Ce code est loin d'être utilisable. Par exemple, si vous entrez une chaîne de texte lorsque la zone de saisie demande une quantité ou un montant, vous remarquerez qu'Excel le permet. Vous pouvez utiliser une condition If pour vérifier si la valeur est numérique ou non, puis l'autoriser en conséquence.
Boucle dans les cellules/plages
Jusqu'à présent, nous avons vu comment sélectionner, copier et saisir les données dans les cellules et les plages.
Dans cette section, nous verrons comment parcourir un ensemble de cellules/lignes/colonnes dans une plage. Cela peut être utile lorsque vous souhaitez analyser chaque cellule et effectuer une action en fonction de celle-ci.
Par exemple, si vous souhaitez mettre en surbrillance chaque troisième ligne de la sélection, vous devez parcourir et vérifier le numéro de ligne. De même, si vous souhaitez mettre en évidence toutes les cellules négatives en changeant la couleur de la police en rouge, vous devez parcourir et analyser la valeur de chaque cellule.
Voici le code qui parcourra les lignes des cellules sélectionnées et mettra en évidence les lignes alternatives.
Sub HighlightAlternateRows() Dim Myrange As Range Dim Myrow As Range Set Myrange = Sélection pour chaque Myrow dans Myrange.Rows If Myrow.Row Mod 2 = 0 Then Myrow.Interior.Color = vbCyan End If Next Myrow End Sub
Le code ci-dessus utilise la fonction MOD pour vérifier le numéro de ligne dans la sélection. Si le numéro de ligne est pair, il est mis en surbrillance en couleur cyan.
Voici un autre exemple où le code parcourt chaque cellule et met en évidence les cellules qui contiennent une valeur négative.
Sub HighlightAlternateRows() Dim Myrange As Range Dim Mycell As Range Set Myrange = Sélection pour chaque Mycell In Myrange If Mycell < 0 Then Mycell.Interior.Color = vbRed End If Next Mycell End Sub
Notez que vous pouvez faire la même chose en utilisant la mise en forme conditionnelle (qui est dynamique et une meilleure façon de le faire). Cet exemple est uniquement dans le but de vous montrer comment fonctionne la boucle avec des cellules et des plages en VBA.
Où mettre le code VBA
Vous vous demandez où va le code VBA dans votre classeur Excel ?
Excel a un backend VBA appelé éditeur VBA. Vous devez copier et coller le code dans la fenêtre de code du module VB Editor.
Voici les étapes à suivre :
- Allez dans l'onglet Développeur.
- Cliquez sur l'option Visual Basic. Cela ouvrira l'éditeur VB dans le backend.
- Dans le volet Explorateur de projets de l'éditeur VB, cliquez avec le bouton droit sur n'importe quel objet du classeur dans lequel vous souhaitez insérer le code.Si vous ne voyez pas l'Explorateur de projets, accédez à l'onglet Affichage et cliquez sur Explorateur de projets.
- Allez dans Insérer et cliquez sur Module. Cela insérera un objet module pour votre classeur.
- Copiez et collez le code dans la fenêtre du module.
