Guide du développeur pour l'objet de plage Excel 2010 (traduction automatique)

La plupart des tâches plus courantes de procéder à l'aide du modèle objet Excel impliquent l'utilisation des plages de cellule. L'objet Range représente une ou plusieurs cellules et peut être utilisé pour représenter une seule cellule, une ligne, une colonne, une sélection de cellules qui contiennent un ou plusieurs blocs contigus de cellules ou une plage 3D.

Cet Visual article explique comment effectuer les tâches suivantes :

• Insérer des valeurs dans une plage

• Reportez-vous à la plage

• Sélectionner des plages

• Combiner et d'intersection des plages

• Utilisation des plages nommée

• Copie et déplacement des plages

• Boucle de plages

• Rechercher dans une plage

Ce Visual How To repose sur Developers Guide à l'objet de plage Excel 2007 (éventuellement en anglais) par Frank Rice. Cet article contient des informations supplémentaires et des exemples de code, et généralement les informations de Excel 2007 sont valides pour Excel 2010.

La propriété Range (éventuellement en anglais) renvoie un objet Range. La syntaxe de la propriété Range peut prendre deux formes suivantes.

expression.Range(cell1)

expression.Range(cell1, cell2)

Dans la première forme, cell1 identifie la plage d'une seule cellule ; dans le second formulaire, les valeurs cell1 et cell2 représentent la plage de cellules contiguës. Dans ces exemples, expression est un élément obligatoire doit être un objet Application, un objet Range (éventuellement en anglais), ou un objet Worksheet (éventuellement en anglais) (ou une expression qui renvoie un de ces objets). S'il est omis, il est supposé pour être l'objet ActiveSheet (éventuellement en anglais). Dans le deuxième exemple, le séparateur de cellule peut être l'opérateur de plage (deux-points), l'opérateur d'intersection (espace) ou l'opérateur d'union (virgule).

Dans les sections suivantes, vous allez découvrir de nombreuses opérations que vous pouvez faire avec les plages Excel. Toutefois, ces exemples sont uniquement un sous-ensemble des choses que vous pouvez accomplir avec des tranches. Pour plus d'informations sur les membres du modèle Range Object, consultez Range Object Members (éventuellement en anglais).

Insertion de valeurs dans une plage

Insertion de valeurs dans une seule cellule dans une plage est simple. L'exemple suivant insère la valeur 3.14159 dans la cellule A1.

Worksheets("Sheet1").Range("A1").Value = 3.14159

De même, vous pouvez utiliser une cellule nommée au lieu de A1. Une plage nommée possède un nom unique, expose des événements et peut être liée aux données. Les plages nommées sont un outil puissant dans Excel vous permet d'attribuer un nom significatif à une seule cellule ou une plage de cellules. Par exemple, vous pouvez attribuer le nom « TaxeVentes » à la cellule C1 et puis utiliser le nom « TaxeVentes » chaque fois que vous utiliseriez normalement la cellule C1.

Worksheets("Sheet1").Range("SalesTax").Value = 0.095

L'exemple suivant insère une valeur unique dans une plage de cellules.

Worksheets("Sheet1").Range("A1:B10").Value = 1

À l'aide de la deuxième forme de la propriété Range décrite plus haut dans cette section, vous pouvez accomplir le même résultat avec la ligne de code suivante. N'oubliez pas que la référence d'objet est omise afin que la valeur par défaut de la feuille active. En outre, une propriété de l'objet Range a été omis, la propriété Value est supposée et a la valeur 1.

Range("A1", "B10") = 1

Là encore, en utilisant le deuxième formulaire, l'instruction suivante place la valeur « xyz » dans les cellules A1 et A3 A5 dans la feuille Sheet2.

Worksheets("Sheet2").Range("A1, A3, A5") = "XYZ"

La syntaxe est similaire pour les formules. L'exemple de code suivant définit la formule de A1 égale à la valeur aléatoire à partir de 0 à 10.

Worksheets("Sheet1").Range("A1").Formula = "=10*RAND()"

L'exemple suivant parcourt D1:D10 de cellules sur Sheet1. Si une des cellules a la valeur String égale à « À vendre », le code remplace cette valeur avec la chaîne « Vendu ».

