Les fichiers exemples associés à cette page (zippés) sont à télécharger ici (téléchargé 1942 fois).

Introduction

Vous avez commencé à mettre les doigts dans VBA et vous avez donc très vite rencontré l'objet RANGE. Ces quelques lignes ont pour objectif de vous le rendre moins mystérieux et de vous indiquer les différentes façons de le manipuler.

Pour sélectionner la cellule A19 en VBA, on ne peut pas écrire directement A19.select. VBA ne sait en effet pas à priori ce qu'est ce A19 qui pourrait être le nom d'une variable (encore qu'il soit fortement déconseillé d'utiliser des noms de variables pouvant être confondus avec des références de cellules). Il faut donc dire à VBA qu'on parle bien d'une plage de cellules. On écrit donc : Range("A19"). Attention aux guillemets qui sont indispensables.

La macro ci-dessous sélectionne la cellule A19:

Sub range1()
	Range("A19").Select
End Sub

"Range" se traduit en français par "plage" ou "étendue". Par exemple, on utilisera le mot range en anglais pour définir une plage de température comprises entre 8 et 18°C.

Propriétés et méthodes

Comme à tout objet, des propriétés et des méthodes sont associées à l'objet range. Par exemple on peut modifier la couleur du fond : 

Sub range2()
	Range("A19").Interior.ColorIndex = 36
End Sub

La propriété par défaut de l'objet range est sa valeur (value). Si la cellule A1 contient "Salut !", 

MaVariable = range("A1")
'est équivalent à 
'MaVariable = Range("A1").value

et renvoie Salut !.

Sub range3()
	'Msgbox Range("A19").value
	'est équivalent à
	MsgBox Range("A19")
End Sub

Dans l'éditeur VBA, lorsque vous écrivez "range("A19").", au moment ou vous tapez le point, un menu déroulant s'ouvre pour vous proposer les méthodes et propriétés disponibles pour l'objet range spécifié. L'explorateur d'objet et l'aide en ligne vous donnent des explications et exemples d'utilisation de ces propriétés et méthodes.

NB : la notion de range est volontairement simplifiée ici en premier abord. Pour les puristes, précisons donc les points suivants :
La propriété Range s'applique à deux types d'objets :

  • Un objet worksheet. Celui-ci (la feuille excel) contient une collection d'objets comme les lignes, colonnes, tableaux croisés dynamiques (PivotTable)... auxquels sont associés des propriétés et méthodes, dont la propriété range.
    Ecrire Range("A19").ClearContents
    est en fait un raccourci pour worksheet("MaFeuille").Range("A19").ClearContents si on travaille sur la feuille MaFeuille.
    Le nom de la feuille peut être indiqué soit par le nom de la feuille tel qu'il apparait dans l'onglet (MaFeuille), soit par son rang dans la collection worksheets : worksheets(1).Range("A19").ClearContents

    Si on ne spécifie pas l'un ou l'autre, excel sous entend que l'on travaille sur la feuille active.

    Range("A19").ClearContents 
est donc en réalité équivalent à
ActiveSheet.Range("A19").ClearContents.

    Il est vivement conseillé de préciser le nom de la feuille dans vos macros pour éviter tout problème, notamment si vous travaillez alternativement sur plusieurs feuilles. Vous pouvez utiliser l'une ou l'autre de ces deux écritures : 

    Worksheets("feuil1").range("A19") 
ou
range("Feuil1!A19")
  • Un objet range. Et oui... range est aussi une propriété de l'objet range. nous verrons ceci un peu plus tard.

Définir une plage de cellules

L'objet range peut se référer à une cellule : range ("A1"), ou à plusieurs : range("A1:C27") fait référence au rectangle délimité par les cellules A1-A27-C27-A27. 

Sub range4()
	Range("A19:C26").Interior.ColorIndex = 37
End Sub
L'utilisation de la méthode offset (=décalage en français) permet de sélectionner une cellule par rapport à une autre. Par exemple, si vous voulez colorier la cellule située 3 lignes plus bas et une colonne à droite de la cellule A19, vous écrirez : 
Sub range5()
	Range("A19").Offset(3, 1).Interior.ColorIndex = 3
End Sub
Offset a besoin de deux paramètres : le décalage vertical, et le décalage horizontal. Des valeurs positives indiquent respectivement un décalage vers le bas ou vers la droite, des valeurs négatives un décalage vers le haut ou vers la gauche. Si votre objet range est une plage de cellules (A19:C23) le décalage sera calculé par rapport à la cellule de référence de cette plage qui est toujours la cellule en haut à gauche de la plage, donc ici A19.

