Cours VB-VBA : Programmer efficacement Microsoft Excel

Cours VB-VBA : Programmer efficacement Microsoft Excel, tutoriel & guide de travaux pratiques structure des programmes Visual Basic 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.

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
ASTUCES DIVERSES
CONCLUSION

…….