All posts tagged VBA

Ecrire un tri en VBA Excel

En VBA, la méthode « Sort » s’applique à des objets « Range » (à des plages de cellules). Nous avons fréquemment besoin d’écrire des sort sur des plages en VBA Excel. Comme souvent dans ce cas, nous n’avons qu’à faire appel à l’enregistreur de macros, faire un tri et adapter le code. Malheureusement, dans ce cas, le code est disons… Trop riche… Voici en effet le code généré pour effectuer un tri sur une plage A3:F75 :

Range("A3:F3").Select
Range(Selection, Selection.End(xlDown)).Select
ActiveWorkbook.Worksheets("Echeancier").Sort.SortFields.Clear
ActiveWorkbook.Worksheets("Echeancier").Sort.SortFields.Add Key:=Range( _
"A4:A75"), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ActiveWorkbook.Worksheets("Echeancier").Sort
.SetRange Range("A3:F75")
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With

Franchement, ça ne donne pas envie d’écrire un tri… Voici donc la marche à suivre :

 1/ Déclarer un Objet Range,

2/ Affecter l’objet Range, dont la première ligne contient les titres,

3/ Affecter les clés de tri,

4/ Appliquer la méthode Sort à l’objet Range sur les clés désirées.

 Voici un exemple :

Dim MonEcheancier As Excel.Range
Set MonEcheancier = Range(MaFeuilleEcheancier.Cells(1, 1), MaFeuilleEcheancier.Cells(1, 1).End(xlDown).Offset(0, 5))
MonEcheancier.Sort Key1:=MaFeuilleEcheancier.Cells(1, 1), Order1:=xlAscending, Header:=xlYes

Si nous avions plusieurs clés, nous n’aurions qu’à écrire MaRange.Sort Key1:=xxx Key2:=yyy etc. Nous avons également positionné l’option Header à oui, car nous avons une en-tête qui ne sera pas triée.

Pour certains, l’affection du Range pourrait sembler confuse… Une Range simple (rectangulaire) est délimitée par 2 cellules, en haut à gauche et en bas à droite. La syntaxe de Range est donc Range (Cell1, Cell2).

Une Cellule est identifiée par son numéro de ligne et son numéro de colonne. On écrit Cells(1,5) pour la 5ème colonne de la 1ère ligne. On peut préfixer Cells par la feuille qu’on adresse, ici MaFeuilleEcheancier, qu’on aura au préalable déclarée avec :

 Dim MonProjet As Excel.Workbook
Dim MaFeuilleEcheancier As Excel.Worksheet
Set MonProjet = Excel.ActiveWorkbook
Set MaFeuilleEcheancier = MonProjet.Worksheets("Echeancier")

Enfin, .End(XlDown) permet d’adresser directement la dernière cellule non vide vers le bas (XlToRight pour aller vers la droite) et .Offset permet de se décaler de 0 lignes et 5 colonnes dans l’exemple.

Top

Utiliser des fonctions du Framework .NET en VBA

On peut faire beaucoup, beaucoup de choses en Excel/VBA… Au fil des articles, nous avons souvent étudié la possibilité de faire interagir Excel avec d’autres applications (Power MAC, Word, PowerPoint, Business Objects…) ou avec diverses fonctionnalités Windows (gestion de fichier FSO…). Pour cela, il faut faire appel à des bibliothèques de fonctionnalités, accessibles via le menu de l’éditeur VBA « outils – références ».

S’il existe une bibliothèque de fonctionnalités (si on peut l’appeler ainsi…) bien documentée, il s’agit du Framework .NET fourni par Microsoft. Mais à priori, pour l’utiliser, nous devons avoir affaire aux langages de « haut niveaux » que sont VB.NET et C# (entre autres), ainsi qu’à l’atelier de développement Visual Studio. Pourtant, en étant (vraiment…) motivé, on peut utiliser des fonctionnalités du Framework en VBA office. Pour l’exemple, nous allons utiliser une classe du Framework 2.0 :

la classe STRINGBUILDER (SYSTEM.TEXT.STRINGBUILDER) , qui est censée être plus rapide pour traiter les chaines de caractère. L’exemple n’est pas le meilleur du monde, car on n’est pas sensé exécuter un code rapide en VBA…

Pour utiliser une classe du Framework Dotnet, il est nécessaire (comme toujours quand on utilise des bibliothèques autres que VisualBasic, Office et Excel) d’en importer la référence via le menu de l’éditeur VBA d’Excel : « Outils – Références ». Mais on ne trouvera aucune référence au Framework, .NET ou dotnet ! Il faut l’importer manuellement. Comment savoir où se trouve cette bibliothèque, dans quel fichier dll ?

Pour cela, ouvrons le programme Visual Studio (une version gratuite Visual Basic Express Edition fait parfaitement l’affaire), nous affichons l’explorateur d’objet (F2), et nous cherchons StringBuilder :

