Programmation en VBA

1. Programmation en VBA Introduction au language Visual Basic for Applications dans le monde Excel

2. Ressources • FAQ : http://msdn2.microsoft.com/en-us/isv/bb190540.aspx • Référence : http://msdn2.microsoft.com/en-us/isv/bb190540.aspx • Nombreux livres • Aide en ligne d’Excel

3. Programmer sous Excel • Pour accéder à l’environement de programmation sous Excel, deux possibilités • Par les menus • Alt F11

4. L’environement de dévelopement Éditeur Explorateur de projet Propriétés Espions (débogage)

5. Ajouter une fonction à Excel Click droitsur “Microsoft Excel objects” : Séléctioner “Module”

6. Généralités sur VBA • VBA est un langage compilé au moment de l’exécution. • Insensible à la casse, l’editeur VBA modifie texte tapé de tel sorte qu’il ay la même casse que des objets existants (fonctions, variables, sub, etc.) • Une fonction peut se nommer MyFunction, on tape myfunction l’éditeur changera m et f an majuscules. • Les commentaires débutent par ‘ et terminent en fin de ligne. • La barre d’outils ’Édition’ peut s’avérer pratique quand on programme.

7. Structure d’unefonction Délimiteurs de fonction Nom de l’argument Liste des arguments Type de l’argument Type de la fonction Nom de la fonction Corps de la fonction: Attribution fonctionne comme valeur de retour

8. Utilisation dans Excel

9. Conditions pour fonctionsà appeler dans Excel • Ne pas changer la structure d’une feuille Excel. • Ne pas changer les propriétés d’une cellule. • Toute fonction appelée doit respecter les mêmes restrictions. • Si la fonction doit être exécuté même si ses arguments ne changent pas il faut appeler Application.Volatile. • Doit retourner un type qui Excel sait traiter.

10. Subroutines • Les sub ne renvoient pas de résultat, contrairement aux fonctions. • Les sub ne peuvent pas être appelées directement depuis les cellules de Excel. • Si une sub est appelée depuis une fonction qui est appelée depuis une cellule, alors elle doit respecter les mêmes restrictions que ces fonctions.

11. Valeurs par défaut • Les paramètres des Function et Sub peuvent être optionnels. Si un paramètre est optionnel tous les paramètres suivants le sont aussi. • Si un paramètre est de type Variant on peut tester que l’appel n’a pas définit ce paramètre avec IsMissing. Sub ajoutEspace(ByRefstr As String, Optional nb As Integer = 5) Dim prefix As String prefix = Space(nb) str = prefix & str Debug.Printstr End Sub Sub ajoutEspace(ByRefstr As String, Optional vnb As Variant) Dim prefix As String Dim nb As Integer If IsMissing(vnb) Then nb = 5 Else nb = CInt(vnb) End If prefix = Space(nb) str = prefix & str Debug.Printstr End Sub

12. Paramètres nommés • On peut appeler une fonction en nommant ses paramètres. Sub ajoutEspace(ByRefstr As String, Optional nb As Integer = 5) Dim prefix As String prefix = Space(nb) str = prefix & str Debug.Printstr End Sub ajoutEspacestr:=res, nb:=10

13. Déclaration des variables On déclareune variable à l’aide de Dim : FunctionsurfCercle(x As Double) As Double Dim pi As Double pi = 3.1415279 surfCercle = x * x * pi End Function Par défaut les variables n’ont pas besoin d’etre déclarées, elles ont le type Variant. Il faut utiliser Option Explicit pour rendre la déclaration obligatoire.

14. Types • String : Chaines de caractères. • Types numériques : entiers, réels en virgule flottante (précision simple ou double) et réels en virgule fixe (decimal). • Dates : Représentation des dates en nombre de jours écoulés depuis le premier janvier 1900. • Tableaux : une collection de variables d’un autre type, l’indexation est au choix de l’utilisateur, par défaut de 1 à N. • Variant : Type abritant tout autre type. • Objets : Types définis dans des bibliothèques ou par des utilisateurs.

15. Strings • Unechaine de caractères se déclare comme String, par exemple : • Dim message As String • De nombreusesfonctions de manipulation de strings sontdisponibles, exemples : • Len, calcule la taille • InStr, cherchedansune String • Replace, modifieune String • Etc.

16. Types numériques • Entiers : • Byte • Integer, entier 2 octets • Long, entier 4 octets • Decimal : Currency • Réels virgule flotante : • Single, 4 octets • Double, 8 octets

