Il est de bon ton de se souhaiter la bonne année. Mais vous, et vous seul, pourrez faire en sorte que cette année soit bonne, meilleure que celle qui vient de s'écouler. Apprenez à ne compter que sur vous, car personne n'est plus qualifié que vous-même pour bâtir, réparer ou améliorer votre propre vie. Personne ne fera les choses à votre place. D'ailleurs, tout ce que les autres peuvent faire, c'est souhaiter que vous le fassiez. Et ne croyez pas que tout ceux qui vous entourent vous apporteront des solutions : certains font juste partie de vos problèmes. Transformez vos résolutions en actes, et dans douze mois, retournez-vous et souriez-vous fièrement : C'était long. C'était difficile. Mais ça y est : 2017 était une bonne année, merci Moi.

Ecriture et déplacement dans les cellules

Ecriture dans une certaine cellule de la feuille active

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.

Remplissage d'une grande zone

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.

Utilisation de Range

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"

Accès à une certaine cellule dans un Range

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

Mise en couleur du texte

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)

Mise en couleur du fond

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 :

  1. Je commence par effacer la feuille : Cells.Clear
  2. Nous déclarons une variable MaSelection qui va contenir Range("A1:A17") afin d'éviter de réécrire Range("A1:A17") à tout bout de champ. Pourquoi de A1 jusqu'é A17 ? Parce que en suite, la boucle For Ctr = 0 To 255 va s'incrémenter de 16 en 16 (Step 16), de 0 jusqu'à 255, ce qui fait que nous avons 16 étapes (16, 32, 48 ... jusqu'à 255) . Nous avons donc besoin de 16 lignes
  3. Nous utilisons une variable Numero qui elle, va s'incrémenter de 1 par 1 pour descendre d'une ligne chaque fois et pas de 16 lignes à la fois.
  4. Nous utilisons Offset MaSelection(Numero).Offset(Ligne, Colonne)(que j'explique plus bas) pour écrire dans la 2ème colonne, puis dans la 3ème, etc.
  5. J'écris donc le texte MaSelection(Numero).Offset(0, 0) = "RGB (0, 0, " & Ctr & ")", et la couleur correspondante .Interior.Color = RGB(0, Ctr, 0)
  6. A près la boucle, j'ajuste toutes les colonnes pour que le tableau soit esthétique, sans texte qui déborde (Cells.EntireColumn.AutoFit)

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

Ecriture dans une autre cellule d'un autre onglet

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

Ecriture dans une autre feuille d'un autre classeur ouvert

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

Utilisation de la sélection courante

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

Désactivation de l'écran

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

Déplacement au sein d'une feuille

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

Sélection du tableau courant

Selection.CurrentRegion.Select

A ne pas confondre avec la sélection de la feuille de calcul entière :

Cells.Select

Accès à des cellules adjacentes avec Offset

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).

Exercice pratique avec Offset

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 :

  1. Sélectionner la liste des chiffres sans savoir la taille de la colonne, mais en sachant qu'elle commence à A1 (Range("A1").CurrentRegion)
  2. Assigner cette région (A1:A5) à une variable-objet Espace simplement pour éviter d'avoir à écrire Range("A1").CurrentRegion à tout bout de champ
  3. Assigner à la variable NombreCellule le nombre de cellules Espace.Count que comprend la sélection Range("A1").CurrentRegion (Sinon, cette zone CurrentRegion va s'agrandir dynamiquement au fur et à mesure qu'on va écrire Grand ou Petit dans la 2ème colonne... ben oui...)
  4. Démarrer une boucle For Ctr = 1 To NombreCellule qui va parcourir les cellules Espace(Range("A1").CurrentRegion) une par une afin d'écrire à chaque fois dans la cellule voisine de droite "Grand" ou "Petit" (Espace(Ctr).Offset(0, 1) ="Grand/Petit")

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).

Exercice de style : Bordure noire

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 :

  1. Je déclare LaBase comme String. Cette variable contiendra tour à tour B2, F2 et B7 (Leas adresses des 3 coins depuis lesquels on va partir pour colorer les bordures) :
    LaBase = LaPlace.Cells(1, 1).Offset(-1, -1).Address
    LaPlace.Cells(1, 1)
    renvoie C3, et grace à Offset(-1, -1), nous avons B2 (cellule de départ)
  2. Je déclare LaPlace comme Object. Je l'initialise avec le tableau : Set LaPlace = ActiveCell.CurrentRegion (Pour raccourcir mes lignes de code : C'est plus commode)
  3. Je fixe une fois pour toutes les longueurs et largeur de ma sélection, pour être certain qu'elles ne varient pas lorsque je vais commencer à colorer les pourtours : NbColonne = LaPlace.Columns.Count et NbLigne = LaPlace.Rows.Count
  4. J'exécute les 4 boucles For To Next qui vont colorer les 4 côtés : For Ctr = 0 To NbColonne + 1
    NbColonne
    contient 3, je vais donc de 0 à (3+1), donc : 0,1,2,3,4 : Ces chiffres sont l'Offset (Déplacement) vers la droite: Je me décale d'1, puis 2, puis 3, puis 4 colonnes vers la droite en démarrant de B2 (LaPlace.Cells(1, 1).Offset(-1, -1).Address). Je colorie en noir la cellule en cours, et c'est peut être la ligne de code la plus difficile à comprendre :
    Range(LaBase).Offset(0, Ctr).Interior.Color = RGB(0, 0, 0)

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