Programmation en VBA avec Microsoft Excel

    Tout d'abord bonjour. Dans cette série d'articles, nous apprendrons à programmer en VBA (Visual Basic for Application) sous Excel (version 5.0, 7.0 et 8.0). Les premiers articles seront consacrés à l'initiation et au principes de base de VBA (et de Visual Basic en général) puis la difficulté ira en grandissant (passage de paramètres, macros complémentaires, apples à l'API Win32, ...). Pour bien comprendre ces articles il faut maîtriser Excel, pas la peine d'être un " dieu " mais il faut quand même bien savoir d'en servir.

    I- Introduction
    Depuis sa première version, Excel est doté d'un langage de macro simple. Ce langage très pratique pour automatiser certaines tâches n'était pas assez puissant pour faire des applications poussées dans le traitement des donnés. Pour palier à ce manque, Microsoft a décidé d'intégrer une version adaptée de son langage fétiche, Visual Basic, dans Excel (et Access), à partir de sa 5ème version. Grâce à cela, nous bénéficions maintenant d'un langage évolué dans un tableur de qualité. Ces deux qualités font d'Excel un excellent tableur pour le tri et l'analyse de données.

    Nous allons donc découvrir les possibilités de ce langage dans cette série d'articles (Tout les exemples sont dans le fichier exemples.xls, téléchargeable ici).

    II- Les bases

    1.1- Généralités

    VBA est très simple, sa syntaxe est claire et son apprentissage aisé, ces trois choses en font un langage parfait pour le débutant.

    VBA possède un assez grand nombre de mot réservé, ces mots sont inutilisables pour des noms variables, ou de procédure car ils désignes une fonction ou une méthode d'accès à un objet. Toutes les procédures commencent par le mot-clé "Sub" suivit du nom de la procédure et se terminent par End Sub (si vous êtes débutant, vous affolez pas, j'expliquerait ça plus tard).

    Les règles à respecter :

      Désignez toujours un objet pas son chemin d'accès complet ( ex : Classeur.Feuille.objet.méthode) ça allonge un peu le code mais ca facilite la lecture.
      Appelez de préférence les procédures, les variables et les fonctions pas des noms évocateur de leur rôle dans le programme (Une petite astuce : quand vous déclarez une variable, utiliser les majuscules et quand vous l'utiliserez écrivez la en minuscules car si son nom est correctement écris, VB mettra correctement les majuscules et ça évite pas mal d'erreur bêtes ; cette astuces est valable aussi pour les fonction intégrées à VBA).
      Eclaircissez le code le plus possible en sautant des lignes dès que c'est nécéssaire ; ça facilite la relecture quand le programme atteint une taille importante
      Rédiger toujours des algorithmes sur papier avant de les coder
    • Et enfin commenter votre code le plus possible car un long code non commenté devient très difficile à lire et ça fait perdre un tas de temps :-).


    1.2- Accès aux objets et appel de propriétés

    Dans VBA, on désigne par objet tout les choses que l'on manipule.Un objet est "une chose" dont la structure même ne change pas, seule les caractéristique changent. Par exemple, une cellule peut avoir un fond vert et du texte rouge, une autre peut avoir un fond blanc et du texte noir ; pourtant, ces deux cellules sont bien un même objet, elles ont juste des caractéristiques différentes. Ainsi, un classeur, une feuille, une cellule, une boite de dialogue, ... sont des objet. A l'inverse, les procédures ne sont pas des objet (bah non puisque c'est des procédures !!) car on peut changer leur structure et elle n'ont pas de propriétés propres, d'ailleurs ont ne peut les modifier qu'en modifiant leur structure. L'autre grande différence entre les procédures et les objets est que les objet communique avec l'extérieur grâce à une interface (c'est elle qui autorise les changements de caractéristiques) alors que la procédure n'en a pas, elle communique avec l'extérieur grâce à des variable globales qui sont accessible par toutes les procédures. Par exemple, pour modifier la variable "valeur" de l'objet "cellule", je fait : cellule.valeur = "25", la variable est "dans" l'objet, alors que si je veux modifier la variable "valeur" (il faut que la variable valeur soit déclarée comme public, ou globale au début de la page de code) dont se sert la procédure "cellule", je fait : valeur = "25". C'est la un grand paradoxe de VBA : on programme par procédure mais on accède à des objet ! On dit que VBA est un lange semi-objet. Cette petite tentative d'explication de la différence entre un objet et une procédure est valable pour tout les language. Je sais pas si j'ai été très clair alors si vous avez rien compris, faites le moi savoir par mail, j'essayerais de vous expliquer plus clairement). Sinon, si vous voulez avoir plus de détails sur la programmation objet, il y a plein de livres la dessus dans toutes les bonnes crèmeries :)

    L'accès à un objet se fait un nommant le nom du classeur (facultatif si l'on travaille sur le classeur actif), puis le nom de la feuille et enfin le nom de l'objet.

    Par exemple pour désigner la valeur de la cellule "A1" dans la feuille "Test", elle même dans le classeur "Demo" on procède ainsi :

    Sub Acces_a_la_cellule_A1 () 'définit la procédure
    Worksheets("Test").select 'sélectionne la feuille "Test"
    Val_cel = Range("A1").value 'met la valeur de la cellule A1 dans la variable Val_Cel
    MsgBox Val_Cel 'affiche une boite de dialogue qui affiche la valeur de la variable et donc de "A1"
    End Sub 'fini la procédure

    Pour écrire la valeur "4" dans la cellule "B2" on fait comme ça :

    Sub Met_la_cellule_B2_a_4 () 'définit la procédure
    Worksheets("Test").select 'sélectionne la feuille "Test"
    Range("B2").value = 4 'met "4" dans B2
    End Sub 'fini la procédure

    Et pour définir Arial comme police pour la plage "A1:B6" comme ça :

    Sub Changement_de_font()
    Worksheets("Test").select
    Range("A1:B6").Font.Name = "Arial" 'défini Arial pour la plage A1:B6
    End Sub

    Voilà quelques exemples d'appel de propriétés des cellules ; pour en avoir d'autre lancez l'aide sur "Range" et cliquez sur propriétés.

    3- Désignation d'une cellule et déplacements dans une feuille

    3.1- Désigner des cellules

    Pour désigner une cellule, il y a deux "grandes" méthode :

    • Par son nom :
      Range ("A2").select 'sélectionne la cellule A2 de la feuille active
    • Par ses coordonnés (n° de ligne, n° de colonne) :
      Cells(2, 1).select 'sélectionne la cellule A2 de la feuille active

    Pour désigne une plage de cellules on utilise les même méthodes. Comme paramètres, il faut indiquer la cellule en haut à gauche en celle en bas à droite de la plage :

    • Par son nom :
      Range ("A2:C4").select 'sélectionne la plage A2:C4 de la feuille active
    • Par ses coordonnés (n° de ligne, n° de colonne) :
      Range(Cells(2, 1), Cells(4, 3)).select 'sélectionne la plage A2:C4 de la feuille active



    3.2- Se déplacer dans une feuille par rapport à d'autres cellules

    Pouvoir nommer une cellule par rapport à une autre est très utile. Pour cela, on utilise la méthode "Offset" sur l'objet range (ou activecell). L'exemple suivant selectionne la cellule C4 en partant de A2

    Range ("A2").select 'sélectionne A2

    Activecell.offset(3, 2).select 'selectione C5 (2 déplacement vers le bas et 3 vers la droite)

    4- Les "boucles" indispensables

    Pour exécuter une série de commande un certain nombre de fois ou jusqu'à ce qu'une condition soit remplie, ou pour tester la valeur d'une variable, on utilise des boucles.

    4.1- Les boucles de test avec IF...THEN...ELSE...

    Se traduit par Si...Alors...Sinon... Ce type de boucle sert à tester une variable. Par exemple n veut que si A1 est inférieure à 10, elle soit mise à 15 et sinon qu'elle soit mise à 0 :

    1ère forme, détaillée (conseillée) :

    Sub Test_avec_If()
    Worksheets("If").Select
    Range("A1").Select
    If ActiveCell.Value < 10 Then 'défini le test
    ActiveCell.Value = 15 'met A1 à 15 si le test est VRAI
    Else 'Sinon
    ActiveCell.Value = 0 'met A1 à 0 si le test est FAUX
    End If 'indique la fin du bloc de test
    End Sub

    2ère forme, compacte (déconseillée) :

    Sub Test_avec_If2()
    Worksheets("If").Select
    Range("A1").Select
    If ActiveCell.Value < 10 Then ActiveCell.Value = 15 Else ActiveCell.Value = 0 'Teste
    End Sub

    4.2- Les boucles avec SELECT...CASE

    C'est en fait un "If" amélioré car ce type de boucle permet de choisir entre plusieurs cas possible :

    Sub Test_avec_Select_Case()
    Worksheets("Case").Select
    Range("A1").Select
    valeur = 8 ' Initialise la variable "valeur"
    Select Case valeur 'Initialise la selection des cas de la variable "valeur"
    Case 1 To 5 '"Valeur" comprise entre 1 et 5.
    ActiveCell.Value = "La valeur est comprise entre 1 et 5"
    Case 6, 7, 8 '"Valeur" = 6 ou 7 ou 8.
    ActiveCell.Value = "La valeur est comprise entre 6 et 8"
    Case Else 'Dans tout les autre cas
    ActiveCell.Value = "La Valeur est supérieure à 8 ou inférieure à 1"
    End Select 'termine la section de cas
    End Sub

    4.3- Les boucles avec FOR...NEXT

    Ce types de boucle est très simple : il permet d'exécuter une boucle un certain nombre de fois. Il a besoin du nom de la variable-compteur, de sa valeur de début, de fin et du nombre d'unités qu'il doit ajouter à la variable à chaque boucle (si ce paramètre est omis, la valeur pas défaut est +1).

    Exemple : Comment remplir les cellules A1 à F6 en "diagonale" par des nombres allant entre 1 et 6 :

    Sub Remplir_en_diagonale()
    Worksheets("Test2").Select
    Range("A1").Select 'sélectionne la cellule A1
    For x = 1 To 6 Step 1 'initialise la boucle avec la variable x=1,
    jusqu'à ce quelle atteigne 6 en ajoutant 1 à x à chaque boucle
    y = x 'met y à la même valeur que x
    Cells(y, x).Select 'sélectionne la cellule qui a pour numéro de ligne y et
    pour numéro de colonne x
    ActiveCell.Value = x 'met la valeur de x dans la cellule active
    Next x 'termine la boucle
    End Sub

    4.4- Les boucles avec DO...LOOP

    C'est la boucle que j'utilise le plus souvent, elle a 3 variantes mais pour l'instant, nous allons en utiliser qu'une seule et c'est bien suffisant car elle englobe les 2 autres. Cette boucle permet d'effectuer une instruction jusqu'à ce qu'on lui dise d'arrêter.

    Voici l'exemple de FOR réécrit avec DO :

    Sub Remplir_en_diagonale2()
    Worksheets("Do").Select
    Range("A1").Select
    x = 0 'initialise x à 0
    Do 'initialise la boucle
    x = x + 1 'ajoute 1 à x
    y = x 'met y à la même valeur que x
    Cells(y, x).Select 'selectionne la cellule qui a pour numéro de ligne y et
    pour numéro de colonne x
    ActiveCell.Value = x 'met la valeur de x dans la cellule active
    If x = 6 Then Exit Do 'Si x=6 alors sort de la boucle
    Loop
    End Sub

    III- Exercice

    Pour appliquer ce que vous avez appris, rien de tel qu'un petit exercice (utilisez la tableau de la feuille exercice dans le fichier d'exemple) :

    Rédigez une procédure qui rempli les colonnes "Nb de vendus" et "Nb de volés" et une autre qui change la couleur de la cellule affichant le gain ou la perte pour chaque fruit suivant que le bilan est un gain ou une perte (et faite pareil pour le total).

    J'ai déjà mis les formules.

    Envoyez moi un mail quand vous avez réussi.

    J'espère que cette première partie vous a plu. Si vous trouvez une erreur (bah oui, j'en fait aussi des fois... :-) ), si quelque chose n'est pas clair, ou si vous avez un problème en VBA, envoyez moi un petit mail, j'essayerais de le résoudre.

    A bientôt

    ZeZeBaR