Chapitre 1 Notions avancées de Visual Basic pour Excel 1.1 Rappels 1.1.1 Développement dans Excel La barre @outils développeur est masquée par défaut dans Excel et il faut l'activer en allant dans Fichier->Options->Personnaliser le ruban Pour éviter tout probléme, Excel désactive par défaut les macros des fichiers qu'il ouvre. II faut soit préciser qu’on veut activer les macors & ouverture du fichier soit activer les macros manucllement dans sécurité des macros de l’onglet développeur., 1.1.2. Lisibilité des programmes lest important de soigner a lisibilité des programmes : choix des noms de variables, commentaires ct surtout indentation. Les deux progranmesde la figure 1.1 font la mémie chose, mais lequel est le plus facile & comprendre et & modifier si besoin? FIGURE 1.1 ~ Lisibilité des programmes 1.2. Les variables : stocker l'information Pour effectuer des caleuls ou stocker des données qui ne doivent pas étre visibles sur 1a feuille de calcul, on utilise des variables. Une variable permet de stocker une valeur pouvant étre modifige aut cours de Vexéeution d'un programme. 1.2.1. Types de variables Les types numériques disponibles sontBoolean True ou False D octets Byte entier non signé 1 octet Integer entier 2 octets Long entier 4 octets Single flottant 4 octets Double flottant 8 octets Currency | numérique a virgule fixe | 8 octets existe aussi des types spécialisés String | chaine de caractéres Date date et heure Object | référence & um objet Variant | tout type de données Le type Variant peut paraitre optimal, mais il occupe beaucoup de mémoire ct donne un code moins rapide qu'un type adapté, I] faut utiliser uniquement quand on ne peut pas faire autrement 1.2.2. Déclaration de variables La déclaration explicite des variables n'est pas obligatoire, mais conseillée pour optimiser le code et faciliter la lisibilité du programme. Par exemple, on peut écrire sans déclaration préalable x=50 Mais dans ce cas, x peut étre de type Byte, Int, Long, Single ou Double. Le choix fait par visual basic n'est pas forcément optimal et il vant micux utiliser les lignes suivante pour étre certain d’avoir um nombre a virgule flottante Dim x As Double x = 50 ‘On déclare les variables selon le modéle Dim var As Type Par exemple Dim a As Integer Dim total as Currency Liaffectation s'écrit comme suit, de méme que les opérations arithmétiques, par exemple largeur = 50 longueur = 30 aire = largeur + longueur perimetre = 2 * (largeur + longueur) Il est préférable Vinstruction forcer la déclaration explicite des variables en VBA. Pour ce faire, il suffit de placer Option Explicit en haut (en premiere ligne) des modules de code avant toutes procédures et toutes fonctions: Exercice : Créer un bouton avec du code associé, Si on clique sur le bouton, le programme prend la valeur entrée dans la case Al, la divise par 3 et place la partie entiére du résultat dans la case A2. Pour garantir qu'une variable ne change pas de valeur, on peut la déclarer en tant que constante. Une constante permet dattribuer un nom & une valeur fixe, La déclaration d'une constante se fait & l'aide du mot clé Const selon le format suivant Const NomConstante [As Type] = valeur oft la déclaration du type est optionnelle mais conseillé1.3 Fonctions et procédures 1.3.1. Définition d'une fonction Lorsqu’on est amené & utiliser plusieurs fois une méme portion de code, il est judiciewx de eréer une fonction ou une procédure. On pourra ainsi l'appeler & chaque fois qu'on en a besoin simplement grace au nom de la fonction. Fonctions et procédures sont deux choses trés proches. La. différence étant qu'une fonction renvoie un résultat alors qu'une procédure ne renvoie rien, Les procédures commencent par Sub ou Private Sub et se terminent par End Sub. Vous aver déja utilisé des fonctions comme Private Sub ConmndButton1_Click() End Sub Celle-ci était créée automatiquement par Visual Basic mais vous pouver créer les vatres en choisissant un nom. Par exemple, on veut une proc entre 0 et 100 wre qui remplit Ia ease AL avee une valeur aléatoire entidre comprise Private Sub HasardAl() [a1] = CInt (Rnd * 100 + 0.5) End Sub On Vappelle ensuite en écrivant Call HasardA1 quand on en a besoin. Si on vent ponvoir utiliser le résultat du tirage autrement que pour remplir A1, on peut eréer une fonction plutét qu'une procédure. Private Function Hasard() As Integer Hasard = CInt(Rnd * 100 + 0.5) End Function On Vappelle ensuite au besoin, par exemple en écrivant [A2] = Hasard() si on veut que le résultat aille dans la case A2. Tr’s souvent on veut pouvoir adapter le comportement de la fonction selon un paramétre. Par exemple, pour faire un tirage entre 1 et n plutét qu’entre 1 et 100, on modifie Ia fonction Private Function Hasard(ByVal n As Integer) As Integer Hasard = CInt(Rad * n + 0.5) End Function On peut ensuite lappeler par [A2] = Hasard(50) pour avoir un nombre compris entre 1 et 50 ow [42] = Hasard(1000) pour un tirage entre 1 et 1000. Le préfixe ByVal devant le paramétre n indique que n n'est pas modifié par la fonction. Si on veut modifier le paramétre alors il faut remplacer ByVal par ByRef Enfin, il est possible de donner autant de paramétres que nécessaire & une fonction en les séparant par des virgules comme ceci Private Function Hasard(ByVal mini As Integer, ByVal maxi As Integer) As Integer Hasard = CInt(Rnd + (maxi - mini + 1) - 0.5) + mini End Function 1.3.2. Variables locales et globales La portée d’une variable définit quelles procédures ou fonctions peuvent utiliser cette variable. Par défaut, les variables sont locales. Cela signifie qu'une variable n'est utilisable que dans la fonction oi elle est déclarée (ou utilisée la premiére fois si on ne la déclare pas). Si le méme nom de variable ap une autre fonction, alors c’est ume autre variable. Par exemple, sur tme feuille on a2 boutons programmés comme suitPrivate Sub ConmandButtoni_Click() End Sub Private Sub CommandButton2_Click() xexed Range("A1") .Value = x End Sub Si on clique sur le bouton 1, puis sur le bonton 2 , la valeur de la case Al sera I et pas 11. Chaque fonction sa propre variable locale x. On peut utiliser une variable globale & condition de la déclarer en début de fichier, comme ci-dessous Dim x As Integer Private Sub ConmandButtoni_Click() End Sub Private Sub ConmandButton2_Click() xexet Range("A1") Value = x End Sub Avec ce deuxitme programme, Ie résultat sera 11. Pour qwune variable soit accessible & Vensemble des procédures et des fonctions d'un module, elle doit étre déclaré au début du module & Ve procédure et de toute fonction. Pour étre accessible & l'ensemble du projet, elle doit étre rendue public & Vaide du mot clé Public comme par exemple térieur de toute Public Age As Integer Sub Modif ierage() Age = 27 End Sub On conseille de privilégier les variables locales et n'utiliser les variables globales que quand c'est absolument nécessaire. De plus, nommer ume variable globale avec un nom explicite rendra le programme bien plus lisible que simplement x. Question: Dans chacun des 2 programmes cr-dessis, que se passe-bil si on clique plusieurs fois sur le bouton 2? ‘On notera qu'une constante a la méme portée qu'une variable. Exercice | Déclarer une constante MyPI simple précision accessible & tout le projet Créer wm fonetion qui convertie une valeur dentrée en degré vers sa valeur en radian en utilisant notre constante. Appeler la fonction dans une cellule de la feuille de calcul. 1.4 Manipuler des collections : les variables de type Tableau Les feuilles de calenl sont des tableaux & deux dimensions visibles par Putilisateur. Dans certaines situations, on peu d'une variable qu’on peut déclarer avec un type et une dimension. Utiliser un tableau en mémoire plutot jue directement sur la feuille de caleul présente un intérét dans plusieurs cas @tre amenés & utiliser un tableau qui n'a pas vocation A étre visible, il s'agit alors — effectuer des calculs qui ne doivent pas étre vus par l'utilisateurine cellule est modifiée la feuille de calcul est recalculée ce qui en particulier dans une bouele — aceélérer des calculs ; en effet dds qu’ peut prendre beaucoup de temp — récupérer des données d'une fonction ou passer une série de paramitres & une fonction quand on travaille hors de la feuille de calcul (par exemple dans une UserFora), utiliser un tablean en mémoire est la meilleure solution. Un tableau & 1 dimension contient une série de données de méme type. Il peut étre déclaré selon le modile suivant Dim t() As Integer t() =2 Range("B2") Value = (1) Ici le tableau s'appelle t, il contient 6 valeurs et on peut accéder & chaque valeur en précisant sa position dans le tableau entre parenthéses, par exemple (0) pour le premier élément et t(6) pour le dernier. Par défaut indice du premier élément d’um tableau est 0, mais on peut aussi déclarer un tableau dont le premier indice est 1 en écrivant Dim u(1 To 5) As Integer Dans ce cas le premier élément est u(1) et l'élément u(0) n'existe pas. Le tableau a seulement 5 éléments. Parfois c'est une fonction qui renvoie un tableau. Par exemple, si on demande & Vutilisateur de sélectionner un ow plusieurs fichiers avec une boite de dialogue, le résultat ost un tableau dont chaque case contient un nom de fichier, On peut ensuite les utiliser ou les recopier dans la feuille de caleul avec une bouele Fer ou For Each comme ceci choix des fichiers par 1’utilisateur fichiers = Application. GetOpenFilename (MultiSelect:~True) For i = 1 To UBound(fichiers) ’Ubound() donne 1a taille du tableau Cells(i, 4) = fichiers(i) ’accés & chaque élément par son indice Next: Attention, dans ce cas vous aurez remarqué que la fonction GetOpenFilenane() renvoie un tableau dont le premier indice est 1. Pour connaitre les indices de début et de fin d'un tableau, on utilise les fonctions LBound() pour Lower Bound et UBound() pour Upper Bound. Pour éviter tout risque, il serait bon de rééerire la boucle ci-dessus sous la forme suivante afin de s'adapter A m'importe quel format de tabl For i = LBound(fichiers) To UBound(fichiers) Cells(i ~ LBound(fichiers) + 1, 4) = fichiers(i Next: 1.5 Manipulation des chaines de caractéres Une chaine de caractires est une variable destin un nom de fichier, ... On peut choisir une chaine de longueur fixe ou variable A contenir une suite de caractéres comme un texte, *chaine de longueur variable Dim titre As String *chaine de longueur fixe (50 caractéres) Dim soustitre As String * 50 Les chaines de caractixes sont éerites avec des guillemets. On peux coneaténer (mettre bout & bout), 2 chaines avec l'opérateur & de cette fagon, non = "Dupont! prenon = "Jean" noncomplet = nom & """ & prenom ‘nomcomplet = "Dupont Jean"Tlexiste une multitude de fonctions pour traiter les chaines de caractére. Voici les principales, illustrées sur des exemples — Len("Visual Basic") renvoie la longueur (nombre de caractéres) de la chaine c'est & dire 12 (Ves- Pace compte) — Left ("Visual Basic", 3) renvoie les 3 caractéres les plus A gauche c'est & dire "Vis" — Right ("Visual Basic", 3) renvoie les 3 caractéres les plus & droite c'est & dire "sic" — Mid ("Visual Basic", 3, 7) renvoie 7 caractéres & partir du troisidme c'est A dire "sual Ba" — InStr("Visual Basic", "a") recherche la premiére apparition de la chaine "a" dans la chaine "Visual Basic" et renvoie sa position, ici 5. Dans TaStr(s1, 82), la chaine s2 peut comporter plusieurs caractéxes. Si elle n'est pas trouvée le retour de la fonction vaut 0. D'autres options de InStr() sont disponibles (faites une recherche web). rrespondant "Basic" — t = Split ("Visual Basic"," ") permet de découper la chaine en plusieurs chaines et chacume & un mot. Le résulta est stocké dans un tableau avec t(0) = "Visual", et (1) Plus généralement t = Split (si, s2) découpe la chaine 1 en utilisant le séparateur douné dans 82. Le séparateur est souvent un espace, une virgule, un point — Val(s) renvoie le nombre contenu dans la chaine s qui peut @tre entier ow non. Attention le séparateur Ainsi Val ("2.3") renvoie bien le nombre 2.3 alors que Val ("2,3") renvoie le nombre entier 2 (la vingule ne fait pas partie du nombre). En complément de Val (s), la fonction IsNumeric(s) renvoie True cimal est le point (comme en anglais) ct pas la virgule (comme en francais) si la chaine contient une valeur numérique ct False sinon. existe autres fonctions moins courantes sur les chaines de caractires qui peuvent se révéler utiles dans certains cas particuliers, ne pas hésiter & faire une recherche web pour compléter ce document. 1.6 Exercices 1.6.1 Monnaie Eerire une fonction qui prend en entrée 3 variables entidres nbS0, nb20, nb10 qui désignent le nombre de billets de 50, 20 et. 10 euros dans un portefeuille, La fonction doit renvoyer la somme totale en euros, 1.6.2. Fonction maximum Ecrire une fonction Maximum qui prend deux arguments entiers et qui renvoie la plus grande des deux valeurs. 1.6.3. Permutation Ecrire une fonction qui prend 2 varaibles entitres en paramétre et qui échange leur contenu. Il faudra faire précéder les paramitres avec le mot clé ByRef au lieu de ByVal comme expliqué dans le cours pour pouvoir modifier | ur valeur. 1.6.4 Déclaration de variables Télécharger depuis I le fichier Total_Erreur .x1sm. Le programme devrait calculer la somme des valeurs des cases A1 A A10 mais le total est toujours égal A 0. Cherchez erreur dans Te programme. Vous ne trouves pas? Ajoutez Option Explicit en haut de la page de programme pour forcer la déclaration des variables, puis déclarez Total et i. La recherche de l'erreur devrait étre beaucoup plus facile. 1.6.5 Sélection de plusieurs de fichiers Eerire un programme qui ouvre une boite de dialogue de sélection de fichier avec la fonction getOpenFileNane() et qui antorise la sélection de plusieurs fichiers simultanément. Ecrire ensuite le nom de chaque fichier sélectionné dans une case de la feuille de calcul. Faire ce programme une premitre fois avec une boucle For, puis une deuxiéme fois avec une boucle For Each.1.6.6 Remplacement Ecrire un programme qui lit une chaine de caractéres dans la case A1 de la feuille de calcul et qui la recopie dans la case A2 en remplacant la lettre "R” par la lettre "L? chaque fois qu’elle est présente. 1.6.7. Vérification En utilisant la fonction InStr(), faire un programme qui vérifie si un mot entré dans la case A1 fait partie de la liste de mots suivants : courroie, poulie, roulement, galet, arbre. La case Ai sera coloriée en vert si le mot fait partie de la liste et en rouge sinon. 1.6.8 Palindrome Eerire une fonction qui prend une chaine de caractéres en entrée et renvoie True si la chaine est un palindrome, c'est & dire que Ie texte est le méme s'il est Iu & Pendroit ou A Penvers. Exemples de palindromes : "été", "bob", "kayale” 1.6.9 Code ASCII Chaque caractére d'une chaine est codée en mémoire sur un octet (8 bits). Ainsi chaque lettre ou caractére spécial a un code correspondant appelé code ASCII. Par exemple, le code ASCTI de A est 65, celui de B est 66, .. Le code ASCII est um standard qui date des débuts de informatique et qui est adapté la langue anglo-saxonne. Il est toujours utilisé dans de nombreuses situations méme s'il est remplacé par de nouveaux standards pour gérer la multitude de signes associés aux différentes langues utilisées sur ‘Terre. Le fonction Asc() renvoie le code ASCII du premier caractire de la chaine passée en argument. A inverse, la fonction Chr) renvoie le caractére correspondant au code passé en argument, A Paide de ces deux fonctions, programmer les boutons suivants 1, Le bouton ASCII prend le caractére contenu dans A1 et met le code correspondant dans B1 2, Le bouton Caractére prend le code contenu dans A2 et met le caractére correspondant dans B2 3. Avec une boucle, faire afficher tous les caractéres correspondant aux codes ASCII allant de 32 & 255, sauf le 127, Les codes de 0 & 31 sont des codes spéciaux non imprimables (le 127 aussi) et ceux au dessus de 128 ne sont pas standards (dépendants du langage). 4, Le bouton Crypter prend le texte contenu dans A3, remplace chaque lettre par le caractire suivant et met résultat dans BS Le bouton Déerypter fait Popération inverse 1.6.10 Signature Ecrire une fonction qui Signature qui prend en entrée une liste de prénoms (sous forme d’um tableau de chaines de caractéres) et wn nom. En sortie cette fonction doit renvoyer une chaine formée par la premitre lettre de chaque prénom séparées par des points suivie du nom, Par exemple, si on donne les prénoms “John” et "Ross" et le nom ”Ewing”, le résultat sera J. R. Ewing” 1.6.11 Décomposition de nom de fichier Ecrire un programme qui ouvre une boite de dialogue de sélection de fichier avec la fonction getOpenF ileName() qui autorise la sélection d'un seul fichier. Ecrire ensuite le nom du fichier choisi dans la case At (par exemple C:\Users\erroyer\Docunents\Classeurt .x1sm puis décomposer ce nom en trois parties — Ie chemin d’accs, ici C:\Users\erroyer\Document s\ — le nom sans extension, ici Classeurt — T'extension du fichier, ici .x1sm1.6.12 Tri de fichiers Ecrire un programme qui ouvre une boite de dialogue de sélection de fichier avec la fonction getOpenFileName() et qui autorise la sélection de plusieurs fichiers simultanément. Eerire ensuite le nom de chaque fichier Glectionné dans tne case de la feuille de calcul en triant les fichiers selon leur extension. Les fichiers avec extension .x1sx doivent étre écrits dans la premitre colonne, ceux avec l'extension x1em dans la deuxitme colonne. On pent utiliser au choix une boucle For ou For Each. 1.6.13 Pendu Le but est de faire un programme qui simule le jeu du pendu. Au début le programme demande au joneur I de rentrer un mot (pendant que le joueur 2 ne regarde pas). La saisie sera faite vie une TextBox placée dans un UserForn, Ensuite c'est le joucur 2 qui joue dans un nouvel UserForm pour essayer de deviner le mot mystére. Le programme affiche d’abord le mot en remplagant chaque lettre par un tiret, A chaque proposition de lettre du joucur 2, si la lettre est présente dans le mot alors le programme Paffiche sa place. Sinon, on compte un erreur. Au bout de 5 erreurs, si le mot n'a pas été trouvé alors le joueur 2 a perdu.