For Each c in Worksheets("Sheet1").Range("D1:D10")
   If c.Value = "For Sale" Then 
      c.Value = "Sold"
   End If 
Next c

Référence à des plages

Il existe plusieurs façons de faire référence à une plage :

• Par adresse de cellule

• En tant qu'offset à partir d'une autre cellule

• Par nom

• La sélection en cours

Il existe deux façons de faire référence à une plage directement en utilisant les adresses de cellule : vous pouvez utiliser des références absolues à l'aide de l'objet ActiveSheet, ou vous pouvez utiliser des références relatives à l'aide de l'objet ActiveCell ou un autre objet de la plage.

Référence à des plages de feuilles de calcul

En règle générale, vous faites référence à une plage de la feuille de calcul active. Toutefois, vous pouvez facilement faire à une plage dans un classeur inactif. Par exemple, vous faites référence à une plage sur le ActiveSheet à l'aide de la propriété de Range comme dans les éléments suivants, qui sélectionne les cellules B1 à B15.

Range("B1:B15").Select

Pour faire référence à la même plage de cellules dans une feuille de calcul autre que la feuille de calcul active, vous spécifiez le nom de la feuille de calcul ou un index.

Worksheets(2). Range("B1:B15").Select

Sans spécifier la feuille de calcul, mais uniquement si la plage contient un nom unique, vous pouvez également faire référence à une plage sur une feuille de calcul inactif. Cette technique fonctionne indépendamment de la feuille de calcul contient la plage nommée se trouve.

Range("SalesTax").Select

Faisant référence à des cellules dans une plage

Pour faire référence à des plages individuelles dans une plage, sachez qu'ils sont référencés à partir du coin supérieur gauche. Donc si nous définissons une plage à B4 à E10, puis la cellule A1 dans la plage est réellement B4 et la cellule B2 dans la plage est C5, comme illustré dans l'exemple de code suivant.

Dim myRange As Range
Set myRange = Range("B4:E10")
myRange.Range("A1").Value = 100000
myRange.Range("B2").Value = 200000

Référence à des cellules dans une plage en utilisant des décalages

La propriété Offset renvoie un objet Range et permet de faire référence à une cellule par rapport à une autre cellule en utilisant la syntaxe suivante.

object.Offset(rowOffSet, columnOffset)

En spécifiant le nombre de colonnes et lignes de décalage d'à partir d'un emplacement spécifique, vous pouvez faire référence à la cellule. Les nombres positifs déplacement le focus vers le bas et vers la droite. Les nombres négatifs déplacement le focus vers le haut et gauche. Zéro fait référence à la cellule active.

Par exemple, supposons que E5 est la cellule active. Cette instruction place 1 une ligne sous E5 dans la cellule E6.

ActiveCell.Offset(1,0) = 1

Cette instruction place 1 une colonne vers la droite d'E5 dans la cellule F5.

ActiveCell.Offset(0,1) = 1

Cette instruction place 1 trois colonnes vers la gauche d'E5 dans la cellule B5.

ActiveCell.Offset(0,-3) = 1

L'exemple de code suivant calcule une moyenne mobile à l'aide de la propriété Offset. Le code fonctionne en définissant la plage en tant que les trois premières valeurs dans la colonne B, divisant la somme de ces valeurs par 3 et puis en insérant la moyenne dans la cellule C3. Ensuite, la propriété Offset la plage descend d'une ligne, toujours dans la colonne b et calcule la moyenne des valeurs dans la plage de B2:B4. Cette valeur est insérée dans la cellule C4. Ce processus est répété par l'intermédiaire de la cellule B12.

Dim rng As Range
Dim lngRow As Long
Set rng = Range("B1:B3")
    
For lngRow = 3 To 12
    Cells(lngRow, "C").Value = WorksheetFunction.Sum(rng) / 3
    Set rng = rng.Offset(1, 0)
Next lngRow

Référence à des cellules dans une plage nommée

Une plage nommée possède un nom unique, expose des événements et peut être liée aux données. Excel stocke les noms des plages définies dans la collection Names, qui est une propriété de l'objet Workbook. Les plages nommées sont un outil puissant dans Excel qui vous permettent d'attribuer un nom significatif à une seule cellule ou une plage de cellules. Par exemple, vous pouvez attribuer le nom « TaxeVentes » à la cellule C1 et puis utiliser le nom « TaxeVentes » lorsque vous utiliseriez normalement la cellule C1, comme dans l'exemple de code suivant.