Définir des plages variables

On ne sait pas toujours l'adresse des cellules définissant la plage au moment où l'on écrit la macro.
Il est fréquent par exemple que l'on veuille travailler sur toutes les cellules non vides d'une région de la feuille qui sera remplie au fur et à mesure des besoins. Cette macro par exemple permet de travailler sur la dernière cellule de la colonne A : 
Sub range6()
	Range("A65536").End(xlUp).Interior.ColorIndex = 38
End Sub
et celle-ci sur toutes les cellules comprises entre la cellule A19 et la dernière cellule non vide de la colonne A : 
Sub range7()
	Range(Range("A19"), Range("A65000").End(xlUp)).Interior.ColorIndex = 35
End Sub
Cette écriture utilise deux objets range pour définir la première et la dernière cellule d'un objet range qui les inclut. Elle est lourde et peut être avantageusement remplacée par celle-ci, dans laquelle les crochets droits sont équivalents à l'instruction "evaluate" 
Sub range7bis()
	Range("A19", [A65000].End(xlUp)).Interior.ColorIndex = 35
End Sub
Ce classeur d'Alain Vallon vous fournit de nombreux exemples qui vous aideront à repérer la cellule la plus à droite, en haut, en bas... de vos plages de données suivant ce que vous souhaitez utiliser comme " dernière cellule" à inclure dans le range.

Union et intersection de range

Pour effectuer une opération sur deux plages de cellules discontinues il faut les unir : 
Sub range8()
	Union(Range("A19:A24"), Range("C22:C26")).Interior.ColorIndex = 40
End Sub
Si vous voulez travailler sur l'intersection entre deux plages utilisez l'espace comme opérateur d'intersection : 
Sub range9()
	Range("A19:A24 A22:C26").Interior.ColorIndex = 49
End Sub
Attention à la position des guillemets et de l'espace !

Utiliser des noms définis dans un range

Via le menu insertion/nom/définir, on peut attribuer à nom à des plages de cellules, que celles-ci soient continues ou discontinues. L'utilisation de ces noms définis est à recommander chaudement. En effet, cela rend la lecture et donc le débugage du code bien plus aisés. Par ailleurs, si dans votre macro vous faites référence à range("B12") puis que vous supprimez la colonne A, votre macro travaillera sur l'actuelle cellule B12 (précédemment en C12) et non sur celle que vous aviez initialement repéré. En revanche, si vous avez nommé B12 "macellule" la référence à cette cellule sera modifiée (et deviendra A12) au moment ou vous supprimerez la colonne A.
Sélectionnez la plage A19:A24. Dans le menu insertion/nom/définir, la référence à cette zone apparaît dans la zone de formule. Entrez MaPlage dans la zone de nom en haut puis cliquez sur ajouter.
Travailler avec un nom défini dans un range est très simple : il vous suffit d'indiquer ce nom entre guillemets. 
Sub range10()
	Range("MaPlage").Interior.ColorIndex = 5
End Sub
Si votre plage de référence est amenée à s'agrandir par ajout de nouvelles données, vous pouvez avantageusement la nommer en utilisant l'instruction décaler. La référence à cette plage s'adaptera ainsi automatiquement à vos nouvelles données. Attention, il ne doit pas y avoir de trous entre vos données. Dans le menu insertion/nom/définir, inscrivez en haut le nom de votre plage (MaPlageDynamique par exemple) et dans la zone du bas, inscrivez la formule adhoc. Pour faire référence aux cellules situées entre la cellule A19 et la dernière remplie de cette colonne, votre formule sera par exemple :
=decaler($A$19;;;nbval($A:$A))
Cliquez sur ajouter. Placez le curseur dans la zone de formule et cliquez. La plage définie par la formule est alors entourée d'un pointillé qui vous permet de vérifier que votre formule est correcte. Des explications détaillées sur cette indispensable fonction d'excel sont disponibles notamment sur la page lexique. Une plage nommée de façon dynamique s'utilise de la même façon qu'une plage nommée normale : 
Sub range11()
	Range("MaPlageDynamique").Interior.ColorIndex = 6
End Sub

Parcourir un range dans une macro

Il faut noter que dans une macro VBA, les paramètres d'un range peuvent être remplacées par une variable. Par exemple, vous pouvez utiliser une variable pour indiquer le N° de la ligne. C'est en particulier très utile pour parcourir une plage de cellules et y appliquer un traitement : 
Sub range12()
	a = 10
	For i = 19 To 24
		Range("A" & i).Interior.ColorIndex = a
		a = a + 5
	Next i