Nous affichons l’espace de nom Sytem.Text pour connaitre la dll, il s’agit de la mscorlib :

Le chemin est le suivant (pour le Framework 2.0, si on a choisi ce Framework dans les propriétés du projet Visual Studio) :

Nous pouvons désormais importer non pas la dll dans VBA, mais le fichier avec l’extension tlb

Le code suivant fonctionne parfaitement sous Visual Studio en .NET :

Pourtant, en VBA, cela ne fonctionne pas !

Cela est dû au fait que les fonctions et les procédures (les méthodes donc…) .NET peuvent avoir plusieurs signatures, et visiblement, le compilateur VBA ne s’y retrouve pas… On observe dans l’explorateur d’objets de Visual Studio (voir copie d’écran un peu plus haut) une multitude de signatures différentes pour la méthode Append de l’objet StringBuilder. Pas moins de 19 !

Pour « invoquer » une signature, il suffit (ce n’était pas trivial…) de suffixer la méthode Append par son numéro. Pour adresser la bonne signature, celle qui attend du type « String » en paramètre, il faut suffixer par un numéro mais lequel ? Et bien, cela se corse… Il faut essayer toutes les possibilités ! Sur certains forums, il est dit qu’il suffit de donner le numéro inverse de l’ordre d’apparition dans l’explorateur d’objet, mais je n’ai rien constaté de tel. Non, il faut tester toutes les combinaisons une à une, jusqu’à la suivante :

C’est donc laborieux, mais nous avons réussi ! Cette manipulation n’est bien sûr à réserver qu’à des cas bien précis, et affirmons-le tout de suite, ce sera très rare ! De plus, l’explorateur d’objet n’est pas opérationnel en VBA, l’IntelliSense n’est plus de la partie… Encore plus mystérieux, la méthode Replace fonctionne sans invoquer la bonne signature, pas de numéro à suffixer ! Comme évoqué en début d’article, cela fonctionne, mais il faut rester motivé…

Sub TestDotnet()

Dim MonStringBuilder As New StringBuilder

Set MonStringBuilder = MonStringBuilder.Append_3(« test1 numéro 1 »)

Set MonStringBuilder = MonStringBuilder.Replace(« numéro », « chiffre »)

MsgBox (MonStringBuilder.ToString())

End Sub

Top

Créer une macro ou un programme VB (ou VBA) Excel

Une macro, un programme VB, VBA Excel, tout ceci est à peu près la même chose. Souvent, sur des forums ou autre, on peut trouver du code VBA, brut, sans autre explication que celle fournie par l’auteur : « OK, ça marche comme ça ». Mais pour le débutant Excel, cela n’a rien d’évident : où donc coller où ce code ?!

Création d’un module de code VB dans l’éditeur VBA

La combinaison de touche « ALT + F11 » ouvre l’éditeur VBA. Dans l’éditeur VBA, faire un clic droit sur le nom du fichier Excel et choisir d’insérer un module :

Nous sommes alors sur une page blanche, sur laquelle nous pouvons copier – coller le code trouvé ça et là sur le net. L’enregistrement du fichier Excel rendra ce programme disponible lors des ouvertures suivantes du fichier. Et pour exécuter, ALT+F8 et choisir le nom du module créé par le copier coller (c’est le nom de la procédure Sub, voir exemple suivant)

Création d’une macro par enregistrement d’actions effectuées sous Excel

Dans Excel, nous pouvons effectuer cette action avec le choix disponible dans le ruban (ou menu) « Affichage – macro – enregistrer une macro ».

Dans la boite de dialogue qui s’ouvre, nous pouvons donner un nom à notre macro (« test » pour l’exemple). Et nous allons effectuer les actions que l’on souhaite voir enregistrer en code VBA par Excel. Nous pouvons entrer le texte « hello world » en cellule A1, puis le mettre en caractère gras.

Pour consulter le code générer, ALT+F8 pour voir la liste des macros disponible, nous sélectionnons « test », puis « modifier ». Et voici le code généré :

Bon, sur ce genre de cas, le code n’est pas franchement idéal… On pourrait avantageusement remplacer le code généré :

Range("A1").Select
ActiveCell.FormulaR1C1 = "hello world"
Range("A1").Select
Selection.Font.Bold = True

par :

Cells(2, 1).Value = "hello world"
Cells(2, 1).Font.Bold = True

ou encore (on « factorise » le cells(1,1)):

With Cells(3, 1)
.Value = "hello world"
.Font.Bold = True
End With

Pour des cas aussi simple, n’importe quel habitué de VBA codera directement. Par contre, pour des manipulations plus rares, comme la création d’un tableau croisé dynamique (code qu’on ne connait pas forcément par cœur), l’enregistrement en mode macro permet de facilement générer le code pour l’adapter à ses propres besoins !