Total = Range("A1") * Range("SalesTax")

Les plages nommées sont décrits plus en détail plus loin dans cet article.

Sélectionner des plages de cellules

Il n'y a aucun objet « ActiveRange » avec laquelle faire référence à la plage active, mais plusieurs autres façons de travailler avec une gamme : la méthode Select permet de sélectionner une plage de cellules. Une fois la plage est sélectionnée, vous pouvez utiliser l'objet Selection pour contrôler les actions dans la sélection de cellules. Dans cet exemple, la propriété CurrentRegion de l'objet ActiveCell est utilisée pour sélectionner la plage, la cellule D1 dans cette instance et copiez le contenu du Presse-papiers. Une nouvelle feuille est ajoutée après la dernière feuille et le contenu de la cellule est copié vers la nouvelle feuille. (La propriété CurrentRegion est décrite plus en détail plus loin.)

Range("D5").Activate
ActiveCell.CurrentRegion.Select
Selection.Copy
Sheets.Add After:=Sheets(Sheets.Count)
Sheets(Sheets.Count).Name = "Sample"
Sheets("Sample").Select
Range("D5").Activate
ActiveSheet.Paste

Vous pouvez également effectuer d'autres opérations sur une plage sélectionnée de cellules telles que la modification du type de police en gras, comme dans l'exemple de code suivant.

Selection.Font.Bold = True

Vous pouvez utiliser la propriété End de l'objet Range pour demander à Excel de direction pour étendre la portée. Les énumérations sont utilisées décrivent les directions pour étendre la portée. L'objet Range retourné par la méthode End représente la cellule à la fin de la région contenant la plage source. À l'aide de la méthode End équivaut à appuyer sur fin + haut, fin + bas, fin + gauche ou fin + droite. Cet exemple sélectionne la cellule en haut de la colonne b dans la région contenant la cellule B4.

Range("B4").End(xlUp).Select

Si vous modifiez l'énumération soit xlToRight, elle va sélectionner la cellule à la fin de la ligne 4 dans la région contenant la cellule B4.

Range("B4").End(xlToRight).Select

L'exemple de code suivant étend la sélection à partir de la cellule B4 à la dernière cellule de la ligne 4 qui contient des données.

Range("B4", Range("B4").End(xlToRight)).Select

Combinant des plages et la vérification de chevauchement de plages

Vous pouvez utiliser la méthode Union de l'objet Application pour combiner des plages multizones, c'est-à-dire, des plages composées de deux ou plusieurs blocs contigus de cellules. L'exemple suivant crée un objet qui est défini comme l'union des plages A1:B2 et C3:D4, puis sélectionne la plage nouvellement définie.

Dim rng1 As Range, rng2 As Range, myMultiRanges As Range
Worksheets("Sheet1").Activate
Set rng1 = Range("A1:B2")
Set rng2 = Range("C3:D4")
Set myMultiRanges = Union(rng1, rng2)
myMultiRanges.Select

La méthode Intersect (Intersect(range1, range2)) renvoie un objet Range qui représente l'intersection rectangulaire de deux plages ou plus. Si les plages ne se croisent pas, la méthode retourne le mot clé Nothing. L'exemple de code suivant sélectionne l'intersection de deux plages nommées, range1 et range2, sur Sheet1. Si les plages ne se croisent pas, l'exemple affiche un message.

Worksheets("Sheet1").Activate 
Set interSect = Application.Intersect(Range("range1"), Range("range2")) 
If interSect Is Nothing Then 
   MsgBox "The ranges do not intersect." 
Else 
   interSect.Select 
End If

Plages nommées

Comme décrit précédemment, nom des plages vous permet de fournir un nom complet pour une plage de cellules adjacentes. Les plages nommées sont la méthode préférée de référence à des blocs de cellules étant à l'aide de Range("Sales") plus informatif que l'utilisation de Range("D1:D15"). Un nom de plage peut contenir des lettres, chiffres et des traits de soulignement, mais pas espaces ou caractères de ponctuation spécial. Les plages nommées ne peuvent dépasser 255 caractères.

Définir une plage nommée

