Ecriture et déplacement dans les cellules La procédure qui suit, toute simple,
Sub Test()
Cells(4, 2) = "Tralala"
End Sub
Ecrit Tralala dans la 4ème ligne, 2ème colonne de la feuille courante du classeur courant. En d'autres mots dans la cellule B4.
En fait, Cells est un membre de Application, mais lorsqu'il s'agit de l'objet Application, nul besoin de le préciser. La procédure suivante fait exactement la même chose :
Sub Test()
Application.Cells(4, 2) = "Tralala"
End Sub
Nous aurions pu également préciser que Tralala est bien le CONTENU de la cellule, en effet, si nous avions écrit GREEN à la place de Tralala, on pourrait imaginer qu'Excel allait reconnaître qu'il s'agit non pas d'écrire GREEN, mais de mettre le texte, ou même la couleur de fond de la cellule en Vert. Le code suivant donne encore une fois exactement le même résultat :
Sub Test()
Application.Cells(4, 2).Value = "Tralala"
End Sub
En fait, la propriété Value ne doit pas obligatoirement se préciser, car Excel imagine (intelligemment) que nous voulons nous occuper du contenu.
Ainsi, grâce à cette méthode, nous avons la possibilité de remplir une grande zone, avec l'instruction de programmation For To Next, comme ceci :
Sub Test()
Dim Ctr As Integer
For Ctr = 1 To 10
Cells(Ctr, 1) = "ca marche"
Next
End Sub
Qui nous permet de remplir les cellules de A1 jusqu'à A10 avec le texte "ca marche"
Si vous avez un peu de peine avec la logique pure et dure de programmation (Structures If .. Then .. Else, For .. To .. Next, Do .. Loop, etc.), qui finalement n'ont pas grand chose à voir avec Excel, je vous conseille de commencer par le commencement, et cliquer ici pour avoir les notions de programmation de base.
Une autre manière de placer du texte ou du chiffre dans une cellule particuière est la méthode Range :
Application.Range("A5").Value = "Ca marche"
Et, comme plus haut, nous pouvons omettre le Mot Application. En fait, Application veut simplement dire que c'est l'application courante (Excel) qui reçoit les instructions
Range("A5").Value = "Ca marche"
Ainsi que Value.
Range("A5") = "Ca marche"
La même technique nous permet de remplir une plage de cellules plus simplement qu'avec For To Next :
Range("A5:A10") = "Ca marche"
De cette manière, il est possible d'attribuer une valeur à une cellule particulière qui fait partie d'une plage de cellule, sans recourir à ActiveCell. Dans l'exemple qui précède, nous attribuons la même valeur à plusieurs cellules, mais voici la manière d'attribuer une valeur à la 3ème cellule d'une plage :
Range("A5:A10")(3) = "Trois"
Ecrit Trois dans la 3ème cellule a partir de la première cellule sélectionnée. La première est donc A5, la 2ème A6, et la 3ème A7. Excel écrit donc dans A7.
De cette manière, avec une simple boucle, on peut écrire les chiffres de 1 à 5 respectivement dans A5, A6, A7, A8 et A9, comme ceci :
Sub test()
For Ctr = 1 To 5
Range("A5:A10")(Ctr) = Ctr
Next
End Sub
Comme je le disais, Excel se doute bien que c'est la valeur (le contenu) de la cellule que vous voulons changer. C'est pourquoi il nous autorise à nous en passer. Si nous avions voulu changer la couleur du texte pour le mettre en rouge (Le code du rouge est 255), nous aurrions écrit ceci :
Application.Range("A5").Font.color = 255
La procédure suivante Ecrit "Tomate" dans la cellule B5, et en plus, l'écrit en Rouge
Sub LaTomateRouge()
Application.Range("B6").Font.Color = 255
Application.Range("B6").Value = "Tomate"
End Sub
Remarque : avec Cells, présenté plus haut, on peut également définir autre chose que le contenu, comme la couleur de caractère, avec l'instruction
Cells(1, 1).Font.Color = 255
Remarque : au moment ou on écrit le point de Cells(1, 1). , on n'a pas droit à l'assistant de saisie automatique pour choisir dans la liste Font, et pourtant ça marche quand même (Pas d'erreur d'exécution)
Grace à la propriété Interior, nous avons la possibilité de définir les couleurs de fond des cellules. Le plsu simple étant d'utiliser la fonction RGB (Red (Rouge) , Green (Vert) , Blue (Bleu)). Red, Green et Blue étant des valeurs comnprises entre 0 (valeur nulle) à 255 (valeur maximum). Par exemple 255,0,0 donne du rouge vif. 0,0,0 donne noir, 255,255,255 donne blanc.
Nous allons dans l'exemple qui suit faire un tableau de récapitulation des couleurs qui écrit les valeurs RGB et colore la cellule voisine de cette couleur :
Sub TableauDeCouleur()
Dim MaSelection As Object
Set MaSelection = Range("A1:A17")
Cells.Clear
Numero = 0
For Ctr = 0 To 255 Step 16
Numero = Numero + 1
MaSelection(Numero).Offset(0, 0) = "RGB (0, 0,
" & Ctr & ")"
MaSelection(Numero).Offset(0, 1).Interior.Color = RGB(0,
0, Ctr)
MaSelection(Numero).Offset(0, 2) = "RGB (0, "
& Ctr & " , 0)"
MaSelection(Numero).Offset(0, 3).Interior.Color = RGB(0,
Ctr, 0)
MaSelection(Numero).Offset(0, 4) = "RGB ("
& Ctr & " , 0, 0)"
MaSelection(Numero).Offset(0, 5).Interior.Color = RGB(Ctr,
0, 0)
MaSelection(Numero).Offset(0, 6) = "RGB ("
& Ctr & ", " & Ctr & ", 0)"
MaSelection(Numero).Offset(0, 7).Interior.Color = RGB(0,
Ctr, Ctr)
MaSelection(Numero).Offset(0, 8) = "RGB ("
& Ctr & ", 0, " & Ctr & ")"
MaSelection(Numero).Offset(0, 9).Interior.Color = RGB(Ctr,
0, Ctr)
MaSelection(Numero).Offset(0, 10) = "RGB ("
& Ctr & ", " & Ctr & ", 0)"
MaSelection(Numero).Offset(0, 11).Interior.Color = RGB(Ctr,
Ctr, 0)
MaSelection(Numero).Offset(0, 12) = "RGB ("
& Ctr & ", " & Ctr & ", " & Ctr &
")"
MaSelection(Numero).Offset(0, 13).Interior.Color = RGB(Ctr,
Ctr, Ctr)
Next
Cells.EntireColumn.AutoFit
End Sub
Sheets("Nom de l'autre onglet").Range("B6") = "Je suis dans un autre onglet"
Mise en rouge de cette même cellule
Sheets("Nom de l'autre onglet").Range("B6").Font.Color = 255
Ca doit s'écrire en 2 lignes (Je n'ai pas trouvé le moyen de le compresser en une seule ligne). Dans l'exemple, on écrit "Autre Classeur, Autre feuille", dans la cellule B11, de l'onglet Janvier, du classeur déjà ouvert AutreFichier.xls :
Windows("AutreFichier.xls").Activate
Sheets("Janvier").Range("B11") = "Autre classeur, Autre
feuille"
Ecriture dans un classeur fermé
Voici le code généré automatiquement par l'enregistreur de Macros. Il s'agit d'ouvrir le fichier, se placer dans le bon onglet, la bonne cellule, et d'y écrire un texte :
Workbooks.Open Filename:="D:\Atelier\vbatest.xls"
Sheets("Janvier").Select
Range("A18").Select
ActiveCell.FormulaR1C1 = "Un texte"
ActiveWorkbook.Save
ActiveWindow.Close
Il peut fréquemment arriver qu'on veuille traiter l'ensemble de la sélection courante (Plage de cellule sélectionnée au clavier ou à la souris juste avant d'exécuter la macro)
L'exemple suivant montre comment remplir une sélection, aussi grande soit-elle avec une numérotation comme ceci:
|
A
|
B
|
C
|
D
|
|
|
1
|
1 | 2 | 3 | 4 |
|
2
|
5 | 6 | 7 | 8 |
|
3
|
9 | 10 | 11 | 12 |
Sub Comptage()
Dim Cellule As Object
For Each Cellule In Selection
Ctr = Ctr + 1
Cellule = Ctr
Next
End Sub
L'astuce ici, consiste à utiliser For Each. En effet, sans ce For Each, on aurait été embêté. Nous aurions pu dire :
Sub Comptage2()
Selection = "xxx"
End Sub
Mais le problème est ici que C'est xxx qui est écrit dans chacune des cellules... Ce qui n'est pas ce qu'on veut.
Nous pourrions passer de cellule en cellule, comme ceci :
Sub Comptage3()
Selection(1) = 10
Selection(2) = 20
End Sub
Mais dans ce cas, autre problème : On ne sait pas jusqu'ou on doit compter...
On a encore un tour dans notre sac : La propriété Count de Selection, qui nous permet de compter le nombre de cellules sélectionnées :
Sub Comptage4()
MsgBox Selection.Count
End Sub
Et finalement, nous pourrions effectuer le comptage proposé au début de cette page comme ceci :
Sub Comptage5()
For Ctr = 1 To Selection.Count
Selection(Ctr) = Ctr
Next Ctr
End Sub
C'est pareil...
ATTENTION : Si vous essayez d'exécuter cette macro alors que vous avez sélectionné à la souris (A l'aide de CTRL) des plages différentes, ça ne fonctionnera plus
Lorsqu'on exécute une macro assez longue, il est souvent préférable de ne pas voir le déroulement de la macrosous ses yeux. D'une part, ce n'est pas très confortable pour la personne qui utilise votre macro de voir tout ce qui se passe à grande vitesse, et d'autre part, paradoxalement, ça ralentit l'exécution de la macro. Justement, dans l'exemple précédent, nous avons une macro qui remplit une plage de cellules avec des chiffres. Quand vous l'avez testée, si vous aviez sélectionné une plage assez étendue de cellules, vous avez sans doute remarqué que les chiffres n'apparaissaient pas comme ça, d'un bloc, mais s'inscrivaient les uns après les autres dans les cellules.
Voici les deux lignes de code à rajouter afin que l'on ne voit pas l'exécution de la macro petit à petit, mais le résultat final qui apparait instantanément quand la macro a fini son travail :
Sub Comptage()
' Désactivation de la mise à
jour de l'écran :
Application.ScreenUpdating
= False
Dim Cellule As Object
For Each Cellule In Selection
Ctr = Ctr + 1
Cellule = Ctr
Next
' Réactivation de l'écran :
Application.ScreenUpdating = False
End Sub
| la case la plus en bas | Selection.End(xlDown).Select |
| la case la plus à droite | Selection.End(xlToRight).Select |
| la case la plus à gauche | Selection.End(xlToLeft).Select |
| la case la plus haute | Selection.End(xlUp).Select |
| Une case vers le bas | ActiveCell.Offset(1, 0).Select |
| Une case vers le haut | ActiveCell.Offset(-1, 0).Select |
| Une case vers la gauche | ActiveCell.Offset(0, -1).Select |
| Une case vers la droite | ActiveCell.Offset(0, 1).Select |
Selection.CurrentRegion.Select
A ne pas confondre avec la sélection de la feuille de calcul entière :
Cells.Select
La méthode Offset permet d'accéder à une cellule ou une plage de cellules située à un certain endroit (en haut, en bas, à gauche ou a droite d'une certaine cellule). La syntaxe théorique est :
Offset (NombreDeLignesVersLeBas , NombreDeColonnesVersLaDroite)
Par exemple :
Range("C6").Offset(0, 1) = 44
Ecrit 44 dans la cellule 0 lignes plus bas et 1 colonne plus à droite de la cellule C6 : Donc 44 dans la cellule D6.
Il est possible d'utiliser des chiffres négatifs pour écrire plus en haut ou plus à gauche :
Range("C6").Offset(-3, -2) = 55
Ecrit 55 3 lignes plus haut, et 2 colonnes plus à gauche que C6 (Donc dans la cellule A3).
Imaginons une liste de valeurs, comme ceci :
|
|
A
|
B
|
|
1
|
4
|
|
|
2
|
2
|
|
|
3
|
7
|
|
|
4
|
9
|
|
|
5
|
5
|
Dans les cellules de B1 à B5, il s'agit d'écrire, par programmation "Grand" si la chiffre à droite est plus grand que 5, sinon, écrire "Petit", comme ceci :
|
|
A
|
B
|
|
1
|
4
|
Petit |
|
2
|
2
|
Petit |
|
3
|
7
|
Grand |
|
4
|
9
|
Grand |
|
5
|
5
|
Petit |
Marche à suivre :
Sub UtilisationOffset()
Dim Espace As Object
Dim Ctr
Dim NombreCellule As Integer
Set Espace = Range("A1").CurrentRegion
NombreCellule = Espace.Count
For Ctr = 1 To NombreCellule
If Espace(Ctr) > 5 Then
Espace(Ctr).Offset(0, 1) = "Grand"
Else
Espace(Ctr).Offset(0, 1) = "Petit"
End If
Next
End Sub
Il est bien clair qu'il s'agit ici d'un exemple servant à illustrer la méthode Offset. Dans la pratique, sans la moindre programmation, on aurait pu tout simplement utiliser la fonction Si, comme ceci :
|
|
A
|
B
|
|
1
|
4
|
=SI(A1>5;"Grand";"Petit") |
Cette façon de faire aurait présenté l'avantage d'être dynamique (Si on change la valeur de A1, B1 se modifie en conséquence), au contraire de la programmation utilisant OffSet qui a fixé une fois pour toutes les valeurs de la colonne B (A moins qu'on réexécute la macro).
Nous allons essayer l'exercice suivant : Il s'agit de placer une bordure noire tout autour du tableau. En fait, plutôt de colorer toutes les cellules adjacentes en noir, comme ceci :
Avant l'exécution de la macro :
|
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
|||||||||
|
2
|
|||||||||
|
3
|
X | 5 | 5 | ||||||
|
4
|
6 | E | 11 | ||||||
|
5
|
ii | 11 | k | ||||||
|
6
|
m | a | b | ||||||
|
7
|
|||||||||
|
8
|
|||||||||
|
9
|
Après l'exécution de la macro :
|
|
A
|
B
|
C
|
D
|
E
|
F
|
G
|
H
|
I
|
|
1
|
|||||||||
|
2
|
|||||||||
|
3
|
X | 5 | 5 | ||||||
|
4
|
6 | E | 11 | ||||||
|
5
|
ii | 11 | k | ||||||
|
6
|
m | a | b | ||||||
|
7
|
|||||||||
|
8
|
|||||||||
|
9
|
L'idée est dond que lorsque on se trouve dans une cellule quelconque située entre C3 et E6, et qu'on exécute la macro, la bordure noire est affichée comme dans cet exemple. Attention : Si le tableau se trouve collé contre la gauche ou en haut de la feuille de calcul, la macro se plante, puisqu'elle ne peut pas colorer les cellules avant la colonne A, ni avant la ligne 1. On voit que la macro est séparée en 4 parties distinctes : Bordure du HAUT, DROITE; GAUCHE et BAS. Fonctionnement :
Sub BordureNoire()
' Déclaration des variables
Dim LaBase As String ' Tour à tour
les 4 coins
Dim LaPlace As Object ' Tableau de base (CurrentRegion)
Dim NbLigne As Integer ' Nombre de lignes
du tableau
Dim NbColonne As Integer ' Nombre de colonnes
du tableau
' Initialisation des variables :
Set LaPlace = ActiveCell.CurrentRegion
NbColonne = LaPlace.Columns.Count
NbLigne = LaPlace.Rows.Count
' Bordure du HAUT :
LaBase = LaPlace.Cells(1, 1).Offset(-1, -1).Address
For Ctr = 0 To NbColonne + 1
Range(LaBase).Offset(0, Ctr).Interior.Color =
RGB(0, 0, 0)
Next
' Bordure de DROITE :
LaBase = LaPlace.Cells(1, NbColonne).Offset(-1, 1).Address
For Ctr = 0 To NbLigne + 1
Range(LaBase).Offset(Ctr, 0).Interior.Color =
RGB(0, 0, 0)
Next
' Bordure de GAUCHE :
LaBase = LaPlace.Cells(1, 1).Offset(-1, -1).Address
For Ctr = 0 To NbLigne + 1
Range(LaBase).Offset(Ctr, 0).Interior.Color =
RGB(0, 0, 0)
Next
' Bordure du BAS :
LaBase = LaPlace.Cells(NbLigne, 1).Offset(1, -1).Address
For Ctr = 0 To NbColonne + 1
Range(LaBase).Offset(0, Ctr).Interior.Color =
RGB(0, 0, 0)
Next
End Sub