Top

Exemple ou Squelette d’un programme VBA / Macro Excel (niveau avancé) : gestion des données sur 2 feuilles

Il s’agit exactement du même exercice que le lien suivant :

http://www.expert-data.fr/exemple-programme-vba-macro-excel-debutant/

Mais cette fois, on utilise réellement le modèle objet d’Excel. Le code présenté est plus mystérieux pour le néophyte. Il serait même à déconseiller, car s’il faut comprendre les concepts objets et connaitre le modèle objet Excel avant d’écrire un premier programme VBA, il faudra de nombreuses semaines pour arriver à ses fins !

Voici donc le code, plus « académique » :

Sub NOTES_BAC2()
ligne_notes = 2
ligne_mentions = 2
Dim notes as Worksheet
Dim mentions As Worksheet
Set notes = Sheets("notes")
Set mentions = Sheets("mentions")
mentions.Cells.ClearContents
mentions.Cells(1, 1).Value = "prénom"
mentions.Cells(1, 2).Value = "nom"
mentions.Cells(1, 3).Value = "mention"
mentions.Cells(1, 4).Value = "note"
 
Dim MaPlageNote, MaCelluleNote As Range
Dim MaPlageMention, MaCelluleMention As Range
Set MaPlageNote = Range(notes.Cells(2, 1), notes.Cells(2, 1).End(xlDown))
'attention, notes.Range(Cells(2, 1), Cells(2, 1).End(xlDown)) ne fonctionne pas si la feuille
'notes n'est pas sélectionnée !
Set MaCelluleMention = mentions.Cells(2, 1)
 
For Each MaCelluleNote In MaPlageNote
note = MaCelluleNote.Offset(0, 2).Value
If note >= 12 Then
MaCelluleMention.Value = MaCelluleNote.Value
MaCelluleMention.Offset(0, 1).Value = MaCelluleNote.Offset(0, 1).Value
MaCelluleMention.Offset(0, 2).Value = "assez bien"
MaCelluleMention.Offset(0, 3).Value = MaCelluleNote.Offset(0, 2).Value
If note >= 14 And note < 16 Then
MaCelluleMention.Offset(0, 2).Value = "bien"
End If
If note >= 16 And note < 18 Then
MaCelluleMention.Offset(0, 2).Value = "très bien"
End If
If note >= 18 Then
MaCelluleMention.Offset(0, 2).Value = "félicitations du jury"
End If
Set MaCelluleMention = MaCelluleMention.Offset(1, 0)
'attention, MaCelluleMention = MaCelluleMention.Offset(1, 0) ne fonctionne pas
'il faut affecter l'objet Cellule avec le Set.
End If
Next
End Sub

Pourquoi ne pas déclarer pas toutes les variables Il est inutile, pour une petit bout de code comme celui-ci, de s’embêter à tout déclarer, et donner un type à chaque variable. Ce genre de code est là pour automatiser une tâche, ce n’est pas un projet (pour lequel bien sur la rigueur s’imposerait, et les déclarations de variables aussi). Quand on fait une boucle for i = 1 to 5, c’est inutile (même si le compilateur va en mémoire allouer plus de place pour la variable non typée qu’une variable en integer par exemple). Par contre, et on peut insister sur ce point : pour toute variable de type objet (excel, fso…) il est essentiel de correctement typer les variable, afin de bénéficier de l’auto-complétion de l’éditeur VBE !

Ci-dessous, nous déclarons correctement un objet « mentions » de type « worksheet » (une feuille d’un fichier excel), et lorsque nous utilisons cette objet, dès que nous tapons le « . » derrière l’objet, l’auto-complétion nous fournit la liste des propriétés et méthodes disponibles pour cet objet. Indispensable !

Par contre, si nous mettons la déclarations en commentaire (ou si nous déclarons sans affecter de type avec « Dim mentions » uniquement), nous n’aurons rien :

En revanche, il va de soi que pour un vrai « projet » VBA, il est bien entendu obligatoire de déclarer toutes les variables, en positionnant en début de module l’expression « Option Explicit On ».

Top

Exemple ou Squelette d’un programme VBA / Macro Excel (niveau débutant)

Celui qui ne connait pas le modèle objet saura lire ce code (d’où la mention débutant). Quand on écrit du code VBA en utilisant « mieux » le VBA Excel et le modèle objet associé, c’est moins lisible.

Dans notre cas de figure, inutile aussi de déclarer les variables : les puristes diront que oui, mais quand on écrit 20 lignes de code pour se faciliter la vie (souvent le cas d’une macro Excel), il faut rester pragmatique…

Nous allons étudier un exemple très simple de programme / Macro VBA, afin de gérer des données présentes sur 2 feuilles différentes d’un même classeur.