End Sub

Eviter de sélectionner inutilement

Lorsqu'on ne connaît pas une instruction VBA, il est très pratique d'utiliser l'enregistreur de macros. Par exemple, si vous ne savez pas quel est le code à utiliser pour mettre le texte en rouge, le centrer et mettre une bordure épaisse, lancez l'enregistreur de macro, et faites cette mise en forme. Vous obtiendrez ceci : 
Sub Macro1()
'
' Macro1 Macro
' Macro enregistrée le 14/07/2007 par Florence Cabon
'

'
    Range("A19").Select
    Selection.Font.ColorIndex = 3
    Selection.Borders(xlDiagonalDown).LineStyle = xlNone
    Selection.Borders(xlDiagonalUp).LineStyle = xlNone
    With Selection.Borders(xlEdgeLeft)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeTop)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeBottom)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection.Borders(xlEdgeRight)
        .LineStyle = xlContinuous
        .Weight = xlMedium
        .ColorIndex = xlAutomatic
    End With
    With Selection
        .HorizontalAlignment = xlCenter
        .VerticalAlignment = xlBottom
        .WrapText = False
        .Orientation = 0
        .AddIndent = False
        .IndentLevel = 0
        .ShrinkToFit = False
        .ReadingOrder = xlContext
        .MergeCells = False
    End With
End Sub
Il est tentant mais très inefficace de conserver ce code : l'enregistreur de macro est très bavard et en général on peut élaguer sans problème la majorité du code, notamment quand il indique la valeur de chacun des paramètres possible pour une propriété. Mais ici c'est même beaucoup plus gênant : il n'est pas du tout nécessaire de sélectionner un objet range pour le modifier. Chaque sélection ralentit l'exécution de la macro. Sur une toute petite macro ceci ne se sentira pas mais si ces sélections intempestives sont répétées ceci peut devenir redhibitoire. La macro ci-dessus sera très efficacement ramenée à ces quelques lignes de code : 
Sub range13()
	With Range("A19")
		.Font.ColorIndex = 3
		.HorizontalAlignment = xlCenter
		.Borders.Weight = xlMedium
	End With
End Sub
C'est mieux non ?

La propriété range d'un objet range

Pour terminer quelques lignes sur cet oiseau exotique parfois très pratique.
Vous pouvez trouver dans des macros ce genre de code : 
Sub range14()
	For each c in range("A19:A24")
		c.range("B2:D4").interior.colorIndex=3
	next c
End Sub
Dans ce cas, on balaie la plage A19:A24, et c prend successivement la valeur range("A19"), puis range("A20"), puis range("A21") .... Comment la machine interprète-t-elle range("A22").range("B2:D4") ? Il s'agit d'une (très intéressante) référence relative qui "équivaut à un décalage", qui peut-être remplacé, dans certains cas, par la propriété Resize. Pour comprendre comment ceci fonctionne, il suffit de regarder ce qui se passe pour c=A1 (même si cette valeur n'est pas inclue dans la plage balayée dans notre exemple).
a) B2 est située 1 cellule à droite et une cellule vers le bas par rapport à A1. La plage renvoyée commencera toujours avec ce décalage. Donc B2 pour la cellule A1 et Z30 si on partait de Y29.
b) D4 est située 3 cellules à droite et 4 cellules plus bas que A1.
On prend maintenant la plage que l'on balaye (A19:A24), on applique à la première cellule le décalage calculé au a) : celle-ci devient donc B20. Puis on applique le décalage calculé en b) à la dernière cellule de la plage de départ : A24 devient D27.
La plage coloriée par notre macro sera donc la plage B20:D27.
Dans de nombreux cas il est plus simple d'utiliser la fonction offset pour calculer ce décalage mais cependant cette utilisation d'un range.range peut être parfois pratique. En effet, on peut tout à fait utiliser un nom défini à la place de range("B2:D4"). De telle sorte que si l'on change la plage à laquelle ce nom se réfère, le décalage changera. Les adeptes de l'utilisation de la fonction decaler pour définir dynamiquement cette plage verront tout de suite l'avantage de cette méthode ! 
Sub range15()
    For Each c In Range("A19:A24")
        c.Range("MonDecalage").Interior.ColorIndex = 3
    Next c
End Sub

Cette page a été vue 10350 fois.