VB-VBA : Programmer efficacement Microsoft Excel

Formation VB-VBA, tutoriel & guide de travaux pratiques en pdf.

Paramètres nommés

Excel accepte une syntaxe particulière pour ses méthodes, celle des paramètres (ou arguments) nommés. En Visual Basic l’appel d’une procédure paramétrée se fait en plaçant les paramètres dans le même ordre que celui qui se trouve dans la déclaration de la procédure. Cette méthode reste vraie en VBA mais il y a aussi la possibilité de ne passer que quelques-uns de ces paramètres en les nommant. Regardons par exemple la méthode Find d’Excel.
Sa déclaration est :
expression.Find(What, After, LookIn, LookAt, SearchOrder, SearchDirection, MatchCase, MatchByte)
Son utilisation normale serait
Set CelluleCible=ActiveSheet.Cells.Find(2,ActiveCell, xlValues, xlWhole, xlByColumns, xlNext, False)
Mais je pourrais très bien simplifier en écrivant
Set CelluleCible=ActiveSheet.Cells.Find(What:=2, LookIn:= xlValues)
Ceci permet une plus grande clarté du code ainsi qu’une simplification à la condition expresse de bien connaître la valeur par défaut des paramètres.

Les évènements

La gestion des événements dans Excel se fait via du code soit dans un module d’objet WorkSheet, soit dans le module de l’objet WorkBook. Pour atteindre le module de code du classeur, on ouvre la fenêtre VBA et dans l’explorateur de projet on double click sur « ThisWorkBook ». Pour atteindre le module d’une feuille on peut soit passer par l’explorateur de projet, soit faire un click droit sur l’onglet de la feuille et choisir « Visualiser le code ».
Intercepter les évènements Excel en Visual Basic
Dans la feuille VB où l’on souhaite récupérer l’événement on déclare une variable globale
Private WithEvents MaFeuille As Excel.Worksheet
Après il suffit d’écrire une procédure d’évènements identique à la procédure Excel correspondante. Par exemple, l’événement de feuille SelectionChange s’écrit dans Excel
Private Sub Worksheet_SelectionChange(ByVal Target As Excel.Range)
Et dans Visual Basic :
Private Sub MaFeuille _SelectionChange(ByVal Target As Excel.Range)
L’exemple suivant montre l’interception de l’événement BeforeClose d’un classeur dans Visual Basic afin d’empêcher la fermeture de celui-ci (et donc de l’application Excel) par l’utilisateur. Pour qu’Excel puisse se fermer dans cet exemple, il faut remplir la case « A1 », ce qui est géré par l’événement MonClasseur_SheetChange.
Private WithEvents MonClasseur As Excel.Workbook
Private MonExcel As Excel.Application, MaFeuille As Excel.Worksheet
Private Sub OuvreExcel()
Set MonExcel = New Excel.Application
MonExcel.ReferenceStyle = xlR1C1
Set MonClasseur =
MonExcel.Workbooks.Open(« D:\User\jmarc\tutorial\excel\tutor1.xls ») Set MaFeuille = MonClasseur.Worksheets(« pilotage ») MonExcel.Visible = True
End Sub
Private Sub MonClasseur_BeforeClose(Cancel As Boolean)
Cancel = True
End Sub
Private Sub MonClasseur_SheetChange(ByVal Sh As Object, ByVal Target As Excel.Range)
If Not MonExcel.Intersect(Target, MaFeuille.Cells(1, 1)) Is Nothing
Then
MonExcel.EnableEvents = False
MonClasseur.Close False
Set MaFeuille = Nothing
Set MonClasseur = Nothing
MonExcel.Quit
Set MonExcel = Nothing
End If
End Sub

La base, l’objet Application

