VBA – Plages et Cellules Excel

Plages et cellules en VBA

Les feuilles de calcul Excel stockent les données dans des cellules. Les cellules sont organisées en rangées et en colonnes. Chaque cellule peut être identifiée par le point d’intersection de sa ligne et de sa colonne (par exemple, B3 ou R3C2).

Une plage Excel fait référence à une ou plusieurs cellules (ex. A3:B4)

Adresse de Cellule

Notation A1

En notation A1, une cellule est désignée par la lettre de sa colonne (de A à XFD) suivie du numéro de sa ligne (de 1 à 1 048 576). C’est ce qu’on appelle l’adresse de la cellule.

En VBA, vous pouvez vous référer à n’importe quelle cellule en utilisant l’objet Range.

'Référence à la cellule B4 de la feuille active
MsgBox Range("B4")

'Référence à la cellule B4 de la feuille nommée 'Données'
MsgBox Worksheets("Données").Range("B4")

'Référence à la cellule B4 de la feuille nommée 'Données' dans un autre classeur ouvert
'nommé 'Mes données'
MsgBox Workbooks("Mes données").Worksheets("Données").Range("B4")

Notation R1C1

Dans la notation R1C1, une cellule est désigné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 désigné par R4C2. En VBA, vous utilisez l’objet Cells pour utiliser la notation R1C1 :

' Référence à la cellule R[6]C[4], c'est-à-dire D6
Cells(6, 4) = "D6"

Plage de Cellules

Notation A1

Pour faire référence à plusieurs cellules, utilisez un « : » entre l’adresse de la cellule de départ et celle de la dernière cellule. L’exemple suivant fait référence à toutes les cellules de A1 à D10 :

Range("A1:D10")

Notation R1C1

Pour faire référence à plusieurs cellules, utilisez un « , » entre l’adresse de la cellule de départ et celle de la dernière cellule. L’exemple suivant fait référence à toutes les cellules de A1 à D10 :