17. Booléens et dates • Boolean, représente vrai ou faux. • Date, représente une date, stockée comme nombre de jours depuis le premier janvier 1900. Il y a un jour de décalage avec des dates Java.

18. Tableaux • Un tableau (array) se déclare en ajoutant au nom d’une variable des paranthèses et une dimension. • On accéde aux éléments d’un tableau par indexation. • L’option globale Option Base permet de fixer le premier indice de chaque tableau, par défaut les tableaux commencent à 1. Dim t(1 To 10) As Integer Sub arrTest(j As Integer) Dim t(1 To 10) As Integer Dim i As Integer i = 1 While i <= 10 t(i) = j + I i = i + 1 Wend End Sub

19. Appel de Function • Une Function s’appelle en plaçant une liste d’expressions entre parenthèses à la place de la liste des arguments. • Un appel de fonction remplace toute expression du même type que le type de retour de la fonction. • Exemple : • X=surfCercle(2.0)+4

20. Appel de sub • 2 façons d’appeler une sub: • Call suivi du nom, les arguments entre () • nom du Sub (sans Call) avec arguments séparés par , Function HelloWorld() As String Dim res As String res = "Hello World" Call ajoutEspace(res) HelloWorld = res End Function Sub ajoutEspace(ByRefstr As String) str = " " & str End Sub Function HelloWorld() As String Dim res As String res = "Hello World" ajoutEspace res HelloWorld = res End Function Sub ajoutEspace(ByRefstr As String) str = " " & str End Sub

21. Sub, Function : paramètres • Deux façons de passer un paramètre a une Function où Sub : • Par valeur, le nom du paramètre est précédé par ByVal. • Par référence, le nom du paramètre est précédé par ByRef (cette méthode de passage de paramètres est la méthode par défaut).

22. Variables globales • Une variable déclarée à l’extérieur de toute Function ou Sub est globale. • Deux types de visibilités sont possibles : • Public, visible depuis tous modules, • Private, visible uniquement dans le module courant.

23. Instructions de contrôle • Conditionels : • If • Iif Function Signe(x As Double) As Byte If x > 0 Then Signe = 1 ElseIf x < 0 Then Signe = -1 Else Signe = 0 End If End Function Function sPlus(x As Double) As Double sPlus = IIf(x > 0, x, 0) End Function

24. Boucles Sub arrTest(j As Integer) Dim t(1 To 10) As Integer Dim i As Integer i = 1 While i <= 10 t(i) = j + i i = i + 1 Wend End Sub • While • For • For Each, s’utilise avec de collections. Sub arrTest(j As Integer) Dim t(1 To 10) As Integer Dim i As Integer For i = 1 To 10 t(i) = j + i Next i End Sub

25. Manipulation des cellules • Les feuilles et les cellules d’Excel sont des variables de type spécial. Elles peuvent être manipulées dans VBA. • À chaque feuille correspond une variable :

26. Utilisation d’objets • Un objet est une variable dont le type est une classe appartenant au projet courant où à une bibliothèque. • Un objet se déclare à l’aide de Dim comme pour toute variable. • Un objet doit être crée avec une instruction New et l’attribution du résultat de New se fait obligatoirement à l’aide de Set. • Un objet non initialisé contient Nothing.

27. Objets et classes • Une classe est un type défini dans une bibliothèque. • Une variable dont le type est une classe est un Object. • Un Objet contient des membres qui peuvent être • des propriétés, • Des méthodes (des fonctions) • Deux objets de même type ont, en général, des propriétés avec des états différents.

28. Accès aux cellules • Parmi les membres de la classe Worksheet on trouve la méthode Cells. • Il y a deux versions de la méthode Cells • À deux arguments entiers représentant le nombre de lignes et le nombre de colonnes. Ex: Cells(1,1) • À un argument String représentant l’adresse d’une cellule. Ex Cells("A1") • La méthode Cells retourne un objet du type Range qui représente un groupe de cellules.

29. Range : quelques membres • Value, propriété qui représente le contenu de la cellule. • Text, propriété qui représente le texte affiché par la cellule. • Formula, propriété qui représente la formule de la cellule. • ClearContents, méthode qui efface le contenu de la cellule. • ClearComments, méthode qui efface les comentaires de la cellule.

30. Exemple Option Explicit Sub displaySquare() Dim i As Integer For i = 1 To 100 result.Cells(i, 1).Value = i * i result.Cells(i, 1).Interior.ColorIndex = i Mod 32 Next i End Sub Result est un Objet de type Excel.Worksheet Excel -> bibliothèque Worksheet -> class Value estunepropriété de la class Range Cells estuneméthode de la class Worksheet à 2 arguments (il a une Autreméthode de même nom à 1 argument) qui retourne un objet de type Excel.Range

