Renommez tous les onglets d’un classeur
Ce que fait la macro : Elle donne à tous les onglets du classeur ouvert un nom composé d’un texte générique, demandé au début à l’utilisateur, et d’un numéro différent pour chaque feuille.
Sub RenommeOnglets ()
Dim Z As Integer
Dim Nom As String
Nom = InputBox(‘ Quel est le nom générique ‘)
For Z = 1 To Worksheets.Count
Worksheets (Z).Name = Nom & Z
Next Z
End SubExplications : Les premières instructions servent à déclarer les variables de travail : Z est un compteur, nous y reviendrons. La variable Nom, de type texte (string) recevra le
nom commun à tous les classeurs. L’instruction Nom = InputBox (‘ Quel est le nom générique ‘) ouvre une fenêtre dans laquelle l’utilisateur saisit un nom qui sera affecté à la variable
Nom. Nous utilisons ensuite une boucle For… Next pour balayer toutes les feuilles du classeur. Il s’agit d’une structure dite de ‘ boucle avec compteur ‘ : toutes
les instructions placées entre for Z = … et Next Z sont exécutées pour toutes les valeurs successives de Z. C’est l’instruction for Z = qui fixe les valeurs limites. Ici, la boucle donnera à
Z toutes les valeurs comprises entre 1 et Worksheets.Count, une variable système qui contient le nombre de feuilles dans le classeur. Ainsi, si le classeur contient 5 feuilles, l’instruction suivante sera
exécutée 5 fois. Worksheets(Z). Name = Nom & Z. Ici, Worksheets(Z). Name représente le nom de la feuille numéro Z. Cette feuille reçoit un nom formé du nom générique que vous avez tapé, suivi du numéro de
feuille. Ainsi, si vous avez choisi le nom Factures, vos feuilles seront successivement nommées Facture1, Facture2, Facture3, Facture4…Remarque : Le caractère ‘ & ‘ de concaténation : il permet de juxtaposer du texte (la variable caractère
Nom) et un nombre (la variable numérique Z), une souplesse rare dans les langages de programmation.
Ajoutez un commentaire indiquant le prix TTC
Ce que fait la macro : Toutes les cellules sélectionnées (elles contiennent un prix hors taxes) reçoivent un commentaire (il apparaîtra dans une infobulle quand vous placerez le pointeur sur les cellules).
Cette explication contient le contenu de la cellule, multiplié par 1,196, autrement dit augmenté de 19,6 % (ce qui transforme un prix HT en prix TTC).Préparation : Dans une feuille, sélectionnez une plage de cellules contenant des prix hors taxes. Après exécution de la macro, chaque cellule apparaît avec une petite marque rouge en haut à droite qui indique
la présence d’un commentaire.
Sub Taxes ()
Dim TTC As Currency
Dim Cellule As Object
Selection.ClearComments
For Each Cellule In Selection
Cellule.AddComment
TTC = Cellule.Value * 1.196
Cellule.Comment.Text ‘ soit TTC ‘ & Format(TTC, ‘ # 0.00 ‘) Next Cellule End SubExplications : La première instruction déclare une variable Taxes de type Currency (unité monétaire). Ce type est celui qui offre la meilleure précision pour les montants
financiers : les variables de ce type peuvent contenir jusqu’à 15 chiffres avant la virgule et quatre après, sans aucun arrondi. Avant d’attribuer des commentaires aux cellules, nous devons nous assurer que tous les éventuels commentaires
existants sont effacés (Selection.ClearComments). Dans la boucle, l’instruction Cellule.AddComment attribue un commentaire à l’objet nommé Cellule. La variable TTC reçoit ensuite la valeur toutes
taxes comprises, soit la cellule multipliée par 1,196. Enfin, le texte du commentaire de cette cellule (Comment.Text) reçoit une phrase formée des mots ‘ soit TTC ‘ suivis du
nombre précédemment calculé, auquel on a appliqué la fonction Format. Le rôle de cette dernière est de contrôler l’affichage d’un nombre, comme vous le faites avec le menu Format, Cellules, Nombres dans Excel. Ici,
l’argument ‘ #0.00 ‘ (avec un point, pas une virgule, attention !) demande un affichage du prix TTC avec deux chiffres décimaux. Après exécution de la macro, si vous passez le pointeur
sur une cellule contenant par exemple 500, vous lirez le commentaire ‘ soit TTC 598,00 ‘. Dans le commentaire, c’est bien une virgule qui apparaît, car cela dépend des paramètres de
Windows.
Insérez une ligne sur deux dans un tableau
Ce que fait la macro : Dans une feuille de calcul, elle insère une ligne blanche entre chacune des lignes du tableau.
Préparation : Ouvrez une feuille de calcul et assurez-vous que le tableau à formater commence en A1.
Sub InsereUneLigneSurDeux()
Dim Ligne As Integer
Range (‘ A1 ‘). Select
Ligne = 1
Boucle: Ligne = Ligne + 2
Rows (Ligne).Select
Selection.Insert Shift:=xlDown
If Ligne < ActiveSheet.UsedRange.Rows.Count Then
GoTo Boucle
End If
End SubExplications : La macro commence par sélectionner la cellule A1. La ligne Boucle: n’est pas une instruction, mais une simple étiquette, nous y reviendrons. L’instruction
Rows(Ligne). Select sélectionne la ligne entière. Puis la macro insère une ligne (Insert) après la ligne courante (Shift:=xlDown). Le test qui suit décide s’il faut continuer la macro. Si la ligne courante
(Ligne) est inférieure au nombre total de lignes dans le tableau (donné par la variable ActiveSheet.UsedRange.Rows.Count), l’exécution de la macro se poursuit (en passant par la commande GoTo) à l’étiquette
Boucle: placée plus haut, sinon elle se termine.Remarque :L’usage du GoTo est déconseillé dans des programmes de grande taille, car il rend les listings difficiles à comprendre. Toutefois, dans la mesure où l’étiquette et le GoTo qui y renvoient sont proches, cette
structure peut quand même vous faire gagner quelques lignes de code.
Sélectionnez toutes les cellules répondant à un critère précis
Ce que fait la macro : Dans une feuille de calcul, elle sélectionne toutes les cellules qui contiennent la même valeur qu’une cellule donnée (la cellule E1 dans notre exemple).Préparation : Ouvrez une feuille de calcul, mettez la valeur de référence en E1 et saisissez un tableau qui commence en A1.
Sub SelectCellulesValeurDonnee ( )
Modele = Range (‘ E1 ‘). Value
Plage = ”
Range (‘ A1 ‘).Select
For Each Cellule In ActiveCell.CurrentRegion
If Cellule. Value = Modele Then Plage = Plage & Cellule. Address () & ‘, ‘
Next Cellule
If Len (Plage) > 0 Then Range (Lef t(Plage, Len (Plage) -1)). Select
End SubExplications : La macro commence par noter la valeur de la cellule E1, qui servira de modèle. Puis une boucle classique balaie toutes les cellules dans le tableau courant
(ActiveCell.CurrentRegion). Ce ‘ tableau courant’ représente une zone autour de la sélection limitée par une ligne vide ou une colonne vide. L’astuce, ici, consiste à utiliser une variable de
type texte, nommée Plage. Pour chaque cellule du tableau courant dont la valeur est identique au modèle, l’adresse de cette cellule est ajoutée au contenu de la variable Plage et on y ajoute un séparateur : le
point-virgule. Par exemple, celle-ci contiendra successivement B3; puis B3;C15; puis B3;C15;E17; et ainsi de suite. Si au moins une cellule a été sélectionnée (la longueur de Plage n’est donc pas nulle, soit
Len(Plage) > 0), on sélectionne une plage de cellules formée par la variable Plage.Mais il faut, au préalable, en ôter le dernier caractère qui est un point-virgule. C’est le rôle de la fonction Left(Plage, Len(Plage) -1) qui retient, à gauche de la variable, un nombre de caractères égal à la
longueur de cette variable moins un. Ainsi, si la variable Plage contenait B3; C15;E17; c’est bien la plage B3;C15;E17 qui sera sélectionnée.Remarque : Cette macro comble une lacune d’Excel qui n’offre aucun moyen direct de sélectionner les cellules contenant une valeur donnée.
Eliminez les lignes vides dans un tableau
Ce que fait la macro : Elle supprime, dans toute la feuille, les lignes entièrement vides.Préparation : Ouvrez une feuille contenant un tableau.
Sub SupprimerLignesVides ()
Dim Derniere As Long
Dim Ligne As Long
Dim CellulesOccupees As Integer
Derniere = ActiveSheet. UsedRange. Rows. Count
Application. ScreenUpdating = False
Ligne = Derniere
For Ligne = Derniere To 1 Step -1
CellulesOccupees = Application. CountA(Rows (Ligne))
If CellulesOccupees = 0 Then Rows (Ligne). Delete
Next Ligne
End SubExplications : La macro commence par déterminer le nombre de lignes dans la ‘ zone utile ‘. Ce nombre est donné par la variable d’environnement
ActiveSheet.UsedRange.Rows.Count (feuille active. Zone utilisée. Lignes. Nombre). Pour avoir une idée de ce qu’Excel considère comme zone utile, sélectionnez la cellule A1 et tapez Ctrl + Fin.
L’instruction Application.ScreenUpdating = False est facultative : elle permet à la macro de s’exécuter un peu plus vite en désactivant la mise à jour de l’affichage entre chaque instruction. Une boucle for…
next va ensuite balayer le tableau en partant de la dernière ligne. Pour supprimer des éléments dans un tableau, il est plus facile de le parcourir de bas en haut. C’est pour forcer un comptage décroissant que l’on ajoute la clause step
-1 dans l’instruction for (incrément de -1). La macro compte ensuite, en exploitant la fonction CountA, le nombre de cellules non vides dans la ligne en cours. Si ce nombre est nul, la ligne est supprimée.
Appliquez un formatage conditionnel à six couleurs
Ce que fait la macro : Elle applique, à toutes les cellules sélectionnées, une couleur choisie parmi six, en fonction de la valeur numérique de la cellule, alors que la fonction Formatage conditionnel d’Excel
est limitée à trois conditions.Préparation : Ouvrez une feuille de calcul et sélectionnez une plage de cellules contenant des nombres compris entre 1 et 40 (ou modifiez les valeurs limites des
‘ bornes ‘ dans la macro).
Sub Conditionnel ()
For Each Cellule In Selection
Select Case Cellule. Value
Case Is < 11
Cellule. Interior. ColorIndex = 3′ rouge
Case 11 To 15
Cellule. Interior. ColorIndex = 46′ orange foncé
Case 16 To 20 Cellule. Interior. ColorIndex = 45′ orange moyen
Case 21 To 25 Cellule. Interior. ColorIndex = 44′ orange clair
Case 26 To 30 Cellule. Interior. ColorIndex = 27′ jaune
Case Else Cellule. Interior. ColorIndex = 19′ jaune clair
End Select
Next Cellule
End SubExplications : La structure if … then … else convient quand il faut prendre une décision ‘ binaire ‘ (par exemple if
A<10). Mais dans notre exemple, la décision est plus complexe puisque nous voulons que la couleur d’une cellule puisse prendre six valeurs distinctes en fonction de la valeur de la cellule (inférieure à 11, comprise entre 11 et 15,
comprise entre 16 et 20…). Au lieu d’emboîter des if … then … end if, ce qui serait peu pratique, nous employons la structure Select Case, qui permet de créer autant de branches que nécessaire.
L’ouverture de cette structure, Select Case Cellule.Value spécifie que les tests suivants porteront sur la variable Cellule.Value. Il reste à créer une ‘ branche ‘ pour chaque
plage de valeurs. Ainsi, Case 11 To 15 s’applique aux valeurs comprises entre 11 et 15. Sous chaque Case, placez les instructions (ici, une seule) à exécuter dans ce cas. Notez que la dernière branche, nommée Case
Else, décrit les instructions à exécuter si aucune des conditions précédentes n’est vérifiée. Enfin, c’est l’instruction Cellule.Interior.ColorIndex = Nnn qui se charge de donner un fond coloré à chacune des cellules balayées par
la boucle. La structure Select Case doit, comme les autres, être refermée. C’est le rôle de la ligne End Select.
Remarque : Dans les lignes Cellule.Interior…, nous
avons ajouté un commentaire indiquant ‘ en clair ‘ la couleur désignée par la valeur numérique. Dans une ligne de code, vous pouvez ajouter un commentaire en le faisant précéder d’une
apostrophe.
Formatez une cellule sur trois dans une colonne
Ce que fait la macro : Dans une colonne zone sélectionnée, la macro donne à une cellule sur N (ce nombre est demandé au début) le même format que la première cellule de la sélection.Préparation : Dans une feuille de calcul, donnez à une cellule d’une colonne le format servant de modèle, sélectionnez cette cellule et plusieurs autres dans la même colonne et lancez la macro.
Sub MarquerCellules ()
Dim I as Integer : Dim Frequence as Integer
Selection. Item (1,1). Copy
I = 0
Frequence = CInt(InputBox(‘ Formater une cellule sur : ‘))
For Each Cellule In Selection
If I Mod Frequence = 0 Then
Cellule. PasteSpecial xlPasteFormats
End If
I = I + 1
Next Cellule
End SubExplications : La macro commence par copier dans le Presse-Papiers la première cellule de la sélection. L’élément Item (1,1) désigne la cellule placée en haut à gauche (ligne 1,
colonne 1) de cette sélection. Puis la fonction InputBox, dont le résultat est converti en valeur Integer par la fonction Cint, demande à quelle fréquence il faut formater les cellules. Un compteur (la variable
I) est mis à zéro. Dans la boucle principale, ce compteur est augmenté d’une unité (I = I + 1) pour chaque cellule. On cherche alors le reste de la division de I par Frequence avec la fonction
Mod (c’est la fonction modulo, la division entière). Si ce reste est nul, la variable I est donc un multiple de Frequence. Dans ce cas, on ne fait pas un collage classique, mais un collage spécial
(PasteSpecial) pour n’appliquer à la cellule en cours que le format de celle servant de modèle (xlPasteFormat).
Lancez une commande à chaque ouverture d’un classeur
Ce que fait la macro : Chaque fois que vous ouvrez le classeur, elle affiche un message ‘ Chiffres provisoires ‘ (cela pourrait être n’importe quelle autre
commande).Préparation : Attention, pour que cette macro fonctionne, vous devez obligatoirement la placer dans le dossier ThisWorkBook. Dans le volet de gauche de l’Editeur, cliquez deux fois sur
cet élément et saisissez votre code dans le volet de droite.
Sub WorkBook _Open ()
MsgBox (‘Chiffres provisoires’)
End SubExplications : Certaines macros ont un nom imposé. C’est le cas ici. Dans la mesure où la macro se nomme WorkBook_Open (classeur_Ouvrir), elle sera automatiquement exécutée chaque fois que vous
ouvrez ce classeur. On parle ici de programmation événementielle. Un ‘ événement ‘, pour VBA, c’est l’ouverture d’un fichier, un clic sur une cellule, un appui sur une touche du clavier… A chaque
événement, vous pouvez associer une série de commandes.
Dressez la liste des fichiers d’un dossier
Ce que fait la macro : Elle écrit, dans la colonne A de la feuille courante, la liste des fichiers du dossier spécifié (C:MonDossier dans notre exemple, mais vous pouvez en indiquer
un autre selon vos besoins).Préparation : Ouvrez ou créez une feuille vierge.
Sub ListeDesFichiers ()
Dim I As Long
With Application. FileSearch
. New Search
. FileType = msoFileTypeAllFiles
. LookIn = ‘ C:MonDossier ‘
. SearchSubFolders = True
. Execute
With. FoundFiles
For I = 1 To. Count
Range (‘ A1 ‘). Offset(I, 0) =. Item (I)
Next I
End With
End With
End SubExplications : Toute la macro est écrite autour d’une boucle Application.FileSearch (recherche de fichiers). L’argument . NewSearch indique qu’il s’agit d’une nouvelle recherche.
L’instruction FileType = msoFileTypeAllFiles spécifie le type de fichiers recherchés (ici tous les fichiers). En remplaçant msoFileTypeAllFiles par msoFileTypeBinders, la macro ne chercherait que les classeurs
Excel. L’argument . LookIn spécifie dans quel dossier effectuer la recherche (C:MonDossier), enfin SearchSubFolders = True demande à la macro de parcourir également, le cas échéant, les sous-dossiers de C:MonDossier. La
clause . Execute indique ensuite ce qu’il faut faire avec les fichiers trouvés. La collection . FoundFile est alors balayée par une boucle for… next dont la limite supérieure est FoundFiles.
Count (nombre de fichiers trouvés). Chacun des noms de fichiers est alors écrit dans une cellule distincte. Pour qu’une macro écrive dans une cellule, il est inutile de la sélectionner. Vous pouvez en effet utiliser l’instruction
Offset qui admet deux arguments : un nombre de lignes et un nombre de colonnes. Ainsi, l’instruction Range(‘B2’). Offset(3,4) = 2006 écrit la valeur 2006 dans la cellule F5, soit 3 lignes
plus bas et 4 colonnes à droite de la cellule B2. Chaque nom de fichier, un des éléments du tableau Item(I), est ici écrit dans une cellule décalée de I lignes et de zéro colonne (donc en colonne
A) par rapport à la cellule A1.
Ajoutez une ligne dans un fichier texte à chaque impression du classeur
Ce que fait la macro : Chaque fois que vous imprimez l’une des feuilles du classeur, la macro écrit, dans un fichier texte (en l’occurrence, le fichier Utilisation. log placé à la racine du disque C) une ligne
qui contient le nom de l’utilisateur et la date.Préparation : Attention, pour que cette macro fonctionne, il faut obligatoirement, comme la précédente, la placer dans le dossier ThisWorkBook. Dans le volet de gauche de l’Editeur,
cliquez deux fois sur cet élément et tapez votre code dans le volet de droite.
Private Sub WorkBook _ BeforePrint (Annule A s Boolean)
Open ‘ C:Utilisation. log ‘ For Append A s # 1
Print # 1, Application. UserName, Now
Close # 1
End SubExplications : C’est là un deuxième exemple d’une macro événementielle. Ici, nous créons une macro qui sera automatiquement exécutée avant (Before) chaque commande d’impression
(Print). Quand vous ouvrez un fichier texte avec la commande Open, vous devez indiquer si vous avez l’intention d’y lire (for input) ou d’y ajouter (for append) des données. Dans ce dernier cas, si
le fichier Utilisation.log n’existe pas, il est automatiquement créé. Ce fichier, de type texte, peut être lu avec n’importe quel traitement de texte ou le Bloc-notes de Windows. Enfin, le #1 est appelé numéro de
canal : vous l’utiliserez dès que vous voudrez écrire des données dans ce fichier. C’est ce que fait l’instruction Print #1 : elle écrit dans ce fichier le nom de l’utilisateur (Application. UserName, variable
prédéfinie d’Excel) et la date et l’heure (la fonction Now). Le fichier Utilisation.log est ensuite refermé par linstruction Close.
🔴 Pour ne manquer aucune actualité de 01net, suivez-nous sur Google Actualités et WhatsApp.