Range(Cells(1, 1), Cells(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 :

' Écrire F6 dans la cellule avec l'adresse F6
Range("F6") = "F6"

' Écrire E6 dans la cellule avec l'adresse R[6]C[5] c'est à dire E6
Cells(6, 5) = "E6"

' Écrire A1:D10 dans la plage A1:D10
Range("A1:D10") = "A1:D10"
' ou
Range(Cells(1, 1), Cells(10, 4)) = "A1:D10"

Lecture à Partir de Cellules

Pour lire les valeurs contenues dans des cellules, il suffit de faire référence à la variable dans laquelle sont stockées les valeurs, de mettre le signe =, puis de faire référence à la plage à lire :

Dim val1
Dim val2

' Lecture de la cellule F6
val1 = Range("F6")

' Lecture de la cellule E6
val2 = Cells(6, 5)

MsgBox val1
MsgBox 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 :

'Écrire 10 dans les cellules A1, A3 et A5
Range("A1,A3,A5") = 10


'Écrire 10 dans les cellules A1:A3 et D1:D3) 
Range("A1:A3, D1:D3") = 10

Intersection de Cellules

Pour faire référence à une intersection de cellules entre deux plages, utilisez un espace entre les adresses des plages :

' Écrire 'Col D' dans D1:D10
' qui est commune entre A1:D10 et D1:F10
Range("A1:D10 D1:G10") = "Col D"

Décalage à Partir d’une Cellule ou d’une Plage

La fonction Offset permet de déplacer la référence d’une plage donnée (cellule ou groupe de cellules) en fonction du nombre de rangées et du nombre de colonnes spécifiés.

Syntaxe de la Fonction Offset

Range.Offset(nombre_de_lignes, nombre_de_colonnes)

Décalage à Partir d’une Cellule

' DÉCÉLAGE à partir de la cellule A1
' Référence à la cellule elle-même
' Déplacez 0 ligne et 0 colonne
Range("A1").Offset(0, 0) = "A1"

' Déplacer de 1 ligne et 0 colonne
Range("A1").Offset(1, 0) = "A2"

' Déplacer de 0 ligne et 1 colonne
Range("A1").Offset(0, 1) = "B1"

' Déplacer de 1 ligne et 1 colonne
Range("A1").Offset(1, 1) = "B2"

' Déplacer de 10 lignes et 5 colonnes
Range("A1").Offset(10, 5) = "F11"

Décalage à Partir d’une Plage

' Déplacer la référence vers la plage A1:D4 de 4 lignes et 4 colonnes
' La nouvelle référence est E5:H8
Range("A1:D4").Offset(4,4) = "E5:H8"

Définition d’une Référence à une Plage

Pour affecter une plage à une variable de plage : déclarez une variable de type Range puis utilisez la commande Set pour l’affecter à une plage. Veuillez noter que vous devez utiliser la commande SET car RANGE est un objet :

' Déclarez une variable Range
Dim maPlage as Range

' Définissez la variable sur la plage A1:D4
Set maPlage = Range("A1:D4")

' Imprime $A$1:$D$4
MsgBox maPlage.Address

Redimensionnement d’une Plage

La méthode Resize de l’objet Range modifie la dimension de la plage de référence :

Dim maPlage As Range

' Plage à redimensionner
Set maPlage = Range("A1:F4")

' Imprime $A$1:$E$10
Debug.Print maPlage.Resize(10, 5).Address

La cellule supérieure gauche de la plage redimensionnée est identique à la cellule supérieure gauche de la plage d’origine.

Syntaxe de la Fonction Resize

Plage.Resize(nombre_de_lignes, nombre_de_colonnes)

Offset vs Resize

Le décalage ne modifie pas les dimensions de la plage mais la déplace du nombre de lignes et de colonnes spécifié. Le redimensionnement ne change pas la position de la plage d’origine mais modifie ses dimensions en fonction du nombre de lignes et de colonnes spécifié.

Toutes les Cellules de la Feuille

L’objet Cells fait référence à toutes les cellules de la feuille (1048576 lignes et 16384 colonnes).

'Effacer toutes les cellules d'une feuille de calcul
Cells.Clear

UsedRange

La propriété UsedRange vous donne la plage rectangulaire allant de la première cellule utilisée en haut à gauche à la dernière cellule utilisée en bas à droite de la feuille active.

Dim fc As Worksheet
Set fc = ActiveSheet

' $B$2:$L$14 si L2 est la première cellule ayant une valeur quelconque 
' et L14 est la dernière cellule ayant une valeur quelconque sur la
' feuille active
Debug.Print fc.UsedRange.Address

CurrentRegion

La propriété CurrentRegion vous donne la plage rectangulaire contiguë, de la cellule supérieure gauche à la cellule utilisée inférieure droite, contenant la cellule/plage référencée.

Dim maPlage As Range

Set maPlage = Range("D4:F6")

' Imprime $B$2:$L$14
' S'il existe un chemin rempli de D4:F16 à B2 ET L14
Debug.Print maPlage.CurrentRegion.Address

' Vous pouvez également vous référer à une seule cellule de départ

Set maPlage = Range("D4") ' Imprime $B$2:$L$14

Propriétés de l’Objet Range

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 :

Dim maPlage As Range

Set maPlage = Range("A1:F10")

'Imprime $A$1:$F$10
Debug.Print maPlage.Address

Set maPlage = Range("F10")

' Imprime 10 pour la rangée 10
Debug.Print maPlage.Row

' Imprime 6 pour la colonne F
Debug.Print maRange.Column

Set maPlage = Range("E1:F5")
' Imprime 5 pour le nombre de lignes de la plage
Debug.Print maRange.Rows.Count

' Imprime 2 pour le nombre de colonnes de la plage
Debug.Print maPlage.Columns.Count

Dernière Cellule de la Feuille

Vous pouvez utiliser les propriétés Rows.Count et Columns.Count avec l’objet Cells pour obtenir la dernière cellule de la feuille :

' Imprime le numéro de la dernière ligne
' Imprime 1048576
Debug.Print "Rangées dans la feuille : " & Rows.Count

' Imprimez le dernier numéro de colonne
' Imprime 16384
Debug.Print "Colonnes dans la feuille : " & Columns.Count

' Imprimez l'adresse de la dernière cellule
' Imprime $XFD$1048576
Debug.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.

Dim dernièreRangée As Long

dernièreRangée = Cells(Rows.Count, 1).End(xlUp).Row

Dernier Numéro de Colonne Utilisé dans une Rangée

Dim dernièreCol As Long

dernièreCol = Cells(1, Columns.Count).End(xlToLeft).Column

La propriété END vous amène à la dernière cellule de la plage, et End(xlToLeft) vous amène à gauche à la première cellule utilisée à partir de cette cellule.

Vous pouvez également utiliser les propriétés xlDown et xlToRight pour vous diriger vers les premières cellules utilisées en bas ou à droite de la cellule actuelle.

Propriétés des Cellules

Propriétés Courantes

Voici un code permettant d’afficher les propriétés de cellule les plus courantes

Dim cell As Range
Set cell = Range("A1")

cell.Activate
Debug.Print cell.Address
'Imprime $A$1

Debug.Print cell.Value
' Imprime 456
' Adresse

Debug.Print cell.Formula
' Imprime =SUM(C2:C3)

' Commentaires
Debug.Print cell.Comment.Text

' Style
Debug.Print cell.Style

' Format de la cellule
Debug.Print cell.DisplayFormat.NumberFormat

Police de Cellule

L’objetCell.Font contient les propriétés de la police de la cellule :

Dim cell As Range

Set cell = Range("A1")

' Régulier, Italique, Gras et Gras Italique peuvent être définis
' Les mots clés anglais suivants doivent être utilisés:
' Regular, Italic, Bold, et Bold Italic
cell.Font.FontStyle = "Bold Italic"  'Gras Italique
' Identique à
cell.Font.Bold = True
cell.Font.Italic = True

' Définir la police sur Courier
cell.Font.FontStyle = "Courier"

' Définir la couleur de la police
cell.Font.Color = vbBlue
' ou
cell.Font.Color = RGB(255, 0, 0)

' Définir la taille de la police
cell.Font.Size = 20

Copier et Coller

Coller tout

Les plages/cellules peuvent être copiées et collées d’un endroit à un autre. Le code suivant copie toutes les propriétés de la plage source vers la plage destination (équivalent à CTRL-C et CTRL-V)

' Copie simple
Range("A1:D20").Copy 
Worksheets("Sheet2").Range("B10").Paste

' ou
' Copie de la feuille courante vers la feuille nommée 'Feuil2'
Range("A1:D20").Copy destination:=Worksheets("Feuil2").Range("B10")

Collage Spécial

Les propriétés sélectionnées de la plage source peuvent être copiées vers la destination en utilisant l’option PASTESPECIAL:

' Coller la plage en tant que Valeurs uniquement
Range("A1:D20").Copy
Worksheets("Feuil2").Range("B10").PasteSpecial Paste:=xlPasteValues

Voici les options possibles pour l’option Paste :

' Types de Collage Spéciaux
xlPasteAll
xlPasteAllExceptBorders
xlPasteAllMergingConditionalFormats
xlPasteAllUsingSourceTheme
xlPasteColumnWidths (largeur des colonnes)
xlPasteComments
xlPasteFormats
xlPasteFormules
xlPasteFormulasAndNumberFormats
xlPasteValidation
xlPasteValues
xlPasteValuesAndNumberFormats

Fonction AutoFit Contents

La taille des lignes et des colonnes peut être modifiée pour s’adapter au contenu à l’aide de la fonction AutoFit:

' Modifiez la taille des rangées 1 à 5 pour les adapter au contenu 
Rows("1:5").AutoFit

' Modifiez la taille des colonnes A à B pour les adapter au contenu 
Columns("A:B").AutoFit

Autres Exemples avec les Objets Plages

Il est recommandé d’utiliser l’enregistreur de macros tout en effectuant l’action requise via l’interface graphique. Cela vous aidera à comprendre les différentes options disponibles et la façon de les utiliser.

Boucle For Each

Il est facile de boucler une plage en utilisant la boucle For Each comme indiqué ci-dessous :

For Each cell In Range("A1:B100")
    ' Faites quelque chose avec la cellule
Next cell

À chaque itération de la boucle, une cellule de la plage est affectée à la variable cell 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.

Sort

Sort est une méthode de l’objet Range. Vous pouvez trier une plage en spécifiant les options de tri dans Range.Sort. Le code ci-dessous permet de trier les colonnes A:C en fonction d’une clé dans 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.

  Columns("A:C").Sort key1:=Range("C2"), _
      order1:=xlAscending, Header:=xlYes

Find

Find est également une méthode de l’objet Range. Elle trouve la première cellule dont le contenu correspond aux critères de recherche et renvoie la cellule sous forme d’objet Range. Elle ne renvoie rien s’il n’y a pas de correspondance.

Utilisez la méthode FindNext (ou FindPrevious) pour trouver l’occurrence suivante (précédente).

Le code suivant changera la police en « Arial Black » pour toutes les cellules de la plage qui commencent par « John » :

For Each c in Range("A1:A100")
    if c Like "John*" Then
        c.Font.Name = "Arial Black"
    End If
Next c

Le code suivant remplacera toutes les occurrences de « À Tester » par « Réussi » dans la plage spécifiée :

With Range("a1:a500")
    Set c = .Find("À Tester", LookIn:=xlValues)
    If Not c Is Nothing Then
        premièreAdresse = c.Address
        Do
            c.Value = "Réussi"
            Set c = .FindNext(c)
        Loop While Not c Is Nothing And c.Address <> premièreAdresse
    End If
End With

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 indéfiniment. Normalement, l’adresse de la première cellule trouvée est stockée dans une variable et la boucle s’arrête lorsque vous atteignez à nouveau cette cellule. Vous devez également vérifier le cas où rien n’est trouvé pour arrêter la boucle.

Adresse d’une Plage

Utilisez Range.Address pour obtenir l’adresse dans le style A1

MsgBox Range("A1:D10").Address
'ou
Debug.Print Range("A1:D10").Address

Utilisez xlReferenceStyle (par défaut xlA1) pour obtenir les adresses dans le style R1C1

MsgBox Range("A1:D10").Address(ReferenceStyle:=xlR1C1)
' ou
Debug.Print Range("A1:D10").Address(ReferenceStyle:=xlR1C1) 

Cette méthode est utile lorsque vous traitez des plages stockées dans des variables et que vous souhaitez traiter certaines adresses uniquement.

Stocker une Plage dans un Tableau

Il est plus rapide et plus facile de transférer une plage dans un tableau, puis de traiter les valeurs. Vous devez déclarer le tableau en tant que Variant pour éviter de calculer la taille nécessaire pour remplir la plage dans le tableau. Les dimensions du tableau sont définies pour correspondre au nombre de valeurs de la plage.

Dim DirTableau As Variant
' Enregistrez les valeurs de la plage dans le tableau

DirTableau = Range("A1:A5").Value

' Boucle pour traiter les valeurs
For Each c In DirTableau 
    Debug.Print c
Next c

Stocker les Valeurs d’un Tableau dans une 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 du tableau. Utilisez le code ci-dessous pour écrire le tableau dans la plage D1:D5 :

Range("D1:D5").Value = DirTableau

Range("D1:H1").Value = Application.Transpose(DirTableau)

Veuillez noter que vous devez transposer le tableau si vous l’écrivez dans une ligne.

Somme d’une Plage

SommeDePlage = Application.WorksheetFunction.Sum(Range("A1:A10"))
Debug.Print SommeDePlage

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. Le nom anglais des fonctions doit être utilisé dans VBA même si Excel est configuré dans une langue différente sur votre système.

Compter une Plage

' Comptez le nombre de cellules contenant des nombres dans la plage
NbDeCellules = Application.WorksheetFunction.Count(Range("A1:A10"))
Debug.Print NbDeCellules 

' Comptez le nombre de cellules non vides dans la plage
NbDeCellulesNonVides = Application.WorksheetFunction.CountA(Range("A1:A10"))
Debug.Print NbDeCellulesNonVides

Écrit par : Vinamra Chandra