Cet objet représente l’application Excel. Il est l’objet parent de tous les autres, et contient des propriétés méthodes et évènements très intéressants, que nous allons regarder maintenant.
Evènements
On utilise rarement les événements au niveau de l’application, pour la raison simple qu’ils ne sont pas accessibles directement. Pour pouvoir les utiliser, il faut dans le projet créer un module de classe dans lequel on met le code suivant :
Public WithEvents App As Application
Comme la plupart des événements gérés se retrouvent dans les objets classeur et feuille, nous les utiliseront plutôt à ce niveau.
Propriétés
Je vais donner maintenant quelques propriétés utiles de l’objet application
AskToUpdateLinks : Si sa valeur est False la mise à jour des liaisons se fait sans appel d’une boite de dialogue, qui sinon apparaît automatiquement.
Calculation (xlCalculationAutomatic, xlCalculationManual, xlCalculationSemiautomatic): Définit le mode de recalcul d’Excel. Il s’agit d’une propriété très importante pour l’optimisation du temps d’exécution. En effet, pour les feuilles contenant des formules de calcul, le mode de calcul automatique peut être très coûteux en temps, en effet Excel recalcule intégralement une feuille de calcul à chaque modification ou suppression. C’est pourquoi, en général on bloque le mode de calcul au départ en faisant :
Application.Calculation = xlCalculateManual
Puis on exécute les calculs lorsqu’on en a besoin avec la méthode Calculate
CutCopyMode (False,xlCopy,xlCut) : Définit si on utilise un mode par défaut copier ou couper. En fait, on l’utilise souvent sous la forme Application.CutCopyMode=False pour vider le presse papier.
DisplayAlerts : Si sa valeur est False, les messages d’avertissements d’Excel ne seront pas affichés.
✭ Il faut toujours remettre la valeur à True en fin d’exécution
Interactive : Désactive toutes les interactions entre Excel et le clavier/souris.
✭ Il faut toujours remettre la valeur à True en fin d’exécution
International : Renvoie des informations relatives aux paramètres régionaux et internationaux en cours.
Cette propriété est en lecture seule. On s’en sert généralement pour connaître les séparateurs.
ReferenceStyle (xlA1 ou xlR1C1): Permet de basculer entre les modes L1C1 et A1. Il est à noter que cela change l’affichage des feuilles mais que cela peut aussi avoir une influence sur le code. En effet certaines formules comme les règles de validations doivent être écrites dans le même système de référence que celui de l’application.
ScreenUpdating : Permet de désactiver la mise à jour d’écran. Pour des raisons de vitesse d’exécution il est conseillé de toujours la désactiver.
✭ Il faut toujours remettre la valeur à True en fin d’exécution, sous peine de récupérer un écran figé. SheetsInNewWorkbook : Permet de définir le nombre de feuilles contenues dans un nouveau classeur.
Lorsque l’on sait exactement le nombre de feuilles nécessaires, on peut modifier cette propriété afin de ne pas faire des worksheets.add. Il convient de restaurer cette valeur en fin d’exécution.
Méthodes
Calculate : Permet de forcer le calcul. La syntaxe Application.Calculate est peu utilisée. On l’utilise principalement sous la forme MaFeuille.Calculate. Sachez toutefois que l’on peut restreindre le calcul à une plage à des fins de performance. Exemple :
Worksheets(1).Rows(2:3).Calculate ne fait les calculs que sur les lignes 2 et 3.
Evaluate : Permet de convertir une chaîne en sa valeur ou en l’objet auquel elle fait référence. Nous allons regarder quelques utilisations de cette méthode.
Interprétation de formule de calcul Imaginons que ma cellule A1 contient le texte (12*3)+4, écrire
Range(« A2 »).Value=Application.Evaluate(Range(« A1 »).Value)
Renverra 40 en A2. De même on pourra écrire :
Resultat= Application.Evaluate(« (12*3)+4 »)
La méthode permet aussi d’évaluer une formule respectant la syntaxe Excel (en anglais) ; on peut écrire Resultat= Application.Evaluate(« Sum(A1:E5) »)
Interprétation d’une adresse Si ma cellule A1 contient B1:B2 je peux écrire
Application.Evaluate(Range(« A1 »).Value).Font.Bold=True
Il est à noter que le mot Application est facultatif et on trouve parfois la notation
[A1].Font.Bold=True
Qui est strictement équivalente !
FindFile Permet de lancer une recherche de fichier dans laquelle on spécifie les critères. La collection FoundFiles contient les fichiers trouvés correspondant.
(cf l’exemple plus loin)GetOpenFileName : Ouvre la boite de dialogue « Ouvrir un fichier » mais n’ouvre pas le fichier. La méthode renvoie juste le nom complet du fichier sélectionné.
GetSaveAsFilename : De même que précédemment mais avec la boite « Enregistrer Sous… »
Goto : Je cite cette méthode pour vous mettre en garde. L’enregistreur de macro l’utilise lors de l’appel d’une plage nommée, elle sous-tend un « Activate » et un « Select ». Il faut donc faire très attention lors de son utilisation dans le code car elle peut facilement changer les objets actifs.
Intersect : Renvoie une plage qui est l’intersection de n plages. Bien que les plages appartiennent à des objets feuilles, la méthode Intersect appartient directement à l’objet Application.
OnKey : Permet l’exécution d’une macro lors de l’appui sur une touche ou sur une combinaison de touches. Application.OnKey « ^{A} », « MaProc » lance la procédure MaProc sur l’appui de CTRL-A Pour restaurer la séquence de touche on écrit :
Application.OnKey « ^{A} »
OnTime : Permet l’exécution d’une macro à une heure fixée ou après un délai précisé.
Application.OnTime Now + TimeValue(« 00:00:15 »), « MaProc » attend 15 secondes avant d’exécuter la procédure MaProc
Run : Lance l’exécution de la procédure spécifiée. Si celle-ci attend des paramètres, ils ne peuvent pas être nommés.
Par exemple imaginons la fonction suivante :
Private Function MaSomme(Oper1 as Double, Oper2 as Double) As Double.
La syntaxe d’appel sera :
Resultat=Application.Run(MonClasseur!MaSomme,1.2,2.3)
Union : Renvoie l’union de n plages. Même remarque que pour Intersect.
Wait : Marque une pause dans l’exécution de la macro.