Pour créer une plage nommée dans le classeur, spécifiez un nom et la plage de cellules à laquelle il doit pointer. Pour ce faire, utilisez la méthode Add de la collection Names.

ThisWorkbook.Names.Add Name:="Home_Sales", RefersTo:="=$A$1:$E$15", Visible:=True

Noms de plage réservée

Il existe certains noms de plages sont réservées pour une utilisation par Excel et qui ne sont pas disponibles pour nommer vos propres plages. Citons notamment Consolidate_Area, Print_Titles, Auto_Open, Recorder, Auto_Close, Data_Form, Extract, Auto_Activate, Database, Auto_Deactivate, Criteria, Sheet_Title et Print_Area.

Toutefois, même si vous ne pouvez pas utiliser ces noms pour définir des plages personnalisées à vos besoins, cela ne signifie pas que vous ne pouvez pas les utiliser. Par exemple, en utilisant le nom de Print_Area, vous pouvez spécifier quelle plage de cellules à imprimer. En outre, la plage de Print_Area vous permet de définir la plage de cellules en utilisant le choix de la notation A1, la notation R1C1 ou noms de plage. L'exemple suivant définit la plage de cellules à imprimer à A1:F15.

Sheet1.Names.Add "Print_Area", Sheet1.Range("A1:F15")

Vous pouvez supprimer la plage de la zone d'impression avec la ligne de code qui utilise la méthode Delete.

Sheet1.Names("Print_Area").Delete

Copie des plages

Les sections suivantes décrivent les moyens de copier des cellules et des plages.

Copie des plages de cellules

Les copies de l'exemple suivant de la cellule A1 à la cellule B1 dans la feuille de calcul active. Sachez que vous n'avez pas à sélectionner la plage avant de le copier. Si vous choisissez de ne sélectionner la plage, la feuille de calcul doit être active. Vous pouvez définir la feuille de calcul active en utilisant la méthode Activate de la collection Worksheets.

Range("A1").Copy Range("B1")

Pour copier une plage dans une feuille de calcul autre que la feuille de calcul active, seulement qualifier la référence de plage.

Workbooks("File.xls").Sheets("Sheet1").Range("A1").Copy _
    Workbooks("File.xls").Sheets("Sheet2").Range("A1")

Vous pouvez copier plusieurs cellules de la même façon. Dans l'exemple suivant, A1 finiront étant le coin supérieur gauche de la destination.

Workbooks("File.xls").Sheets("Sheet1").Range("B1:C5").Copy _
    Workbooks("File.xls").Sheets("Sheet2").Range("A1")

Vous pouvez déplacer une plage au lieu de le copier à l'aide de la méthode Cut.

Range("A1").Cut Range("B1")

Copie des plages de taille Variable

Dans de nombreux cas, vous ne connaissez pas la taille exacte de la plage que vous souhaitez copier ou déplacer. Par exemple, vous pouvez suivre les stocks hebdomadaire et le nombre de lignes varie en fonction de vos ventes pour la semaine. Pour gérer ce genre de situation, utilisez la propriété CurrentRegion. Cette propriété renvoie un objet Range qui fait référence aux cellules qui entourent une cellule particulière.

En règle générale, la propriété CurrentRegion se compose d'un bloc rectangulaire de cellules entourée par (mais non compris) un ou plusieurs lignes vides ou des colonnes ou par les bords de la feuille de calcul. Par exemple, si vous aviez une table de données dans les cellules D2 à E15 et que le focus était dans la cellule D2, la propriété CurrentRegion renvoie un objet Range qui représente les cellules D2 à E15.

L'exemple de code suivant copie la plage de cellules qui entourent la cellule A1 de la feuille Sheet1 à la plage correspondante dans la feuille Sheet2.

Range("A1").CurrentRegion.Copy Sheets("Sheet2").Range("A1")

Copie de données entre baies et plages

À l'aide de tableaux VBA, vous pouvez accéder les valeurs des cellules individuelles, déplacement des données entre la feuille de calcul et le code VBA. Pour ce faire, créez un type de données Variant et affecter la plage à ce type de Variant. Vous pouvez ensuite accéder les valeurs des cellules individuelles comme vous le feriez avec n'importe quel élément du tableau. L'exemple de code suivant copie la plage B1:B10 dans un type de Variant et affiche ensuite la valeur d'une seule cellule. Notez qu'une plage retourne toujours un tableau multidimensionnel d'au moins deux dimensions, une des lignes et une des colonnes, voire plus selon le nombre de colonnes dans la plage.