Le principe est de lire ligne par ligne les données de la première feuille, et de faire un traitement sur la deuxième feuille. Le tout avec un code volontairement simpliste, qui utilise peu les objets et méthodes Excel (cela fera l’objet d’un autre tutoriel).

L’objet de l’exercice est de lire une première feuille »notes » contenant nom, prénom, et note. Pour toutes les notes supérieures à 18, on écrira dans la deuxième feuille « mentions » les nom, prénom, et la mention « félicitations du jury ». Pour les notes entre 16 et 18, on notera mention très bien, et ainsi de suite jusqu’à la mention « assez bien ». Seules les notes supérieures à 12 sont affichées.

Voici la feuille notes :

Et nous souhaitons obtenir le résultat suivant :

Pour cela, nous pouvons proposer le code suivant :

Sub NOTES_BAC()
ligne_notes = 2
ligne_mentions = 2
Sheets("mentions").Cells.ClearContents
Sheets("mentions").Cells(1, 1).Value = "prénom"
Sheets("mentions").Cells(1, 2).Value = "nom"
Sheets("mentions").Cells(1, 3).Value = "mention"
Sheets("mentions").Cells(1, 4).Value = "note"
Do While Sheets("notes").Cells(ligne_notes, 1).Value <> ""
note = Sheets("notes").Cells(ligne_notes, 3).Value
If note >= 12 Then
Sheets("mentions").Cells(ligne_mentions,1).Value = Sheets("notes").Cells(ligne_notes, 1).Value
Sheets("mentions").Cells(ligne_mentions,2).Value = Sheets("notes").Cells(ligne_notes, 2).Value
Sheets("mentions").Cells(ligne_mentions,3).Value = "assez bien"
Sheets("mentions").Cells(ligne_mentions,4).Value = note
If note >= 14 And note < 16 Then
Sheets("mentions").Cells(ligne_mentions, 3).Value = "bien"
End If
If note >= 16 And note < 18 Then
Sheets("mentions").Cells(ligne_mentions,3).Value = "très bien"
End If
If note >= 18 Then
Sheets("mentions").Cells(ligne_mentions,3).Value = "félicitations du jury"
End If
ligne_mentions = ligne_mentions + 1
End If
ligne_notes = ligne_notes + 1
Loop
End Sub

Que faut-il savoir pour comprendre ce code ? Écartons-nous d’emblée des notions de programmation objet, qui ne sont pas simples à comprendre. Un lointain passé de basic, ou de n’importe quel langage structuré permet de s’en sortir. Les rares subtilités de ce code sont :

1/ L’adressage de la cellule Excel

Permet de savoir lire le contenu d’une cellule Excel, ou bien l’écrire. Une cellule Excel dans un fichier Excel est une entité définie avec 3 paramètres : Feuille, Ligne, Colonne. Si on doit travailler avec plusieurs fichiers Excel, alors l’adressage de la cellule Excel nécessite un 4ème paramètre, le nom du fichier.

exemple en écriture : Sheets(« mentions »).Cells(ligne_mentions,3).Value = « assez bien »

Nous valorisons la cellule située en ligne « ligne_mentions » et en colonne 3, de la feuille nommée « mentions ».

exemple en lecture : note = Sheets(« notes »).Cells(ligne_notes, 3).Value

2/ Effacer le contenu d’une feuille

Pour réinitialiser des traitements.

Sheets(« mentions »).Cells.ClearContents

Le reste est de l’algorithmique on ne peut plus classique.

Top

VBA EXCEL & OUTLOOK : Exporter le carnet d’adresse Outlook vers Excel

Pour diverses raisons, il peut être utile d’exporter le carnet d’adresse d’Outlook vers Excel.

Voici un bout de code très simple qui boucle sur toutes les listes d’adresses Outlook et le contenu dans un fichier Excel. Au préalable, il faut bien entendu activer dans l’éditeur VBA la référence Outlook (menu « outils – références ») :


Sub EXPORT_ADRESSES()
Dim MonOutlook As New Outlook.Application 
Set MaSession = MonOutlook.Session 
Cells.ClearContents 
Cells(1, 1).Value = "Liste" 
Cells(1, 2).Value = "Addresse" 
Cells(1, 3).Value = "Nom" 
Cells(1, 4).Value = "Type" 
ligne = 2
For Each Liste In MaSession.AddressLists 
For Each Entry In Liste.AddressEntries 
Cells(ligne, 1).Value = Liste.Name 
Cells(ligne, 2).Value = Entry.Address 
Cells(ligne, 3).Value = Entry.Name 
Cells(ligne, 4).Value = Entry.Type 
ligne = ligne + 1 
Next 
Next
End Sub
Top
1 2 Page 1 of 2

Contactez-nous contact@expert-data.fr

EXPERT data, solutions pour TPE/PME et grands comptes