Collections et objets particuliers

Nous allons regarder trois collections (objets) intéressantes accessibles depuis l’objet Application.
FileSearch
L’objet FileSearch permet une recherche standard de fichiers. On peut spécifier ses critères de recherche en valorisant ses propriétés. L’appel de la méthode Execute lance la recherche. Le résultat se trouve alors dans la collection FoundFiles.
Par exemple pour rechercher l’ensemble des fichiers Res*.xls dans le répertoire courant, et les ouvrir.
Public Sub OuvreFichier()
Dim ChercheFichier As FileSearch, compteur As Long
Set ChercheFichier = Application.FileSearch With ChercheFichier
.LookIn = CurDir
.FileName = « Res* »
.FileType = msoFileTypeExcelWorkbooks
.SearchSubFolders = True
.Execute
For compteur = 1 To .FoundFiles.Count Workbooks.Open .FoundFiles(compteur)
Next
End With
End Sub
Dialogs
La collection Dialogs contient l’ensemble des boites de dialogues utilisées par Excel. Comme il y en a beaucoup (646 dans Excel 97) je ne vais pas les énumérer, vous trouverez cette liste soit dans l’explorateur d’objets, soit dans l’aide à « Listes d’arguments de boîte de dialogue intégrée », soit dans le fichier ListeVBA.xls que vous retrouverez dans votre dossier Office (VBAlist.xls depuis Excel 2000). Ces boites agissent le plus souvent sur la sélection en cours donc nous sommes dans un des rares cas où l’emploi de la méthode Select sera obligatoire.
Voici par exemple l’appel de la boite de dialogue « alignement » pour permettre à l’utilisateur de modifier l’alignement sur la plage B1:B10
Public Sub UtilDial()
Range(Cells(1, 2), Cells(10, 2)).Select
Application.Dialogs(xlDialogAlignment).Show
End Sub
On pourrait de la même façon donner des valeurs par défaut à cette boite en lui passant des arguments
Par exemple :
Application.Dialogs(xlDialogAlignment).Show 3,False,2
Ouvre la même boite mais en définissant les alignements verticaux et horizontaux sur « centré ».
WorksheetFunction
Cet objet contient les fonctions de feuille de calcul intégrées dans Excel. Attention ces fonctions sont en anglais. Il faut faire très attention aux types des paramètres passés, en effet certaines fonctions acceptent indifféremment des plages ou des nombres alors que d’autres n’acceptent que des plages. L’exemple suivant montre le calcul d’une moyenne mêlant chiffres et plages
Dim resultat As Double
resultat = Application.WorksheetFunction.Average(Range(Cells(1, 2), Cells(10, 2)), 100, 200)
Là encore je ne donnerai pas la liste complète des fonctions que vous pourrez trouver dans l’aide, dans le fichier listeVBA.xls ou avec l’explorateur d’objets.
Résumé
Plus loin dans cet article nous trouverons des exemples d’utilisation des propriétés/méthodes de l’objet Application, mais il faut bien garder à l’esprit qu’une procédure Excel devrait toujours contrôler le mode de calcul et désactiver la mise à jour de l’écran.

L’objet WorkBook (classeur)

