All posts in Visual Basic (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

Générer automatiquement un document PowerPoint

Voici un exemple de code pour générer automatiquement un document PowerPoint. Bien entendu, nous pourrons générer des documents PowerPoint à partir de bases de données Excel.

Sub GenerationPowerPoint()
'programme de génération d'un document Powerpoint à partir d'un modèle
 
'instanciantion PowerPoint (ajouter la référence Microsoft PowerPoint 1x.xx Library
'dans éditeur VBA menu Outils - Référence
Dim MonPowerPoint As New PowerPoint.Application
MonPowerPoint.Visible = True
 
'ouverture de la présentation modèle (1ere diapositive titre, 2ème diapositive modèle des autres diapos)
Dim MaPresentation As PowerPoint.Presentation
Set MaPresentation = MonPowerPoint.Presentations.Open2007("D:\MonModele.pptx")
 
'on recopie le modele (MonModele) pour créer des diapositives supplémentaires
'modèle composé d'un titre et d'un cadre de texte
Dim MaDiapositive As PowerPoint.Slide
Dim MonModele As PowerPoint.CustomLayout
Set MonModele = MaPresentation.Slides(2).CustomLayout
Set MaDiapositive = MaPresentation.Slides.AddSlide(3, MonModele)
 
'pour activer une diapositive avec son numéro de page
Set MaDiapositive = MaPresentation.Slides(2)
 
'saisie du titre de la diapositive
MaDiapositive.Shapes.Title.TextFrame.TextRange.Text = "Titre diapo"
 
'saisie de 2 lignes dans le cadre principal de la diapositive
'attention, ici on choisit le 3ème cadre à titre d'exemple, à adapter
With MaDiapositive.Shapes(3).TextFrame.TextRange
.InsertAfter ("ligne 1")
.InsertAfter (Chr(10))
.InsertAfter ("ligne 2")
End With
 
'ajout et saisie d'un cadre avec texte
Set MonText = MaDiapositive.Shapes.AddShape(msoShapeRectangle, 300, 300, 250, 140)
With MonText.TextFrame.TextRange
.Text = "exemple de texte en arial"
.Font.Name = "arial"
End With
 
End Sub
Top

Manipuler Excel en dotnet : utiliser l’automation

Attardons-nous aujourd’hui sur le langage haut de gamme Microsoft, dotnet. Que ce soit du VB.net ou du C#, qu’importe, les principes sont exactement les mêmes. Nous pouvons au sein d’un projet Visual Studio (toutes versions) manipuler Excel presque de la même manière que dans l’éditeur VBA d’Excel, c’est l’Automation (on manipule Excel à l’aide de son modèle objet)

Il faut au préalable importer les références Excel (au même titre que dans l’éditeur VBA Excel, nous importons parfois les références « dll » d’autres logiciels tels que Business Objects ou Word).

Pour cela, ajoutons la référence COM à Microsoft Excel. Ce n’est pas une référence .NET ! Nous verrons que cela a une incidence.

Validons par OK. En ajoutant la référence dans notre code :

Imports Excel = Microsoft.Office.Interop.Excel

Nous pourrons utiliser ainsi un code très proche du VBA, comme en témoignent les quelques lignes ci-dessous.

Dim MonExcel As New Excel.Application
MonExcel.Visible = True
Dim MonFichier As Excel.Workbook = MonExcel.Workbooks.Open(MonPath)
Dim MaFeuille As Excel.Worksheet = MonFichier.Sheets(1)
Dim MaPlage As Excel.Range = MaFeuille.Range(MaFeuille.Cells(2, 1), & _
 MaFeuille.Cells(2, 1).End(Excel.XlDirection.xlToRight).End(Excel.XlDirection.xlDown))
Dim MonEntete As Excel.Range = MaFeuille.Range(MaFeuille.Cells(1, 1), & _
 MaFeuille.Cells(1, 1).End(Excel.XlDirection.xlToRight))

A titre d’exemple, nous proposons ici un code simpliste qui ouvre Excel, accède à un fichier Excel choisi par l’utilisateur, et affiche le contenu du fichier (pour peu que ce contenu soit sous forme de table) dans un composant DATAGRIDVIEW d’une application Windows.

Le code VB.net proposé pour l’exemple (pas de classe, pas de gestion des erreurs) est :

Imports Excel = Microsoft.Office.Interop.Excel
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'CHOIX DU FICHIER EXCEL
Dim MonOpenFileDialog As New Windows.Forms.OpenFileDialog()
MonOpenFileDialog.Filter = "Fichiers Excel (*.xls)
*.xls"
MonOpenFileDialog.ShowDialog()
Dim MonPath As String = MonOpenFileDialog.FileName
'DECLARATION OBJETS EXCEL
Dim MonExcel As New Excel.Application
MonExcel.Visible = True
Dim MonFichier As Excel.Workbook = MonExcel.Workbooks.Open(MonPath)
Dim MaFeuille As Excel.Worksheet = MonFichier.Sheets(1)
Dim MaPlage As Excel.Range = MaFeuille.Range(MaFeuille.Cells(2, 1), & _ 
 MaFeuille.Cells(2, 1).End(Excel.XlDirection.xlToRight).End(Excel.XlDirection.xlDown))
Dim MonEntete As Excel.Range = MaFeuille.Range(MaFeuille.Cells(1, 1), & _ 
 MaFeuille.Cells(1, 1).End(Excel.XlDirection.xlToRight))
 
'AJOUT DATAGRIDVIEW DANS LE FORM1
Dim MonDataGridView As New Windows.Forms.DataGridView
MonDataGridView.Location = New Point(0, 0)
MonDataGridView.Dock = DockStyle.Fill
Me.Controls.Add(MonDataGridView)
 
'METHODE : AUTOMATION CLASSIQUE
For Each MaCellule As Excel.Range In MonEntete
MonDataGridView.Columns.Add(MaCellule.Value, MaCellule.Value)
Next
MonDataGridView.Rows.Add(MaPlage.Rows.Count - 1)
For Each MaCellule As Excel.Range In MaPlage
MonDataGridView.Item(MaCellule.Column - 1, MaCellule.Row - 2).Value = MaCellule.Value
Next
End Sub
End Class

Nous obtenons un résultat comme celui-ci :

AVANTAGES :

Pour celui qui connait bien le modèle objet Excel, qui a l’habitude de VBA, l’automation Excel en dotnet est très simple. Presque aucune adapation de code n’est requise, à part pour les constantes (dans le code, voir Excel.XlDirection.xlToRight)

INCONVENIENTS :

De même qu’en VBA, attention à la lenteur d’un tel code ! Pour extraire les 1000 lignes de l’exemple ci-dessus, il a fallu près de 15 secondes !

Un autre inconvénient majeur est que la référence n’est pas .NET, mais COM. Aussi, les objets parlent assez « sommairement » entre eux. Nous aurions pu imaginer que le RANGE Excel puisse se déverser directement dans un objet typé dotnet, comme une DATATABLE, ou directement dans les ITEMS du DATAGRIDVIEW, mais il n’en n’est rien. Le RANGE Excel est un Array d’object, avec lequel on ne peut pas travailler directement. Il faut boucler sur tous les éléments pour les exploiter, ce qui prend un temps considérable.

CONCLUSION :

L’automation permet donc de manipuler Excel exactement comme on le ferait dans Excel, mais attention aux performances ! Pour un accès rapide, il faudra travailler avec Excel comme un fournisseur de donner et exploiter ADO.NET.

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
1 2 3 Page 1 of 3

Contactez-nous contact@expert-data.fr

EXPERT data, solutions pour TPE/PME et grands comptes