Plages et cellules en VBA
Les feuilles de calcul Excel stockent les données dans les cellules. Les cellules sont organisées en lignes et en colonnes. Chaque cellule peut être identifiée par le point d'intersection de sa ligne et de sa colonne (Exs. B3 ou R3C2).
Une plage Excel fait référence à une ou plusieurs cellules (ex. A3:B4)
Adresse de la cellule
Notation A1
En notation A1, une cellule est désignée par sa lettre de colonne (de A à XFD) suivie de son numéro de ligne (de 1 à 1 048 576).
En VBA, vous pouvez faire référence à n'importe quelle cellule en utilisant le Objet de plage.
| 123456789 | ' Reportez-vous à la cellule B4 de la feuille actuellement activePlage MsgBox("B4")' Référez-vous à la cellule B4 sur la feuille nommée 'Données'MsgBox Worksheets("Données").Range("B4")' Référez-vous à la cellule B4 sur la feuille nommée 'Données' dans un autre classeur OUVERT' nommé 'Mes données'MsgBox Workbooks("Mes données").Worksheets("Data").Range("B4") |
Notation R1C1
Dans la notation R1C1, une cellule est référencée par R suivi du numéro de ligne, puis de la lettre « C » suivie du numéro de colonne. par exemple B4 dans la notation R1C1 sera référencé par R4C2. En VBA, vous utilisez le Objet Cellules pour utiliser la notation R1C1 :
| 12 | ' Se référer à la cellule R[6]C[4] c'est-à-dire D6Cellules (6, 4) = "D6" |
Gamme de cellules
Notation A1
Pour faire référence à plusieurs cellules, utilisez un « : » entre l'adresse de la cellule de départ et la dernière adresse de la cellule. Ce qui suit fera référence à toutes les cellules de A1 à D10 :
| 1 | Plage ("A1:D10") |
Notation R1C1
Pour faire référence à plus d'une cellule, utilisez un "," entre l'adresse de la cellule de départ et la dernière adresse de la cellule. Ce qui suit fera référence à toutes les cellules de A1 à D10 :
| 1 | Plage (cellules (1, 1), cellules (10, 4)) |
Écriture dans les cellules
Pour écrire des valeurs dans une cellule ou un groupe contigu de cellules, il suffit de se référer à la plage, de mettre un signe =, puis d'écrire la valeur à stocker :
| 12345678910 | ' Stocker F5 dans la cellule avec l'adresse F6Plage("F6") = "F6"' Stocker E6 dans la cellule avec l'adresse R[6]C[5] c'est-à-dire E6Cellules (6, 5) = "E6"' Stocker A1:D10 dans la plage A1:D10Plage("A1:D10") = "A1:D10"' ouPlage (Cellules (1, 1), Cellules (10, 4)) = "A1:D10" |
Lecture à partir de cellules
Pour lire les valeurs des cellules, référez-vous simplement à la variable pour stocker les valeurs, mettez un signe = puis référez-vous à la plage à lire :
| 1234567891011 | Dim val1Dim val2' Lire à partir de la cellule F6val1 = Plage("F6")' Lire à partir de la cellule E6val2 = Cellules (6, 5)MsgBox val1Msgbox val2 |
Remarque : Pour stocker les valeurs d'une plage de cellules, vous devez utiliser un tableau au lieu d'une simple variable.
Cellules non contiguës
Pour faire référence à des cellules non contiguës, utilisez une virgule entre les adresses des cellules :
| 123456 | ' Stockez 10 dans les cellules A1, A3 et A5Plage("A1,A3,A5") = 10' Stockez 10 dans les cellules A1:A3 et D1:D3)Plage ("A1:A3, D1:D3") = 10 |
Intersection de cellules
Pour faire référence à des cellules non contiguës, utilisez un espace entre les adresses des cellules :
| 123 | ' Mémoriser 'Col D' dans D1:D10' qui est commun entre A1:D10 et D1:F10Plage("A1:D10 D1:G10") = "Col D" |
Décalage à partir d'une cellule ou d'une plage
En utilisant la fonction Offset, vous pouvez déplacer la référence d'une plage donnée (cellule ou groupe de cellules) par le nombre_de_lignes et le nombre_de_colonnes spécifiés.
Syntaxe de décalage
Range.Offset(number_of_rows, number_of_columns)
Décalage à partir d'une cellule
| 12345678910111213141516 | ' OFFSET à partir d'une cellule A1' Se référer à la cellule elle-même' Déplacer 0 lignes et 0 colonnesPlage ("A1").Décalage(0, 0) = "A1"' Déplacer 1 lignes et 0 colonnesPlage ("A1").Décalage(1, 0) = "A2"' Déplacer 0 lignes et 1 colonnesPlage("A1").Décalage(0, 1) = "B1"' Déplacer 1 lignes et 1 colonnesPlage("A1").Décalage(1, 1) = "B2"' Déplacer 10 lignes et 5 colonnesPlage ("A1").Décalage (10, 5) = "F11" |
Décalage à partir d'une plage
| 123 | ' Déplacer la référence vers la plage A1:D4 de 4 lignes et 4 colonnes' La nouvelle référence est E5:H8Plage("A1:D4").Décalage(4,4) = "E5:H8" |
Définition d'une référence à une plage
Pour affecter une plage à une variable plage : déclarez une variable de type Plage puis utilisez la commande Set pour la définir sur une plage. Veuillez noter que vous devez utiliser la commande SET car RANGE est un objet :
| 12345678 | ' Déclarer une variable RangeDim myRange en tant que plage' Réglez la variable sur la plage A1:D4Définir maPlage = Plage("A1:D4")' Imprime $A$1:$D$4MsgBox myRange.Address |
Redimensionner une plage
La méthode de redimensionnement de l'objet Range modifie la dimension de la plage de référence :
| 1234567 | Dim myRange As Range' Plage à redimensionnerDéfinir maPlage = Plage("A1:F4")' Imprime $A$1:$E$10Debug.Print myRange.Resize (10, 5).Adresse |
La cellule en haut à gauche de la plage redimensionnée est la même que la cellule en haut à gauche de la plage d'origine
Syntaxe de redimensionnement
Range.Resize(number_of_rows, number_of_columns)
DÉCALAGE vs redimensionnement
Le décalage ne modifie pas les dimensions de la plage mais la déplace du nombre spécifié de lignes et de colonnes. Le redimensionnement ne modifie pas la position de la plage d'origine mais modifie les dimensions selon le nombre spécifié de lignes et de colonnes.
Toutes les cellules de la feuille
L'objet Cells fait référence à toutes les cellules de la feuille (1048576 lignes et 16384 colonnes).
| 12 | ' Effacer toutes les cellules dans les feuilles de calculCellules.Effacer |
Plage d'occasion
La propriété UsedRange vous donne la plage rectangulaire de la cellule utilisée en haut à gauche à la cellule utilisée en bas à droite de la feuille active.
| 1234567 | Dim ws As Feuille de calculDéfinir ws = ActiveSheet' $B$2:$L$14 si L2 est la première cellule avec n'importe quelle valeur' et L14 est la dernière cellule avec n'importe quelle valeur sur le' feuille activeDebug.Print ws.UsedRange.Address |
Région actuelle
La propriété CurrentRegion vous donne la plage rectangulaire contiguë de la cellule en haut à gauche à la cellule utilisée en bas à droite contenant la cellule/plage référencée.
| 1234567891011 | Dim myRange As RangeDéfinir maPlage = Plage("D4:F6")' Imprime $B$2 :$L$14' S'il y a un chemin rempli de D4:F16 à B2 ET L14Debug.Print myRange.CurrentRegion.Address' Vous pouvez également vous référer à une seule cellule de départSet myRange = Range("D4") ' Imprime $B$2:$L$14 |
Propriétés de la plage
Vous pouvez obtenir l'adresse, le numéro de ligne/colonne d'une cellule et le nombre de lignes/colonnes dans une plage comme indiqué ci-dessous :
| 123456789101112131415161718192021 | Dim myRange As RangeDéfinir maPlage = Plage("A1:F10")' Imprime $A$1:$F$10Debug.Print myRange.AddressDéfinir maPlage = Plage("F10")' Imprime 10 pour la ligne 10Debug.Print myRange.Row' Imprime 6 pour la colonne FDebug.Print myRange.ColumnDéfinir maPlage = Plage("E1:F5")' Imprime 5 pour le nombre de lignes dans la plageDebug.Print myRange.Rows.Count' Imprime 2 pour le nombre de colonnes dans la plageDebug.Print myRange.Columns.Count |
Dernière cellule de la feuille
Vous pouvez utiliser Nombre de lignes et Colonnes.Nombre propriétés avec Cellules objet pour obtenir la dernière cellule de la feuille :
| 1234567891011 | ' Imprimer le dernier numéro de ligne' Imprime 1048576Debug.Print "Lignes dans la feuille : " & Rows.Count' Imprimer le dernier numéro de colonne' Imprime 16384Debug.Print "Colonnes dans la feuille : " & Columns.Count' Imprimer l'adresse de la dernière cellule' Imprime $XFD$1048576Debug.Print "Adresse de la dernière cellule de la feuille : " & Cells(Rows.Count, Columns.Count) |
Dernier numéro de ligne utilisé dans une colonne
La propriété END vous amène à la dernière cellule de la plage et End(xlUp) vous amène à la première cellule utilisée à partir de cette cellule.
| 123 | Dim lastRow As LonglastRow = Cells(Rows.Count, "A").End(xlUp).Row |
Dernier numéro de colonne utilisé dans une ligne
| 123 | Dim lastCol As LonglastCol = Cells(1, Columns.Count).End(xlToLeft).Column |
La propriété END vous emmène à la dernière cellule de la plage et End(xlToLeft) vous emmène à gauche de la première cellule utilisée à partir de cette cellule.
Vous pouvez également utiliser les propriétés xlDown et xlToRight pour accéder aux premières cellules utilisées en bas ou à droite de la cellule actuelle.
Propriétés de la cellule
Propriétés communes
Voici le code pour afficher les propriétés de cellule couramment utilisées
| 12345678910111213141516171819202122 | Cellule tamisée en tant que plageDéfinir la cellule = Plage("A1")cellule.ActiverDebug.Print cell.Address' Imprimer $A$1Debug.Print cell.Value' Impressions 456' AdresseDebug.Print cell.Formula' Imprime =SOMME(C2:C3)' CommenterDebug.Print cell.Comment.Text' StyleDebug.Print cell.Style' Format de celluleDebug.Print cell.DisplayFormat.NumberFormat |
Police de cellule
L'objet Cell.Font contient les propriétés de la police de cellule :
| 1234567891011121314151617181920 | Cellule tamisée en tant que plageDéfinir la cellule = Plage("A1")' Régulier, Italique, Gras et Gras Italiquecell.Font.FontStyle = "Gras italique"' Pareil quecell.Font.Bold = Truecell.Font.Italic = True' Définir la police sur Couriercell.Font.FontStyle = "Courrier"' Définir la couleur de la policecell.Font.Color = vbBlue' oucell.Font.Color = RVB (255, 0, 0)' Définir la taille de la policecell.Font.Size = 20 |
Copier et coller
Coller tout
Les plages/cellules peuvent être copiées et collées d'un emplacement à un autre. Le code suivant copie toutes les propriétés de la plage source vers la plage de destination (équivalent à CTRL-C et CTRL-V)
| 1234567 | 'Copie simplePlage ("A1:D20").CopierFeuilles de calcul("Feuille2").Plage("B10").Coller'ou' Copie de la feuille actuelle vers la feuille nommée 'Feuille2'Plage("A1:D20").Destination de la copie :=Feuilles de calcul("Feuille2").Plage("B10") |
Pâte Spéciale
Les propriétés sélectionnées de la plage source peuvent être copiées vers la destination en utilisant l'option PASTESPECIAL :
| 123 | ' Coller la plage en tant que valeurs uniquementPlage ("A1:D20").CopierWorksheets("Sheet2").Range("B10").PasteSpecial Paste:=xlPasteValues |
Voici les options possibles pour l'option Coller :
| 12345678910111213 | ' Coller les types spéciauxxlCollerToutxlCollerTousSaufBorduresxlCollerToutFusionnerFormatsConditionnelsxlPasteAllUsingSourceThemexlCollerColonneLargeursxlCollerCommentairesxlCollerFormatsxlCollerFormulesxlCollerFormulesEtNombreFormatsxlCollerValidationxlCollerValeursxlCollerValeursEtNombreFormats |
Contenu de l'ajustement automatique
La taille des lignes et des colonnes peut être modifiée pour s'adapter au contenu à l'aide du code ci-dessous :
| 12345 | ' Modifier la taille des lignes 1 à 5 pour s'adapter au contenuRangées("1:5").Ajustement automatique' Modifier la taille des colonnes A en B pour s'adapter au contenuColonnes("A:B").Ajustement automatique |
Plus d'exemples de gamme
Il est recommandé d'utiliser l'enregistreur de macros lors de l'exécution de l'action requise via l'interface graphique. Il vous aidera à comprendre les différentes options disponibles et comment les utiliser.
Pour chaque
Il est plus facile de parcourir une plage en utilisant Pour chaque construire comme indiqué ci-dessous :
| 123 | Pour chaque cellule dans la plage ("A1:B100")' Faire quelque chose avec la celluleCellule suivante |
À chaque itération de la boucle, une cellule de la plage est affectée à la variable c et les instructions de la boucle For sont exécutées pour cette cellule. La boucle se termine lorsque toutes les cellules sont traitées.
Sorte
Sort est une méthode de l'objet Range. Vous pouvez trier une plage en spécifiant des options de tri sur Range.Sort. Le code ci-dessous triera les colonnes A:C en fonction de la clé de la cellule C2. L'ordre de tri peut être xlAscending ou xlDescending. Header:= xlYes doit être utilisé si la première ligne est la ligne d'en-tête.
| 12 | Colonnes("A:C").Clé de tri1:=Plage("C2"), _order1:=xlAscending, Header:=xlYes |
Trouve
Find est également une méthode de Range Object. Il trouve la première cellule dont le contenu correspond aux critères de recherche et renvoie la cellule en tant qu'objet Range. ça revient Rien s'il n'y a pas de correspondance.
Utilisation RechercherSuivant (ou FindPrevious) pour rechercher l'occurrence suivante (précédente).
Le code suivant changera la police en "Arial Black" pour toutes les cellules de la plage commençant par "John":
| 12345 | Pour chaque c dans la plage ("A1:A100")Si c Comme "John*" Alorsc.Font.Name = "Arial Noir"Fin sic suivant |
Le code suivant remplacera toutes les occurrences de « À tester » par « Réussi » dans la plage spécifiée :
| 12345678910 | Avec plage ("a1:a500")Définir c = .Find("À tester", LookIn:=xlValues)Si non c'est rien alorsfirstaddress = c.AdresseFairec.Valeur = "Réussi"Définir c = .FindNext(c)Boucle tant que non c n'est rien et c.Address firstaddressFin siTerminer par |
Il est important de noter que vous devez spécifier une plage pour utiliser FindNext. Vous devez également fournir une condition d'arrêt, sinon la boucle s'exécutera pour toujours. Normalement, l'adresse de la première cellule trouvée est stockée dans une variable et la boucle est arrêtée lorsque vous atteignez à nouveau cette cellule. Vous devez également vérifier le cas où rien n'est trouvé pour arrêter la boucle.
Plage d'adresses
Utilisez Range.Address pour obtenir l'adresse dans le style A1
| 123 | MsgBox Range ("A1:D10").Adresse' ouDebug.Print Range ("A1:D10").Adresse |
Utilisez xlReferenceStyle (la valeur par défaut est xlA1) pour obtenir les adresses dans le style R1C1
| 123 | MsgBox Range ("A1:D10").Address(ReferenceStyle:=xlR1C1)' ouDebug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1) |
Ceci est utile lorsque vous traitez des plages stockées dans des variables et que vous souhaitez traiter uniquement certaines adresses.
Plage à tableau
Il est plus rapide et plus facile de transférer une plage vers un tableau, puis de traiter les valeurs. Vous devez déclarer le tableau comme Variant pour éviter de calculer la taille requise pour remplir la plage dans le tableau. Les dimensions du tableau sont définies pour correspondre au nombre de valeurs de la plage.
| 123456789 | Dim DirArray en tant que variante' Stocke les valeurs de la plage dans le tableauDirArray = Range("a1:a5").Valeur' Boucle pour traiter les valeursPour chaque c dans DirArrayDebug.Print cProchain |
Tableau à plage
Après le traitement, vous pouvez réécrire le tableau dans une plage. Pour écrire le tableau de l'exemple ci-dessus dans une plage, vous devez spécifier une plage dont la taille correspond au nombre d'éléments dans le tableau.
Utilisez le code ci-dessous pour écrire le tableau dans la plage D1:D5 :
| 123 | Plage("D1:D5").Valeur = DirArrayRange("D1:H1").Valeur = Application.Transpose(DirArray) |
Veuillez noter que vous devez transposer le tableau si vous l'écrivez sur une ligne.
Plage de somme
| 12 | SumOfRange = Application.WorksheetFunction.Sum(Range("A1:A10"))Debug.Print SumOfRange |
Vous pouvez utiliser de nombreuses fonctions disponibles dans Excel dans votre code VBA en spécifiant Application.WorkSheetFunction. avant le nom de la fonction comme dans l'exemple ci-dessus.
Plage de comptage
| 1234567 | ' Compter le nombre de cellules avec des nombres dans la plageCountOfCells = Application.WorksheetFunction.Count(Range("A1:A10"))Debug.Print CountOfCells' Compter le nombre de cellules non vides dans la plageCountOfNonBlankCells = Application.WorksheetFunction.CountA(Range("A1:A10"))Debug.Print CountOfNonBlankCells |
Écrit par : Vinamra Chandra