Le classeur est en général l’objet central de la programmation Excel. Lorsque l’application va utiliser un seul classeur, on peut utiliser ActiveWorkbook, mais dans le cas d’une application multi-classeurs, il convient de les mettre dans des variables afin d’en simplifier la manipulation. Pour cela, on fait :
En VBA
Dim MonClasseur as WorkBook
Set MonClasseur=ActiveWorkBook
En VB
Dim MonClasseur as Excel.WorkBook
Set MonClasseur=ActiveWorkBook
On peut bien sûr faire l’affectation dans le même temps que l’ouverture ou l’ajout, par exemple Set MonClasseur=WorkBooks.Open Filename:= »C:\User\Classeur1.xls »
N.B. (en VBA uniquement) : Si on utilise deux classeurs dont celui qui contient la macro, il n’est pas utile de mettre celui-ci dans une variable puisqu’on le retrouve sous le nom « ThisWorkbook ».
La collection WorkBooks
Cette collection contient l’ensemble des classeurs ouverts. L’ordre des classeurs dans la collection (index) est l’ordre d’ouverture. Elle possède quatre méthodes que nous allons étudier
Add
Permet d’ajouter un nouveau classeur à la collection et non pas d’ouvrir un classeur existant. Le classeur créé devient le classeur actif.
Elle suit la syntaxe Worbooks.Add(Template)
Si Template est un fichier Excel existant, le classeur est créé en suivant le modèle défini. Template peut aussi être une constante (xlWBATChart, xlWBATExcel4IntlMacroSheet, xlWBATExcel4MacroSheet, xlWBATWorksheet) et dans ce cas, le classeur ne contiendra qu’une feuille du type donné. Enfin si Template est omis un nouveau classeur standard est créé.
Close
Cette méthode ferme tous les classeurs de la collection. Donc attention de ne pas confondre WorkBooks.Close et WorkBooks(1).Close
Open
Ouvre un classeur Excel. Sa syntaxe est :
WorkBooks Open(FileName, UpdateLinks, ReadOnly, Format, Password, WriteResPassword, IgnoreReadOnlyRecommended, Origin, Delimiter, Editable, Notify, Converter, AddToMRU)
Seul FileName est obligatoire.
OpenText
Permet d’ouvrir un fichier texte délimité comme un classeur. Sa syntaxe est
WorkBooks. .OpenText(Filename, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo)
Quelques méthodes à connaître
Close
Ferme le classeur. La syntaxe est :
MonClasseur.Close(SaveChanges, FileName, RouteWorkbook)
Si SaveChanges est omis, une boite de demande d’enregistrement apparaît. Donner une valeur FileName différentes de celle du classeur revient à faire un SaveAs.
PrintOut
Lance l’impression du classeur. Sa syntaxe est :
MonClasseur.PrintOut(from, To, Copies, Preview, ActivePrinter, PrintToFile, Collate)
A noter que From et To sont des pages d’imprimante et non les feuilles du classeur, et que ActivePrinter permet de choisir l’imprimante
Protect / UnProtect
Active ou désactive la protection du classeur. La syntaxe est :
MonClasseur.Protect(Password, Structure, Windows) pour activer la protection
MonClasseur.UnProtect(Password) pour la retirer.
A ce propos, la protection du classeur et des feuilles est une chose indépendante. La protection du classeur sert à bloquer la structure de celui -ci, celle de la feuille à protéger les objets qu’elle contient. On peut parfaitement protéger une feuille sans protéger le classeur et inversement.
Save / SaveAs
Sauvegarde le classeur. La syntaxe de SaveAs est :
MonClasseur.SaveAs(Filename, FileFormat, Password, WriteResPassword, ReadOnlyRecommended, CreateBackup, AddToMru, TextCodePage, TextVisualLayout)

Evènements du classeur