Dim varA As Variant
Set varA = Range("B1:B10")
MsgBox (varA(3))

Parcours des plages

Une tâche courante pour les développeurs est une boucle sur une plage de valeurs et effectuer une action sur une cellule ou les cellules de la plage. Pour ce faire, le plus simple consiste à l'aide d'un formulaire de la boucle For...Next. Cet exemple montre comment exécuter une boucle sur une plage de cellules nommée et, selon leur valeur, définit la couleur de police à l'aide de la propriété Cells de l'objet Range, en spécifiant les valeurs de ligne et de colonne. Sachez qu'il utilise les boucles imbriquées For…Next, une des lignes et une pour les colonnes.

Dim rngSales As Range
Dim lngRow As Long, lngColumn As Long
Set rngSales = Range("SalesData")
   
For lngRow = 1 To rngSales.Rows.Count
    For lngColumn = 1 To rngSales.Columns.Count
        If rngSales.Cells(lngRow, lngColumn).Value < 100 Then
            rngSales.Cells(lngRow, lngColumn).Font.ColorIndex = 3
        Else
            rngSales.Cells(lngRow, lngColumn).Font.ColorIndex = 1
        End If
        
    Next lngColumn
Next lngRow

Voici un autre exemple qui montre comment effectuer une boucle sur les lignes d'une plage. Dans cet exemple de code, les lignes sont mises en forme en gras en fonction de la première cellule de la ligne supérieure à une valeur de 1000.

Dim rngRow As Range
' Bold rows where total over 1000.
For Each rngRow In Range("Data").Rows
    If rngRow.Cells(1).Value > 1000 Then
        rngRow.Font.Bold = True
    Else
        rngRow.Font.Bold = False
    End If    
Next rngRow

Recherche dans les plages

Cette section décrit comment faire pour rechercher une valeur dans une plage de données en utilisant la méthode Find. L'exemple suivant recherche la chaîne « Jun » dans une plage de valeurs et s'il est trouvé, le code copie la ligne de données associées à cette valeur et le colle dans un autre emplacement ; Sinon, il affiche un message à l'utilisateur. Être conscient des paramètres associés à la méthode Find qui spécifient les éléments à rechercher, que nous sommes mise en correspondance avec l'ensemble et que nous sommes correspondance avec les valeurs de cellule.

Dim rng As Range
Set rng = Range("A1:A12").Find(What:="Jun", _
              LookAt:=xlWhole, LookIn:=xlValues)
If rng Is Nothing Then
    MsgBox "Data not found"
Else
    rng.Resize(1, 3).Copy Destination:=Range("G1")
End If

L'article de riz (Guide du développeur à l'objet de plage Excel 2007 (éventuellement en anglais)) contient des informations supplémentaires sur l'objet Range. Elle comprend :

• Comment utiliser la propriété End pour les colonnes de la somme des valeurs.

• L'existence d'une plage nommée dans une feuille de calcul.

• Comment faire pour supprimer le nom d'une plage nommée.

• Comment effectuer une recherche dans toutes les feuilles de calcul d'un classeur en utilisant la méthode Find.

Regardez la vidéo (éventuellement en anglais) | Longueur : 00 : 15 : 36

• Guide du développeur à l'objet de plage Excel 2007 (éventuellement en anglais)

• mise en route avec le développement de VBA dans Office 2010 (éventuellement en anglais)

• mise en route avec VBA dans Excel 2010

• Guide du développeur à l'objet d'Application Excel 2007 (éventuellement en anglais)

À propos de l’auteur

Peter Gruenbaum

  Peter gruenbaum (éventuellement en anglais), a commencé comme un physicien, mais est devenu un développeur de logiciels travaillant sur des technologies aussi diversifiés que Tablet PC, de réalité augmentée, de conception assistée par ordinateur et de simulation CHIRURGICALE. Il a fondé SDK Bridge LLC (éventuellement en anglais) de réunir son amour de la technologie et d'écriture, où il a écrit et enseigne sur la technologie.