31. Évènements • Le évènements sont des subroutines qui s’exécutent assynchronement . Ils sont déclenchés automatiquement par des actions extérieures. • Quelques exemples : • Click de souris sur bouton • Changement de contenu d’une cellule • Changement de la cellule courante Private Sub btnGo_Click() displaySquare End Sub Private Sub Worksheet_Change(ByVal Target As Range) If Target.Cells.Count > 1 Or IsEmpty(Target) Then Exit Sub End If If Target.Address = "$A$1" Then calc.Cells(1, 4).Value = "A1 Changed" End If End Sub Private Sub Worksheet_SelectionChange(ByVal Target As Range) calc.Cells(2, 4).Value = "selection is now " & Target.Address End Sub

32. Évènements Nom de la class, Dans certains cas nom De l’objet Nom de l’évènement

33. Erreurs • Dans son comportement par défaut l’exécution s’arrête et un message est affiché. • On peut spécifier un autre comportement si une erreur se produit, trois comportemens sont possibles : • On ErrorGoto 0, le comportement par défaut • On ErrorResumeNext, ignorer les erreurs et passer à l’instruction suivante. • On ErrorGoto <label>, sauter à <label> (ligne marqué de <label>:) si une erreur se produit. • Si une erreur se produit la variable Err est initialisée.

34. Erreurs (suite) • La variable Err contient plusieurs membres, les plus importants : • Number, contient le numéro unique de l’erreur. • Description, contient la description de l’erreur. • Raise, produit une erreur (utile pour signaler des erreurs à d’autres parties du programme. • Clear, nettoye la dernière erreur.

35. Erreurs (exemple) Sub ajoutEspace(ByRefstr As String, Optional vnb As Variant) Dim prefix As String Dim nb As Integer On Error GoToerr_label If IsMissing(vnb) Then nb = 5 Else nb = CInt(vnb) End If prefix = Space(nb) str = prefix & str Exit Sub err_label: MsgBoxErr.Description End Sub Modification du traitement des erreurs. Label Accès à l’erreur

36. Modules de classe • Dans un module de classe on peut définir ses propres classes. • Une classe contient 3 types de membres : • Des variables membres • Des méthodes • Des propriétés • 2 types de visibilité pour les membres : • Private • Public

37. Créer une classe

38. Exemple : Action Option Explicit Public nom As String Public cours As Currency Variables membres

39. Exemple : Option Option Explicit Private isCall As Boolean Public strike As Currency Public maturity As Date Public sousjacent As Act Public Property Let TypeContrat(tp As String) If LCase(tp) = "call" Then isCall = True Else isCall = False End If End Property Membre privé Membrespubliques Propriété : Écriture

40. Exemple : Option Public Property Get TypeContrat() As String If isCall Then TypeContrat = "Call" Else TypeContrat = "Put" End If End Property Propriété : Lecture

41. Exemple : Option Public Function GetPrice(r As Double, vol As Double) As Double Dim td As Date Dim days2exp As Long td = Date days2exp = maturity - td If isCall Then GetPrice = Call_Eur(sousjacent.cours, strike, days2exp, r, vol) Else GetPrice = Put_Eur(sousjacent.cours, strike, days2exp, r, vol) End If End Function Fonctionmembre

42. Utilisation d’une classe Sub test() Dim FT As New Act Dim CallFT As New Opt Dim price As Double FT.nom = "Fance Telecom" FT.cours = 15.3 CallFT.TypeContrat = "Call" Set CallFT.sousjacent = FT CallFT.strike = 15 CallFT.maturity = "20/12/2012" price = CallFT.GetPrice(0.01, 0.2) Debug.Print price End Sub Déclarations/allocations Utilisation de propriétés Appel de méthode

43. Forms • Les forms sont des objets graphiques qu’on peut afficher dans Excel. • On utilise des forms pour intéragir avec un utilisateur. • Une form est toujours composé d’une fenêtre sur laquelle s’affichent des contrôles graphiques. • La boite à outils de Excel propose plusieurs contrôles : boutons, zones de texte, liste de choix, etc. • Chaque contrôle propose des évènements qui permettent d’interagir avec l’utilisateur. • Chaque form gère son propre module.

44. Créer une form

45. Form et boite à outils

46. Design de la form

47. Code associé à une form Évènement click du bouton Accès à une propriété d’un autre contrôle