L’objet classeur possède beaucoup d’événements, je ne vais donc détailler que ceux les plus souvent utilisés.
BeforeClose, BeforePrint, BeforeSave
Private Sub Workbook_Before—-(Cancel As Boolean)
Se produit avant l’événement. Dans le cas de BeforeClose se produit avant la demande d’enregistrement des modifications. Mettre Cancel = True dans la procédure empêche l’événement d’avoir lieu.
Open
Se produit à l’ouverture du classeur. Permet donc de faire des macros à exécution automatique
SheetBeforeDoubleClick, SheetBeforeRightClick
Private Sub MonClasseur_SheetBefore—-Click(ByVal Sh As Object, ByVal Target As Range, ByVal Cancel
As Boolean)
Ces évènements sont des évènements de feuille. On utilise l’événement au niveau classeur lorsqu’on veut centraliser l’événement pour plusieurs feuilles du classeur. S’il existe aussi une procédure pour l’événement au niveau feuille, elle s’exécutera avant la procédure du classeur.
Cet événement ne se produit pas sur les feuilles graphiques.
L’objet « Sh » est un objet WorkSheet qui représente la feuille sur laquelle l’événement se produit. Nous regarderons l’argument Target dans « les techniques de programmation ».
SheetCalculate, SheetChange, SheetSelectionChange
Les mêmes remarques sont valables. Nous étudierons en détail ces événements dans le chapitre sur les feuilles.
Les Feuilles (Sheets)
Un classeur Excel est composé d’une ou plusieurs feuilles. Ces feuilles peuvent être de cinq types :
Feuille de calcul, Feuille graphique, Macro Excel4, Macro Excel4 International, feuilles de boîtes de dialogue Excel 5.
La collection Sheets contient toutes les feuilles du classeur quel que soit leur type. En fait, on utilise très peu cette collection sauf pour connaître le nombre total de feuille avec la propriété Count ou parfois pour ajouter une feuille de type macro Excel4.
Feuille de calcul (WorkSheet)
L’objet WorkSheet doit suivre les mêmes règles de programmation que l’objet classeur. On utilise ActiveSheet que dans le cas d’une application ne mettant en jeu qu’une seule feuille. Sinon on référence la feuille par :
En VBA
Dim MaFeuille as WorkSheet
Set MaFeuille=ActiveWorkSheet
En VB
Dim MaFeuille as Excel.WorkSheet
Set MaFeuille=ActiveWorkSheet
Observons qu’il est rarement nécessaire de référencer à la fois la feuille et le classeur.
La collection Worksheets
Cette collection contient l’ensemble des feuilles de calcul du classeur. Le numéro d’ordre d’un objet WorkSheet dans cette collection est son ordre dans les onglets du classeur.
Add Méthodes utiles
WorkSheet.Add(Before, After, Count, Type)
Before et/ou After permettent de préciser la position où la feuille doit être ajoutée. L’argument Count détermine le nombre de feuilles à ajouter. Type revient à faire un Add de la collection Sheets.
Comme je l’ai signalé au préalable, la position de l’objet WorkSheet dans l’ensemble des Worksheets du classeur va aussi être son index dans la collection WorkSheets. C’est pourquoi accéder à une feuille par WokSheets(index) peut être une source d’erreur.
Copy Worksheets(index).Copy(Before, After)
Duplique une feuille dans le classeur ou crée une copie dans un autre classeur.
Attention lors de la copie d’une feuille, la collection « Names » des plages nommées appartient à l’objet WorkBook et à l’objet WorkSheet. Lors de la duplication de la feuille, Excel va créer deux noms presque identiques, ce qui peut engendrer des bugs.
DeleteWorkSheets(index).Delete
Supprime la feuille spécifiée par index. Un classeur doit toujours contenir au moins une feuille. FillAcrossSheets WorkSheets(collection). FillAcrossSheets (Range, Type)
Permet de recopier une plage sur plusieurs feuilles en même temps. Range détermine la plage à copier, Type définit le mode de copie (xlFillWithAll, xlFillWithContents ou xlFillWithFormulas)
Il faut passer une collection ou un tableau d’objets WorkSheet contenant les feuilles concernées par la recopie à la méthode. Cette collection doit toujours contenir la feuille contenant la plage source.
Le code suivant recopie la plage A1:A10 sur toutes les feuilles de calcul du classeur

INTRODUCTION
NOTIONS IMPORTANTES
LES COLLECTIONS
L’ADRESSAGE
PARAMÈTRES NOMMÉS
LES ÉVÈNEMENTS
LA BASE, L’OBJET APPLICATION
EVÈNEMENTS
PROPRIÉTÉS
MÉTHODES
COLLECTIONS ET OBJETS PARTICULIERS
RÉSUMÉ
L’OBJET WORKBOOK (CLASSEUR)
LA COLLECTION WORKBOOKS
QUELQUES MÉTHODES À CONNAÎTRE
EVÈNEMENTS DU CLASSEUR
LES FEUILLES (SHEETS)
FEUILLE DE CALCUL (WORKSHEET)
PLAGE ET CELLULE (RANGE)
PROPRIÉTÉS NE RENVOYANT PAS UN OBJET – COLLECTION
PROPRIÉTÉS RENVOYANT UN OBJET
QUELQUES MÉTHODES
PLAGES PARTICULIÈRES
OBJETS GRAPHIQUES (CHART & CHARTOBJECT)
COLLECTION CHARTS & CHARTOBJECTS
EVÈNEMENTS
PROPRIÉTÉS ET MÉTHODES
LES OBJETS CONSTITUANTS
CONSEILS GÉNÉRAUX
TECHNIQUES DE PROGRAMMATION
EVÈNEMENTS
APPLICATION
CLASSEUR
FEUILLE
GESTION DES ERREURS
PLAGE
GRAPHIQUE
PILOTER EXCEL AVEC VISUAL BASIC 6
ASTUCES DIVERSES
CONCLUSION