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.

Gestion des onglets

Les onglets sont le synonymes de feuilles. Dans un classeur (Fichier .XLS), il doit au moins y en avoir 1, et au maximum 255. On peut confondre les objets Sheets et WorkSheets. Apparemment, ce sont les mêmes objets, qui se comportent de la même manière. Pour éviter les confusions, utilisons Sheets dans tous les cas de figure.

Principe des onglets

Les onglets existants dans le classeurs sont numérotés de 1 à Sheets.Count, et peuvent être accédés par leur nom ou leur numéro d'ordre :

MsgBox Sheets.Count ' Donne le nombre d'onglets existants
MsgBox Sheets(1) ' génère une erreur
MsgBox Sheets(1).Name ' Donne le nom de l'onglet le plus à gauche
MsgBox Sheets(Sheets.Count).Name ' Donne le nom de l'onglet le plus à droite

L'onglet sur lequel on se trouve est appelé ActiveSheet.

MsgBox ActiveSheet.Name ' Donne le nom de l'onglet actif
MsgBox ActiveSheet.Index ' Donne la position actuelle de l'onglet actuel (3 si c'est le 3ème onglet a partir de la gauche)

Effacement des onglets

Effacement d'un onglet

On utilise la méthode Delete pour effacer les onglets. Admettons que je veuille effacer l'onglet nommé "Onglet inutile", j'écris :

Sheets("Onglet inutile").Delete

Afin d'éviter le message d'erreur sempiternel d'Excel "Attention, les feuilles sélectionnées vont être effacées", j'envoie la touche ENTER à l'aide de Sendkeys, comme ceci :

SendKeys ("{ENTER}")
Sheets("Onglet inutile").Delete

Evidemment, si l'onglet "Onglet inutile" n'existe pas, Excel renvoie une erreur. Erreur que nous contournons en lui demandant de ne pas y prêter attention, et de simplement finir la procédure sans rien effacer :

On Error Resume Next

Finalement on a la procédure propre suivante :

Sub EffacerOnglet()
  On Error Resume Next
  SendKeys ("{ENTER}")
  Sheets("Onglet inutile").Delete

End Sub

Effacement de tous les onglets

Dans l'exemple précédent, nous avons effacé un onglet en utilisant son nom. Ceci dit, les onglets sont numérotés de 1 à Sheets.Count. Ce qui nous permet de tous les effacer sans connaître leur nom

Un classeur doit contenir au moins un onglet (une feuille de calcul). Ainsi, le code suivant génère une erreur :

For Ctr = 1 To Sheets.Count
  Sheets(Ctr).Delete
Next

Il faut ajouter -1 comme ceci pour que ça marche :

For Ctr = 1 To Sheets.Count - 1
  Sheets(Ctr).Delete
Next

Sheets.Count contient le nombre d'onglets actuellement existants dans votre classeur. Avec le For To Next, on se déplace d'onglet en onglet, et on efface tout sauf le dernier, pour éviter l'erreur (S'il y a 10 onglets, on se déplace du numéro 1 jusqu'au numéro 10 - 1 (9))

Une solution plus élégante serait d'effacer tous les onglets sauf justement celui sur lequl on se trouve :

Sub EffacementTouteFeuille()
  For Ctr = Sheets.Count To 1 Step -1
    If Sheets(Ctr).Name <> ActiveSheet.Name Then
      Sheets(Ctr).Delete
    End If
  Next
End Sub

Pourquoi avoir écrit Sheets.Count To 1 Step -1 au lieu de 1 To Sheets.Count?

Parce qu'a chaque tour de boucle, on efface chaque fois une feuille, et Sheets.Count devient chaque fois plus petit. Au bout d'un moment, VBA ne trouve plus les feuilles. C'est subtil !

Création d'onglets

La commande de base s'appelle

Sheets.Add

Le simple fait d'écrire Sheets.Add ajoute un onglet juste avant l'onglet actif, dont le nom est FeuilX, X étant un nombre imprévisible (5 si c'est la 5ème fois qu'on crée un onglet, même si on a déjà effacé les 4 précédents).

On peut connaître immédiatement le nom de l'onglet qu'on vient de créer, en une seule ligne :

MsgBox Sheets.Add.Name

Sheets.Add possède la syntaxe suivante :

Sheets.Add OngletAvantLequelLaNouvelleFeuilleDoitEtreInsérée , OngletAPRESLequelLaNouvelleFeuilleDoitEtreInsérée , NombreDeFeuillesAInsérer, GenreDeFeuilleMaisCeNestPasTrèsIntéressant

Il est donc possible, avec le 1er paramètre, d'insérer une nouvelle feuille à un endroit précis. L'exemple suivant insére la nouvelle feuille avant la première :

Sheets.Add Worksheets(1)

Et maintenant, après la dernière :

Sheets.Add , Worksheets(WorkSheets.Count)

Dernier exemple : Insertion de 3 feuilles après la dernière feuille :

Sheets.Add , Worksheets(WorkSheets.Count), 3

Cas pratique : On crée un onglet, et on le renomme d'un même coup

Sheets.Add.Name = "Nouveau"

Une autre manière de faire, en utilisant une variable-objet (L'avantage est qu'on peut donner un ou plusieurs paramètres à Add):

Set NouvelleFeuille = Worksheets.Add (Sheets(1))
NouvelleFeuille= "Nouveau"

Copier les onglets

La copie d'onglets se passe exactement de la même manière que la création d'onglets, sauf qu'on utilise Copy à la place de Add

Sheets("Ici").Copy

Copie la feuille Ici juste avant elle-même

Déplacer les onglets

Le déplacement se passe comme la copie ou la création d'onglets.

Sheets("Ici").Move, Worksheets(WorkSheets.Count)

Déplace la feuille Ici avant la première feuille

Renommer les onglets

Renommer l'onglet actuel en Test :

ActiveSheet.Name = "test"

Exercice pratique : Création d'onglets à partir d'une plage de cellules

Lorsqu'il s'agit de créer un onglet, c'est simple, mais si tout à coup vous deviez créer 12 onglets : De janvier à Décembre, vous devez faire 12 fois la manoeuvre de création d'un nouvel onglet, suivi du re-nommage de celui-ci. Je vous propose ici de créer une macro qui crée les onglets voulus à partir d'une plage de cellules (CurrentRegion), comme ceci :

 
A
1
Janvier
2
Février
3
Mars
4
Avril

Imaginez que vous êtes actuellement dans la cellule A2 (Ou A1, A3 ou A4, ou même que vous ayez sélectionné A1:A4, peu importe), et que voudriez, par la simple exécution d'une macro, créer les onglets Janvier, Février, Mars et Avril, dans cet ordre, sans toucher aux onglets existants, mais en les plaçants après les onglets déjà existants. Il faut donc :

1. Sélectionner tout le tableau de A1 à A4 (ActiveCell.CurrentRegion.Select)
2. Créer un par un les onglets de Janvier à Avri l (Sheets.Add)
3. Les renommer (Sheets(Sheets.Count).Name)

On commence :

Sélection du tableau (région actuelle) : activeCell.CurrentRegion.Select

Création d'un tableau de texte (Dim Tableau() As String) que nous redimensionnons (ReDim) en le nombre de cellules (CurrentRegion.Count) que contient notre sélection
Dim Tableau() As String
ReDim Tableau(1 To ActiveCell.CurrentRegion.Count)

Remplissage de ce tableau avec les différents éléments (Janvier à Avril). ActiveCell.CurrentRegion.Count est le nombre de cellules.
For Ctr = 1 To ActiveCell.CurrentRegion.Count
  Tableau(Ctr) = ActiveCell.CurrentRegion(Ctr)
Next

On recommence la boucle (For Ctr = 1 To ActiveCell.CurrentRegion.Count), cette fois pour créer les onglets réellement (Sheets.Add), chaque onglet étant créé après le denier onglet (Sheets(Sheets.Count)). Dans un deuxième temps, il s'agit de le renommer (Sheets(Sheets.Count).Name = Tableau(Ctr))
For Ctr = 1 To ActiveCell.CurrentRegion.Count
  Sheets.Add , Sheets(Sheets.Count)
  Sheets(Sheets.Count).Name = Tableau(Ctr)
Next

J'ai été obligé de faire 2 boucles, car avec une seule boucle, j'ai eu des problèmes techniques de récupération de ActiveCell.CurrentRegion.Count. En effet, à chaque création de nouvel onglet, Excel active cet onglet automatiquement, ce qui fait qu'évidemment, ActiveCell.CurrentRegion.Count ne contient plus qu'une cellule, puisque cette fonction ne marche que si on est dans une cellule qui fait partie d'un groupe de cellules, alors évidemment, quand on crée un nouvel onglet, si Excel l'active automatiquement. il n'y a plus de CurrentRegion qui tienne.. C'est pourquoi j'ai contourné le problème en "Fixant" le contenu des cellules de ce CurrentRegion dans un tableau (Tableau 1 to ActiveCell.CurrentRegion(Ctr)), comme ça, Excel peut changer de feuille active tant qu'il veut, je m'en fiche, j'ai stocké à part ma précieuse liste de mois.

Voici la procédure définitive :

Sub CreationOnglet()
  ActiveCell.CurrentRegion.Select
  Dim Tableau() As String
  ReDim Tableau(1 To ActiveCell.CurrentRegion.Count)

  For Ctr = 1 To ActiveCell.CurrentRegion.Count
    Tableau(Ctr) = ActiveCell.CurrentRegion(Ctr)
  Next

  For Ctr = 1 To ActiveCell.CurrentRegion.Count
    Sheets.Add , Sheets(Sheets.Count)
    Sheets(Sheets.Count).Name = Tableau(Ctr)
  Next
End Sub

Exercice de style : Tri des onglets par ordre alphabétique

L'algorithme utilisé est réellement compliqué à comprendre pour un non-professionnel de la programmation. En deux mots, il s'agit d'une boucle imbriquée (Un For To Next dans un autre for To Next). Pour trier les feuilles, il faut comparer la première feuille avec toutes les autres, et quand on rencontre une feuille qui est alphabétiquement plus petite qu'une autre, il s'agit de remplacer la première feuille avec la feuille plus petite, de sorte qu'à la fin du parcourt de toutes les feuilles, on est cette fois certains que la feuille la plus à gauche est la plus petite de toutes, alphabétiquement. Ensuite, les autres feuilles ne sont pas encore triées : Il s'agit de comparer maintenant la 2ème feuille avec toutes les suivantes, sauf la première puisque la première on sait déjé maintenant que c'est la plus petite. On compare donc la 2ème feuille avec toutes les autres, et à nouveau, on échange la 2ème feuille avec toute feuille qui sera alphabétiquement la plus petite. On est alors sûrs que la 2ème feuille est alphabétiquement la plus petite, mais plus grande que la première. On continue le même principe avec la 3ème feuille qui est comparé avec toutes les feuilles restantes, puis la 4ème, etc... jusqu'à ce qu'on ait plus qu'à comparer l'avant dernière feuille avec la dernière pour voir quelle est la plus petite des deux (ce sont dont alphabétiquement les deux plus grandes..). Cette façon de faire s'appelle le "Tri à bulles". Il existe d'autres moyens de trier des choses, mais celui-ci est le plus simple à expliquer (le moins compliqué disons...), et également le plus catastrophique en matière de performance (Si on devait trier 10000 onglets, on aurait pour un sacré bout de temps.)

Sub TriNomsOnglets()
  On Error Resume Next
  Dim I As Integer, J As Integer
  For I = 1 To Sheets.Count
    For J = I To Sheets.Count
      If UCase(Sheets(J).Name) < UCase(Sheets(I).Name) Then
        Sheets(J).Move Sheets(I)
      End If
    Next J
  Next I
End Sub